Running a team on a spreadsheet-database without losing data

Problem

I work at a K-12 online learning provider: schools in the US and abroad partner with us so their students can take courses online. My team runs tutoring and grading, and keeps its records in Airtable, a database that looks and feels like a spreadsheet. It holds tutoring requests, the shared calendar, staff details, which grading work each person has taken on, a library of answer keys and rubrics, and a copy of the platform’s course lists. Automations on top of it send emails, from alerts for staff to session reports for partner schools. I built the automations and reports described here, and I look after the database.

Constraints

• There is no test copy. The database is the real one, and colleagues are working in it while I change it.
• An automation that emails a school cannot be tested by running it, because a test email is a real email.
• Tables link to each other, so deleting a row in one place can quietly break a link somewhere else.

What I built

Five rules I follow for every change:

1. Look before writing. Count the rows, check the new rows are not already there under a similar name, and save a copy of anything I am about to change.
2. Small, labelled batches, each approved by a person who has seen exactly what will be written.
3. Check afterwards. Read each new row back; the new row count must equal the old count plus the batch.
4. Never delete. When something disappears from the source, its row gets a “last seen” date and stays, so no link breaks.
5. Prove the source data is complete before marking anything as gone. An incomplete download changes nothing.

Automation code gets a stricter check. I make the edit offline first and take its fingerprint (a hash: a short code that changes if even one character changes). In the editor, the same edit is applied to the live code, and it is saved only if the result has the same fingerprint, so a typo cannot slip through.

Reports to schools went to me first, marked as a test, before any reached a school. If reading the data fails or comes back empty, I get an alert and the school gets nothing. Tutors’ notes are left out for group sessions. I test on saved data where names and email addresses are replaced by fingerprints, and notes by their length.

How I proved it worked

• Predicted before building. Before adding a column that labels which language a course is taught in, I worked out every row’s answer offline, then compared row by row once the column was live. Every row matched. A catch-all value turns anything unexpected into a visible count instead of a blank.
• A tiny character, caught. A new tutor’s email address had an invisible tab character at the start, so their work shifts would never have matched them. It was fixed the same day, and a checklist script now tests every new tutor’s record before they can be booked.
• Looking past the obvious. When the guide linked from a staff alert moved to a new address, I checked every automation and formula for an old link. None was left, but a field sent reviewers to a section of the guide by its number, which a renumbering would have silently broken. The fix: new versions now replace the guide in place, so its address never changes.
• Closing gaps early. Building a second school report from the first showed two ways a student from another school could end up in a report: a tutor’s email typed in the student field, or a request naming a different school. Neither had happened. I closed both in the live report too, and that week’s real data listed the same students before and after.

Outcome

• Each batch is written as one action that can be undone, with its before-and-after counts on record.
• Partner schools receive daily and weekly session reports; the newer ones ran as tests to me before any reached a school.

What I would do next

• Keep all automation code in version control, with the same before-and-after fingerprints.
• Run the checklist script and the cross-table counts on a schedule that sends alerts.

The demo it pairs with

Safe-write sandbox (new). Stage a batch of changes on a made-up table, approve it, and watch the checks and the read-back. Try to delete a row and see it flagged instead.