Implementation worksheet · 7 min read
A Schema Review Worksheet for a Generated SaaS Database
Review a generated schema table by table against a fixed list before customer data goes in. Every tenant-owned table carries the tenant key and every query filters by it; relationships are declared as foreign keys with a delete rule someone chose; the rules from the brief exist as unique, not-null and check constraints rather than only in app code; money uses an exact type; moments in time are stored with their time zone; the columns that filters and joins use are indexed; deletion works one documented way per table; and personal-data columns are listed. The review is cheapest now: once customers' data is in the tables, every fix becomes a migration.
Scope: the actor is a founder or reviewer who can read a table definition, looking at the schema an AI builder generated for a multi-tenant SaaS app. The starting state is an app running on synthetic data with no customer data yet. The boundary is tables, columns, constraints and indexes; application code is checked only where a rule lives there instead of in the database. The database details below come from the PostgreSQL documentation (version 18 as of September 2026); if the app uses a different database, check the equivalent behaviour in its own documentation. The outcome is a finding per check, each with a fix and a decision on whether it blocks launch.
Put it into practice
1. Get the schema as text
Export or ask for the table definitions: columns, types, constraints and indexes. A diagram isn't enough, because what matters is which rules are declared. If the schema is only visible through a UI, copy it into the worksheet table by table.
2. Check the tenant key on every tenant-owned table
Most tables in a SaaS app belong to one customer account. Each of those needs the account or workspace id, marked not null. A child table that inherits tenancy from its parent (invoice lines through invoices) is acceptable if every query joins through the parent; write down which tables rely on that. The schema can't show whether queries filter by tenant, so pair this with the two-account permission test.
3. Turn the brief's rules into constraints
Every 'must', 'only one' and 'can't' in the brief should exist as a constraint where the database can enforce it: unique, not null, check or a foreign key. A rule enforced only in app code holds until an import, a background job or a second code path skips it. Two PostgreSQL details: a unique constraint treats nulls as distinct by default, so duplicates with a null in a constrained column are allowed unless the column is not null or NULLS NOT DISTINCT is added; and to treat values that differ only in case as duplicates, make the uniqueness apply to lower(column), as the documentation's expression-index example shows.
4. Give every foreign key a delete rule on purpose
In PostgreSQL the default, NO ACTION, makes deleting a parent that still has children fail with an error. CASCADE deletes the children; SET NULL keeps them with an empty reference. Choose per relationship: deleting a workspace should probably take its records with it, while deleting a member who created invoices probably shouldn't delete the invoices.
5. Check money and time types
PostgreSQL's documentation recommends the numeric type for monetary amounts and describes real and double precision as inexact; storing integer minor units (cents) is another exact option. For time, plain timestamp means timestamp without time zone; for moments such as created_at or paid_at, timestamptz is stored internally in UTC. Where a rule depends on local time (a 09:00 booking at the venue), also store the time zone name.
6. Index what the app filters and joins on
PostgreSQL creates an index automatically for a primary key or unique constraint, but not for the referencing columns of a foreign key; its documentation says indexing them is often a good idea. Check the tenant key and foreign keys on the tables that will grow largest, and the columns list views filter or sort by.
7. Settle the deletion model and list personal data
Hard delete, soft delete (a deleted_at column), or each for different tables: write down which. Soft delete interacts with uniqueness: a deleted member's email keeps blocking a re-invite unless uniqueness is a partial unique index that only covers rows not deleted, which PostgreSQL supports. Then list every column that holds personal data, so you can answer 'what do we hold about this person?' from the schema rather than by searching.
8. Worked example (illustrative, synthetic data)
A generated schema for a fictional client-invoicing SaaS with workspaces, members, clients, invoices, invoice_lines and payments. Findings from a synthetic review: clients.workspace_id nullable (blocks launch); members unique on email alone, so one person can't join two workspaces and case variants slip through (blocks launch); invoices.due_date nullable although the brief requires it (blocks launch); invoice_lines.amount stored as double precision (blocks launch); payments.paid_at stored without time zone (blocks launch, since reports cover several time zones); invoice status as free text (fix soon); every foreign key left at the default delete rule (fix soon); no index on invoices.client_id (fix soon); clients soft-deleted but members hard-deleted with nothing written down (document it); clients.phone and notes missing from the personal-data list (fix soon). Five blockers, all one-line changes while the data is synthetic.
Schema review worksheet
Copy this structure into your review document and record your observed result for each row.
| Check | Pass condition | Worked example finding (synthetic) | Fix | Blocks launch? |
|---|---|---|---|---|
| Tenant key | Every tenant-owned table has a not-null workspace id, or reaches one through a parent every query joins | clients.workspace_id is nullable | Make it not null | Yes |
| Tenant filtering | Every list and detail query filters by tenant | Can't be seen in the schema | Run the two-account permission test | Yes, if the test fails |
| Uniqueness rules | Each 'only one' rule is a unique constraint or unique index | members unique on email alone | Unique index on workspace id plus lower(email) | Yes |
| Nulls in unique columns | Constrained columns are not null, or NULLS NOT DISTINCT is set deliberately | None found | No change | No |
| Required fields | Fields the brief requires are not null | invoices.due_date is nullable | Not null, with a rule for existing rows | Yes |
| Allowed values | Statuses and ranges enforced by check constraints | Invoice status is free text | Check constraint listing the five statuses | No |
| Delete rules | Each foreign key has a chosen ON DELETE action | All left at the default | Cascade from workspace; restrict from member to invoice | No |
| Money | numeric, or integer minor units | invoice_lines.amount is double precision | numeric(12,2) or integer cents | Yes |
| Time | Moments stored as timestamptz; local-time rules keep a zone name | payments.paid_at has no time zone | timestamptz, plus the workspace's time zone name | Yes |
| Indexes | Tenant key, foreign keys and filter columns indexed on large tables | No index on invoices.client_id | Add the index | No |
| Deletion model | One documented approach per table | clients soft-deleted, members hard-deleted, undocumented | Document it; partial unique index on clients | No |
| Personal data | Every personal-data column listed | clients.phone and notes not listed | Add them to the inventory | No |
A failure worth checking
The rule that lives only in the signup form. The brief says one member per email per workspace; the builder implements it as a check in the form, and the schema has no unique constraint. Signup works. A CSV import written later inserts members directly, creates duplicates the form would have refused, and the permission logic that assumed one row per email starts matching the wrong member. Constraints hold for every code path; an app-level check holds for the path it was written in. The counterexample: not every rule belongs in the database. 'An invoice can't be edited after it is sent' depends on role and state in ways a check constraint expresses badly, and forcing it into the schema makes the schema brittle. Put invariants in the database and workflow rules in code, and write down which is which.
Common questions
Do I need to read SQL to use this?
Enough to read a table definition: column names, types, and the words NOT NULL, UNIQUE, REFERENCES and CHECK. If the builder can describe its schema in plain language, ask it to list those per table, fill the worksheet from that, then spot-check a few rows against the actual definitions.
Should findings be fixed through the builder or by hand?
Through whatever route the app's schema changes normally take, so the schema and the code that uses it stay in step. Once real data exists each fix is a migration, and the order of schema and code changes starts to matter.
What about row-level security?
If the app runs on PostgreSQL and uses it, check that each tenant table has row security enabled and a policy. PostgreSQL's documentation notes that tables have no policies by default, that enabling row security with no policy means no rows are visible, and that a table's owner is typically not subject to the policies, so an app connecting as the owner isn't protected by them.
Basis and scope
This is a proposed implementation method using illustrative examples, not a measured benchmark or a customer case study. Prepared with AI assistance. Validate product-specific behavior against current documentation and your own test environment.