Fourteen Foreign Keys and an Index Named Placeholder

If the database is the message bus, then its schema is the wire protocol — and this part reads that protocol as an archaeologist reads a dig site, by what was built, what was half-built, and what was shipped by accident and never removed. Thirty-eight tables, thirty-one procedures, and a set of design decisions that reveal, layer by layer, a schema that was reverse-engineered from a running database rather than designed, and then never finished. The headline finding is a number: fourteen foreign keys. Not fourteen missing — fourteen present, across thirty-eight tables, and half of those belong to bolt-on schemas the team didn't write.

Integrity as a code of conduct

Count where the foreign keys are, and the estate's real priorities appear. Of the fourteen, roughly half sit inside the Hangfire and report-builder schemas — third-party and framework-generated, integrity supplied by their authors. The domain tables the team wrote — the high-volume, business-critical ones — carry almost none. Transactions (raw badge events) has no FK to Employee. Neither does LogRecorder, nor VirtualCheckIn, nor LeavesApi, nor Delegate, nor Tolerance, nor StatusSheet, nor the reporting fact table's link back to employees. Attendance for an employee who does not exist is, as far as the database is concerned, perfectly legal.

The maddening part is that the constraint was available. Employee carries a unique constraint on its business key — the exact prerequisite a foreign key needs to point at. The referential integrity was one ALTER TABLE away on every one of those tables, and it was declined every time. This is not ignorance; it is a philosophy, and it deserves its steelman: on very high-volume insert paths, foreign key checks cost something, and badge events arrive in volume. A team optimising ingestion throughput might disable them deliberately. But the same absence on Delegate and StatusSheet — tables that see a trickle of writes — tells me it was not a considered performance trade at all. It was a habit: integrity treated as something the application code promises to uphold, a code of conduct rather than a constraint. And code-of-conduct integrity holds exactly until the first bug, the first bad sync, the first injected EXEC from part 12 — at which point the orphan rows arrive and the database, having promised nothing, catches nothing.

The index named after its own template

The single most eloquent artefact in the schema is an index name. Reverse-engineer a database into EF Core and you inherit its indexes verbatim into the fluent configuration; nobody cleaned this one up, and among the reporting fact table's six non-clustered indexes sits this:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
    ON dbo.ShiftDetailMonthlyReport (LogDate, EmployeeId);

That bracketed string is not a name. It is the placeholder token from SSMS's “Create Index” template — the text the tool inserts for you to overtype with a real name. Someone opened the template, filled in the columns, and executed it without replacing the name field. The index works — it indexes exactly what it says — and it has carried the tool's fill-in-the-blank prompt as its identity ever since, all the way through a database-first scaffold into the application's C# model, where the same string appears as a HasDatabaseName argument. A tool's instruction to the developer became a permanent database object. It is funny, and it is also a perfect fossil of the schema's whole provenance: generated, accepted, shipped, never read.

It has company. Two more of the fact table's indexes carry machine-generated nci_wi_ hash names — the signature of Azure SQL's automatic tuning, which watches query patterns and creates indexes for you. Accepting those recommendations blind is its own hazard: three of the table's six indexes lead on LogDate with overlapping column shapes, so writes pay to maintain near-duplicate structures while the optimiser picks one and ignores the rest. Auto-tuning is a genuinely useful feature; auto-tuning with nobody reviewing the accumulation is how a hot table grows a maintenance tax nobody chose.

Three-state booleans and naming drift

Two smaller pathologies, because they recur across the schema and each teaches a rule.

The three-state boolean. Most reference tables carry an IsActive BIT soft-delete flag — sensible. But on Employee and ShiftAllowance the column is nullable. A BIT NULL is not a boolean; it is a three-valued thing — true, false, and unknown — and every query that filters WHERE IsActive = 1 silently drops the NULL rows, while WHERE IsActive = 0 drops them too. An employee whose IsActive is NULL is neither active nor inactive; they are invisible to both halves of every filter that ever asked. Whether that means “active” or “inactive” is decided per query, by whoever wrote the WHERE clause, inconsistently. A nullable boolean is a bug that compiles, and it is scattered through the most-queried tables in the estate.

Naming drift. The schema cannot decide what anything is called. The primary-key column is ID here, Id there, RecordId elsewhere, JobId in a fourth place. The business identifier is EmployeeId INT on most tables but Employee.ID NUMERIC(18) on its own row, and ManagerID NUMERIC(18) where it points back — so an employee's ID and their manager's ID have different types, and joining them forces an implicit conversion on a high-traffic path. A table is named LeavesApi, after the API that fills it rather than the thing it holds. The work-queue enum is spelled Tolorance in C# while its table is spelled Tolerance. None of these is a bug on its own; collectively they are a tax on every developer who must remember which spelling this table chose, and a trap for every join that assumes the types match.

Demo mode mutates production

The archaeology turns up one genuinely alarming artefact. A pair of stored procedures, DemoBegin and DemoEnd, exist to prepare the system for a demonstration — and the way they do it is to reach into the live employee master and deactivate real people whose IDs match certain leading digits, stashing them in a holding table, then restore them afterwards:

-- DemoBegin (shape only): move real employees aside for a demo
INSERT INTO dbo.DemoEmp SELECT * FROM dbo.Employee WHERE <id pattern>;
UPDATE dbo.Employee SET IsActive = 0 WHERE <id pattern>;

Demo mode by mutating production master data. If DemoBegin runs and DemoEnd does not — a crash, a closed session, a forgotten step — real employees are left deactivated in the live system, dropping out of syncs, dashboards and, downstream, attendance and allowance calculations. The blast radius of a sales demo is the production org chart. Demo and test states belong in demo and test environments; the moment “put the system in demo mode” is a mutation of production rows, the demo is one missed cleanup away from an incident, and the recovery depends on a holding table nobody monitors.

What the dig site tells you

Assemble the layers — verbatim scaffolded indexes, an SSMS placeholder as an object name, auto-tuning accepted unreviewed, foreign keys declined wholesale, nullable booleans, drifting names, demo-by-mutation — and the story is coherent. This schema was not designed; it was reverse-engineered from a running system and then extended by accretion. Each individual decision is defensible or forgivable in isolation; together they describe a database that the application treats as a data-shaped scratch space it fully controls, rather than a system of record that defends its own invariants. That works right up until something the application didn't anticipate touches the data — and in a five-executable estate with no foreign keys, something always does.

Not everything in the schema is accretion, though. Buried in the stored-procedure catalogue is a small, deliberate, and genuinely interesting attempt at real computer science — walking an org hierarchy — attempted three separate ways, with a relationship flag that is a decimal number cosplaying as binary. Next, org charts in T-SQL, three ways.