Data & storage

The Store protocol, Spanner, Postgres, ClickHouse, and multi-cloud.

Research note for the 0-to-CTO onboarding pack. Paths are relative to quill-router/ and cited as path:line. Snapshot taken 2026-10-19 at main 778120494. Several source docs are older than the code, and the places where they disagree are flagged [STALE].

1. Overview: the data layer in brief

• One abstraction, Store. Store is a runtime_checkable Protocol in src/trusted_router/store_protocol.py:76. AGENTS.md says "~97 methods" (AGENTS.md:91). [STALE] The Protocol body (lines 76–1146) now declares 210 methods. There are also four narrower capability Protocols: TypedBillingStore (store_protocol.py:1147), SnapshotReaperStore (:1241), ShadowTransaction (:1254) and ShadowStore (:1259).
• Three production backend values are accepted by create_store (src/trusted_router/storage.py:3974-4085) and by config validation (src/trusted_router/config.py:1576):
• memory: InMemoryStore (storage.py:150), used by tests and local dev.
• postgres: PostgresStore (src/trusted_router/storage_postgres.py:542), used by AWS (Aurora DSQL) and Azure (Flexible Server).
• spanner-clickhouse: SpannerStore (src/trusted_router/storage_gcp.py:389). This is GCP production. Spanner is the system of record and ClickHouse serves analytics reads.
• A module-global proxy. STORE is a _StoreProxy (storage.py:3880) that forwards calls through __getattr__ (storage.py:3936). The app calls configure_store() to swap its target (storage.py:3950). It is convenient and it is a trap for tests (see §5).
• The executable contract is tests/conformance/. Every test in it talks only to the Store Protocol. A backend is conformant when it passes the suite unchanged (tests/conformance/conftest.py:1-17).
• Spanner holds a generic table and typed tables. The legacy generic table is tr_entities(kind, id, body JSON-string, updated_at) with PK (kind, id) (scripts/deploy/infra.sh:33). Money and request state has moved into typed tables such as tr_credit_balance, tr_gateway_authorization, tr_reservation and tr_settle_outbox. Their full DDL is generated in tests/conformance/spanner_ddl.py.
• Analytics is ClickHouse only. Bigtable analytics were retired on 2026-09-28 and the code was deleted on 2026-09-29 (docs/clickhouse-reliability.md:133-142; docs/storage-portability/README.md:6-11). Rows reach ClickHouse through durable outboxes in the cloud's OLTP database. Each outbox has a drain daemon on the ClickHouse publisher node.
• Each cloud is a separate TrustedRouter. Identity federates across clouds. Credits, keys, workspaces and analytics stay in their own cloud (docs/storage-portability/multi-cloud-separation.md:9-28).

2. Data stores and what lives where

flowchart LR
  subgraph APP["Control plane (FastAPI)"]
    PROXY["STORE proxy<br/>storage.py:3880"]
  end

  subgraph GCP_DB["GCP: Cloud Spanner trusted-router-nam6 / trusted-router"]
    ENT["tr_entities<br/>PK kind,id - body JSON<br/>users, workspaces, api_key,<br/>credit, stripe_event, member,<br/>heartbeat rows ..."]
    TYPED["Typed money tables<br/>tr_credit_balance, tr_key_limit,<br/>tr_gateway_authorization,<br/>tr_reservation, tr_credit_movement,<br/>tr_earnings_balance"]
    SOUT["tr_settle_outbox<br/>durable settle"]
    AOUT["tr_analytics_outbox<br/>16 shards, 7-day TTL<br/>benchmark samples"]
    OOUT["tr_operational_analytics_outbox<br/>32 shards, 30-day TTL<br/>activity, synthetic, client_events"]
    TRUST["tr_trust_* tables<br/>trust events, inbox, overrides"]
    GEN["tr_generation<br/>metadata-only lookup"]
  end

  subgraph PG["AWS / Azure: Postgres-wire DB"]
    PENT["tr_entities<br/>body JSONB + index cols"]
    PTYPED["tr_credit_balance, tr_key_limit,<br/>tr_trust_*, tr_credit_movement ..."]
    POUT["tr_operational_analytics_outbox"]
    PHOME["tr_home_settlement_outbox,<br/>tr_deferred_outstanding"]
  end

  subgraph CH["ClickHouse (per cloud)"]
    PBS["provider_benchmark_samples"]
    ACT["activity_generations"]
    SYN["synthetic_probe_samples,<br/>synthetic_status_rollups"]
    ROLL["provider_analytics_hourly,<br/>daily, monthly"]
    CLI["client_request_events, ..."]
  end

  ARCH["GCS Parquet archive<br/>7-year retention"]

  PROXY -->|"spanner-clickhouse"| GCP_DB
  PROXY -->|"postgres"| PG
  PROXY -->|"analytics reads"| CH
  CH --> ARCH

flowchart LR
  subgraph APP["Control plane (FastAPI)"]
    PROXY["STORE proxy<br/>storage.py:3880"]
  end

  subgraph GCP_DB["GCP: Cloud Spanner trusted-router-nam6 / trusted-router"]
    ENT["tr_entities<br/>PK kind,id - body JSON<br/>users, workspaces, api_key,<br/>credit, stripe_event, member,<br/>heartbeat rows ..."]
    TYPED["Typed money tables<br/>tr_credit_balance, tr_key_limit,<br/>tr_gateway_authorization,<br/>tr_reservation, tr_credit_movement,<br/>tr_earnings_balance"]
    SOUT["tr_settle_outbox<br/>durable settle"]
    AOUT["tr_analytics_outbox<br/>16 shards, 7-day TTL<br/>benchmark samples"]
    OOUT["tr_operational_analytics_outbox<br/>32 shards, 30-day TTL<br/>activity, synthetic, client_events"]
    TRUST["tr_trust_* tables<br/>trust events, inbox, overrides"]
    GEN["tr_generation<br/>metadata-only lookup"]
  end

  subgraph PG["AWS / Azure: Postgres-wire DB"]
    PENT["tr_entities<br/>body JSONB + index cols"]
    PTYPED["tr_credit_balance, tr_key_limit,<br/>tr_trust_*, tr_credit_movement ..."]
    POUT["tr_operational_analytics_outbox"]
    PHOME["tr_home_settlement_outbox,<br/>tr_deferred_outstanding"]
  end

  subgraph CH["ClickHouse (per cloud)"]
    PBS["provider_benchmark_samples"]
    ACT["activity_generations"]
    SYN["synthetic_probe_samples,<br/>synthetic_status_rollups"]
    ROLL["provider_analytics_hourly,<br/>daily, monthly"]
    CLI["client_request_events, ..."]
  end

  ARCH["GCS Parquet archive<br/>7-year retention"]

  PROXY -->|"spanner-clickhouse"| GCP_DB
  PROXY -->|"postgres"| PG
  PROXY -->|"analytics reads"| CH
  CH --> ARCH

Spanner instance facts:

• Config nam6, Enterprise Plus, managed autoscaler from 1,000 to 10,000 PU with a 45% high-priority CPU target (infra/spanner_trusted_router.tf:10-39).
• prevent_destroy is set (infra/spanner_trusted_router.tf:36-38).
• PITR is 7 days, with daily full backups plus incrementals every 4 hours, and a 30-day copy in the trustedrouter-dr project (docs/spanner-reliability.md:10-23).
• [STALE] That runbook line still says "400 until 2026-09-25 and 600 after". The Terraform has since moved to the autoscaler.
• The Terraform deliberately leaves databases, schema and backups to the deploy scripts (infra/spanner_trusted_router.tf:1-4).

3. tr_entities and why JSON-body scans are dangerous

Model. Each row is kind STRING(64), id STRING(512), body STRING(MAX) (JSON) and updated_at (commit timestamp), with PRIMARY KEY (kind, id) (scripts/deploy/infra.sh:33). Reads that touch the generic table:

• Point reads: SELECT body FROM tr_entities WHERE kind=@kind AND id=@id (src/trusted_router/storage_gcp.py:7316-7342).
• Range reads: a list uses kind=@kind AND STARTS_WITH(id, @prefix) (storage_gcp.py:7344-7375). This bounds the scan to one kind's key range.
• Secondary indexes are emulated as extra rows whose id is <owner>#<rest>.
• On Postgres, _ in owner ids is a LIKE wildcard. A prefix must therefore be escaped with _like_prefix (storage_postgres.py:1107), or one tenant's scan can return another tenant's rows (docs/storage-portability/HANDOFF.md:626-628).
• Body: nothing in body is indexed on Spanner.

Bloat. In July, issue #334 counted these numbers (HANDOFF.md:232-246):

• 14.8M rows backing 185 workspaces and 251 keys.
• About 5.45M dead reservation, reservation_idemp and gateway_authorization_idempotency rows.
• About 9.4M rows still growing with no TTL.

Status of the remediation:

• New terminal generations are no longer written to tr_entities (docs/spanner-reliability.md:331-333).
• rate_limit rows now have a TTL.
• The historic rows remain until a separate, reviewed cleanup.

The September 6 CPU incident (docs/incidents/2026-09-06-spanner-operator-search-cpu.md):

• Three "harmless" operator searches for a Stripe PaymentIntent ran against tr_entities. Each used a leading-wildcard body LIKE or id LIKE with no PK predicate, and each had LIMIT 5.
• Each query consumed about 30 CPU-seconds and ran for about 35 s (lines 7-12). That pushed high-priority CPU to 61% against the 45% alarm (line 16).
• Root cause: LIMIT caps the rows returned, not the rows scanned. Searching for an absent substring scans the whole table (lines 29-32).
• Remediation: scripts/inspect_payment_owner.py does one exact Stripe GET, then at most two complete-PK Spanner reads. They run at LOW priority with the tr_ops_payment_owner tag, 5 s deadlines and no retries (lines 45-56).

A related incident on 2026-09-24 had the same lesson: a periodic job read tr_reservation without a covering index (docs/incidents/2026-09-24-spanner-overrun-rollup-scan.md; docs/spanner-reliability.md:177-181).

4. Store backends

Notes:

• TypedBillingStore capability. Always check it with typed_billing_store() (storage.py:3954-3968), not isinstance(STORE, ...). isinstance does not see methods that are only reachable through the proxy's __getattr__. That mistake silently routed typed billing down the legacy path once (codex #97).
• Known divergences are registered as strict xfail. They are not skips, so they stay visible (tests/conformance/conftest.py:236-313). Examples:
• _C1_LEGACY_MONEY: native Spanner does not route the Protocol's JSON reserve methods.
• _NATIVE_ROLLUPS_FROM_CLICKHOUSE_WORKER.
• A method that exists is not a method that works. PostgresStore._not_implemented() satisfies mypy and structural checks, then raises at runtime. tests/test_store_protocol_conformance.py:222-280 statically fails if an enclave-facing route calls one of those methods. This rule was added after 11 gateway methods raised on Postgres (router#452, HANDOFF.md:381-389).
• Analytics sink abstraction. _StoreProxy.record_provider_benchmark writes to the store first, then calls _ANALYTICS_SINK (storage.py:3912-3934). The in-process ClickHouse sink from PR #291 (src/trusted_router/analytics_sink.py) is disabled by design: Cloud Run gives background threads no CPU between requests, and wait_for_async_insert=0 loses rows (docs/storage-portability/analytics-ingestion.md:72-114). The default is NullAnalyticsSink (storage.py:3877).

5. The STORE proxy trap (issue #333)

• Mechanism. STORE forwards through __getattr__. Calling monkeypatch.setattr(STORE, "x", fake) installs an instance attribute on the proxy. On teardown, monkeypatch "restores" it as a method bound to a store that no longer exists, and every later test hits the dead store. This once broke 136 tests (AGENTS.md:42-45; HANDOFF.md:733-735).
• Second failure mode. A proxy-level patch only intercepts external callers. A self-call inside the store (for example ensure_user calling self.create_workspace) binds self to the real backend and bypasses the patch (tests/conftest.py:179-185).
• Rule: patch the backend class, as in monkeypatch.setattr(InMemoryStore, "create_workspace", ...). Two places show the pattern:
• the autouse fixture tests/conftest.py:168-195
• the comment at tests/test_settle_outbox_drain.py:102
• Reset between tests. The autouse reset_store fixture puts back a fresh InMemoryStore if a test swapped the target, then calls reset() (tests/conftest.py:105-109).
• Run the full suite. Pollution only shows up across files.

6. Running conformance locally

The memory backend alone needs nothing:

uv run pytest tests/conformance -q -k "backend=memory"

Postgres and Spanner-PG both run against containers and need no cloud credentials (AGENTS.md:95-105; tests/conformance/conftest.py:196-200). The commands below have four steps:

1. Start the Postgres 17 container.
2. Start the Spanner PG dialect container (PGAdapter plus the emulator).
3. Run the suite. --dist loadgroup is required with -n: the Spanner PG emulator rejects concurrent DDL, and plain -n 4 produced 14 setup errors (conftest.py:225-242; .github/workflows/ci.yml:207-213).
4. Stop both containers.

# 1. Postgres 17
docker run -d --rm --name tr-conformance-pg -e POSTGRES_PASSWORD=tr -p 5433:5432 postgres:17
export TR_CONFORMANCE_POSTGRES_DSN=postgresql://postgres:tr@localhost:5433/postgres

# 2. Spanner PG dialect (PGAdapter + emulator)
docker run -d --rm --name tr-spanner-pg -p 5434:5432 \
  gcr.io/cloud-spanner-pg-adapter/pgadapter-emulator:latest
export TR_CONFORMANCE_SPANNER_PG_DSN=postgresql://localhost:5434/tr-conformance

# 3. Run the suite
uv run pytest tests/conformance -q -n 4 --dist loadgroup

# 4. Clean up
docker stop tr-conformance-pg tr-spanner-pg

The native GoogleSQL emulator backend (spanner-emulator) mirrors the CI job at .github/workflows/ci.yml:34-46. It is opt-in and requires a loopback host (tests/conformance/spanner_emulator.py:158-172). The run below exports the two variables CI sets: SPANNER_EMULATOR_HOST and TR_CONFORMANCE_EMULATOR_SCHEMA=1.

docker run -d --rm --name tr-spanner-emu -p 127.0.0.1:9010:9010 -p 127.0.0.1:9020:9020 \
  gcr.io/cloud-spanner-emulator/emulator@sha256:c6f3402f2599684f295a0fdefb6fbbbfb18a0e43e309ff5456ccb452a4570a79
export SPANNER_EMULATOR_HOST=127.0.0.1:9010 TR_CONFORMANCE_EMULATOR_SCHEMA=1
uv run pytest tests/conformance -q -k "backend=spanner-emulator"

Traps:

• A skipped backend proves nothing. Without the DSN env vars, the postgres and spanner-pg backends pytest.skip() and the run still looks green (HANDOFF.md:612-619).
• Do not weaken a conformance test to make a backend pass (AGENTS.md:93).
• CI gates. CI runs all three gates: uv run ruff check . && uv run mypy && uv run pytest -q (AGENTS.md:29-31). Never run ruff format ..
• Not a local-only command. scripts.audit_spanner_schema (docs/storage-portability/spanner-emulator-conformance.md:130-141) reads production schema metadata. It is an operator tool, not a local test.

7. Migrations and schema

• Location. Spanner DDL lives in idempotent shell scripts under scripts/deploy/, not in Terraform:
• infra.sh for tr_entities
• migrate_typed_counters.sh
• migrate_money_primitives.sh
• migrate_request_retention.sh
• migrate_spend_lease.sh
• migrate_receipt_key_versions.sh
• migrate_generation_records.sh
• migrate_analytics_outbox.sh
• migrate_operational_analytics_outbox.sh
• migrate_async_settle_*.sh
• migrate_fastpath.sh
• migrate_entity_ttl.sh
• migrate_gateway_request_index.sh
• migrate_speculation_shadow.sh
• migrate_trust_reconciliation.sh
• Pattern. Each script checks INFORMATION_SCHEMA and only adds what is missing. Most are additive. Several are dry-run unless you pass --apply (for example migrate_request_retention.sh:2-3). The script header states any prerequisite ordering.
• When they run. The migrate-schema job in .github/workflows/deploy.yml:242-335 runs them before deploy (deploy.yml:415, :451). A merge can therefore migrate the schema even if the deploy is later skipped (multi-cloud-separation.md §7, around line 410).
• Schema drift guard. tests/conformance/spanner_ddl.py is generated from those scripts (python -m tests.conformance.spanner_schema_source). It pins a SHA-256 per source script (spanner_ddl.py:7-24). CI provisions this DDL on the emulator. Allowlists live in spanner_schema_drift_allowlist.json and spanner_ddl_exemptions.json.
• Postgres applies storage_postgres_schema.sql from PostgresStore.apply_schema() (storage.py:4029-4030). Aurora DSQL has two restrictions:
• It rejects DESC index keys, so all indexes are ascending (storage_postgres_schema.sql:25-35).
• It needs CREATE INDEX ASYNC.
• Rules:
• Never run large unbatched DML against production Spanner during a rolling deploy (AGENTS.md:68).
• Roll out typed readers before writers. Do not roll back past a revision that can read typed tables once typed rows exist (docs/spanner-reliability.md:350-353).
• Repeat cloud-local migrations in every cloud. A GCP success says nothing about AWS or Azure (multi-cloud-separation.md:30-46).
• ClickHouse DDL is numbered clickhouse/001_…sql through 023_…sql. It comes in replicated (GCP cluster) and *_single_node.sql (AWS/Azure) variants. Apply scripts:
• scripts/deploy/clickhouse_replicated_migrate.sh
• _clickhouse_single_node_schema.sh
• aws_eu_clickhouse_schema_apply.sh

8. Analytics pipeline: request → outbox → ClickHouse

flowchart TD
  REQ["Inference request<br/>gateway authorize, then settle"] --> SETTLE["Settle commit<br/>Spanner typed ledger"]
  SETTLE -->|"one-commit happy path:<br/>INSERT rides in money txn"| AOB["tr_analytics_outbox<br/>PK shard, commit_ts, event_id<br/>16 shards"]
  SETTLE -->|"fallback: separate best-effort txn<br/>logs loss_tolerated"| AOB
  SETTLE --> OOB["tr_operational_analytics_outbox<br/>32 shards<br/>activity / synthetic / client_events<br/>surrogate ids only"]
  PROBE["Synthetic probes"] --> OOB

  subgraph PUB["ClickHouse publisher node (tr-clickhouse-role=publisher)"]
    ING1["ingest_outbox.py<br/>tr-clickhouse-ingest.service"]
    ING2["ingest_operational_outbox.py<br/>tr-clickhouse-operational-ingest.service"]
    ROLLUP["rollup_analytics.py<br/>hourly / daily timers"]
    ARCHIVE["archive_daily.py<br/>+ restore drill"]
  end

  AOB -->|"read by shard, commit_ts floor"| ING1
  OOB --> ING2
  ING1 -->|"insert, wait for ack"| CH["ClickHouse 3 replicas<br/>ReplicatedReplacingMergeTree"]
  ING2 --> CH
  ING1 -->|"delete exact PKs AFTER ack"| AOB
  ING2 -->|"delete AFTER ack"| OOB
  ING2 -->|"heartbeat row every 15s or more"| HB["tr_entities<br/>kind=operational_outbox_heartbeat"]
  HB -->|"point read"| STATUS["/status.json analytics section<br/>drain_lag_seconds"]
  CH --> ROLLUP --> CH
  CH --> ARCHIVE --> GCS["GCS Parquet<br/>immutable revisions"]
  CH -->|"tr-clickhouse-ilb, read-only user"| READERS["Provider portal,<br/>leaderboard, route health,<br/>activity"]

flowchart TD
  REQ["Inference request<br/>gateway authorize, then settle"] --> SETTLE["Settle commit<br/>Spanner typed ledger"]
  SETTLE -->|"one-commit happy path:<br/>INSERT rides in money txn"| AOB["tr_analytics_outbox<br/>PK shard, commit_ts, event_id<br/>16 shards"]
  SETTLE -->|"fallback: separate best-effort txn<br/>logs loss_tolerated"| AOB
  SETTLE --> OOB["tr_operational_analytics_outbox<br/>32 shards<br/>activity / synthetic / client_events<br/>surrogate ids only"]
  PROBE["Synthetic probes"] --> OOB

  subgraph PUB["ClickHouse publisher node (tr-clickhouse-role=publisher)"]
    ING1["ingest_outbox.py<br/>tr-clickhouse-ingest.service"]
    ING2["ingest_operational_outbox.py<br/>tr-clickhouse-operational-ingest.service"]
    ROLLUP["rollup_analytics.py<br/>hourly / daily timers"]
    ARCHIVE["archive_daily.py<br/>+ restore drill"]
  end

  AOB -->|"read by shard, commit_ts floor"| ING1
  OOB --> ING2
  ING1 -->|"insert, wait for ack"| CH["ClickHouse 3 replicas<br/>ReplicatedReplacingMergeTree"]
  ING2 --> CH
  ING1 -->|"delete exact PKs AFTER ack"| AOB
  ING2 -->|"delete AFTER ack"| OOB
  ING2 -->|"heartbeat row every 15s or more"| HB["tr_entities<br/>kind=operational_outbox_heartbeat"]
  HB -->|"point read"| STATUS["/status.json analytics section<br/>drain_lag_seconds"]
  CH --> ROLLUP --> CH
  CH --> ARCHIVE --> GCS["GCS Parquet<br/>immutable revisions"]
  CH -->|"tr-clickhouse-ilb, read-only user"| READERS["Provider portal,<br/>leaderboard, route health,<br/>activity"]

Key properties:

• At-least-once delivery, made safe.
• The ClickHouse ack happens before the exact Spanner PK delete, and the delete is the cursor (clickhouse/ingest_outbox.py:1-21).
• A crash replays the batch. Exact readers use FINAL over ReplacingMergeTree (docs/clickhouse-reliability.md:43-45).
• The cursor is commit_ts, never created_at. An event-time cursor permanently strands late commits (HANDOFF.md:701-703; analytics-ingestion.md:196-213).
• Shard by hash. A blake2b hash of event_id picks the shard, so the monotonic commit_ts does not hot-spot one Spanner split (src/trusted_router/storage_gcp_analytics_outbox.py:12-36). There are 16 benchmark shards and 32 operational shards (src/trusted_router/storage_operational_analytics.py:31).
• Analytics never destabilizes money.
• The benchmark enqueue is best-effort.
• It rides inside the settle commit only on the one-commit path, which has a full two-commit fallback (storage_gcp_analytics_outbox.py:1-10; AGENTS.md:83-87).
• Failures log loss_tolerated / repairable_via (src/trusted_router/storage_gcp_generations.py:152-230).
• Privacy split. Operational rows carry one-way surrogates (analytics_surrogate, storage_operational_analytics.py:67). Raw workspace and key ids never leave Spanner (storage_gcp_operational_analytics_outbox.py:1-12).
• No additive materialized views. MVs run per insert block, so replays inflate them: one measurement saw 3 loads of 30,832 rows produce 92,500 aggregate samples (HANDOFF.md:713-715). Rollups instead recompute into a staging table, check sum(attempts), then replace the partition atomically (docs/clickhouse-reliability.md:94-109).
• Drain freshness is published out of band.
• Every control plane publishes analytics.{available, backend, drain_lag_seconds, outbox_depth, generated_at} in /status.json (docs/clickhouse-reliability.md:279-311).
• On Spanner the plane reads the poller's heartbeat row instead of the outbox. Reading the outbox head walks deleted versions at about 0.9 s CPU per call.
• A missing or stale heartbeat (>180 s) publishes poller_stale (clickhouse-reliability.md:313-326).
• Deploy order: install the VM poller first, then the control plane (clickhouse-reliability.md:123-131).
• Postgres drain (AWS/Azure). clickhouse/ingest_operational_outbox_postgres.py has no timestamp cursor, because now() is transaction start time. Instead it loops SELECT → insert → DELETE. It retries SQLSTATE 40001, which DSQL raises for routine optimistic-concurrency aborts (lines 1-30).
• Direct sink (optional). operational_analytics_direct.py writes operational telemetry straight to ClickHouse. The polling drain once cost about 25% of Spanner CPU and helped collapse authorize/settle on launch day (2026-08-25) (lines 1-27). GCP still pins TR_OPERATIONAL_ANALYTICS_SINK=outbox (scripts/deploy/rollout.sh:602). The direct sink is available but not selected on GCP.
• Topology.
• Three nodes: tr-clickhouse-1/2/3 in us-central1-a/b/c, e2-standard-4, 500 GB SSD each, no external IP, a three-voter Keeper, one logical shard (docs/clickhouse-reliability.md:31-62).
• Workers run only on the node whose metadata says publisher. Takeover is fenced (clickhouse-reliability.md:233-257).
• Never restart all three nodes together (clickhouse-reliability.md:153-154).
• Sharding gate.
• Add a shard when peak rows/s exceeds 25% of measured replicated ingest, when p95 query latency exceeds 250 ms, or when replication lag exceeds 30 s (docs/clickhouse-sharding.md:15-25).
• Distribute tenant activity by the opaque tenant hash and provider data by event id.
• Never shard on a raw workspace id.

9. Multi-cloud separation

flowchart TB
  IDP["Identity issuers<br/>Google / GitHub OAuth<br/>signed assertions"]

  subgraph GCPC["GCP deployment - trustedrouter.com"]
    G_ENC["Enclave: Confidential Space<br/>api.trustedrouter.com"]
    G_CP["Control plane: Cloud Run"]
    G_DB["Spanner nam6<br/>SpannerStore"]
    G_CH["ClickHouse 3-replica cluster<br/>+ GCS Parquet"]
    G_ENC --> G_CP --> G_DB --> G_CH
  end

  subgraph AWSC["AWS deployment - aws.trustedrouter.com"]
    A_ENC["Enclave: Nitro<br/>api-aws.trustedrouter.com"]
    A_CP["Control plane: ECS Fargate<br/>tr-cp-euw1 / tr-cp-euw3"]
    A_DB["Aurora DSQL<br/>PostgresStore"]
    A_CH["ClickHouse Paris<br/>+ Stockholm replica"]
    A_ENC --> A_CP --> A_DB --> A_CH
  end

  subgraph AZC["Azure deployment - azure.trustedrouter.com"]
    Z_ENC["Enclave: SEV-SNP / MAA<br/>api-azure.trustedrouter.com"]
    Z_CP["Control plane: Container Apps"]
    Z_DB["Postgres Flexible Server<br/>PostgresStore"]
    Z_CH["ClickHouse node"]
    Z_ENC --> Z_CP --> Z_DB --> Z_CH
  end

  IDP -. "federates: verified independently" .-> G_CP
  IDP -. "federates" .-> A_CP
  IDP -. "federates" .-> Z_CP

  A_ENC -. "dial-failure-only fallback" .-> G_CP
  Z_ENC -. "dial-failure-only fallback" .-> G_CP

flowchart TB
  IDP["Identity issuers<br/>Google / GitHub OAuth<br/>signed assertions"]

  subgraph GCPC["GCP deployment - trustedrouter.com"]
    G_ENC["Enclave: Confidential Space<br/>api.trustedrouter.com"]
    G_CP["Control plane: Cloud Run"]
    G_DB["Spanner nam6<br/>SpannerStore"]
    G_CH["ClickHouse 3-replica cluster<br/>+ GCS Parquet"]
    G_ENC --> G_CP --> G_DB --> G_CH
  end

  subgraph AWSC["AWS deployment - aws.trustedrouter.com"]
    A_ENC["Enclave: Nitro<br/>api-aws.trustedrouter.com"]
    A_CP["Control plane: ECS Fargate<br/>tr-cp-euw1 / tr-cp-euw3"]
    A_DB["Aurora DSQL<br/>PostgresStore"]
    A_CH["ClickHouse Paris<br/>+ Stockholm replica"]
    A_ENC --> A_CP --> A_DB --> A_CH
  end

  subgraph AZC["Azure deployment - azure.trustedrouter.com"]
    Z_ENC["Enclave: SEV-SNP / MAA<br/>api-azure.trustedrouter.com"]
    Z_CP["Control plane: Container Apps"]
    Z_DB["Postgres Flexible Server<br/>PostgresStore"]
    Z_CH["ClickHouse node"]
    Z_ENC --> Z_CP --> Z_DB --> Z_CH
  end

  IDP -. "federates: verified independently" .-> G_CP
  IDP -. "federates" .-> A_CP
  IDP -. "federates" .-> Z_CP

  A_ENC -. "dial-failure-only fallback" .-> G_CP
  Z_ENC -. "dial-failure-only fallback" .-> G_CP

Credits, API keys, workspaces, usage and analytics do not cross the dotted lines. Only identity federates.

• Why identity federates and money does not.
• An identity is a signed assertion. Every cloud can verify it on its own, even when partitioned from the others.
• A balance has a conservation law. Sharing it would need a single authority (the cross-cloud hop lands on the hot path) or 2PC (availability becomes the AND of both clouds). Partitioning the money is the only option that keeps the clouds independent (multi-cloud-separation.md:50-81).
• Moving credit between clouds would be a new feature: an explicit, asynchronous, idempotent transfer shaped like the settle outbox (multi-cloud-separation.md:83-92).
• Product consequence. The UI must show balances per cloud. A 402 must name the cloud it checked. Keys must be visibly scoped to one cloud (multi-cloud-separation.md:96-107).
• Dial-failure-only fallback. The enclaves try their own plane first and fall back to trustedrouter.com only on a dial failure. Each plane has its own database and the idempotency key does not travel, so retrying a request that reached a server could escrow or bill twice (HANDOFF.md:266-287; enclave-go/internal/trustedrouter/endpoints.go).
• Keyless WIF. The GCP pool multicloud with provider aws-workloads exists for operations only. Do not build data flow on it (HANDOFF.md:209-218).
• Definition of done for a cloud. A cloud is done when scripts/deploy/verify_cloud_complete.sh <cloud> exits 0.
• It checks five stages (registry, analytics section present, available, lag bound, outbox enabled), then requires two in-cloud count() readings ten minutes apart that show growth (multi-cloud-separation.md:204-273).
• Exit codes: 0 = VERIFIED, 5 = NOT YET OBSERVABLE, 1 = NOT VERIFIED (multi-cloud-separation.md §"Exit codes").
• The rule comes from AWS-EU. It ran for 15 days with no drain installed and accumulated about 470k undelivered outbox rows with no alarm (multi-cloud-separation.md:210-227).

10. Query-safety rules for operators

1. Read-only is not load-safe. - Never search production tr_entities.body with LIKE, regex or JSON predicates unless you have a complete indexed key. - LIMIT caps the rows returned, not the rows scanned (AGENTS.md:70-79).
2. Never discover entity kinds with wildcard COUNT(*) queries. Use code (the _read/_write_entity call sites) or bounded SPANNER_SYS table-size stats (docs/spanner-reliability.md:303-316).
3. Payment attribution. Use uv run python -m scripts.inspect_payment_owner ... (docs/spanner-reliability.md:188-208). Missing metadata is a stop condition, not permission to full-scan.
4. Request, token, provider and latency analytics come from ClickHouse. Never aggregate raw Spanner generation, entity or outbox rows (docs/spanner-reliability.md:183-186).
5. CPU investigations use bounded SPANNER_SYS.TXN_STATS_TOP_MINUTE and LOCK_STATS_TOP_MINUTE (docs/spanner-reliability.md:221-251).
6. Never loosen the 45% CPU alarm to make an operator scan pass (AGENTS.md:79).
7. Periodic jobs must read through a covering index (docs/spanner-reliability.md:177-181).
8. No large unbatched DML during a rolling deploy. Use batched or partitioned DML only after confirming there are no readers (AGENTS.md:68; HANDOFF.md:244-246).
9. ClickHouse access. - Connect through an SSH local forward over IAP. A plain IAP TCP tunnel is correctly refused by the 10.0.0.0/8 user ACL (HANDOFF.md:724-731). - When comparing replicas, use FINAL and a fixed cutoff at least 5 minutes in the past (docs/clickhouse-reliability.md:197-208).
10. SQL NULL is three-valued. Use ifNull() when porting Python predicates (HANDOFF.md:708-711).
11. Never write id LIKE 'prefix%' with unescaped _. Use _like_prefix (HANDOFF.md:626-628).

11. HANDOFF status summary (docs/storage-portability/HANDOFF.md)

The HANDOFF was last updated 2026-08-05, with later inline notes up to 2026-09-14. Where newer docs and code supersede it, this summary is reconciled against them.

Done

[STALE] in HANDOFF. Items §4.2 (route-health cutover), §4.3 (leaderboard proof) and §4.4 (reconciler repair) describe Bigtable-era work. The prove_*.py and reconcile scripts were deleted once ClickHouse became the only store (docs/storage-portability/README.md:6-11). Treat those items as closed or moot.

Next / open

• Issue #334: tr_entities cleanup. Dead and duplicate historic rows still need a reviewed, batched removal (HANDOFF.md:230-246; docs/spanner-reliability.md:331-333).
• ACME has no fallback (#56). A Let's Encrypt outage takes down TLS for GCP and Azure (HANDOFF.md:398-405).
• Azure hostdata is published nowhere. Third parties cannot verify Azure attestation (HANDOFF.md:406-412).
• Azure region two (australiaeast). Blocked on four human-applied IAM grants. There is no cross-region failover yet (HANDOFF.md:448-538).
• Azure shared ACME cache. Seal tr-cross-cloud-sa-key into the bundle and set QUILL_ACME_CACHE_GCS_BUCKET (HANDOFF.md:540-599).
• The AWS control plane's catalog lags GCP (448 vs 451 models). Reconcile it (HANDOFF.md:91-92).
• Nines cannot yet be demonstrated. Sample counts are too low (HANDOFF.md:418-431).
• Fleet freshness workflow is workflow_dispatch only. Enable the cron once every cloud publishes analytics (docs/clickhouse-reliability.md:348-360).
• Direct operational sink. Decide whether to cut GCP over from the outbox to cut Spanner CPU (operational_analytics_direct.py:1-27; rollout.sh:602).
• Citus re-validation. Required before Azure scales beyond Flexible Server (azure_control_plane.sh:33-39).

Traps (most costly first)

• Green status pages can lie. Peer planes answered 200 while their authorize path was broken, because the probes went through enclaves that dialled GCP. Always ask which backend actually served the request (HANDOFF.md:605-610).
• A conformance backend that skips proves nothing. Stand up the containers (HANDOFF.md:612-619).
• STORE is monkeypatch-hostile. Patch the class (#333).
• created_at is not commit order. Never build a cursor on it.
• A ClickHouse MV is not covered by ReplacingMergeTree.
• A deploy that pins a value is not done until the code reading it is deployed. A component with no code or DNS reference can still be load-bearing: tr-eu ran the probes through EventBridge (HANDOFF.md:666-680).
• The full test suite dirties tracked OG assets. It rewrites static/og/providers/*. Do not git commit -a after running it (HANDOFF.md:691-699).
• Credentials need two env vars. You need both GOOGLE_APPLICATION_CREDENTIALS and CLOUDSDK_AUTH_CREDENTIAL_FILE_OVERRIDE. Never export them in a shell profile (HANDOFF.md:746-756).