Entities

An *.entity.xml file defines a database entity (table). It is read at compile time by the Genie.Source generator (to emit an EF Core entity + configuration) and again at runtime by the migration service (EntityStore).

<Schema>
  <Entity Name="Customer" SchemaName="Sales" PluralName="Customers" Description="Customer master">
    <Fields> … </Fields>
    <Indexes> … </Indexes>
  </Entity>
</Schema>

<Entity> attributes

Attribute	Req	Notes
Name	✅	entity / class name (singular, PascalCase)
SchemaName	✅	DB schema (e.g. Sales)
PluralName	✅	table name & collection name (e.g. Customers)
Description	—	becomes a DB table comment + XML doc on the class
Logging	Disabled	Enabled opts the entity into row-level change logging (see Logging)
Concurrency	Disabled	Enabled opts the entity into optimistic-concurrency (lost-update) protection (see Concurrency)
Warehousing	(off)	Names the strategy that replicates this entity’s rows to the configured warehousing targets — Polling or Watermark (see Warehousing)

Inherited columns (do not declare these)

Every entity derives EntityTraits<long>, which already provides: Id (PK), IsDeleted, CreatedAt, CreatedById, UpdatedAt, UpdatedById, DeletedAt, DeletedById, CompanyId (tenant). Declaring Id yourself would conflict. The audit timestamps are DateTimeOffset stored in UTC (stamped DateTimeOffset.UtcNow).

Field types

Each child of <Fields> is named after its type. Universal attributes (all types):

Attribute	Default	Notes
Name (required)	—	column / property name
Label	= Name	display label
Required	false	NOT NULL (the CLR property becomes non-nullable)
Unique	false	unique constraint
Indexed	false	single-column index
Searchable	false	include this column in global-search matching (see Search)
DefaultValueIfNull	—	default value
Description	—	column comment + XML doc

Element	CLR type	Type-specific attributes
<String>	string	Limit (max length), RegexValidation
<Int>	long	CalculatedExpression
<Decimal>	decimal	Precision (18), Scale (2)
<Money>	decimal	— (SQL Server money; auto-mapped to numeric(19,4) on PostgreSQL)
<Double>	double	—
<Boolean>	bool	—
<DateTime>	DateTimeOffset	— (timezone-aware; SQL Server datetimeoffset, PostgreSQL timestamptz)
<Sequence>	string	Format (required, e.g. INV-#6; a bare # defaults to width 2) — emits [SequenceNumber]; a DB trigger fills it
<Select>	enum	Multiple; child <Options><Option Value="" Label="" Default="" /></Options> — generates a {Entity}{Field} enum
<Lookup>	FK	EntityName (required, target entity), Multiple, IsParent — generates {Name}Id + a navigation; the target gets an inverse collection
<Attachment>	string	MediaTypesSupported (required, CSV/; of MIME types), Multiple

Point Lookups at generated entities

Keep Lookups pointing at other generated entities — the generator adds the reverse collection on the target, which it can’t do for hand-written engine entities. Company association is already available via the inherited CompanyId.

Indexes

<Indexes>
  <Index Name="IX_Customer_Email" Unique="true" Filter="[IsActive] = 1">
    <FieldRef Name="Email" Sort="Asc" />
  </Index>
  <Index Name="IX_Customer_Type_Active">
    <FieldRef Name="CustomerType" Sort="Asc" />
    <FieldRef Name="IsActive" Sort="Asc" />
  </Index>
</Indexes>

<Index>: Name (required), Unique (false), Filter (extra WHERE; combined with the soft-delete filter). <FieldRef>: Name (required; for a Lookup use the lookup’s name — it maps to the …Id column), Sort (Asc/Desc). A FieldRef may target a declared field or an inherited EntityTraits column (Id, CompanyId, IsDeleted, CreatedAt/CreatedById, UpdatedAt/UpdatedById, DeletedAt/DeletedById) — needed to lead a composite index with the tenant/soft-delete columns.

Grid performance

A table view runs WHERE CompanyId=@x AND IsDeleted=0 ORDER BY <SortBy> (plus a COUNT(*)). Past a few hundred-thousand rows, give it a composite index that matches: lead with the equality column(s) (CompanyId) then the sort column, e.g. IX_T_Company_Name on (CompanyId, Name). The generator ANDs [IsDeleted]=0 into the index filter automatically, so the optimizer can SEEK by company and read rows already ordered — no full scan + sort. (INCLUDE/covering columns aren’t expressible here; the per-page key lookups for ~20 rows are negligible. Sort on a FieldRef is recorded but the emitted index is ASC.) Worked example: sample/Inventory/models/entities/Supplier.entity.xml.

Worked example: sample/Inventory/models/entities/Product.entity.xml.

Search

Expose an entity to the global header search (the Ctrl/Cmd-Shift-F box; enabled per app via modules.headerSearch, see Integration). Add an optional <Search> block as a sibling of <Fields>/<Indexes>, and mark the columns to match with Searchable="true":

<Search UrlTemplate="/object/Customer?id={Id}" Filter="IsActive = 1 AND CompanyId = @SessionCompanyId">
  <Context Field="Name" Role="Primary" />
  <Context Field="CustomerNumber" Role="Number" />
</Search>

Attribute / element	Default	Notes
ObjectType	= entity Name	the group label results appear under
UrlTemplate (required)	—	route a selected result opens; {Token} placeholders are substituted from row values ({Id} = primary key)
Enabled	true	set false to keep the config but drop the entity from search
Filter	—	extra SQL predicate AND-gated onto the generated query for row-level authorization/visibility (see below)
<Context Field Role>	—	a projected value. Role is Primary (the row’s main text, shown under the Name key), Number (the record identifier shown in the result tag), or Extra

The columns matched against the term are the fields marked Searchable="true" (falling back to the <Context> fields if none are). Results are shaped { ObjectType, Context, Url } and shown grouped by object type, each row tagged with its object type + record number.

Row-level authorization (Filter)

The generated SQL query already excludes soft-deleted rows and scopes to the caller’s company (fn_user_company_scope). Filter adds your own predicate on top (joined with AND) — use it so users only see records they’re allowed to. It is trusted author SQL (like an index Filter) and may reference the session parameters injected per request:

Parameter	Value
@SessionUserId	the authenticated user’s id
@SessionCompanyId	the active company id
@SessionRoles	comma-separated role names (filter with STRING_SPLIT(@SessionRoles, ','))
@SessionCartId	the browser/cart id; plus any host-registered ISessionParameterProvider params
@SessionTimeZone	the caller’s effective IANA time zone (e.g. Asia/Karachi); resolved from the session cookie / user preference / X-TimeZone header, else UTC
@SessionTimeZoneOffset	that zone’s current UTC offset in minutes

Timestamps are stored UTC (DateTimeOffset) and the UI converts to local for display — so most SQL needs no conversion. When SQL genuinely needs local time (e.g. grouping by the user’s local day), use the session zone: dialect-neutral via the offset — DATEADD(MINUTE, @SessionTimeZoneOffset, SYSUTCDATETIME()) (SQL Server) / now() + (@SessionTimeZoneOffset || ' minutes')::interval (PostgreSQL) — or, for full DST-correctness across arbitrary dates on PostgreSQL, utcColumn AT TIME ZONE @SessionTimeZone. Keep instant comparisons (e.g. “is this deadline overdue?”) in UTC (SYSUTCDATETIME() / now()) — they are timezone-independent.

Write Filter dialect-neutral for dual-DB deployments (the predicate is emitted verbatim into both the SQL Server and PostgreSQL queries). The Filter applies to the SQL provider; under Meilisearch only the company filter is enforced (model your index/permissions accordingly).

Search resolves through one backend provider, chosen per deployment: the built-in SQL provider (dialect-appropriate LIKE/ILIKE generated at runtime, company-scoped) by default, or Meilisearch when a warehousing target is configured.

The IsSyncable sync bit

Declaring <Search> also gives the entity a NOT NULL IsSyncable bit + an insert/update trigger (trg_{table}_Syncable). The warehousing sync worker uses it to ship changed rows incrementally; it is harmless (just an extra bit column) when the SQL search provider is used and no warehousing target is configured. Warehousing="Polling" earns the same bit — one flag serves both features, because they ask a row the same question: has it changed since it was last shipped out? Worked example: sample/Inventory/models/entities/Product.entity.xml (and Supplier.entity.xml for <Search>).

Raw-SQL inserts must supply it. EF sets IsSyncable automatically, but hand-written <InsertSql> and <ImportConfig> <BulkCopy> bypass EF and the column has no DB default — so include IsSyncable in the column list and set it (1 = queue for indexing), e.g. <Expression Column="IsSyncable" Expression="1" />. The same applies to any other NOT-NULL framework column a bulk insert doesn’t carry (IsDeleted, CompanyId, CreatedById, CreatedAt).

Logging

Set Logging="Enabled" on <Entity> to capture an append-only change log for the entity — every INSERT / UPDATE / DELETE, with the full before/after row image, the acting user, and the tenant. It is disabled by default.

<Entity Name="Invoice" SchemaName="Sales" PluralName="Invoices" Logging="Enabled">

How it works, and why it is reliable regardless of how the row was changed:

• The entity migration installs a per-entity database trigger (AFTER INSERT, UPDATE, DELETE, dual-dialect) that writes one row per change into the engine-owned Audit.AuditLog table. Because capture lives in the database and runs inside the mutating transaction, it covers every path — form submit, row delete, LINQ, import, even raw SQL — and no change can commit without its audit row.
• The actor and tenant are read straight off the row’s own audit columns (CreatedById on insert, UpdatedById on update/delete, CompanyId), which the engine already stamps from @SessionUserId / @SessionCompanyId. (Hard DELETEs record the row’s last UpdatedById as a best effort; prefer soft-delete — an UPDATE — for exact delete attribution.)
• The before/after images are stored as JSON in OldValues / NewValues. The application-only IsSyncable search flag and the RowVersion concurrency stamp are excluded, so a change that only flips IsSyncable (the warehousing sync worker clearing it, or the syncable trigger setting it) or only bumps RowVersion produces no audit row. This also lets the logging trigger coexist with the Warehousing IsSyncable trigger and the Concurrency RowVersion trigger without conflict.
• Setting Logging="Disabled" (or removing the attribute) drops the trigger on the next migration.

Worked example: sample/Inventory/models/entities/Product.entity.xml.

Warehousing

Set Warehousing="Polling" on <Entity> to replicate the entity’s rows to external stores — a ClickHouse database for analytics, a Meilisearch index for search. It is disabled by default.

<Entity Name="PurchaseOrderLine" SchemaName="Inventory" PluralName="PurchaseOrderLines"
        Warehousing="Polling">

The model says only that the entity is replicated, never where to. Which stores exist and which entities each one receives is a host concern, configured in Genie:Warehousing — so the same model file deploys to an environment with a warehouse and one without.

Attribute	Default	Notes
Warehousing	(off)	Polling | Watermark — opts the entity in and names how its rows are delivered. Enabled/true are accepted as synonyms for Polling; Disabled/false/absent leave it off. An unknown value throws at parse time, naming the entity

One attribute carries both facts on purpose. The pair it replaces could express Warehousing="Disabled" WarehousingStrategy="Polling", which means nothing — and a strategy is only ever interesting alongside the opt-in it qualifies.

WarehousingStrategy was removed

A leftover WarehousingStrategy attribute is rejected at parse time rather than ignored. Ignoring it would hand an author who wrote WarehousingStrategy="Watermark" the polling behaviour instead — the opposite of what they asked for.

Choosing a strategy

	Polling	Watermark
For	ordinary mutable tables — the default	append-only ledgers: rows inserted, never updated
Needs	IsSyncable column + trigger, a long key, IsDeleted	a monotonic long key, nothing else
Writes to the source	one UPDATE per replicated row	nothing
Captures soft deletes	yes	n/a — nothing is deleted

Watermark reads forward from the entity’s key and records how far it got in the target, so the source table is never written to. That is the entire reason it exists: putting a pending flag on a high-volume append-only table means an UPDATE per row forever, on the one table whose design is built around cheap inserts. It earns no IsSyncable column and no trigger, and therefore cannot be combined with <Search>.

Per-field control

Warehousing replicates every column of an entity it covers — there is no column-level projection in the target configuration — so two optional field attributes exist to shape that:

<String Name="Notes"   Warehouse="false" />          <!-- never leaves the OLTP database -->
<String Name="Payload" WarehouseType="Json" />       <!-- text here, a real JSON column there -->

Attribute	Default	Meaning
Warehouse	true	false holds the column back from every target
WarehouseType	derived from the field	Declares the column as String, Int64, Decimal, Double, Boolean, DateTimeOffset or Json. An unknown value throws at parse time, naming the field

Warehouse="false" is the more important of the two: without it, opting a table in is an all-or-nothing decision about its contents, which is what makes a table carrying one sensitive or very wide column unwarehousable. WarehouseType is for the handful of columns whose storage type is not the type they hold — most usefully a text column holding a JSON document, which a target with a real JSON type can index by path rather than store as an opaque blob.

How it works:

• The entity gains the NOT NULL IsSyncable bit and a per-entity trg_{table}_Syncable trigger that sets it on every insert and update. Because capture lives in the database, it covers every write path — form submit, LINQ, import, raw SQL.
• A background worker sweeps flagged rows and pushes them to every configured target that selects the entity, clearing the flag only once they have all accepted. See Warehousing for the delivery model, ClickHouse schema management, and the hard-delete caveat.
• The initial load is automatic. The first time a target reports it has no store for the entity — a ClickHouse table that does not exist, an index that was never created — the worker flags the whole source table pending, so the entity’s existing rows are replicated rather than only the ones that change afterwards.
• An enabled <Search> block opts the entity in too. Both features are driven by the same IsSyncable flag, so declaring either one is enough to earn it — and an entity that declares both gets one trigger, not two.
• Setting Warehousing="Disabled" (or removing the attribute) drops the trigger on the next migration, unless the entity still declares <Search>.

A Meilisearch target needs <Search>

A Meilisearch document’s shape — its UrlTemplate and <Context> roles — comes from the <Search> block. An entity with Warehousing="Polling" and no <Search> replicates to analytical targets but is skipped by search ones, since there is nothing to render a result row from.

Worked example: sample/Inventory/models/entities/PurchaseOrderLine.entity.xml (warehousing without <Search>).

Concurrency

Set Concurrency="Enabled" on <Entity> to protect the entity against lost updates — the classic race where two users load the same record and the second save silently overwrites the first. It is disabled by default.

<Entity Name="Product" SchemaName="Inventory" PluralName="Products" Concurrency="Enabled">

How it works:

• The entity gains a RowVersion column (a long, default 0) marked as an EF concurrency token. A per-entity AFTER UPDATE (SQL Server) / BEFORE UPDATE (PostgreSQL) trigger increments it on every update, so any write path — form submit, row delete (soft-delete is an update), LINQ, import, even raw SQL — advances the stamp. The column is provider-agnostic (no SQL Server rowversion / PostgreSQL xmin), so the same definition works on both databases.

• The React form loads RowVersion with the record and round-trips it on submit. The engine compares the submitted stamp against the current stored value before writing; if they differ, someone changed the row first and the submit is rejected with HTTP 409 (the UI shows a “record was changed by someone else — reload” message). No editor field or layout entry is needed — RowVersion is a reserved, read-only column the framework threads through automatically.

• The LINQ/EF write path is fully atomic: EF includes RowVersion in the UPDATE … WHERE, and a zero-row result raises the same 409. For authored-SQL views the compare is a reload-then-write check (a narrow window). To make an authored update/delete atomic too, add the stamp to the WHERE clause — the engine treats “zero rows affected” as a conflict when the caller supplied a RowVersion:

  <EditSql>UPDATE Inventory.Products SET Name = @Name /* … */ WHERE Id = @Id AND RowVersion = @RowVersion</EditSql>

• RowVersion is excluded from change-logging (like IsSyncable) — the trigger’s own increment never produces an audit row.

• Setting Concurrency="Disabled" (or removing the attribute) drops the trigger on the next migration.

The host must add the physical column

Like IsSyncable, the generated RowVersion property needs a database column — add and run an EF migration after enabling concurrency (dotnet ef migrations add AddProductRowVersion). Until the column exists the trigger creation is skipped (logged, non-fatal) and the check simply doesn’t engage.

Worked example: sample/Inventory/models/entities/Product.entity.xml.

Not yet tamper-evident

Capture is complete, but the ledger is not yet tamper-evident — hardening it (revoking UPDATE/DELETE on Audit.AuditLog from the app login, plus a hash chain) is a planned follow-up.