Postgres Schema Design for an Enterprise AI Platform: 120+ Tables, No ORM, Verified Migrations

Connect, the AI platform we built on top of Syncify, keeps its system of record in one Postgres database with 120+ tables. There is no ORM. Every query is SQL written by hand and run through the pg driver, and every schema change is a numbered SQL file that ships with the release. The server checks that the database matches those files exactly before it opens a socket, and it never migrates itself.

That sounds strict, and it is on purpose. A multi-tenant AI platform for construction holds things people will argue about later: who approved a baseline schedule, which version a client saw, what an agent changed and on whose behalf. This article walks through how we laid out the schema, why Postgres holds the record while a document database holds vectors and agent state, and the migration discipline that keeps a fast-moving codebase from drifting away from its own database.

The shape of the schema

The 120+ tables fall into a handful of groups. Reading them in this order is the fastest way to understand the product.

A few of these deserve a closer look, because they show the patterns we reuse everywhere.

Tenancy lives in the keys, not just the WHERE clause

Most tenant tables have a composite unique key on (workspace_id, id), and child tables point at that pair instead of at the bare id. There are around 45 of those composite foreign keys in the folded schema. The effect is that a segment cannot reference a project in a different workspace, and a file version cannot claim a sync run from another tenant. The database refuses the row.

Application code still scopes every query by workspace, and authorization runs through one server-side chokepoint (covered in the RBAC article). The composite keys are the backstop for the day someone forgets. When we looked at row-level security for one table, we chose not to add it: the composite key already made a cross-workspace write impossible, so RLS would have guarded something that was already structural.

Program, Project, Segment

The domain hierarchy is deliberately shallow. A Program groups Projects and holds the commercial description of a body of work (client, stage, value, dates). A Project is one imported schedule source and the thing a person is given access to. A Segment is a named tag that slices a Project, such as one building on a two-building job or a system like utilities.

The schema encodes the decision that only the Project grants access. Programs have no members table. Segments own nothing, and every record is stored under its Project with the tag attached. An earlier design had sub-projects as containers, and a later migration removed them. Keeping access on a single level means one join answers "can this person see this", and that join is the one the authorization layer already makes.

Append-only snapshots plus a projection

Schedules arrive from Syncify as columnar snapshots. We store each snapshot's bytes verbatim in blob storage and record a row in schedule_snapshots with the schedule id, revision, blob name, size and etag. Those rows are never updated. Detail, compare and history views decode the stored bytes on read.

For set-wide questions ("which tasks across this portfolio slipped more than ten days") decoding every blob is too slow, so a projection step writes tasks and dependencies into ordinary tables after each sync. The projection can always be rebuilt from the snapshots. The snapshots can never be rebuilt from the projection. That asymmetry is what lets us change the projection's shape without fear.

Snapshot retention is the next piece of this design. Today every snapshot blob is kept, and a pruning policy is planned.

Revisions are rows

Baseline schedules, project updates and notes all follow the same pattern: a parent row with a pointer to its current state, and a child table of numbered revisions with a unique key on (parent_id, revision). Approving a baseline schedule records the approved revision, the exact export, the review assessments, the actor and the timestamp. A trigger blocks any later update that would change an approved revision's content:

-- illustrative, not the production trigger
CREATE FUNCTION freeze_approved() RETURNS trigger AS $$
BEGIN
  IF OLD.approval IS NOT NULL
     AND (NEW.content IS DISTINCT FROM OLD.content
          OR NEW.approval IS DISTINCT FROM OLD.approval) THEN
    RAISE EXCEPTION 'approved revisions are immutable';
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

Putting that rule in the database means no code path, migration script or support query can quietly rewrite what a client signed off on.

Audit written by triggers

The entity_history table is filled by triggers on projects, programs, files, notes, schedules and chat. Each trigger computes a field-level delta, resolves names at write time (so the history still reads correctly after a project is renamed or deleted), and stamps the actor. The actor comes from a transaction-local setting: the shared transaction helper calls set_config('connect.actor_id', ..., true) at the start of a write, and the trigger reads it. No actor means the row is labelled as a system or integration change.

A BEFORE UPDATE OR DELETE trigger on the history table itself raises an exception. History is append-only at the database level, not by convention. Alongside it, access_changes records who changed permissions and when, and the agent ledger, agent_events, records tokens, cost, duration and success per agent turn, which feeds cost reporting in the executive admin portal.

A read-only role for generated apps

Connect's app builder lets users create dashboards that run declared queries. Those queries do not touch base tables. They run inside a transaction that switches to a NOLOGIN role with SET LOCAL ROLE, and that role can read only a small set of views in an app schema. The views are marked security_barrier and filter on the current workspace and project list, which the runtime binds as session settings before the query runs. Default privileges revoke SELECT on new tables from that role, so widening what apps can see is always a deliberate grant.

Direct SQL, no ORM

The project rule is one line: direct SQL and no ORM. Each capability has a postgres-store.ts that owns its tables and exposes typed methods. Nothing else reads those tables.

We chose this for three reasons.

1. The interesting queries are not CRUD. The job-claim query in our worker uses a CTE, FOR UPDATE OF ... SKIP LOCKED, two NOT EXISTS subqueries and an UPDATE ... FROM. Enqueueing relies on INSERT ... SELECT ... ON CONFLICT DO NOTHING against a partial unique index. An ORM either cannot express these or makes them harder to read than the SQL.
2. Constraints are the design. Partial unique indexes, CHECK constraints, composite foreign keys and triggers carry real rules. When the schema is the source of truth, SQL migrations are the natural way to write it, and an ORM's model file becomes a second copy that can disagree.
3. Reviewability. A reviewer can paste any query into psql and run EXPLAIN. There is no generated SQL to reverse-engineer.

The cost is boilerplate: hand-written row types, manual mapping, and no automatic relation loading. We accept it because the store layer is thin and each capability's store is small.

Postgres for the record, DocumentDB for vectors and documents

Connect also runs Azure DocumentDB (Cosmos DB for MongoDB vCore). The split is simple to state: if it needs a transaction, a foreign key or an audit trail, it goes in Postgres. If it is a large, loosely structured document or an embedding, it goes in DocumentDB.

DocumentDB still stamps the workspace id on every document, re-applied server-side after the caller's filter, so a misrouted write is detectable. The server provisions per-workspace databases in a background sweep after it starts listening, because blocking boot on a slow document cluster would take down an API that mostly does not need it.

The retrieval side of this is covered in the RAG pipeline article.

Migrations: per-slice files, verified at boot, never self-applied

The rules

• Each change ships as migrations/NNN_description.sql, written as part of the vertical slice that needs it. New work numbers above the highest prefix on disk, and an architecture test enforces a unique three-digit prefix.
• schema_migrations records each applied file by full filename. Renaming a file makes it look unapplied. Editing an applied file is worse, because nothing complains while the database silently diverges, so applied files are never edited.
• The runner takes a session advisory lock, checks the ledger, and applies each pending file in its own transaction along with its ledger row.
• The server, the worker and every other process call a schema check on boot. It compares the set of shipped files to the set of applied versions. Missing files fail with "run migrate." Applied files the build does not ship fail too, because that means either an older build is being started against a newer database or the database predates a squash.

Why the server refuses to migrate itself

If two instances start at once and both try to migrate, you get races. More importantly, a migration is a deploy step with its own failure mode, and it should fail loudly in the deploy script, not halfway through a process restart. Our deploy runs migrations once, after installing the new build and before restarting services. If a migration fails, the deploy checks whether the database still matches the previous build and only then rolls back. The deployment article covers that sequence.

The same check guards local development. Running the app directly after pulling a new migration fails with a message telling you to migrate. The dev command brings up local services and migrates before it starts anything.

Squashing history, carefully

Over time the migration count grew past a hundred. We squashed twice into a single baseline file. Both squashes were safe only because nothing had been deployed to a database that needed carrying forward, and the project rules say plainly that this is not a licence to keep squashing.

Squashing creates a real problem: an existing database's ledger names files that no longer exist. Both the migrate command and the boot check detect this by looking for applied versions the build does not ship, not by the absence of a baseline row, and the error names both possible causes. A CLI command re-stamps a fully migrated pre-squash database onto the baseline without touching its schema. It refuses any database that stopped short of the last pre-squash migration, because that one genuinely has a different shape.

A db/schema-history.md file records which old migration created which table, and which commits still hold the squashed files, because the numbering restarted more than once and a bare number is ambiguous.

A folded schema for humans

Migration files answer "how did we get here." Reviewers usually want "what is the shape now." So we keep db/schema.sql: every table once, in full, in foreign-key dependency order, with no ALTERs patching earlier declarations. It is read-only. The runner never applies it.

It is generated, not hand-written. A shell script dumps tables, columns, constraints and indexes from a fully migrated database's catalog. A Python script topologically sorts tables by foreign key and emits the file, deferring the few pointers that close cycles (a file's current version, a schedule's approved version, a job walk's latest report) as ALTERs after both tables exist.

Then a verifier builds one database from the migrations and another from the folded file, and compares them as sorted sets of facts: tables, columns with types, defaults and nullability, constraints, indexes, views, functions, triggers and grants. A text diff of pg_dump would not work, because folding a column into its CREATE TABLE changes its physical position. The verifier also refuses to report success if it collected fewer than a plausible minimum of facts, because two empty result sets would otherwise look identical. Triggers are compared explicitly, since one of them carries a security property, and a fold that dropped it would look equivalent while shipping a database with the guard missing.

Why this matters if you're building something similar

• Put tenant ids in your foreign keys. A composite (tenant_id, id) key turns cross-tenant references into errors the database raises, not bugs you find in an audit.
• Make immutable things immutable in the database. Approved versions and history tables are protected by triggers, not code review.
• Stamp the actor per transaction. A transaction-local setting read by audit triggers gives every write an author without threading user ids through every function.
• Keep derived tables rebuildable. Store raw inputs append-only and project from them. You can change a projection; you cannot recover lost raw data.
• Never let the app migrate itself. Verify on boot, migrate in the deploy, and track migrations by filename so a rename or an edit is caught.
• Generate a folded schema and prove it. Reviewers need the current shape. Generate it from the catalog and verify it semantically, with a floor so the check cannot pass on nothing.

Where to go next

• How we built Connect: architecture of an enterprise AI platform
• Multi-tenant RBAC through a single authorization chokepoint
• Leased background jobs with a Postgres worker
• Tracing LLM agents in a Postgres ledger
• Deploying a Node monolith to VMs with CI and rollback

Planning a platform with this kind of data discipline? Scope your build.