Chronos DB, self-hosted
Beside Postgres
chronos attach: a copy of your Postgres tables where agents work in worlds, and every merge is written to Postgres first, in one checked transaction.
Self-hostedChronos DB is one binary you run yourself. Releases and install script. To put agents on a database you already run, with approvals: Chronos Gateway
How it works
Your data stays in the Postgres you already run. chronos attach copies the tables you name into a new folder. That copy is main. An agent does its whole task in a world forked from main, a person sees the row-level diff, and merging the world writes the change to Postgres in one transaction. Each row the merge writes is locked and compared with the value main holds. A row that changed in Postgres while the agent worked comes back as a conflict, and nothing is written.
Main’s rows change only this way. Writes to main and RESTORE on main are refused, and no statement may change a table’s shape here. A refresh, or serve --follow, brings main up to Postgres. This page describes Chronos DB 0.6.2 (2026-11-19).
export CHRONOS_SOURCE_URL='postgres://chronos_attach:example-password@staging-db:5432/crm?sslmode=verify-full'chronos attach ./crm --tables public.leads,public.accountschronos serve ./crmTo put runs, rules and approvals on top of an attached folder, use Chronos Gateway. The gateway reads what a source can do from the attached answer described under limits.
Requirements and versions
- Postgres 12 or later. Attach reads catalog columns that 12 added (
attgenerated,relispartition). On 11 or older attach refuses before reading or making anything:Postgres 11.x is too old: attach needs Postgres 12 or later (nothing was copied). - Tested on 12 to 18 (and Neon’s 17), with TLS
verify-fulland through PgBouncer in transaction mode. The compatibility tests cover column types (with the citext, hstore, pgvector and PostGIS ones), key shapes, constraints, foreign keys, settings, and LATIN1 and SQL_ASCII databases. - Supabase: tested on Postgres 17, through its pooler Supavisor in session and transaction mode. The compatibility cases pass, PostGIS aside (its install hit Supabase’s statement timeout). Supabase prints floats with 15 digits (
extra_float_digits = 0); main keeps all of them, as Postgres stores them. - Neon: tested 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.
- A key on every table: a primary key, or a unique index on NOT NULL columns to stand for one (see what is attached).
- For
serve --followonly:wal_level = logicaland a role withREPLICATION(see serve --follow).--refresh-everyworks on any Postgres. - Commits Postgres keeps (from 0.6.2):
fsyncandsynchronous_commitnotoff. Each merge into main reads them first. With either off, Postgres may lose a commit it acknowledged after a crash of its own while main keeps the merge, so the merge is refused, nothing written:... may lose commits it acknowledged (synchronous_commit = off): .... SetCHRONOS_ATTACH_UNSAFE_SOURCE=1to merge anyway.
The role and its grants
Attach, refresh and every merge connect as the role the URL names (the attach role). What it needs:
- It must read every row of the attached tables. Attach refuses a table whose row-level security hides rows from the role (see what is attached).
- It must be allowed to write the attached tables, since a merge, a rehearsal and an undo run their inserts, updates and deletes as it (unless an agent has a
pg_role: see writing as the end user). USAGEon the sequences behind serial and identity columns of attached tables, so new rows take their ids from Postgres’s own sequence.chronos.merges:SELECTandINSERT. Attach makes the schema and the table if the role can. If it can’t, attach prints the statements for an administrator.chronos.merge_rows:SELECT,INSERTandDELETE. The first merge makes it if the role can. If it can’t, merges go on without it, and undo by merge id isn’t there until an administrator runs the same statements.DELETEis for pruning records past the undo window; without it, old records stay.REPLICATION(or superuser), only forserve --follow.- Membership of each
pg_rolean agent writes as (GRANT authenticated TO chronos_attach), only if you usepg_role.
The statements attach prints when the role can’t make Chronos’s tables itself, with the role’s name filled in:
create schema if not exists chronos;create table if not exists chronos.merges (id text primary key, at timestamptz not null default now());create table if not exists chronos.merge_rows (merge_id text not null, n int not null, table_name text not null, op text not null, key jsonb, before jsonb, after jsonb, at timestamptz not null default now(), primary key (merge_id, n));create index if not exists merge_rows_at on chronos.merge_rows (at);grant usage on schema chronos to chronos_attach;grant select, insert on chronos.merges to chronos_attach;grant select, insert, delete on chronos.merge_rows to chronos_attach;| Message from attach | Means |
|---|---|
<role> can’t make the table chronos.merges in Postgres (...) | the role may not create the schema or table; the statements above follow the message |
<role> may not read and insert into chronos.merges | the table is there but the role lacks SELECT or INSERT on it |
chronos.merges in Postgres isn’t Chronos’s table (its columns are ...): rename it, then attach | another table already has that name; Chronos expects id text, at timestamp with time zone |
chronos attach
chronos attach <new folder> (--tables <schema.table>,... | --schema <schema>) [--lazy-tenant <column>] [--yes]| Flag or variable | Does |
|---|---|
<new folder> | the folder attach makes. One that exists is refused: ./crm exists: attach makes a new folder (to attach again, give another) |
--tables a,b,app.c | the tables to attach, comma separated. A name without a schema is read as public.<name> |
--schema app | every ordinary table of a schema. With neither flag: name the tables to attach: --tables a,b,app.c or --schema <schema> |
--lazy-tenant org_id | tables with that column start empty and are loaded a tenant at a time (see tenants) |
--yes | goes on above about 1,000,000 rows (Postgres’s estimate). Without it: about N rows in all (Postgres’s estimate): copying holds one read open on Postgres for that long. Run again with --yes to go on |
CHRONOS_SOURCE_URL | the connection string: postgres://user:password@host:port/database?sslmode=... |
CHRONOS_SOURCE_FILE | a file that holds the connection string, readable only by you (mode 600). chronos serve --source-file f sets it |
- The URL is never a command argument and is written nowhere in the folder.
chronos serveandchronos refreshon the folder need it too. - URL parameters:
sslmodeis one ofdisable,prefer(the default),require,verify-ca,verify-full.sslrootcert=<file.pem>gives the CA certificates forverify-caandverify-full.connect_timeoutis in seconds. - One consistent read. Attach copies every table in one
REPEATABLE READ, READ ONLYtransaction, so the copy is of one moment. It holds that read open on Postgres while it copies. - It makes
chronos.mergesin Postgres: a row per merge written, and one row naming this folder (the marker). Every later merge, dry run and refresh checks the marker, so a URL that points at another database is refused. - It prints each table with Postgres’s estimate of its rows before copying. On any failure nothing is left behind: the folder is removed again.
- Missing tables:
no table public.x in Postgres, orschema app has no tables. - Refused tables:
can’t attach, nothing was copied:followed by one line per table with the reason.
What is attached, and what is not
| In Postgres | In the folder | Why |
|---|---|---|
| A table with a primary key | attached; the key is main’s primary key | a merge finds each row in Postgres by its key |
| A table with no primary key but a unique index on NOT NULL columns | attached; those columns become main’s primary key | the index must have no WHERE, no expressions, and be checked at once rather than deferred. A constraint’s index is taken first, then the one with the fewest columns. A refresh stops on the table if the index goes or one of its columns may be NULL |
| A table with neither | refused | a row could not be told from another |
| A partitioned table | attached whole, as one plain table with all its partitions’ rows | 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 later need nothing |
| A partition alone | refused | attach its partitioned table |
Inheriting tables (INHERITS) | not attached | |
Generated columns (GENERATED ALWAYS AS (...) STORED) | attached; main works them out from the same expression | a world can’t write one, a merge never sends them, and they are never compared with Postgres’s. Attach fails, saying so, if Chronos can’t run the expression |
| A table with row-level security on | attached only when the role reads past it | otherwise only the rows the policies show would be copied, and the rest would look deleted |
| Triggers, rules, foreign key actions | stay in Postgres | they run as the merge is written (see the write-back) |
Row-level security
Supabase turns row-level security on for every table its dashboard makes. Such a table is attached when the role is a superuser, has BYPASSRLS, owns the table (unless the table FORCEs it), or is shown every row by a policy with no restrictive policy that applies to it:
CREATE POLICY chronos_all ON leads TO chronos_attach USING (true) WITH CHECK (true);Otherwise attach refuses the table, saying so. 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.
Values and ordering
- A timestamp written without an offset (
'2026-03-01'into atimestamptzortstzrange) is read in UTC, as Chronos’s sessions are, not in the database’s owntimezonesetting. Write the offset if they differ. - Main sorts text by its bytes (
C), soORDER BYa text column can list rows in another order than a database with a language collation (en_US.UTF-8) does. - Masking a column is the one change of definition main takes:
ALTER TABLE ... ALTER COLUMN ... SET MASKED(orDROP MASKED) on main. It changes only what agents read here, never Postgres. Worlds forked after read the column masked; one forked before keeps the table as it was then. A refresh keeps the mask, and theattachedanswer marks each masked column (mask). In a world it is refused.
Working in a world
create world fix_leads;use world fix_leads; -- the agent's whole task, in its worldupdate leads set owner = 'ada' where region = 'EU';delete from leads where email is null;diff world fix_leads; -- what it changed, row by rowmerge world fix_leads; -- written to Postgres first, then hereAn 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.
The write-back
What happens at merge world:
Chronos’s own checks
As for any merge: conflicts with main, constraints, merge policies, merge checks, stale reads.
Lock and compare, in one Postgres transaction
The folder’s marker and each table’s shape are checked. Each row the world changed is locked (
FOR UPDATE) and compared with the value main holds. Any difference means the row changed in Postgres since main last had it: 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.Write, check, commit
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. The merge’s row is written tochronos.merges, and it commits. Values a Postgres trigger or default set come back into main.
BY COLUMNSsettles one kind of conflict by itself, once: a row where the world and Postgres changed different columns combines (the world’s columns, Postgres’s for the rest). Only a column both changed is still a conflict.- Isolation: the write-back runs at
READ COMMITTEDunlesswriteback.jsonsaysserializable. Its session uses UTC, ISO dates andsearch_path = public. - Size: at most 50,000 changed rows per merge by default.
CHRONOS_ATTACH_MAX_ROWSraises it, to 1,000,000 at most. Rows are sent 5,000 at a time in the one transaction, and each stays locked until it commits.MERGE WORLD w ONLY TABLES (...)merges part of a world. - Undo records: each write-back also keeps the rows it wrote in
chronos.merge_rows, in its own transaction (see undo).
Triggers and cascades
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. Until that refresh is done the pending record stays, so after a crash recovery does it. With track_counts off, those rows wait for the next refresh.
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.
Round trips
A write-back sends its statements in pipelines: several statements go in one write, and their answers are read after. A small merge into one or several tables takes five round trips to Postgres, whatever kinds of changes it makes: the start (with each table’s shape and the counts), locking and comparing every row, the writes, the deferred triggers (with RESET ROLE and the counts), and the merge’s row with COMMIT. Writing as the end user adds one. A write sends at most 16 KB, so a big chunk still goes a statement at a time. A rehearsal with FAIL AT n runs one statement at a time.
Big merges: IN CHUNKS OF n
One write-back holds every row’s lock until COMMIT. merge world backfill in chunks of 5000 merges 5,000 rows at a time, each chunk a merge and a write-back transaction of its own, until nothing is left, and then merges the world itself. A failure stops it with how far it got: the chunks already merged stay merged, and running the statement again carries on from there. Only people and admins may chunk a merge, since each chunk would otherwise pass an agent’s policy max_rows on its own.
The kept connection
The folder keeps one connection to Postgres between merges; refreshes and dry runs use it too. 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.
A crash around commit
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 (the merge’s row in chronos.merges is written in it), then makes the merge here or drops it. Until it knows, merges into main wait; everything else works. From 0.6.1 the merge made here is exactly the one written there, at the world’s version and keys as the merge had them; a world written since keeps those writes, and main takes the source’s rows. 0.6.0 and earlier merged the whole world again, so after a partial merge or a write to the world after the crash, main could take rows the source never got: upgrade.
If you ran a partial merge that was cut short by a crash on an earlier version, compare main with Postgres with refresh --check (from 0.6.2).
Refresh
chronos refresh ./crm # once, on a folder no server has openchronos serve ./crm --refresh-every 5m # on a timer, while it servescurl -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, so 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.
| Form | Notes |
|---|---|
chronos refresh <attached folder> | needs the folder to itself. On a folder a server has open, the error says to use POST /v1/refresh with the admin token, or serve --refresh-every |
chronos refresh <folder> --tenant 42 | in a folder attached with --lazy-tenant: main takes that tenant’s rows now |
chronos refresh <folder> --unfollow | drops the replication slot serve --follow made |
chronos refresh <folder> --check | the reconciliation scan, from 0.6.2: compares main with Postgres as a refresh does and takes nothing. Exit 0 when they agree, 1 when they differ, 2 on an error or with --tenant or --unfollow |
chronos serve <folder> --refresh-every 5m | takes a time such as 60s, 5m or 1h. A failed refresh is logged and tried again after the same time |
POST /v1/refresh | answers {at, changed, tables: [{table, inserted, updated, deleted}], added, skipped}. 409 in a folder that isn’t attached, 502 when Postgres can’t be read. The database’s own user only (the admin token) |
The command prints one line per thing it took: column added in Postgres, taken: ..., changed in Postgres, taken: ..., enum label added in Postgres, taken: ..., leads: 3 inserted, 1 updated, 0 deleted, and left as it was: ... for a table it skipped.
Checking without taking: --check
chronos refresh ./crm --checkleads: 1 only in Postgres, 2 differ, 0 only in mainaccounts: 0 only in Postgres, 0 differ, 0 only in mainaudit: columns differ in Postgres (column note added)--check reads Postgres as a refresh does, in the same read-only transaction, and changes nothing in main: not its rows, columns or enum labels. Per table it prints the rows Postgres has that main hasn’t, the rows both have but differ, and the rows main has that Postgres hasn’t. A table whose columns differ is named, and its rows aren’t compared (a refresh takes the columns first). When nothing differs it prints main agrees with Postgres and exits 0; otherwise it exits 1, so it can run from cron or CI and alert. A merge whose outcome is still in doubt is refused (exit 2), as settling it writes. Postgres sources only for now.
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).
serve --follow
chronos serve ./crm --follow # main takes Postgres's changes about a second after they commitchronos serve ./crm --follow --refresh-every 1h # and a whole refresh now and then, to be surechronos refresh ./crm --unfollow # drop the replication slot, once the folder is no longer followedA 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 attach role needsREPLICATION(or superuser). Self-hosted:ALTER SYSTEM SET wal_level = logicaland a restart. RDS and Aurora:rds.logical_replication = 1in the parameter group, andGRANT 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 them the server says why and keeps trying less often.
--refresh-everyworks 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 underREPLICA IDENTITY NOTHING, more than 100,000 rows changed at once, or columns that changed (which a refresh takes). A partitioned table’s partitions count as it. - With
--lazy-tenant, follow reads only the tenants loaded.
| Message | Means |
|---|---|
following Postgres’s changes through replication slot chronos_... (it keeps Postgres’s WAL until read: chronos refresh <folder> --unfollow drops it) | follow is working |
... needs wal_level = logical there (ALTER SYSTEM SET wal_level = logical, then a restart; RDS: rds.logical_replication = 1; Cloud SQL: cloudsql.logical_decoding = on). Until then, chronos serve --refresh-every 5m takes them on a timer | wal_level is not logical |
... needs a role with REPLICATION (ALTER ROLE ... REPLICATION; RDS: GRANT rds_replication TO ...). Until then, chronos serve --refresh-every 5m takes them on a timer | the attach role may not make a slot |
... every replication slot Postgres allows is in use (max_replication_slots) | raise max_replication_slots or drop an unused slot |
... Postgres hasn’t the test_decoding plugin (it comes with Postgres’s contrib modules) | install the contrib modules |
dropped replication slot chronos_...: Postgres keeps no WAL for this folder now | --unfollow dropped the slot |
no replication slot to drop (chronos_... isn’t there) | --unfollow found none |
Schema changes in Postgres
No statement may change a table’s shape in the folder. Change it in Postgres, as a migration does, and a refresh takes it.
| Change in Postgres | What a refresh does |
|---|---|
ALTER TABLE ... ADD COLUMN | the column is 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. Listed as added. One stopped halfway is redone at the next |
A column added with a default worked out per row (gen_random_uuid(), random()) | taken; the default is set once main’s rows have Postgres’s values |
| A column added with a sequence or identity default, or a generated one | not taken: the table is left as it was and named (skipped) |
| A column dropped, renamed or retyped | done in main too, before its rows are read (altered). Attach keeps each column’s attnum, so a rename is told from a drop and an add |
ALTER TYPE ... ADD VALUE (an enum label) | added to main’s type, in Postgres’s order, before the rows are read (labels). A label main has that Postgres hasn’t (renamed there) is left, and said |
| A changed key, row security, or generated column; a key or generated column retyped | the table is left as it was and named (skipped). Attach again into a new folder to take the new shape |
- Until a refresh has taken an added, dropped, renamed or retyped column, a merge writing to the table is refused and says to refresh. Until a refresh has taken an enum label, a world can’t write it.
- 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 OURStoo. A row it inserts leaves those columns out, so Postgres fills them as it fills its own rows, and main takes the row back as Postgres has it. - Worlds forked before a column was dropped, renamed or retyped 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 folder attached before
attnumwas kept fills it in at its next refresh; until then a rename is refused.
Sequences and ids
- Serial and identity columns take their new values from Postgres’s own sequence, reserved 1,000 at a time. An id a world gives a row is never given to another row by Postgres, and it 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
USAGEon the sequence.
Dry runs
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, thenROLLBACK. - Nothing is written to Postgres, and main doesn’t take Postgres’s rows (a real merge’s conflict does).
- Each row gets a
postgrescolumn: Postgres’s row now. A row changed there since main’s copy has outcomechanged in postgres, and the plan’sblockedrow 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.
- If Postgres can’t be reached (or no
CHRONOS_SOURCE_URLis set), the plan still comes back, and thepostgresrow says it wasn’t checked. - Merge checks run on main only in a dry run: its Postgres transaction is read only, so the merge’s rows aren’t there to check.
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.
Rehearsals
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 withRETURNING, so triggers, defaults, constraints and foreign keys’ actions act. ThenSET CONSTRAINTS ALL IMMEDIATE, so deferred constraints and constraint triggers run now. Then the merge checks on attached tables. ThenROLLBACKinstead ofCOMMIT. - It runs as the attach role, 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.
- Nothing is sent, nothing is merged, and main doesn’t take Postgres’s rows.
merge world fix_leads rehearse fail at 2stops after the second write statement, as if it had failed there, to watch the rollback on your rows.- 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.
The plan comes back as a dry run’s, with more rows:
| Outcome | Says |
|---|---|
rehearsed | each statement it ran and the rows Postgres returned |
postgres set | each row Postgres stored otherwise than it was sent (a trigger or default set a column: the value main would take) |
postgres wrote | tables that triggers or cascades wrote besides |
held | a merge check that found rows |
stopped | 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 |
effect | each NOTIFY ON MERGE the merge would send once it lands |
Checking reads
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, which a row changed straight in Postgres never reaches. So the write-back also reads, for each attached table the world read:
| The world read | What Postgres is asked |
|---|---|
| Rows by key | those rows, compared as the write-back compares the rows it writes |
A filtered scan (where org_id = 42 and status = 'open'), with or without an index | only 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 is asked, so more rows are compared, never fewer |
| A range, a whole table, a lookup through an expression index, a key that found no row, or a filtered scan with no condition Postgres can be asked by | the table whole, at most 100,000 rows. Past that the merge is held for a person, saying so |
| Anything, as an agent held to a tenant | of a table read whole, only that tenant’s rows: the fork point’s, 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 |
- A row added in Postgres that the read would have found counts, an empty result included. A row the read couldn’t have found doesn’t hold the merge.
- A read that changed holds the merge as any stale read: refused with
40001and 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 withrerun_reads) runs its SELECTs again on those Postgres rows before holding. The same answer there, and nothing is held for it. Another answer, and the reason says what the query gave then and gives in Postgres now. CHECK READSruns the same check between steps, outside any merge.- The check reads without a predicate lock (
READ COMMITTED): a row Postgres takes between the check andCOMMITlands after the merge in time, an order in which the world’s decision was right.
Writing as the end user
By default the write-back runs as the attach role. That role must see every row, so Postgres’s row-level security never judges what it writes. For an agent that works for one of your users, give it that user’s Postgres role and session settings, and its writes are judged as that user’s own:
create agent run_42 with ( can = 'read,fork,write_own,merge_own', tenant = 'org_id = 42', pg_role = 'authenticated', pg_settings = '{"request.jwt.claims": {"sub": "u-17", "role": "authenticated"}}');grant authenticated to chronos_attach; -- SET ROLE needs the attach role to be a member- How it runs. In a write-back of a world this agent forked, rows are locked and compared as the attach role, as always. Then
SET LOCAL ROLE authenticatedruns, andset_config(name, value, true)for each setting. The inserts, updates, deletes and deferred triggers run as that role.RESET ROLEfollows, and the counts, merge checks and the merge’s own row run as the attach role again. Everything is still one transaction. - What Postgres judges. Each write, with its own grants and policies, using the claims (Supabase’s
auth.uid()readsrequest.jwt.claims). A row the policies hide from an update or delete is one the statement doesn’t write. A row theirWITH CHECKrefuses is an error. Either way the merge is refused with42501, which names the role and the settings (never their values), and nothing is written. - Who the merge runs as. A person who merges or approves the world still writes as the end user. So do
MERGE ... REHEARSEand a merge by the agent itself. A world keeps whom it was forked for: changing or dropping the agent’s role later doesn’t change worlds it already forked. - Rules. Only an admin sets
pg_roleandpg_settings. Settings must be custom ones (prefix.name, such asrequest.jwt.claimsorapp.user_id), at most 32 of them. A value that isn’t text is stored as its JSON text.pg_settingsneed apg_role, andpg_role = ''removes both. A superuser orBYPASSRLSrole is refused, since it reads past row-level security. - If
SET ROLEfails, the error says the attach role must be a member of the role, and that the role must exist. - Gateway. Chronos Gateway sets these per run from
act_asinPOST /v1/runs.
Merge checks run in Postgres
A merge check runs first on main, Chronos’s copy, as for any merge. A row added straight in Postgres since the last refresh 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. 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 with23514(breaks merge check one_lead_per_email in Postgres, which finds {"email":"ada@x.com","n":2}), or queued inSHOW REVIEWSunder the agent’s merge policy. The world and its queued effects (NOTIFY ON MERGE) stay in the world. - How it runs: the check’s SQL runs in Postgres as written, under the write-back’s settings (UTC, ISO dates,
search_path = public) with its timeout asstatement_timeout. A check that fails or times out there holds the merge too. - A check with no
ON TABLESmay read any table, Chronos-only ones too, so it runs only on main. Give itON TABLESnaming the attached tables it reads to have it run in Postgres. MERGE ... DRY RUNruns merge checks on main only. A rehearsal runs them in Postgres.
writeback.json
Every column is compared and written by default. To change that table by table, put a writeback.json in the attached folder (or point CHRONOS_WRITEBACK_RULES at the file) and keep it in git, reviewed like code. It is read again at every merge into main, so a change takes effect at the next merge. A file that doesn’t parse, or a rule naming a column the table doesn’t have, refuses merges (write-back rules: ...) rather than writing with rules nobody meant.
{ "mode": "enforce", "isolation": "read committed", "tables": { "inventory": { "delta": ["stock"], "ignore": ["seen_at"], "check": "stock >= 0" }, "leads": { "keep_live": ["last_seen"] }, "notes": { "procedure": "app.apply_notes", "outbox": "app.chronos_outbox" } }}| Rule | What it does | For |
|---|---|---|
delta | Numeric columns written as increments: stock = stock + (world’s value - main’s value). They are never compared, so the app changing them doesn’t make merges conflict. Inside Chronos, a row both sides changed combines as theirs + ours - base. | Stock, balances, counters |
ignore | Columns not compared: the world’s value is written whatever Postgres holds (last writer wins). | updated_at, notes |
keep_live | Columns not compared and not written by an update: Postgres’s value stays. | last_seen, app-owned fields |
check | A condition every row the merge updates or inserts must meet in Postgres, after the writes and before COMMIT. Rows that break it hold the merge, as a merge check does. A NULL answer passes, as with a CHECK constraint. | Invariants nobody wrote as a constraint |
writable | A condition every row the merge updates or deletes must meet in Postgres as it is now, checked on the locked rows before any write. A row that doesn’t refuses the merge with 42501. | Closed deals, paid invoices |
scope | The rows a merge may reach, as any SQL condition (several columns, a subquery, current_setting('app.org_id') from the agent’s pg_settings): checked on the rows it updates or deletes before the writes, and on the rows it leaves after them, so a merge can’t reach past it or move a row out of it. | Tenancy that isn’t one column |
resolver | Your function f(main jsonb, world jsonb, postgres jsonb) returns jsonb settles a row the world and Postgres both updated, instead of refusing the merge. The row it returns is written (columns it leaves out keep the world’s values; null keeps Postgres’s row), and main takes it. It can’t change the key. | Rows the app and agents both edit |
procedure | Your function f(changes jsonb) returns bigint is called in place of the inserts, updates and deletes, after the rows are locked and compared. It must return how many changes it applied, and main then takes the table from Postgres. | Writes that must go through the app’s own logic |
outbox | Each changed row is inserted into this table in the same transaction: merge_id, world, table_name, op, key, before, after, at. Your worker acts on it after COMMIT. | Emails, API calls, events |
mode: "observe" | Every write-back runs whole (locks, writes, checks), then rolls back. The merge is refused and says what it would have written. | The first weeks |
isolation: "serializable" | Write-backs run at SERIALIZABLE, so Postgres refuses one that forms write skew with a concurrent transaction. | Invariants across rows |
changes, for a procedure and the outbox alike, is a list of {"op": "insert"|"update"|"delete", "key": {...}, "before": {...}|null, "after": {...}|null}, with each value as Postgres’s text. The rules file is trusted like code: check, procedure and outbox run as the attach role.
Tenants and --lazy-tenant
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', ...), or HTTP create_agent with tenant and valid_for in ms.
- In every world it reads and changes only rows of tables with an
org_idcolumn that hold 42, whatever its role, the table’s own row security, orrow_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). 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.
- 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 (
tenantin 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 notSHOW REVIEWS,SHOW AUDITor the wholeSHOW HISTORY. The row API is refused on tables the tenant limits, and its writes on shared tables. - Its token stops working once
valid_forhas passed.
Loading a tenant at a time
A multi-tenant database is often too big to copy whole, and an agent only ever works for one tenant. chronos attach shop --tables orgs,leads,notes --lazy-tenant org_id copies the tables without an org_id column whole, and makes the others empty.
- A table with the column starts empty.
- So does a table without it whose foreign key points straight at one with it (
notes.lead_idtoleads): its rows are loaded with their parent’s. A table two foreign keys down is refused, naming the fix (add the column, or leave the table out). - The first time a world is forked from main for a tenant, main takes that tenant’s rows from Postgres, and then the world is made. This happens when an agent holds
tenant = 'org_id = 42', or a person runscreate world w with (tenant = 'org_id = 42'). It is one read (REPEATABLE READ, read only), and Postgres’s index on the column makes it quick. Later forks for the same tenant cost nothing more. chronos refresh shop --tenant 42loads a tenant ahead of time. It printstenant 42: main took N rows from Postgres, ortenant 42: main has its rows already.- Refreshes and
serve --followread only the tenants loaded, and the folder’s record keeps the list (attached.json,lazy.loaded). - A world forked for no tenant sees only the tenants loaded so far. A world forked from another world starts from that world’s rows, so nothing is loaded for it.
Undo
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 CHANGEDthat row is left as it is and the rest go back. UNDO AGENT bot SINCE '-1 hour'(andUNDO 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:effects_not_undone), with where it is:sent,queued(not sent yet:ACK OUTBOX '<id>'drops it), orclaimed/ambiguous(it may have been sent). The undo itself leaves the outbox alone.
Undo by merge id, with chronos.merge_rows
UNDO MERGE OF WORLD rebuilds the merge from the folder’s history, which a new folder keeps for an hour. For longer, each write-back also keeps the rows it wrote in Postgres, in chronos.merge_rows, in its own transaction: the row as main had it (checked equal to Postgres’s under lock) and the row as Postgres stored it. They are kept 30 days (CHRONOS_UNDO_WINDOW_DAYS), whatever the history window, and outlive the folder.
UNDO MERGE ID 'merge:...' [, 'merge:...'] [SKIP CHANGED] [ALLOW CASCADE] [DRY RUN]- The id. A merge over HTTP answers its id:
{"merged": n, "merge": "merge:..."}. - HTTP
undo_mergewithids: ["merge:...", ...]puts back every row those merges wrote, newest merge first, in one transaction, through Postgres as above. It answers{ok, dry_run, undone, skipped, already, merge, rows, cascades, effects_not_undone}.mergeis the undo’s own id. Each ofrowshas itsmerge,table,key, what the merge did (op),before,after, andstatus(put_back,changedwith the row as main holds itnow, oralready). - A row already put back is
already. From 0.5.2, a row that is as it was before its merge is neither undone nor skipped, and never refuses the undo. The reply counts such rows inalready. Before 0.5.2 they read as changed since. skip_changed: trueleaves rows changed since and undoes the rest. Without it, any changed row refuses the undo with nothing written: 409 with code40001.dry_run: trueanswers the same list and writes nothing. It runs the undo’s writes in Postgres as far asCOMMITand rolls them back, so rows are locked for that moment. A row changed only in Postgres shows aschanged, andcascadesnames the tables Postgres would write rows of besides the undo’s own.- Cascades hold an undo. Undoing an insert deletes the row; with
ON DELETE CASCADE(or a trigger) Postgres would also change rows other people added under it since. That refuses the undo (409, code2BP01, nothing written) unless it saysallow_cascade: true. Only attached tables are counted. - Effects aren’t undone. The effects the merges queued are answered in
effects_not_undone, eachsent,queued,claimedorambiguous; ones still delayed are taken back unsent (cancelled). - An undo is a merge: it has its own records, and its rows obey
writeback.jsonlike any merge’s. - A merge undone in part can be undone again. After an undo with
skip_changed, the rows it left go back in a later undo of the same id, once they hold what the merge wrote. The rows the first undo put back arealready. The check is still inside the undo’s own transaction. - Undoing a merge twice does nothing. From 0.5.2 a second undo of a merge already undone finds every row
alreadyand writes nothing. It is not an error. attachedsaysundo: {ready, window_days}.readyis false when the table is missing or the role can’t insert into it. Pruning past the window needsDELETEon it; without that, old records stay.
Restore drill
Before you let an agent merge unattended, rehearse the restore on your own data.
Rehearse the merge
merge world w rehearseruns every statement in Postgres and rolls back. Check thepostgres setrows (what triggers would set), anystoppedrow, and therolled backrow saying Postgres is as it was. Postgres: unchanged. Outbox: unchanged (theeffectrows list what the merge would send).Watch it fail halfway
merge world w rehearse fail at 1stops after the first statement.rolled backshows the rollback held. Postgres: unchanged.Merge
merge world w. Postgres: the rows changed, in one committed transaction, and a row inchronos.merges. Outbox: the merge’s effects queued in main, sent once a worker claims them.Undo
undo merge of world w(skip changedif your app changed some of the rows since). Postgres: the rows as they were before the merge, in one more transaction, with its ownchronos.mergesrow. Rows changed since are left, and counted. Outbox: untouched. TheWARNINGlists the merge’s effects, sent or still queued, for you to follow up by hand, or drop the queued ones withACK OUTBOX.
Limits and what is not there
- Stage 1, for staging copies, by the engine’s own guide.
- Postgres 11 and older are refused.
- Main is as old as the last refresh for rows no merge touched, unless
serve --followruns. - 50,000 changed rows per merge by default (
CHRONOS_ATTACH_MAX_ROWS, up to 1,000,000), orIN CHUNKS OF n. - 100,000 rows is the broadest read
check_readscan check against Postgres. A world that reads more broadly (a range or a whole-table read of a bigger table, or a filtered scan more than 100,000 of its rows can match) has its merge held for a person, saying so. - No shape changes in the folder. A changed key, row-level security or generated column needs a new attach into a new folder.
- Not attached: tables with no usable key, inheriting tables, a partition alone, tables whose row-level security hides rows from the role.
- Effects are never undone by an undo: it lists them.
- Text ordering and timestamps without an offset differ from a database with a language collation or a non-UTC
timezone(see what is attached).
Asking what a folder is attached to
POST /v1/attached (the database’s own user only) answers {attached: false}, or {attached: true, source, kind, capabilities, undo: {ready, window_days}, copied_at, refreshed_at, as_of, pending, tables: [{name, source_table, postgres, inserts, key, columns: [{name, type}]}]}, with no secret in it. For Postgres kind is postgres, and every capability is true: follow, dry_run, check_reads, act_as, lazy_tenant, source_checks. source_table is the table’s name in Postgres’s SQL; postgres holds the same under its older name.