SQLite Support Implementation Plan#

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

Goal: Add SQLite as a fully supported database backend alongside PostgreSQL, replacing the permissions text[] column with a normalized role_permissions join table that works on both dialects.

Architecture: createDb(url) in packages/db/src/index.ts inspects the URL prefix to select postgres.js or @libsql/client. Two Drizzle schema files mirror each other with dialect-appropriate column types. The Postgres schema is the TypeScript source of truth; the SQLite instance is cast at the boundary. The Postgres migration is split into two steps with a data migration script between them to avoid data loss.

Tech Stack: @libsql/client, drizzle-orm/libsql, drizzle-orm/postgres-js, Drizzle Kit 0.31

Important notes before starting#

seed-roles.ts needs no changes. It creates AT Proto records with permissions: string[] — that's the lexicon format, not the DB schema. The indexer handles DB writes.
rolePermissions cascade deletes. The onDelete: "cascade" FK means deleting a role automatically cleans up its role_permissions rows. Test cleanup code in test-context.ts does not need a separate rolePermissions delete.
Two-stage Postgres schema. You MUST generate migration 0011 (add table) BEFORE removing the permissions column from schema.ts. Removing it first would generate a single migration that drops data.
Run commands inside devenv shell. All pnpm commands must run inside the Nix devenv shell, or use the absolute path .devenv/profile/bin/pnpm.
Test environment. DATABASE_URL must be set for all test runs. For SQLite: DATABASE_URL=file::memory:.

Task 1: Add @libsql/client to packages/db#

Files:

Modify: packages/db/package.json

Step 1: Add the dependency

In packages/db/package.json, add @libsql/client to dependencies:

"dependencies": {
  "@libsql/client": "^0.14.0",
  "drizzle-orm": "^0.45.1",
  "postgres": "^3.4.8"
}

Step 2: Install

pnpm install

Expected: no errors, @libsql/client appears in pnpm-lock.yaml.

Step 3: Commit

git add packages/db/package.json pnpm-lock.yaml
git commit -m "feat(db): add @libsql/client dependency for SQLite support"

Task 2: Create packages/db/src/schema.sqlite.ts#

Files:

Create: packages/db/src/schema.sqlite.ts

Step 1: Write the full SQLite schema

The SQLite schema is identical to the Postgres schema in table names and column names but uses sqlite-core helpers. Key differences: integer({ mode: "bigint" }).primaryKey({ autoIncrement: true }) replaces bigserial, integer({ mode: "timestamp" }) replaces timestamp({ withTimezone: true }), and integer({ mode: "boolean" }) replaces boolean. There is no permissions column — role_permissions table is included from the start.

Create packages/db/src/schema.sqlite.ts:

import {
  sqliteTable,
  text,
  integer,
  uniqueIndex,
  index,
  primaryKey,
} from "drizzle-orm/sqlite-core";

// ── forums ──────────────────────────────────────────────
export const forums = sqliteTable(
  "forums",
  {
    id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [uniqueIndex("forums_did_rkey_idx").on(table.did, table.rkey)]
);

// ── categories ──────────────────────────────────────────
export const categories = sqliteTable(
  "categories",
  {
    id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
    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: integer("forum_id", { mode: "bigint" }).references(() => forums.id),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("categories_did_rkey_idx").on(table.did, table.rkey),
  ]
);

// ── boards ──────────────────────────────────────────────
export const boards = sqliteTable(
  "boards",
  {
    id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
    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"),
    categoryId: integer("category_id", { mode: "bigint" }).references(() => categories.id),
    categoryUri: text("category_uri").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("boards_did_rkey_idx").on(table.did, table.rkey),
    index("boards_category_id_idx").on(table.categoryId),
  ]
);

// ── users ───────────────────────────────────────────────
export const users = sqliteTable("users", {
  did: text("did").primaryKey(),
  handle: text("handle"),
  indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
});

// ── memberships ─────────────────────────────────────────
export const memberships = sqliteTable(
  "memberships",
  {
    id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
    did: text("did").notNull().references(() => users.did),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    forumId: integer("forum_id", { mode: "bigint" }).references(() => forums.id),
    forumUri: text("forum_uri").notNull(),
    role: text("role"),
    roleUri: text("role_uri"),
    joinedAt: integer("joined_at", { mode: "timestamp" }),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("memberships_did_rkey_idx").on(table.did, table.rkey),
    index("memberships_did_idx").on(table.did),
  ]
);

// ── posts ───────────────────────────────────────────────
export const posts = sqliteTable(
  "posts",
  {
    id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
    did: text("did").notNull().references(() => users.did),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    title: text("title"),
    text: text("text").notNull(),
    forumUri: text("forum_uri"),
    boardUri: text("board_uri"),
    boardId: integer("board_id", { mode: "bigint" }).references(() => boards.id),
    rootPostId: integer("root_post_id", { mode: "bigint" }).references((): any => posts.id),
    parentPostId: integer("parent_post_id", { mode: "bigint" }).references((): any => posts.id),
    rootUri: text("root_uri"),
    parentUri: text("parent_uri"),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
    bannedByMod: integer("banned_by_mod", { mode: "boolean" }).notNull().default(false),
    deletedByUser: integer("deleted_by_user", { mode: "boolean" }).notNull().default(false),
  },
  (table) => [
    uniqueIndex("posts_did_rkey_idx").on(table.did, table.rkey),
    index("posts_forum_uri_idx").on(table.forumUri),
    index("posts_board_id_idx").on(table.boardId),
    index("posts_board_uri_idx").on(table.boardUri),
    index("posts_root_post_id_idx").on(table.rootPostId),
  ]
);

// ── mod_actions ─────────────────────────────────────────
export const modActions = sqliteTable(
  "mod_actions",
  {
    id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
    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: integer("forum_id", { mode: "bigint" }).references(() => forums.id),
    reason: text("reason"),
    createdBy: text("created_by").notNull(),
    expiresAt: integer("expires_at", { mode: "timestamp" }),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("mod_actions_did_rkey_idx").on(table.did, table.rkey),
    index("mod_actions_subject_did_idx").on(table.subjectDid),
    index("mod_actions_subject_post_uri_idx").on(table.subjectPostUri),
  ]
);

// ── firehose_cursor ─────────────────────────────────────
export const firehoseCursor = sqliteTable("firehose_cursor", {
  service: text("service").primaryKey().default("jetstream"),
  cursor: integer("cursor", { mode: "bigint" }).notNull(),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull(),
});

// ── roles ───────────────────────────────────────────────
// No `permissions` column — permissions are stored in role_permissions.
export const roles = sqliteTable(
  "roles",
  {
    id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
    did: text("did").notNull(),
    rkey: text("rkey").notNull(),
    cid: text("cid").notNull(),
    name: text("name").notNull(),
    description: text("description"),
    priority: integer("priority").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
    indexedAt: integer("indexed_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [
    uniqueIndex("roles_did_rkey_idx").on(table.did, table.rkey),
    index("roles_did_idx").on(table.did),
    index("roles_did_name_idx").on(table.did, table.name),
  ]
);

// ── role_permissions ─────────────────────────────────────
// Normalized join table replacing the permissions text[] column.
// Cascade delete ensures permissions are cleaned up when a role is deleted.
export const rolePermissions = sqliteTable(
  "role_permissions",
  {
    roleId: integer("role_id", { mode: "bigint" })
      .notNull()
      .references(() => roles.id, { onDelete: "cascade" }),
    permission: text("permission").notNull(),
  },
  (t) => [primaryKey({ columns: [t.roleId, t.permission] })]
);

// ── backfill_progress ───────────────────────────────────
export const backfillProgress = sqliteTable("backfill_progress", {
  id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
  status: text("status").notNull(),
  backfillType: text("backfill_type").notNull(),
  lastProcessedDid: text("last_processed_did"),
  didsTotal: integer("dids_total").notNull().default(0),
  didsProcessed: integer("dids_processed").notNull().default(0),
  recordsIndexed: integer("records_indexed").notNull().default(0),
  startedAt: integer("started_at", { mode: "timestamp" }).notNull(),
  completedAt: integer("completed_at", { mode: "timestamp" }),
  errorMessage: text("error_message"),
});

// ── backfill_errors ─────────────────────────────────────
export const backfillErrors = sqliteTable(
  "backfill_errors",
  {
    id: integer("id", { mode: "bigint" }).primaryKey({ autoIncrement: true }),
    backfillId: integer("backfill_id", { mode: "bigint" })
      .notNull()
      .references(() => backfillProgress.id),
    did: text("did").notNull(),
    collection: text("collection").notNull(),
    errorMessage: text("error_message").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
  },
  (table) => [index("backfill_errors_backfill_id_idx").on(table.backfillId)]
);

Step 2: Verify it compiles

pnpm --filter @atbb/db build

Expected: dist/ updated with no TypeScript errors.

Step 3: Commit

git add packages/db/src/schema.sqlite.ts
git commit -m "feat(db): add SQLite schema file"

Task 3: Stage 1 — Add rolePermissions to Postgres schema (keep permissions column)#

Critical: Do NOT remove the permissions column yet. That happens in Task 8 after the data migration runs. Removing it here would collapse both migrations into one, losing existing data.

Files:

Modify: packages/db/src/schema.ts

Step 1: Add primaryKey to imports and add rolePermissions table

In packages/db/src/schema.ts:

Add primaryKey to the import from drizzle-orm/pg-core (line 1):

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

After the roles table definition (after line 215), add the rolePermissions table:

// ── role_permissions ─────────────────────────────────────
// Normalized join table replacing the permissions text[] column.
// Cascade delete ensures permissions are cleaned up when a role is deleted.
export const rolePermissions = pgTable(
  "role_permissions",
  {
    roleId: bigint("role_id", { mode: "bigint" })
      .notNull()
      .references(() => roles.id, { onDelete: "cascade" }),
    permission: text("permission").notNull(),
  },
  (t) => [primaryKey({ columns: [t.roleId, t.permission] })]
);

Do NOT touch the permissions column on roles yet.

Step 2: Export rolePermissions from packages/db/src/index.ts

packages/db/src/index.ts line 40 currently does export * from "./schema.js" — rolePermissions is automatically included. No change needed.

Step 3: Build to verify

pnpm --filter @atbb/db build

Expected: clean build.

Step 4: Commit

git add packages/db/src/schema.ts
git commit -m "feat(db): add role_permissions table to Postgres schema (permissions column still present)"

Task 4: Update createDb factory for URL-based dialect detection#

Files:

Modify: packages/db/src/index.ts

Step 1: Rewrite index.ts

Replace the entire file with:

import { drizzle as drizzlePg } from "drizzle-orm/postgres-js";
import { drizzle as drizzleSqlite } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import postgres from "postgres";
import * as pgSchema from "./schema.js";
import * as sqliteSchema from "./schema.sqlite.js";

/**
 * Create a Drizzle database instance from a connection URL.
 *
 * URL prefix determines the driver:
 *   postgres:// or postgresql://  →  postgres.js (PostgreSQL)
 *   file:                         →  @libsql/client (SQLite file)
 *   file::memory:                 →  @libsql/client (SQLite in-memory, tests)
 *   libsql://                     →  @libsql/client (Turso cloud)
 */
export function createDb(databaseUrl: string): Database {
  if (databaseUrl.startsWith("postgres")) {
    return drizzlePg(postgres(databaseUrl), { schema: pgSchema }) as Database;
  }
  return drizzleSqlite(
    createClient({ url: databaseUrl }),
    { schema: sqliteSchema }
  ) as unknown as Database;
}

// Database type uses the Postgres schema as the TypeScript source of truth.
// Both dialects produce identical column names and compatible TypeScript types
// (bigint for IDs, Date for timestamps), so the cast is safe at the app layer.
export type Database = ReturnType<typeof drizzlePg<typeof pgSchema>>;

export type Transaction = Parameters<Parameters<Database["transaction"]>[0]>[0];

export type DbOrTransaction = Database | Transaction;

export * from "./schema.js";

Step 2: Build and verify

pnpm --filter @atbb/db build

Expected: clean build. The as unknown as Database cast is intentional — it bridges the Drizzle dialect types at the one boundary where we cross dialects.

Step 3: Commit

git add packages/db/src/index.ts
git commit -m "feat(db): URL-based driver detection in createDb (postgres vs SQLite)"

Task 5: Set up Drizzle config files and update package.json scripts#

Files:

Create: apps/appview/drizzle.postgres.config.ts
Create: apps/appview/drizzle.sqlite.config.ts
Modify: apps/appview/package.json
The old apps/appview/drizzle.config.ts will be deleted

Step 1: Create drizzle.postgres.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "../../packages/db/src/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Step 2: Create drizzle.sqlite.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "../../packages/db/src/schema.sqlite.ts",
  out: "./drizzle-sqlite",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Step 3: Update apps/appview/package.json scripts

Replace the two db:* scripts with four:

"db:generate":        "drizzle-kit generate --config=drizzle.postgres.config.ts",
"db:migrate":         "drizzle-kit migrate  --config=drizzle.postgres.config.ts",
"db:generate:sqlite": "drizzle-kit generate --config=drizzle.sqlite.config.ts",
"db:migrate:sqlite":  "drizzle-kit migrate  --config=drizzle.sqlite.config.ts"

Step 4: Delete the old config

rm apps/appview/drizzle.config.ts

Step 5: Verify both configs parse correctly

pnpm --filter @atbb/appview db:generate -- --dry-run 2>&1 | head -5

Expected: drizzle-kit starts without config errors (may error on DB connection which is fine for dry-run).

Step 6: Commit

git add apps/appview/drizzle.postgres.config.ts apps/appview/drizzle.sqlite.config.ts apps/appview/package.json
git rm apps/appview/drizzle.config.ts
git commit -m "feat(appview): add dialect-specific Drizzle configs and update db scripts"

Task 6: Generate and apply Postgres migration 0011 (add role_permissions table)#

Step 1: Generate the migration

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

Expected: a new file apps/appview/drizzle/0011_*.sql is created. Verify its contents — it should CREATE TABLE role_permissions and nothing else. If it also tries to DROP COLUMN permissions, STOP — the schema.ts has the permissions column removed prematurely. Fix by re-adding permissions to schema.ts before continuing.

Step 2: Apply the migration

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

Expected: role_permissions table now exists in the database.

Step 3: Verify

Connect to the database and confirm the table exists:

psql postgres://atbb:atbb@localhost:5432/atbb -c "\d role_permissions"

Expected: table with role_id (bigint), permission (text), primary key on both.

Step 4: Commit

git add apps/appview/drizzle/
git commit -m "feat(db): migration 0011 — add role_permissions table"

Task 7: Write the data migration script#

Files:

Create: apps/appview/scripts/migrate-permissions.ts

Step 1: Write the script

/**
 * One-time data migration: copies permissions from roles.permissions[]
 * into the role_permissions join table.
 *
 * Safe to re-run (ON CONFLICT DO NOTHING).
 * Must be run AFTER migration 0011 (role_permissions table exists)
 * and BEFORE migration 0012 (permissions column is dropped).
 */
import postgres from "postgres";
import { drizzle } from "drizzle-orm/postgres-js";
import * as schema from "../../packages/db/src/schema.js";
import { sql } from "drizzle-orm";

const databaseUrl = process.env.DATABASE_URL;
if (!databaseUrl) {
  console.error("DATABASE_URL is required");
  process.exit(1);
}

const client = postgres(databaseUrl);
const db = drizzle(client, { schema });

async function run() {
  // Read roles that still have permissions in the array column.
  // We use raw SQL here because the Drizzle schema will have the
  // permissions column removed by the time this script ships.
  const roles = await db.execute(
    sql`SELECT id, permissions FROM roles WHERE array_length(permissions, 1) > 0`
  );

  if (roles.length === 0) {
    console.log("No roles with permissions to migrate.");
    await client.end();
    return;
  }

  let totalPermissions = 0;

  for (const role of roles) {
    const roleId = role.id as bigint;
    const permissions = role.permissions as string[];

    if (!permissions || permissions.length === 0) continue;

    // Insert each permission as a row, skip duplicates (idempotent)
    await db.execute(
      sql`INSERT INTO role_permissions (role_id, permission)
          SELECT ${roleId}, unnest(${sql.raw(`ARRAY[${permissions.map(p => `'${p.replace(/'/g, "''")}'`).join(",")}]`)}::text[])
          ON CONFLICT DO NOTHING`
    );

    totalPermissions += permissions.length;
    console.log(`  Role ${roleId}: migrated ${permissions.length} permissions`);
  }

  console.log(
    `\nMigrated ${totalPermissions} permissions across ${roles.length} roles.`
  );
  console.log("Safe to proceed with migration 0012 (drop permissions column).");

  await client.end();
}

run().catch((err) => {
  console.error("Migration failed:", err);
  process.exit(1);
});

Step 2: Add a package.json script to run it

In apps/appview/package.json, add:

"migrate-permissions": "tsx --env-file=../../.env scripts/migrate-permissions.ts"

Step 3: Run it against your development database

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

Expected output: "Migrated N permissions across M roles." or "No roles with permissions to migrate." (both are valid depending on whether you had seeded roles).

Step 4: Verify (optional)

psql postgres://atbb:atbb@localhost:5432/atbb -c "SELECT r.name, rp.permission FROM roles r JOIN role_permissions rp ON r.id = rp.role_id ORDER BY r.name, rp.permission"

Step 5: Commit

git add apps/appview/scripts/migrate-permissions.ts apps/appview/package.json
git commit -m "feat(appview): add migrate-permissions data migration script"

Task 8: Stage 2 — Remove permissions column, generate and apply migration 0012#

Files:

Modify: packages/db/src/schema.ts

Step 1: Remove the permissions column from roles in schema.ts

In packages/db/src/schema.ts, remove the permissions line (currently line 205) from the roles table:

// REMOVE this line:
permissions: text("permissions").array().notNull().default(sql`'{}'::text[]`),

Also remove the sql import from drizzle-orm if it's no longer used anywhere else in the file. Check by searching for other `sql`` usages in schema.ts — if none, remove:

import { sql } from "drizzle-orm";

Step 2: Build to verify the schema compiles

pnpm --filter @atbb/db build

Expected: clean build.

Step 3: Generate migration 0012

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

Expected: a new apps/appview/drizzle/0012_*.sql containing only ALTER TABLE roles DROP COLUMN permissions.

Step 4: Apply migration 0012

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

Step 5: Verify

psql postgres://atbb:atbb@localhost:5432/atbb -c "\d roles"

Expected: no permissions column in the output.

Step 6: Commit

git add packages/db/src/schema.ts apps/appview/drizzle/
git commit -m "feat(db): migration 0012 — drop permissions column from roles (data already in role_permissions)"

Task 9: Generate SQLite migrations#

SQLite deployments always start clean, so a single migration contains the full schema without the permissions column.

Step 1: Generate SQLite migrations

Use an in-memory SQLite URL (no actual database needed, drizzle-kit generates from schema):

DATABASE_URL=file::memory: pnpm --filter @atbb/appview db:generate:sqlite

Expected: apps/appview/drizzle-sqlite/ directory created with 0000_*.sql containing the full schema including role_permissions, no permissions column.

Step 2: Inspect the generated migration

cat apps/appview/drizzle-sqlite/0000_*.sql

Verify: all 11 tables present, role_permissions table present, no permissions column in roles.

Step 3: Commit

git add apps/appview/drizzle-sqlite/
git commit -m "feat(db): add SQLite migrations (single clean initial migration)"

Task 10: Update checkPermission and getUserRole (TDD)#

Files:

Modify: apps/appview/src/middleware/__tests__/permissions.test.ts
Modify: apps/appview/src/middleware/permissions.ts

The current checkPermission at lines 57 and 62 uses role.permissions.includes(...). After this change, it queries rolePermissions directly. The getUserRole function at line 91 returns permissions: string[] — its return type and select must change.

Step 1: Update the existing failing tests

In permissions.test.ts, find all db.insert(roles).values({...}) calls that include a permissions field. For each, you must:

Remove permissions from the roles insert
Add a separate db.insert(rolePermissions).values(...) for each permission

Find all occurrences:

grep -n "permissions:" apps/appview/src/middleware/__tests__/permissions.test.ts

For each role insertion like:

// OLD: single insert with permissions array
await ctx.db.insert(roles).values({
  did: ctx.config.forumDid,
  rkey: "member",
  cid: "bafy...",
  name: "Member",
  permissions: ["space.atbb.permission.createPosts"],
  priority: 30,
  createdAt: new Date(),
  indexedAt: new Date(),
});

Replace with:

// NEW: role insert + separate rolePermissions inserts
const [memberRole] = await ctx.db.insert(roles).values({
  did: ctx.config.forumDid,
  rkey: "member",
  cid: "bafy...",
  name: "Member",
  priority: 30,
  createdAt: new Date(),
  indexedAt: new Date(),
}).returning({ id: roles.id });

await ctx.db.insert(rolePermissions).values([
  { roleId: memberRole.id, permission: "space.atbb.permission.createPosts" },
]);

For the wildcard Owner role test:

await ctx.db.insert(rolePermissions).values([
  { roleId: ownerRole.id, permission: "*" },
]);

Also add import { rolePermissions } from "@atbb/db"; to the test file imports.

Step 2: Run the tests to confirm they fail (permissions column no longer exists)

DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb pnpm --filter @atbb/appview test -- --reporter=verbose middleware/__tests__/permissions

Expected: compilation errors or test failures because roles.permissions no longer exists in the schema.

Step 3: Update permissions.ts

In apps/appview/src/middleware/permissions.ts:

Add rolePermissions to the import (line 4):

import { memberships, roles, rolePermissions } from "@atbb/db";

Add or to the drizzle-orm import (line 5):

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

Replace steps 4 and 5 in checkPermission (lines 56-62) with a rolePermissions query:

// 4. Check if user has the permission (wildcard or specific)
const [match] = await ctx.db
  .select()
  .from(rolePermissions)
  .where(
    and(
      eq(rolePermissions.roleId, role.id),
      or(
        eq(rolePermissions.permission, permission),
        eq(rolePermissions.permission, "*")
      )
    )
  )
  .limit(1);

return !!match; // fail-closed: undefined → false

Update getUserRole return type (line 91) — remove permissions from the return type:

async function getUserRole(
  ctx: AppContext,
  did: string
): Promise<{ id: bigint; name: string; priority: number } | null> {

Update the select inside getUserRole (lines 109-114) — remove permissions from the select:

const [role] = await ctx.db
  .select({
    id: roles.id,
    name: roles.name,
    priority: roles.priority,
  })
  .from(roles)
  .where(
    and(
      eq(roles.did, ctx.config.forumDid),
      eq(roles.rkey, roleRkey)
    )
  )
  .limit(1);

Step 4: Run the tests again

DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb pnpm --filter @atbb/appview test -- --reporter=verbose middleware/__tests__/permissions

Expected: all permissions middleware tests pass.

Step 5: Commit

git add apps/appview/src/middleware/permissions.ts apps/appview/src/middleware/__tests__/permissions.test.ts
git commit -m "feat(appview): update checkPermission and getUserRole to use role_permissions table"

Task 11: Update indexer for role records (TDD)#

Files:

Modify: apps/appview/src/lib/__tests__/indexer-roles.test.ts
Modify: apps/appview/src/lib/indexer.ts

The indexer receives role records from the AT Proto firehose. Currently it stores permissions: record.permissions in the roles table. After this change, it must:

Upsert the role row (no permissions field)
Delete existing role_permissions rows for that role (handles permission updates)
Insert new role_permissions rows

Step 1: Update the indexer tests

In indexer-roles.test.ts:

Find all assertions like expect(role.permissions).toEqual([...]) — these will now need to query rolePermissions:

// OLD
const [role] = await ctx.db.select().from(roles).where(...);
expect(role.permissions).toEqual(["space.atbb.permission.createPosts"]);

// NEW
const [role] = await ctx.db.select().from(roles).where(...);
const perms = await ctx.db
  .select({ permission: rolePermissions.permission })
  .from(rolePermissions)
  .where(eq(rolePermissions.roleId, role.id));
expect(perms.map(p => p.permission)).toEqual(["space.atbb.permission.createPosts"]);

Add import { rolePermissions } from "@atbb/db"; to the test file imports.

Step 2: Run tests to confirm they fail

DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb pnpm --filter @atbb/appview test -- --reporter=verbose lib/__tests__/indexer-roles

Expected: test failures (role.permissions no longer exists as a column).

Step 3: Find the role indexing code in indexer.ts

Search for where the indexer inserts into the roles table:

grep -n "roles\|permissions" apps/appview/src/lib/indexer.ts | head -40

Find the block that inserts/upserts a role. It likely looks like:

// OLD pattern in indexer
await ctx.db
  .insert(roles)
  .values({
    did: ...,
    rkey: ...,
    cid: ...,
    name: record.name,
    description: record.description,
    permissions: record.permissions,   // ← remove this
    priority: record.priority,
    createdAt: new Date(record.createdAt),
    indexedAt: new Date(),
  })
  .onConflictDoUpdate({ target: [roles.did, roles.rkey], set: { ... } });

Step 4: Update the role indexing code

Replace the role upsert block with a three-step operation:

// 1. Upsert the role row (no permissions field)
const [upsertedRole] = await ctx.db
  .insert(roles)
  .values({
    did: record_did,   // use whatever variable holds the record author's DID
    rkey: record_rkey, // use whatever variable holds the record key
    cid: record_cid,
    name: record.name,
    description: record.description ?? null,
    priority: record.priority,
    createdAt: new Date(record.createdAt),
    indexedAt: new Date(),
  })
  .onConflictDoUpdate({
    target: [roles.did, roles.rkey],
    set: {
      name: record.name,
      description: record.description ?? null,
      priority: record.priority,
      cid: record_cid,
      indexedAt: new Date(),
    },
  })
  .returning({ id: roles.id });

// 2. Replace all permissions for this role (handles updates)
await ctx.db
  .delete(rolePermissions)
  .where(eq(rolePermissions.roleId, upsertedRole.id));

// 3. Insert new permissions (skip if empty)
if (record.permissions && record.permissions.length > 0) {
  await ctx.db.insert(rolePermissions).values(
    record.permissions.map((permission: string) => ({
      roleId: upsertedRole.id,
      permission,
    }))
  );
}

Also add rolePermissions to the indexer's imports from @atbb/db.

Step 5: Run indexer tests

DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb pnpm --filter @atbb/appview test -- --reporter=verbose lib/__tests__/indexer-roles

Expected: all indexer role tests pass.

Step 6: Commit

git add apps/appview/src/lib/indexer.ts apps/appview/src/lib/__tests__/indexer-roles.test.ts
git commit -m "feat(appview): update indexer to store role permissions in role_permissions table"

Task 12: Update admin routes to return permissions via join (TDD)#

Files:

Modify: apps/appview/src/routes/__tests__/admin.test.ts
Modify: apps/appview/src/routes/admin.ts

The admin routes return role objects with a permissions array in the response. Since getUserRole no longer returns permissions, any code reading role.permissions from that function needs to query rolePermissions directly.

Step 1: Find all admin.ts usages of permissions

grep -n "permissions" apps/appview/src/routes/admin.ts

Look for:

Responses that include role.permissions (the GET /api/admin/roles endpoint)
The GET /api/admin/members/me endpoint that returns the caller's permissions

Step 2: Update the test — ensure role inserts include rolePermissions

In admin.test.ts, find all db.insert(roles).values({...}) calls with permissions arrays. Apply the same pattern as Task 10 — split into role insert + rolePermissions insert.

Run the failing tests first:

DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb pnpm --filter @atbb/appview test -- --reporter=verbose routes/__tests__/admin

Step 3: Add a helper to fetch a role's permissions

In admin.ts, wherever a role's permissions are needed in the response, add a helper query:

// Fetch permissions for a role by ID
async function getRolePermissions(
  ctx: AppContext,
  roleId: bigint
): Promise<string[]> {
  const perms = await ctx.db
    .select({ permission: rolePermissions.permission })
    .from(rolePermissions)
    .where(eq(rolePermissions.roleId, roleId));
  return perms.map((p) => p.permission);
}

Use this helper when building role response objects:

// Instead of: permissions: role.permissions
permissions: await getRolePermissions(ctx, role.id),

For the GET /api/admin/members/me endpoint returning the caller's permissions:

const userRole = await getUserRole(ctx, user.did);
const permissions = userRole
  ? await getRolePermissions(ctx, userRole.id)
  : [];

Add rolePermissions to admin.ts imports from @atbb/db.

Step 4: Run admin tests

DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb pnpm --filter @atbb/appview test -- --reporter=verbose routes/__tests__/admin

Expected: all admin tests pass.

Step 5: Run the full test suite to catch any remaining permissions references

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

Expected: all tests pass. If any still fail with role.permissions errors, find them with:

grep -rn "\.permissions" apps/appview/src/routes/ apps/appview/src/middleware/ apps/appview/src/lib/

Fix any remaining cases using the same rolePermissions query pattern.

Step 6: Commit

git add apps/appview/src/routes/admin.ts apps/appview/src/routes/__tests__/admin.test.ts
git commit -m "feat(appview): update admin routes to fetch permissions from role_permissions table"

Task 13: Update test context for SQLite support#

Files:

Modify: apps/appview/src/lib/__tests__/test-context.ts

The test context currently hardcodes postgres.js. It needs to detect the URL and use createDb for both dialects. For SQLite, it must run migrations programmatically before tests, and skip the manual sql.end() call (libsql clients close automatically).

Step 1: Update test-context.ts

Replace the file with this updated version (preserving all existing cleanup logic):

import { eq, or, like } from "drizzle-orm";
import { createDb } from "@atbb/db";
import { forums, posts, users, categories, memberships, boards, roles, modActions, backfillProgress, backfillErrors } from "@atbb/db";
import * as schema from "@atbb/db";
import { createLogger } from "@atbb/logger";
import path from "path";
import { fileURLToPath } from "url";
import type { AppConfig } from "../config.js";
import type { AppContext } from "../app-context.js";

const __dirname = fileURLToPath(new URL(".", import.meta.url));

export interface TestContext extends AppContext {
  cleanup: () => Promise<void>;
  cleanDatabase: () => Promise<void>;
}

export interface TestContextOptions {
  emptyDb?: boolean;
}

export async function createTestContext(
  options: TestContextOptions = {}
): Promise<TestContext> {
  const config: AppConfig = {
    port: 3000,
    forumDid: "did:plc:test-forum",
    pdsUrl: "https://test.pds",
    databaseUrl: process.env.DATABASE_URL ?? "",
    jetstreamUrl: "wss://test.jetstream",
    logLevel: "warn",
    oauthPublicUrl: "http://localhost:3000",
    sessionSecret: "test-secret-at-least-32-characters-long",
    sessionTtlDays: 7,
    backfillRateLimit: 10,
    backfillConcurrency: 10,
    backfillCursorMaxAgeHours: 48,
  };

  const db = createDb(config.databaseUrl);

  const isSqlite = !config.databaseUrl.startsWith("postgres");

  // For SQLite: run migrations programmatically before any tests
  if (isSqlite) {
    const { migrate } = await import("drizzle-orm/libsql/migrator");
    const migrationsFolder = path.resolve(__dirname, "../../../../../apps/appview/drizzle-sqlite");
    await migrate(db as any, { migrationsFolder });
  }

  const stubFirehose = {
    start: () => Promise.resolve(),
    stop: () => Promise.resolve(),
  } as any;

  const stubOAuthClient = {} as any;
  const stubOAuthStateStore = { destroy: () => {} } as any;
  const stubOAuthSessionStore = { destroy: () => {} } as any;
  const stubCookieSessionStore = { destroy: () => {} } as any;
  const stubForumAgent = null;

  const cleanDatabase = async () => {
    if (isSqlite) {
      // SQLite in-memory: delete in FK order (role_permissions cascade from roles)
      await db.delete(posts).catch(() => {});
      await db.delete(memberships).catch(() => {});
      await db.delete(users).catch(() => {});
      await db.delete(boards).catch(() => {});
      await db.delete(categories).catch(() => {});
      await db.delete(roles).catch(() => {}); // cascades to role_permissions
      await db.delete(modActions).catch(() => {});
      await db.delete(backfillErrors).catch(() => {});
      await db.delete(backfillProgress).catch(() => {});
      await db.delete(forums).catch(() => {});
      return;
    }

    // Postgres: delete by test DID patterns
    await db.delete(posts).where(eq(posts.did, config.forumDid)).catch(() => {});
    await db.delete(posts).where(like(posts.did, "did:plc:test-%")).catch(() => {});
    await db.delete(memberships).where(like(memberships.did, "did:plc:test-%")).catch(() => {});
    await db.delete(users).where(like(users.did, "did:plc:test-%")).catch(() => {});
    await db.delete(boards).where(eq(boards.did, config.forumDid)).catch(() => {});
    await db.delete(categories).where(eq(categories.did, config.forumDid)).catch(() => {});
    await db.delete(roles).where(eq(roles.did, config.forumDid)).catch(() => {}); // cascades to role_permissions
    await db.delete(modActions).where(eq(modActions.did, config.forumDid)).catch(() => {});
    await db.delete(backfillErrors).catch(() => {});
    await db.delete(backfillProgress).catch(() => {});
    await db.delete(forums).where(eq(forums.did, config.forumDid)).catch(() => {});
  };

  await cleanDatabase();

  if (!options.emptyDb) {
    await db.insert(forums).values({
      did: config.forumDid,
      rkey: "self",
      cid: "bafytest",
      name: "Test Forum",
      description: "A test forum",
      indexedAt: new Date(),
    });
  }

  const logger = createLogger({
    service: "atbb-appview-test",
    level: "warn",
  });

  return {
    db,
    config,
    logger,
    firehose: stubFirehose,
    oauthClient: stubOAuthClient,
    oauthStateStore: stubOAuthStateStore,
    oauthSessionStore: stubOAuthSessionStore,
    cookieSessionStore: stubCookieSessionStore,
    forumAgent: stubForumAgent,
    backfillManager: null,
    cleanDatabase,
    cleanup: async () => {
      const testDidPattern = or(
        eq(posts.did, "did:plc:test-user"),
        eq(posts.did, "did:plc:topicsuser"),
        like(posts.did, "did:plc:test-%"),
        like(posts.did, "did:plc:duptest-%"),
        like(posts.did, "did:plc:create-%"),
        like(posts.did, "did:plc:pds-fail-%")
      );
      await db.delete(posts).where(testDidPattern);

      const testMembershipPattern = or(
        eq(memberships.did, "did:plc:test-user"),
        eq(memberships.did, "did:plc:topicsuser"),
        like(memberships.did, "did:plc:test-%"),
        like(memberships.did, "did:plc:duptest-%"),
        like(memberships.did, "did:plc:create-%"),
        like(memberships.did, "did:plc:pds-fail-%")
      );
      await db.delete(memberships).where(testMembershipPattern);

      const testUserPattern = or(
        eq(users.did, "did:plc:test-user"),
        eq(users.did, "did:plc:topicsuser"),
        like(users.did, "did:plc:test-%"),
        like(users.did, "did:plc:duptest-%"),
        like(users.did, "did:plc:create-%"),
        like(users.did, "did:plc:pds-fail-%")
      );
      await db.delete(users).where(testUserPattern);

      await db.delete(boards).where(eq(boards.did, config.forumDid));
      await db.delete(categories).where(eq(categories.did, config.forumDid));
      await db.delete(roles).where(eq(roles.did, config.forumDid)); // cascades to role_permissions
      await db.delete(modActions).where(eq(modActions.did, config.forumDid));
      await db.delete(backfillErrors).catch(() => {});
      await db.delete(backfillProgress).catch(() => {});
      await db.delete(forums).where(eq(forums.did, config.forumDid));

      // Close connection — only needed for postgres.js
      if (!isSqlite) {
        // Access the underlying postgres client via the db instance internals
        // The createDb function owns the client lifecycle; for tests we accept
        // that postgres connections close when the process exits.
        // If connection leak warnings appear, add explicit client tracking to createDb.
      }
    },
  } as TestContext;
}

Note on postgres connection cleanup: The old test context held a sql reference to call sql.end(). Since createDb now owns the client, tests rely on process exit to close Postgres connections. If this causes "too many connections" errors in CI, refactor createDb to return { db, close: () => Promise<void> } and thread close() through to the test context cleanup.

Step 2: Run the full test suite with Postgres

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

Expected: all tests pass.

Step 3: Run the full test suite with SQLite

DATABASE_URL=file::memory: pnpm --filter @atbb/appview test

Expected: all tests pass. If any tests fail due to SQLite behavior differences (e.g., boolean handling, BigInt serialization), fix them individually — the errors will be self-descriptive.

Step 4: Commit

git add apps/appview/src/lib/__tests__/test-context.ts
git commit -m "feat(appview): update test context to support SQLite in-memory testing"

Task 14: Create docker-compose.sqlite.yml#

Files:

Create: docker-compose.sqlite.yml

Step 1: Write the compose file

# SQLite deployment — no external database required.
# Data is persisted via a named volume at /data/atbb.db inside the container.
#
# Usage:
#   docker compose -f docker-compose.sqlite.yml up
#
# The existing docker-compose.yml (PostgreSQL) is unchanged.
services:
  appview:
    build: .
    environment:
      DATABASE_URL: file:/data/atbb.db
      NODE_ENV: production
    env_file:
      - .env
    volumes:
      - atbb_data:/data
    ports:
      - "80:80"
    restart: unless-stopped

volumes:
  atbb_data:
    driver: local

Step 2: Commit

git add docker-compose.sqlite.yml
git commit -m "feat: add docker-compose.sqlite.yml for SQLite deployments"

Task 15: Update nix/module.nix#

Files:

Modify: nix/module.nix

Step 1: Read the current module.nix

Read nix/module.nix in full to find the exact location of:

The database.enable option definition
The database.name option definition
The atbb-migrate service script
The atbb-appview environment block
The services.postgresql block

Step 2: Add database.type and database.path options

Find the database attribute set in the options block and add two new options alongside the existing ones:

type = lib.mkOption {
  type = lib.types.enum [ "postgresql" "sqlite" ];
  default = "postgresql";
  description = "Database backend. Use 'sqlite' for embedded single-file storage without a separate PostgreSQL service.";
};

path = lib.mkOption {
  type = lib.types.path;
  default = "/var/lib/atbb/atbb.db";
  description = "Path to the SQLite database file. Only used when database.type = \"sqlite\".";
};

Step 3: Make database.enable default to true only for PostgreSQL

Find the database.enable option and update its default:

enable = lib.mkOption {
  type = lib.types.bool;
  default = cfg.database.type == "postgresql";
  description = "Enable local PostgreSQL 17 service. Ignored when database.type = \"sqlite\".";
};

Step 4: Make the DATABASE_URL conditional in atbb-appview

Find the environment block in the atbb-appview systemd service and update the DATABASE_URL line (currently hardcoded to postgres):

DATABASE_URL = if cfg.database.type == "sqlite"
  then "file:${cfg.database.path}"
  else "postgres:///atbb?host=/run/postgresql";

Step 5: Add StateDirectory for SQLite file persistence

In the serviceConfig block of atbb-appview, add:

StateDirectory = lib.mkIf (cfg.database.type == "sqlite") "atbb";

This creates /var/lib/atbb/ and makes it writable by the service user.

Step 6: Make the atbb-migrate script dialect-aware

Find the atbb-migrate oneshot service and update its script:

script = if cfg.database.type == "sqlite"
  then "${atbb}/bin/atbb db:migrate:sqlite"
  else "${atbb}/bin/atbb db:migrate";

Step 7: Make PostgreSQL conditional

Find the services.postgresql block and wrap it:

services.postgresql = lib.mkIf (cfg.database.type == "postgresql" && cfg.database.enable) {
  # ... existing content unchanged
};

Step 8: Commit

git add nix/module.nix
git commit -m "feat(nix): add database.type option to NixOS module (postgresql | sqlite)"

Task 16: Update nix/package.nix#

Files:

Modify: nix/package.nix

Step 1: Find the drizzle migrations copy in installPhase

Read nix/package.nix and find the line that copies the drizzle directory (search for drizzle).

Step 2: Add drizzle-sqlite alongside the existing copy

Find the line (approximately):

cp -r apps/appview/drizzle $out/apps/appview/drizzle

Add immediately after:

cp -r apps/appview/drizzle-sqlite $out/apps/appview/drizzle-sqlite

Step 3: Commit

git add nix/package.nix
git commit -m "feat(nix): include SQLite migrations in Nix package output"

Task 17: Update devenv.nix for optional PostgreSQL#

Files:

Modify: devenv.nix

Step 1: Wrap the postgres service in mkDefault

Read devenv.nix and find the services.postgres block. Change enable = true (or the implicit enablement) to use lib.mkDefault:

services.postgres = {
  enable = lib.mkDefault true;
  # ... rest of existing config unchanged
};

Step 2: Add a comment explaining the SQLite override

Add a comment above the services.postgres block:

# PostgreSQL is enabled by default for development.
# To use SQLite instead, create devenv.local.nix with:
#   { ... }: { services.postgres.enable = false; }
# Then set DATABASE_URL=file:./data/atbb.db in your .env file.

Step 3: Update .env.example to document the SQLite option

In .env.example, add a comment to the DATABASE_URL line:

# PostgreSQL (default, used with devenv):
DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb

# SQLite alternative (no devenv postgres needed):
# DATABASE_URL=file:./data/atbb.db
# DATABASE_URL=file::memory:   # in-memory, for tests only

Step 4: Commit

git add devenv.nix .env.example
git commit -m "feat(devenv): make postgres optional via mkDefault, document SQLite alternative"

Task 18: Final verification#

Step 1: Run the full test suite with Postgres

DATABASE_URL=postgres://atbb:atbb@localhost:5432/atbb pnpm test

Expected: all tests pass.

Step 2: Run the full test suite with SQLite in-memory

DATABASE_URL=file::memory: pnpm test

Expected: all tests pass. This is the proof that the abstraction works end-to-end.

Step 3: Verify TypeScript builds cleanly

pnpm build

Expected: clean build with no TypeScript errors.

Step 4: Smoke test SQLite file deployment

DATABASE_URL=file:./data/atbb.db pnpm --filter @atbb/appview db:migrate:sqlite
DATABASE_URL=file:./data/atbb.db pnpm --filter @atbb/appview dev

Expected: server starts, migrations run, /api/healthz returns 200.

Step 5: Clean up the data directory

rm -rf ./data/

Step 6: Final commit

git add .
git commit -m "feat: SQLite support complete — dual-dialect database with role_permissions join table"

Operator upgrade instructions (reference)#

See docs/plans/2026-02-24-sqlite-support-design.md → "Operator Migration Instructions" for the three-step Postgres upgrade procedure and NixOS-specific instructions. The key safety rule: run migrate-permissions between applying migration 0011 and migration 0012.

Configure Feed

Configure Feed

SQLite Support Implementation Plan#

Important notes before starting#

Task 1: Add @libsql/client to packages/db#

Task 2: Create packages/db/src/schema.sqlite.ts#

Task 3: Stage 1 — Add rolePermissions to Postgres schema (keep permissions column)#

Task 4: Update createDb factory for URL-based dialect detection#

Task 5: Set up Drizzle config files and update package.json scripts#

Task 6: Generate and apply Postgres migration 0011 (add role_permissions table)#

Task 7: Write the data migration script#

Task 8: Stage 2 — Remove permissions column, generate and apply migration 0012#

Task 9: Generate SQLite migrations#

Task 10: Update checkPermission and getUserRole (TDD)#

Task 11: Update indexer for role records (TDD)#

Task 12: Update admin routes to return permissions via join (TDD)#

Task 13: Update test context for SQLite support#

Task 14: Create docker-compose.sqlite.yml#

Task 15: Update nix/module.nix#

Task 16: Update nix/package.nix#

Task 17: Update devenv.nix for optional PostgreSQL#

Task 18: Final verification#

Operator upgrade instructions (reference)#