ADR-005: Mainark's control plane moves to Postgres

Date: 2026-10-10
Status: Accepted (2026-10-10); amended 2026-10-10 (the Supabase plan stays Free; P1 is a recovery gate, not a Pro upgrade). Implementation not started; cutover prerequisites P1–P4 outstanding (Migration sequence)
Supersedes: ADR-001 storage choice and its offline requirement; the conclusion of the backend spike ("SQLite prior stands")
Amends: ADR-004 (one database per Product, storage names, I4/I8/I9/I10 enforcement, phase order), ADR-003 (DDL dialect), plan inbox ("only a mailbox"), dual task graphs (retired)

Decision

One Postgres database is the single source of truth for every Product's control-plane data. SQLite remains only as a rollback path during a bounded compatibility window, then is removed.

• One database, many Products. Fulreach, Mainark and future Products live in one database. product_id partitions every domain row. Product is a table, not an identifier prefix or a file path.
• Where: Supabase project okznyuneokewngghvziq ("Mainark", organisation "Mainark", eu-central-1, Postgres 17.6), in a schema mainark that the Supabase Data API does not expose. Never Fulreach's project (qvqvpnog…, organisation "Adaminds"). The project stays on the Free plan for implementation and rehearsals. It does not become the sole production authority until the recovery gate passes.
• Who: daemon, coordinator, HTTP API (and therefore the web UI) and CLI read and write Postgres. Agents never get database credentials.
• No dual write. Data moves once, under a writer freeze and fence. A reverse export exists only for the rollback window.
• Invariants move into the schema (composite foreign keys, row checks, locked triggers).
• Merge, not pick. The existing SQLite copies have diverged; the cutover merges their legitimate history by documented rules (Reconciliation).
• Order: storage moves before ADR-004 phases 3–5 and ADR-003's tables. products and product_id (ADR-004 phase 5) move into this migration.
• Client: postgres.js (evaluation).

Context

Measured 2026-10-10.

Separate files per Product and per machine produced the collision above. One shared database removes the cause rather than the symptom.

Why Postgres, and what we give up

Gains: one truth for every machine; real concurrency (row locks, SKIP LOCKED, advisory locks); declarative invariants; managed backups; logical dumps portable to any Postgres; plan inbox and work items in one transaction.

Losses and costs

• Offline operation ends. ADR-001 required issue CRUD, implement, queue and dispatch to work with no network. After cutover every Mainark command that reads or writes work needs the database. There is no offline mode and no local cache that can accept writes; local Postgres exists only for development against non-production data. Accepted: central consistency matters more, and nearly every useful action already needs GitHub, Cursor or a model API.
• Supabase is a hard dependency. If it is unreachable, Mainark stops dispatching and the CLI fails. Running agents continue; their results are pulled later because every external input is pull-based.
• Async conversion of every repository call (the largest piece of work).
• Network latency per query (laptop in Zurich → Frankfurt ≈ 10 ms; box → Frankfurt measured in phase B). Hot-path loops that query per row are batched.
• Cost: none while on Free. The recovery gate may choose Pro (managed backups) or another tested recovery mechanism; PITR later, if ever.

Where: the Mainark Supabase project

Recommendation: reuse this project, with:

1. Stay on Free for now. Schema, adapters, importer/exporter, tests and rehearsals use the current Free project. Free has no managed backups and can pause after inactivity, so the project becomes the sole production authority only after the recovery gate passes. No plan upgrade or paid add-on without explicit approval.
2. Schema mainark, not in the Data API's exposed schemas; revoke all on schema mainark from public, anon, authenticated. The inbox stays in public behind its RPCs. CI runs Supabase's security advisor.
3. Roles: mainark_owner (owns the schema; migrations only) and mainark_app (DML on mainark.*, select, update on public.plan_inbox). Not postgres, not the service key. The service key leaves the box after cutover.

Database layout

mainark  (not exposed)
  products, product_policies, id_sequences
  work_items, work_item_aliases, work_item_dependencies
  work_item_events, work_item_changes
  agent_runs, review_cycles, events, scheduled_dispatch_slots, inbox_imports
  schema_migrations, migration_ledger
public   plan_inbox            private  inbox_secret

Table names. Postgres uses work_items (and work_item_*): a neutral persistence name for rows whose explicit kind is goal | task. It is not user-facing vocabulary; the product speaks of Goals and Tasks (ADR-004). Repository function names and domain types are unchanged by the port.

Products

create table mainark.products (
  id                 text primary key check (id ~ '^[a-z][a-z0-9-]{1,30}$'),   -- 'fulreach', 'mainark'
  name               text not null,
  identifier_prefix  text not null unique check (identifier_prefix ~ '^[A-Z][A-Z0-9]{1,9}$'),  -- 'FUL', 'MAI'
  repo_url           text not null unique,
  default_branch     text not null default 'main',
  branch_prefix      text not null,              -- 'feat/ful-'
  validate_cmd       text,
  linear_team_key    text unique,                -- integration boundary only
  status             text not null default 'active' check (status in ('active', 'archived')),
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now()
);

create table mainark.product_policies (
  product_id               text primary key references mainark.products(id),
  auto_dispatch_enabled    boolean not null default false,
  auto_review_enabled      boolean not null default false,
  auto_review_max_rounds   int     not null default 3,
  max_review_cycles        int     not null default 3,
  max_inflight_runs        int     not null default 1,
  daily_budget_usd         numeric(10,2),
  auto_merge               boolean not null default false,
  auto_merge_strategy      text check (auto_merge_strategy in ('merge', 'squash', 'rebase')),
  pr_review_after_push     boolean not null default false,
  cloud_env                text check (cloud_env in ('cloud', 'machine', 'pool')),
  cloud_env_name           text,
  updated_at               timestamptz not null default now(),
  updated_by               text not null
);

create table mainark.id_sequences (
  product_id  text not null references mainark.products(id),
  family      text not null check (family in ('item', 'incident', 'run')),
  next_value  bigint not null check (next_value > 0),
  primary key (product_id, family)
);

Where today's per-Product configuration goes

Rule: facts every machine must agree on go into the database; paths on one machine and secrets stay in that machine's environment. While knobs move, env may only restrict database policy (a kill switch can turn auto-dispatch off, never on).

Schema translation

The port is a translation. Allowed changes: Postgres types, product_id, domain table names, declarative invariants, normalised dependencies, global run identity. Pipeline columns, statuses and JSON shapes keep their meaning.

Run identity

agent_runs.id is a global bigint identity and the only key anything references (parent_run_id, scheduled_dispatch_slots.agent_run_id, API paths). run_number is a Product-scoped display number from id_sequences(product, 'run'), unique per Product, used in CLI and Slack text ("FUL run 48"). At import, run_number = the run's SQLite id, which the reconciliation shows is consistent across copies for every shared run.

Dependencies

depends_on becomes a table designed around ADR-004's semantics.

create table mainark.work_item_dependencies (
  id                bigint generated always as identity primary key,
  product_id        text not null,
  work_item_id      text not null,               -- the dependent: Goal or Task
  depends_on_id     text not null,               -- the blocker: Goal or Task
  created_at        timestamptz not null default now(),
  created_by        text not null,               -- 'cli:adrian', 'inbox:42', 'migration'
  waived_at         timestamptz,
  waived_by         text,
  waiver_reason     text,
  replaced_by_id    bigint references mainark.work_item_dependencies(id),  -- set when re-pointed
  foreign key (product_id, work_item_id)  references mainark.work_items (product_id, id),
  foreign key (product_id, depends_on_id) references mainark.work_items (product_id, id),
  check (work_item_id <> depends_on_id),
  check ((waived_at is null) = (waiver_reason is null)
     and (waived_at is null) = (waived_by is null))
);
-- One active edge per pair; waived edges stay as history.
create unique index work_item_dependencies_active
  on mainark.work_item_dependencies (product_id, work_item_id, depends_on_id)
  where waived_at is null;
-- "What is X blocked by" and "what does X block".
create index on mainark.work_item_dependencies (product_id, work_item_id)  where waived_at is null;
create index on mainark.work_item_dependencies (product_id, depends_on_id) where waived_at is null;

Goal/Task invariants in Postgres

Verified in a spike (PGlite, Postgres 17): each rule rejected the violating write and accepted the valid one.

create table mainark.work_items (
  product_id   text not null references mainark.products(id),
  id           text primary key,
  kind         text not null check (kind in ('goal', 'task')),
  parent_id    text,
  parent_kind  text generated always as (case when parent_id is null then null else 'goal' end) stored,
  work_status  text not null default 'todo' check (work_status in ('todo','in_progress','paused','done','cancelled')),
  pr_number    int,
  pr_url       text,
  -- … pipeline columns unchanged …
  unique (product_id, id),
  unique (product_id, id, kind),
  foreign key (product_id, parent_id, parent_kind) references mainark.work_items (product_id, id, kind),
  check (kind = 'task' or (pr_number is null and pr_url is null))
);

create table mainark.agent_runs (
  id              bigint generated always as identity primary key,
  product_id      text not null,
  run_number      bigint not null,
  work_item_id    text not null,
  work_item_kind  text not null default 'task' check (work_item_kind = 'task'),
  -- …
  unique (product_id, run_number),
  foreign key (product_id, work_item_id, work_item_kind) references mainark.work_items (product_id, id, kind)
);

The id-prefix rule (FUL-… belongs to the Product with prefix FUL) is an insert trigger, because a CHECK cannot read products.

Foreign keys lock the referenced row and are race-free under READ COMMITTED; trigger checks that read other rows are not (SQLite hid this with its single writer). Hence FKs for I2/I3/I5/I9, and an explicit per-Product advisory lock for the remaining triggers.

Concurrency

• Claims: one conditional update … where kind = 'task' and work_status = 'todo' returning …; queue pickers use for update skip locked.
• Id allocation: update id_sequences … returning inside the creating transaction.
• One loop per Product: daemon/coordinator loops take a session pg_try_advisory_lock per Product and skip the tick if another process holds it. Requires a session connection (direct or session pooler), never the transaction pooler.
• Inbox import: for update skip locked on public.plan_inbox, create work items, mark the row — one transaction. Plans name their Product.

Postgres client

Evaluated postgres.js 3.4.9 against Bun's built-in Bun.sql on Bun 1.2.2 (laptop) and 1.4.2 (box), against a real Postgres, on this codebase's patterns:

Recommendation: postgres.js. It is the boring choice: mature, behaviour independent of the Bun version installed on each machine (they already differ), array parameters for batch and dependency queries, transactions, savepoints, reserved connections, prepare: false if a transaction pooler is ever needed, and portable to Node. Bun.sql is improving quickly but corrupted numeric values two minor versions ago. Behind the Db interface the choice can be revisited without touching repositories. Bun versions are pinned per machine regardless.

Access layer

• Repositories remain the only owners of SQL; functions become async.
• A minimal Db interface (query(text, params), transaction(fn), reserve()), with a postgres.js adapter (production) and a PGlite adapter (tests). No ORM.
• Connections: box → direct (IPv6), pool ≤ 5. Laptop → session pooler.
• no-floating-promises lint for src/ during the conversion.
• Query count per coordinator tick and per CLI command is instrumented; hot loops are batched.

Migrations and schema versions

• SQL files in src/db/pg/migrations/, applied by Mainark's migrator in a transaction under an advisory lock, recorded in mainark.schema_migrations. The same files run in PGlite, local Postgres, CI and production.
• No process migrates on startup. Production is migrated only by mainark db migrate run by the box's deploy step. Every process checks on startup that the schema version is within its supported range and refuses to run otherwise.
• Expand/contract: laptop and box may run different releases against one database, so every migration works with the previous release.
• The Supabase CLI runs a local stack only; production migrations do not go through supabase db push.

Testing

No test depends on a remote Supabase project.

PGlite is single-connection, so races belong to the integration tier. CI runs the repository tier on both adapters to catch drift.

Local development and CI

• Local Postgres (supabase start or postgres:17); MAINARK_DATABASE_URL points at it by default; mainark db migrate and mainark db seed (two Products, a few Goals/Tasks).
• Production from the laptop requires an explicit production URL plus MAINARK_PRODUCTION=1; a laptop daemon only dispatches if it wins the Product lease.
• CI: typecheck → pure + PGlite → Postgres integration → security advisor.

Backups and recovery

Today's state, measured: the Mainark Supabase project (Free) has no backups and no PITR. Since 2026-10-10 the box's nightly SQLite backup covers both fulreach-tasks.db and mainark-tasks.db (before that, only the idle MAI file, now frozen). The authoritative MAI file is on the laptop and has no scheduled backup: SQLite is transitional, and the effort goes into making Postgres authoritative. Its recovery points are the two verified snapshots from the handover.

Operational risk: box backups are on the same disk. mainark-backup.sh writes to /opt/mainark/backups on /dev/sda1, the disk that holds /opt/mainark/data. Nothing copies them off the box. Losing the disk, the box or the Hetzner account loses the live SQLite databases and every backup together; the only other copies are the laptop's divergent files and the /tmp reconciliation snapshots. Off-site copies are deliberately not set up yet. The risk is accepted until cutover, where the off-site pg_dump copies below replace it, and should be revisited if cutover slips.

Free-plan risks (explicit, current):

• No managed automatic backups on Free.
• The project can be paused after a period of inactivity; a paused project stops every Mainark command until it is resumed.
• The project has zero backups today (verified 2026-10-10).
• Therefore it must not become the sole production authority until the recovery gate passes. Implementation and rehearsals on Free are fine: during them the SQLite files remain authoritative.

Recovery gate (P1)

Before Postgres becomes the sole production authority (step 15 of the migration sequence), a recovery strategy is explicitly approved and tested. The gate is about the outcome, not a plan tier:

• Approved: the chosen strategy, its RPO and RTO, and who restores, are written down and approved by the maintainer.
• Tested: a restore from that strategy into a fresh Postgres passes mainark db reconcile/validation and mainark next per Product, within the stated RTO.
• Availability: the strategy says how a paused or unreachable project is handled (resume procedure, or restore elsewhere from the backups).

Candidates (none predetermined; one or a combination satisfies the gate):

Layers planned regardless of the gate's choice (no PITR yet):

PITR (add-on, requires Small compute; ≈ $100/month for 7 days, RPO ≈ 2 min) is not a cutover prerequisite: hourly dumps bound loss to one hour of control-plane writes, most of which (PR merges, run status) are re-derivable by polling GitHub and Cursor. Enable PITR when unattended dispatch runs at volume. After any Supabase restore, reset mainark_app / mainark_owner passwords (custom-role passwords are not in Supabase backups).

Reconciliation of the existing SQLite copies

Snapshots taken 2026-10-10 with VACUUM INTO (read-only on the source) from every copy; all pass integrity_check. Re-checked at 17:10 UTC: the live files are unchanged since the snapshots except the box's FUL counter (interim partition). Natural keys are used because autoincrement ids are per file: items by id (created_at detects collisions); runs by (item, role, created_at, external run id); item events by (item, time, kind, detail); issue_changes by (item, time); duplicate keys are numbered, not merged. The read-only comparison (review.ts, kept with the snapshots) becomes mainark db reconcile in phase C, and its 2026-10-10 output is the baseline for the T-1 inventory.

Copies

Both MAI files also contain an unused FUL counter row (next_value 1; no FUL items): dropped.

This table and the results below are the reviewed 2026-10-10 baseline. Since the MAI authority handover the laptop MAI row describes the archived stale file, the laptop's data/mainark-tasks.db is the box file's content plus later laptop writes, and the box MAI file is frozen.

Results by table

FUL, laptop vs box:

MAI, laptop vs box:

Shared runs keep the same SQLite id on both sides (FUL 14 of 14, MAI 61 of 61), so run_number = SQLite id is unambiguous.

Unique rows and conflicts, by type

FUL

MAI

Integrity findings (identical in both FUL copies; none in MAI): no orphan runs, no missing parents.

The 16 colliding FUL ids

The laptop created FUL-1061…1076 on 2026-09-15…17: Hiring Signal dashboards, the CEO digest, the Hiring Snapshot Goal FUL-1064 with children, the Provider Governor Goal FUL-1070 with children, and the Warm engager Goal FUL-1076. They have 20 runs, PRs #1343 (FUL-1061), #1344 (FUL-1062), #1359 (FUL-1065) on the items and #1345, #1385, #1387 on runs of FUL-1063, FUL-1066, FUL-1067, plus dependents: FUL-990 → FUL-1075; FUL-1077…1081 are children of FUL-1076 and depend on FUL-1075. The box created a different FUL-1061…1076 on 2026-10-03/04 from inbox plans 1 ("Workspace lead ownership", 7 steps) and 2 ("Inbound analysis", 9 steps): todo, no parent, no runs, 17 dependency edges among themselves. GitHub PRs #1502 (fix/ful-1063-…), #1504 (fix/ful-1064-…) and #1505 ("FUL-1066: …") use the box's ids. Both sets are legitimate, and both are referenced on GitHub.

Attribution. A row referencing FUL-1061…1076 belongs to the laptop item if it is dated before 2026-10-03 19:34:21 (the box's first import) and to the box item otherwise. Verified: the laptop's 67 events and 20 runs on these ids are all dated ≤ 2026-10-01; the box's 16 rows are the created events at import time; no row shared by both copies references these ids.

Handling

• The laptop items keep their ids: created first, with runs, PRs, children and dependents inside Mainark.
• The box items get new ids in creation order, then old-id order, after the highest FUL number in any copy at the final snapshot. Today that is FUL-1325, giving FUL-1326…1341. If the box has allocated ids ≥ 2000 by then (interim partition), they are imported as they are and the 16 follow the highest of them.
• Their 17 edges among themselves are rewritten to the new ids; their 16 created events and the 2 inbox_imports rows move with them; plan_inbox rows 1 and 2 get the new ids in the same transaction.
• work_item_aliases records each former id (reason renumbered: id collision, box copy 2026-10-03), so a search for "FUL-1066" finds both items; the import writes a renumbered event on each.
• Not inferred: six PRs for the box plans were merged on 2026-10-04 (#1498, #1499, #1501, #1502, #1504, #1505) but never linked, because merge sync links through runs and these items have none. The import keeps those items todo; the cutover report lists them for a human to link and close.

Canonical dataset

Computed by applying these rules to the 2026-10-10 snapshots (a dry run in memory); the T-1 rehearsal recomputes it.

Checks on the data: no run and no PR is attached to a Goal (I5, I6 hold); 27 FUL Goals are done or in_progress, so I8's CHECK stays deferred to ADR-004 phase 3 as planned.

Treatment of each discrepancy

Interim measures until cutover

Taken 2026-10-10, independent of this ADR's acceptance.

• FUL id partition (temporary). The box's FUL counter is raised from 1077 to 2000 (guarded UPDATE … WHERE next_value = 1077, run as mainark with the coordinator running; rollback snapshot /opt/mainark/backups/fulreach-tasks-pre-counter-*.db). Until cutover the laptop allocates from 1116…1999 (skipping FUL-1325, which exists) and the box from 2000 up, so the two SQLite copies cannot allocate the same id again. The laptop counter is unchanged. This is a namespace partition for the SQLite coexistence period only, not part of the Postgres identifier design: at cutover the partition ends, the FUL id_sequences value is set by the rule above (highest FUL number + 1 after renumbering), and the unused gap below 2000 is not back-filled. Applied 2026-10-10 17:01 UTC after the backup below passed verification (1 row changed; verified FUL|2000; rollback snapshot fulreach-tasks-pre-counter-20261010T170120Z.db).
• Box backup covers both databases. mainark-backup.sh now backs up fulreach-tasks.db as well as mainark-tasks.db (previous script kept as mainark-backup.sh.bak-20261010). Verified 2026-10-10 by a manual run and a restore into /tmp: integrity_check ok; row-level diff against a fresh snapshot of the live file shows no differences; the box's repository code reads the restored copy with results identical to the live copy. foreign_key_check reports one row, orphan item event 525 (FUL-759 created), which is pre-existing in the live file and handled by the rule above. Same-disk limitation: see Backups and recovery.

MAI authority handover (2026-10-10)

MAI had two SQLite copies with separate counters, like FUL. Instead of a partition, MAI moved to a single writer: the laptop. Done 2026-10-10, 17:21–17:23 UTC, before any new MAI item was created:

1. Pre-check. No process held the box's /opt/mainark/data/mainark-tasks.db (fuser, lsof; WAL empty, last write 2026-09-28). State as expected: 48 items, highest MAI-48, MAI counter 49.
2. Handover snapshot. VACUUM INTO /opt/mainark/backups/mainark-tasks-handover-20261010T172121Z.db (as mainark): integrity_check ok, foreign_key_check empty, sha256 f1af9737…a70d435f9 (identical to the reconciliation snapshot). Row count and per-table checksum (sha256 of all rows in rowid order) equal the live file for all 10 tables.
3. Transfer. Copied to the laptop; sha256, all 10 table checksums and the MAI counter equal the box source. The stale laptop file (46 items, counter 47, sha256 c0e03841…c9e4a7fd6) was moved to data/archive/mainark-tasks.laptop-stale-20261010.db and the handover copy installed as data/mainark-tasks.db.
4. Box file frozen in place (not renamed: sqlite3 in the backup would create an empty file at the old path). wal_checkpoint(TRUNCATE), journal_mode = DELETE, 30 freeze_<table>_<op> triggers (BEFORE INSERT / UPDATE / DELETE on all 10 tables) raising MAI authority moved to laptop 2026-10-10, chmod a-w. Frozen sha256 4e0ec6b4…d9e157ef8; data checksums unchanged. Verified: writes as mainark fail (attempt to write a readonly database); writes as root (which bypasses file modes) fail on the triggers; the nightly backup still reads it (48 items, 68 runs); the janitor's two read queries still run against it.
5. Migration 013 on the laptop copy via the repository's migrate() (only 013 was pending; the box's 011_autonomy row came with the file). Result: kind = 4 Goals (MAI-1, 12, 24, 25) / 44 Tasks, as in the canonical dataset; every Goal has children and no Task does; 8 triggers and idx_issues_kind present. Apart from the new column, items are unchanged; issue_changes gained 4 backfill rows (the 4 Goals).
6. Pre-seed snapshot, then seed. A snapshot of the laptop file was taken and restore-verified (integrity_check ok, no FK rows, all 10 table checksums equal the live file, repository code reads it); scripts/seed-mainark-postgres-control-plane.ts ran as a dry run against that exact file (MAI-49…100 planned, counter 101, source sha256 and table checksums unchanged) and was then applied.
7. Result. 100 items (12 Goals, 88 Tasks), MAI counter 101. Root Goal MAI-49 "Control plane on Postgres (ADR-005)" with Goals MAI-50 (A, 11 Tasks), MAI-62 (B, 10), MAI-73 (C, 7), MAI-81 (recovery gate and prerequisites, 5), MAI-87 (D, 3), MAI-91 (E, 3), MAI-95 (F, 5); 74 dependency edges, none dangling, no cycle; 9 Human Tasks marked "not for agent dispatch". MAI-1…48 and their item events are identical to the handover snapshot. integrity_check ok, foreign_key_check empty.

Consequences for the plan:

Transitional state (until the Postgres cutover, which makes Postgres the single authority for MAI and FUL):

• Sole MAI SQLite authority: the laptop's ~/mainark-data/mainark-tasks.db (schema 013), the MAI source for reconcile and import. .env.mainark-self sets SQLITE_PATH to it directly. The file was moved there after the seed (table checksums equal before and after the move).
• Compatibility symlink: data/mainark-tasks.db points to it, so scripts that hard-code the repository path open the authoritative file instead of creating a second, empty MAI database with its own counter.
• Frozen box copy: the box MAI file is a frozen archive, not a source; its MAI-1…48 must equal the laptop's except for later laptop-side changes.
• Preserved, read-only, in data/archive/ (each integrity_check ok, foreign_key_check empty):
  • mainark-tasks-handover-20261010T172121Z.db: the handover snapshot, sha256 f1af9737…a70d435f9, tables equal to the box source (the original stays in /opt/mainark/backups/);
  • mainark-tasks.pre-seed-20261010T172302Z.db: the pre-seed snapshot, 48 items, MAI counter 49, tables equal to the live file just before the seed;
  • mainark-tasks.laptop-stale-20261010.db: the stale laptop copy.
• No scheduled laptop backup. A nightly LaunchAgent was set up on 2026-10-10 and removed the same day: no operational infrastructure is built around the transitional SQLite file. Recovery requirements apply to Postgres through the recovery gate before SQLite is retired.
• The MAI figures in Canonical dataset and step 13 are the pre-handover baseline (48 items, 34 edges); the T-1 rehearsal recomputes them (today 100 items, 108 edges).
• No undo. The handover could only be reversed before the first new laptop MAI write; the seed was that write, so the laptop stays the MAI authority until cutover.

Cutover

Migration sequence

Each step says who runs it, what proves it, and what stops the cutover. The sources are the four files in Copies; writers are the box's mainark-coordinate (the only process holding fulreach-tasks.db open on 2026-10-10) and the laptop's CLI and dev daemon (the only MAI writer since the handover). mainark-worker-janitor only reads: its unit sets MAINARK_JANITOR_DB=/opt/mainark/data/fulreach-tasks.db (the script's default is mainark-tasks.db); mainark-daily.service has no enabled timer. The frozen box MAI file is not a source; T-1 checks it is unchanged.

Prerequisites (before T0 is scheduled)

• P1. Recovery gate passed (Recovery gate): a recovery strategy explicitly approved and tested. A Pro upgrade is one option, not a requirement. P1 blocks only step 15 (Postgres becoming the production authority); it does not block implementation or rehearsals, which run on the Free project.
• P2. mainark db migrate has created schema mainark (empty tables) and roles mainark_owner / mainark_app; mainark is not in the Data API's exposed schemas; the security advisor is clean.
• P3. Hourly pg_dump running with an off-site copy; one restore drill passed.
• P4. Phases A–C merged; box and laptop run release R0 (the last SQLite release, TASK_STORE=sqlite), which contains mainark db reconcile, import, export-sqlite and the fence commands.

T-1 day: inventory and rehearsal (operator; read-only on the sources)

1. Snapshot the four sources with VACUUM INTO (box files as mainark) into a dated directory; integrity_check each.
2. mainark db reconcile --baseline <2026-10-10 baseline>: every change since the baseline must fall into an existing class (new item on one side → union; item advanced on one side only → that row; new runs/events → union). Stop on: a new id collision; a true conflict (three-way: both sides changed one item differently); a new orphan or missing dependency target; a box item < FUL-2000 or a laptop item ≥ FUL-2000 created after 2026-10-10 (partition broken); the frozen box MAI file no longer at sha256 4e0ec6b4…d9e157ef8, or missing its freeze_* triggers (MAI authority broken).
3. Record decisions in the decision file: FUL-696 → FUL-692, and anything new from step 2. The importer refuses to run with an undecided item.
4. Rehearse: full import of the snapshots into a local Postgres and into a scratch schema on Supabase; every T0 validation check (step 13) passes; export-sqlite round-trips; duration recorded.
5. Publish the provisional renumbering map and cutover report (renumbered items, the six PRs to link, excluded rows, FUL-696 decision); announce the freeze window.

T0: freeze and fence

1. Stop writers. Box: systemctl stop mainark-coordinate. Laptop: stop the dev daemon and every CLI session; no agent may run Mainark commands.
2. Prove it: fuser /opt/mainark/data/*.db and lsof data/*.db ~/mainark-data/*.db on the laptop list no process. Per Product, no run is queued/running, or each is recorded for re-attachment by external_run_id.
3. Fence each source: PRAGMA wal_checkpoint(TRUNCATE), PRAGMA journal_mode = DELETE; record its sha256; create BEFORE INSERT / UPDATE / DELETE triggers on every table raising moved to postgres; chmod a-w. A test write must fail.
4. Copy the fenced files as the final snapshots; mainark db reconcile against the T-1 snapshots: differences are only writes made between T-1 and step 6, each in a known class (else stop → R1).
5. Compute the final renumbering map and counters from the final snapshots.

T0: import

1. Check the mainark tables are empty and the schema is at the release's version.
2. mainark db import --sources <final snapshots> --decisions <file> in one transaction: Products and policies; work items (rules above, renumbering, recomputed kind); aliases; dependency edges; runs; item events (+ renumbered events); events; review_cycles; inbox_imports; id_sequences; migration_ledger; and the update of public.plan_inbox rows 1 and 2 to the new ids.

T0: validation (inside the transaction before commit; any failure ⇒ rollback ⇒ R1)

1. • Per Product and table: source rows = imported + ledger exclusions (today: FUL 868 items, 129 runs, 2,038 + 16 item events, 457 events, 452 edges; MAI 48, 68, 161, 0, 34).
   • Per-row checksums (canonical JSON, normalised timestamps) equal the row the rules chose.
   • Every renumbering, alias, exclusion and dropped reference is in the ledger; the 16 renumbered events exist.
   • Recomputed kind equals the laptop's 013 kind column for every laptop item (today 87 Goals).
   • id_sequences > highest number per Product and family; the work_item_changes identity starts above every source issue_changes id.
   • Constraints hold (enforced on insert).
   • Golden diff per Product: queue, next, listAutoDispatchReady, Goal views and run listings from R0 code on the merged SQLite export equal the new code on Postgres.
2. Commit.

T0: switch

1. Deploy the Postgres release with MAINARK_DATABASE_URL, MAINARK_PRODUCT, TASK_STORE=postgres on box and laptop; start mainark-coordinate.
2. Smoke test: one tick per Product, mainark next per Product, one test plan through the inbox in a scratch Product. The first production write to Postgres is go-live; record its time.
3. Send the cutover report (renumbered items, the six PRs to link and close, FUL-696 decision, excluded rows).

Window and close

1. Rollback window (2 weeks): nightly export-sqlite to the box; watch latency and query counts.
2. Close the window: delete the SQLite adapter, TASK_STORE, SQLITE_PATH; move the janitor's checks to Postgres; archive the fenced files, snapshots and older copies off-site; move per-Product knobs into product_policies.

Nothing is lost during the freeze: merged PRs are found by syncMergedIssues, Cursor runs by polling external_run_id, plans wait in plan_inbox. Step 7 also confirms no webhook-driven writer is active (the events history shows Linear webhooks were used before; none must be live at cutover).

Rollback sequence

Rollback compatibility window (2 weeks)

Development continues during the window; schema changes are limited to what the reverse export can carry to the last SQLite release (R0).

Every PR touching migrations during the window runs the round-trip test (Postgres → export-sqlite → R0's validation and golden diff) in CI.

Implementation order

Recommendation: A. Phase 3 logic work may proceed in parallel if it adds no SQLite schema.

Phases

• A — Async repository boundary on SQLite. Db interface; async repositories and callers; floating-promise lint; stray SQL moved into repositories. No behaviour change.
• B — Postgres schema and adapters. Migrations (Products, policies, translated tables, dependencies, FK invariants, triggers); postgres.js and PGlite adapters; three test tiers; suite runs on SQLite and PGlite in CI.
• C — Importer, exporter, rehearsals. mainark db reconcile (with the 2026-10-10 baseline), reconciliation rules and decision file as code, preflight, import, post-validation, golden diff, reverse export; Supabase roles, schema exposure, hourly dumps, restore drill; rehearsals on the Free project.
• D — Cutover (sequence above).
• E — Rollback compatibility window (2 weeks).
• F — Remove SQLite; move knobs into product_policies; then ADR-004 phases 3, 4, 5 on Postgres.

What this supersedes and amends

Consequences

• One truth for every machine and Product; id collisions and stale copies end.
• Mainark no longer works offline. Every command that touches work needs the database; a Supabase outage stops dispatch and the CLI.
• The Goal/Task model is enforced by the schema under concurrency.
• A large mechanical async conversion lands before new features.
• ADR-004 phases 3–5 and ADR-003 pause for phases A–E, then proceed on a schema that already has Products and normalised dependencies.
• Running cost: none while on Free; whatever the recovery gate chooses (possibly Supabase Pro); PITR later, if ever.
• The migration carries one-time data repairs that are recorded, not silent: 16 renumbered items, 5 excluded test rows, 1 orphan item event moved to the ledger, 3 dependency references not imported (FUL-696's decided at T-1), and a change feed that restarts at go-live.