Enum tables and behaviour flags

Why this exists

A handful of enum rows are load-bearing — business rules in command handlers, list-view mappers, completeness scoring, duplicate detection, and frontend gates branch on whether a row is “Closed”, “Investor”, “Common”, and so on. Historically those branches matched on the row’s Name or Code string. The Admin UI for managing enums (rename, re-order, re-parent) is therefore blocked: a rename of “Closed” would silently break the scheme-closure cascade, a rename of “Investor” would empty the list-view investor column, etc.

The fix: every load-bearing row carries a behaviour flag column. Business rules query the flag, not the Name. Admins can rename freely; the rule keeps firing.

The convention

For each enum-like table that participates in a business rule, add a typed column whose value identifies the semantic the rule needs:

• Boolean flag for a binary distinction (IsClosedStatus, IsCatchAll, IsOperatorRole, IsExtension).
• Free-text classifier NVARCHAR(50) when the rule branches on one of several semantic categories (RoleKind of Investor / Vendor / InvestorAgent / VendorAgent).
• Foreign key when the link is to another enum table (BuildingType.MarketTypeId → MarketType).

The visible Name and Code columns remain editable; the behaviour flag is the contract.

Where each flag is queried

Flag	Where the rule reads it
DevelopmentStatus.IsClosedStatus	CreateDevelopmentCommandHandler, PatchDevelopmentCommandHandler, ReplaceDevelopmentCommandHandler (closure cascade trigger AND reverse-cascade trigger when a development moves out of Closed); SchemeDuplicateStrategy (skip closed schemes).
DevelopmentStatus.IsProposedOrPlanned	Development.cs [ScoredProperty] on StartDate / CompletionDate (excluded when proposed).
DevelopmentStatus.IsClosureCategory	Development.cs [ScoredProperty] on ClosedDate (required when in closure category).
InvestmentCompanyRole.RoleKind	InvestmentEventListViewMapper (investor/vendor name aggregation).
PortfolioCompanyRole.RoleKind	PortfolioListViewMapper.
OccupierCompanyRole.IsOccupierRole	OccupierEventListViewMapper (primary occupier lookup).
SchemeCompanyRole.IsOperatorRole	Scheme.cs [ScoredCollection] Operator-exclude-unless-Hotel rule.
MarketType.IsCatchAll	Portfolio sector-markets picker — orphan-sector fallback.
BuildingType.MarketTypeId	Portfolio sector-markets picker — sector→market filter.
InvestmentOwnershipType.OwnershipKind	PortfolioPartiesEdit.svelte — per-investor Share % column visibility.
DevelopmentType.IsExtension / IsReduction	DevelopmentDetail.svelte — non-first-development picker filter.

How the flags travel to the frontend

Where a flag drives client-side behaviour (Portfolio sector-markets filter, ownership Share % gate, dev-type filter), EnumService.GetXxxAsync projects the flag into EnumGroup.Metadata. The frontend then reads Metadata.IsCatchAll, Metadata.OwnershipKind, etc. when applying the filter.

The OData expand on the consuming entity also surfaces the flag (e.g. the Portfolio expand includes OwnershipKind under InvestmentOwnershipType).

Adding a new flag

When a new business rule needs to depend on a specific enum row:

1. Add a column to the model + DACPAC table + extended-property description. Match the type to the rule’s shape: BIT NULL for binary, NVARCHAR(50) NULL for categorical, INT NULL FK for cross-table links.
2. Update FlagBehaviourEnumRows.sql with an idempotent UPDATE that back-fills the flag by Name or Code (one-time bootstrap from the brittle data we’re replacing).
3. Update the per-table test fixture under src/services/api/app/app.e2etests/Data/TestData/ so e2e seeds carry the flag — the post-deploy script populates production data but doesn’t run after the fixture seed.
4. If the rule fires on the frontend, project the flag into EnumGroup.Metadata inside EnumService.GetXxxAsync.
5. Replace the existing Name/Code match in the rule with a query on the flag.
6. Add a regression test that renames the row but keeps the flag set and asserts the rule still fires.

Adding a new enum table

When you need a brand-new lookup table (not just a new flag on an existing one), walk through each layer below. Every existing enum table follows the same pattern — copy from any of them as a reference.

1. DACPAC table

Create src/data/app/app/Tables/NewThing.sql with the standard column block. Match the indentation of the surrounding columns — the column names should line up vertically. IsActive defaults to 1, IsSystem to 0, Version to 1, ValidFrom to GETUTCDATE():

CREATE TABLE [app].[NewThing] (
    [NewThingId]   INT            IDENTITY (1, 1) NOT NULL,
    [NewThingCode] NVARCHAR (50)  NOT NULL,
    [NewThingName] NVARCHAR (255) NOT NULL,
    [SortIndex]    INT            NOT NULL,
    [IsActive]     BIT            NULL CONSTRAINT [DF_NewThing_IsActive]  DEFAULT ((1)),
    [IsSystem]     BIT            NULL CONSTRAINT [DF_NewThing_IsSystem]  DEFAULT ((0)),
    [Version]      INT            NULL CONSTRAINT [DF_NewThing_Version]   DEFAULT ((1)),
    [CreatedBy]    NVARCHAR (255) NULL,
    [ValidFrom]    DATETIME       NULL CONSTRAINT [DF_NewThing_ValidFrom] DEFAULT (GETUTCDATE()),
    [ValidTo]      DATETIME       NULL,
    CONSTRAINT [PK_NewThingId] PRIMARY KEY CLUSTERED ([NewThingId] ASC),
);

Add sp_addextendedproperty blocks for every column — the existing tables document the standard wording for IsSystem / Version / ValidFrom / ValidTo.

2. EF model

Create src/common/models/Models/NewThing.cs inheriting BaseEntity. The BaseEntity inheritance is what wires the row into the AuditSaveChangesInterceptor audit trail — don’t skip it.

[Table("NewThing", Schema = "app")]
public class NewThing : BaseEntity
{
    [Key] public int NewThingId { get; set; }
    [Required, StringLength(50)]  public required string NewThingCode { get; set; }
    [Required, StringLength(255)] public required string NewThingName { get; set; }
    [Required] public int SortIndex { get; set; }

    public bool? IsActive { get; set; }
    public bool? IsSystem { get; set; }
    public int? Version { get; set; }
    [StringLength(255)] public string? CreatedBy { get; set; }
    public DateTime? ValidFrom { get; set; }
    public DateTime? ValidTo { get; set; }

    [NotMapped] public override int DbId => NewThingId;
}

A new BaseEntity type also needs a one-line entry in src/data/app/app/Functions/ZeroPoint.sql — the ZeroPointSqlMatchesReflection test self-heals the file on first run, so just running it once locally regenerates the SQL.

3. Wire into FormationDbContext

Add public virtual DbSet<NewThing>? NewThings { get; set; } to src/services/api/app/app.api/Data/FormationDbContext.cs. EF picks up the [Table(...)] attribute automatically.

4. Add to EnumTableRegistry

Add a single line to src/services/api/app/app.api/Services/EnumTableService.cs:

new("NewThing", typeof(NewThing), "new thing", "NewThingCode", "NewThingName"),

• The first arg is the URL slug — match the CLR type name for consistency with the existing entries. Slug lookup is case-insensitive.
• displayName is the human-readable label used in error messages (e.g. “Cannot delete new thing ‘X’ because…”). Use lowercase.
• codeProperty is the property frozen on IsSystem = 1 rows. Pass null if the table has no *Code column (role tables, SchemeAttributeListItem, SizeUnit, StatisticField, etc.) — then IsSystem only blocks IsSystem itself from being patched, nothing else.
• nameProperty is what surfaces in the delete-rejected message — pick whatever a human would recognise.

Also update the registry count in EnumWriteGuardTests.Registry_CoversAllEditableEnumTables.

5. Cache invalidation

If anything reads this table through IEnumService.GetEnumByNameAsync, map the CLR type to its cache key(s) in EnumCacheInvalidationInterceptor.EntityToCacheKeys (in src/services/api/app/app.api/Data/Interceptors/). Otherwise admin edits won’t appear in dropdowns until the cache TTL expires.

6. Read surface (if needed)

If the frontend wants to list/filter the table via OData (most enums do), add an OData read controller — src/services/api/app/app.api/Controllers/NewThingsController.cs modelled after TierTypesController.cs. Write goes through /admin/enums/{table} regardless; reads stay on per-type OData routes.

7. Post-deploy back-fill (only on the first deploy that adds the table)

If the table ships with rows (a seed script), the rows want IsSystem = 1. Two paths:

• Existing rows back-fill — add a line to BackfillEnumVersioning.sql (covers IsActive / Version / ValidFrom) and BackfillEnumIsSystem.sql (covers IsSystem). Both are idempotent; pasting the new UPDATE line near the existing alphabetical block is fine.
• Seed script — if you ship a Seed{NewThing}.sql, set IsSystem = 1 explicitly in the MERGE (the back-fill scripts’ IF NOT EXISTS … WHERE IsSystem = 1 guard would otherwise skip the table once it has any system row, leaving the new seed at the column default of 0).

8. Behaviour flags (only if a rule needs to branch on a specific row)

If a business rule needs to react to a specific row (e.g. “the Closed status triggers the cascade”), add a typed flag column following the Adding a new flag recipe above. The flag is the contract, not the row’s Name or Code.

Versioning (Slowly Changing Dimension Type 2)

Every enum table now carries four lifecycle columns: IsActive, Version, CreatedBy, ValidFrom, ValidTo. The behaviour rule is simple:

• A row is “current” when ValidTo IS NULL. EnumService filters every read by this predicate (both root rows and child collections in hierarchical enums). Historical rows are still in the DB but won’t appear in any dropdown.
• Version increments on each semantic edit (changing what the row means: re-parenting, flipping a behaviour flag, splitting one row into two). A cosmetic edit (Name, Description, SortIndex) updates in place and leaves Version alone.
• ValidFrom / ValidTo mark the window in which a row was current. Rows present at the time the versioning columns were introduced have ValidFrom back-filled to the deploy time (post-deploy script); new rows created by the Admin UI will get ValidFrom = now via the column DEFAULT. When a row is superseded, the old row gets ValidTo = now and a new row is inserted with ValidFrom = now, Version = old.Version + 1.
• CreatedBy records who introduced the row (or the version). 'seed' for DACPAC-seeded rows.

FK semantics on data rows are unchanged: a Scheme that pointed at BuildingType id 5 still points at id 5 after a versioning edit — it captures the row’s meaning at the time of capture. If the admin later supersedes id 5, the new version gets a new id; the old Scheme keeps the old reference, which is the point of SCD Type 2.

The Admin UI’s “Save vs Save-as-new-version” choice (PR 5) is what surfaces this distinction to admins. Until that lands, the columns are populated by seeds + post-deploy scripts only.

Who can edit enums

Every editable enum / lookup table is reached through the single admin write surface at /admin/enums/{table}, gated with [Authorize(Policy = "AdminOnly")]. Non-admin users get a 403 regardless of the row’s IsSystem value. This is the user-permission gate; IsSystem (below) is the shape-of-edit gate that admins themselves see.

The surface is generic: one controller (EnumTablesWriteController), one service (EnumTableService), and an allow-list (EnumTableRegistry) of the 27 editable tables. Adding a new enum to the registry makes it editable through the same shape — no per-table handler.

System rows (IsSystem)

Every enum / lookup table carries a nullable IsSystem BIT column. IsSystem = 1 means the row’s *Code is referenced by load-bearing logic and must not change; IsSystem = 0 means the row is fully editable.

The flag was originally back-filled to 1 on every row in every enum table (the safest default while we audited). An audit of the seed scripts and FlagBehaviourEnumRows.sql found that only DevelopmentStatus has Code-prefix logic that genuinely needs the lock — its codes drive completeness scoring and the SchemeDetail exclude-from-total rule via LIKE 'B%' / 'C%' / 'D%' / 'E%' / 'F%'. Every other table was relaxed to IsSystem = 0 by RelaxEnumIsSystem.sql, and admins can rename / re-code those rows freely.

The service honours three rules when IsSystem = 1:

1. *Code is immutable. EnumTableService.PatchAsync rejects any change to the row’s *Code column. The visible *Name and SortIndex stay editable. Reason: the *Code is what the load-bearing rule keys off; silent code drift on such a row would silently break the rule.
2. IsSystem itself is immutable. Admins can’t patch /IsSystem either direction. Forging a system row would let an admin freeze their own code; unfreezing one would let them rename a shipped row.
3. Delete is gated by the same usage check that applies to non-system rows — there’s no separate “system rows can never be deleted” rule, just that they tend to be referenced everywhere so the FK guard fires.

Which rows actually need IsSystem = 1?

Today: only DevelopmentStatus rows whose Code starts with B/C/D/E/F. Those prefixes drive:

• B% / C% → IsProposedOrPlanned = 1 back-fill (drives Development completeness scoring of StartDate / CompletionDate).
• F% → IsClosureCategory = 1 back-fill (drives Development completeness scoring of ClosedDate).
• D% / E% → frontend rule in SchemeDetail.svelte#shouldExcludeFromTotal (excludes Reduction/Extension developments from scheme totals based on Under Construction / Complete status).

If a future change introduces another rule that branches on a specific row’s Code, set IsSystem = 1 on the affected rows (via DB write or a one-shot seed) so the Admin UI freezes the code accordingly. Better yet, follow the behaviour-flag pattern instead and avoid the Code dependency.

Audit trail

Every enum-table edit lands in app.AuditLog automatically, on the same AuditSaveChangesInterceptor that audits the rest of the data tables. Each row carries TableName, RecordId, OperationType (Create / Update / Delete), UserId / UserName / UserEmail, and JSON PreviousValue / NewValue of the changed properties. There is no parallel enum-audit pipeline — the interceptor keys off BaseEntity, and every enum model inherits BaseEntity, so coverage is uniform across all 28 enum tables (including JobType, which became a BaseEntity in PR 3).

The audit log is searchable through /AuditLogs in the existing audit UI; filter by TableName = 'TierType' (etc.) to see the change history for a given enum.

Delete-with-FK-usage guard

EnumUsageProbe (in app.api/Services/EnumUsageProbe.cs) enumerates IEntityType.GetReferencingForeignKeys() for the principal, runs a COUNT_BIG(1) per dependent table, and returns a list of (EntityName, Count) references. EnumTableService.DeleteAsync calls the probe before issuing the DELETE and converts any non-empty result into a ValidationError that lists the affected entities — e.g. "Cannot delete tier 'Tier 1' because it is referenced by: 7 BuildingType, 2 DevelopmentStatus.".

Because the probe is driven from EF metadata, adding a new FK to an enum table is automatically picked up — no change to the probe or the handler. The DbUpdateException catch on SQL error 547 stays as a last-line backstop.

What admins still can’t do (yet)

The only remaining piece is the UI itself — /admin/enums (PR 5), including the Save-vs-Save-as-new-version flow that uses the Version / ValidFrom / ValidTo columns introduced here.

A flag is not always the right answer

Some rules still depend on code-prefix matching:

• SchemeDetail.svelte#shouldExcludeFromTotal excludes Reduction (A.05) and Extension (A.04) developments from scheme totals based on the status code prefix (D for Under Construction, E for Complete). These prefixes haven’t been replaced by flags yet — both the type filter (IsExtension / IsReduction) and the status filter would need flags, and the rule’s D / E semantics aren’t yet captured by an IsUnderConstruction / IsComplete column.

When you find yourself reaching for a code prefix in a new rule, prefer adding a flag. When you find an existing prefix-match that doesn’t have a flag, opening an issue is the right call before extending it.