Verifiable database snapshots for dbsync

## The problem

Operators standing up a fresh dbsync instance don't sync from genesis if they can avoid it — they restore a prebuilt database dump. That's the established pattern: exchanges and API providers don't upgrade in place, they bring up a new instance and switch over.

Those dumps cannot currently be verified in any meaningful sense.

[yaci-store#1047](https://github.com/bloxbean/yaci-store/issues/1047) demonstrates the consequence against upstream cardano-db-sync. The reporter took a real mainnet snapshot (`db-sync-snapshot-schema-13.7-block-13716610-x86_64.tgz`), modified a single ordinary unspent UTxO — `tx_id=122090067, index=0`, value 1,000,000,000 → 50,000,000,000 (1,000 → 50,000 ADA) — and recomputed the checksum. The result:

- Archive integrity check: passed
- Checksum: passed (recomputed)
- Schema migrations: passed
- Genesis supply check: passed (the tampered output isn't in the genesis distribution)
- Position reconciliation: passed
- Synced ~18,000 blocks to the live tip with zero errors, landing on the same block and hash as two genuinely-synced instances

Permanently wrong data, no detection. An earlier attempt that tampered a *genesis-distribution* output was caught, but only incidentally, by the total-supply assertion.

Upstream's `create_snapshot` does produce a `.sha256sum` sibling. `restore_snapshot` never reads it.

## Why a signature doesn't fix this

The tampering happened *before* the checksum was recomputed, so a signature over the artifact would have been perfectly valid. Signing answers "did the publisher produce these bytes", not "are these rows correct".

| Property | Established by | Addresses #1047 |
|---|---|---|
| Bytes arrived intact | Content-addressed digest | No |
| We published these bytes | Signature | No |
| **The rows are correct** | **Re-derivation from the chain** | **Yes** |

We also can't borrow Mithril's approach. Mithril works because many independent signers recompute an identical hash over the same input. A PostgreSQL dump isn't reproducible — surrogate keys depend on insert order, `pg_dump` output isn't byte-stable — so there's no common message to multi-sign. Mithril hits this same wall on the node's own ledger state and falls back to single-key signing for it ([mithril#2525](https://github.com/IntersectMBO/mithril/issues/2525)).

## The proposal

Rest on a different observation:

> Anyone running dbsync already runs a cardano-node, and Mithril already certifies that node's copy of the chain. Nearly everything dbsync stores is derived from transaction bodies, so it can be re-derived from the operator's own trusted blocks and compared. **The snapshot never has to be trusted — only checked.**

That gives three verification layers, each usable on its own:

| Layer | What it checks | What it catches | What it needs |
|---|---|---|---|
| **1 — Provenance** | Ed25519 signature over a manifest of per-file hashes | Corruption in transit, a substituted archive, a mirror serving something else | The published verification key. Seconds |
| **2 — Consistency** | Recomputes stored aggregates from the rows they summarise, in SQL | Any tamper that changes a value without also correcting every total derived from it — including the reported one | Nothing but the restored database. No node, no network |
| **3 — Re-derivation** | Re-reads blocks from the operator's own Mithril-verified node and compares row by row | Anything internally consistent but still not what the chain says | A synced node and one chain pass |

The layers are not simply ordered. Layer 1 answers *who published this*; layers 2 and 3 answer *is it correct*, with 3 subsuming 2. The reported attack passes layer 1 by construction, which is exactly why layer 1 is not the load-bearing part — and why proposing signatures alone would miss the point.

Layer 2 is the pragmatic default: it costs a few queries, needs no node, and catches the class of tamper that has actually been demonstrated. Layer 3 is the complete answer for anyone whose data has to be right.

---

## Layer 1 — What exactly gets signed

Not the tarball. A **manifest**, which commits to the content by hash:

```json
{
"formatVersion": 1,
"network": { "magic": 764824073, "name": "mainnet" },
"chainPoint": { "slot": 141_552_000, "blockNo": 11_802_431,
"blockHash": "a1b2…" },
"schemaVersion": 1,
"schemaFingerprint": "sha256:…",
"extractors": ["core", "utxo", "multi_asset", "..."],
"files": {
"pgdump/…": "sha256:…",
"ledger/…": "sha256:…"
}
}
```

Signed with Ed25519 over the manifest's canonical hash, key held in a KMS, verification key published per network.

This mirrors what Mithril already does for its ancillary archive (the half containing the node's ledger state, which likewise can't be multi-signed): `ancillary_manifest.json` is a `SignableManifest { data: BTreeMap, signature }`, signed via GCP KMS, with the vkey published at `mithril-infra/configuration/release-mainnet/ancillary.vkey`.

**Verification order matters and must fail closed.** Mithril's client recomputes every file hash, *then* checks the signature, and only then moves files into their final location. Same here — no partial restore. A verifier that leaves a half-restored database on failure is worse than none.

What this establishes: the bytes are the ones we published, and they describe the chain point / schema / data set the manifest claims. What it does **not** establish: that the rows are right. Hence layers 2 and 3.

---

## Layer 2 — What the internal checks actually do

Pure SQL over the restored database. No node, no network. These recompute aggregates from base rows and compare against the stored derived values — a tamper has to be consistent across *every* one of them to survive.

| Check | Compares | Catches |
|---|---|---|
| `txOutSumDrift` | `tx.out_sum` vs `SUM(tx_out.value)` per tx | **The #1047 tamper directly** |
| `adaPotsDrift` (new) | Ledger-sourced pot totals vs `SUM(tx_out.value)` unspent | A tamper that also fixed `tx.out_sum` |
| `consumedByDrift` | `tx_in` ↔ `tx_out.consumed_by_tx_id`, both directions | Fabricated or deleted spends |
| `blockTxCountDrift` | `block.tx_count` vs `COUNT(tx)` | Inserted/removed transactions |
| `epochContiguityGap` | Holes in `block.epoch_no` | Missing block ranges |
| `epochFinalizedDrift` | `epoch_finalized` vs fresh `block`+`tx` aggregate | Rewritten epoch summaries |
| `duplicateEpochRowGroup` | Duplicate natural keys across 7 epoch tables | Double-applied epoch boundaries |

The layering is what makes this useful. Editing one `tx_out.value` trips `txOutSumDrift`. Also fixing `tx.out_sum` to match then trips `adaPotsDrift`, because the pot totals come from **ledger events, not from SQL** — they're an independent measurement of the same quantity, so the attacker would have to forge ledger-derived state to agree with forged block-derived state.

Most of this already exists: `tests/lib/DbSync/Test/RecomputeInvariants.hs` implements all but `adaPotsDrift` and runs in CI today. It has simply never been available in production. This layer is largely a matter of moving existing SQL into the main library and exposing a flag.

**What layer 2 cannot catch:** anything that doesn't disturb an aggregate. Repointing `tx_out.address_id` to a different address moves the coin to another party while every sum still balances. That needs layer 3.

---

## Layer 3 — Re-derivation against the operator's own node

Stream blocks from the operator's cardano-node, re-run the extraction logic, compare against what's in the database.

Two depths:

- `headers` — compare every block hash and transaction hash. Proves the skeleton: no inserted, removed or reordered blocks or transactions. Cheap.
- `full` — re-derive every row and compare field by field. Catches the address case and anything else layer 2 misses.

This is the layer that actually addresses #1047 completely, and it works precisely because the operator's blocks are Mithril-certified. We aren't asking them to trust us; we're asking them to check us against a source they already trust.

Not verifiable this way: the ledger-derived tables (`reward`, `epoch_stake`, `ada_pots`, `epoch_param`, `pool_stat`, `drep_distr`). They come from ledger rules at epoch boundaries, not from transaction bodies, so re-deriving them means a full ledger replay. Layer 2's invariants cover them for now; Mithril's own ledger-state certification work ([#2525](https://github.com/IntersectMBO/mithril/issues/2525), [#2720](https://github.com/IntersectMBO/mithril/issues/2720)) would cover them properly if it lands.

Worth noting every table a wallet or explorer actually reads is block-derived, so the fully-verifiable set covers the cases where wrong data does real damage.

---

## What the snapshot contains

### Two halves, both required

| Half | Contents | Approx. size |
|---|---|---|
| PostgreSQL dump | The database | ~262 GB live (dump is smaller — no indexes, compressed) |
| Ledger state directory | `snapshot-headers//` + `lsm/snapshots//` | ~11 GB |

The ledger half is not optional and not a duplicate. dbsync resumes from a ledger snapshot; without one it replays from genesis regardless of what the database contains. The two directories are separate because the header (ExtLedgerState minus UTxO) and the UTxO tables are written by different mechanisms upstream and can't be merged.

They must be captured at a **consistent point** — the ledger snapshot slot must correspond to the database's `last_committed_slot`, or boot takes the replay-window path and re-applies the gap.

One further constraint: the dump is only valid **after** the `PreparingForVolatileTail` phase. 70 of 73 tables are `UNLOGGED` during bulk ingest, and Prep is what runs `ALTER TABLE … SET LOGGED`, creates indexes and adds foreign keys.

### Which projections it carries

dbsync splits its schema into optional **projections** (internally, "extractors"): `utxo`, `multi_asset`, `metadata`, `scripts_datums`, `governance`, `cbor`, the ledger-derived group, and so on. Operators enable only what they query — the schema is created accordingly, and a projection that's off has no tables at all.

Today that choice is **immutable**: it's fixed when the database is created, and changing it means a fresh sync from genesis.

### Proposal: ship everything except `cbor`

The reasoning is an asymmetry between the two directions an operator can move from a published artifact:

| | Add a projection | Remove a projection |
|---|---|---|
| Work | Re-derive rows from blocks | Discard rows |
| Mechanism | Full chain re-pass | DDL |
| Possible from a snapshot? | **Not for ledger-derived data** | Always |

You can always subtract; you cannot always reconstruct. So the artifact should be the superset, and one artifact then serves every profile.

`cbor` is the one worth excluding: `tx_cbor` is ~218 GB of a ~480 GB `everything` database — **46% of the total for one table** — and it is simultaneously the cheapest thing to rebuild (one leaf table, PG-managed identity id, one FK to `tx`, no dedup structures, nothing referencing it). Excluding it takes the artifact from ~480 GB to ~262 GB.

Two groups look omittable and are not:

**Ledger-derived data cannot be rebuilt from a snapshot at all.** `epoch_stake` for epoch 42 is the stake distribution *as it stood at epoch 42's boundary*. A snapshot carries the ledger state at slot `T` only, so that history simply isn't in it — the only route is a genesis ledger replay. At ~11 GB this is the highest value-per-byte in the artifact, and the verdict doesn't depend on the figure: unbackfillable means ship it at any size.

**Off-chain metadata is unrecoverable, not self-healing.** The workers do rediscover their fetch queue from `pool_metadata_ref`, so the *mechanism* heals. The *data* doesn't: a pool whose metadata URL died years ago can never be re-fetched, and the worker records a fetch error where the snapshot held the real record. Small tables, only surviving copy.

### Reconciling config against the database

The database records its own projection set in `dbsync_sync_state.extractors`, so a dump carries its own contents description — no side-channel manifest. Boot already compares that against the config's enabled set. Today either direction of mismatch aborts. The proposal gives each direction a recovery:

```
config has more than the dump → backfill (chain re-pass)
dump has more than the config → trim (DDL only)
```

Both stay behind explicit flags, and both flags take **no arguments** — the config is the sole declaration of intent. A flag carrying names could diverge from the config and act on projections the config doesn't claim, manufacturing exactly the mismatch it was meant to resolve.

Trim is the common path and is cheap: null the shared reference columns pointing into the dropped set, `DROP TABLE`, update the projection list, reset the dropped tables' id counters. No chain pass. Verified that every cross-projection reference column is nullable — the only `NOT NULL` one is `committee_member.committee_id → committee`, and both belong to `governance`, so they drop together.

---

## Hosting

Same as cardano-db-sync does today: plain HTTPS on the existing bucket. No registry, no new client tooling — `curl` and nothing else.

Worth separating two artifacts that get conflated:

| Artifact | What | Size |
|---|---|---|
| Container image | The dbsync binary and entrypoint | Normal image size |
| Snapshot data | PG dump + ledger state | ~262 GB |

The container image is an ordinary image pulled by Compose. The snapshot data is fetched over HTTPS by the entrypoint. They are versioned and distributed independently.

Layout, mirroring the existing `db-sync-snapshot-…tgz` convention:

```
//dbsync-snapshot---db.tgz PG dump
//dbsync-snapshot---ledger.tgz ledger state
//dbsync-snapshot--.manifest.json per-file sha256, chain
point, schema fingerprint,
projection list
//dbsync-snapshot--.manifest.sig detached signature
```

Two archives rather than one so the ~11 GB ledger half can be re-fetched without the ~262 GB dump, with a single manifest covering both so they cannot be mixed across slots.

**The gap today is not the transport, it is that nothing verifies.** Upstream's `create_snapshot` writes a `.sha256sum` sibling and `restore_snapshot` never reads it. So this proposal keeps the hosting and adds the part that was missing.

Everything a registry would have provided is either already available or already something we have to build:

| Concern | How it is covered |
|---|---|
| Integrity | The signed manifest carries per-file sha256 — needed regardless of transport |
| Resumable transfer | HTTP range requests (`curl -C -`), already supported |
| Signature distribution | `manifest.json` + `.sig` alongside the archives, as Mithril does for its ancillary archive |
| Mirroring | The bucket is already CDN-fronted |

Signing does not need a registry either: `cosign sign-blob` / `cosign verify-blob` work on plain files and keep the keyless option open, or Ed25519 + KMS mirroring Mithril's ancillary signer. The signature is over the manifest, so the transport is irrelevant to it.

Deferred: layer-granular delta pulls, which would need an OCI-style chunked layout. Only worth revisiting if publish cadence ever makes re-downloading dominant — operators restore once and sync forward, so today it buys nothing.

---

## Open questions

1. Is one artifact right, or is a second smaller ledger-less variant worth the extra build and storage cost, given small-profile operators gain least from a superset download?
2. Default layer-3 depth. `headers` is cheap enough to be always-on; `full` carries the real guarantee but costs a chain pass. Suggest defaulting to `headers` and documenting `full` as recommended before serving data that must be right.
3. Does `collateral_tx_out` belong in the layer-2 supply cross-check? Phase-2 failure collateral enters the ledger UTxO set in Babbage+, and getting it wrong means false positives on every snapshot.
4. Publish cadence, and whether chunked layers for delta pulls are ever worth it — only matters once re-downloading dominates.

## Prior art

- **Mithril ancillary manifest** — the closest working precedent for signing a non-reproducible artifact, and the model for layer 1.
- **Stelae** ([dolos#1056](https://github.com/txpipe/dolos/pull/1056), [#1146](https://github.com/txpipe/dolos/pull/1146)) — TxPipe hit this exact problem and designed a generic, profile-parameterised snapshot protocol: canonical logical records rather than database files, OCI transport, a canonical signable inscription, multi-party attestation. Proposed and unimplemented, and the protocol crate is deliberately free of Dolos dependencies so third parties can add a profile. If this direction matures, adopting a profile beats inventing a third ecosystem snapshot format.
- **yaci-store#1047** — same gap, different indexer, still open. This is ecosystem-wide rather than specific to us.