PostgreSQL Database Schema Implementation Plan#

For Claude: REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

Goal: Add a PostgreSQL database schema to the AppView with Drizzle ORM, defining 6 tables for indexed AT Proto forum state.

Architecture: Drizzle ORM provides schema-as-code in TypeScript. We define tables in src/db/schema.ts, configure a connection pool in src/db/index.ts, and use drizzle-kit to generate SQL migration files. The schema indexes AT Proto records from the firehose — it's a read-optimized mirror, not the source of truth.

Tech Stack: Drizzle ORM, drizzle-kit, postgres (pg driver), PostgreSQL

Task 1: Install Drizzle dependencies#

Files:

• Modify: apps/appview/package.json

Step 1: Add runtime dependencies

Run from repo root:

pnpm --filter @atbb/appview add drizzle-orm postgres

This adds:

• drizzle-orm — the ORM / query builder
• postgres — the PostgreSQL driver (postgres.js, not pg)

Step 2: Add dev dependencies

pnpm --filter @atbb/appview add -D drizzle-kit

This adds:

• drizzle-kit — CLI for migration generation and management

Step 3: Verify build still passes

pnpm build

Expected: all 3 packages build successfully.

Step 4: Commit

git add apps/appview/package.json pnpm-lock.yaml
git commit -m "feat(appview): add drizzle-orm and postgres dependencies"

Task 2: Define the database schema#

Files:

• Create: apps/appview/src/db/schema.ts

Step 1: Create the schema file

Create apps/appview/src/db/schema.ts with the full schema:

import {
  pgTable,
  bigserial,
  text,
  timestamp,
  integer,
  boolean,
  bigint,
  uniqueIndex,
  index,
} from "drizzle-orm/pg-core";

// ── forums ──────────────────────────────────────────────
// Singleton forum metadata record, owned by Forum DID.
// Key: literal:self (rkey is always "self").
export const forums = pgTable(
  "forums",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
  },
  (table) => [uniqueIndex("forums_did_rkey_idx").on(table.did, table.rkey)]
);

// ── categories ──────────────────────────────────────────
// Subforum / category definitions, owned by Forum DID.
export const categories = pgTable(
  "categories",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    slug: text("slug"),
    sortOrder: integer("sort_order"),
    forumId: bigint("forum_id", { mode: "bigint" }).references(() => forums.id),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
  },
  (table) => [
    uniqueIndex("categories_did_rkey_idx").on(table.did, table.rkey),
  ]
);

// ── users ───────────────────────────────────────────────
// Known AT Proto identities. Populated when any record
// from a DID is indexed. DID is the primary key.
export const users = pgTable("users", {
  did: text("did").primaryKey(),
  handle: text("handle"),
  indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
});

// ── memberships ─────────────────────────────────────────
// User membership in a forum. Owned by user DID.
// `did` is both the record owner and the member.
export const memberships = pgTable(
  "memberships",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did")
      .notNull()
      .references(() => users.did),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    forumId: bigint("forum_id", { mode: "bigint" }).references(
      () => forums.id
    ),
    forumUri: text("forum_uri").notNull(),
    role: text("role"),
    roleUri: text("role_uri"),
    joinedAt: timestamp("joined_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
  },
  (table) => [
    uniqueIndex("memberships_did_rkey_idx").on(table.did, table.rkey),
    index("memberships_did_idx").on(table.did),
  ]
);

// ── posts ───────────────────────────────────────────────
// Unified post model. NULL root/parent = thread starter (topic).
// Non-null root/parent = reply. Mirrors app.bsky.feed.post pattern.
// Owned by user DID.
export const posts = pgTable(
  "posts",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did")
      .notNull()
      .references(() => users.did),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    text: text("text").notNull(),
    forumUri: text("forum_uri"),
    rootPostId: bigint("root_post_id", { mode: "bigint" }).references(
      (): any => posts.id
    ),
    parentPostId: bigint("parent_post_id", { mode: "bigint" }).references(
      (): any => posts.id
    ),
    rootUri: text("root_uri"),
    parentUri: text("parent_uri"),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
    deleted: boolean("deleted").notNull().default(false),
  },
  (table) => [
    uniqueIndex("posts_did_rkey_idx").on(table.did, table.rkey),
    index("posts_forum_uri_idx").on(table.forumUri),
    index("posts_root_post_id_idx").on(table.rootPostId),
  ]
);

// ── mod_actions ─────────────────────────────────────────
// Moderation actions, owned by Forum DID. Written by AppView
// on behalf of authorized moderators after role verification.
export const modActions = pgTable(
  "mod_actions",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    action: text("action").notNull(),
    subjectDid: text("subject_did"),
    subjectPostUri: text("subject_post_uri"),
    forumId: bigint("forum_id", { mode: "bigint" }).references(
      () => forums.id
    ),
    reason: text("reason"),
    createdBy: text("created_by").notNull(),
    expiresAt: timestamp("expires_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
  },
  (table) => [
    uniqueIndex("mod_actions_did_rkey_idx").on(table.did, table.rkey),
  ]
);

Step 2: Verify build passes

pnpm build

Expected: all packages build successfully (schema file is valid TypeScript).

Step 3: Commit

git add apps/appview/src/db/schema.ts
git commit -m "feat(appview): define database schema for all 6 tables"

Task 3: Create the database connection module#

Files:

• Create: apps/appview/src/db/index.ts
• Modify: apps/appview/src/lib/config.ts

Step 1: Add DATABASE_URL to config

Modify apps/appview/src/lib/config.ts to add databaseUrl:

export interface AppConfig {
  port: number;
  forumDid: string;
  pdsUrl: string;
  databaseUrl: string;
}

export function loadConfig(): AppConfig {
  return {
    port: parseInt(process.env.PORT ?? "3000", 10),
    forumDid: process.env.FORUM_DID ?? "",
    pdsUrl: process.env.PDS_URL ?? "https://bsky.social",
    databaseUrl: process.env.DATABASE_URL ?? "",
  };
}

Step 2: Create the database connection module

Create apps/appview/src/db/index.ts:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema.js";

export function createDb(databaseUrl: string) {
  const client = postgres(databaseUrl);
  return drizzle(client, { schema });
}

export type Database = ReturnType<typeof createDb>;

export * from "./schema.js";

Step 3: Verify build passes

pnpm build

Expected: all packages build successfully.

Step 4: Commit

git add apps/appview/src/db/index.ts apps/appview/src/lib/config.ts
git commit -m "feat(appview): add database connection module and DATABASE_URL config"

Task 4: Configure drizzle-kit and generate migrations#

Files:

• Create: apps/appview/drizzle.config.ts

Step 1: Create drizzle-kit config

Create apps/appview/drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
});

Step 2: Add migration scripts to package.json

Add to apps/appview/package.json scripts:

{
  "db:generate": "drizzle-kit generate",
  "db:migrate": "drizzle-kit migrate"
}

Step 3: Generate the initial migration

Run from the appview package directory:

pnpm --filter @atbb/appview db:generate

Expected: a migration SQL file appears in apps/appview/drizzle/ with CREATE TABLE statements for all 6 tables.

Step 4: Inspect the generated SQL

Read the generated .sql file in apps/appview/drizzle/ and verify it contains:

• 6 CREATE TABLE statements (forums, categories, users, memberships, posts, mod_actions)
• All UNIQUE indexes on (did, rkey)
• All additional indexes (posts.forum_uri, posts.root_post_id, memberships.did)
• All foreign key constraints

Step 5: Commit

git add apps/appview/drizzle.config.ts apps/appview/drizzle/ apps/appview/package.json
git commit -m "feat(appview): add drizzle-kit config and generate initial migration"

Task 5: Update environment configuration#

Files:

• Modify: .env.example

Step 1: Add DATABASE_URL to .env.example

Add to .env.example:

# Database
DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb

Step 2: Commit

git add .env.example
git commit -m "feat: add DATABASE_URL to .env.example"

Task 6: Verify migrations run on fresh Postgres#

Step 1: Start a temporary Postgres instance

Using Docker:

docker run --rm --name atbb-pg-test -e POSTGRES_USER=atbb -e POSTGRES_PASSWORD=atbb -e POSTGRES_DB=atbb -p 5432:5432 -d postgres:17

Wait for it to be ready:

until docker exec atbb-pg-test pg_isready -U atbb; do sleep 1; done

Step 2: Run migrations

DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb pnpm --filter @atbb/appview db:migrate

Expected: migrations complete successfully, all 6 tables created.

Step 3: Verify tables exist

docker exec atbb-pg-test psql -U atbb -d atbb -c '\dt'

Expected: lists forums, categories, users, memberships, posts, mod_actions tables (plus drizzle's internal migration tracking table).

Step 4: Verify indexes exist

docker exec atbb-pg-test psql -U atbb -d atbb -c '\di'

Expected: lists all primary key indexes, unique indexes on (did, rkey) for each record table, plus posts_forum_uri_idx, posts_root_post_id_idx, memberships_did_idx.

Step 5: Clean up

docker stop atbb-pg-test

Step 6: No commit needed — this was verification only.

Task 7: Final build verification and docs update#

Step 1: Full build from repo root

pnpm build

Expected: all packages build successfully.

Step 2: Commit the design doc (if not already committed)

git add docs/plans/2026-02-06-database-schema-design.md
git commit -m "docs: add database schema design document for ATB-7"

Summary of files created/modified#