How the cattle pipeline was built

The site is static: Evidence queries the database when it builds and ships the results as files, so visitors never touch the database. The only live code is three small functions on Cloudflare: one takes uploads, one builds pre-filled templates, and one builds an animal's history file.

Designed to cost almost nothing

The only bill is the domain, about $11 a year. Everything else is on a free plan, and the free plans' limits are what shaped most of the design:

Decisions

An append-only event log. Every upload adds events and nothing is ever changed or deleted. The herd's current state is a database view that takes each animal's latest event. A bad upload is handled by marking its batch as voided, which removes it from every view without losing the record. That gives an audit trail for free and makes uploads safe to repeat.

All-or-nothing validation. Every row is checked before anything is written: required fields, allowed values, real dates, sensible weights, and rules that depend on the database, such as no events recorded after an animal was sold. If any row fails, nothing is saved, and the response names the sheet, row and column of every problem.

Duplicate detection by content. Each upload is fingerprinted from its cleaned contents, so the same data sent again as a CSV, an Excel file or a Google Sheet is recognised and refused.

A data model that looks like a ranch's own notes. The event types are the ones a rancher would use: worked, moved, birth, purchase, sold and deceased. What happened on a working day (vaccinated, weighed, pregnancy checked) goes in the notes. A positive pregnancy check also carries the months pregnant; an open cow is written as "preg check - open" with no months. The current herd is a database view that takes each animal's latest event and works out the weight and whether the cow is pregnant, so the dashboards and the templates read one definition.

Short rows, filled in from the animal's record. Only the tag, date and event type are required. For an animal already on the ranch, the server fills in its type, gender, birth year, mother and pasture from what is stored, so a new row can be as short as a date and a note. A new animal must arrive with a birth or purchase row, and its later rows copy from that one. The check runs in two passes: first on the file alone, then against the database.

Spreadsheets written from scratch. My first pre-filled templates edited the real template file, which is a zip of XML files, so the dropdowns and formats survived. When the data model changed, that became the thing to maintain, so I replaced it with a small writer that builds the workbook directly. The allowed values and the column order now live in one file of code that the writer and the validator both read, so a change is made once instead of in a binary template and again in the code.

One file to look up, edit and add to. The Animal Lookup page downloads one animal's history as a spreadsheet, one row per event, each with the id it has in the database. A visitor adds a row at the bottom and uploads the file on the same page. The server recognises the saved rows by their ids and skips them, ignores an edit to an old row (the log is append-only, so corrections stay with me), refuses any new row for a different animal, and refuses a sold or deceased animal. The duplicate check fingerprints only the new rows, so the same new event sent twice is still caught.

A shortcut for the Transactions tab. After typing a purchase's tags on the Events tab, the Transactions tab asked for the same tags again as a comma-separated list. Now a transaction row with no tags covers every purchase (or sale) dated that day, and the head count and total are worked out. A row that lists its tags follows the original rules. The response notes which tags were filled in, so it is never a silent guess, and two blank rows on one date are refused as ambiguous.

One database login per job. The dashboards read with a read-only role. The upload function can insert but not update or delete. The clean-up job can change only one column. A leaked credential can do only what that one job needs.

Guardrails for a public database

Anyone can write to this demo, so it has several security layers:

• A bot check (Cloudflare Turnstile), verified on the server before any file is read. The template and history downloads sit behind the same check, so a script cannot keep waking the database.
• A limit of five saved uploads per visitor per hour. The visitor is identified by a keyed hash of the address, so no addresses are stored.
• File limits: 1 MB, 500 rows per tab, short notes.
• Parameterised SQL everywhere, so uploaded text can never become a command.
• Google links are accepted only from docs.google.com, and the export address is built on the server, so the function cannot be pointed at another site.
• A daily job that marks uploads older than seven days as voided.

Things that went wrong

Leaving Metabase and Railway for Evidence. This whole project started on Supabase and Metabase, with Metabase hosted on Railway. I never loved Metabase. It carries its own branding, so to make the dashboards look like part of my site I had to embed them with iframes. Keeping it running on Railway cost about $12 a month, which did not seem worth it for a portfolio demo. I switched to Evidence because it gives me much more control over the layout, and because everything lives in git: the pages, the queries and the styling are plain files I can review, version and rebuild, instead of settings stored inside a tool.

Supabase to Neon. Evidence and Supabase never connected smoothly. There were workarounds, but it was always more fiddly than it should have been. Then I left the project paused for too long, the free Supabase project was turned off, and because it was a free account I could not get help bringing it back. I moved the database to Neon. I was worried about Neon putting the database to sleep when it is idle, but this data is not busy, and Evidence loads the data when it builds, so the dashboards never wait on the database and cannot break while it sleeps. It wakes quickly when an upload arrives. The same move later rescued my Kansas cattle market data project, which was on Supabase too.

Planning around build credits. I originally planned to host the demo on Netlify, next to the rest of my site. But Netlify's free plan meters deploys in credits, and my Kansas data project was already using most of them. A demo that rebuilds every time someone uploads would have burned through the rest within days, so before launching I moved the demo to Cloudflare Pages, which has a much larger free build allowance. The portfolio still serves it under its own address through a proxy.

A file too big for the host. Cloudflare Pages rejects any file over 25 MB, and the in-browser database engine that Evidence ships is bigger than that. I wrote a build step that removes those two files and points the site at the identical files on a public CDN. As a side effect, first-time visitors download less from my hosting.

A page with no queries. The upload page stopped starting up, and the bot check never appeared. Evidence writes a data file for each page that has queries, and the upload page had none, so the server answered the missing file with an HTML page and a success code. Evidence tried to read that as data and gave up. Adding one small, useful query fixed it. The lesson is to check what a "success" response actually contains.

The code

The dashboards and the upload function are in the cattle-demo-evidence repository on GitHub.

← Back to the project page