Schema migration guide

The schema DSL changed. A project written against the old one is refused at push with the file named, rather than deployed against a guess:

db/schema.ts is the old layout: rename it to db/public.ts and declare each table
with defineTable("name", {…}) — see the migration guide at /docs/backend/schema-migration

There is no compatibility shim and no flag that restores the old shape: the old names are gone from @palbase/backend, so the compiler finds most of this for you. Four changes, in the order they are easiest to make. A worked before/after of a whole file is at the end.

Note: Every before block on this page is the retired shape. Its names are gone from @palbase/backend, so it does not compile — that is what makes the compiler your migration tool.

1. Rename the file

git mv db/schema.ts db/public.ts

Everything under db/ is now read as a schema declaration, one file per schema: db/public.ts is the one Palbase expects to find, and db/billing.ts would declare a second schema called billing.

The file name no longer carries the identity — but it still has to agree with it. The schema names itself in the declaration (defineSchema("public", …)), and a file whose name disagrees with what it declares is refused at push with both names in the error. So db/billing.ts must declare defineSchema("billing", …); neither half is decoration.

2. Every table becomes a binding

The table name used to live in a dictionary key. It now lives in the table, and defineSchema takes an ARRAY of tables plus the schema's own name.

Before

// db/schema.ts
export default defineSchema({
  tables: {
    lists: {
      columns: {
        id: uuid().primaryKey().defaultRandom(),
        title: text().notNull(),
      },
    },
    todos: {
      columns: {
        id: uuid().primaryKey().defaultRandom(),
        title: text().notNull(),
      },
      indexes: [{ name: "todos_title_idx", columns: ["title"] }],
    },
  },
  extensions: ["pg_trgm"],
});

After

// db/public.ts
import { defineSchema, defineTable, index, text, uuid } from "@palbase/backend";

export const lists = defineTable("lists", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    title: text().notNull(),
  },
});

export const todos = defineTable("todos", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    title: text().notNull(),
  },
  indexes: (c) => [index("todos_title_idx").on(c.col("title"))],
});

export default defineSchema("public", {
  tables: [lists, todos],
  extensions: ["pg_trgm"],
});

Mechanically: <name>: { … }, becomes export const <name> = defineTable("<name>", { … });, lifted out of the tables object, and the names go into the array. columns, rls, primaryKey, unique, checks, search and raw come across unchanged, and extensions stays on defineSchema. Two fields did change shape, and change 5 below is where they are: policies and indexes are callbacks now.

You do not have to add exposed here. public defaults to exposed: true, so a migrated project keeps serving its tables over /v1/db exactly as before — that asymmetry exists precisely so this rename cannot take an existing project's traffic away. Every schema you add LATER defaults to closed. Write the field only to go against that: exposed: false to close public, exposed: true to open a second schema.

Two more rules the array form brings, both refused where you write them rather than at deploy: a table name may not be empty, and two tables in one schema may not declare the same name.

Why the name moved

A table declared under a dictionary key does not know what it is called while it is being built, so it cannot resolve a reference to a sibling — the old form had no way to write todos.list_id → lists.id as an expression at all. Moving the name onto the table gives it an identity that is never empty, and that is what makes change 4 possible.

3. Ownership is a verb, not a chain

referencesAuthUser and referencesInstallation are gone. Three factories replace them, and they are factories rather than chain methods because the column type, the nullability and the ON DELETE are part of what each one means.

Before

columns: {
  user_id:         text().notNull().referencesAuthUser("cascade"),
  edited_by:       text().nullable().referencesAuthUser("set null"),
  installation_id: text().nullable().referencesInstallation("set null"),
}

After

// db/public.ts — the three factories, in the columns object they replace
import { defineTable, installationRef, ownedByUser, userRef, uuid } from "@palbase/backend";

export const notes = defineTable("notes", {
  columns: {
    id:              uuid().primaryKey().defaultRandom(),
    user_id:         ownedByUser(),
    edited_by:       userRef({ onDelete: "set null" }).nullable(),
    installation_id: installationRef({ onDelete: "set null" }).nullable(),
  },
});

ownedByUser() implies text, NOT NULL and ON DELETE CASCADE, so the text().notNull() prefix and the "cascade" argument both go away — there is only one correct ON DELETE for ownership, because ownership is what account erasure walks. userRef keeps its onDelete because both answers are legitimate there, and "set null" still needs the .nullable().

No DDL changes. All three produce the same foreign key the old chain did, so this rewrite is a no-op in palbase db plan — the columns are text, the references are the same, the ON DELETE actions are the same.

More than one owner column

This is the change that can alter behaviour, so read it before you convert a table with two referencesAuthUser("cascade") columns.

Under the old form, several columns could reference auth.users, and the one treated as the row's owner was whichever came first in declaration order — moving a created_by above a user_id silently changed which rows an account deletion took with it. Only one column can be the owner now, and a table declaring two ownedByUser() columns is refused at push with both names in the error.

Convert the column that actually owns the row to ownedByUser(), and every other user reference to userRef({ onDelete: "cascade" }):

// db/public.ts
import { defineTable, ownedByUser, userRef, uuid } from "@palbase/backend";

// before: two cascading auth-user FKs, owner decided by declaration order —
//   user_id:    text().notNull().referencesAuthUser("cascade"),
//   created_by: text().notNull().referencesAuthUser("cascade"),
// after: one owner, one plain reference — the same two foreign keys
export const articles = defineTable("articles", {
  columns: {
    id:         uuid().primaryKey().defaultRandom(),
    user_id:    ownedByUser(),
    created_by: userRef({ onDelete: "cascade" }),
  },
});

The foreign keys are identical either way; what you are choosing is which one erasure and the owner relation follow.

4. References point at a column, not at two strings

references("lists", "id") named its target with strings nothing checked: a typo in either one was a runtime problem, and neither name moved when you renamed the table. The target is now a column, behind a thunk.

Before

list_id:   uuid().notNull().references("lists", "id").onDelete("cascade"),
parent_id: uuid().nullable().references("categories", "id"),  // this same table

After

// db/public.ts
import { defineTable, text, uuid } from "@palbase/backend";

export const lists = defineTable("lists", {
  columns: { id: uuid().primaryKey().defaultRandom(), title: text().notNull() },
});

export const categories = defineTable("categories", {
  columns: {
    id:        uuid().primaryKey().defaultRandom(),
    list_id:   uuid().references(() => lists.id).onDelete("cascade"),
    parent_id: uuid().nullable().selfReferences("id"),
  },
});

Three shapes, and which one you need depends on where the target is:

Why a thunk

references(lists.id) cannot be written for a cycle: in x → y, y → x the second binding does not exist yet when the first is evaluated. The callback defers the lookup to defineSchema, which runs after every binding exists and every table knows its name.

Self-references take no thunk

The target table is the one being declared, so there is nothing to defer and no annotation to write:

import { defineTable, uuid } from "@palbase/backend";

export const categories = defineTable("categories", {
  columns: {
    id:        uuid().primaryKey().defaultRandom(),
    parent_id: uuid().nullable().selfReferences("id"),
  },
});

Naming a column the table does not have is refused where you declare it.

A two-table cycle needs one annotation

Without a return type on one side, TypeScript chases its own tail (TS7022). Annotating one side is enough — measured; annotating both is not wrong, just unnecessary:

import { type AnyColumn, defineTable, uuid } from "@palbase/backend";

export const users = defineTable("users", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    primary_org_id: uuid().nullable().references((): AnyColumn => orgs.id),
  },
});

export const orgs = defineTable("orgs", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    owner_id: uuid().nullable().references(() => users.id),
  },
});

Two references to the same table

Each foreign key produces two relation names. The forward one is derived from the column (list_id → list) and is renamed with { as }; the reverse one, on the parent, defaults to this table's own name and is renamed with { reverseAs }. Two foreign keys onto one parent therefore collide on the reverse side whatever the columns are called:

// db/public.ts
import { defineTable, text, uuid } from "@palbase/backend";

export const addresses = defineTable("addresses", {
  columns: { id: uuid().primaryKey().defaultRandom(), line1: text().notNull() },
});

export const orders = defineTable("orders", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    billing_address_id:  uuid().references(() => addresses.id, { reverseAs: "billed_orders" }),
    shipping_address_id: uuid().references(() => addresses.id, { reverseAs: "shipped_orders" }),
  },
});

Under the old form the second such foreign key was silently swallowed. It is now either named or refused.

5. policies and indexes are callbacks

Both used to be plain arrays. Both are now functions of the table's own columns, and passing an array is a type error on the line you wrote it — not a deploy-time refusal:

// db/reviews.ts
import { defineSchema, defineTable, index, ownedByUser, policy, text, uuid } from "@palbase/backend";

export const reviews = defineTable("reviews", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    user_id: ownedByUser(),
    body: text().notNull(),
  },
  // was: indexes: [{ name: "reviews_user_idx", columns: ["user_id"] }]
  indexes: (c) => [index("reviews_user_idx").on(c.col("user_id"))],
  // was: policies: [policy("reviews_owner")…using("user_id = (select auth.uid())")]
  policies: (p) => [
    policy("reviews_owner")
      .for("all")
      .to("authenticated")
      .using(p.col("user_id").eq(p.auth.uid()))
      .withCheck(p.col("user_id").eq(p.auth.uid())),
  ],
});

export default defineSchema("reviews", { tables: [reviews] });

The callback is what carries the column names: p.col("user_idd") does not compile, where the string form "user_idd = (select auth.uid())" only came back from Postgres while CREATE POLICY was running — in the middle of an apply. using() and withCheck() still accept a raw string, so a policy you cannot express in the expression language keeps working; the callback is about where the expression is written, not about what it may say.

The whole file, before and after

Before — db/schema.ts

import {
  boolean, defineSchema, enumType, policy, text, timestamp, uuid,
} from "@palbase/backend";

export default defineSchema({
  tables: {
    lists: {
      columns: {
        id: uuid().primaryKey().defaultRandom(),
        user_id: text().notNull().referencesAuthUser("cascade"),
        title: text().notNull(),
      },
    },
    todos: {
      columns: {
        id: uuid().primaryKey().defaultRandom(),
        list_id: uuid().notNull().references("lists", "id").onDelete("cascade"),
        parent_id: uuid().nullable().references("todos", "id"),
        user_id: text().notNull().referencesAuthUser("cascade"),
        edited_by: text().nullable().referencesAuthUser("set null"),
        title: text().notNull(),
        status: enumType("todo_status", ["open", "done"]).default("open"),
        done: boolean().default(false),
        created_at: timestamp().defaultNow(),
      },
      indexes: [{ name: "todos_list_idx", columns: ["list_id"] }],
      policies: [
        policy("todos_owner")
          .for("all")
          .to("authenticated")
          .using("user_id = (select auth.uid())")
          .withCheck("user_id = (select auth.uid())"),
      ],
    },
  },
  extensions: ["pg_trgm"],
});

After — db/public.ts

// db/public.ts
import {
  boolean, defineSchema, defineTable, enumType, index, ownedByUser, policy,
  text, timestamp, userRef, uuid,
} from "@palbase/backend";

export const lists = defineTable("lists", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    user_id: ownedByUser(),
    title: text().notNull(),
  },
});

export const todos = defineTable("todos", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    list_id: uuid().references(() => lists.id).onDelete("cascade"),
    parent_id: uuid().nullable().selfReferences("id"),
    user_id: ownedByUser(),
    edited_by: userRef({ onDelete: "set null" }).nullable(),
    title: text().notNull(),
    status: enumType("todo_status", ["open", "done"]).default("open"),
    done: boolean().default(false),
    created_at: timestamp().defaultNow(),
  },
  indexes: (c) => [index("todos_list_idx").on(c.col("list_id"))],
  policies: (p) => [
    policy("todos_owner")
      .for("all")
      .to("authenticated")
      .using(p.col("user_id").eq(p.auth.uid()))
      .withCheck(p.col("user_id").eq(p.auth.uid())),
  ],
});

export default defineSchema("public", {
  tables: [lists, todos],
  extensions: ["pg_trgm"],
});

Every column is the same column, so the migration produces no plan lines:

palbase build      # regenerate palbase-env.d.ts from the new file
palbase db plan    # expect: the database matches your declaration

If a plan does appear, it is telling you something real about the rewrite — a column that moved between NOT NULL and nullable, a references retargeted by a typo, an index whose name changed. Read it before you apply it.

Splitting a second schema out

Only worth doing once you have tables no client should ever fetch. Move them into their own file and leave exposed off:

// db/billing.ts
import { defineSchema, defineTable, numeric, uuid } from "@palbase/backend";
import { lists } from "./public";

export const invoices = defineTable("invoices", {
  columns: {
    id: uuid().primaryKey().defaultRandom(),
    list_id: uuid().references(() => lists.id),   // across schemas, and typed
    amount: numeric(),
  },
});

export default defineSchema("billing", { tables: [invoices] });

A foreign key may cross schemas: import the binding and point at it exactly as you would within one file. Export the tables another file references; a table used only inside its own file does not need to be exported.

Moving an existing table between schemas is not a rename the declarative rail follows — from its side a table left public and a table appeared in billing, and the data goes with the drop. Do that move as an explicit migration.

Checklist

• db/schema.ts renamed to db/public.ts, and any other schema file named after what it declares
• every table lifted to export const <name> = defineTable("<name>", {…})
• defineSchema("public", { tables: [...] }) — name first, array second
• exposed left alone unless you mean to close public or open a second schema
• every referencesAuthUser("cascade") → ownedByUser(), at most one per table
• every other auth-user reference → userRef({ onDelete }), set null with .nullable()
• every referencesInstallation(...) → installationRef({ onDelete })
• every references("t", "c") → references(() => t.c), self-references → selfReferences("c")
• one (): AnyColumn => annotation per two-table cycle
• two references to the same parent given reverseAs: names (and as: if the forward names would collide too)
• every policies: [...] → policies: (p) => [...], every indexes: [{…}] → indexes: (c) => [...]
• palbase build then palbase db plan — expect no changes

Related

• Schema — the full reference for the current DSL
• Row-Level Security — policy(), auth.uid(), and $asService()
• Schema changes — how a change reaches a database, and what a refusal looks like
• Database — palbase db plan, db apply and db query