Documentation

Adding a New Table

Step-by-step tutorial — create a Drizzle schema, export it, generate a migration, apply it, and write queries in Motoko Base.

Open inChatGPT Claude Cursor

This walkthrough adds a notes table — a simple user-owned resource, similar to Link Manager. Adapt the same steps for any new feature.

Goal: each user can store notes with a title and body. Only the owner can read or mutate their rows.

Overview

1. Create schema     →  src/lib/db/schema/notes.ts
2. Export schema     →  src/lib/db/schema/schema.ts
3. Generate migration →  pnpm db:generate
4. Apply migration   →  pnpm db:migrate
5. Create queries    →  src/features/notes/queries.ts

Step 1 — Create schema

Create src/lib/db/schema/notes.ts:

import { index, pgTable, text, timestamp } from "drizzle-orm/pg-core";

import { user } from "./auth";

export const notes = pgTable(
  "notes",
  {
    id: text("id").primaryKey(),
    userId: text("user_id")
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    body: text("body").notNull().default(""),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at")
      .defaultNow()
      .$onUpdate(() => new Date())
      .notNull(),
  },
  (table) => [index("notes_userId_idx").on(table.userId)],
);

Key points:

userId — ownership column with cascade delete (when a user is deleted, their notes go too)
Text primary key — generate IDs in application code (e.g. crypto.randomUUID()), matching existing tables
Index on user_id — required for list-by-owner queries

Step 2 — Export schema

Add the export to src/lib/db/schema/schema.ts:

export * from "./auth";
export * from "./links";
export * from "./files";
export * from "./user-preferences";
export * from "./notes";   // add this line

Drizzle Kit only sees tables exported from this barrel file.

Step 3 — Generate migration

Make sure DATABASE_URL is set in .env, then run:

pnpm db:generate

Drizzle Kit creates a new folder under drizzle/migrations/ with SQL similar to:

CREATE TABLE "notes" (
  "id" text PRIMARY KEY NOT NULL,
  "user_id" text NOT NULL,
  "title" text NOT NULL,
  "body" text DEFAULT '' NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "notes" ADD CONSTRAINT "notes_user_id_user_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."user"("id")
  ON DELETE cascade ON UPDATE no action;

CREATE INDEX "notes_userId_idx" ON "notes" USING btree ("user_id");

Review the generated SQL before applying. If something looks wrong, fix the schema file, delete the unapplied migration folder, and run db:generate again.

Supabase RLS (recommended)

Motoko Base enables RLS on all public tables. After generating the migration, add RLS statements to the same migration.sql (or a follow-up migration):

ALTER TABLE "notes" ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE "notes" FROM anon, authenticated;

This matches the pattern in drizzle/migrations/20260822044000_enable_rls/migration.sql. The Next.js app connects via the pooler role and bypasses RLS; anon/authenticated Supabase API roles cannot read rows.

Step 4 — Apply migration

pnpm db:migrate

Verify with Drizzle Studio:

pnpm db:studio

Open the notes table and confirm columns match your schema.

If migrate fails through the Supabase pooler, temporarily switch DATABASE_URL to the direct connection (port 5432), run migrate, then switch back. See Migrations.

Step 5 — Create queries

Create feature queries in src/features/notes/queries.ts. Reads and writes stay in the feature folder — not in route components.

import { and, desc, eq } from "drizzle-orm";

import { db } from "@/lib/db";
import { notes } from "@/lib/db/schema/notes";

export type NoteItem = {
  id: string;
  title: string;
  body: string;
  createdAt: Date;
};

function toNoteItem(row: typeof notes.$inferSelect): NoteItem {
  return {
    id: row.id,
    title: row.title,
    body: row.body,
    createdAt: row.createdAt,
  };
}

export async function listNotesForUser(userId: string): Promise<NoteItem[]> {
  const rows = await db
    .select()
    .from(notes)
    .where(eq(notes.userId, userId))
    .orderBy(desc(notes.createdAt));

  return rows.map(toNoteItem);
}

export async function findOwnedNote(userId: string, id: string) {
  const [row] = await db
    .select()
    .from(notes)
    .where(and(eq(notes.id, id), eq(notes.userId, userId)))
    .limit(1);

  return row ?? null;
}

export async function insertNote(input: {
  id: string;
  userId: string;
  title: string;
  body: string;
}) {
  const [row] = await db
    .insert(notes)
    .values({
      id: input.id,
      userId: input.userId,
      title: input.title,
      body: input.body,
    })
    .returning();

  return row;
}

export async function deleteOwnedNote(userId: string, id: string) {
  const [row] = await db
    .delete(notes)
    .where(and(eq(notes.id, id), eq(notes.userId, userId)))
    .returning({ id: notes.id });

  return row ?? null;
}

Wire it into the app

Follow the same pattern as Link Manager:

Zod schemas — src/features/notes/schemas.ts for action input validation
Server Actions — src/features/notes/actions.ts with "use server", session check, Zod parse, query calls
UI — src/features/notes/components/notes-page.tsx
Route — src/app/dashboard/notes/page.tsx fetches via queries, renders the component
Navigation — add an item in src/features/dashboard/config/nav.ts

Always derive userId from the session — never trust client-supplied user IDs:

const session = await getCachedSession();
if (!session?.user?.id) {
  return { ok: false, error: "You must be signed in." };
}
const userId = session.user.id;

Checklist

Schema file in src/lib/db/schema/
Exported from schema.ts
pnpm db:generate — migration SQL reviewed
RLS + REVOKE added for Supabase (if using Supabase)
pnpm db:migrate — applied successfully
Queries in src/features/<name>/queries.ts
Ownership filter on every mutation (id + userId)
Migration folder committed to git

Next steps

Migrations — Command reference and troubleshooting.

Database — Architecture, conventions, and where files live.

Project structure — Feature folder layout and rules of thumb.