Database & Migrations

A Rome app can save data in SQL tables managed with Drizzle ORM. The database layer is optional — delete it all if your app doesn't need to store anything — but when it's there it follows a set storage model, a migration flow run by drizzle-kit, and a repository pattern that keeps SQL out of your actions and API handlers.

Storage model

One shared SQLite file

App data is not written to its own SQLite database. Every app table lives in the same system SQLite file:

~/.rome/<profile>/rome.db

Apps never open this file themselves. The runtime hands each app its own Drizzle connection through appContext.db, typed as AppDbContext:

export type DrizzleDb = BetterSQLite3Database<Record<string, never>>;

export interface AppDbContext {
  connection: DrizzleDb;
  tablePrefix: string;
  tableName(name: string): string;
}

connection is the Drizzle handle you run queries against. tablePrefix is your app's name space. tableName(name) joins the two: it turns a logical name into its real, prefixed table name.

Keeping tables apart with tablePrefix

Because all apps share one file, table names must not clash. Each app sets a tablePrefix (usually derived from its appId) in app.yaml:

db:
  migrations: db/migrations
  tablePrefix: myapp           # typically equals the appId

The db.migrations path is resolved under the app's appRoot (typically dist), so the build copies src/db/migrations/ into the packaged output; the installer rejects the app if <appRoot>/db/migrations/meta/_journal.json is missing.

Real table names are built as <tablePrefix>__<logical_name> — the prefix and the logical name joined by two underscores. So a logical notes table for the myapp app is really named myapp__notes.

The migration history table (__drizzle_migrations_app_<tablePrefix>) is named the same way, so your migration records stay apart from other apps' and from the system's.

DB tables vs. the app's data folder

The table prefix only covers SQL tables. File data that isn't in tables — caches, uploads, generated files — belongs in the app's data folder, a separate path:

~/.rome/<profile>/apps/data/<appId>/

This folder has nothing to do with the table prefix. When the app is removed with system:app_management { op: "uninstall", purge: true }, the daemon drops the <tablePrefix>__* tables and the migration table and deletes apps/data/<appId>/.

Schema and migrations

Define the schema with Drizzle

Tables are declared in src/db/schema.ts using Drizzle's sqlite-core builders. The example below is the run-history table from the morning-brief example app. It wraps the definition in a factory so the tablePrefix is added when the schema is built:

// src/db/schema.ts
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export function createAppDbSchema(tablePrefix: string = "morning_brief") {
  const runs = sqliteTable(`${tablePrefix}__runs`, {
    id: text("id").primaryKey(),
    status: text("status", { enum: ["running", "success", "error"] }).notNull(),
    dryRun: integer("dry_run", { mode: "boolean" }).notNull(),
    input: text("input", { mode: "json" }),
    result: text("result", { mode: "json" }),
    error: text("error"),
    startedAt: integer("started_at", { mode: "timestamp" }).notNull(),
    finishedAt: integer("finished_at", { mode: "timestamp" }),
    durationMs: integer("duration_ms"),
  });

  return { runs };
}

const defaultSchema = createAppDbSchema();
export const runs = defaultSchema.runs;

Notes on the column patterns above:

• text(..., { mode: "json" }) stores any JSON value (the column is read and written as a parsed object).
• integer(..., { mode: "boolean" }) and integer(..., { mode: "timestamp" }) store booleans and Dates in integer columns.
• text(..., { enum: [...] }) limits a column to a fixed set of string values at the type level.

The daemon never rewrites your migration SQL — migrations run verbatim, so the schema itself must produce the prefixed name. The factory pattern above, writing ${tablePrefix}__<name> right into the table name string, is the standard way: the generated SQL then matches the real name directly. A schema with bare logical names would create unprefixed tables the daemon doesn't own — purge only drops <tablePrefix>__* tables, so they'd be left behind. For raw SQL at runtime, appContext.db.tableName(name) joins a logical name into its prefixed form.

drizzle.config.ts

The generator reads a drizzle.config.ts at the app root. Point it at your schema, the folder migrations are written to, the app's own migration table, and a tablesFilter set to your prefix so drizzle-kit ignores every other app's tables:

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./src/db/migrations",
  dialect: "sqlite",
  migrations: { table: "__drizzle_migrations_app_myapp" },
  tablesFilter: ["myapp__*"],
});

Generate migrations with pnpm db:generate

The app template's package.json ships a db:generate script:

{
  "scripts": {
    "db:generate": "drizzle-kit generate"
  }
}

Run it from the app folder whenever you change schema.ts:

pnpm db:generate

This writes a new migration to src/db/migrations/ — a .sql file plus an updated meta/ snapshot. Two rules:

• Rerun pnpm db:generate after every schema change. A schema edit without a fresh migration will not reach the database.
• Never hand-edit existing migration SQL. Treat generated migrations as history you only add to; just add new files.

Auto-migrate on install

There is no manual migrate step. When the app is installed (or upgraded) via system:app_management, the daemon runs any pending migrations for the app on its own, tracked in the app's own __drizzle_migrations_app_<tablePrefix> table.

If the app doesn't need to store anything, remove the whole layer together: delete src/db/, drizzle.config.ts, the db: block in app.yaml, the drizzle-* dependencies, and the db:generate script from package.json.

Repository pattern

Keep all data access in one place, behind repositories in src/db/repositories/. Pull the Drizzle connection from appContext.db and wrap each query in a named method. Don't write raw SQL inside actions or API handlers — they call repository methods only.

// src/db/repositories/notes.ts
import type { DrizzleDb } from "@rome-os/app-runtime";
import { eq } from "drizzle-orm";
import { notes } from "../schema.js";

export function createNotesRepository(db: DrizzleDb) {
  return {
    list: () => db.select().from(notes).all(),
    byId: (id: string) => db.select().from(notes).where(eq(notes.id, id)).get(),
    insert: (row: typeof notes.$inferInsert) => db.insert(notes).values(row).run(),
  };
}

Here db is the Drizzle connection (appContext.db.connection). Build the repository once with that connection, then call its methods from your action execute bodies and API routes:

async execute(args): Promise<ActionResult> {
  const notesRepo = createNotesRepository(appContext.db.connection);
  const all = notesRepo.list();
  return { status: "ok", data: all };
}

Why repositories

• No SQL at the call sites. Actions and handlers say what they want (notesRepo.byId(id)), not how the query works. Schema changes touch one file.
• Types come from the schema. typeof notes.$inferInsert and $inferSelect keep row types in step with the table definition.
• The same everywhere. API handlers get the same context shape (ctx.db, ctx.repositories) as actions, so the same repositories serve both.

The platform also offers a small set of built-in repositories on appContext.repositories (for example, settings for key/value app settings). Use those for what they cover, and add your own under src/db/repositories/ for your app's tables.