Database Standards

"The schema is the contract. Types are generated, never guessed. The database enforces what the application assumes."

The Problem

There's a special kind of bug that only appears in production, weeks after the code that caused it was deployed. A developer adds a discount_cents column to the orders table. They update the migration file, run it, and modify the two components they know about. The PR looks clean. Tests pass. It ships.

But there was a third component — a billing report — that constructs its own Order interface by hand. That interface doesn't have discount_cents. The report still compiles, still runs, and still generates numbers. The numbers are just wrong. Every billing report for the next three weeks overstates revenue by the discount amount.

This is what happens when types and schema drift apart. It's what happens when migrations are hand-written instead of generated. It's what happens when the database is treated as a dumb store instead of the source of truth.

The drift is insidious because it's invisible. TypeScript gives you a false sense of safety — your hand-written Order interface says the code is correct. But the interface is lying. It describes the schema as it was last month, not as it is today.

And the migration problem compounds. Hand-written migrations contain subtle errors: a missing IF NOT EXISTS, a column that's NOT NULL without a default (breaking for existing rows), an index defined with slightly different syntax than the rest of the codebase. Each migration is a snowflake. Reviewing them means reading raw SQL and mentally diffing it against the current schema.

The Principle

The database schema is the single source of truth. Everything flows from it: types, migrations, validation. Never the other way around.

We define the schema in SQL files. We generate migrations by diffing the schema against the live database. We generate TypeScript types from the schema. If the schema changes, the types change. If the types change, the compiler tells every file in the codebase what broke.

This workflow has three properties that hand-written approaches lack:

1. Migrations are always correct — they're computed diffs, not human guesses.
2. Types are always current — they're generated output, not manual transcriptions.
3. Reviews are meaningful — reviewers see the schema change and the generated diff, not hand-rolled SQL.

The Pattern

The schema-first workflow

Schema files live in a dedicated directory. They define the desired state of each table, including indexes, constraints, and RLS policies. Migrations are generated by diffing this desired state against the current database.

# 1. Edit the schema file
# schemas/orders.sql

# 2. Generate a migration from the diff
supabase db diff -f add_discount_column --linked

# 3. Review the generated migration
# migrations/20260315_add_discount_column.sql

# 4. Apply the migration
bun run db:push

# 5. Regenerate TypeScript types
bun run db:types

# 6. Commit schema + migration + types together
git add schemas/ migrations/ database.types.ts
git commit -m "feat(db): add discount_cents to orders"

Step 5 is non-negotiable. Schema, migration, and types ship as a unit. If you push a migration without regenerating types, you've introduced exactly the drift this workflow exists to prevent.

SQL format standards

Every schema file follows the same conventions. This makes diffs clean and reviews fast.

CREATE TABLE IF NOT EXISTS "public"."orders" (
  "id" text NOT NULL PRIMARY KEY,
  "organization_id" text NOT NULL,
  "title" text NOT NULL,
  "status" text NOT NULL DEFAULT 'pending',
  "total_cents" integer NOT NULL DEFAULT 0,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS orders_org_id_idx
  ON "public"."orders" ("organization_id");

CREATE INDEX IF NOT EXISTS orders_created_at_idx
  ON "public"."orders" ("created_at");

ALTER TABLE "public"."orders" ENABLE ROW LEVEL SECURITY;

CREATE POLICY "org_read" ON "public"."orders"
  FOR SELECT
  TO authenticated
  USING (organization_id = (auth.jwt() ->> 'org_id')::text);

CREATE POLICY "service_role_all" ON "public"."orders"
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

The rules: uppercase keywords, quoted identifiers, IF NOT EXISTS for idempotency, schema prefix on every table, RLS enabled on every table that holds user data.

Generated types drive everything

After running type generation, the output becomes the type system for your entire data layer.

// database.types.ts (GENERATED — never edit by hand)
export type Database = {
  public: {
    Tables: {
      orders: {
        Row: {
          id: string;
          organization_id: string;
          title: string;
          status: string;
          total_cents: number;
          created_at: string;
          updated_at: string;
        };
        Insert: {
          id?: string;
          organization_id: string;
          title: string;
          status?: string;
          total_cents?: number;
          created_at?: string;
          updated_at?: string;
        };
        Update: {
          id?: string;
          organization_id?: string;
          title?: string;
          status?: string;
          total_cents?: number;
          created_at?: string;
          updated_at?: string;
        };
      };
    };
  };
};

Repositories import Row, Insert, and Update from this file. Server Actions use these types for their parameters. If a column is renamed or removed, every consumer gets a compile-time error. No grep required.

Safe migration patterns

Not every schema change is safe to apply in a single step. Renaming a column, for instance, will break every query that references the old name the moment the migration runs — even if the new code hasn't been deployed yet.

-- DANGEROUS: breaks production instantly
ALTER TABLE "public"."orders" RENAME COLUMN "name" TO "title";

-- SAFE: three-step migration
-- Step 1: Add the new column
ALTER TABLE "public"."orders" ADD COLUMN "title" text;

-- Step 2: Deploy code that writes to both columns, reads from new
-- Backfill existing rows: UPDATE orders SET title = name;

-- Step 3: Drop the old column (after all code references are updated)
ALTER TABLE "public"."orders" DROP COLUMN "name";

The principle: every migration must be safe to run while the previous version of the code is still serving traffic.

RLS on every table

Every table that holds user or organization data gets Row Level Security policies. This is the database enforcing what the application assumes — that a user can only see their own organization's data.

-- Organization isolation: the authenticated user's JWT
-- contains org_id, and the policy filters by it
CREATE POLICY "org_isolation" ON "public"."orders"
  FOR ALL
  USING (organization_id = (auth.jwt() ->> 'org_id')::text);

The new table checklist

When creating a new table, every step matters:

1. Create the schema file with proper SQL format
2. Generate the migration with db diff
3. Enable RLS on the table
4. Add organization isolation policy
5. Regenerate TypeScript types
6. Create a repository file for the new entity
7. Test locally with a database reset
8. Commit schema, migration, types, and repository together

The Business Case

• Zero schema-code drift. Generated types guarantee that the codebase and the database agree on every column, every type, every constraint. The compiler catches what code review misses.
• Reviewable migrations. Generated diffs show exactly what changed. Reviewers don't need to mentally reconstruct the schema from raw SQL — they see the before and after.
• Tenant isolation by default. RLS policies on every table mean that a single missed WHERE organization_id = ? clause can't leak data across tenants. The database enforces the boundary regardless of application bugs.

Try It

Install the Modh Playbook skills to enforce this pattern automatically:

# Add to your project
git submodule add https://github.com/modh-labs/playbook .agents/modh-playbook
./.agents/modh-playbook/install.sh