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)

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="/view/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 configured (Genie:Meilisearch, see Integration).

The IsDirty sync bit

Declaring <Search> also gives the entity a NOT NULL IsDirty bit + an insert/update trigger. The Meilisearch sync worker uses it to re-index changed rows incrementally; it is harmless (just an extra bit column) when the SQL provider is used. Worked example: sample/Inventory/models/entities/Product.entity.xml (and Supplier.entity.xml for <Search>).

Raw-SQL inserts must supply it. EF sets IsDirty automatically, but hand-written <InsertSql> and <ImportConfig> <BulkCopy> bypass EF and the column has no DB default — so include IsDirty in the column list and set it (1 = queue for indexing), e.g. <Expression Column="IsDirty" 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 Genie.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 IsDirty search flag is excluded, so a change that only flips IsDirty (the Meilisearch sync worker clearing it, or the search trigger setting it) produces no audit row. This also lets the logging trigger coexist with the <Search> IsDirty 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.

Not yet tamper-evident

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