The data consistency checklist for SaaS: 14 database checks between a demo and production

Is your app’s database ready for customers? It is when 14 things are true and you can show each. In a selected set of 21 AI-built apps I audited in June and July 2026, one had an endpoint where a single request drops the production database. This data consistency checklist for SaaS is those 14 checks, each with a test.

What this data consistency checklist for SaaS covers

A data consistency checklist for one SaaS app is 14 checks: a schema that rejects bad rows, appropriate indexes, no N+1 queries, atomic multi-step writes, a sized connection pool, version-controlled migrations, a restored backup, UTC timestamps, recoverable deletion, a staging seed script, orphan cleanup, a timed recovery drill, a diagram that matches the schema, and bounded private file storage.

This area holds 14 of the 123 checks in production hardening, the ones that keep records consistent, queries efficient and recovery practical. “Data quality” here means something narrower than the analytics-team sense: the rows your app writes are rows it can trust. So this is a data quality checklist for your own database, and the table below is the template itself, with nothing to download.

SQL, NoSQL, and what your app is actually running on

Your app’s database engine decides which checks apply: a SQL database, such as Postgres on Supabase, Neon or Railway, or a document store, such as Firestore or Base44’s NoSQL database. Six of the 14 checks are written for a relational engine; the other eight apply unchanged to both. On Firestore, checks 4 and 11 still apply in their own form.

Find out which one yours is before running anything below. Postgres keeps rows in tables with declared columns and can enforce the links between them; a document store keeps documents in collections. Base44’s docs describe each entity as a schema for “documents in a collection, stored in Base44’s NoSQL database”, and add that “The database is MongoDB-compatible”.

The six relational checks are 1 (schema), 2 (indexes), 4 (transactions), 5 (pooling), 6 (migrations) and 11 (orphaned records). The other eight carry over as written. Check 3 is among them, because fetching one document per list row is the same per-record lookup as one query per row. Two of the six still bite on Firestore, in a different shape. Check 4 does, because Firestore’s transactions and batched writes are atomic operations where “either all of the operations succeed, or none of them are applied”. Check 11 does too, because Firestore’s docs warn: “Deleting a document does not delete its subcollections!” If you are still picking an engine, start with Supabase alternatives.

What goes wrong without it

Each failure below is named the way it looks from the outside, with the check that catches it.

One request drops the production database

The data is gone, and the logs show one call to one route. In the 21 third-party AI-built apps I audited in June and July 2026, one had an endpoint where a single web request drops the entire production database, and 9 of the 21 had row-level security gaps. Those apps are a selected set, not a random sample or a rate for all AI-built apps. In that one app, the endpoints were leftover migrate and purge routes. On Supabase, row-level security will not stop a route that runs with a secret key, or the legacy service_role key, because that key’s role “skips every Row Level Security policy you attach”. The database side is in how to harden managed database settings; who may call the route belongs to the authentication checklist.

Two writes half-succeed and the money goes missing

A customer is charged, but the order never shows up, or it shows up with no line items. One cause is a workflow that writes to two tables without a transaction: if the second write fails, the first one stays, and a partial operation like that can leave orders in an inconsistent state. Anything that spans several writes can end half-done this way. Check 4 below is the test. The code pattern is a separate topic: how to wrap multiple writes in a transaction.

The list page runs one query per row

The customer list was quick in the demo and gets slower as the table grows. The page loads the list, then asks the database for each row’s related record one at a time, so a single screen becomes a long chain of round trips. A missing useful index on the columns those queries filter by can make each trip slower as data grows. Checks 2 and 3 test for both. The fix pattern is in the N+1 query problem solution, and reading what Postgres actually did means learning to analyze a Postgres query.

The database went read-only and nobody knows why

Signups start failing at a busy moment, and the error names either the connection limit or a read-only transaction. Those are two different problems. Running out of connections can make the database unavailable while traffic peaks, which is what a pooler in front of Postgres is for. The read-only error has causes of its own, covered in cannot execute INSERT in a read-only transaction. Pool sizing is part of connection pooling for Postgres, and check 5 tests it.

Deleted means gone, including the customer who clicked by mistake

A customer deletes a project by accident and asks support to bring it back. With a hard delete, the app keeps nothing to undo, and recovery falls to a backup or point-in-time recovery, if either is set up. Where the business workflow needs an undo, deleted records have to stay recoverable and out of normal queries. The opposite problem is rows left behind when their parent goes, which pile up and get in the way of exports and reports. Checks 9 and 11 cover both: check 9 rests on what soft delete is, and check 11 is in effect a database cleanup checklist.

The backup exists and has never been restored

The backup job shows green every night, and nobody has ever restored from it. A job can stop working with nobody watching, and only a restore shows whether the backup is usable. GitLab’s own postmortem, published on 10 February 2017, says that during GitLab’s 31 January 2017 outage, when engineers went to look for the pg_dump backups, “they were not there”: the procedure had “failed silently” because it ran PostgreSQL 9.2 against a 9.6 database. To the question of why the procedure was not tested on a regular basis, the postmortem’s answer is that there was no ownership, so “nobody was responsible for testing this procedure.” My reading of the lesson: a backup job nobody owns can fail in silence until the day it is needed, so the restore test in check 7 needs a named owner and a date. The full routine is the database backup checklist for startups, the timed drill belongs to a disaster recovery checklist for SaaS, and running the business while data comes back is a business continuity plan template.

The 14 checks, one by one

Each check says what it is, how to verify it, and which page goes deeper. The numbers match the table above.

1. The schema rejects bad rows on its own

The database should refuse a row that breaks the rules before any app code sees it. That means checking column types, which columns may be null, the foreign keys that keep referential integrity between related tables, uniqueness where duplicates would be wrong, and what each delete cascades to, then correcting what is off. To verify: test rejected invalid records and expected relationship behavior using representative data. In practice, try a missing required value, a duplicate where only one is allowed, and a child row whose parent does not exist. A database review checklist goes table by table.

2. Foreign keys and hot queries are indexed

Postgres does not index a foreign key for you. PostgreSQL’s constraints documentation says “the declaration of a foreign key constraint does not automatically create an index on the referencing columns”, and because a DELETE of a referenced row or an UPDATE of a referenced column “will require a scan of the referencing table”, indexing those columns is “often a good idea”. This check reviews every foreign key and hot query path with execution plans and implements appropriate indexes, not an index on every column. The evidence: retain before-and-after query plans and timings; document the index decisions. Reading a plan is on the query analysis page above.

3. List and detail views run a fixed number of queries

A list page should cost roughly the same number of queries whether it shows a handful of rows or hundreds. The fix swaps per-row lookups for appropriate joins, batching, or prefetching, whichever suits the view. The test: measure database query counts for representative list and detail views. The count should stay flat as the list grows; if it climbs with the rows, the lookup is still inside a loop. The database’s query log or the ORM’s debug output is enough to count them. The fix pattern page linked above walks through it.

4. Multi-step writes are atomic

Related database changes, such as an order and its line items or a credit moved between two accounts, go inside one transaction so they land together or not at all, and any workflow that spans several writes gets the same protection. Verify it: inject a failure between writes and verify atomic rollback or the documented recovery behavior. On Firestore, the transactions and batched writes described in the engine section do this job. The transaction page named above has the code.

5. Connections are pooled and the pool is sized

Serverless and edge functions “open many short-lived connections”, in the words of Supabase’s connection guide, which points them at its shared pooler in transaction mode. This check configures connection pooling, or the platform’s own connection management, and tests it under load. The test: run concurrent requests and record connection usage, errors, and pool configuration. The pooling page named above covers how big the pool should be.

6. Every schema change is a migration in version control

A column added by clicking in a database dashboard leaves nothing in the repository, and untracked changes like that make environments difficult to reproduce. This check moves each schema change into ordered migration files committed to the repository. Watch the tool: Drizzle lets you “push” a schema straight to the database or “generate SQL migration files”, and only the files leave a history; Prisma ORM also changes the database through migrations. To verify: apply the migration sequence to a clean test database and compare the resulting schema. The database migration checklist has the rest.

7. Backups exist and a restore has been done

This check confirms automated backups are running, sets how long they are kept, and does a real restore. Verify it: restore a backup into an isolated environment and check representative data integrity. Representative means records you know should be there, such as your own account, last week’s newest customer and a recent invoice. The backup copy needs protecting as well, at rest and in transit; database encryption covers both. The host table below shows which providers take backups for you and on which plans, and the backup checklist linked above has the full routine.

8. Timestamps are stored in UTC and shown in the user’s zone

Event timestamps are stored in UTC, and the rules for local dates and display times are written down, for example which day a late-evening payment counts toward. To verify, test timestamps across time zones and relevant date boundaries. The boundaries worth trying are midnight, month end and a daylight-saving change in a zone that has one, since billing periods and daily reports start and end there. The time zone handling checklist has the details.

9. Deletion is recoverable where it should be

Where the product needs recovery, a delete becomes a flag instead of a removal, and the flag has to agree with the app’s account-erasure and retention rules, so an erasure request still erases. Verify it: delete and restore a test entity and verify normal queries exclude deleted records. Not every table needs this: I would not bother for a page-view log, and I would for a customer’s projects. The soft delete page above covers the query side.

10. Staging has a seed script, not a copy of production

Staging needs data to be worth testing on, and a copy of production puts real customers’ records in a second place you then have to protect. This check is a repeatable seed script with realistic records that contain nothing sensitive. The test: run the seed process on a clean staging database and exercise core flows. Core means sign-up, creating the app’s main object and paying, if the app charges. Making the data itself is a separate task: generate realistic fake data for staging.

11. Orphaned records are found and cleaned with a dry run

Rows whose parent was deleted, sessions for users who no longer exist, uploads that nothing points to: this check finds abandoned records and broken relationships and removes them with documented safeguards. Verify it: run a dry-run comparison and confirm the cleanup preserves valid linked records. On Firestore, deleted documents leave their subcollections behind, as the engine section notes, so the same hunt applies there. The cleanup checklist above has the queries to find them.

12. A recovery drill has been timed

A drill restores the application and its data into a fresh environment, times the recovery and writes the procedure down, so someone other than its author can repeat it. To verify, record the timed drill, recovered services, integrity checks, and observed recovery limits. I’d measure the elapsed time from the decision to restore until a user can log in and use the app, because that is the wait your customers see. The disaster recovery checklist above lays out the drill step by step.

13. The data model diagram matches the schema

A readable diagram shows the entities, how they relate and which part of the system owns which data, so a technical reviewer or a new engineer can get their bearings quickly. A diagram drawn at the start and never updated misleads more than it helps. Verify it: compare the diagram against the delivered schema and migrations. Where the two disagree, the schema wins and the diagram gets redrawn. The database review checklist covers reading the schema itself.

14. Private files need a signed link; uploads are bounded

Private files are served only through signed, expiring links; uploads have size and type limits, get scanned for malware, and are removed under a retention rule. The test: fetch a private file without a signed link and confirm refusal; upload an oversized and a disallowed file and confirm rejection. Then upload a safe malware-scanner test file and confirm rejection, and verify that expired uploads are removed according to the configured retention rule. Bucket settings belong to an object storage security checklist.

What your host already does, and what it does not

A managed database host can take the backups, encrypt the data at rest and, on the Postgres hosts, run a connection pooler, though on some you switch these on yourself. The restore test, the timed drill, the schema, the migrations and the transaction boundaries stay yours, because the host keeps the backup and only you can prove it comes back.

Defaults checked on 28 September 2026 against Supabase’s backups page, Neon’s restore page, Railway’s backups page and Firestore’s point-in-time recovery page.

The host can take the backups, on the plans its row names and, where the row says so, once you switch them on. It encrypts at rest where its docs say it does, and on the three Postgres hosts it can pool connections. The schema, the migrations, the transaction boundaries, the restore test and the drill are always yours.

Where the sprint stops

The sprint works on the app you already have: its current framework and hosting setup are the starting point, and components are refactored or replaced where the production work requires it. Hosting, the database plan and any backup or point-in-time recovery add-on stay in your own accounts, and any required third-party cost is explained before anything is enabled. Formal third-party certifications and independent audit opinions are separate from these engineering deliverables. Legal advice is a separate service too; the technical data-handling controls themselves are implemented and documented.

Where the sprint does this

Area 4 of the sprint is these 14 checks. Each is implemented and then verified by its own verify line, the ones quoted under each check above, and every result is recorded with its evidence in the production readiness report, where a failure stays visible until it is resolved. The wording of each deliverable is in area 4 of the published scope.

How to verify the whole area in an afternoon

Verifying this area takes 14 tests and, as my working estimate for a small app, about an afternoon: restore a backup into an isolated database, apply the migrations to an empty one, insert a bad row, count the queries on a representative list page, break a write halfway, and time a recovery drill. Record each.

The order below is the one a one-person team can run from start to finish, because the restore and the rebuilt schema feed the checks after them. Each item points back to its check above and says what to write down.

1. 01 Check seven, the restore: load a recent backup into an isolated database, look up the records you know should be there, and note the backup timestamp and how long it took.
2. 02 Check six, the migrations: rebuild the schema from the migration history on a clean, empty database, diff it against production, and keep the diff even when it is empty.
3. 03 Check thirteen, the diagram: hold it against that rebuilt schema and list every table or relationship that appears in one but not the other.
4. 04 Check one, bad rows: try invalid records and an orphan row; each should be refused, and related rows should behave as expected when a parent changes. Note each attempt and the error it got.
5. 05 Check three, query counts: load a representative list view and a detail view with query logging on, and write down the count for each.
6. 06 Check two, the slowest query: capture its plan and timing, change the index, capture both again, and keep the pair with a line on why.
7. 07 Check four, a failure between writes: force an error after the first write of a multi-step workflow and record whether it all rolled back or the documented recovery ran.
8. 08 Check five, concurrent requests: send a burst of simultaneous requests and note connection counts, any errors and the pool settings in force.
9. 09 Check eight, timestamps: create records either side of the date boundaries that matter to you, in more than one time zone, and note where each lands.
10. 10 Check nine, deletion: delete a test entity, confirm normal queries no longer return it, bring it back, and note both results.
11. 11 Check eleven, the cleanup: run it as a dry run, compare what it would remove with the records that still have valid links, and keep the comparison.
12. 12 Check ten, the seed: seed a clean staging database from the script, walk the core flows, and note any that fail.
13. 13 Check fourteen, files: request a private file with no signed link, then try an oversized upload and a disallowed file type, and record each refusal.
14. 14 Check twelve, the drill: time a full recovery into a fresh environment, then note which services came back, which integrity checks passed and what limits you hit.

When every item has a note beside it, this area is done. The other areas follow the same pattern; together they make up the full production readiness checklist.