Skip to content

SunriseAI.Sync — Study Notes

What it is, in one line

A .NET 8 console app that runs nightly (or on-demand), reads vw_sai_* views from MariaDB, and writes them into a per-merchant local .duckdb file that the API queries read-only. It's the ETL layer between the live ERP and the analytical store.

The moving parts, top to bottom

Program.cs
  └─ foreach active merchant (sequential):
       DuckDbConnectionFactory.OpenConnection(merchant)   → opens/creates {merchant}.duckdb
       new SyncOrchestrator(merchant, duckDb, fullLoad).RunAsync()
            ├─ SchemaInitializer.Initialize()              → CREATE TABLE IF NOT EXISTS × 8
            ├─ (if --full-load) SyncStateRepository.ResetAll()
            ├─ SyncWatermarkAsync × 5   (order_summary, order_line_detail,
            │                            customer_master, warehouse_stock_levels,
            │                            purchase_order_detail)
            └─ SyncFullRefreshAsync × 2 (supplier_summary, supplier_product_supply)

Design decisions worth being able to explain

One DuckDB file per merchant ("Option A" isolation). Chosen over one shared file with a merchant_id column. Trade-off: simpler security boundary (a merchant's file physically can't leak another merchant's rows even on a query bug), at the cost of N separate schema-init/sync cycles instead of one. Sync runs merchants sequentially, not in parallel — DuckDB allows many readers but only one writer per file, and since this is a single process anyway, sequential keeps file contention at zero and keeps logs readable. Comment in Program.cs explicitly says parallelize only if sync time becomes a real problem at scale (>10 merchants).

Per-merchant failure isolation. Program.cs wraps each merchant's sync in try/catch — one merchant's DuckDB file being locked, or its MariaDB connection failing, does not stop the other merchants in the same run. Logged and the run continues; overall exit code reflects whether any merchant failed (overallSuccess).

Column mapping is name-based, not ordinal — load-bearing decision. DuckDbWriter.BulkInsert calls PRAGMA table_info(tableName) on the DuckDB side, then does reader.GetOrdinal(colName) against the MariaDB reader to find where that same column lives in the MariaDB result set. This means the CREATE TABLE column order and the view's SELECT column order don't have to match — reordering columns in a view, or adding a column mid-list, doesn't break anything else silently shifting into the wrong column. Explicitly called out in CLAUDE.md as something never to revert to index-based mapping.

VARCHAR-first DuckDB schema, coerced on write. Every DuckDB table column is typed loosely — mostly VARCHAR even for things that are "really" numeric — and DuckDbWriter does the type coercion itself when appending: parses "BIGINT" into long, "INTEGER" into int, "DOUBLE"/"DECIMAL"/"FLOAT" into double. Why: MariaDB on a European-locale server can return numeric-looking values as strings with comma decimal separators ("0,000000"), which DuckDB's own implicit cast can't parse. DuckDbWriter normalizes , → . before parsing, with InvariantCulture, so this doesn't depend on the machine's regional settings.

Primary-key null-guard, silent skip. Before appending a row, BulkInsert checks every column flagged as a PK in PRAGMA table_info. If any PK column is missing from the reader or is null/empty, the whole row is skipped — not appended, no error thrown. Rationale (from CLAUDE.md): avoids PRIMARY KEY violations crashing the whole sync over a handful of bad source rows, at the cost of those specific rows silently not appearing in DuckDB. Worth knowing this is a silent skip — nothing surfaces which rows got dropped or why, beyond the row-count-synced number in sync_state/logs being lower than expected.

Appender, not parameterized INSERT, for bulk writes. BulkInsert uses _connection.CreateAppender(tableName) — DuckDB's native bulk-load API, purpose-built for streaming large row counts in without per-row SQL parsing overhead. This is why CLAUDE.md says never introduce C# DTOs here — the whole path is MySqlDataReader → (inline type coercion) → DuckDB Appender, one row at a time, forward-only, nothing buffered into an object graph. That's what lets it handle 600K+ row tables (order_line_detail) without OOM.

The thing worth flagging — a deliberate rejection, not an oversight

"Watermark sync" and "full refresh" are currently the exact same code path — on purpose.

Look at SyncOrchestrator.SyncWatermarkAsync vs SyncFullRefreshAsync — both do:

var rowCount = await _reader.ReadAllAsync(mariaDbView, reader => _writer.Replace(duckDbTable, reader));

Identical. SyncWatermarkAsync takes a watermarkColumn parameter and never uses it. MariaDbReader.ReadSinceAsync (incremental WHERE watermarkColumn > @since) and DuckDbWriter.Upsert (INSERT OR REPLACE, row-by-row) both exist fully implemented and are never called. This was tried and deliberately de-linked, not forgotten.

Why, concretely — this is the actual reasoning, not a guess: The full-replace path goes through DuckDB's Appender — a bulk columnar C++ load API. The incremental path (Upsert) does one parameterized INSERT OR REPLACE + cmd.ExecuteNonQuery() per row. That per-row overhead (statement binding, type coercion, execution) is so much higher than the Appender's bulk-loaded throughput that a full replace of ~10M orders/order-lines completes in ~20s, while an incremental Upsert of even a single day's ~1,000 new rows, one row at a time, takes longer than that full 10M-row replace. The bottleneck isn't network or MariaDB read time — it's DuckDB single-row write overhead vs. bulk-appended write throughput. So "sync only what changed" is the theoretically-smaller workload but the practically-slower one, given how the two write paths are actually implemented — bulk-appending the full table every time is faster than row-by-row upserting the delta.

The real lesson here, if this comes up: don't assume incremental beats full-refresh just because it moves less data — the two write mechanisms have wildly different per-row costs, and the delta needs to be so small relative to the full-load bulk throughput that it's not obvious the incremental path is faster in walltime, only in bytes-read. ReadSinceAsync/Upsert are kept in the codebase as working, tested code — not dead weight to delete, just not the right tool given how cheap the bulk Appender path already is at this data volume. If daily order volume grows enough that a 20s full replace becomes the actual bottleneck, the fix isn't "turn on the existing Upsert path" as-is — it'd need a bulk-appended incremental write (Appender + a WHERE filter on the read side), not row-by-row INSERT OR REPLACE.

Idempotency / crash-safety model

SyncStateRepository writes status = 'in_progress' before work starts, then 'success' or 'failed' after. If the process is killed mid-sync (crash, OOM, host restart), the next run sees in_progress sitting there from the dead run — but since every sync is a full replace regardless of prior state, the next run just does another full replace and overwrites it. MarkFailed deliberately does not advance LastSyncedAt — that logic is future-proofing for if the incremental path is ever revisited at higher data volumes; it doesn't affect current behavior since nothing reads LastSyncedAt to decide what to pull right now.

--full-load flag

Resets sync_state (DELETE FROM sync_state) before running. Given every sync is already a full replace, --full-load and a normal run are functionally identical in terms of what DuckDB ends up containing — the only difference is whether sync_state's bookkeeping rows get wiped first. Kept as a flag anyway for clarity of intent at the call site (first run / post-schema-change vs. a routine nightly run) and as the switch that would matter again if the incremental path above ever gets revisited.

Quick recall drill (interview-style Q&A)

Q: Why one DuckDB file per merchant instead of a shared file with a merchant_id column? A: Physical isolation as the security boundary — a query bug literally cannot leak across merchants if the data isn't in the same file. Trade-off is N schema-init cycles instead of one, considered acceptable at current merchant counts.

Q: Why does DuckDbWriter map columns by name instead of position? A: The MariaDB view's SELECT column order and the DuckDB CREATE TABLE column order are independently maintained — a view can reorder/add columns without the appender silently shifting values into the wrong DuckDB column and throwing PK violations downstream.

Q: What happens to a source row missing its primary key? A: Silently skipped during bulk insert — not an error, not logged per-row, just absent from the resulting DuckDB table. Only visible as a lower-than-expected row count.

Q: Is the sync incremental or full each night? A: Full, every table, every merchant, every run — by deliberate choice, not oversight. Incremental (ReadSinceAsync + row-by-row Upsert) was implemented and tested, then not wired in, because DuckDB's bulk Appender write path is so much faster than per-row parameterized INSERT OR REPLACE that a full replace of ~10M rows (~20s) beats an incremental upsert of a single day's ~1,000 new rows. The bottleneck is per-row write overhead, not data volume moved.

Q: What happens if the sync process dies mid-run for one merchant? A: That merchant's failure is caught and logged; other merchants still run (overallSuccess becomes false but the loop continues). sync_state for the killed table is left at in_progress from the dead run, but since the next run does a full replace regardless, that stale state doesn't cause incorrect behavior — it just gets overwritten.

Q: Where does the European-locale comma-decimal handling come from, and why? A: MariaDB, on some locale configurations, returns decimal-looking columns as VARCHAR with , as the decimal separator instead of .. DuckDB's own type coercion can't parse that. DuckDbWriter explicitly .Replace(',', '.') before double.TryParse with InvariantCulture, both in BulkInsert and Upsert, so this doesn't depend on what locale the host machine sync runs on.