Rotor agent connection instructions Rotor is a database for applications that need to show or check later what their data was. It runs PostgreSQL-style SQL and keeps every committed version of every row. Any read can run at a past commit and return the data exactly as it was then. So an application can record the commit each decision read, explain or correct the decision later, and test a change on past decisions with the data they saw. - SQL keeps its PostgreSQL meaning, or is refused with an error that names what to use instead. - Each statement commits on its own. Over PostgreSQL, a transaction block first locks the tables it writes; over HTTP, up to 16 writes sent together as `statements` commit together. - Rotor's own features are `rotor_` functions, which you SELECT from to read, and `rotor_` procedures, which you CALL to change something. - Rotor keeps your data. One server runs the service, with no failover. Rotor is in preview: tell the user before storing data, and keep a copy outside Rotor of anything they cannot afford to lose (details: /assets/connection/agent-preview.txt). ## Public data to build with Rotor publishes `job-postings`, a database every account can read for free: nothing is charged for its data, and queries on it count toward the account's usage like any other. It holds open job postings that may involve engineering with existing AI models, from employers' own job boards: each posting's full text and links, with answers to questions such as the kind of work, the countries and US states someone could do it from, how much is in an office, experience level, pay, and visa and relocation help. Find its ID with `{"op":"public_databases","after":""}` and read its `about` table first: it explains the tables and how to search them. If the user wants something to build with Rotor and has nothing in mind, suggest starting with a small app that helps them find a software engineering job that works with AI models: it searches the postings by their answers and keeps the user's shortlist, notes and applications in a database of their own, with the commit each search read, so it can show later exactly what a posting said. - Read a public database by naming its ID as the request's `"database"` (or the PostgreSQL database name) and using its own table names: this needs no database of your own, and writes there answer 42501. To join it with the user's own tables, name its tables with `rotor_database('ID', 'TABLE')` from a database of the user's own, creating one if needed. ## Connect Use the origin from the user's connection instruction, over HTTPS only, and never follow a redirect to another origin. Then use Rotor through PostgreSQL, with psql or your language's standard driver, or through HTTP where your sandbox allows only HTTPS. Save ORIGIN/assets/connection/rotor.py (Python 3, standard library only) and run `python3 rotor.py --origin ORIGIN --credentials PRIVATE_FILE connect --project my-project`. Show the user the code it prints and ask them to enter it at ORIGIN/connect; do not approve for the user. The client waits for the approval and saves the token in PRIVATE_FILE, and the PostgreSQL password file beside it. Never put the token in chat, Git, URLs or logs; if it is lost, connect again and have the user revoke the old connection. A connection reaches only the projects the user approves. Each of the account's databases belongs to one project, and a connection reads, or reads and writes, the databases of its projects; it cannot see the account's others. Public databases and those shared with the account stay readable. Ask for what the task needs: `--project NAME` asks to read and write a project, existing or new (repeat it for more), `--read-project NAME` only to read one, and `--whole-account` for every database. Asking for none asks for a new project named after the connection. The user sees the request, can change it when approving or later on /account, and the client prints what was approved. Without Python, connect over HTTP. POST /v1/device/start with {"name":"My agent","projects":{"my-project":"write"}} ("read" only to read; {"account":true} in place of "projects" for every database); keep result.device_code private and show result.user_code for the user to enter at /connect. Poll POST /v1/device/poll with {"device_code":"PRIVATE_CODE"} at the returned interval until result.status is `connected`, and save result.token, which is returned once; result.access is what the user approved. Then send Content-Type: application/json and Authorization: Bearer TOKEN to POST /v1/host. Over PostgreSQL the token is the password of user `rotor` at result.postgres.host and result.postgres.port, with sslmode=verify-full; keep it in a password file, not in a connection string. Name yourself: set PGAPPNAME to a short name for this agent or job, such as `invoice-triage-3` (or the driver's `application_name`, the client's `--application-name`, or `application_name=` in a script). Every commit you make records it, so the user can see what each agent wrote. ### PostgreSQL python3 rotor.py --credentials PRIVATE_FILE create --database my-data eval "$(python3 rotor.py --credentials PRIVATE_FILE postgres --database my-data)" psql "$DATABASE_URL&sslrootcert=system" -X -c 'SELECT 1' - `create` makes a database and prints its `id`, `name` and `project`; running it again returns the same database. A database's name or `id` is its PostgreSQL database name. - There is no `postgres` database: psql and drivers must name a database you created, as DATABASE_URL does, and any other name fails with 3D000. - `postgres` prints `export PGPASSFILE=...` and `export DATABASE_URL='postgresql://rotor@HOST:PORT/my-data?sslmode=verify-full'`. Neither holds a secret: give DATABASE_URL to the driver, which reads the token from PGPASSFILE. In a script: `rotor.postgres_env(auth, "my-data")` returns both. psql and other libpq-based drivers, such as psycopg, need `&sslrootcert=system` added, to check the certificate against the system's authorities. - From Python, use what is installed; both reach the same databases. With psycopg (`python3 -c 'import psycopg'` succeeds): `psycopg.connect(os.environ["DATABASE_URL"] + "&sslrootcert=system", autocommit=True)`, which reads the token from PGPASSFILE. With no PostgreSQL driver: `import rotor` and use it over HTTP, as in the HTTP section below; it needs only the standard library. - Each statement commits on its own. A transaction block writes only the tables its first LOCK TABLE names, in EXCLUSIVE MODE; it may read any table. Other sessions keep reading those tables, and their writes wait until the block ends: ```sql BEGIN; LOCK TABLE stock, orders IN EXCLUSIVE MODE; UPDATE stock SET n = n - 1 WHERE id = 7 AND n > 0; INSERT INTO orders (item, qty) VALUES (7, 1); COMMIT; ``` - A write in a block to a table it did not lock fails with 0A000, and the block then fails until ROLLBACK, as in PostgreSQL. - Or, as with PostgreSQL, run the block at REPEATABLE READ and take no lock. It reads the database as it was at its first statement. If another transaction changed a row the block writes after that, the block fails with 40001 (an insert of a key taken since: 23505), at that statement or at COMMIT, and nothing of it is kept: ROLLBACK and run the whole block again. Blocks that write different rows do not conflict, and no block waits for another: ```sql BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE stock SET n = n - 1 WHERE id = 7 AND n > 0; INSERT INTO orders (item, qty) VALUES (7, 1); COMMIT; ``` - `SET default_transaction_isolation = 'repeatable read'` makes every block of the connection REPEATABLE READ, as for code an ORM generates. - In such a block, `SELECT ... FOR UPDATE` reserves nothing: it reads the rows, and the block fails with 40001 if another transaction changes one of them before COMMIT. Where PostgreSQL would make the other writer wait, Rotor lets it commit and fails the block. - A statement outside a block that changes a row an open block has written commits at once, and the block fails at COMMIT with 40001; in PostgreSQL the statement would wait and the block would win. - Connect in autocommit mode, the default of most drivers (in psycopg, `autocommit=True`), and open a block only where several statements must commit together. While any connection has a block open that has read or locked, schema changes in that database are refused with 0A000, so end each block promptly. - An account has at most 16 PostgreSQL connections at once, and the next one fails with 53300: keep the pools of all your processes within 16 together. - A write over PostgreSQL does not report its commit: read it from the row, as in `SELECT rotor_commit FROM stock WHERE id = $1`. `SELECT head FROM rotor_status()` gives the commit that reads see now, which includes any connection's later writes, so it is your write's commit only if nothing has written since. - To read several statements at one commit, use `BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY`: each reads the commit current at the block's first read. - Load and export with COPY, as in PostgreSQL: psql's `\copy notes FROM 'notes.csv' (FORMAT csv, HEADER)`, psycopg's `cursor.copy()`, asyncpg's `copy_records_to_table`. Outside a block a COPY of any size is one commit. COPY to or from a server file or PROGRAM is refused: use `\copy`, or STDIN and STDOUT. - If PRIVATE_FILE has no `postgres`, this service has no PostgreSQL listener yet: use HTTP. ### HTTP python3 rotor.py --credentials PRIVATE_FILE sql --database my-data 'SELECT id, body FROM notes WHERE body LIKE $1' --param 'note%' python3 rotor.py --credentials PRIVATE_FILE load --database my-data --table notes --input notes.jsonl python3 rotor.py --credentials PRIVATE_FILE copy --database my-data 'COPY notes FROM STDIN (FORMAT csv, HEADER)' --input notes.csv - `sql` prints up to 50 rows and a summary line with the commit (`--all`: every row; `--json`: the whole result; `--read-at COMMIT_UUID`, `--read-only` and `--set NAME=VALUE`: the settings below). - `load` sends a file of JSON objects (an array, or one per line), keyed by column name, as gzip-compressed COPY: one commit for up to about 28 MB compressed, a larger file as several COPYs, each its own commit. `--skip-existing` first reads the table's primary keys and leaves out rows that have one, so a load that stopped can be run again. - `copy` runs one COPY: FROM STDIN sends `--input` or standard input, TO STDOUT writes to `--output` or standard output. - In a script: `import rotor`, `auth = json.load(open(PRIVATE_FILE))`, then `rotor.host(auth, op="list_databases", after="")`, `rotor.sql(auth, database_id, statement, parameters, read_at=None)`, `rotor.atomic(auth, database_id, statements)`, `rotor.load(auth, database_id, table, rows)` or `rotor.copy(auth, database_id, statement, data)`. Each returns the `result` described below or raises `rotor.RotorError`, and waits and sends again when an error says `retry`. Of the account's own databases, a connection reaches only its projects': {"op":"list_projects"} lists them with its access, a database it creates goes into its project (with write access to several, name one with "project", or the client's `create --project NAME`), and any other of the account's databases answers as if it did not exist. A write through read-only access fails with 42501. A connection cannot widen its own reach: the user moves databases between projects and changes a connection's access on /account; permanent deletion also needs the user to allow it on /account. Begin with {"op":"list_databases","after":""} and {"op":"usage"}, report what you found, and ask before changing application data. Never discard history or purge anything without the user approving the exact table and scope. Work runs on the trial's or the plan's allowance. `usage`'s result.plan says how much is left: its state (trial, active, past_due, lapsed or none), when the trial ends (trial_ends_at), the allowance resets (allowance_resets_at) and a cancelled plan ends (ends_at), and the used and included cpu_hours, memory_gib_hours, transfer_bytes and storage_bytes. A credit of 0 is normal: it refuses nothing on a trial or within a plan. plan is null when the account runs on credit alone. ## Run SQL Reuse an appropriate database, or create one when the task needs it; choose its name yourself (1 to 64 letters, numbers, hyphens or underscores), and repeat the same request_id if the reply is lost. Requests name a database by its name or by the returned `id`: ```json {"op":"create_database","name":"my-data","request_id":"create-my-data"} ``` Send each statement to POST /v1/host: ```json {"op":"sql","database":"my-data","sql":"SELECT id, body FROM notes WHERE created_at > $1 ORDER BY body","parameters":["2026-11-01T00:00:00Z"]} ``` A read answers: ```json {"ok":true,"result":{"command":"SELECT","columns":[{"name":"id","type":"uuid"},{"name":"body","type":"text"}],"rows":[["ROW_UUID","Hello from Rotor"]],"commit":"COMMIT_UUID"}} ``` - Parameters and values are plain JSON, and a parameter takes its type from where it is used. bigint and numeric come back as strings so no digits are lost (`"12.50"`); send them as numbers or strings. timestamptz is RFC 3339 text in UTC (`"2026-11-10T08:00:00Z"`), uuid and bytea use PostgreSQL's text forms, and jsonb is embedded JSON. - A read reports `commit`, the commit it read. A write or schema change reports `affected_rows`, its `commit` when it changed something, and the RETURNING rows. - Settings change how a request runs, as `"settings":{"application_name":"NAME","rotor.read_at":"COMMIT_UUID"}`. A request's settings last for that request alone; over PostgreSQL the same names are settings, given with SET, SET LOCAL or the connection's options. The settings: - `application_name`: a short name for this agent or job, which its commits record. Give it on every `sql` request; other requests ignore `settings`. - `rotor.read_at`: a commit. A SELECT reads that commit, with ordinary table names and the schema as it was then, and a statement that would change data is refused. - `transaction_read_only`: `true` refuses any statement that would change data. - `statement_timeout`: stops a SELECT that runs longer, such as `"5s"`; a bare number is milliseconds. - `rotor.max_examined`: a SELECT fails once it would examine more than this many keys. - Errors are `{"ok":false,"error":{"code":"sql","detail":{"sqlstate":"0A000","message":"..."},"committed":false,"retry":false}}` for SQL, or another code with an optional detail (below). To change several rows or tables together, send up to 16 INSERT, UPDATE and DELETE statements as `statements`. They commit together at one commit, or none of them does: ```json {"op":"sql","database":"DATABASE_ID","statements":[{"sql":"UPDATE accounts SET balance = balance - $1 WHERE id = $2","parameters":[10,"ACCOUNT_ID"]},{"sql":"INSERT INTO transfers (account, amount) VALUES ($1, $2)","parameters":["ACCOUNT_ID",10]}]} ``` - The answer is `{"ok":true,"result":{"results":[...],"commit":"COMMIT_UUID"}}`: one result per statement and the commit they share. - Each statement sees the earlier statements' changes. Only the first may hold a subquery or INSERT ... SELECT, and an UPDATE or DELETE of a table an earlier statement changed must give whole primary keys in its WHERE (`id = $1` or `id IN ($1, $2)`). - An error refuses the whole request and names the statement (`statement 2: ...`). SELECT, CALL and schema changes run as requests of their own. To load or export many rows, send PostgreSQL's COPY, the request on the body's first line and the data on the lines after it: ``` {"op":"copy","database":"my-data","sql":"COPY notes (body) FROM STDIN (FORMAT csv)"} "first note" "second note" ``` - It answers `{"ok":true,"result":{"command":"COPY","affected_rows":2,"commit":"COMMIT_UUID"}}`: every row commits, or none does. `COPY ... TO STDOUT` answers with the data itself, as text. - Send a large body with `Content-Encoding: gzip`, which any request may use: a COPY takes 32 MB as sent and 1 GiB of data; send more as several COPYs, each its own commit. ## How Rotor differs from PostgreSQL - Transactions: a PostgreSQL block writes only the tables it locked first with `LOCK TABLE ... IN EXCLUSIVE MODE`; code an ORM generates takes no such lock and is refused, unless its blocks run at REPEATABLE READ. Over HTTP there is no BEGIN or COMMIT: to change several rows or tables together, use one multi-row statement, INSERT ... ON CONFLICT, a conditional UPDATE, or `statements`. - Types: boolean, smallint, integer, bigint, numeric (exact decimals of up to 38 digits), real, double precision, text, varchar(n), bytea, uuid, date, timestamp, timestamptz and jsonb. numeric, real and double precision cannot be primary-key columns. For char(n), use varchar(n) or text. A date plus or minus an integer is a date, and two dates subtract to their days; there is no interval type. date and timestamp do not convert to or from timestamptz, which would read the session's time zone. - Keys: every table needs a primary key. Prefer `uuid PRIMARY KEY DEFAULT uuidv7()`, which rises with time so rows sort by creation; keys the application chooses work too. serial and GENERATED ... AS IDENTITY work on a single-column smallint, integer or bigint primary key, but a retried insert gets a second ID. - Constraints and indexes: PRIMARY KEY, NOT NULL, DEFAULT (constants, now(), uuidv7(), gen_random_uuid()) and UNIQUE; `CREATE [UNIQUE] INDEX` on columns. No foreign keys, CHECK, or indexes on expressions or with WHERE. `ON CONFLICT (email)` works on a UNIQUE column. - Joins: up to 16 tables with JOIN, LEFT JOIN, CROSS JOIN, commas, USING or NATURAL. On its primary key or an index's first columns the join reads only the matching rows; on another column it reads the whole table, so CREATE INDEX on a column you join by often. A table with no = condition to the tables before it, as in CROSS JOIN or `JOIN p ON true`, pairs each of its rows with each row before it, so keep such tables small. No FULL JOIN, and RIGHT JOIN only as the first join. - Subqueries: IN (SELECT ...), scalar subqueries and EXISTS, which may read the outer query's columns through = conditions in their WHERE between an outer column and their own expression; WITH (not RECURSIVE); UNION, INTERSECT and EXCEPT, with or without ALL; subqueries in FROM, joined like tables; and INSERT ... SELECT of any number of rows. An IN (SELECT ...) that reads no outer column gives up to 1,024 values. One that reads the outer query's columns runs in a SELECT, not in UPDATE or DELETE, and as a scalar subquery without an aggregate it must compare a primary key or UNIQUE column with =. - Series: `SELECT g FROM generate_series(1, 100) g`, with an optional step, over integer or bigint and alone in its FROM, as in `INSERT INTO t SELECT g, ... FROM generate_series(1, $1) g`. - Expressions: comparisons, AND, OR, NOT, + - * / %, IS [NOT] NULL, IS [NOT] DISTINCT FROM, IN, BETWEEN, LIKE, ILIKE, CASE, casts and `||`, which joins text with text or with any other value (`'order-' || id`), with the functions coalesce, length, octet_length, lower, upper, round, date_trunc, extract, date_part, now(), uuidv7(), gen_random_uuid(), md5 (of text or bytea, as hex text), sha224, sha256, sha384 and sha512 (of bytea; of text as `sha256(convert_to(t, 'UTF8'))`), encode and decode (bytea to and from base64, hex or escape text), and convert_to and convert_from (text to and from its UTF8 bytes). Aggregates: count, sum, avg, min, max and string_agg, which joins text or bytea in the order of an ORDER BY inside the call (`string_agg(name, ', ' ORDER BY name)`), or without one in the order rows reach it, with GROUP BY (expressions, output positions such as `GROUP BY 1, 2`, or output names), HAVING, DISTINCT and `FILTER (WHERE ...)`; as in PostgreSQL, sum of bigint and avg return numeric, and `round(x, 2)` takes a numeric, so round a double with `round(x::numeric, 2)`. `date_trunc('day', at)` and `extract(hour FROM at)` read a timestamptz in the session's TimeZone, which is UTC over HTTP. Windows: `row_number()`, `rank()` and `dense_rank()` with `OVER (PARTITION BY ... ORDER BY ...)`; to filter on one, select it in a FROM subquery and filter outside. - JSON: `doc -> 'key'` and `doc -> 0` give an object's member or an array's element as jsonb, `doc ->> 'key'` gives it as text, and `doc #> '{a,b}'` and `doc #>> '{a,b}'` follow a path of keys and indexes. `doc @> '{"status": "open"}'` and `<@` test containment, and `doc ? 'key'`, `?|` and `?&` test for keys; `jsonb_typeof(doc)` and `jsonb_array_length(doc)` describe a value. No index serves these, so on a large table filter on other columns too. To change a document in an UPDATE use `doc || '{"status": "done"}'`, `doc - 'key'`, `doc #- '{a,b}'` or `jsonb_set(doc, '{a,b}', '1')`; build one with `jsonb_build_object('k', v)`, `jsonb_build_array(v)` or `to_jsonb(v)`; a timestamptz goes in as UTC text (`"2026-11-10T08:00:00+00:00"`); a date or timestamp as ISO text (`"2026-11-10T08:00:00"`); cast double precision or real to numeric first. There is no jsonb_array_elements, jsonb_agg or jsonpath. - Schema changes: CREATE TABLE [IF NOT EXISTS]; ALTER TABLE ADD, DROP and RENAME COLUMN, RENAME TO, SET or DROP DEFAULT and NOT NULL, ADD or DROP CONSTRAINT for UNIQUE; CREATE and DROP INDEX; DROP TABLE [IF EXISTS]. No column type changes, views or TRUNCATE. - DROP TABLE frees the name at once, but Rotor keeps the table's data and history until it is purged. `SELECT * FROM rotor_dropped_tables()` lists dropped tables, and `CALL rotor_undrop_table('TABLE_ID')` restores one (add `, 'new_name'` to rename it). - Catalog: `SELECT * FROM rotor_tables()` (table_id, table_name, layout, layout_pending, and history_from: the earliest commit its history answers from), `SELECT * FROM rotor_columns('notes')` and `SELECT * FROM rotor_indexes('notes')`. information_schema.tables and information_schema.columns read as in PostgreSQL, alone in their FROM; there is no pg_catalog. - Layout: tables store rows together, which suits reading and updating records by key. For logs and other tables summarized over many rows, use `CREATE TABLE events (...) WITH (rotor_layout = column)`. Rotor converts files as it merges them; /assets/connection/agent-maintenance.txt covers converting them now. ## Write safely - Choose keys the application knows, and write with `INSERT ... ON CONFLICT (id) DO NOTHING`, so a retry cannot insert twice. - Record events and decisions as new rows instead of overwriting earlier ones. - Make state changes conditional: `UPDATE tickets SET status = 'closed' WHERE id = $1 AND status = 'open'` changes nothing when repeated. - To claim work so that two agents never take the same item, change it only while it is still free: `UPDATE tasks SET status = 'taken' WHERE id = $1 AND status = 'open' RETURNING id` returns the row to one agent and nothing to any other, which then tries another item. - Every error says `committed`: false when the request changed nothing, "unknown" when a write may have committed. After "unknown", a timeout, or a lost or malformed reply, read the intended state before writing again; never blindly repeat increments. `"retry":true` means send the same request again after a pause; the client does this itself. - Otherwise fix the request (`sql`: the SQLSTATE says why; `invalid`; `limit`), or stop and tell the user what the error's message says: `plan_required` when the trial or the plan has ended or an allowance is used up (on a paid plan, only once no credit is left; the user subscribes or updates their card on /account); `credit_required` when an account on credit alone has none left or its data is being deleted (the user asks Rotor support, hello@runrotor.com); `account_paused` (the user resumes Rotor on /account); `purge_not_allowed` (the user allows permanent deletion on /account); `storage_limit` (discard history or purge, or the user asks Rotor support to raise the limit). - DELETE and DROP TABLE keep history and free no storage. `{"op":"usage"}` reports result.storage_quota (used_bytes, limit_bytes); tell the user both before a large import (more: /assets/connection/agent-storage.txt). - To check a load without reading it back, compare each range of keys with the same count and digest computed from your source: `SELECT count(*), md5(string_agg(md5(id || ',' || coalesce(name, '')), '' ORDER BY id)) FROM t WHERE id >= $1 AND id < $2`, joining each row's columns as your program does. Send binary values as base64 text and store them with `decode($1, 'base64')`. ## Use history Every commit has an ID, the `commit` of a response, and IDs sort in commit order. A row version is named by its table, its key and the commit that wrote it. One way to use this; adapt the tables to the application: - Record what a decision read. Make all of its reads at one commit: take `commit` from the first read's response and give it as `rotor.read_at` to the others. Store that commit with the decision, for example `INSERT INTO decisions (id, loan_id, outcome, read_at) VALUES ($1, $2, $3, $4)`. That is enough to recover every row it saw, so do not copy values or look rows up one at a time. - See what decisions saw. Send the same SELECT with `rotor.read_at` set to a stored commit, or read one table with `SELECT * FROM rotor_at('loans', $1)`. For many decisions in one query, join each to the row at its own commit: `SELECT d.id, l.amount FROM decisions d LEFT JOIN rotor_at('loans', d.read_at) l ON l.id = d.loan_id`. Such a join is read row by row: window functions and correlated subqueries do not run over it and its rows have no `rotor_commit`, so compare the columns read, as in `SELECT d.id FROM decisions d JOIN rotor_at('loans', d.read_at) l ON l.id = d.loan_id LEFT JOIN loans n ON n.id = d.loan_id WHERE n.id IS NULL OR n.amount IS DISTINCT FROM l.amount`, and number rows in your program. - See what was written between two commits. `SELECT * FROM rotor_writes('loans', $1, $2)` returns every row version written after the first commit, up to the second (any current read's `commit` serves as the second). Each has the table's columns plus `rotor_commit` (the commit that wrote it), `rotor_deleted` and `rotor_replaced_at` (the key's next write in the range, else null), so `WHERE rotor_replaced_at IS NULL` keeps each key's latest version. A write that sets the same values is still a version; to find real changes, compare with the earlier state: `SELECT w.id FROM rotor_writes('loans', $1, $2) w LEFT JOIN rotor_at('loans', $1) b ON b.id = w.id WHERE w.rotor_replaced_at IS NULL AND (w.rotor_deleted OR w.amount IS DISTINCT FROM b.amount)`. - Know which commit wrote each row. Name the hidden column `rotor_commit` in a SELECT from one table: `SELECT id, amount, rotor_commit FROM loans`. Rows written by one statement share its commit, and a row whose `rotor_commit` is the same as before has not been written since. In a join, select it in a subquery in FROM: `SELECT d.id, w.rotor_commit FROM decisions d JOIN (SELECT id, rotor_commit FROM loans) w ON w.id = d.loan_id`. - Follow one key. `SELECT rotor_commit, rotor_deleted, amount FROM rotor_writes('loans', $1, $2) WHERE id = $3` lists the key's versions, newest first, when the first commit is the table's `history_from` from `SELECT history_from FROM rotor_tables() WHERE table_name = 'loans'`. One version is `SELECT * FROM rotor_at('loans', $1) WHERE id = $2 AND rotor_commit = $1`: the row that commit wrote, even after it has changed or been deleted. - See when and by whom. `SELECT * FROM rotor_commits()` lists recent commits, newest first, each with committed_at (when it was made durable), connection (the ID of the connection that made it; NULL for Rotor's own work) and application_name (the name its writer gave, or NULL). Use them to say when a row changed or was deleted, and by which connection and agent; choose what to read by commit, never by time. A name is what the writer claimed, not proof. - Test a change on past decisions: read what they saw as above, apply the new rule, and compare outcomes. - Try changes on a branch. `{"op":"create_database","name":"loans-try","request_id":"branch-loans-try-1","branch_of":"DATABASE_ID","branch_at":"COMMIT"}` creates a new database holding the original's tables and history up to that commit (its newest without "branch_at"). It shares the original's storage, so it is quick at any size; commit IDs up to the branch point work in both. Change the branch freely: the original never changes. In the branch, `rotor_writes` from the branch point lists what you changed; write back what the user wants with ordinary SQL. Deleting or purging one leaves the other intact. - Read another database. `SELECT * FROM rotor_database('prices', 'quotes')` reads table quotes of another database at its newest published commit. Today that is the newest version that database has written out, which trails its latest by seconds while it writes heavily and by up to about two minutes when it is quiet. It reads a database this connection reaches by name, or one shared with the account or public by its ID (`{"op":"public_databases","after":""}` lists public ones). The response's `published` lists each database and commit read: store that commit with the decision, as you store `read_at`, and read the same rows again with `rotor_database('prices', 'quotes', 'COMMIT')`. For several reads of one database at one commit, take `commit` from `SELECT * FROM rotor_database('prices')` first. It joins like one of your own tables. If that database's owner discards its history, those reads answer that it was erased: copy what must be kept, with `INSERT INTO mine SELECT ... FROM rotor_database('prices', 'quotes') WHERE ...`, any number of rows as one commit; copied rows are yours and stay. Other writes cannot name it: read, then write the values. - History is kept until the user approves discarding it. Missing or erased history fails explicitly; it never substitutes current data. ## Limits and paging - A result has no row or size cap: use LIMIT, or page as below, so a large result does not fill your context. A SELECT runs until it is done, unless the settings `statement_timeout` or `rotor.max_examined` stop it. - One UPDATE, DELETE or INSERT ... SELECT changes any number of rows as one commit: write the whole change as one statement. Inside `statements` or a transaction one changes at most 1,024 rows. An UPDATE that changes a UNIQUE column changes at most 1,024 rows, and one that changes a primary-key column at most 512. A large INSERT ... SELECT ... ON CONFLICT DO NOTHING needs each key once in its SELECT (GROUP BY), and one whose DO UPDATE sets a UNIQUE column inserts at most 1,024 rows. - To load more than a few MB, use COPY: over PostgreSQL `\copy` or the driver's copy, over HTTP `rotor.py load` or a copy request. Never loop INSERTs: each is a round trip and a commit. - INSERT ... VALUES takes up to 1,024 rows and 8,192 parameters, within 64 KiB of SQL and 4 MiB of parameters. - To delete or update rows that another table names, past the 1,024 values of `IN (SELECT ...)`, repeat one statement until it changes 0 rows, each run its own commit: `DELETE FROM t WHERE id IN (SELECT d.id FROM gone d JOIN t ON t.id = d.id LIMIT 1000)`. - Over PostgreSQL, a pipeline of writes before one Sync, such as psycopg's `executemany`, holds at most 4 MiB of messages (54000), about 20,000 small rows; it commits as one transaction. Load more with COPY. - Page with `WHERE id > $1 ORDER BY id LIMIT $2`, and give the first page's `commit` as `rotor.read_at` to the later pages so they read the same commit. With a composite key, fix its leading columns and page the last one. - Use a whole-primary-key predicate for point reads, and `WHERE id IN ($1, $2, $3)` for several keys. ## More Fetch these from the same origin when the task needs them: - /assets/connection/agent-preview.txt: what Rotor keeps and can lose, open-database limits and the trial. - /assets/connection/agent-maintenance.txt: layout, compacting a table now, and checking that a commit has reached object storage. - /assets/connection/agent-storage.txt: the storage limit, permanent deletion, deleting and restoring databases, and exporting current records. - /assets/connection/agent-decisions.txt: an optional starting shape for recording decisions and their inputs, and calls to choose-one models without copying their questions or inputs.