The one SQLite database that lives at <repo>/.procni/procni.db for a given project: opening it, migrating its schema, and giving the rest of the codebase a single typed entry point (openDb) to query it through. Nothing in Procni talks to better-sqlite3 directly except this module.
Why SQLite, and one file per project. A developer running procni init should not need Postgres, Docker, or a running database server just to try the tool — SQLite is a library, not a service. One database file per project (not one shared global database) matches the mental model "the repo is the unit of work": .procni/procni.db is exactly the generated state for this project, rm -rf .procni is a complete, obvious uninstall, and no query anywhere needs a project_id column to scope itself.
Why better-sqlite3, not the built-in node:sqlite. Node ships a built-in SQLite module now, which would remove a native-module dependency entirely — genuinely tempting. It was passed over for one concrete reason: on Node 22 (the LTS this project targets, see the root engines field), node:sqlite is still flagged experimental; it only reaches release-candidate stability on newer Node lines. better-sqlite3 is the boring, battle-tested choice today. This is a real dependency, not a permanent one — worth revisiting once Procni's minimum supported Node version catches up to a stable node:sqlite, at which point this file is the only one that would need to change.
Why better-sqlite3 specifically, not an async driver. better-sqlite3's API is fully synchronous — db.prepare(sql).run(...) returns immediately, no await. That sounds like a step backward until you notice Procni's server handles one developer's requests against one local file, not concurrent traffic across a network — there's no I/O-bound scenario here where blocking the event loop for a sub-millisecond local disk read actually costs anything, and synchronous code is dramatically simpler to write correctly (no accidentally-unawaited queries, no interleaved transaction bugs).
Why Kysely, not a full ORM. The schema is 8 tables (see schema.ts and the migration below) — small enough that an ORM's model classes, decorators, and migration DSL would be more ceremony than the problem calls for. Kysely is a query builder: it gives fully-typed db.selectFrom("content_drafts").where(...) autocomplete and compile errors on typos, but every query still reads as SQL, which matters when the whole point of this codebase is to be legible to someone learning how it's built.
Why a hand-rolled migration runner instead of a framework like golang-migrate or umzug. migrate.ts is ~35 lines: create a schema_migrations ledger table if missing, read .sql files from migrations/ in filename order, run each one not yet in the ledger inside a transaction. That's the entire feature set a project this size needs, and it's short enough to read start-to-finish in the time it'd take to learn a migration framework's conventions. This is a deliberate, documented trade-off — if migrations ever need to run down as well as up, or need to be generated from a diff, that's the point to reach for a real framework instead of growing this one.
Why schema.ts is hand-written instead of generated. Some Kysely setups use kysely-codegen to generate the DB interface from a live database. At 8 tables, hand-writing schema.ts to mirror migrations/0001_init.sql is simple enough to keep in sync by eye, and avoids a codegen step in the dev loop. If the schema grows large enough that the two drift, that's the signal to add codegen — not before.
migrations/0001_init.sql— the whole V1 schema in one migration:commits,analysis_runs,content_opportunities,content_drafts,assets,generation_jobs,provider_configs,publications. Read this file's comments alongside the project plan's domain-model section to see why it's these 8 tables and not the larger candidate list (e.g.WorkSessioncollapsed intoanalysis_runs,Settingsdeliberately kept out of SQLite and intoconfig.tomlinstead — see../config/README.md).schema.ts— the KyselyDBinterface, one*Tabletype per table, column-for-column matching the migration.Generated<T>marks columns SQLite fills in for you (autoincrementid,DEFAULT-valued columns) so Kysely knows they're optional on insert but always present on select.migrate.ts—runMigrations(db). Notice it's idempotent: run it against a database that's already up to date and it does nothing (checked explicitly inmigrate.test.ts), which is what makes it safe foropenDbto call on every single startup rather than requiring a separate "migrate" step a developer has to remember to run.client.ts—openDb(filePath), the one function everything else calls: opens the file, turns on WAL mode (better crash-safety, and lets a read happen while a write is in flight), migrates, returns aKysely<DB>.
Update (Phase 1): repositories/ now exists — analysisRuns.ts, contentOpportunities.ts, and generationJobs.ts, built once ../pipeline/runner.ts and ../jobs/worker.ts became real callers needing to persist and query those tables. This is the follow-through on the deferral described above, not a change of plan: at the time, content_drafts, assets, provider_configs, and publications still had no repository — they were waiting for Phase 2's Draft Generation stage and provider-settings work, the same way the first three waited for Phase 1. Each repository maps snake_case DB columns to camelCase row types at the boundary (see toAnalysisRunRow etc.) and JSON-serializes/parses the *_json columns, so nothing outside db/ ever handles a raw SQLite row or a JSON string it has to remember to parse itself.
Update (Phase 2): contentDrafts.ts and publications.ts joined the same directory once ../pipeline/generateDraft.ts needed to persist drafts and the review UI needed to record copy/export actions. provider_configs still has no repository — it's waiting on provider-settings UI/API work that hasn't started. Two things worth knowing about contentDrafts.ts specifically:
insertContentDraft'sis_currentbookkeeping runs inside a transaction. WhenrevisionOfis given, the draft being revised is flipped tois_current = 0in the same transaction as the new row's insert — not as two separate calls a caller has to remember to sequence correctly. This is what guaranteescontent_draftsnever has two current rows for one opportunity, checked directly incontentDrafts.test.tsand again, end-to-end, ingenerateDraft.test.ts's regeneration case.- Ordering ties get an explicit
id desctiebreaker in bothlistContentOpportunitiesandlistPublicationsForDraft— not a hypothetical concern:publications.test.ts's own test inserted two rows fast enough to land in the same millisecond (exported_at's resolution) and got a flaky, order-dependent failure on first write.ORDER BY <timestamp> DESCalone doesn't guarantee insertion order on a tie; the rowid(always monotonically increasing) does.
Update (Phase 3): updateContentDraftBody was added once the review UI needed a developer to be able to edit a draft's text directly, not just regenerate it wholesale. It's a plain field update, not a revision — editing in place doesn't go through insertContentDraft's revisionOf bookkeeping, since an edit isn't "a new draft that supersedes the old one" the way a regeneration is. updateContentOpportunityStatus (added in Phase 2 for the "drafted" transition) turned out to already be exactly what dismiss/restore needed too — "dismissed" and "new" are just two more values in the same status column, so no new repository code was needed there at all.
Update (Phase 3, revision history): listContentDraftsForOpportunity returns every draft ever generated, edited, or restored for an opportunity — newest first, with the same id desc tiebreaker described above. It's what apps/server's GET /api/v1/opportunities/:id/drafts and the web dashboard's History panel are built on. "Restoring" an old revision is deliberately not a new repository function: apps/server/src/routes/drafts.ts's restore route just calls the existing insertContentDraft with the old row's content and revisionOf pointing at whatever is current right now — the same call a regeneration makes. This keeps the invariant insertContentDraft already enforces (at most one current row per opportunity) automatically true for restores too, and means the full history — generate → edit → regenerate → restore — stays as one real, inspectable chain of rows instead of a restore quietly rewriting or deleting anything.
Update (multi-platform drafts): "current" stopped being scoped to just an opportunity and became scoped to (opportunity, platform) once the review UI grew independent tabs per platform (X/Twitter, LinkedIn, Blog, Changelog — see web/README.md) — each tab generates and revises its own draft, and two platforms' regenerations must never clobber each other's revisionOf chain. Concretely:
getCurrentDraftForOpportunitynow takes a requiredplatformargument and filters on it — a plain "give me the current draft" with no platform doesn't mean anything once an opportunity can have up to one current draft per platform at once.listCurrentDraftsForOpportunityis new: every platform's current draft at once (0 to one-per-platform), what the web UI fetches once on load to populate all four tabs in a single request instead of one per tab.listContentDraftsForOpportunity(history) grew an optionalplatformfilter, so a tab's History panel shows only that platform's chain rather than every platform's revisions interleaved.
insertContentDraft itself didn't need to change — it was already scoped to whatever revisionOf id a caller passed in, never to "all drafts for this opportunity." The platform-scoping work was entirely in what callers look up before calling it (see ../pipeline/README.md's note on generateDraft moving its existing-draft lookup to after Content Strategy resolves a concrete platform).
Update (custom opportunities): a developer can now create a content_opportunities row by hand — from the web UI's file-search Compose flow (or, for a topic-only quick post with no file context at all, the home page's quick composer) — instead of every row coming from an automated analysis run. No schema change and no new repository function needed for this, since insertContentOpportunities was already a generic insert: apps/server/src/routes/opportunities.ts's standalone POST /api/v1/opportunities/custom route calls it with storyType: "custom" (a plain string in an untyped TEXT column — story_type was never constrained to OPPORTUNITY_STORY_TYPES, that union only governs what the AI ranking stage is allowed to output) and a fixed high score so a developer's own explicit choice sorts near the top rather than behind every auto-ranked opportunity. Unlike an earlier version of this feature, a custom opportunity is no longer scoped to an existing opportunity's evidence — it gets its own fresh analysis_runs row (triggeredBy: "manual"), since a file-search or topic-only post isn't derived from any prior run.
Update (home page activity/recent-posts): publications.ts gained two read-only queries once the home page needed them — listPublicationActivity (every publication's {exportedAt, platform}, for the GitHub-style activity graph to bucket by day client-side) and listRecentPublications (the same rows joined with content_drafts/content_opportunities for a body preview and title, for the recent-posts feed). Both are plain joins with no new tables — "published" in this project has only ever meant a publications row (a copy/export action a developer actually took), so the home page's definition of "did I publish today" is the exact same signal the rest of the app already uses, not a new concept.
Update (idempotent publications, first real second migration): clicking Copy more than once on the same draft used to insert a new publications row every time, inflating the activity graph and recent-posts feed with duplicates of the exact same event — caught live, against this project's own real dogfooded database. migrations/0002_publications_idempotent.sql is the schema's first migration after the initial one: it de-duplicates any pre-existing repeat rows (keeping the earliest per (draft_id, action) — that's the real "first published" moment) and then adds a unique index on those two columns. recordPublication upserts against it (onConflict().doNothing(), falling back to re-selecting the existing row) rather than erroring or minting a duplicate, so a repeat click is a safe no-op that returns the original row unchanged — including its original exportedAt, so a much-later re-copy doesn't shift which day it counts toward.
pnpm --filter @procni/core test -- dbmigrate.test.ts runs runMigrations against a real in-memory SQLite database (new BetterSqlite3(":memory:")) and asserts every expected table exists, and that running it twice doesn't error. client.test.ts goes one step further and exercises openDb against a real temporary file on disk, doing an actual typed insert-then-select round trip through Kysely and checking PRAGMA journal_mode reports wal — proving the whole stack (file → migration → typed query) works together, not just that each piece compiles.
One environment note worth knowing if pnpm install ever seems to silently produce a broken better-sqlite3: pnpm's default security posture skips dependencies' native build/postinstall scripts unless explicitly allow-listed. This is already handled at the workspace root — see pnpm.onlyBuiltDependencies in the root package.json — so a normal pnpm install builds it automatically; you shouldn't need to think about this, but it's the answer if openDb ever throws a "bindings not found" error on a fresh machine.
Depends on nothing else in this repo. Depended on by procni init/procni dev (to open and migrate the project database) and by ../pipeline and ../jobs, which persist and query analysis_runs, content_opportunities, content_drafts, generation_jobs, and publications rows through this module's repositories.