DB design & schemas

-- 1:1: split rarely read or sensitive columns out
CREATE TABLE user_profiles (
  user_id    bigint PRIMARY KEY
             REFERENCES users (id) ON DELETE CASCADE,
  bio        text,
  avatar_url text
);
 
-- 1:N: FK on the many side, indexed
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY
              PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  total_cents bigint NOT NULL CHECK (total_cents >= 0),
  placed_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_idx
  ON orders (customer_id, placed_at DESC);

CREATE TABLE enrollments (
  student_id  bigint NOT NULL
              REFERENCES students (id) ON DELETE CASCADE,
  course_id   bigint NOT NULL
              REFERENCES courses (id) ON DELETE CASCADE,
  enrolled_at timestamptz NOT NULL DEFAULT now(),
  grade       text,
  PRIMARY KEY (student_id, course_id)
);
-- the PK serves student lookups; add the reverse
CREATE INDEX enrollments_course_idx
  ON enrollments (course_id);

CREATE TABLE employees (
  id         bigint GENERATED ALWAYS AS IDENTITY
             PRIMARY KEY,
  name       text NOT NULL,
  manager_id bigint
             REFERENCES employees (id) ON DELETE SET NULL,
  CHECK (manager_id <> id)
);
CREATE INDEX employees_manager_idx ON employees (manager_id);

CREATE TABLE comments (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id  bigint REFERENCES posts (id) ON DELETE CASCADE,
  photo_id bigint REFERENCES photos (id) ON DELETE CASCADE,
  body     text NOT NULL,
  CHECK (num_nonnulls(post_id, photo_id) = 1)
);
CREATE INDEX comments_post_idx ON comments (post_id)
  WHERE post_id IS NOT NULL;
CREATE INDEX comments_photo_idx ON comments (photo_id)
  WHERE photo_id IS NOT NULL;

CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE categories (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  path ltree NOT NULL UNIQUE   -- 'electronics.phones'
);
CREATE INDEX categories_path_gist
  ON categories USING gist (path);
 
-- subtree, ancestors, depth
SELECT * FROM categories WHERE path <@ 'electronics';
SELECT * FROM categories
WHERE path @> 'electronics.phones.android';
SELECT name, nlevel(path) AS depth FROM categories;

CREATE DOMAIN positive_cents AS bigint CHECK (VALUE >= 0);
 
CREATE TABLE order_statuses (
  code  text PRIMARY KEY,         -- 'pending', 'paid'
  label text NOT NULL,
  sort  int NOT NULL DEFAULT 0
);
INSERT INTO order_statuses (code, label)
VALUES ('pending', 'Pending'), ('paid', 'Paid');
 
ALTER TABLE orders
  ADD COLUMN status text NOT NULL DEFAULT 'pending'
    REFERENCES order_statuses (code),
  ADD COLUMN tax_cents positive_cents NOT NULL DEFAULT 0;

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE product_prices (
  product_id  bigint NOT NULL REFERENCES products (id),
  valid       tstzrange NOT NULL,
  price_cents bigint NOT NULL CHECK (price_cents >= 0),
  PRIMARY KEY (product_id, valid WITHOUT OVERLAPS) -- 18+
);
 
INSERT INTO product_prices VALUES
  (1, tstzrange('2026-01-01', '2026-07-01'), 1000),
  (1, tstzrange('2026-07-01', NULL), 1200);
 
SELECT price_cents FROM product_prices
WHERE product_id = 1 AND valid @> now();

CREATE TABLE projects (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id uuid NOT NULL REFERENCES orgs (id),
  slug   text NOT NULL,
  UNIQUE (org_id, id),      -- target for composite FKs
  UNIQUE (org_id, slug)
);
 
CREATE TABLE tasks (
  id         bigint GENERATED ALWAYS AS IDENTITY
             PRIMARY KEY,
  org_id     uuid NOT NULL,
  project_id bigint NOT NULL,
  title      text NOT NULL,
  FOREIGN KEY (org_id, project_id)
    REFERENCES projects (org_id, id) ON DELETE CASCADE
);
CREATE INDEX tasks_org_project_idx
  ON tasks (org_id, project_id);

CREATE TABLE integrations (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id   uuid NOT NULL REFERENCES orgs (id),
  kind     text NOT NULL CHECK (kind IN ('slack', 'github')),
  settings jsonb NOT NULL DEFAULT '{}'
           CHECK (jsonb_typeof(settings) = 'object'),
  -- promote a hot key to a real, indexable column
  channel  text GENERATED ALWAYS AS
           (settings ->> 'channel') STORED
);
CREATE INDEX integrations_channel_idx
  ON integrations (channel);

import { defineConfig } from "drizzle-kit";
 
export default defineConfig({
  dialect: "postgresql",
  schema: "./db/schema/index.ts",
  out: "./db/migrations",
  casing: "snake_case",        // createdAt -> created_at
  dbCredentials: { url: process.env.DATABASE_URL ?? "" },
});

bunx drizzle-kit generate --name add_projects  # diff -> SQL
bunx drizzle-kit migrate      # apply pending migrations
bunx drizzle-kit push         # dev only: sync, no files
bunx drizzle-kit check        # detect conflicting files
bunx drizzle-kit studio       # browse data

import { drizzle } from "drizzle-orm/bun-sql";
import { and, eq } from "drizzle-orm";
import * as schema from "./schema";
import { memberships, orgs, users } from "./schema";
 
export const db = drizzle({
  connection: process.env.DATABASE_URL ?? "",
  schema,
  casing: "snake_case",
});
 
export function orgsFor(userId: string) {
  return db
    .select({ org: orgs.name, role: memberships.role })
    .from(memberships)
    .innerJoin(orgs, eq(orgs.id, memberships.orgId))
    .where(eq(memberships.userId, userId));
}
 
export async function join(orgId: string, email: string) {
  return db.transaction(async (tx) => {
    const [user] = await tx
      .select().from(users).where(eq(users.email, email));
    if (!user) throw new Error(`no user ${email}`);
    await tx.insert(memberships)
      .values({ orgId, userId: user.id })
      .onConflictDoNothing();
    return tx.select().from(memberships).where(and(
      eq(memberships.orgId, orgId),
      eq(memberships.userId, user.id),
    ));
  });
}

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}
datasource db {
  provider = "postgresql"
}
model Membership {
  orgId  String @map("org_id") @db.Uuid
  userId String @map("user_id") @db.Uuid
  role   String @default("member")
  org    Org    @relation(fields: [orgId], references: [id], onDelete: Cascade)
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  @@id([orgId, userId])
  @@map("memberships")
}

 ──||──  exactly one        ──o|──  zero or one
 ──|<──  one or many        ──o<──  zero or many
 
┌─────────────┐          ┌───────────────┐          ┌─────────────┐
│ users       │          │ memberships   │          │ orgs        │
├─────────────┤          ├───────────────┤          ├─────────────┤
│ id       PK │──||───o<─│ user_id PK,FK │─>o───||──│ id       PK │
│ email    UQ │          │ org_id  PK,FK │          │ slug     UQ │
│ name        │          │ role          │          │ name        │
└─────────────┘          └───────────────┘          └─────────────┘
 
A user has zero or many memberships; each membership has exactly one user.
An org has zero or many memberships; each membership has exactly one org.

erDiagram
  USERS ||--o{ MEMBERSHIPS : "belongs via"
  ORGS  ||--o{ MEMBERSHIPS : has
  ORGS  ||--o{ PROJECTS : owns

CREATE TABLE users (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  email      text NOT NULL,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX users_email_key ON users (lower(email));
 
CREATE TABLE orgs (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  slug       text NOT NULL UNIQUE
             CHECK (slug ~ '^[a-z0-9-]{2,40}$'),
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE memberships (
  org_id  uuid NOT NULL REFERENCES orgs ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
  role    text NOT NULL DEFAULT 'member'
          CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (org_id, user_id)
);
CREATE INDEX memberships_user_idx ON memberships (user_id);

CREATE TABLE tags (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
CREATE UNIQUE INDEX tags_name_key ON tags (lower(name));
 
CREATE TABLE post_tags (
  post_id bigint NOT NULL REFERENCES posts ON DELETE CASCADE,
  tag_id  bigint NOT NULL REFERENCES tags ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);
CREATE INDEX post_tags_tag_idx ON post_tags (tag_id);
 
-- posts carrying ALL of the given tags
SELECT pt.post_id
FROM post_tags pt
JOIN tags t ON t.id = pt.tag_id
WHERE lower(t.name) = ANY ($1::text[])
GROUP BY pt.post_id
HAVING count(*) = cardinality($1::text[]);

CREATE TABLE audit_log (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tbl      text NOT NULL,
  row_id   text,
  op       text NOT NULL,   -- INSERT, UPDATE, DELETE
  actor    text DEFAULT current_setting('app.user_id', true),
  at       timestamptz NOT NULL DEFAULT now(),
  old_row  jsonb,
  new_row  jsonb
);
 
CREATE OR REPLACE FUNCTION audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (tbl, row_id, op, old_row, new_row)
  VALUES (TG_TABLE_NAME,
    coalesce(to_jsonb(NEW), to_jsonb(OLD)) ->> 'id',
    TG_OP, to_jsonb(OLD), to_jsonb(NEW));
  RETURN NULL;             -- AFTER trigger: ignored
END
$$;
 
CREATE TRIGGER orgs_audit
AFTER INSERT OR UPDATE OR DELETE ON orgs
FOR EACH ROW EXECUTE FUNCTION audit();

CREATE TABLE comments (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  parent_id bigint REFERENCES comments ON DELETE CASCADE,
  body      text NOT NULL
);
CREATE TABLE comment_paths (
  ancestor   bigint REFERENCES comments ON DELETE CASCADE,
  descendant bigint REFERENCES comments ON DELETE CASCADE,
  depth      int NOT NULL CHECK (depth >= 0),
  PRIMARY KEY (ancestor, descendant)
);
CREATE INDEX comment_paths_desc_idx
  ON comment_paths (descendant);
 
-- after inserting comment $1 under parent $2 (or NULL)
INSERT INTO comment_paths (ancestor, descendant, depth)
SELECT $1::bigint, $1::bigint, 0
UNION ALL
SELECT ancestor, $1::bigint, depth + 1
FROM comment_paths WHERE descendant = $2::bigint;
 
-- the subtree under comment $1, shallowest first
SELECT c.*, p.depth FROM comment_paths p
JOIN comments c ON c.id = p.descendant
WHERE p.ancestor = $1 ORDER BY p.depth, c.id;

CREATE FUNCTION app_org_id() RETURNS uuid
LANGUAGE sql STABLE AS $$
  SELECT nullif(current_setting('app.org_id', true), '')
         ::uuid
$$;
 
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['projects', 'tasks'] LOOP
    EXECUTE format(
      'ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format(
      'ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
    EXECUTE format(
      'CREATE POLICY tenant_isolation ON %I
         USING (org_id = (SELECT app_org_id()))
         WITH CHECK (org_id = (SELECT app_org_id()))', t);
  END LOOP;
END
$$;
-- per request, in a transaction (Bun.sql):
--   await tx`SELECT set_config('app.org_id', ${id}, true)`

import { sql } from "drizzle-orm";
import * as p from "drizzle-orm/pg-core";
const id = () =>
  p.uuid().primaryKey().default(sql`uuidv7()`);
const createdAt = () =>
  p.timestamp({ withTimezone: true }).notNull().defaultNow();
 
export const users = p.pgTable("users", {
  id: id(), email: p.text().notNull().unique(),
  name: p.text().notNull(), createdAt: createdAt(),
});
export const orgs = p.pgTable("orgs", {
  id: id(), slug: p.text().notNull().unique(),
  name: p.text().notNull(), createdAt: createdAt(),
});
export const memberships = p.pgTable("memberships", {
  orgId: p.uuid().notNull()
    .references(() => orgs.id, { onDelete: "cascade" }),
  userId: p.uuid().notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  role: p.text({ enum: ["owner", "admin", "member"] })
    .notNull().default("member"),
}, (t) => [p.primaryKey({ columns: [t.orgId, t.userId] }),
  p.index("memberships_user_idx").on(t.userId)]);
export type User = typeof users.$inferSelect;