Warehousing

Warehousing copies rows out of the application database into external stores as they change: a ClickHouse database to run analytics against, a Meilisearch index to search. An entity opts in with a single attribute in its model; the host decides which stores exist and which entities each one gets.

It is the write-side counterpart to Genie:ReportingSources — that section names external databases the engine only ever reads (report datasets, the assistant), this one names stores it writes. A deployment that warehouses into ClickHouse and then reports off it configures both, pointed at the same server.

How a change reaches a target

INSERT / UPDATE                    every ~30s
      │                                 │
      ▼                                 ▼
 trg_{table}_Syncable          WarehousingPollingWorker
 sets IsSyncable = 1     ──▶   CLAIMS a batch: clears the flag
                               and returns the keys, atomically
                                        │
                                   reads those rows ONCE
                                        │
                          ┌─────────────┴─────────────┐
                          ▼                           ▼
                  ClickHouse target            Meilisearch target
                          └─────────────┬─────────────┘
                                        ▼
                        all accepted?  ──▶ claim stands
                        any failed?    ──▶ re-flag, retry next cycle

1. A database trigger flags the row. Because capture lives in the database, it covers every write path — form submit, LINQ, import, raw SQL — not just the ones that go through the engine.
2. One worker sweeps every warehoused entity and fans each batch out to all targets.
3. Rows are claimed — the flag is cleared by the same statement that takes them — and re-flagged if any target rejects the batch.

Why claim first rather than clear afterwards

The obvious order — select the flagged rows, ship them, clear the flag — loses changes. A row updated between the select and the clear has its flag re-raised by the trigger and then cleared by the worker, so that change reaches no target until the row happens to change again. The window spans the whole fan-out, which is seconds on a full batch.

Clearing the flag as the rows are taken closes it: a concurrent update re-raises the flag after the claim and is picked up next cycle. The race becomes a duplicate instead of a loss, and duplicates are already free because every target writes by key.

The cost is the mirror image, and much smaller: if the process dies between the claim and the fan-out, that one batch is not retried.

Why one worker rather than one per target

IsSyncable is a single bit shared by every destination. Independent workers would race to clear it, and the first one home would strand the others’ changes with nothing left to replay them — the flag is the only record that a row was pending. Reading once and fanning out also means a second target costs no extra query against the application database.

The cost of that design is that a batch is replayed to targets that already took it when a sibling fails. Both providers write by key (ClickHouse via ReplacingMergeTree, Meilisearch via the document primary key), so a replay replaces rather than duplicates — which is exactly what makes re-flagging the whole batch the safe choice.

Opting an entity in

Set Warehousing on <Entity> to the strategy its rows should be delivered by, or declare a <Search> block — either one earns the entity its IsSyncable flag and trigger. See Entities → Warehousing for the full contract.

<!-- the default strategy: a dirty flag, swept every cycle -->
<Entity Name="PurchaseOrderLine" PluralName="PurchaseOrderLines" SchemaName="Inventory"
        Warehousing="Polling">

<!-- append-only: read forward from a stored position, never written to -->
<Entity Name="StockMovement" PluralName="StockMovements" SchemaName="Inventory"
        Warehousing="Watermark">

Enabled and true are still accepted and mean Polling.

Turning it on for a table that already has rows

The initial load is automatic. When a target reports that it had no store for an entity — a ClickHouse table that does not exist, a Meilisearch index that was never created or has been deleted — the worker flags every row of the source table pending before reading anything. The following cycles then replicate the whole table rather than only the rows that happen to change from that moment on.

That check happens once per process per entity, before the first write, so it also covers the case where someone drops a warehouse table to force a rebuild: create the gap, restart, and it refills.

The flagging UPDATE skips rows that are already pending (WHERE IsSyncable = 0), so it is cheap to repeat and never rewrites a row for nothing. A large table then drains over several cycles at BatchSize rows each — and a cycle that fills its batch loops again after a second rather than waiting the poll interval, so the backlog clears without extra tuning.

Adding the column to an existing table

The IsSyncable column arrives through your EF migration, and it scaffolds with defaultValue: true — so a column added to a table that already holds rows starts them all queued and the first cycle is an initial load. No hand-edit is needed. (Earlier versions scaffolded false, which silently treated every pre-existing row as already replicated.)

Configuring targets

Targets live under Genie:Warehousing:Targets, keyed by a name that identifies them in logs and on the hosted-services dashboard. See Configuration for every key.

"Warehousing": {
  "Enabled": true,
  "Strategy": "Polling",
  "PollIntervalSeconds": 30,
  "BatchSize": 5000,
  "Targets": {
    "analytics": {
      "Provider": "ClickHouse",
      "ConnectionString": "Host=localhost;Port=8123;Database=genie;Username=default;Password=",
      "Entities": [ "*" ]
    },
    "search": {
      "Provider": "Meilisearch",
      "Url": "http://localhost:7700",
      "Key": "masterKey",
      "Entities": [ "*" ],
      "ExcludeEntities": [ "AuditLog" ]
    }
  }
}

Which entities a target receives

1. The entity must opt in through its model — a Warehousing strategy or <Search>. Without the IsSyncable flag there is nothing to drive replication, so a target can never widen beyond this set.
2. Entities selects from it: "*" (or omitting the key) means every warehoused entity, a name list means only those. Matched case-insensitively against the entity name or its ObjectType.
3. ExcludeEntities is subtracted afterwards and wins on a tie — listing an entity in both is a contradiction, and the safe reading of a contradiction is the one that replicates less.
4. A Meilisearch target additionally skips entities with no <Search> block: a document’s shape (its UrlTemplate and <Context> roles) comes from that config, so there is nothing to build a search result from. It is skipped with a debug log, not an error.

"*" includes <Search>-only entities

Because <Search> is itself an opt-in, Entities: ["*"] on a ClickHouse target picks up entities that declare only a search block. That is intended — "*" means “everything warehoused”. List Entities explicitly if you want a narrower set.

ClickHouse

The engine creates and evolves the target tables itself; there is no separate schema to maintain.

• Each entity becomes one table named wh_{Schema}_{Table} — wh_Inventory_Products, wh_Workflow_Instances. The prefix says at a glance which tables the engine creates and manages, so a warehouse database can hold other things too.

• Columns are ordered as the class reads: the key, then the entity’s own columns in declaration order, then the framework traits it inherits (IsDeleted, the audit stamps, CompanyId) at the end. Providers resolve by name, so this is purely for whoever reads the table — but a warehouse table is read far more often than it is created. Engine-internal bookkeeping (IsSyncable, RowVersion) is not replicated: it describes the replication, not the record. Neither is a field marked Warehouse="false".

  The schema qualifier is not decoration. ClickHouse has one flat namespace per database, so without it a host’s Sales.Forms and the framework’s Wizard.Forms would collapse into a single table.

• Column types come from the EF model, not from the XML — the real column name, nullability, precision and scale, and the provider type behind any value converter. A property that persists through a converter stores something other than its own type, and declaring the property’s type would create a warehouse column the data does not fit.

• The table is exactly the entity — no engine-owned bookkeeping columns. It uses ENGINE = ReplacingMergeTree() ORDER BY (CompanyId, Id), which is what makes a replayed batch collapse back to one row per key.

• On first sight of an entity each process, the table is created if absent and diffed against the model: columns the model has gained are added with ALTER TABLE … ADD COLUMN IF NOT EXISTS.

• Columns are never dropped. A warehouse keeps history the source no longer has, and a field removed from a model is not a reason to discard rows already collected under it. Removed columns are reported in a startup warning naming the table; drop them by hand once you are sure.

Type mapping:

Field	ClickHouse
String, Sequence, Attachment, Select	String
Int, Lookup (the {Name}Id foreign key)	Int64
Decimal, Money	Decimal(precision, scale)
Double	Float64
Boolean	Bool
DateTime	DateTime64(7, 'UTC')

Timestamps are replicated exactly, as UTC. DateTime64(7) is 100-nanosecond ticks — the same resolution a .NET DateTime and SQL Server’s datetimeoffset(7) carry, and finer than PostgreSQL’s microsecond timestamptz — so nothing is lost in the copy. A DateTime64 is one Int64 whatever its scale, so the precision costs no storage.

The wall-clock will not match, and should not

A source column is a datetimeoffset, which carries an instant and the offset it was written at. The warehouse column carries the instant, in UTC. So a row written at 16:44:16 +05:00 reads back as 11:44:16 — the same moment, named the way the rest of Genie names moments (timezones: stored UTC, converted for display). Comparing the two columns side by side in a query tool looks like a five-hour error and is not one.

The original offset itself is not replicated. If you need to know the local time a row was written at rather than the instant, that has to be its own column in the model.

Optional columns are wrapped in Nullable(…), except the two sort-key columns: a nullable sort key costs an indirection on every read, and a replicated row always carries both (CompanyId is written as 0 when unset). Multi-value lookups contribute no column — they live in their own join table and are not replicated today.

Schema checks run in the worker, not at startup

The first sync cycle does the reconciliation, not application start. EntityStore is only populated once the model migration finishes, and the worker already waits on that signal — a separate startup service would race it and see an empty catalog.

Updates replace, they do not accumulate

An updated source row is written again under the same (CompanyId, Id) sort key, and ReplacingMergeTree keeps the last row inserted for that key. One worker ships one entity’s batches in order, so last-inserted is the current state.

Collapsing happens in the background at merge time, so a plain SELECT can briefly return both the old and the new copy of a row. Read through FINAL (or aggregate with argMax) when you need the current state:

SELECT * FROM Inventory.wh_Inventory_Products FINAL WHERE IsDeleted = 0;

That is a ClickHouse fact rather than a Genie one, and it is the standard trade: writes stay append-only and cheap, and readers ask for the collapsed view when they need it.

Why there is no version column

ReplacingMergeTree can take a version column and a delete marker. Genie declares neither. The marker would only ever have mirrored the entity’s own IsDeleted, which is replicated like any other column — a second spelling of the same fact, in a table people read by hand. And the marker cannot be given without a version beside it, so both go.

What you give up is dedup by an explicit stamp: with no version, a key that arrives from two writers at once could collapse to either row. Genie has one warehousing worker per deployment sweeping one entity at a time, so arrival order is state order. If you run two hosts, run the worker on one of them — see Splitting API and worker hosts.

Framework tables

The engine’s own tables are warehoused the same way your entities are — an attribute and an interface, discovered from the same EF model:

Table	Strategy	Why it is replicated
Workflow.Instances	Polling	the process fact table — cycle time, throughput, work in progress
Workflow.TransitionLogs	Polling	where the time actually goes; a bottleneck is invisible without it
Workflow.InstanceStates	Polling	per-node dwell time
Workflow.Approvals	Polling	approver SLA and rejection rates
Workflow.Definitions	Polling	dimension — without it every chart reads DefinitionId = 47
Wizard.Forms	Polling	the wizard dimension, for the same reason
Audit.AuditLog	Watermark	the change history of everything else

They behave exactly like your own entities: Entities: ["*"] covers them, ExcludeEntities holds them back, and they appear in the cycle log alongside everything else.

Upgrading turns these on

A target already configured with Entities: ["*"] starts replicating these tables as soon as you upgrade — no config change, nothing in a diff to explain the extra volume. If you do not want them, name them in ExcludeEntities, or list only the entities you do want in Entities.

Not replicated, deliberately: AssistantChat/AssistantChatMessage (user prose, reasoning traces and generated SQL — and warehousing has no column-level projection, so it is all or nothing), the report-definition tables (warehousing your report definitions into the warehouse you report off is circular), FlowVersion (a full definition blob per save), and every Identity table. The last one is not a policy the reviewer enforces: ISyncable requires CompanyId, and no Identity table has one, so marking Users does not compile.

Wizard responses

A wizard’s responses are warehoused too, and they are the one case where the warehouse table is not a copy of the source table’s shape.

In the application database a response is one row with its field values inside a single JSON column. In the warehouse each field gets its own column — wh_Wizard_Resp{WizardName}, matching the Wizard.Resp{WizardName} table it comes from — so a response is queryable without a JSON function in every expression. Column types follow the field’s type: a Number field becomes Decimal, a Date or DateTime field becomes a timestamp, a Boolean becomes Bool, everything else becomes String.

Three things follow from a wizard being editable while the application runs:

• Every field column is nullable, whatever the definition says today. A response submitted before a field existed has no value for it, and a field that is optional today may be required tomorrow — so nullable is the only setting that stays true for the life of the table.
• The whole submission is kept alongside the columns, in a Payload column of ClickHouse’s native JSON type, last in the table. This is what makes dropping a field safe: the column can go, and the values are still in Payload and still queryable as Payload.ThatField. The columns are a projection for convenience; the payload is the record of what was actually submitted.
• The table is reconciled when the wizard changes. Editing a wizard re-derives its shape, and the next sweep adds any new column before it writes. Warehousing only ever adds — it never drops a column or narrows one — so history stays readable across every shape the wizard has had.

Responses replicate by Polling, like any other mutable table: the response table carries an IsSyncable flag and its trigger, installed when the wizard is provisioned. Soft-deleting a response tombstones it in the warehouse, the same as any other entity.

A wizard that has never been provisioned — one saved with an invalid definition, or one whose table could not be created — is skipped and named in the log, rather than failing the sweep for everything else.

Your own EF entities

The same two declarations work on a host’s hand-written entity, and it is replicated alongside the rest:

[Warehoused]
public class Shipment : EntityTraits<long>, ISyncable
{
    public bool IsSyncable { get; set; } = true;
}

Its trigger is installed by the engine at startup, the same as the framework’s own.

Reading the warehouse

Warehousing is the write side. To read what it wrote, declare the same server as a named datasource under Genie:ReportingSources, and give it the same name as the target — so “Analytics” means the same server in both directions:

"Genie": {
  "Sources": {
    "Analytics": {
      "Dialect": "ClickHouse",
      "Connection": "Host=localhost;Port=8123;Database=Inventory;Username=default;Password=",
      "TenantColumn": "CompanyId"
    }
  },
  "Warehousing": {
    "Targets": {
      "Analytics": { "Provider": "ClickHouse", "ConnectionString": "Host=localhost;Port=8123;Database=Inventory;Username=default;Password=" }
    }
  }
}

Both maps match their keys case-insensitively, so the two sections resolving the same name is not a matter of matching capitalisation. Matching it anyway is what makes the pairing readable.

The two halves stay separate entries on purpose: the write side needs credentials that can create and insert, the read side should be a least-privilege read-only login, and a host may well read from a warehouse it does not write to.

That name is then what a report dataset (Source="Analytics") or an object (<Table DataSource="Analytics">) points at. Both read paths are subject to the same two rules as any ReplacingMergeTree query:

SELECT * FROM Inventory.wh_Inventory_Products FINAL WHERE IsDeleted = 0

• FINAL collapses the row versions, so an updated row does not appear twice.
• IsDeleted = 0 excludes deleted records — the same predicate the application database uses, because it is the same column. FINAL does not drop them: a deleted row keeps its final values in the warehouse on purpose. (An entity with no soft-delete flag, like Audit.AuditLog, needs only FINAL.)

Omit either and the numbers are quietly wrong rather than obviously broken — which is the reason both are stated here rather than left to be discovered.

Meilisearch

A Meilisearch target is both a warehousing destination and the backend of global search. One target configures both halves: the write path indexes documents, and the read path (MeilisearchSearchProvider) queries them. With no Meilisearch target configured, search falls back to the built-in SQL provider.

Documents carry only what a search result needs — the key, CompanyId, and the columns the entity declared Searchable="true" or listed as <Context>. CompanyId is declared filterable because that is what the query path filters on to keep one tenant’s records out of another’s results.

Each write waits for Meilisearch to report the enqueued task finished before reporting success. Meilisearch accepts writes asynchronously, and returning as soon as the task was queued would let the worker clear IsSyncable for a batch the index then rejected.

Deletes

Soft-deleted rows replicate as deletes, and the two targets treat that differently on purpose. ClickHouse gets the whole row with IsDeleted = 1 — a warehouse is read for history, and “this record held these values until it was deleted” is the interesting fact, so nothing is removed and a reader filters the flag. Meilisearch drops the document, because a search hit the user cannot open is worse than no hit at all.

Hard deletes do not reach a target

A hard-deleted row takes its IsSyncable flag with it, so nothing signals the target and the warehouse keeps a ghost record. Genie’s soft-delete trait covers the normal path; delete-row hard deletes and raw SQL DELETEs do not. Prefer soft-delete for warehoused entities. Closing the gap properly needs a delete outbox, which is not implemented.

Operations

The cycle log

A sweep emits one short line per cycle, carrying the cumulative row count across every table:

Warehouse 🏭 Sync Completed in 1.84 s — 7,420 row(s) synced

The per-table detail is attached as a structured Tables property rather than written into the message, so the console stays readable while a structured sink (Seq, or the file sink’s {Properties:j}) can expand it on demand:

Tables: [
  "'Products' synced to analytics in 890 ms and search in 240 ms (5,000 row(s))",
  "'StockMovements' synced to analytics in 470 ms and search in 120 ms (2,000 row(s))",
  "'PurchaseOrderLines' synced to analytics in 195 ms (420 row(s))"
]

A table only earns a line for the targets that actually took its batch, and a table with nothing pending earns no line at all — most tables on most cycles have nothing to ship, and listing them would bury the handful that did something under a roster of idle ones. So the property lists only the tables that actually moved rows, and is empty on a quiet cycle. The level is Information when rows moved and Debug when nothing was pending, so a quiet deployment stays quiet.

Suppressing the line does not suppress the bookkeeping: a target that threw on an empty batch is still counted and still explained in the run detail below.

Errors and warnings are their own events, never folded into the completion line — searching for problems should find log events, not substrings inside a success message:

🏭 [WAREHOUSE:analytics] Failed to write 200 row(s) of Product; the rows stay pending.
🏭 [WAREHOUSE] Sync failed for Product; will retry next cycle.
🏭 [WAREHOUSE] Flagged 12,500 row(s) of Product for a full reload (analytics: created Inventory.Product).

What the monitor holds

Like the notification workers, the cycle’s detail is held by the hosted-service monitor and shown in the dashboard’s run-details view — the per-table lines, then anything that went wrong, then the cumulative total last:

'Products' synced to analytics in 890 ms and search in 240 ms (5,000 row(s))
'StockMovements' synced to analytics in 470 ms (2,420 row(s))

'Categories' → search FAILED: connection refused (rows left pending)

Total: 7,420 row(s) synced across 2 table(s) in 1.84 s; 300 row(s) still pending

Failures are kept here as well as logged separately: run details are where someone asks “what did this cycle actually do?”, and an answer listing only the successes would mislead. An idle cycle says so explicitly rather than showing a blank panel.

The dashboard

The worker appears on the hosted-services dashboard as Warehousing Sync Worker, with per-cycle metrics and pause/resume. Its one-line summary says what happened:

Summary	Meaning
Idle — nothing pending	No entity had a flagged row.
Synced N row(s) across M table(s) in 2.14 s	Everything shipped and the claims stood.
… ; 1 full reload(s) started	A target started empty; its source table was flagged.
… ; M write(s) failed — see warnings	At least one target rejected a batch. Those rows replay next cycle.

Splitting API and worker hosts

To keep the sync loop off an API host, switch to explicit workers in code. A Meilisearch target still registers the search read path on both, independent of the worker:

genie.AddWorkers(w => w.AddWarehousingSyncWorker());

Cost and tuning

The sweep is designed to stay flat in memory as the batch grows:

• Rows are read straight off the reader into positional arrays — no DataTable, and no dictionary per row. Each target resolves its columns to ordinals once per batch rather than hashing a column name per row per target.
• Every target’s columns are read in one query. A second target costs no extra read against the application database.
• ClickHouse rows are projected lazily as the driver streams them, so a batch is never held twice.
• The live/deleted split shares the row arrays; only the pointers are copied, and the deleted list is allocated only when something was actually deleted.

BatchSize defaults to 5,000, which suits the bulk paths both providers use. Lower it only if a single row is unusually wide (large text columns); raising it rarely helps, because a full batch already makes the sweep loop again immediately.

The pending flag is indexed for you

The claim is WHERE IsSyncable = 1, and in steady state almost every row is cleared — so every syncable entity gets a filtered index on the flag automatically, written in your datasource’s dialect ([IsSyncable] = 1 on SQL Server, "IsSyncable" = true on PostgreSQL). It stays tiny, and it turns the per-entity, per-cycle probe into a seek. It arrives in the same EF migration as the column; you do not need to author one.

Strategies

An entity names its strategy on the Warehousing attribute itself — Warehousing="Polling" — so one declaration says both that it is replicated and how.

Polling is the sweep described above, and the default: claim the rows a trigger flagged, ship them, put them back if a target refuses. It needs a mutable row with a long key, an IsDeleted flag, and the IsSyncable column.

Watermark is for append-only tables — an immutable ledger whose rows are inserted and never updated. The worker reads forward from a stored high-water mark on the entity’s monotonic key, and the target records how far it has got. No flag, no trigger, and no write of any kind to the source.

That last property is the whole point. Putting a pending flag on an append-only table means an UPDATE per row forever — on PostgreSQL a non-HOT update on a table whose entire configuration (UNLOGGED, no surrogate key) exists to make inserts cheap. Watermark costs the source nothing.

	Polling	Watermark
Source table	mutable, keyed	append-only
Extra source writes per change	one UPDATE	none
IsSyncable column + trigger	required	never
Captures soft deletes	yes	n/a
Can also be searched	yes	no — there is no flag to share

Delivery is at-least-once either way: a crash mid-cycle replays a batch. Targets write by key, so a replay replaces rather than duplicates.

An unknown value is rejected at model-parse time, naming the entity — choosing a non-default strategy is the entire reason to name one, so falling back to the default would look exactly like it worked.

How Watermark works

Each target keeps its own position in a SyncWatermarks table inside the target itself, one row per replicated table. The worker reads that position, reads forward from it, writes the rows, and then advances it.

That order is the whole design. If the source owned the position, the worker could advance it and then fail to write, losing rows with nothing left to say they existed. Owned by the target, a crash between the write and the advance replays the batch instead — and replaying is free, because every target writes by key.

It also decouples targets from one another. The shared IsSyncable bit cannot clear until every target has acknowledged, so one broken target strands the rest; a checkpoint per target simply falls behind. A target with nowhere to record a position (a search index) cannot use this strategy at all, and says so.

The commit-order gap

An identity key is assigned at insert, not at commit, so a lower key can still be uncommitted while a higher one is already visible:

txn A  inserts Id=100  ............................ commits
txn B  inserts Id=101  ... commits
                            ↑ a sweep here sees 101 but not 100

Advancing to 101 would lose row 100 permanently, and nothing would detect it. So the watermark stops below a gap and the rows above it are re-sent next cycle (harmless — targets write by key).

Some gaps never fill: a rolled-back transaction burns its key, and SQL Server’s identity cache can skip up to a thousand values after an unclean restart. Waiting forever would stall on an ordinary event, so a gap that has persisted longer than WatermarkLagSeconds (default 60) is treated as permanent and stepped over.

Stepping over one gap does not step over a younger one above it. A gap remembers how far the batch reached when it was first noticed: at or below that mark, a key has been observed absent for the whole window and is permanent, so a burned block clears in one step rather than one key per cycle. Above it, the key had not been issued yet — it may be a transaction that started moments ago — so it starts its own window and the checkpoint stops below it.

That setting must exceed the longest transaction that writes to the table. Set it too low and a slow writer’s row is skipped as though it had been rolled back.

If a target’s table is dropped or truncated, its stored position would otherwise skip everything below it silently and forever. So the checkpoint is reconciled once per process against the highest key the table actually holds, and the lower of the two wins — re-shipping, which dedup absorbs, rather than skipping, which nothing recovers.

Related

• Entities → Warehousing — the model contract
• Triggers — how trg_{table}_Syncable coexists with the audit and concurrency triggers
• Search — the read path a Meilisearch target backs
• Configuration — every config key