EncodedId columns on entity tables

The API exposes entities via the 8-character URL-safe base64 Id string described in Entity Identifiers; the database stores integer primary keys. For a long time there was no bridge between those two worlds: an analyst with a bug ticket reading “scheme SC1b2Cd looks wrong” had no way to find that row in SQL without running the C# decoder.

This page describes the bridge — a set of persisted computed columns on the five tables analysts touch most often — and how to use it. One caveat up front: the columns are SQL-Server computed columns, and Fabric mirroring (which is what the dev/UAT/prod warehouses use) does not replicate computed columns. They’re useful for ad-hoc SQL on the OLTP database itself; for navigating from a Fabric-mirrored warehouse back to Formation, use the pattern in the Data warehouse section instead.

Which tables
How it’s implemented
Usage in SQL
Going the other way — int PKs in app URLs
Data warehouse
- Recommended pattern: Fabric-mirrored warehouses
- Computing an encoded ID outside SQL Server
Adding the column to another table
Gotchas

Which tables

A CHAR(8) persisted computed column named EncodedId is present on:

Table	PK column
`app.Address`	`AddressId`
`app.Scheme`	`SchemeId`
`app.InvestmentEvent`	`InvestmentEventId`
`app.OccupierEvent`	`OccupierEventId`
`app.Portfolio`	`PortfolioId`

Each has a supporting nonclustered index (IX_<Table>_EncodedId) so equality lookups by the encoded form are a single-seek.

Other entity tables don’t carry the column yet — see Adding the column to another table below. In the meantime, the app.EncodeEntityId / app.DecodeEntityId functions still work against them (the lookup knows every registered entity type), you just have to call them explicitly.

How it’s implemented

Three scalar UDFs live in the app DACPAC (src/data/app/app/Functions/):

app.ZeroPoint(@typeName) — auto-generated from the BaseEntity subclass list. Returns the per-type XOR basis used by the C# encoder. Regenerated by the ZeroPointSqlMatchesReflection unit test whenever a new entity type is added.
app.EncodeEntityId(@typeName, @dbId) — XORs the PK against the zero-point and emits the 8-char URL-safe base64 form. Mirrors BaseEntity.EncodeIdentifier in C#.
app.DecodeEntityId(@typeName, @encoded) — inverse. Accepts the 6-, 7-, or 8-char form (padded or stripped).

All three are WITH SCHEMABINDING and deterministic, which is what lets the computed columns be PERSISTED. The values are materialised physically on disk (not recomputed on read), so behaviourally they behave like ordinary columns for everything done inside SQL Server itself. They are still defined as computed columns at the DDL level, which matters for downstream pipelines — see Data warehouse below.

The T-SQL and C# encoders are pinned against each other by an e2e test (EncodedIdUdfTests.TSqlEncoder_Matches_CSharpEncoder_ForAllBaseEntityTypes) that runs both on every registered entity type × a spread of ids and asserts byte-for-byte equality. Drift between the two implementations fails CI.

Usage in SQL

Find a row by its encoded ID (the common case — ticket says SC1b2Cd, you want the row):

SELECT *
FROM app.Scheme
WHERE EncodedId = 'SC1b2Cd==';

The column is padded to 8 chars with trailing =; strip or pad to match. For a quick-and-dirty match that’s robust to padding:

SELECT *
FROM app.Scheme
WHERE RTRIM(REPLACE(EncodedId, '=', '')) = 'SC1b2Cd';

Join app-layer identifiers into an analytics query without hand-decoding:

SELECT s.SchemeId, s.EncodedId, o.OccupierEventId, o.EncodedId
FROM app.Scheme s
JOIN app.OccupierEvent o ON o.SchemeId = s.SchemeId
WHERE s.SchemeName LIKE 'ACME%';

Produce an encoded ID from an integer PK (e.g. for a Slack reply pointing at a record):

SELECT EncodedId FROM app.Scheme WHERE SchemeId = 42;
-- or directly:
SELECT app.EncodeEntityId(N'Scheme', 42);

Decode an encoded ID to its integer PK (when the column isn’t available on the target table):

SELECT app.DecodeEntityId(N'Company', 'AB03KwA=');

Going the other way — int PKs in app URLs

The same five entity routes accept the integer primary key directly in the URL path and in OData $filter=Id eq '…' queries:

GET /Schemes/42                   → same row as /Schemes/<encoded>
GET /Schemes?$filter=Id eq '42'   → same match as $filter=Id eq '<encoded>'

So once you have SchemeId = 42 from a SQL query, you can paste /schemes/42 into a browser and land on the record — no decoder required. See Entity Identifiers → Int PKs work directly in URLs too for the detection rule and the (rare) edge cases.

Data warehouse

The story depends on which replication pipeline you’re using. The columns are SQL-Server computed columns (defined AS [app].[EncodeEntityId](...) PERSISTED); they’re stored on disk inside SQL Server, but downstream tools see them as computed and not all of them carry computed columns through to the warehouse.

Pipeline	Carries `EncodedId` to the warehouse?
Fabric mirroring	No. Fabric explicitly skips computed columns — `PERSISTED` doesn’t change that. The supporting `app.EncodeEntityId` / `DecodeEntityId` functions aren’t replicated either.
Synapse Link / CDC / replication	Yes, for most configurations — the change-feed reads the materialised value the same as any other column.
Classic ETL with explicit `SELECT EncodedId, …` projections	Yes — the column is queryable from inside SQL Server like any other.
Classic ETL that introspects schema and skips computed columns	Varies; check your tool’s settings.

For Fabric-mirrored warehouses, do the bridging on the Fabric side instead. The integer PK (e.g. SchemeId) mirrors cleanly as an ordinary column, and the API accepts it directly as a URL — see the recommended pattern below.

The EncodedId columns on OLTP are still useful regardless of pipeline — for ad-hoc SQL queries against the OLTP database itself, where being able to filter WHERE EncodedId = '…' is the whole point. They just don’t reach a Fabric warehouse, so the warehouse pattern needs to come from a different angle.

Recommended pattern: Fabric-mirrored warehouses

Add a calculated column to the Fabric semantic model (not the warehouse schema — the model layer above it) that builds the URL from the integer PK that Fabric does mirror:

URL = "https://formation.pma.co.uk/schemes/" & FORMAT([SchemeId], "0")

Set the column’s Data Category to Web URL. In Power BI visuals, Excel pivot tables, and Excel via Analyze in Excel, the column then renders as a clickable hyperlink straight to the Formation page for that record. Same for the other four entities — swap schemes and SchemeId for the matching slug + PK column.

This works because the API accepts both /schemes/SC1b2Cd== and /schemes/42 as URL forms for the same record (the integer-PK route was added alongside the EncodedId columns; see Entity Identifiers → Int PKs work directly in URLs too). The DAX expression sits entirely in the semantic model, so it doesn’t need any OLTP schema change, no Fabric-side script, and no encoder reimplementation.

Computing an encoded ID outside SQL Server

For ad-hoc cases where you have an integer PK in a non-SQL environment and need to derive the encoded form (e.g. for a manual URL share), the algorithm is:

Compute SHA-256(typeName) (UTF-8 bytes of e.g. "Scheme").
Read the first 4 bytes little-endian as a signed 32-bit int → zeroPoint.
xorResult = zeroPoint XOR dbId.
Serialise xorResult as 4 big-endian bytes.
Base64-encode → 8 characters (including == padding).
URL-safe: replace + with -, / with _.

Decode is the inverse.

Adding the column to another table

Adding EncodedId to an entity table that doesn’t have it yet is a ~5-line change:

In the table’s DACPAC file (src/data/app/app/Tables/<Entity>.sql), add the computed column after the last regular column and before the CONSTRAINT block:
```
[EncodedId] AS ([app].[EncodeEntityId](N'<EntityName>', [<PkColumn>])) PERSISTED,
```

Add a supporting index below the CREATE TABLE:

CREATE NONCLUSTERED INDEX [IX_<Entity>_EncodedId]
    ON [app].[<Entity>]([EncodedId] ASC);
GO

Build the DACPAC (dotnet build src/data/app/data.app.sqlproj -c Release) and deploy via the usual path.

You don’t need to update the zero-point lookup or the UDFs — they already know every BaseEntity type. If the type you’re adding is new (not yet in BaseEntity), the unit test ZeroPointSqlMatchesReflection will fail and auto-regenerate ZeroPoint.sql; commit the diff.

Gotchas

The column is 8 characters including == padding. The app sometimes displays the 6- or 7-char stripped form (without trailing =). For equality queries, either pad both sides or compare stripped forms — see the example above.
Adding the column to a large table rewrites it. ALTER TABLE … ADD … PERSISTED materialises the computed value for every existing row, which takes real time on app.Address and app.Scheme. Roll out during a maintenance window if the table is big.
Algorithm drift is caught at CI, not at deploy. The pinning test (TSqlEncoder_Matches_CSharpEncoder_ForAllBaseEntityTypes) fails if anyone changes the C# encoder without regenerating ZeroPoint.sql, or if the T-SQL UDFs diverge. Don’t bypass it.
Foreign-key columns are unchanged. The encoded column is additive — integer FKs (SchemeId, AddressId) continue to work exactly as before. Existing analyst queries don’t need to migrate.