SQLite Support Design#

Date: 2026-02-24 Status: Approved, pending implementation plan

Motivation#

Adding SQLite as a fully supported database backend alongside PostgreSQL serves two audiences:

• Self-hosters who want to run atBB on a single server without operating a separate PostgreSQL service
• Developers and agents who want to spin up, test, and discard a local database without any external infrastructure

The implementation is transparent to all application code above the createDb factory — route handlers, middleware, and services remain unaware of which database they are talking to.

Scope#

SQLite is a full production deployment target, not only a development convenience. Both PostgreSQL and SQLite are equally supported. Operators choose their backend at deploy time via DATABASE_URL.

Architecture Overview#

The URL prefix in DATABASE_URL is the single decision point. Everything below createDb is dialect-specific; everything above it is identical for both backends.

DATABASE_URL=postgres://...        →  postgres.js driver  →  Drizzle (PostgreSQL dialect)
DATABASE_URL=file:./data/atbb.db   →  @libsql/client      →  Drizzle (LibSQL dialect)
DATABASE_URL=file::memory:         →  @libsql/client      →  Drizzle (LibSQL in-memory, tests)
DATABASE_URL=libsql://...          →  @libsql/client      →  Drizzle (Turso cloud, no code changes)

File layout changes#

packages/db/
  src/
    schema.ts              ← PostgreSQL schema (updated: permissions column removed)
    schema.sqlite.ts       ← SQLite schema (new: identical structure, dialect-specific column types)
    index.ts               ← createDb() with URL-prefix detection (updated)

apps/appview/
  drizzle/                 ← PostgreSQL migrations (existing + two new migrations)
  drizzle-sqlite/          ← SQLite migrations (new, single clean initial migration)
  drizzle.postgres.config.ts   ← renamed from drizzle.config.ts
  drizzle.sqlite.config.ts     ← new
  scripts/
    migrate-permissions.ts ← one-time data migration script (new)

Schema Changes#

New role_permissions join table#

The permissions text[] array column in roles is replaced by a dedicated role_permissions table. This is a data-model improvement independent of SQLite support: it adds referential integrity (cascade delete when a role is removed) and makes "find all roles with permission X" queries natural.

Both schema files (schema.ts and schema.sqlite.ts) define this table. The structure is identical; only the Drizzle column helper syntax differs by dialect.

// Postgres (schema.ts)
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] }),
]);

// SQLite (schema.sqlite.ts)
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] }),
]);

The permissions column is removed from roles in both schema files.

Dialect differences between the two schema files#

Both dialects use mode: "bigint" for integer IDs and mode: "date" / mode: "timestamp" for dates, so TypeScript types are identical (bigint for IDs, Date for timestamps). The Database union type works without narrowing in route handlers.

Impact on checkPermission#

The current role.permissions.includes(permission) pattern is replaced by a targeted existence query — more efficient since it avoids fetching all permissions to check one:

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

All other permission-related queries (listing a role's permissions, seeding default roles) are updated to use join or relational queries against role_permissions.

createDb Factory#

packages/db/src/index.ts becomes:

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";

export function createDb(databaseUrl: string) {
  if (databaseUrl.startsWith("postgres")) {
    return drizzlePg(postgres(databaseUrl), { schema: pgSchema });
  }
  return drizzleSqlite(
    createClient({ url: databaseUrl }),
    { schema: sqliteSchema }
  );
}

export type Database = ReturnType<typeof createDb>;
export type Transaction = Parameters<Parameters<Database["transaction"]>[0]>[0];
export type DbOrTransaction = Database | Transaction;

AppContext is unchanged — db: Database continues to work for both backends.

Drizzle Configuration#

The existing apps/appview/drizzle.config.ts is renamed and a sibling added:

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

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

apps/appview/package.json gains dialect-scoped scripts:

"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"

Migration Strategy#

PostgreSQL (existing deployments)#

The permissions text[] column cannot be dropped atomically with data preservation. The migration is split into three ordered steps:

Step 1 — Schema migration: add role_permissions table

apps/appview/drizzle/0011_add_role_permissions.sql
  CREATE TABLE role_permissions (role_id bigint, permission text, PRIMARY KEY (role_id, permission))
  -- permissions column is NOT dropped here

Step 2 — Data migration script (must run between steps 1 and 3)

apps/appview/scripts/migrate-permissions.ts
  SELECT id, permissions FROM roles WHERE permissions != '{}'
  → INSERT INTO role_permissions (role_id, permission) VALUES (...)
     ON CONFLICT DO NOTHING   ← idempotent, safe to re-run
  → Prints: "Migrated N permissions across M roles"

Step 3 — Schema migration: drop the now-redundant column

apps/appview/drizzle/0012_drop_permissions_column.sql
  ALTER TABLE roles DROP COLUMN permissions

SQLite (fresh deployments only)#

SQLite deployments always start clean. The drizzle-sqlite/ folder contains a single initial migration that includes role_permissions from the start and has no permissions column. No data migration script is needed for SQLite.

Docker Deployment#

The existing docker-compose.yml (PostgreSQL) is unchanged. A new compose file enables SQLite deployments without a database service:

# docker-compose.sqlite.yml
services:
  appview:
    build: .
    environment:
      DATABASE_URL: file:/data/atbb.db
    volumes:
      - atbb_data:/data
    ports:
      - "80:80"

volumes:
  atbb_data:

The Dockerfile needs no changes — the runtime binary is identical for both backends.

NixOS Module Changes (nix/module.nix)#

A new database.type option is added alongside the existing database.enable:

database = {
  enable = mkOption {
    type = types.bool;
    default = cfg.database.type == "postgresql";
    description = "Enable local PostgreSQL service (ignored for SQLite).";
  };
  type = mkOption {
    type = types.enum [ "postgresql" "sqlite" ];
    default = "postgresql";
    description = "Database backend. Use 'sqlite' for embedded single-file storage.";
  };
  name = mkOption { ... };  # unchanged, PostgreSQL only
  path = mkOption {
    type = types.path;
    default = "/var/lib/atbb/atbb.db";
    description = "Path to SQLite database file. Only used when database.type = 'sqlite'.";
  };
};

The atbb-appview service environment becomes conditional:

environment = {
  DATABASE_URL = if cfg.database.type == "sqlite"
    then "file:${cfg.database.path}"
    else "postgres:///atbb?host=/run/postgresql";
  # ...other env vars unchanged
};
serviceConfig = {
  StateDirectory = mkIf (cfg.database.type == "sqlite") "atbb"; # creates /var/lib/atbb/
  # ...
};

The atbb-migrate oneshot service runs the appropriate migration script:

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

The PostgreSQL system service is conditionally enabled:

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

Existing module users default to database.type = "postgresql", so no existing NixOS configurations require changes.

Nix Package Changes (nix/package.nix)#

The drizzle-sqlite/ migrations directory must be included in the package alongside the existing drizzle/ directory:

# In installPhase, alongside existing drizzle copy:
cp -r apps/appview/drizzle      $out/apps/appview/drizzle
cp -r apps/appview/drizzle-sqlite $out/apps/appview/drizzle-sqlite  # new

devenv Changes (devenv.nix)#

The PostgreSQL service is made optional via a devenv input flag, allowing developers using SQLite to skip the database service entirely:

{ config, lib, pkgs, ... }: {
  # Postgres service — disable if using SQLite locally
  services.postgres = {
    enable = lib.mkDefault true;
    # ...existing config unchanged
  };
}

Developers opting into SQLite locally set in their .env:

DATABASE_URL=file:./data/atbb.db

and can disable the devenv Postgres service by overriding services.postgres.enable = false in a local devenv.local.nix (devenv supports this pattern via devenv.local.nix overrides).

Tests#

The test context detects the URL and selects the appropriate setup:

// test-context.ts
if (config.databaseUrl.startsWith("postgres")) {
  // existing postgres.js path — unchanged
} else {
  // SQLite: run migrations programmatically (no external process needed)
  const { migrate } = await import("drizzle-orm/libsql/migrator");
  await migrate(db, { migrationsFolder: resolvedDrizzleSqlitePath });
  // No manual cleanup needed — in-memory DB is discarded with the process
}

Developers without a running Postgres instance run the full test suite with:

DATABASE_URL=file::memory: pnpm test

CI continues to use PostgreSQL via DATABASE_URL=postgres://....

Operator Migration Instructions#

Fresh SQLite installs (new deployments)#

No special steps required. Run the standard migration command:

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

Or if using the NixOS module with database.type = "sqlite", the atbb-migrate service handles this automatically on startup.

Existing PostgreSQL deployments upgrading to role_permissions#

Warning: Steps must be run in order. Running step 3 before step 2 will permanently destroy all role permission data.

# Step 1: Add role_permissions table (does NOT drop permissions column yet)
pnpm --filter @atbb/appview db:migrate

# Step 2: Copy existing permission data into the new table
#         MUST run before step 3. Safe to re-run if interrupted.
pnpm --filter @atbb/appview migrate-permissions
# Verify output: "Migrated N permissions across M roles"
# Do not proceed to step 3 if N is unexpectedly 0 and you had role permissions.

# Step 3: Drop the now-redundant permissions column
pnpm --filter @atbb/appview db:migrate

Using the NixOS module: If autoMigrate is enabled, steps 1 and 3 run automatically on service restart. You must run step 2 manually between the two restarts:

# 1. Deploy the new build (runs step 1 automatically on atbb-migrate)
# 2. SSH into the server and run the data migration:
sudo -u atbb pnpm --filter @atbb/appview migrate-permissions
# 3. Restart the service (runs step 3 automatically on atbb-migrate)
sudo systemctl restart atbb-appview

Switching an existing PostgreSQL deployment to SQLite#

This requires a full data export and import — there is no automated migration path between backends. Export your data first using the atBB backup tooling (to be documented separately), then set up a fresh SQLite deployment and import. This is an unusual operation and only makes sense for very small deployments.