# Operations Abbreviate `moon run cmd/main --target native --` as `mooncdc` below. Errors go to stderr with exit 2. Successful reports use exit 0; inspect `consistent`, `healthy`, `eligible` or `applicable` in their JSON for the reported condition. ## Initialize and resume Configure every writer with `PRAGMA recursive_triggers=ON`. Run `plan SOURCE TABLE...`, then `install SOURCE TABLE...` once. Use `bootstrap SOURCE NEW_TARGET CONSUMER` once and `sync SOURCE TARGET CONSUMER` repeatedly. Default sync processes at most 100 batches of 1,000 events; compare returned watermarks for remaining lag. Never share a consumer between independently progressing targets. `status SOURCE` reports retained events and consumers; `target-status TARGET` reports target cursor, plan and row counts. ## Upgrading from 0.1.0 to 0.2.0 Version 0.1.0 encoded captured REAL values through SQLite decimal formatting. Some values, including `1e300`, were changed by that conversion. A target initialized from a directly read snapshot could then disagree with the rounded before-image of a later ordinary update. Version 0.2.0 replaces that path with exact binary significand/exponent encoding and keeps strict before-image checks. This changes the installed capture trigger SQL. The new verifier intentionally rejects old triggers; replacing just the executable is not a migration. Reading legacy two-element decimal REAL records is supported, but cannot recover precision already lost in old journals or projections. Do not suppress conflicts, compare with a tolerance, or advance a consumer over an unprocessed interval. Use a new capture generation and a fresh snapshot: 1. Stop application writes and all sync workers. Take verified, consistent backups of the old source and targets, retaining the old executable and consumer positions. If 0.1.0 can still process pending changes safely, finish and audit them before retiring that stream. If it encounters the reported conflict, retain the old source, journal and target for investigation rather than skipping the event. 2. Create a **new SQLite database** containing the intended user tables and their current data from the authoritative source. Copy the required user schema, indexes, constraints and business triggers deliberately; exclude every `_mooncdc_*` metadata object and capture trigger. A whole-database backup alone is not this new source because it retains the old capture objects. Verify user data and application behavior in the new database. Preserve the original database and backup; these steps do not require deleting either one. 3. With 0.2.0, run `plan` and `install` against the new source. Configure every application writer with `PRAGMA recursive_triggers=ON`. Generate any filtered plan again from the new source schema, and review its selections and predicates. 4. Run `bootstrap` with a **new consumer name and a new target path**. This creates a fresh snapshot and cursor for the new source identity. Do not reuse old cursors, append new events to an old journal, or reuse the damaged target. 5. Run `sync` and `audit` (with `--plan` for a filtered target). Confirm matching watermarks and data before switching the application and downstream consumers to the new source/target generation. Keep old backups according to your retention policy. Resume writes and continue normal sync only after the switch is verified. For example, after preparing and checking `source-v020.db` from user data: ```sh mooncdc plan source-v020.db orders customers mooncdc install source-v020.db orders customers mooncdc bootstrap source-v020.db report-v020.db reporting-v020 mooncdc sync source-v020.db report-v020.db reporting-v020 mooncdc audit source-v020.db report-v020.db ``` If the old source was itself modified after incorrect data was fed back from a projection, recover authoritative user data from a trusted backup or application record first. New encoding cannot reconstruct historical values absent from the source and its backups. ## Offline transport ### Upgrading intact 0.2.0 sources to 0.3.0 Stop writers and workers, back up databases, then run `upgrade-capture SOURCE --limit 100000`. The limit must cover all retained events. The command acquires a write transaction, verifies exact known trigger definitions, table definitions, UTF-8 database encoding and retained journal decodability, then replaces triggers atomically. Source identity, event sequence, floor, journal and consumer acknowledgements remain unchanged; repeating the command is harmless. Use 0.3.0 for every worker after the upgrade, since new captured TEXT uses a new tag. Unknown or missing triggers, additional business triggers, schema drift and invalid retained events abort the upgrade without partial changes. This is not a repair tool for already lost precision, invalid text or arbitrary schema edits. Such sources require a reviewed migration/new snapshot from authoritative user data. Version 0.1.0 must still follow the fresh-generation migration above. Only UTF-8 databases with valid Unicode TEXT are supported. Invalid UTF-8 detected in historical/external writes stops snapshots, consumption and audits; use a new reviewed snapshot after correcting source data rather than skipping journal events. Do not install other triggers on captured tables. Newly added triggers are detected in the persistent main schema and protected writes abort; their business effects must be reviewed separately. Do not install connection-local TEMP business triggers either: persistent main triggers cannot fully inspect that private schema, so this restriction must be enforced by the application managing writer connections. Version 0.3.1 changes only parameter-expression semantics and table-kind inspection; an intact 0.3.0 source needs no further capture upgrade. The 0.2.0 upgrade command and the 0.1.0 fresh-snapshot procedure remain as described above. ### Upgrading intact 0.2.0/0.3.0/0.3.1 sources to 0.3.2 SQLite builds before 3.48.0 normally limit SQL functions to 127 arguments. Earlier MoonCDC versions generated a 128-argument `json_array` for 128-column tables; installing it with the bundled 3.49.1 engine could make an older writer reject the database schema, including statements for other tables. Version 0.3.2 appends the last cell with `json_insert`, preserving the same wire data without exceeding 127 arguments. See [SQLite function limits](https://www.sqlite.org/limits.html#max_function_arg). Stop writers/workers and back up the database, then use the 0.3.2 CLI to run `upgrade-capture SOURCE --limit 100000`, with a limit covering all retained events. Known 0.2.0/0.3.0/0.3.1 triggers are replaced atomically, preserving identity, journal, retention and consumer state. All widths receive a revised `pragma_recursive_triggers()` guard: function-call syntax prevents an ordinary table of that name from spoofing the PRAGMA value and bypassing REPLACE deletion capture. A collision fails the write instead. An intact upgraded capture does not need a fresh snapshot. Never drop a capture trigger manually to work around a schema error. Library users must remove TEMP tables/views that shadow captured or projected tables or MoonCDC metadata before CDC operations. These operations now reject shadowing rather than accidentally reading or updating a connection-local object. The upgrade cannot repair a target that previously acknowledged writes to TEMP objects or a history that already missed implicit deletes. After upgrading, catch up and audit the persistent source/target. Reinitialize a divergent target from an authoritative snapshot with a new consumer; never advance or rewind cursors as a substitute for rebuilding missing data. SQLite REAL zero is normalized to +0.0 in snapshots and captured images, including BLOB/untyped columns that retain negative zero. Audits intentionally consider these zeros equivalent. If retaining the IEEE sign of zero is an application requirement, store that value as BLOB or an explicit domain representation rather than REAL. ```sh mooncdc snapshot source.db offline --format jsonl --output snapshot.jsonl mooncdc init target.db --format jsonl --input snapshot.jsonl mooncdc export source.db offline --format jsonl --output batch.jsonl mooncdc preview target.db --format jsonl --input batch.jsonl mooncdc apply target.db --format jsonl --input batch.jsonl mooncdc ack source.db offline --format jsonl --input batch.jsonl ``` Export never acknowledges. Ack only after effects are durably committed. Output files must not exist. `--input -` reads stdin. JSONL needs a commit trailer even with zero events. Persist the complete output before using it for recovery. ## Filtered targets ```sh mooncdc make-plan source.db --output projection.json # Edit the plan, preserving all primary key columns. mooncdc bootstrap source.db subset.db subset --plan projection.json mooncdc sync source.db subset.db subset --plan projection.json mooncdc audit source.db subset.db --plan projection.json ``` Each rule contains source schema, target name, selected columns and filter. Example predicates: ```json {"op":"equal","column":"active","value":["integer","1"]} ``` ```json {"op":"all","children":[{"op":"integer_between","column":"id","lower":"1","upper":"100"},{"op":"prefix","column":"name","prefix":"A"}]} ``` Other operators: `always`, `null`, `any`, `not` with `child`. No SQL type coercion. Maximum nesting is 32; each all/any node allows 64 children. Stored plan comparison includes predicates, columns and source schema. Changing a plan requires a new target. Bound-target `apply --plan` and `preview --plan` accept RAW source batches. `project-snapshot` and `project-batch` are separate utilities for external consumers; do not transform twice. Those output files alone do not carry a durable rule lock. ## Recovery Version 0.3.4 rejects table-level ON UPDATE CASCADE/SET NULL/SET DEFAULT cycles that return to a captured table, including self-reference and paths through uncaptured tables. SQLite can execute a nested update before recording the outer AFTER event, producing an unreplayable before-image order. `plan`, `verify` and `diagnose` identify the cycle. This is a conservative schema check; current row contents and plans to keep keys immutable do not make a cyclic definition eligible. Acyclic cascades and NO ACTION/RESTRICT references remain eligible subject to the other capture requirements and the deletion-path check below. Version 0.3.5 also rejects a path from DELETE on a captured table back to an UPDATE of that table's primary key. It follows DELETE CASCADE as deletion, SET NULL/DEFAULT as update, then subsequent mutating ON UPDATE actions. An inner update can otherwise move a row into the just-deleted key before the outer DELETE is journaled, causing replay to hit a uniqueness conflict. This check includes uncaptured tables and considers the final foreign-key columns. Pure DELETE CASCADE, safe non-key self-reference actions, and capture subsets without the unsafe key return remain supported. `plan`, `verify` and `diagnose` identify unsupported return paths. An existing source with such a cycle or primary-key return path requires a reviewed application/schema design before it can be captured with this version. Preserve the old databases and logs; trigger upgrades do not repair already reordered events. After the source satisfies the capture requirements, initialize a new generation and targets from authoritative data if the old journal cannot replay. Intact compatible 0.3.2–0.3.4 sources continue to use their existing trigger definitions. Every source writer connection requires both `PRAGMA recursive_triggers=ON` and `PRAGMA trusted_schema=ON`. The latter permits the trigger's table-valued PRAGMA guard; with `trusted_schema=OFF`, SQLite rejects the write as an unsafe use of a virtual table before changes commit. The `plan` report lists these connection requirements. Applications whose security policy requires untrusted schema mode cannot use this capture mechanism. This requirement is separate from the 0.3.2 trigger upgrade. Version 0.3.3 keeps the 0.3.2 capture definitions. Stop old synchronization/pruning workers before switching to 0.3.3 so every pruner seals the corrected high-water mark. An intact 0.3.2 capture needs no trigger upgrade. After OR FAIL, SQLite can retain a successful prefix even though the statement reported an error; the new watermark includes those committed events. Existing consumers can retry from their durable target positions. If an earlier snapshot was taken with a falsely low watermark, subsequent replay may conflict: audit or reinitialize that target from an authoritative snapshot rather than skipping events to bypass the conflict. | Failure | Action | | --- | --- | | Exit before target commit | Retry sync; SQLite rolls back the target transaction | | Target committed, source ack missing | Retry sync; target cursor repairs source ack | | Old batch replay | Target skips an already committed end cursor | | Target changed externally | Preview/apply rejects before-image conflicts; audit detects other changes; restore or reinitialize | | Missing trigger / changed DDL | Diagnose, stop consumers, initialize a new generation; recreating a trigger cannot recover missing events | | Cursor below retained floor | New snapshot and new consumer; guessing a cursor does not reconstruct data | | Snapshot output failure | Check completeness; explicitly drop new consumer and retry if necessary | | Bootstrap failure | Inspect consumer and target; source registration and target initialization are separate transactions; use a new destination | Pause still pins retention. `retention SOURCE` shows safe pruning and blockers. `prune-bounded SOURCE --limit N` removes at most N acknowledged contiguous events. `prune` removes all safely acknowledged events. Dropping a consumer releases its pin. `fork-consumer SOURCE OLD NEW` copies only a cursor; clone the target separately under coordinated writes. `restore-consumer SOURCE NAME --input CURSOR_FILE` requires an existing materialized state corresponding to that retained checkpoint. Neither operation creates target data. To uninstall: drain consumers, prune events, drop consumers explicitly, then run `uninstall SOURCE SOURCE_ID`. The identity must match; remaining consumers or events prevent uninstall. User tables remain intact. ## Audits and budgets Before-image checks use the same strict raw UTF-8 tagged reads as snapshots and audits. Invalid target TEXT rejects preview/apply/sync before its cursor advances; any preceding events in that target batch roll back. Diagnose and repair or reinitialize a divergent target before retrying. A decoder error is not permission to skip an event. `verify SOURCE --limit N` audits ALL retained events and fails on exceeding N (CLI default 1,000). `diagnose SOURCE` collects schema/trigger problems and runs SQLite quick_check. `audit SOURCE TARGET` requires equal watermarks rather than mislabeling lag as corruption; default row budget is 100,000. Preview takes a write lock, applies events and always rolls back. Actual apply validates again because another writer may change data after preview. Snapshots, audits and batches are bounded in-memory operations, not unlimited streaming backups. ## Raw SQL API transactions `Database.query(sql, values, max_rows=...)` scopes INSERT, UPDATE, DELETE, REPLACE and WITH statements in a transaction. It opens its own transaction when none is active, otherwise uses a private nested savepoint. Result statements are finalized before the scope commits or rolls back. A row-limit or ordinary statement error undoes that query's writes, including RETURNING effects, while preserving earlier writes in a caller-owned transaction. A successful inner query still belongs to the caller's transaction and can be rolled back by it. If an owned COMMIT fails with a lock conflict, the owned transaction is rolled back before the error returns. The first-token check skips SQL whitespace, BOM, comments and leading semicolons. WITH SELECT queries are conservatively scoped too. SELECT, EXPLAIN, PRAGMA and explicit transaction commands retain their native execution behavior. This is a DML result-query guarantee; PRAGMA setting side effects are not covered. SQLite's explicit OR ROLLBACK conflict policy may end the entire caller transaction. `Database.execute` remains a direct SQLite statement operation: OR FAIL can retain a successful prefix, as described by SQLite. Parameter binding also follows SQLite: unbound slots are NULL. Provide every intended parameter explicitly. Raw `query` reads use the binding's ordinary value conversion; CDC snapshot, audit and before-image paths use strict raw UTF-8 decoding for valid Unicode TEXT.