DuckDB sync health — runbook

How the DuckDB sync watches itself, what each signal means, and what to do when one goes red. Written after the 2026‑07 incident, in which the VehicleEntry and SSCAffectedVIN syncs silently dropped almost every changed row for eleven weeks while every run reported success.

What went wrong, in one paragraph

The ADP models gained properties (VehicleEntryModel: Intermediary, Distributor; SSCAffectedVINModel: Labors, PartNumbers, …) but the DuckDB tables, created once, did not. The sync destination wrote rows by position, so a batch failed on the first extra column, the diff queue retried each row five times and then marked it Dead — with no error message — and the run carried on and logged success. The only things anyone could see were freshness checks, and a single leaked row per batch kept those green.

Three layers now stand in the way of a repeat:

Layer	Where	What it does
Package fix (ADP.SyncAgent ≥ 1.16.5)	DuckDBSyncDataDestination, DuckDbCsvSyncDataSource	Reconciles every table with its model by name before writing; refuses type/key drift loudly; never writes a torn row or an aborted batch; a failed batch carries its reason; Dead rows rest and retry (DeadRetryAfter, default 24 h) and are re‑queued the moment a table is repaired.
Host hotfix (tiq‑sync‑agent)	DuckDbSyncUtilities.RunSyncEngineAsync	Compares every table with its model on every run (Warn), repairs VehicleEntry / SSCAffectedVIN in place (Reconcile), re‑queues Dead rows once per schema change, refuses to publish a snapshot with an un‑checkpointed WAL.
Guardrails (this page)	Rastgo checks + alerting	Per‑row reconciliation against the source files, the sync engine's own footprint, row‑shape invariants, and a notification when a critical check changes state.

The sync engine's footprint

Every DuckDB sync run leaves two small JSON files, written atomically and read by the health checks with plain DuckDB read_json_auto():

• Per destination table — company-data-duckdb-databases/sync-status/<table>.json schemaVerdict (Match / TrailingColumnsMissing / Incompatible / Absent / Unknown), driftCount, the exact missing / extra / mismatched columns, succeeded, lastRunUtc, lastSource.
• Per CSV diff queue — <source>_DuckDbSyncEngine/<file>.duckdb.status.json pending, inFlight, dead, deadWithoutError, plus the same verdict and run fields.

They are files, not tables, on purpose: they never ship inside a published read snapshot, they need no DuckDB lock to read, and a glob is all a check needs.

The checks and what to do

All live in tiq-sync-agent.DataHealth/checks.sync-agent.yaml, run hourly by the Functions app (health/run, health/dashboard).

Check	Severity	Red means	Do
gap.vehicle_entry.source_rows_not_loaded	critical	Per dealer, source rows (last 120 days) whose VIN + dealer + invoice date is not in VehicleEntry. This is the tripwire the incident lacked — it catches a lost update, which max‑date and count checks cannot.	Look at quality.sync_engine.* for the cause. If the queue shows Dead rows, fix the cause, then let DeadRetryAfter retry or call RequeueDeadChangesAsync. Verify with the CSV‑vs‑DuckDB comparison per company.
gap.vehicle_entry.loaded_rows_not_in_source	warning	Recent VehicleEntry rows no source file for that dealer contains: rows the sync leaked without recording (the diff queue can never delete them).	POST duckdb/vehicle/vehicle-entry/reconcile-orphans (dry run), review, then ?apply=true. Refuses more than 5 % / 10 000 rows without force=true.
gap.ssc_affected_vin.source_rows_not_loaded	critical	Source campaign rows missing from SSCAffectedVIN.	As for VehicleEntry.
quality.sync_engine.schema_drift	critical	A table's columns disagree with its model.	With ADP ≥ 1.16.5 this repairs itself on the next run (added columns) — if it stays red, the drift is a type or primary‑key change and the run refuses on purpose; decide the migration.
quality.sync_engine.dead_changes	critical	Rows the queue gave up on. Data loss in progress.	Read _LastError in <file>.duckdb …_changes; fix the cause; rows retry after DeadRetryAfter.
quality.sync_engine.dead_changes_without_error	warning	Dead rows with no reason recorded — the silent shape of the incident.	Impossible on ADP ≥ 1.16.5; means an older package is deployed.
quality.sync_engine.failed_runs	critical	The last run of a source failed or was refused.	Function App logs for that activity.
freshness.sync_engine.queue_last_run	warning	A source's queue has not been drained for > 9 h (runs are 3‑hourly).	Durable orchestration status; app health.
quality.ssc_affected_vin.row_shape / quality.vehicle_entry.row_shape	critical / warning	Values that cannot come from the source (a JSON array in LaborCode1, absurd dates or ids): the signature of misplaced columns.	Re‑send the affected rows (SSC: the hotfix's forced re‑send does it once).
gap.vehicle_entry.source_vs_loaded	warning	Newest source invoice vs newest loaded. Only catches a load that stopped.	Kept for that case; do not rely on it for dropped rows.

Alerts

After every health run, HealthAlertNotifier compares the set of failing critical checks with the last alert (<Rastgo results root>/alerts/critical-state.json) and speaks when it changed — something broke, or something recovered — plus one reminder a day while anything stays red. Nothing is repeated hourly.

• Rastgo:AlertWebhookUrl — a Slack‑ or Teams‑compatible incoming webhook; receives {"text": …}.
• Rastgo:DashboardUrl — appended to the message when set.
• Without a webhook the alert is still written to the host log at Error level with the prefix HEALTH ALERT, so an Application Insights alert rule can key on it. Set one or the other; an alert that reaches nobody is the incident again.

Before upgrading ADP (checklist)

A model gaining a property is exactly the event that started this. Before publishing a build that carries a new ShiftSoftware.ADP.Models:

1. git log ADP.Models since the sha currently deployed; list every property added, removed or retyped on a model that has a DuckDB table.
2. Run the audit against a copy of the latest read snapshot (DestinationSchemaReconcileTests.Audit_Production_Snapshot_Against_Models); it prints each table's verdict. Added columns are fine (self‑repairing). A type or key change needs a migration decision.
3. Back up company-data-write.duckdb and the *_DuckDbSyncEngine diff databases while the durable orchestration is idle. Never open those files from outside the app while it runs — DuckDB's lock is exclusive.
4. After the first orchestration: quality.sync_engine.schema_drift green, dead_changes zero, gap.*.source_rows_not_loaded within bounds.

Verifying a recovery by hand

-- per dealer: source rows the table does not have (same logic as the check, no window)
-- run in DuckDB against the read snapshot, with the source CSVs mounted
SELECT parse_filename(filename, true) AS file, COUNT(*) AS not_loaded
FROM read_csv_auto('…/CompanyInvoiceData/VSDatas_*.csv', union_by_name=true, filename=true, all_varchar=true) s
WHERE NOT EXISTS (SELECT 1 FROM VehicleEntry v
                  WHERE v.VIN = upper(regexp_replace(s.FullChassis, '[\s\p{Z}\x{0B}\x{85}]+', '', 'g'))
                    AND v.CompanyHashID = s.Dealer_IdentityID)
GROUP BY 1;

Expect zero (or the handful of rows the loader drops on purpose: blank or malformed VINs, which quality.sales.empty_vin / malformed_vin list).