Data

A table in the per-tenant schema — naming it, picking its tier, writing its policies, and the query and action layer over it.

The model

Tenant isolation is connection-layer: each tenant is its own database with one public schema, and no table carries a tenant id. Authorization is Postgres row-level security, not the app gate. Every exposed table has it enabled, with policies whose role list names who may read and who may write.

Design principles

Map the process onto the existing tables. Three master tables, persons, organizations and products, carry rich columns so a new business process is inserts, not tables. Two roles, admin and user; more is downstream custom work.
An enumeration is a table. A closed set is a lookup row referenced by code, never a check constraint. A reference column names its own type beside the code, so the foreign key discriminates, and every type seeds a not_specified entry.
No booleans for lifecycle. A status_code for state, a JSON metadata column for flags. A boolean is only for a fact that never gains a third state.
A type implies a status, never the reverse. A table that classifies its rows also tracks their lifecycle. Add a type only when something reads it.
Parent and child is one table with a self-referencing parent pointer. Master and detail is a second table named for what the detail is: order lines, file versions, audit entries. Never a generic _items or _details.
A measured column declares its domain as a check, per column, so the violation names the column. A rank is a position, not a measurement, and carries none.
_at is an instant, _time a clock time.

Naming

Tier	Rule	Examples
Module anchor	A plural noun, no prefix. It is the namespace.	`products`, `orders`, `persons`
Every other table	Prefixed with the module or a bucket it owns.	`product_options`, `order_lines`, `leave_requests`
Pure link	`<a>_<b>_links`	`product_collection_links`
Association entity	A subordinate noun, never `_links`.	`organization_persons`
Master and detail	Named for what the row is.	`order_payments`, `file_versions`

A prefix names what a row always has, never an optional foreign key. A noun a tenant would recognise on a sidebar stands bare where one module owns the concept outright. Views are <table>_view, indexes idx_<table>_<column>, constraints keep Postgres's default shape.

Where the DDL lives

The Drizzle schema is the source; the SQL under the migrations folder is generated output. Each schema file is listed explicitly in the Drizzle config, because the folder barrel would make the generator see every table twice, so a new file is added to that list and to the barrel separately.

Stateful DDL, tables, columns, constraints, indexes, policies, is the numbered chain, applied once per database and recorded. Stateless DDL, functions, views, triggers, grants, RPCs, is replayed on every apply and is never a migration; see schema migrations. Generate the next chain entry from the diff, commit the snapshot with it, and never regenerate the baseline. The chain is additive; a deliberate drop declares itself.

After touching SQL or a seed, regenerate the bundled SQL module, because a bundler ships modules, not files on disk.

Column conventions

export const organizations = pgTable(
  "organizations",
  {
    id: uuid()
      .default(sql`uuidv7()`)
      .primaryKey()
      .notNull(),
    parent_id: uuid("parent_id"),
    code: text().default("").notNull(),
    name: text().notNull(),
    type_code: text("type_code").default("not_specified").notNull(),
    type_lookup_type: text("type_lookup_type")
      .generatedAlwaysAs(sql`'organization_types'::text`)
      .notNull(),
    status_code: text("status_code").default("not_specified").notNull(),
    status_lookup_type: text("status_lookup_type")
      .generatedAlwaysAs(sql`'organization_statuses'::text`)
      .notNull(),
    metadata: jsonb(),
    created_by: uuid("created_by"),
    created_at: timestamp("created_at", { withTimezone: true, mode: "string" })
      .defaultNow()
      .notNull(),
    updated_at: timestamp("updated_at", { withTimezone: true, mode: "string" })
      .defaultNow()
      .notNull(),
  },
  (table) => [
    /* indexes, foreignKey(), unique(), pgPolicy(), check() */
  ],
);

Ids are UUID v7 and client-minted ids are accepted; the mobile outbox depends on that.
Every stateful table carries created-by, updated-by, created-at and updated-at. The actor columns are filled by triggers, never trusted from the caller.
A lookup reference is a code with a default beside a generated type column and a composite foreign key into the lookups table.
Hierarchy is a self-referencing parent pointer. A header's child rows cascade.
Money is an integer count of the currency's smallest unit. Never a float, never a literal multiply-by-a-hundred.
Auto-filled codes and slugs need a sequence and a trigger wiring line.

Access tiers and policies

Assign the tier by sensitivity: products public, organizations protected, persons private. Transactions are private. Widen per requirement, never speculatively.

Two policies per table, a read and a write. Copy the pair from a table already on the tier you picked:

pgPolicy("organizations_read", {
  for: "select",
  to: ["authenticated", "reader"],
  using: sql`true`,
}),
pgPolicy("organizations_write", {
  for: "all",
  to: ["authenticated"],
  using: sql`(SELECT is_admin()) OR created_by = (SELECT current_person_id())`,
  withCheck: sql`(SELECT is_admin()) OR created_by = (SELECT current_person_id())`,
}),

Wrap every helper call in a sub-select so the planner runs it once per query, not once per row.
A child of a header carries no owner column; it scopes through an exists-test on its parent, inheriting the parent's visibility. A child written only by a privileged function gets a read policy and no write policy.
The blanket grant to signed-in users means a new server-only table needs an explicit revoke. Default-deny is the backstop; the revoke is the wall.
A new privileged function reachable from a client needs its execute grant, with a note saying which policy it deliberately writes past. One not meant to be reachable is revoked from public.
Every view runs as its caller. A view exposing another person's data uses a definer helper that returns only the safe field, never a widened policy.

Query and action layer

Pick the path by transport and privilege, as data access lays out: the cached read-only role for public and protected server reads, the Data API as the signed-in user for private reads and every session write, a privileged function over the sessionless writer for server-to-server writes.

A cached read:

export async function getProductById(tenantSlug: string, id: string) {
  "use cache";
  cacheTag("products");
  cacheLife("max");

  const db = await getReaderDb(tenantSlug);
  return (await getProduct(db, id)) ?? null;
}

Keep the slug internal: a private cached function taking the slug, and a slug-less wrapper that resolves the tenant and calls it. Never translate inside the scope, and never cache a tenant miss.

A write:

"use server";

export async function createOrganizationAction(data: CreateOrganizationInput) {
  await protect();
  const api = await getDataApi();

  let org: Organization;
  try {
    org = await createOrganization(api, data);
  } catch (error) {
    return err(error);
  }

  updateTag("organizations");
  refresh();
  return ok(org);
}

Verify the session first, return a result rather than throwing, then expire the tag and refresh. A multi-statement write cannot go over the serverless driver; wrap it in a function and expose it as an RPC.

A list* returns a collection, a get* one or null. A bounded reference table is fetched whole; an unbounded one is read by keyset cursor, with a composite index per feed, the id as tie-breaker, and only non-null columns sortable.

New-table checklist

Name follows the tier table; generic nouns qualified.
Schema file added to the Drizzle config's explicit list and the folder barrel.
Id, timestamps, attribution columns, metadata.
Lookup references coded and typed, each type seeded with not_specified.
Row-level security enabled, a read and a write policy for its tier, helpers wrapped.
A server-only table revoked explicitly.
Triggers wired for the timestamp bump, and for code or slug if auto-filled.
A composite (created_at, id) index if it feeds an infinite grid.
Any new privileged function granted or revoked.
A view appended at the end of the schema file if a grid needs resolved labels.
Seeds idempotent.
The bundled SQL regenerated and the db package rebuilt before dependents typecheck.

Traps

Symptom	Cause
The generator errors on duplicate tables	The folder barrel was listed instead of the files.
Policies missing from the generated SQL	Role emission is off in the config, or the role did not exist at apply time.
A signed-out client still gets 403 after a new grant	The Data API cached grants at creation and needs reprovisioning. Policy changes are live; grants are not.
Every infinite feed stops after one block	The Data API's max rows was capped at the block size. Leave it unset.
A SQL change has no effect at runtime	The bundled SQL module was not regenerated.
The serverless driver rejects the write	A multi-statement transaction over HTTP. Move it into a function.
A policy helper makes a query a hundred times slower	It is called per row. Wrap it in a sub-select.

The model

Design principles

Map the process onto the existing tables. Three master tables, persons, organizations and products, carry rich columns so a new business process is inserts, not tables. Two roles, admin and user; more is downstream custom work.
An enumeration is a table. A closed set is a lookup row referenced by code, never a check constraint. A reference column names its own type beside the code, so the foreign key discriminates, and every type seeds a not_specified entry.
No booleans for lifecycle. A status_code for state, a JSON metadata column for flags. A boolean is only for a fact that never gains a third state.
A type implies a status, never the reverse. A table that classifies its rows also tracks their lifecycle. Add a type only when something reads it.
Parent and child is one table with a self-referencing parent pointer. Master and detail is a second table named for what the detail is: order lines, file versions, audit entries. Never a generic _items or _details.
A measured column declares its domain as a check, per column, so the violation names the column. A rank is a position, not a measurement, and carries none.
_at is an instant, _time a clock time.

Naming

Tier	Rule	Examples
Module anchor	A plural noun, no prefix. It is the namespace.	`products`, `orders`, `persons`
Every other table	Prefixed with the module or a bucket it owns.	`product_options`, `order_lines`, `leave_requests`
Pure link	`<a>_<b>_links`	`product_collection_links`
Association entity	A subordinate noun, never `_links`.	`organization_persons`
Master and detail	Named for what the row is.	`order_payments`, `file_versions`

Where the DDL lives

After touching SQL or a seed, regenerate the bundled SQL module, because a bundler ships modules, not files on disk.

Column conventions

export const organizations = pgTable(
  "organizations",
  {
    id: uuid()
      .default(sql`uuidv7()`)
      .primaryKey()
      .notNull(),
    parent_id: uuid("parent_id"),
    code: text().default("").notNull(),
    name: text().notNull(),
    type_code: text("type_code").default("not_specified").notNull(),
    type_lookup_type: text("type_lookup_type")
      .generatedAlwaysAs(sql`'organization_types'::text`)
      .notNull(),
    status_code: text("status_code").default("not_specified").notNull(),
    status_lookup_type: text("status_lookup_type")
      .generatedAlwaysAs(sql`'organization_statuses'::text`)
      .notNull(),
    metadata: jsonb(),
    created_by: uuid("created_by"),
    created_at: timestamp("created_at", { withTimezone: true, mode: "string" })
      .defaultNow()
      .notNull(),
    updated_at: timestamp("updated_at", { withTimezone: true, mode: "string" })
      .defaultNow()
      .notNull(),
  },
  (table) => [
    /* indexes, foreignKey(), unique(), pgPolicy(), check() */
  ],
);

Ids are UUID v7 and client-minted ids are accepted; the mobile outbox depends on that.
Every stateful table carries created-by, updated-by, created-at and updated-at. The actor columns are filled by triggers, never trusted from the caller.
A lookup reference is a code with a default beside a generated type column and a composite foreign key into the lookups table.
Hierarchy is a self-referencing parent pointer. A header's child rows cascade.
Money is an integer count of the currency's smallest unit. Never a float, never a literal multiply-by-a-hundred.
Auto-filled codes and slugs need a sequence and a trigger wiring line.

Access tiers and policies

Assign the tier by sensitivity: products public, organizations protected, persons private. Transactions are private. Widen per requirement, never speculatively.

Two policies per table, a read and a write. Copy the pair from a table already on the tier you picked:

pgPolicy("organizations_read", {
  for: "select",
  to: ["authenticated", "reader"],
  using: sql`true`,
}),
pgPolicy("organizations_write", {
  for: "all",
  to: ["authenticated"],
  using: sql`(SELECT is_admin()) OR created_by = (SELECT current_person_id())`,
  withCheck: sql`(SELECT is_admin()) OR created_by = (SELECT current_person_id())`,
}),

Wrap every helper call in a sub-select so the planner runs it once per query, not once per row.
A child of a header carries no owner column; it scopes through an exists-test on its parent, inheriting the parent's visibility. A child written only by a privileged function gets a read policy and no write policy.
The blanket grant to signed-in users means a new server-only table needs an explicit revoke. Default-deny is the backstop; the revoke is the wall.
A new privileged function reachable from a client needs its execute grant, with a note saying which policy it deliberately writes past. One not meant to be reachable is revoked from public.
Every view runs as its caller. A view exposing another person's data uses a definer helper that returns only the safe field, never a widened policy.

Query and action layer

A cached read:

export async function getProductById(tenantSlug: string, id: string) {
  "use cache";
  cacheTag("products");
  cacheLife("max");

  const db = await getReaderDb(tenantSlug);
  return (await getProduct(db, id)) ?? null;
}

Keep the slug internal: a private cached function taking the slug, and a slug-less wrapper that resolves the tenant and calls it. Never translate inside the scope, and never cache a tenant miss.

A write:

"use server";

export async function createOrganizationAction(data: CreateOrganizationInput) {
  await protect();
  const api = await getDataApi();

  let org: Organization;
  try {
    org = await createOrganization(api, data);
  } catch (error) {
    return err(error);
  }

  updateTag("organizations");
  refresh();
  return ok(org);
}

New-table checklist

Name follows the tier table; generic nouns qualified.
Schema file added to the Drizzle config's explicit list and the folder barrel.
Id, timestamps, attribution columns, metadata.
Lookup references coded and typed, each type seeded with not_specified.
Row-level security enabled, a read and a write policy for its tier, helpers wrapped.
A server-only table revoked explicitly.
Triggers wired for the timestamp bump, and for code or slug if auto-filled.
A composite (created_at, id) index if it feeds an infinite grid.
Any new privileged function granted or revoked.
A view appended at the end of the schema file if a grid needs resolved labels.
Seeds idempotent.
The bundled SQL regenerated and the db package rebuilt before dependents typecheck.

Traps

Symptom	Cause
The generator errors on duplicate tables	The folder barrel was listed instead of the files.
Policies missing from the generated SQL	Role emission is off in the config, or the role did not exist at apply time.
A signed-out client still gets 403 after a new grant	The Data API cached grants at creation and needs reprovisioning. Policy changes are live; grants are not.
Every infinite feed stops after one block	The Data API's max rows was capped at the block size. Leave it unset.
A SQL change has no effect at runtime	The bundled SQL module was not regenerated.
The serverless driver rejects the write	A multi-statement transaction over HTTP. Move it into a function.
A policy helper makes a query a hundred times slower	It is called per row. Wrap it in a sub-select.

The model

Design principles

Naming

Where the DDL lives

Column conventions

Access tiers and policies

Query and action layer

New-table checklist

Traps

On this page

Data

The model

Design principles

Naming

Where the DDL lives

Column conventions

Access tiers and policies

Query and action layer

New-table checklist

Traps

On this page