# Data (/product/building/data)



## 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](/product/infrastructure/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

```ts
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:

```ts
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](/product/infrastructure/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:

```ts
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:

```ts
"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.                                                            |