Skip to main content

Working beside your Postgres (chronos attach)

Your data stays in the Postgres you already run. Chronos copies the tables an agent works on, the agent does its whole task in a world, a person sees the row-level diff, and merging the world writes the change to Postgres in one transaction. A row that changed in Postgres while the agent worked comes back as a conflict, and nothing is written.

Status: Stage 1, for staging copies. Read the limits below before pointing it at production.

Attach​

export CHRONOS_SOURCE_URL='postgres://agent_writer:...@staging-db:5432/crm?sslmode=verify-full'
chronos attach ./crm --tables public.leads,public.accounts
chronos serve ./crm
  • The URL comes from CHRONOS_SOURCE_URL, or from a file only you can read (chronos serve --source-file f, or CHRONOS_SOURCE_FILE). It is never a command argument and is written nowhere in the folder.
  • --schema app attaches every ordinary table of a schema. Above about 1,000,000 rows attach asks for --yes: it holds one read open on Postgres while it copies.
  • Attach makes one table in Postgres, chronos.merges (a row per merge written, and one naming this folder). If the role can't, attach prints the statements for an administrator.

Work​

create world fix_leads;
use world fix_leads;           -- the agent's whole task, in its world
update leads set owner = 'ada' where region = 'EU';
delete from leads where email is null;
diff world fix_leads;          -- what it changed, row by row
merge world fix_leads;         -- written to Postgres first, then here

What happens at merge world:

  1. Chronos's own checks, as for any merge: conflicts with main, constraints, merge policies, merge checks, stale reads.
  2. One Postgres transaction: each row the world changed is locked and compared with the value main holds. Any difference means it changed in Postgres since: everything is rolled back, main takes Postgres's current values for those rows, and the merge returns the conflicts. Merge again and settle them as any conflict. A merge BY COLUMNS does that once by itself: a row where the world and Postgres changed different columns combines (the world's columns, Postgres's for the rest), and only a column both changed is still a conflict.
  3. Otherwise the updates, inserts and deletes are applied with RETURNING. Then, in the same transaction, the merge checks on attached tables run on Postgres as those writes leave it; rows found roll everything back. Otherwise it commits. Values a Postgres trigger or default set come back into main.

Dry run against live Postgres​

merge world fix_leads dry run shows, row by row, what the merge would apply, conflict on or be held for. In an attached folder it then checks the rows it would write against Postgres as it is now:

  • one read-only transaction (REPEATABLE READ, the same settings and source check as a write-back, and the same check that each table still has the shape it was attached with), compared as the write-back compares, then ROLLBACK. Nothing is written to Postgres, and main doesn't take Postgres's rows (a real merge's conflict does);
  • each row gets a postgres column, Postgres's row now. A row changed there since main's copy has outcome changed in postgres, and the plan's blocked row says the merge would be refused;
  • a last row, outcome postgres, says when Postgres was read and lists the tables' triggers;
  • the rows are read with no locks, so what it shows is a snapshot. The merge itself locks and compares again. Triggers aren't run, so values they would set show up only after the real merge (main takes them then);
  • if Postgres can't be reached (or no CHRONOS_SOURCE_URL is set), the plan still comes back, and the postgres row says it wasn't checked.

The HTTP merge op with dry_run: true returns the same as postgres: {source, at, error, triggers, summary, rows: [{key, changed, postgres}]}, and MCP's merge_preview shows the column.

Rehearsing a merge on your own data​

A dry run reads; it runs none of the merge's statements, so triggers, constraints and cascades in Postgres aren't exercised. merge world fix_leads rehearse runs the whole write-back in Postgres and then rolls it back:

  • the same transaction a merge runs: the source and shape checks, the world's reads checked if it checks them, every row locked (FOR UPDATE) and compared, then each update, insert and delete with RETURNING, so triggers, defaults, constraints and foreign keys' actions act; then SET CONSTRAINTS ALL IMMEDIATE, so deferred constraints and constraint triggers run now rather than at a COMMIT; then the merge checks on attached tables. Then ROLLBACK instead of COMMIT;
  • it runs as the role CHRONOS_SOURCE_URL names, which must be allowed to write those tables, as for a merge. Its locks are held for as long as the rehearsal runs, as a merge's are;
  • the plan comes back as a dry run's, with more rows: rehearsed for each statement it ran and the rows Postgres returned; postgres set for each row Postgres stored otherwise than it was sent (a trigger or default set a column: the value main would take); postgres wrote for tables triggers or cascades wrote besides; held for a merge check that found rows; stopped for a statement that failed (which one, and Postgres's error) or a refusal (a row changed in Postgres since main's copy, a read that changed); rolled back, the check after the rollback, in a read-only transaction of its own, that Postgres holds each row as it did before; and effect for each NOTIFY ON MERGE the merge would send once it lands. Nothing is sent, nothing is merged, and main doesn't take Postgres's rows (as a merge's conflict would);
  • merge world fix_leads rehearse fail at 2 stops after the second write statement, as if it had failed there, to watch the rollback on your rows.

REHEARSE is refused in a folder that isn't attached (use DRY RUN), and for a merge that doesn't write to Postgres (into a world other than main). Agents need the right to merge the world, not only to see it.

Reads checked against live Postgres​

A world forked with (check_reads = true) (or by an agent whose merge policy has check_reads) has its reads checked against Postgres too, inside the write-back's transaction and before anything is written. Within Chronos a read is checked against main, Chronos's copy, which a row changed straight in Postgres never reaches; so the write-back also reads, for each attached table the world read:

  • rows read by key: compared with Postgres's as the write-back compares the rows it writes;
  • filtered scans (where org_id = 42 and status = 'open'), with or without an index: Postgres is asked only for the rows the conditions can match, now and at the fork, however big the table. Conditions go to Postgres as they compare here: whole numbers on integer and numeric columns, text on text and varchar compared byte by byte, booleans, IS NULL. Other conditions are left out of what's asked, so more rows are compared, never fewer;
  • ranges, whole-table reads, lookups through an expression index, keys that found no row, and filtered scans with no condition Postgres can be asked by: the table is read whole from Postgres (at most 100,000 rows; past that the merge is held for a person),

so a row added there that the read would have found counts, an empty result included.

  • a world read by an agent held to a tenant: every read it made found only that tenant's rows, so of a table read whole only those are compared: the fork point's tenant rows, and Postgres's by the same condition in its SQL (org_id = 42, or for a child table lead_id in (select id from leads where org_id = 42), up to three foreign keys, the parents attached too). The 100,000 rows counts the tenant's rows, so a tenant's read of a child table of millions of rows is checked, and the app writing other tenants' rows doesn't hold it.

Postgres's rows are put in the world's fork point, with the index entries they make, and checked as a read against main is. A row the read couldn't have found (another email) doesn't hold the merge. A read that changed holds it as any stale read: refused with 40001 and nothing written (read rows that changed in Postgres since it forked: suppressed/(12)), or queued for a person under the agent's merge policy. A dry run reports the same.

A world that runs its reads again (rerun_reads = true, or a merge policy with rerun_reads) runs its SELECTs again on those Postgres rows before holding: the same answer there (a sum that came out the same) and nothing is held for it; another answer and the reason says so (it read ``select sum(amount) from orders where status = 'open'``, which gave 30 then and gives 31 in Postgres now).

The check reads without a predicate lock (READ COMMITTED): a row Postgres takes between the check and COMMIT lands after the merge in time, an order in which the world's decision was right.

An empty read isn't checked at all without check_reads. An agent that decides "create it if it's missing" (no lead for this email, so make one) relies on a read that found nothing. Without check_reads nothing at merge looks at that read again, so a matching row added in Postgres meanwhile isn't seen, and the agent's row is written beside it (Postgres refuses only an exact duplicate of a unique key). Turn on check_reads for such agents (a merge policy with check_reads, plus require_scope if they must also declare what they change), or add a merge check (below) that finds the duplicates.

Merge checks against live Postgres​

A merge check runs first on main, Chronos's copy, as for any merge. A row added straight in Postgres since the attach isn't there, so a check such as "no two leads with the same lower(email)" passes on main and the duplicate is written, when no unique index in Postgres catches it (case-insensitive and fuzzy matches seldom have one). So a merge check whose ON TABLES are all attached tables also runs in Postgres, inside the write-back, after the merge's rows are applied and before COMMIT:

create merge check one_lead_per_email on tables (leads) as
  select lower(email) as email, count(*) as n from leads group by 1 having count(*) > 1;
  • Rows found: ROLLBACK, nothing written, and the merge is refused with 23514 (breaks merge check one_lead_per_email in Postgres, which finds {"email":"ada@x.com","n":2}), or queued in SHOW REVIEWS under the agent's merge policy. The world and its queued effects (NOTIFY ON MERGE) stay in the world.
  • The check's SQL runs in Postgres as written, under the write-back's settings (UTC, ISO dates, search_path = public) with its timeout as statement_timeout; a check that fails or times out there holds the merge too.
  • A check with no ON TABLES may read any table, Chronos-only ones too, so it runs only on main. Give it ON TABLES naming the attached tables it reads to have it run in Postgres.
  • MERGE ... DRY RUN runs merge checks on main only: its Postgres transaction is read only, so the merge's rows aren't there to check.

Main's rows change only this way: writes to main and RESTORE on main are refused (an UNDO goes back through Postgres, as a merge: see below), and no statement may change a table's shape here. Change it in Postgres: a refresh takes columns added, dropped, renamed or retyped there, and enum labels (below); a change to a key, to row-level security or to a generated column needs a new attach into a new folder.

Masking a column is the one change of definition main takes: ALTER TABLE ... ALTER COLUMN ... SET MASKED (or DROP MASKED) on main, which changes only what agents read here, never Postgres (see masked columns). Worlds forked after read the column masked; one forked before keeps the table as it was then. A refresh keeps the mask, and the attached answer marks each masked column (mask). In a world it's refused.

An agent's merge into main needs a person unless the agent has a merge policy of its own; a person approves by merging the queued world.

Limits in Stage 1​

  • Postgres 12 or later: attach reads catalog columns 12 added (attgenerated, relispartition). Tested on 12 to 18 (and Neon's 17), with TLS verify-full and through PgBouncer in transaction mode (tests/attach_compat.rs: column types with the citext, hstore, pgvector and PostGIS ones, key shapes, constraints, foreign keys, settings, LATIN1 and SQL_ASCII databases). On 11 or older attach refuses before reading or making anything, saying the version is too old.
  • A timestamp written without an offset ('2026-03-01' into a timestamptz or tstzrange) is read in UTC, as Chronos's sessions are, not in the database's own timezone setting; write the offset if they differ.
  • Main sorts text by its bytes (C), so ORDER BY a text column can list rows in another order than a database with a language collation (en_US.UTF-8) does.
  • Each table needs a primary key, or else a unique index on NOT NULL columns to stand for one (no WHERE, no expressions, checked at once rather than deferred; a constraint's first, then the fewest columns). Its rows are found by those columns in Postgres, and main gets them as its primary key. A refresh stops on the table if the index goes or a column of it may be NULL. Inheriting tables (INHERITS) aren't attached.
  • A partitioned table is attached whole, as one plain table in main with all its partitions' rows (a partition alone is refused: attach its partitioned table). A merge writes the partitioned table, and Postgres puts each row in its partition, moving one whose partition key changed; a row no partition takes fails the merge, with Postgres's error. Partitions added or detached in Postgres later need nothing: a refresh reads the partitioned table.
  • Generated columns (GENERATED ALWAYS AS (...) STORED) are attached: Postgres works them out, and main works them out too, from the same expression (attach fails, saying so, if Chronos can't run it). A world can't write one; a merge never sends them, and they're never compared with Postgres's, so a value the two print differently (a division's digits) isn't a conflict. A column that becomes generated, or stops being, stops the refresh.
  • A table with row-level security on (Supabase turns it on for every table its dashboard makes) is attached when the role reads past it: a superuser, a role with BYPASSRLS, the table's owner (unless the table FORCEs it), or a role a policy shows every row, CREATE POLICY chronos_all ON t TO <role> USING (true) WITH CHECK (true), with no restrictive policy that applies to it. Otherwise attach refuses it, saying so, rather than copy only the rows the policies show (the rest would look deleted), and a refresh stops taking a table whose policies come to hide rows. The policies stay in Postgres, for your app's users; agents are held by Chronos's own rules (an agent held to one tenant sees only its rows).
  • Serial and identity columns take their new values from Postgres's own sequence, reserved 1,000 at a time, so an id a world gives a row is never given to another by Postgres, and is the id written at merge. The cost is gaps in the sequence. An id typed by hand into such a column is refused at merge. The role needs USAGE on the sequence.
  • At most 50,000 changed rows per merge by default (CHRONOS_ATTACH_MAX_ROWS raises it, to 1,000,000), sent 5,000 at a time in the one transaction; each stays locked until it commits. MERGE WORLD w ONLY TABLES (...) merges part of one.
  • Rows no merge touched are as old as the last refresh (below). A refresh reads every attached table whole: on big tables, run it from a read replica's URL and not too often.
  • A world that reads a table more broadly than 100,000 rows (a range or a whole-table read of a bigger table, or a filtered scan more than 100,000 of its rows can match) with check_reads on can't have that read checked against Postgres: its merge is held for a person, saying so.
  • Triggers stay in Postgres and run as the merge is written. Rows they (or a rule, or a foreign key's ON DELETE / ON UPDATE action) write or delete in other attached tables come into main with the merge: the write-back counts what its transaction wrote per table (pg_stat_xact_user_tables), and the tables written besides the merge's own rows are refreshed alone right after it (read whole, as a refresh reads them). Deferred constraint triggers run before the counts are read (SET CONSTRAINTS ALL IMMEDIATE), so a deferred check that fails refuses the merge there rather than at COMMIT. Until that refresh is done the pending record stays, so after a crash recovery does it. With track_counts off, they wait for the next refresh.
  • Tested on Supabase (Postgres 17, through its pooler Supavisor in session and transaction mode): the compatibility cases above, PostGIS aside (its install hit Supabase's statement timeout), and the gateway's end-to-end test. Supabase prints floats with 15 digits (extra_float_digits = 0); main keeps all of them, as Postgres stores them.
  • Tested on Neon, both straight to the database and through its pooler (PgBouncer, transaction mode): a merge of 1,000 rows took about 1.2 s from a machine 60 ms away, mostly round trips (BENCHMARKS.md §14).
  • The folder keeps one connection to Postgres between merges (refreshes and dry runs use it too), so a merge doesn't connect, handshake TLS and log in each time. It closes after 5 minutes unused, so Neon or Supabase can scale to zero; one closed by the server is replaced at the next merge's first statement, before anything is written. A changed CHRONOS_SOURCE_URL or CHRONOS_SOURCE_FILE is used from the next merge on.

Keeping main up to date (refresh)​

chronos refresh ./crm                      # once, on a folder no server has open
chronos serve ./crm --refresh-every 5m     # on a timer, while it serves
curl -H "Authorization: Bearer $ADMIN" -H 'Content-Type: application/json' localhost:7070/v1/refresh -d '{}'

A refresh reads every attached table in one REPEATABLE READ, READ ONLY transaction, under the write-back lock (no merge into main runs meanwhile). Each of main's rows is compared with Postgres's as the write-back compares them, and rows Postgres has that main hasn't are read whole; main takes the differences in one change. Open worlds see main move: a row a world changed that Postgres changed too is a conflict at its merge, settled as any.

  • Columns added in Postgres (ALTER TABLE ... ADD COLUMN, as a migration does) are added to main's table first, with Postgres's type and default; main's rows then take their values in the same refresh, and the column is made NOT NULL if Postgres has it. A default worked out per row (gen_random_uuid(), random()), which Chronos won't fill rows it has with, is set once they have Postgres's values. The refresh lists the columns (added). One stopped halfway is redone at the next. Until a refresh has taken them, a merge writing to the table is refused and says to refresh. A column added with a sequence or identity default, or a generated one, isn't taken.
  • Enum labels added in Postgres (ALTER TYPE ... ADD VALUE) are added to main's type, in Postgres's order, before the rows are read; the refresh lists them (labels). Until then a world can't write a new label. A label main has that Postgres hasn't (renamed there) is left, and said.
  • Worlds forked before a column was added keep the table as they had it. When one merges, a row it changed keeps the values Postgres holds in the columns it never had, settled USING OURS too: it can't have meant to change them. A row it inserts leaves those columns out, so Postgres fills them as it fills its own rows (now() is the insert's time), and main takes the row back as Postgres has it.
  • Columns dropped, renamed or retyped in Postgres are so in main too, before its rows are read (altered); a retyped column's values are then Postgres's, as the refresh compares them. Attach keeps each column's attnum, so a rename is told from a drop and an add (a folder attached before it was kept fills it in at its next refresh, and until then a rename is refused as below). Until a refresh has taken the change, a merge writing to the table is refused and says to refresh. A world forked before can't merge rows of the table after, nor a merge that checks its reads of it: it is refused, saying to fork a new world from main and make its changes again (a renamed column would read there as never set).
  • A table whose key, row security, or generated columns changed, or with a key or generated column retyped, or a column added taking values from a sequence, is left as it was and named (skipped); attach again into a new folder to take the new shape.
  • POST /v1/attached says what the folder is attached to, its tables, and as_of: how old main's untouched rows may be (the last refresh, else the attach).

Following Postgres's changes (serve --follow)​

chronos serve ./crm --follow                     # main takes Postgres's changes about a second after they commit
chronos serve ./crm --follow --refresh-every 1h  # and a whole refresh now and then, to be sure
chronos refresh ./crm --unfollow                 # drop the replication slot, once the folder is no longer followed

A refresh reads every row of every attached table; --follow reads only the rows that changed. Postgres says which through a logical replication slot (chronos_<id>, made by the first follow with the test_decoding plugin that comes with Postgres). Every second the server reads the changes since the last time from it, then reads the rows they name as Postgres has them now and compares them with main's as a refresh does, under the write-back lock. The slot only says where to look: a change read twice, or a merge's own write coming back, takes nothing. The slot moves past the changes only once main took them, so a server stopped halfway reads them again.

  • Postgres needs wal_level = logical, and the role attach connects as needs REPLICATION (or superuser). Self-hosted: ALTER SYSTEM SET wal_level = logical and a restart. RDS and Aurora: rds.logical_replication = 1 in the parameter group, and GRANT rds_replication TO <role>. Cloud SQL: cloudsql.logical_decoding = on. Supabase has it on. Neon: turn on logical replication in the project's settings. Without it the server says why, and keeps trying less often; --refresh-every works on any Postgres.
  • The first follow refreshes every table, as changes before the slot was made aren't in it. So does a follow after Postgres gave up on the slot (max_slot_wal_keep_size).
  • A table is refreshed whole where a change doesn't say which row: a TRUNCATE, a delete or key change under REPLICA IDENTITY NOTHING, more than 100,000 rows changed at once, or columns that changed (which a refresh takes, see above). A partitioned table's partitions count as it.
  • A slot keeps Postgres's WAL until it is read. While the server is stopped, WAL piles up on the database's disk. Drop the slot (chronos refresh ./crm --unfollow) when the folder won't be followed again, and on Postgres 13 or later set max_slot_wal_keep_size so a forgotten slot can't fill the disk.

Undoing a merge that reached Postgres​

UNDO MERGE OF WORLD w (or HTTP undo_merge) on a merge into main puts the rows back through Postgres: they go back in a world of main, which is merged as any world is, every row locked and compared there, in one transaction. Updated rows get their old values, inserted rows are deleted, deleted rows come back with their ids.

  • A row changed since the merge (in main, or straight in Postgres) is a conflict: nothing is written. With SKIP CHANGED that row is left as it is and the rest go back (a row changed only in Postgres is found by the write-back's compare; main takes Postgres's row, and the undo runs once more without it).
  • UNDO AGENT bot SINCE '-1 hour' (and UNDO DIFF) on main go back through Postgres the same way.
  • Effects aren't undone: a webhook called or an email sent stays sent. The undo lists each effect the undone change brought into main, in a WARNING (HTTP undo_merge: effects_not_undone), with where it is: sent (a worker acknowledged it), queued (not sent yet: ACK OUTBOX '<id>' drops it), or claimed / ambiguous (it may have been sent). The undo itself leaves the outbox alone.

Restore drill​

Before you let an agent merge unattended, rehearse the restore on your own data:

  1. Rehearse the merge. merge world w rehearse runs every statement in Postgres and rolls back: check the postgres set rows (what triggers would set), any stopped row, and the rolled back row saying Postgres is as it was. Postgres: unchanged. Outbox: unchanged (the effect rows list what the merge would send).
  2. Watch it fail halfway. merge world w rehearse fail at 1 stops after the first statement: rolled back shows the rollback held. Postgres: unchanged.
  3. Merge. merge world w. Postgres: the rows changed, in one committed transaction (and a row in chronos.merges). Outbox: the merge's effects queued in main, sent once a worker claims them.
  4. Undo. undo merge of world w (skip changed if your app changed some of the rows since). Postgres: the rows as they were before the merge, in one more transaction (and its own chronos.merges row); rows changed since are left, and counted. Outbox: untouched; the WARNING lists the merge's effects, sent or still queued, for you to follow up by hand (or drop the queued ones with ACK OUTBOX).

Agents held to one tenant​

For an agent working for one of your customers, create agent run_42 with (tenant = 'org_id = 42', valid_for = '1 hour', ...) (HTTP create_agent with tenant and valid_for in ms):

  • in every world it reads and changes only rows of tables with an org_id column that hold 42, whatever its role, the table's own row security, or row_security; a row it writes must hold 42 before and after;
  • a table without the column is held to the tenant through its foreign keys, up to three (a line item through its order, a line item's note through the item and the order): it reads and changes only rows whose key names a row of the tenant's, and a row it writes must name one (a null key names none). Of a table's foreign keys, the one that reaches a table with the column in the fewest hops counts (the first of those);
  • a table with neither the column nor such a foreign key is shared by every tenant (a lookup table): it reads it whole and may not write it (42501, ... may only read table "countries" ...);
  • every world it forks is declared that tenant's (tenant in its scope), so its merge is held to it too;
  • it sees changes as rows (DIFF, a dry run, CHECK READS, history) only of the worlds it forked, and not SHOW REVIEWS, SHOW AUDIT or the whole SHOW HISTORY; the row API is refused on tables the tenant limits (SQL applies it), and its writes on shared tables;
  • its token stops working once valid_for has passed.

After a crash​

If Chronos stops during a write-back, the outcome is settled the next time chronos serve starts or a merge into main runs: Chronos asks Postgres whether that transaction committed, then makes the merge here or drops it. Until it knows, merges into main wait; everything else works.