Tables

Each table lives in its own file under {project}-db/src/schemas/, where {project} is your workspace name. Drizzle Kit reads the exported tables when it generates migrations, and Drizzle uses the same exported table objects when you write queries.

The example below is a complete table definition. Every helper it uses (table, primaryId, authUserId, auditColumns, and the owner policies) is documented under Schema helpers further down.

import {
  auditColumns,
  authenticatedOwnerInsertPolicy,
  authenticatedOwnerSelectPolicy,
  authUserId,
  primaryId,
  table,
} from "@zeno-lib/db/schema"
import { text } from "drizzle-orm/pg-core"

export const posts = table(
  "posts",
  {
    id: primaryId("uuid"),
    title: text().notNull(),
    // Supabase owns auth.users; this reference links your table to the signed-in user.
    // `notNull` because the owner policies below key on it.
    userId: authUserId({ notNull: true }),
    // created_at, updated_at, created_by, and updated_by, from @zeno-lib/db/schema.
    ...auditColumns(),
  },
  (t) => [
    authenticatedOwnerSelectPolicy("posts_owner_select", t.userId),
    authenticatedOwnerInsertPolicy("posts_owner_insert", t.userId),
  ]
)

Re-export every table from a single {project}-db/src/schema.ts barrel. That barrel is what the Drizzle Kit config reads, what your queries import to reference tables, and what feeds defineRelations(...) for the relational query API when querying:

export * from "./schemas/posts"
// export * from "./schemas/comments"

The TypeScript table is the source of truth for migrations. Drizzle infers query types from the same object, common columns are helpers you call instead of redeclaring, and you can derive validation schemas from the table later. For that next layer, see Schemas.

Schema helpersCopy Anchor Link

Everything below is exported from @zeno-lib/db/schema, and none of it is required. When you need something these helpers don't cover, define tables, columns, and RLS policies with Drizzle's own pg-core builders. The module gathers the common cases (table builders, reusable columns, RLS policies, and curated re-exports from Drizzle and Supabase) behind one Zeno-owned entrypoint, so you don't import them from several packages.

Anatomy of a tableCopy Anchor Link

Every helper goes in one of three places in a table definition. Each helper below is tagged with the slot it belongs to.

export const posts = table(   // ① table builder: wraps the table, toggles RLS
  "posts",
  {                           // ② columns map: primary key, columns, column builders
    id: primaryId("uuid"),
    userId: authUserId({ notNull: true }),
    ...auditColumns(),
  },
  (t) => [                    // ③ extra callback: indexes, constraints, RLS policies
    authenticatedOwnerSelectPolicy("posts_owner_select", t.userId),
  ]
)

• ① table builder: wraps the columns and the extra callback.
• ② columns map: spread a group (...auditColumns()) or assign to a key (userId: authUserId({ notNull: true })).
• ③ extra callback: receives the built columns t and returns indexes, constraints, and RLS policies.

Each entry below shows the Drizzle it expands to and the SQL that Drizzle Kit generates from it.

Table buildersCopy Anchor Link

Slot ①: wraps the whole table. Both map camelCase TypeScript keys to snake_case database identifiers, so userId becomes user_id.

table(name, columns, extra?) builds a Postgres table with RLS enabled. Use it for application-owned tables:

table("posts", { /* columns */ }, (t) => [ /* policies */ ])

CREATE TABLE "posts" ( /* columns */ );
ALTER TABLE "posts" ENABLE ROW LEVEL SECURITY;

unsecureTable(name, columns, extra?) is the same builder without the ENABLE ROW LEVEL SECURITY line. Use it only for intentionally unprotected tables such as seed or reference data.

schema(name) builds a non-public schema that gives you the same two builders, with the same casing and the same RLS default:

const billing = schema("billing")

billing.table("invoices", { /* columns */ })        // RLS enabled
billing.unsecureTable("rates", { /* columns */ })   // no RLS

CREATE SCHEMA "billing";
CREATE TABLE "billing"."invoices" ( /* columns */ );
ALTER TABLE "billing"."invoices" ENABLE ROW LEVEL SECURITY;
CREATE TABLE "billing"."rates" ( /* columns */ );

It is otherwise Drizzle's pgSchema, so existing(), enum(), sequence(), view() and materializedView() all work as they do there. Use Drizzle's pgSchema only when you want a schema whose column names come verbatim from their TypeScript keys.

Primary keysCopy Anchor Link

Slot ②: assign to a key in the columns map. primaryId(kind) covers the three ways a primary key gets its value. Each kind emits the column Supabase itself creates for that choice, so a table you started in the dashboard and a table you started here describe the same thing. The column is named id.

primaryId("sequential") is an auto-incrementing key. It is what the dashboard gives a new table when you accept its defaults, so it is also what you get from a bare primaryId():

id: primaryId("sequential")

"id" bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (
  sequence name "posts_id_seq" INCREMENT BY 1
  MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1
)

It uses bigint rather than integer because integer stops at 2.1 billion rows, and by default rather than always because always refuses ids you supply yourself, which breaks seeds and upserts.

primaryId("uuid") is a random UUID key, the dashboard's UUID option:

id: primaryId("uuid")

"id" uuid PRIMARY KEY DEFAULT gen_random_uuid()

primaryId("assigned") is a key your application supplies, such as a human-readable invoice number. It has no default and no identity, so you always pass a value:

id: primaryId("assigned")

"id" varchar PRIMARY KEY

Configuring a primary keyCopy Anchor Link

primaryId takes the kind and nothing else. To rename the column or change how the value is generated, call the helper behind the kind and pass options.

uuidPrimaryId({ defaultRandom: false }) skips the gen_random_uuid() default, for a table whose id comes from somewhere else. A public table mirroring auth.users is the usual case: the row's id is the auth user's id, so a random one would be wrong. Dropping the default also makes id required when you insert, which is what you want here.

id: uuidPrimaryId({ defaultRandom: false }).references(() => authUsers.id, {
  onDelete: "cascade",
  onUpdate: "cascade",
})

"id" uuid PRIMARY KEY
-- foreign key emitted as a separate statement:
ALTER TABLE "profiles" ADD CONSTRAINT "profiles_id_users_id_fkey"
  FOREIGN KEY ("id") REFERENCES "auth"."users"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

sequentialPrimaryId() takes one option for each setting of the identity column it builds:

mode defaults to "number" because PostgREST serialises to JSON numbers, so with "bigint" your Drizzle type would no longer match the types Supabase generates. It applies to bigint only, and passing it alongside type: "integer" is a type error.

assignedPrimaryId({ length }) bounds the column:

id: assignedPrimaryId({ length: 32 }) // "id" varchar(32) PRIMARY KEY

All three take name when the column isn't called id.

ColumnsCopy Anchor Link

Slot ②: in the columns map. These helpers create common columns so you don't redeclare them on every table. Each one is a function you call at each use site, and they come in two shapes.

Ready-made columns have fixed database names. Spread a group into your column map (...auditColumns()), or assign a single column to a key. Every call returns fresh Drizzle column builders, so customising a column on one table can never reach another table built from the same helper.

timestamps() adds createdAt and updatedAt:

...timestamps()
// createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow()
// updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow()

"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL

It takes withTimezone (default true), precision (0 to 6) and mode, and auditColumns() forwards them. mode: "date" (the default) reads a Date; mode: "string" reads Postgres's own text form, which also keeps the microseconds a Date truncates. The mode is a JavaScript mapping and emits no SQL:

...timestamps({ mode: "string" })
// createdAt: string, updatedAt: string in $inferSelect

Keeping the update columns currentCopy Anchor Link

created_at and created_by are column DEFAULTs, so Postgres fills them for every writer. updated_at and updated_by have no equivalent for updates, so they need a trigger, and that trigger is required for the values to mean anything.

Drizzle offers a $onUpdateFn hook for this, and Zeno does not use it. Drizzle applies it while building its own statement, so a write arriving through PostgREST (the supabase-js client, the dashboard, any REST caller) never runs it, and in a Supabase app that is most writes. A column that is right only when you write through Drizzle is worse than one you know needs a trigger.

Drizzle Kit generates no trigger DDL, but drizzle-kit generate --custom writes an empty but tracked migration (its snapshot links into the chain like any generated one), and @zeno-lib/db/triggers gives you the SQL to put in it.

pnpm db:generate --custom --name=posts_audit_triggers

import { auditTriggers } from "@zeno-lib/db/triggers"
import { posts } from "./schemas/posts"

console.log(auditTriggers(posts))

Paste the output into the generated migration:

create extension if not exists moddatetime with schema "extensions";

drop trigger if exists "handle_updated_at" on "public"."posts";
create trigger "handle_updated_at"
  before update on "public"."posts"
  for each row
  execute function "extensions".moddatetime ("updated_at");

create or replace function "public"."set_updated_by"()
  returns trigger
  language plpgsql
  security invoker
  set search_path = ''
as $$
begin
  new."updated_by" = (select auth.uid());
  return new;
end;
$$;

drop trigger if exists "handle_updated_by" on "public"."posts";
create trigger "handle_updated_by"
  before update on "public"."posts"
  for each row
  execute function "public"."set_updated_by"();

Each reads the table's real name and schema, accepts a plain table name, and takes column, name and schema overrides. The SQL is re-runnable (if not exists, drop ... if exists, create or replace), so regenerating a migration is safe. A write with no session records NULL in updated_by, because that is what auth.uid() returns for the admin and service_role clients.

Column builders take their column name from the key you assign them to, or from the name option.

authUserId(options?) is a nullable UUID column referencing auth.users.id:

userId: authUserId()                              // name from the key → "user_id"
ownerId: authUserId({ name: "owner_id" })         // explicit column name
userId: authUserId({ notNull: true })             // required, ON DELETE RESTRICT

"user_id" uuid
-- foreign key emitted as a separate statement:
ALTER TABLE "<table>" ADD CONSTRAINT "<table>_user_id_users_id_fkey"
  FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;

userId(reference, options?) is the same column against your own table, which is the common case once you have a profiles mirror:

ownerId: userId(() => profiles.id, { notNull: true })

RLS policiesCopy Anchor Link

Slot ③: return them from the extra callback. These helpers wrap Drizzle's pgPolicy so you don't hand-write the for operation or the owner check at every call site. Like the rest of this module they are optional. Use policy(...) or Drizzle's pgPolicy when you need full control.

Operation-scoped policies preset the policy's for operation. Each takes (name, config?), and config accepts the usual Drizzle policy options (to, using, withCheck, as). With no to, Postgres applies the policy to public:

selectPolicy("posts_read", { using: authUserOwns(t.userId) })
// pgPolicy("posts_read", { for: "select", using: ... })

CREATE POLICY "posts_read" ON "posts" AS PERMISSIVE FOR SELECT TO public
  USING ("posts"."user_id" = (select auth.uid()));

These helpers only preset for; you still supply the condition. Postgres accepts it in a different clause per operation:

authUserOwns(ownerColumn) is the condition most of them use. It returns an SQL fragment for composing using or withCheck, not a policy:

authUserOwns(t.userId) // sql`${t.userId} = ${authUid}`

"posts"."user_id" = (select auth.uid())

Authenticated policies add one preset to the operation-scoped ones, TO authenticated, which every hand-written policy repeats. You still write the condition. Use them when access depends on more than the row's own owner column.

authenticatedSelectPolicy("posts_read", { using: sql`is_published(posts.id)` })

CREATE POLICY "posts_read" ON "posts" AS PERMISSIVE FOR SELECT
  TO "authenticated" USING (is_published(posts.id));

Function policies shorten the case the authenticated policies still leave verbose. Once access depends on anything but the row's own owner column, the usual answer is a security definer function. Supabase's RLS guide recommends one when a policy has to read another table, because the function reads that table without running its RLS policies. functionPolicies(columns, options?) writes all four:

export const posts = table("posts", { id: primaryId("uuid") }, (t) =>
  functionPolicies(t, { argument: t.id })
)

CREATE POLICY "can_select_posts" ON "posts" AS PERMISSIVE FOR SELECT
  TO "authenticated" USING ((select "can_select_posts"("posts"."id")));
CREATE POLICY "can_insert_posts" ON "posts" AS PERMISSIVE FOR INSERT
  TO "authenticated" WITH CHECK ((select "can_insert_posts"("posts"."id")));
CREATE POLICY "can_update_posts" ON "posts" AS PERMISSIVE FOR UPDATE
  TO "authenticated" USING ((select "can_update_posts"("posts"."id")));
CREATE POLICY "can_delete_posts" ON "posts" AS PERMISSIVE FOR DELETE
  TO "authenticated" USING ((select "can_delete_posts"("posts"."id")));

It puts the condition in the clause each operation takes: using for select, update and delete, withCheck for insert. Update accepts both clauses, but Postgres reuses using for the check when withCheck is left out. A second copy would say the same thing, and would only add a polwithcheck that the USING-only policies in an existing database do not have. functionPolicies wraps the call in a select so Postgres evaluates it once per statement rather than once per row, the same shape auth.uid() uses.

The row-scoped operations usually take the row, while insert takes nothing because there is no row yet to authorise, only the caller. Pass a record for that, and for functions whose signatures differ in any other way:

export const projects = table("projects", { id: primaryId("assigned") }, (t) =>
  functionPolicies(t, { argument: { delete: t.id, select: t.id, update: t.id } })
)

CREATE POLICY "can_insert_projects" ON "projects" AS PERMISSIVE FOR INSERT
  TO "authenticated" WITH CHECK ((select "can_insert_projects"()));

An operation left out of the record, or set to null, is called with no arguments. An array passes several: argument: [t.profileId, t.organisationId] emits can_select_memberships("memberships"."profile_id", "memberships"."organisation_id").

The functions for a table outside public usually live in that schema beside it, and a bare name resolves only through the search_path in effect when CREATE POLICY runs. schema qualifies the name:

const billing = schema("billing")

export const invoices = billing.table("invoices", { id: primaryId("uuid") }, (t) =>
  functionPolicies(t, { schema: "billing" })
)

CREATE POLICY "can_select_invoices" ON "billing"."invoices" AS PERMISSIVE FOR SELECT
  TO "authenticated" USING ((select "billing"."can_select_invoices"()));

It takes the columns object the extra callback gives you (t), not the table, because naming a table inside its own definition makes its type circular. The table name comes from the columns, so it stays right through a rename.

You write the functions themselves; Zeno only writes the policies that call them.

To call the same functions from application code (to show or hide an Edit button, say), selectFunctionPermissions from @zeno-lib/db/query calls all four in one statement. It looks the table name up in an allowlist built once from your schema, so a name from a request never reaches the SQL:

import { functionPermissionTables, selectFunctionPermissions } from "@zeno-lib/db/query"
import * as schema from "./schema"

const tables = functionPermissionTables(schema, { schemas: ["public", "billing"] })

await selectFunctionPermissions(db, { tables, table: "invoices", id })
// { select: true, insert: false, update: true, delete: false }

select "billing"."can_select_invoices"("id" => $1) as "select",
  "billing"."can_insert_invoices"() as "insert",
  "billing"."can_update_invoices"("id" => $2) as "update",
  "billing"."can_delete_invoices"("id" => $3) as "delete"

It qualifies the functions with the table's schema, which functionSchema overrides. It passes id to every function but insert as a named argument (argumentName, default "id"), and prefix matches the one given to functionPolicies. A null answer counts as false.

Owner policies are the narrowest preset: they force TO authenticated and default the owner check to authUserOwns(ownerColumn). Each takes (name, ownerColumn, config?); pass using or withCheck to override.

authenticatedOwnerSelectPolicy("posts_owner_select", t.userId)

CREATE POLICY "posts_owner_select" ON "posts" AS PERMISSIVE FOR SELECT
  TO "authenticated" USING ("posts"."user_id" = (select auth.uid()));

Supabase primitivesCopy Anchor Link

These are re-exported from drizzle-orm/supabase so the roles, the Realtime messages table, and the SQL helpers come from one Zeno-owned entrypoint. Reference these; do not redeclare Supabase's built-in roles in your schema.

auth.usersCopy Anchor Link

auth.users comes from a separate entrypoint, @zeno-lib/db/auth:

import { authUsers } from "@zeno-lib/db/auth"

It is the full 35-column table, generated with drizzle-kit pull and pinned to Supabase CLI 2.84.1 and GoTrue v2.188.1. Drizzle's own drizzle-orm/supabase export has 8 of those columns, so with auth in schemaFilter, drizzle-kit wants to drop the other 27, and a foreign key into auth.users means hand-writing the table.

Several columns hold credential material (encryptedPassword, the *Token columns), and reading the table at all needs an RLS-bypassing client. Select the columns you need rather than select():

import { createAdminClient } from "@zeno-lib/db"
import { authUsers } from "@zeno-lib/db/auth"

const db = createAdminClient()
const users = await db
  .select({ email: authUsers.email, lastSignInAt: authUsers.lastSignInAt })
  .from(authUsers)

Zeno does not ship the nine auth enums. auth.users uses none of them (they belong to the mfa_*, oauth_* and sso_* tables), and drizzle-kit never filters enums, so an exported one would be CREATE TYPE in every command.

pg-core aliasesCopy Anchor Link

These are curated aliases over drizzle-orm/pg-core, so schema files don't repeat the pg prefix at every call site. They behave like their Drizzle counterparts. (schema is the one exception and has its own section above, because it applies casing and RLS where pgSchema applies neither.)

PitfallsCopy Anchor Link

• Use table for user-scoped application tables so RLS is enabled by default.
• Use unsecureTable only when the table is intentionally not protected by RLS.
• Do not hand-write RLS policies in migration files when the table is otherwise managed by Drizzle. Put policy(...), selectPolicy(...), or owner policy helpers in the schema and generate the migration.
• Do not manually declare Supabase's built-in roles; import the existing role exports from @zeno-lib/db/schema.