August 6, 2026

This is not hypothetical yet

The concrete example came from risk advisory services — adjacent to audit, not audit itself. Tools in that area are cutting work that used to be billed hourly to roughly a third of the hours it once took. The immediate consequence is realization: the firm is not recovering what the engagement should recover. The second consequence follows behind it, because the same compression that lowers realization also lowers reported productivity.

That is the honest framing of the problem. It is not that AI might eventually change the economics. It is that a measurable amount of billable time has already left the model, and the metrics a firm manages by are the first place it shows up.

Where the group landed

Debbie’s answer had two halves, and both are practical.

The first is that metered AI is a real and growing cost that does not belong in an overhead or admin fee. Everyone in the room already knows it is something else — it has credits, it has a unit cost, and that cost is going to rise. Burying it in overhead is a way of not deciding.

The second is that STAR already supports the billing shape this calls for. It does not do value-based billing, and that limitation is real. But it does fixed-fee billing, and if a piece of work has a known value before it starts, that is project-based billing and the price belongs there — at the higher value, set up front. For firms that want the detail, metered AI fees can be imported and allocated.

Then the caution that kept the discussion grounded: do not spend more money allocating the fee than the fee is worth. It does not have to be specific. Pro rata across the practice using it is a legitimate answer. The practical obstacle raised from the floor supports that — getting a credit feed down to an individual client number is genuinely hard, and it is fair to ask whether the precision earns its cost.

Why this is a compelling change rather than an optional one: accounting firms are famously slow to change workflows, but no firm wants to send a client a smaller bill than last year. That pressure is what will move this faster than the usual pace.

A counterpoint on headcount

The host offered a different read on the same facts. AI is a tool. The conclusion a firm draws from a tool that makes people more productive should be to hire more people, not fewer — because each person can now produce more, and the constraint was never the number of hours available, it was what the firm could deliver with them.

The competitive version of that argument is the sharper one. A firm that cuts headcount to cut cost keeps producing roughly what it produced before, more cheaply. A firm that keeps its people and points them at more work produces materially more. Over a few cycles, the second firm is doing things the first one cannot bid on.

Debbie’s refinement is the part worth taking away, because it is more specific than the headcount question. What changes is not the number of people, it is the skill mix. The people at risk of being marginalized are the ones without the analytical ability to work with developers and get more out of these tools. The people who have that ability become expensive — which is a statement about where a firm should be investing, not just about who it should keep.

Why it belongs on a SQL page

Every answer offered in the room ends in a database. Importing metered usage is an integration. Allocating it pro rata is a query. Pricing fixed-fee work at a defensible number requires knowing what the work has historically cost. Measuring whether realization actually recovered requires the reporting layer to be trustworthy in the first place.

Whatever a firm decides about billing, SQL is where the decision gets implemented and where anyone finds out whether it worked. That is the connection between the first fifteen minutes of this meeting and the seventy-five that followed.

What this database is — and what it is not

It is worth being blunt about this before any code, because the name invites the wrong assumption. This is not a data warehouse. There is no cleaning here, no renaming, no business meaning, no zero rows, no flattening, no reporting objects, no opinions of any kind. Nothing in it has been interpreted.

What it is, is the source that the data warehouse reads from — sitting locally on the same server as the warehouse, so the warehouse never reaches across the network and never touches the live STAR database. Every table in it is a byte-for-byte copy of a STAR table’s shape, holding STAR’s own values under STAR’s own column names. If a query returns something surprising, the answer is in STAR, not in a transformation that happened on the way in.

That distinction is what makes the rest of the design simple. Because this layer has no opinions, it can be verified against STAR by direct comparison — the same query, run in both places, must return the same rows. A warehouse cannot be checked that way, because a warehouse is supposed to differ from its source. Keeping the two apart means one of them is always trivially provable.

It is also not STAR-specific, and not warehouse-specific. Any application that needs a fast local subset of STAR can point at this instead: an application server, a reporting server, a departmental tool. A firm can run several of them on one instance, each carrying the tables one consumer needs, versioned and scheduled independently.

One row of configuration: where STAR lives

Start at the configuration, because it is the entire footprint the environment has in this database. One table, constrained to exactly one row:

create table [app].[config] (
	  [id] int not null
	, [enabled] bit not null
	, [debugger] bit not null
	, [star_server_name] sysname not null
	, [star_db_name] sysname not null
	, constraint [app.config!!!id] primary key clustered ([id])
	, constraint [app.config?id=0] check ([id]=(0))
);

The check constraint pinning the identifier to zero is deliberate: this is a settings row, not a table of settings rows, and the constraint says so rather than leaving it to convention.

The row itself:

select * from app.config;

--	id     enabled   debugger   star_server_name   star_db_name
--	-----  --------  ---------  -----------------  ------------
--	0      1         1          STARSERVER         STARDB

star_server_name is a SQL Server linked server, configured once with credentials that can read the STAR database. Where the STAR server physically sits on the network is irrelevant to everything downstream — another box in the server room, another site, another data centre. The linked server absorbs that question, and this database only ever knows the name.

star_db_name is the STAR database on that server. Between the two, retargeting the whole thing at a different STAR environment — test, training, a second firm, a restored backup for a point-in-time investigation — is an update to one row. Nothing is compiled in, and nothing else has to be edited.

The debugger flag controls whether every generated statement is printed before it runs. It is on during development so the SQL can be read, and off in production, where it accounts for most of the difference between the two run times quoted later.

The mirrored tables are STAR’s own shapes

For each STAR table needed, script the structure and keep only the columns the work actually uses. Everything else is left exactly as STAR declares it: same data types, same lengths, same nullability, same column order, same primary key. A small lookup table survives intact:

create table [dbo].[tblClientType] (
	  [ClientTypeID] int not null
	, [ClientType] varchar(25) null
	, [Hide] bit null
	, [TimeStampField] binary(8) not null
	, primary key clustered ([ClientTypeID])
);

A wide table gets narrowed. STAR’s client table carries around a hundred columns; this copy takes the twenty-nine the work depends on, in STAR’s original order:

create table [dbo].[tblClient] (
	  [ClientID] int not null
	, [ClientRef] varchar(10) null
	, [FirstName] varchar(50) null
	, [SearchName] varchar(100) null
	, ...
	, [CreditControllerID] int null
	, [TimeStampField] binary(8) not null
	, [SupervisorID] int null
	, [CountryID] int null
	, primary key clustered ([ClientID])
);

Two things in those scripts are doing all the work, and they are the only two things every mirrored table must have.

The first is the primary key. It is what the generated merge joins on, and — as the delete strategy below depends on entirely — it is what the key space gets partitioned by. No primary key, no sync.

The second is TimeStampField, and it carries the one deliberate difference from STAR. In STAR the column is a timestamp (the older name for rowversion); here it is declared binary(8). That is physically the same eight bytes, but a rowversion column cannot be written to — SQL Server generates its own value on every insert and update. This copy must store the value STAR issued, so the column has to be ordinary binary. Get this wrong and every row silently carries the local server’s counter instead of the source’s, and the incremental logic quietly compares the wrong clock.

Notice too that TimeStampField is not the last column in the client table. It sits where STAR put it, with two columns after it. Column order is preserved because the point is fidelity, not tidiness.

The registry: one row per table, and it is also the run log

Which tables to sync, in what order, and how far each has got, all live in one table. These are its first eight columns for the two examples above:

select	  t.ordinal
	, t.name
	, t.enabled
	, t.incremental
	, t.rowversion
	, t.status
	, t.local_rows
	, t.remote_rows
	from app.dbotables t
	where t.name in ('tblClientType', 'tblClient')
	order by t.ordinal;

--	ordinal   name             enabled   incremental   rowversion           status   local_rows   remote_rows
--	--------  ---------------  --------  ------------  -------------------  -------  -----------  ------------
--	2020      tblClientType    1         1             0x0000000074D66682   ok       10           10
--	2500      tblClient        1         1             0x0000000074D66682   ok       41754        41754

ordinal fixes the sync order, and the values are spaced rather than consecutive — 1, 11, 12, 20, 21, and so on — so a table can be inserted between two others without renumbering anything. enabled and incremental are the two switches: whether to sync this table at all, and whether to sync it by watermark or reload it fully.

rowversion is the high-water mark, and both rows show the same value because a run reads one fence from STAR and stamps every table it loads with it. Zeroing this column is the manual override: it forces the next run to treat that table as a full load, which is what you do after widening the mirror with a column that was not there before.

status is a computed column, and it is the part worth stealing. It compares the two row counts and describes the difference in words, so the registry answers “is this table in step with STAR?” without anyone writing a query:

, [status] as (convert(nvarchar(100),
		case when [local_rows] is not distinct from [remote_rows] then 'ok'
			else concat([remote_rows] - [local_rows], ' row differences')
				+ case when [local_rows] > [remote_rows] then ' (deletes detected)' else '' end
		end))

The remaining columns are history rather than control — the last run’s identifier, its begin and end times and rowversions, rows affected, rows deleted, duration, and any error message. The row that configures a table is also the row that records what happened to it, so “when did this last load, and what did it do?” is a one-row select.

Everything below is reflection

Before the sync logic, the single most important structural fact about this database: there is no per-table code in it anywhere. Not one procedure mentions a client, a job, or a WIP row. Every statement that runs is generated at execution time by reading the catalog views.

One scalar function does that work. It reads sys.columns and sys.indexes for a given object and precomputes every column list a template could ask for — plain, quoted, aliased to source or target, and as comparison predicates — split three ways into all columns, key columns, and non-key columns:

select
		  qname                 = quotename(t.name)
		, t_qname_eq_s_qname    = concat('t.', quotename(t.name), ' = ', 's.', quotename(t.name))
		, t_qname_idf_s_qname   = concat('t.', quotename(t.name), ' is distinct from ', 's.', quotename(t.name))
	from sys.columns t
		left outer join sys.indexes i on t.object_id = i.object_id and i.is_primary_key = 1
			left outer join sys.index_columns ic on i.object_id = ic.object_id
				and i.index_id = ic.index_id and t.column_id = ic.column_id
		left join sys.types ut on t.user_type_id = ut.user_type_id
	where t.object_id = @object_id;

--	those aggregate into the tokens a template asks for, for example:
--		{columns_qname}                       [ClientTypeID], [ClientType], [Hide], [TimeStampField]
--		{pkcolumns_t_qname_eq_s_qname_and}     t.[ClientTypeID] = s.[ClientTypeID]
--		{dcolumns_t_qname_eq_s_qname}          t.[ClientType] = s.[ClientType], t.[Hide] = s.[Hide], …

The same function also substitutes {star_server_qname} and {star_db_qname} from the configuration row, which is how one edit to that row retargets every generated statement in the database.

It handles the awkward types too. Comparing text, ntext, xml or image columns with an equality operator fails outright, so the generator emits a cast to nvarchar(max) or varbinary(max) for exactly those types and leaves every other column alone. That matters here because STAR’s client table stores both the address block and the email addresses as text.

The consequence is the thing to take away: adding a table to this database is scripting its structure and inserting one registry row. There is no sync code to write, and therefore no sync code to get wrong, and no sync code that can drift from the table it feeds. What follows are templates, not programs.

OPENQUERY rather than four-part names

Performance testing settled this one. A four-part linked-server name reads naturally and works fine for a single table, but as soon as the statement joins or filters, you lose visibility into where the work is actually happening — the local optimizer may decide to drag far more across the wire than you intended and filter it locally.

OPENQUERY removes the ambiguity. The query text is handed to the remote server as a string, the remote server plans and executes all of it, and only finished rows travel. That is the difference between filtering at the source and filtering after the fact, and on a table of millions of rows it is the difference between a few rows on the wire and all of them.

Yes, this is dynamic SQL, and it is worth being explicit about why that is safe here rather than waving it away. Every fragment going into the command comes from the local catalog views or from a configuration row you own. Nothing in the path originates from a user, a form, a file, or an external system. The standing objection to dynamic SQL is injection, and injection requires an untrusted input to inject.

The change clock, and the fence around it

STAR’s TimeStampField is a rowversion: a database-wide counter that gets stamped onto any row inserted or updated, anywhere in the database. That makes “what changed since I last looked?” a single range predicate the source server can answer directly — and it is why this approach needs nothing switched on at the STAR end. The columns are already there, in every table, put there by the vendor.

The subtle part is which value to use as the upper bound, and the obvious choice is wrong. @@DBTS returns the last value the database issued — including values issued inside transactions that are still open and may yet roll back. Sync past one of those once and the rows it would have written are skipped permanently, because their rowversions now sit below your watermark. That is a silent, unrecoverable gap, and it is the classic way a watermark sync loses data.

The correct value is min_active_rowversion(), fetched from the STAR server itself:

declare @value binary(8);

execute [STARSERVER].[STARDB].sys.sp_executesql
	  N'set @value = min_active_rowversion();'
	, N'@value binary(8) output'
	, @value output;

--	@value: 0x0000000074E11A03 (1961466371)

The name is genuinely misleading and it is worth saying out loud. “Min” reads like the bottom of a range. What it actually returns is the lowest rowversion still in flight, which makes it the highest value you are allowed to trust — a safe ceiling. Everything below it is committed and settled.

One fence is read per run and applied to every table, and each table pulls the half-open window from its own stored mark up to that fence. Half-open matters: the upper bound is exclusive, so the fence value itself belongs to the next run and no row can be claimed by two windows or by neither.

The incremental load, generated

The merge template is the first statement of the procedure, so what the code becomes can be read before it is generated:

declare @sqlcommand nvarchar(max) = '
merge dbo.{object_qname} with (holdlock) t
	using openquery({star_server_qname}, ''
		select <column-list>
			from {star_db_qname}.dbo.{object_qname}
			<where>
	'') s on {pkcolumns_t_qname_eq_s_qname_and}
	when matched and (t.[TimeStampField] is distinct from s.[TimeStampField])
		then update set {dcolumns_t_qname_eq_s_qname}
	when not matched by target
		then insert ({columns_qname})
			values ({columns_s_qname});
set @rowcount = @@rowcount;';

And this is what it actually becomes for the client type table — generated, not written:

merge dbo.[tblClientType] with (holdlock) t
	using openquery([STARSERVER], '
		select [ClientTypeID], [ClientType], [Hide], cast([TimeStampField] as binary(8)) as [TimeStampField]
			from [STARDB].dbo.[tblClientType]
			where [TimeStampField] >= 0x0000000074D66682 and [TimeStampField] < 0x0000000074E11A03
	') s on t.[ClientTypeID] = s.[ClientTypeID]
	when matched and (t.[TimeStampField] is distinct from s.[TimeStampField])
		then update set t.[ClientType] = s.[ClientType], t.[Hide] = s.[Hide], t.[TimeStampField] = s.[TimeStampField]
	when not matched by target
		then insert ([ClientTypeID], [ClientType], [Hide], [TimeStampField])
			values (s.[ClientTypeID], s.[ClientType], s.[Hide], s.[TimeStampField]);

There is no delete branch here, and that omission is correct rather than careless — an incremental window cannot see a row that no longer exists. Deletes are the next section, and they are handled separately and deliberately. The full-load variant of this same template does carry when not matched by source then delete, because a full load can see the whole source and therefore can be sure.

Index the TimeStampField at the source

This is the single highest-value tuning step available, and it is easy to miss because the column is already there and already works.

Depending on the STAR version and how a particular database has been maintained, TimeStampField is usually not indexed. That is worth checking rather than assuming, and the evidence in the database used for this session is stark: the WIP table carries more than twenty-five nonclustered indexes — on charge type, job, client, staff, date, and a dozen identifiers besides — and not one of them is on TimeStampField. The same is true of the client, client type, and job tables.

Without an index, the range predicate is still correct, but STAR has to scan to satisfy it. With one, it becomes a seek that touches only the rows that changed:

create nonclustered index [dbo.tblWIP!TimeStampField]
	on dbo.tblWIP ([TimeStampField]);

That is what turns the whole approach from workable into lightning fast. The source server reads only what changed, only what changed travels over the wire, and the cost of a sync stops scaling with the size of the table and starts scaling with the size of the change. On a quiet minute, a sync of a seven-million-row table reads almost nothing.

Which is precisely what makes a one-minute cadence realistic. Most warehouse implementations run overnight because a nightly window is the only time they can afford to move the volume they move. This moves the rows that changed, so the window stops mattering — and the choice of how fresh the data should be becomes a policy decision rather than a technical ceiling.

One honest caveat, since it is a change to a vendor database rather than to your own: adding an index to STAR is a source-side modification and belongs in a conversation with whoever owns that server. It is an ordinary, supported, reversible DBA action — unlike enabling a feature such as Change Data Capture — but it is still their database, and it should be their decision.

The delete problem

Here is where most rowversion-based synchronization stops, and it is the reason so many of them fall back to a nightly full reload.

A rowversion is stamped onto a row when it is written. Insert a row, it gets a value. Update a row, it gets a new one. Delete a row and nothing happens at all — there is no row left to carry a marker, no tombstone, and nothing for a watermark query to find. Ask the source “what changed since 0x…74D66682?” and a row deleted five seconds ago is simply not in the answer. It is not that the sync gets it wrong; it is that the question cannot express it.

None of those are acceptable if the target is a one-minute cadence against a database you are only allowed to read. So the problem gets solved instead.

The bucket signature

The insight is that you do not need to know which rows were deleted. You need to know where to look, cheaply enough that looking costs nothing when the answer is nowhere. And a delete always leaves a fingerprint, if you compare the right two numbers.

Partition the primary key space into fixed-width buckets — one hundred thousand keys each — and compute two aggregates per bucket on each side: how many rows it holds, and the sum of the keys in it. Taking the WIP table, because that is where this matters, its 6.7 million rows fall into 68 buckets.

First the local signature, generated from the catalog like everything else:

insert into #local_signatures (bucket, n, s)
	select bucket = convert(bigint, [WIPID]) / @bucket_size
		, n = count_big(*)
		, s = sum(convert(bigint, [WIPID]))
		from dbo.[tblWIP]
		group by convert(bigint, [WIPID]) / @bucket_size;

Then the same aggregate on the STAR side, executed remotely so only 68 rows of summary come back rather than any data:

insert into #remote_signatures (bucket, n, s)
	select s.bucket, s.n, s.s
		from openquery([STARSERVER], '
			select bucket = convert(bigint, [WIPID]) / 100000
				, n = count_big(*)
				, s = sum(convert(bigint, [WIPID]))
				from [STARDB].dbo.[tblWIP]
				where [WIPID] <= 6724240
				group by convert(bigint, [WIPID]) / 100000
		') s;

The where [WIPID] <= 6724240 is the local maximum key, and it is essential. Rows created at the source since the last run have keys above anything held locally; without the cap they would show up as remote-only rows and flag buckets that are perfectly healthy. Capping the comparison at the highest key already seen restricts the question to territory both sides have had a chance to agree on.

Now compare, and note that a null-safe comparison is doing real work — a bucket that exists locally and not remotely returns nulls from the outer join, which must count as a difference rather than as unknown:

insert into @mismatches (bucket)
	select l.bucket
		from #local_signatures l
			left outer join #remote_signatures r on l.bucket = r.bucket
		where l.n is distinct from r.n
			or l.s is distinct from r.s;

On a run where nothing was deleted, that returns no rows, and the entire delete pass is finished: two aggregates, 68 summary rows over the wire, and done. That is the case almost every minute of almost every day, and it is why this is cheap enough to run continuously.

When a bucket does disagree, only that bucket pays. The anti-join runs over one hundred thousand keys, not seven million:

delete t
	from dbo.[tblWIP] t
	where t.[WIPID] between 3400000 and 3499999
		and not exists (
			select *
				from openquery([STARSERVER], '
					select [WIPID]
						from [STARDB].dbo.[tblWIP]
						where [WIPID] between 3400000 and 3499999
				') s
				where t.[WIPID] = s.[WIPID]
		);

One deleted WIP row out of nearly seven million costs two aggregate queries plus one hundred thousand keys examined — and the keys are examined at the source, with only the surviving set coming back. The bucket width is the tuning dial: wider buckets mean fewer summary rows to compare and more keys to fetch when one disagrees; narrower buckets mean the opposite.

Why it cannot be wrong

A signature comparison invites a fair objection: two different sets can share a count and a sum. Why is this proof rather than a good bet?

The answer is in the ordering, and it is the part of the design that deserves the most attention. Reconciliation runs after the incremental load, not before it. By the time the signatures are compared, every insert and every update inside the window has already been applied locally. So the two sides cannot differ by an insert, and they cannot differ by an update.

Which leaves exactly one kind of difference a bucket can still be carrying: a row that exists locally and no longer exists at the source. A delete. And a delete always changes the count — you cannot remove a row without the count falling by one. The count alone is conclusive; the key-sum is a second, independent witness on top of a test that was already decisive.

The one case worth chasing down is a delete followed by an insert that reuses the same key, which would leave the count untouched. That row comes back with a new rowversion, so the incremental pass has already updated it before reconciliation looks. Covered, and covered by the step that runs first.

Then the failure modes are bounded on the safe side. A row created at the source after the fence was read has a key above the local maximum and is excluded from the comparison entirely — the next run collects it. A bucket flagged by anything other than a delete finds nothing to delete and does no harm. There is no path by which a row is removed locally while still present in STAR, because the delete is an anti-join against STAR itself rather than against an inference about STAR.

This is the piece of the design worth arguing with, and it holds up. Rowversion sync is normally sold with the delete problem attached as an asterisk. Here the asterisk is removed, at a cost of two aggregates per table per run.

The whole sequence

Two failure behaviours are worth noting. A table is only marked as current after its load succeeds, so a run that fails halfway leaves the failed table’s mark untouched and the next run simply retries that window. And because the fence is read once, tables loaded in the same run are consistent with each other as at a single point in STAR’s history rather than each as at whenever it happened to be reached.

The interface to everything downstream

A view in this database emits the create-synonym script for the whole mirrored set, in order. Paste the result into any consuming database — the data warehouse, an application database, a reporting database — and every STAR table reads as though it were local, under the same names and the same shapes.

That gives the consumer something more useful than convenience. A query written against the mirror can be run unchanged against the real STAR database, because it refers to the same tables and the same columns. Within the limits of whatever SQL Server version STAR is on, the two are interchangeable — which means any result can be verified against the source by running the identical query in both places. That is the proof mechanism this layer exists to preserve, and it is the reason it holds no opinions.

What it delivers

The full incremental cycle across all forty-eight tables runs in roughly eight seconds with the debug printing turned on, and about two seconds with it off. The first load is longer, because everything has to come across once; every run after that moves only what changed.

That performance changes the nature of the refresh question. Nightly stops being a technical constraint and becomes a policy choice. Every five minutes is available. Every minute is available.

And the reason nightly is still often the right answer belongs beside that. A partner who runs a report at two o’clock and again at four o’clock should get the same number both times, and a warehouse that updates continuously cannot promise that. Live data and stable reporting are different requirements, and different consumers want different ones. What this design gives the firm is the ability to choose per consumer instead of accepting one answer for everybody.

None of it is STAR-specific. Any source database whose tables carry rowversion columns works the same way. And there is no reason to have only one — a firm can run several of these on an instance, each carrying the subset one application or one team needs, versioned and scheduled separately.

What it taught

Separation of concerns is not an abstraction here, it is the load-bearing decision. Because the extraction layer holds no opinions about meaning, everything above it can be rewritten without touching the wire. Because everything above it holds no opinions about the wire, the extraction can be retargeted by editing one row.

Build rather than configure when you can explain the result. The argument for hand-building a replication equivalent is not that SQL Server’s features are bad; it is that this one is small enough to hold in your head, and every line of it can be justified. A replication topology cannot make that claim, and when it misbehaves the troubleshooting starts from a much worse position.

And the limitation is the design. Rowversion cannot see deletes; rather than hiding that behind a nightly full reload, the design names it and answers it with an aggregate cheap enough to run every minute.

A full load every time — but only the rows that actually changed

The virtual layer went to considerable trouble to be incremental, because it is talking to a production server across a network. This layer deliberately does the opposite: every run reads every source function in full. There is no watermark, no window, and no reconciliation pass.

That is not laziness, it is a consequence of the previous decision. The source is a local database on the same instance, so reading all of it is cheap, and being able to say “this warehouse is a pure function of the mirror as it stands right now” is worth more than shaving seconds off a local read. It also means the interpretation layer can be rewritten freely — change a rule, reload, and the result is complete, with no watermark state to reason about.

What the load is careful about is writing. A merge that updates every row every time would rewrite a gigabyte of pages, churn 190 nonclustered indexes, and re-validate 203 foreign keys for rows where nothing happened. So the match clause compares the non-key columns and only fires when something is genuinely different:

merge db.{object_qname} with (holdlock) t
	using dbsource.{object_qname}() s on {pkcolumns_t_qname_eq_s_qname_and}
	when matched and ({dcolumns_t_qname_idf_s_qname_or})
		then update set {dcolumns_t_qname_eq_s_qname}
	when not matched by target
		then insert ({columns_qname})
			values ({columns_s_qname});

{dcolumns_t_qname_idf_s_qname_or} expands to every non-key column compared with is distinct from, chained with OR. On a quiet load the merge reads everything and writes nothing — no page churn, no index maintenance, no foreign-key re-validation, and no transaction log to speak of. The same reflection that generates the virtual database’s statements generates these; this database has no per-table load code either.

Deletes are a separate pass, and the ordering is the whole trick. Every table is merged in dependency order, parent before child, so a child row never arrives before the parent it references. Then the same ordered list is walked backwards, child before parent, applying deletes — because a parent cannot be deleted while children still point at it. Two passes over one list in opposite directions, and the ordinals in the registry are what make it work.

The cleanup layer

This continues the idea introduced back in May: one inline table function per warehouse table, whose job is to present the source data already cleaned. There are seventy-four of them, one for every table in the warehouse, and they are the only place interpretation happens.

Nulls become empty strings where a null adds nothing. Text is standardised to one length so a source field widening does not ripple downstream. Dates that must exist get a floor rather than a null, because there is no such thing as an empty date.

Each function also carries a zero row — a single literal row of defaults with an identifier of zero. Anything that fails to resolve points at it. That one convention is what lets every join in the layers above be an inner join, and it is why the warehouse never negotiates with a null twice.

Verified identifiers, and why they are necessary

Almost no vendor system enforces referential integrity, and this is not a criticism of STAR specifically — most vendor databases are the same. Without foreign keys, nothing prevents a row from carrying an identifier that points at a record which does not exist, and over years of operation, custom imports, and application changes, orphans accumulate quietly.

So the cleanup functions do not take an identifier at face value. Rather than selecting the client type identifier from the client row, they join to the client type table and take the identifier from there. If the join fails, the value becomes zero and lands on the default row. Role identifiers are resolved two hops — partner to staff, manager to staff, supervisor to staff — so the person is the person everywhere downstream. A client whose parent points at itself is caught and zeroed, because a self-referencing parent is an infinite loop waiting for the first recursive query.

This is exactly the work that was too expensive to do before the virtual layer existed. Every one of those verification joins is now a local lookup against a table sitting on the same instance.

SQL Server 2025 earning its place

The assumption is that if you are installing a SQL Server for this — and it can be Express, since the work is mostly moving and shaping rather than computing — you may as well install the current one. Two examples from the client extraction show why.

STAR stores client email addresses as a single free-text field. Rather than carrying a blob forward, the extraction splits it, validates each candidate against a pattern using the new regular expression support, and stores a JSON array of the values that actually are email addresses. Whatever else was in the field does not make it into the warehouse.

The address block is a single field containing carriage returns. It becomes an ordered JSON array of address lines. In both cases the result is queryable with the built-in JSON functions instead of requiring string surgery in every consuming query.

Deconstructing the two widest tables

STAR’s WIP and nominal tables are the two beasts, and they are beasts for the same structural reason. Each holds several genuinely different kinds of record in one table, separated only by a type code, with a column list wide enough to accommodate all of them at once. A timesheet entry lives in a twenty-three column table where most columns mean nothing for time. An invoice, a receipt and a bad debt all live in the same twenty-seven column nominal table.

The warehouse does not reproduce that shape. It takes it apart. Each kind becomes its own table carrying only the columns that kind actually uses, and the type code stops being something you have to remember and becomes the name of the table you are querying.

The structure is a spine plus its kinds. db.wip and db.ar are two-column tables — an identifier and a type — and every kind table’s primary key is also a foreign key back to that spine. That is not decoration. It is what makes the deconstruction provable: every original row appears exactly once in the spine and exactly once in one kind table, and the database enforces it. A row cannot be lost, duplicated across two kinds, or land nowhere.

The spine costs almost nothing to carry — six point seven million rows of two columns is about 111 MB — and it buys the guarantee that the partition is exact and total.

The WHERE clauses that define each kind

Everything about the decoding reduces to a set of predicates, one per kind. This is the whole decoder ring, and it is worth reading as a unit, because the expensive knowledge here is not the SQL — it is knowing which number means what:

--	the WIP family — one table per ChargeTypeID
where t.ChargeTypeID = 24                         -- db.wipTime               timesheet entries
where t.ChargeTypeID = 22                         -- db.wipBilling            the bill that consumes WIP
where t.ChargeTypeID = 23                         -- db.wipBillingWriteoff    the P&L leg of billing
where t.ChargeTypeID between 14 and 18            -- db.wipBillingLine        the bill's summary lines
where t.ChargeTypeID = 25                         -- db.wipExpense            disbursements
where t.ChargeTypeID in (26, 27)                  -- db.wipAdjustment         journals + opening balances
where t.ChargeTypeID in (34, 35, 44)              -- db.wipBudget             planning rows, no money

--	the AR family — one table per NomTypeID
where t.NomTypeID = 1                             -- db.arInvoice             the document
where t.NomTypeID = 10 and t.TaxType is null      -- db.arInvoiceFee          its fee lines
where t.NomTypeID = 10 and t.TaxType is not null  -- db.arInvoiceTax          its tax lines
where t.NomTypeID = 2                             -- db.arReceipt             money landing
where t.NomTypeID = 3                             -- db.arAdjustment          bad debts, finance charges

--	and one table that is a state + event hybrid, split into two
where t.TypeID = 0                                -- db.arInvoiceFeeProvision       the maintained position
where t.TypeID <> 0                               -- db.arInvoiceFeeProvisionEvent  the history behind it

Two of those deserve their parentage spelled out, because the join is doing as much work as the predicate. A summary line is not a free-standing record — it only exists as part of a bill, and the same is true of a write-off. A provision only exists against an invoice fee line. Declaring the parent in the source function is what turns each of those from an assumption into a foreign key:

--	db.wipBillingLine — a summary line only exists as part of a ct-22 bill
	inner join dbo.tblWIP bill on t.BillWIPID = bill.WIPID and bill.ChargeTypeID = 22
	where t.ChargeTypeID between 14 and 18

--	db.arInvoiceFeeProvision — a provision only ever attaches to an invoice FEE line,
--	never to a tax line, a receipt or the document itself. That was a finding, not an
--	assumption: every provision examined at both firms resolved to a fee line.
	inner join dbo.tblNominal fee on t.NomID = fee.NomID
		and fee.NomTypeID = 10 and fee.TaxType is null
	where t.TypeID = 0

And the event logs get the same treatment. STAR’s allocation history is a pair of loose identifiers; the warehouse will not carry an edge unless both ends resolve, which is what allows it to be a real relationship on the other side:

--	db.wipBillingAllocation — the bill-to-work edge, from tblWAH
	from dbo.tblWAH t
	where t.BillWIPID <> t.AlloWIPID
		and exists (select * from dbo.tblWIP bill where bill.WIPID = t.BillWIPID)
		and exists (select * from dbo.tblWIP work where work.WIPID = t.AlloWIPID);

Asserting the assumptions, because firms are configured differently

Everything above is a claim about how STAR behaves. And STAR does not behave one way — it behaves according to a long list of firm-level switches, set at implementation and sometimes changed years later by someone solving an unrelated problem. A decoder ring that is correct at one firm can be quietly wrong at another, and quietly wrong is the worst possible outcome for a warehouse.

So the assumptions are not left as comments. They are executable assertions that run before the load, against the mirror, and stop the load dead if the data has stopped matching the model. There are sixty-six of them: twenty-five on the WIP side and forty-one on AR.

The first one is the clearest example of why this exists:

--	This firm posts manual provisions to tblWIPProvision (the WPNoWIPProvInTblWIP setting);
--	a charge-type-28 row appearing in tblWIP means the firm setting has been flipped and
--	the load model is now wrong.
if exists (select * from dbo.tblWIP t where t.ChargeTypeID = 28)
	throw 50000, 'tblWIP contains ChargeTypeID 28 provision rows (manual provisions belong in tblWIPProvision at this firm) - the load model must be re-ruled.', 0;

--	The partition guard: the modelled charge types cover tblWIP exactly. Anything outside
--	this list is a kind nobody has modelled, and the spine would have nowhere to put it.
if exists (
	select * from dbo.tblWIP t
		where t.ChargeTypeID is null
			or t.ChargeTypeID not in (14, 15, 16, 17, 18, 22, 23, 24, 25, 26, 27, 34, 35, 44)
)
	throw 50000, 'tblWIP contains a ChargeTypeID outside the supported WIP partitions.', 0;

--	Parentage as law: every summary line descends from a real bill. The builder inner-joins
--	the bill, so an orphan would vanish silently rather than fail loudly.
if exists (
	select * from dbo.tblWIP t
		left outer join dbo.tblWIP bill on t.BillWIPID = bill.WIPID
		where t.ChargeTypeID between 14 and 18
			and (nullif(t.BillWIPID, 0) is null or bill.WIPID is null or bill.ChargeTypeID <> 22)
)
	throw 50000, 'db.wipBillingLine.bill_id has a summary line without a ct-22 bill.', 0;

Notice what the third one is protecting against, because it is the subtle case. The source function inner-joins the parent bill, so an orphaned summary line would simply not appear in the warehouse — no error, no failure, just a number that is quietly short. The assertion exists specifically to turn a silent omission into a loud stop.

These are the difference between a warehouse that works at the firm it was built for and one that can be pointed at a different firm with any confidence. Every assertion currently passes with zero rows at both of the firms this has been run against, and each one that fails somewhere new is not a bug — it is a firm whose configuration differs, discovered before anyone reported a wrong number rather than after.

Relationships, and why they are the trust claim

Seventy-four tables, two hundred and three foreign keys, and a nonclustered index on every identifier column — 190 of them, named for the column they cover. Every identifier in the warehouse resolves to a real row, and the database enforces it rather than a convention somebody has to remember.

That is what makes the thing trustworthy, and the reasoning is worth stating plainly: a load either satisfies every declared relationship or it fails. There is no third outcome where the data is subtly wrong and nobody notices for a quarter. The cleanup layer’s verified identifiers and the physical foreign keys are two halves of one guarantee — the first makes the load possible, the second proves it happened.

It also inverts the usual relationship between a warehouse and its source. STAR cannot make this promise about its own data, because it does not declare the relationships. The warehouse can, because it refuses to store anything that would break one.

What deconstruction actually costs: measured

The intuition is that all this must be expensive — more tables, more rows, a spine, and an index on every identifier. The measurement says otherwise, and the reason is the one every data warehousing course teaches under the name star schema: when a wide table is split into narrow typed facts around shared dimensions, the duplication that made it wide goes away.

Take the WIP family. STAR’s table, mirrored, against the tables it deconstructs into:

--	                                                       rows        base data
--	dbo.tblWIP          the mirror, 23 columns          6,724,240      1,693 MB
--
--	db.wip              spine (id, type_id)             6,724,616        111 MB
--	db.wipTime          21 columns                      4,652,703        912 MB
--	db.wipBilling       13 columns                      1,091,579         97 MB
--	db.wipBillingWriteoff  13 columns                     403,346         36 MB
--	db.wipBillingLine   12 columns                        377,833         34 MB
--	db.wipExpense       18 columns                        131,656         24 MB
--	db.wipBudget         8 columns                         64,456          3 MB
--	db.wipAdjustment    12 columns                          2,668          -
--	                                                                 ---------
--	the deconstructed WIP family                                      1,217 MB   = 72%

The AR side is more dramatic still: the nominal table mirrors at 467 MB and deconstructs into 264 MB, or 57 percent, because an invoice, a fee line and a tax line have very little in common and stop pretending to share a column list.

Across the whole database the base data lands at 1,851 MB against the mirror’s 2,631 MB — about seventy percent — while holding forty-eight percent more rows (22.7 million against 15.4 million), because deconstruction turns one wide row into a spine row plus a narrow typed row.

And here is the part worth sitting with. That saving is roughly what the entire index layer costs. The 190 nonclustered indexes occupy about 1,043 MB — so the warehouse gets a fully indexed, fully constrained, fully typed model, and the total still lands within ten percent of a raw mirror that carries a single index and no relationships at all. The deduplication pays for the indexing.

The wide views that come next, and why they are free

The obvious objection to a deconstructed model is that nobody wants to write six joins to see a timesheet entry with its job, client, partner and office. That objection is correct, and the answer is the layer this database is built to carry next: wide views, one per business object, joining a fact to every dimension it can reach, including whatever custom fields a particular firm has added.

Those views cost nothing to store — a view is a definition, not data. The part that surprises people is that they usually cost nothing to read either, because of a property called inlining.

When you query a view, SQL Server does not run the view and then filter the result. It expands the view’s definition into your query and optimises the whole thing as one statement. What that means in practice is that the optimiser can see you only asked for three columns out of forty, and it will simplify away everything that was only needed to produce the other thirty-seven.

That first point is the one that makes wide views viable at all, and it depends entirely on the foreign keys being declared. Without them the optimiser cannot prove the join is redundant, so it has to perform it. This is the concrete, measurable payoff for the 203 relationships: they are not documentation, they are information the query optimiser uses to do less work.

So the firm gets a reporting surface as wide and convenient as it likes, at no storage cost, with performance that degrades only in proportion to what is actually asked for. A dashboard selecting four columns pays for four columns, from a view that offers eighty.

The reports: six pairs, twelve functions

On top of the model sit the six reports that matter most to a practice, each as a matched pair — a summary and the detail it is built from. This carries forward the rule established in the July session: the summary is an aggregate over its own detail, so the two cannot disagree, and every figure opens into the rows behind it.

The detail rows all begin with the same idea: a source column naming which branch of the model the row came from, and the identifiers of the STAR records behind it. A partner’s question about a number becomes a filter on the detail, and the detail answers with identifiers that can be looked up in STAR itself.

By period or by date, which matters more than it sounds

Five of the six take a @mode parameter of Date or Period, along with both a date range and a period range. That is not redundancy — it is two genuinely different questions.

Date mode asks what happened between two calendar dates. Period mode asks what belongs to an accounting period, which is not the same set of transactions: a bill posted on the third of the month may belong to the period that closed on the last day of the previous one. Firms reconcile in period mode and investigate in date mode, and a reporting layer that only offers one of them will disagree with the ledger at exactly the moment anyone is looking.

This is what makes month-end work. When a period closes, a partner can pull WIP reconciliation, WIP aging, billings, collections, AR reconciliation and AR aging for that period, from one model, with every figure computed on the same basis and every one of them openable into its detail. And because the period is closed, those numbers are frozen — running the same report in six months returns what it returned on the day.

WIP aging is the exception: it takes a date and the five aging boundaries, with no period mode, because aging is a question about a point in time rather than about a period’s contents. The aging boundaries are parameters with defaults rather than constants, since 30/60/90/120/150 is a convention and not a law.

Functions, not procedures — and why that is the important choice

All twelve are inline table-valued functions. Not stored procedures. That is deliberate and it is the single decision that makes the reporting layer useful rather than merely correct.

A stored procedure returns a result set to whoever called it. You cannot join to it, filter it from outside, or aggregate it without first landing it in a temporary table and copying the column list by hand. Every consumer ends up writing the same plumbing, and every consumer’s copy of the column list drifts.

A table function is a table. It can be joined, filtered, grouped, and composed with anything else in the database:

--	the firm's own dimensions, applied to a standard report — no plumbing, no temp tables
select	  o.name
	, sl.name
	, wip = sum(r.closing)
	from report.wip_reconciliation('Period', @firstPeriod, @lastPeriod, null, null) r
		inner join db.job j on r.job_id = j.id
		inner join db.client c on j.client_id = c.id
		inner join db.office o on c.office_id = o.id
		inner join db.serviceLine sl on c.serviceLine_id = sl.id
	group by o.name, sl.name;

The reports deliberately return identifiers rather than descriptions — job_id, client_id, staff_id — precisely so the consumer chooses the dimensions. One firm slices by office and service line, another by partner and job type, another by a custom field that exists only in their database. None of that requires a change to the report, because the report was never asked to know about it.

And because these are inline functions, the join above is optimised as one statement. The report is not executed and then joined; its definition is expanded into the query, and the filters and grouping are pushed down into it. Composing a report with a firm’s own dimensions costs nothing extra, which is the same inlining property the wide views depend on.

Where this is not finished, and what that actually costs

This is a work in progress, and it is worth being precise about which part is unfinished, because it is not the part people expect.

The model is solid. The deconstruction, the relationships, the assertions and the load are the product of a long, evidence-driven exercise, and they hold at more than one firm. What is not universal — and never will be — is the arithmetic in the reports.

Every firm’s numbers are shaped by decisions that are genuinely theirs: how progress bills are presented, whether unallocated cash offsets the oldest debt, which charge types count as chargeable, how provisions are treated, where the aging boundaries fall, which of the many firm-level switches were set at implementation and which were changed since. Two firms running the same STAR report can legitimately get different answers, and both can be right.

So getting a firm’s numbers to tie out to their existing reports is a consulting engagement, and there is no honest way to pretend otherwise. What can be said honestly is how long it takes. Because the data is already understood at this level — the kinds, the parentage, the switches, the places where firms usually differ, all of it already mapped and asserted — that engagement is measured in days rather than weeks. The expensive part of this work is the part that has already been done, once, and it does not have to be repeated per firm. What remains is finding the handful of places where a particular firm’s conventions differ, and those are the places the assertions already point at.

Dogfooding before sharing

The warehouse is ready in the sense that it loads, constrains and reports. It is not being handed to the group yet, and the reason is a deliberate one.

It is going to be used in anger first. The next version of Encapsulated’s Financial Dashboard product will be built on this warehouse as its source, which means it has to survive a real product development lifecycle — real requirements, real edge cases, real performance expectations, and a real deadline — before anyone else has to depend on it. Gaps found that way are gaps found by the person who has to fix them.

A second path is already in motion. The host has approached one firm about implementing the warehouse for them pro bono, and is waiting on that client’s approval. The objective there is the hardest possible test: take a firm’s own data and make the warehouse’s numbers tie out to the reports they already run and already trust. Nothing exposes a wrong assumption faster than a firm that knows what the answer should be.

Other firms are welcome on the same terms. If your firm would find it valuable to have this implemented against your data and reconciled to your reporting — and you are in a position to start quickly — reach out to the host directly. There is availability in August 2026, and the value runs both ways: your firm gets the warehouse and the reconciliation, and the warehouse gets tested against conventions it has not met yet.

The intention is that what eventually reaches the group is not a promising prototype but something that has already been through product development and at least one real implementation. Battle-tested, rather than merely finished.

More on this next month

The warehouse got about a third of the meeting and deserves more, so it comes back in September alongside the AI material — including how the decoding work was actually done, how the relationships were established and proven, and how much of the reasoning behind them can be captured well enough that the next person does not have to rediscover it.

The problem: every catalog view stops at the database boundary

SQL Server tells you everything about a database, and nothing about an instance. sys.objects, sys.columns, sys.indexes, sys.sql_modules — each one is scoped to the database you happen to be connected to. That design is perfectly reasonable and completely maddening the moment you have more than a handful of databases.

Every developer with a busy instance has written the same throwaway cursor at least once: loop over sys.databases, build a string, execute it in each database, union the results into a temp table, then finally ask the actual question. It works, you throw it away, and three weeks later you write it again slightly differently.

The fix is to write it once, properly, and leave it there.

One view per catalog view, spanning the whole instance

A schema called system holds a mirror of the catalog, federated across every non-system database. Twenty-six views are generated: objects, columns, computed and identity columns, indexes and index columns, foreign keys and their columns, key constraints, check and default constraints, tables, views, procedures, modules, parameters, dependencies, synonyms, triggers, types, table types, schemas, principals, partitions, files and filegroups.

Each one is a union across every database, with two columns injected at the front so you never lose track of where a row came from:

create or alter view system.[objects] as
	select 12 as database_id, cast('firm_reporting' as sysname) as database_name
			, t.name collate database_default as name, t.object_id, t.schema_id
			, t.type collate database_default as type
			, t.type_desc collate database_default as type_desc
			, t.create_date, t.modify_date, t.is_ms_shipped
		from [firm_reporting].sys.objects t
	union all
	select 13 as database_id, cast('star_virtual' as sysname) as database_name
			, t.name collate database_default as name, t.object_id, t.schema_id
			-- …
		from [star_virtual].sys.objects t
	union all
	-- …one branch per database on the instance. Generated, never typed.

Nobody writes that. A procedure does, from a list of the catalog views worth having, and the way it writes them is where the care went:

--	the column list comes from the catalog itself, not from a hard-coded list
declare @columns nvarchar(max) = (
	select string_agg(cast('t.' + t.name
			+ iif(t.collation_name is not null, ' collate database_default as ' + t.name, '')
		as nvarchar(max)), ', ') within group (order by t.column_id)
		from sys.all_columns t
		where t.object_id = object_id('sys.' + @view_name)
);

if @columns is null begin;
	declare @msg nvarchar(max) = concat('sys.', @view_name, ' resolved no columns - config name wrong or view retired');
	throw 50000, @msg, 0;
end;

--	one branch per database, skipping the four system databases
declare @sqlcommand nvarchar(max) = (
	select string_agg(cast(
				char(9) + 'select ' + cast(t.database_id as nvarchar) + ' as database_id, '
				+ 'cast(' + quotename(t.name, '''') + ' as sysname) as database_name, '
				+ @columns + ' from ' + quotename(t.name) + '.sys.' + @view_name + ' t'
			as nvarchar(max)), ' union all' + char(13) + char(10))
		within group (order by t.name)
		from sys.databases t
		where t.name not in ('master', 'tempdb', 'model', 'msdb')
);

set @sqlcommand = 'create or alter view system.' + quotename(@view_name) + ' as'
	+ char(13) + char(10) + @sqlcommand + ';';
execute sys.sp_executesql @sqlcommand;

Keeping them true: the generator repairs itself

A generated view is a photograph of the instance as it was when the generator ran. Create a database and the views are missing it. Drop one and every view referencing it breaks outright, because a union branch now points at a database that does not exist.

Which means a generator like this has exactly one real failure mode: someone forgets to re-run it. So it does not rely on anyone remembering. The event handler described next checks whether the change it just recorded was a database being created or dropped, and if so, rebuilds the views on the spot:

if @event_type in ('CREATE_DATABASE', 'DROP_DATABASE') begin;
	execute system.build_views;
end;

Three lines, and the maintenance task stops existing. The event that would invalidate the views is the event that regenerates them, so the only way to have stale views is to have no events — which would mean nothing changed.

Two triggers, both scoped to the server

SQL Server offers two families of DDL event. Server-level events cover the instance: databases being created, altered or dropped, logins, linked servers, credentials. Database-level events cover everything inside a database: tables, procedures, views, functions, indexes, schemas, users, constraints, triggers, types.

Capturing both takes two triggers, and here is the part that is genuinely worth knowing, because it is easy to get wrong for years:

--	server-level events — 70 event types
create trigger [sqlserver.triggers.ddl_server_level_events]
	on all server
	for ddl_server_level_events
as begin; set nocount on;
	begin try;
		declare @eventdata xml = eventdata();
		execute sqlserver.ddl_event.new 'server', @eventdata;
	end try begin catch;
		print error_message();
	end catch;
end;

--	database-level events — 158 event types — ALSO on all server
create trigger [sqlserver.triggers.ddl_database_level_events]
	on all server
	with execute as '<logging-principal>'
	for ddl_database_level_events
as begin; set nocount on;
	begin try;
		declare @eventdata xml = eventdata();
		execute sqlserver.ddl_event.new 'database', @eventdata;
	end try begin catch;
		print 'ERROR: ' + error_message();
	end catch;
end;

Look at the second one again. It handles database-level events, and it is created on all server. That combination is legal, and it is the whole trick: one object covers every database on the instance — including databases that do not exist yet.

The alternative is a database-scoped trigger created in each database, which means remembering to create it every time anyone adds a database. That is a task that gets done reliably for about four months. A server-scoped trigger for database-level events cannot be forgotten, because there is nothing to remember.

Both bodies are deliberately trivial: capture the event, hand it to one procedure, done. Every decision lives in a stored procedure that can be read, altered and tested normally — which matters, because a trigger is one of the more awkward objects to iterate on, and a trigger with logic in it is a trigger nobody wants to touch.

The execute as clause is the one real security decision here. The logging insert has to succeed no matter which principal issued the DDL, so the trigger runs as a fixed principal rather than as the caller. In a production install that should be a dedicated account whose only privilege is writing to this log — not a broadly privileged one — because a server-scoped trigger is a piece of code that runs on somebody else’s statement.

And note both bodies catch and print rather than throw. That is correct for a logging trigger: a failure to record a change must never be allowed to roll back the change itself. An audit trail that can block a deployment will be disabled by the first person it inconveniences.

What lands in the log

One procedure receives every event from both triggers. It shreds the event XML into typed columns and keeps the original XML alongside them:

select
		  @event_type     = x.value('EventType[1]', 'nvarchar(100)')
		, @post_time      = x.value('PostTime[1]', 'nvarchar(100)')
		, @spid           = try_cast(x.value('SPID[1]', 'nvarchar(100)') as int)
		, @login_name     = nullif(x.value('LoginName[1]', 'sysname'), '')
		, @user_name      = nullif(x.value('UserName[1]', 'sysname'), '')
		, @database_name  = nullif(x.value('DatabaseName[1]', 'sysname'), '')
		, @schema_name    = nullif(x.value('SchemaName[1]', 'sysname'), '')
		, @object_name    = nullif(x.value('ObjectName[1]', 'sysname'), '')
		, @object_type    = nullif(x.value('ObjectType[1]', 'nvarchar(100)'), '')
		, @command_text   = nullif(x.value('TSQLCommand[1]/CommandText[1]', 'nvarchar(max)'), '')
	from @eventdata.nodes('/EVENT_INSTANCE[1]') _(x);

Keeping the raw XML as well as the shredded columns is the quiet good decision. The shred covers what is common to every event; the XML carries whatever was specific to that one. Anything not extracted today is still recoverable tomorrow, without having lost a year of history to a decision made before you knew what you would need.

Two views split the log by scope — one for database-level events, one for server-level — so the common questions do not start with a filter. On the instance this was demonstrated from, the log holds a few thousand database-level events across twenty-seven event types, and a couple of hundred server-level ones, the overwhelming majority of which are ALTER DATABASE.

And the single most important column is command_text. Not that something changed — the exact statement that changed it.

Six questions this makes answerable

A structural audit trail sounds like compliance paperwork until the first time it saves an afternoon. These are the situations where it stops being abstract.

select t.post_time, t.login_name, t.database_name, t.object_name, t.command_text
	from ddl.database_events t
	where t.event_type in ('DROP_INDEX', 'CREATE_INDEX', 'ALTER_INDEX')
		and t.post_time >= dateadd(day, -7, sysdatetime())
	order by t.post_time desc;

select	  t.database_name
	, t.object_name
	, edits         = count(*)
	, first_touched = min(t.post_time)
	, last_touched  = max(t.post_time)
	from ddl.events_range('2026-07-01', '2026-08-01') t
	where t.login_name = suser_sname()
	group by t.database_name, t.object_name
	order by min(t.post_time);

select t.post_time, t.login_name, t.command_text
	from ddl.database_events t
	where t.database_name = 'firm_reporting'
		and t.object_name  = 'usp_month_end'
	order by t.post_time desc;

--	row 1 is the version you have.
--	row 2 is the version you wish you had.

select t.post_time, t.event_type, t.schema_name, t.object_name
	from ddl.events_range(@deploy_started, @deploy_finished) t
	where t.database_name = 'firm_reporting'
	order by t.post_time;

None of these are exotic. Every one of them is a Tuesday afternoon that used to cost hours.

ddl.events_range, and the question it is really shaped for

The two views are for people. The table function is for code.

create function ddl.events_range (@from_inclusive_date datetime2, @to_exclusive_date datetime2)
returns table as return (
	select t.id, t.level, t.event_type, t.post_time, t.spid
			, t.server_name, t.login_name, t.user_name
			, t.database_name, t.schema_name, t.object_name, t.object_type
			, t.command_text, t.eventdata
		from sqlserver.ddl.events t
		where t.post_time >= @from_inclusive_date
			and t.post_time <  @to_exclusive_date
);

Two details make it more than a convenience wrapper. It references its own table by three-part name, so any database on the instance can call it without a synonym or a linked server — the log becomes a service the whole instance can consume. And the window is half-open: inclusive at the start, exclusive at the end. That is the same discipline the virtual database uses for its rowversion fence, and for the same reason. Consecutive windows tile perfectly. No event is ever claimed by two callers, and none falls between them.

Which raises the obvious question: who needs that? A person investigating a slowdown does not care about tiling. Nobody types a datetime range to find out what they did four seconds ago.

A half-open window with a stored high-water mark is the shape of a very specific pattern — remember where you got to, ask what has happened since, act on it, move the mark. That is not a human workflow. It is a polling loop. Something else on this instance asks “what has changed since I last looked?” continuously, and needs an answer it can trust to be complete and non-overlapping.

Rendering an entire database as files

The last piece scripts a whole database out as a folder-and-file tree — every object as its own file, foldered by object type — returned as XML for something outside SQL Server to write to disk.

It can be run at any moment, against anything on the instance, and it takes its argument as a sql_variant so it accepts either form:

declare @xml xml;

execute smo.generate_scripts 'firm_reporting', @xml output;   -- by name
execute smo.generate_scripts 12, @xml output;                 -- or by database_id

--	inside, the parameter is resolved either way:
declare @base_type sysname = cast(sql_variant_property(@database, 'BaseType') as sysname);
declare @database_id int =	case
								when @base_type like '%int'  then cast(@database as int)
								when @base_type like '%char' then db_id(cast(@database as sysname))
							end;
if @database_id is null begin;
	declare @msg nvarchar(max) = concat(object_schema_name(@@procid), '.', object_name(@@procid),
		': database "', isnull(cast(@database as nvarchar(max)), '{null}'), '" not found.');
	throw 50000, @msg, 0;
end;

Small thing, but it is the difference between a tool you use and a tool you look up the signature for. A caller iterating sys.databases has identifiers; a person has names; neither should have to convert. And when it cannot resolve either, it throws a message that names itself and quotes what it was given.

The output is shaped as nested XML — database, then folder, then file, then content — with the folder derived from the object type, so a stored procedure lands in sql-stored-procedures, a table in user-tables, a view in views. What receives that XML only has to create directories and write text; it needs to understand nothing about SQL Server.

The trick worth stealing: a function that becomes its own dynamic SQL

Reconstructing DDL for every object in a database is a genuinely large query. The version here runs to about five hundred lines: database options, schemas, synonyms, table types, every module, and tables assembled from their columns, defaults, checks, keys, indexes and foreign keys.

Writing five hundred lines of anything inside a string variable is miserable, and everyone who has tried it knows the specific miseries: no syntax checking until run time, no IntelliSense, no formatting, every quote doubled, and error messages that point at a line number in a string nobody can see.

So the query is not a string. It is a real inline table function — smo_template.generate_scripts — compiled, syntax-checked at creation, formatted, with full IntelliSense, and directly runnable while you work on it:

--	while developing, it is just a function. run it and look at the output.
select * from smo_template.generate_scripts(db_id());

--	the last line of its body is a SELECT, with the INSERT commented out:
--	insert into #generate_scripts (database_name, folder_name, file_name, content)
	select
			  database_name = d.name
			, folder_name   = isnull(lower(replace(t.object_type_desc, '_', '-')) + 's', '')
			, file_name     = t.file_name
			, content       = t.content
		from ( … ) t

The procedure then performs a small, precise piece of surgery on that function’s own definition, read straight out of the catalog:

declare @definition nvarchar(max) = object_definition(object_id(
	object_schema_name(@@procid) + '_template' + '.' + object_name(@@procid)));

--	split into lines, then:
--	1. drop the "create function … returns table as return (" line
delete t from @lines t where t.ordinal = (select min(t.ordinal) from @lines t
	where t.line like '%create%function%(%)%returns%table%as%return%(%');

--	2. drop the trailing ");" line
delete t from @lines t where t.ordinal = (select max(t.ordinal) from @lines t
	where t.line like '%);%');

--	3. un-comment the INSERT — the SELECT becomes an INSERT … SELECT
update t set t.line = replace(t.line, '--', '') from @lines t
	where t.ordinal = (select t.ordinal from @lines t
		where t.line like '%--%insert%into%#generate_scripts%(%)%');

--	4. point it at the requested database, and run it
set @sqlcommand = replace(replace(@sqlcommand, '@database_id', @database_id)
	, quotename(db_name()), quotename(@database_name));
execute sys.sp_executesql @sqlcommand;

Notice how the procedure finds its template: it takes its own schema and name from @@procid and appends _template to the schema. The pairing is a convention rather than a hard-coded name, so the two objects can never be renamed apart, and the pattern can be reused for the next generator without inventing anything.

The single commented line is the seam, and it is the elegant part. Commented, the body is a valid function returning a table you can inspect. Uncommented, the identical body is an INSERT that populates the results table. One statement, two lifetimes: a readable, checkable, debuggable object while you are working on it, and dynamic SQL when it needs to run somewhere else.

You never author dynamic SQL. You author a function, and the procedure mechanically converts it. That is the July session’s lesson finishing its arc: describe the thing once, in the place where the tooling can check it.

What it taught

Instrumentation is worth building before you need it. Nothing in this database was urgent when it was written, and every part of it pays off at the exact moment something is wrong and nobody can remember what changed.

Put the logic where you can read it. Trivial triggers calling one procedure keeps a normally-painful object trivial to maintain, and moves every real decision somewhere it can be tested.

A generator should repair what it generates. The view builder being re-run by the very event that would invalidate it means there is no maintenance task left to forget — and no maintenance task is the only kind that never gets skipped.

And a last observation, which is where the next section starts. Everything in this database was built for a human developer: to answer what is on this instance, what changed on it, and what does this code look like. But look at the shape of the answers. A complete, queryable record of every change, addressable by a half-open time window. And the ability to render any database on the instance as plain files, on demand, at any moment.

Those are not conveniences for someone sitting in Management Studio. They are exactly what you would need to hand a database to a collaborator who cannot open Management Studio, has no memory of yesterday, and works only from text files. That collaborator now exists, and it is the subject of the last section.

The premise: an agent is a process, and processes can be instrumented

Claude Code exposes lifecycle hooks. A session starts. Instructions load. A prompt is submitted. A tool is about to run. A tool has run. A tool failed. A sub-agent starts, and stops. The context is compacted. A message is displayed. The session ends. Each of those can call an external program, and the agent waits for it to return.

That last clause is the entire opportunity. A hook is not a notification you receive after the fact; it is a gate the agent stops at. Whatever you do in that moment happens before the model produces its next token, and whatever you print becomes part of what it reads.

So the design question is not “how do I log this?” It is “what should already be true by the time the model starts thinking?”

hook.exe — the whole console application, in one page

Every hook calls the same executable, with the event name as its only argument and the event payload on standard input. The program is about 350 lines including helpers, and it contains no business logic at all. Its job is to capture context, hand it to SQL Server, do whatever SQL Server tells it to do, and say whatever SQL Server tells it to say.

static int Main(string[] args) {
    var hook_name = args?.FirstOrDefault() ?? "";
    var stdin = Console.IsInputRedirected ? Console.In.ReadToEnd().TrimEnd('\r', '\n') : "";

    var xmlhook = new XElement("hook",
        new XAttribute("name", hook_name),
        new XElement("stdin", stdin),
        ProcessHelper.buildProcessElement(),          //  the whole parent chain
        EnvironmentHelper.buildEnvironmentElement()); //  every environment variable

    run(xmlhook);
    return 0;     //  ALWAYS. a hook must never be the reason a session fails.
}

That return 0 is not laziness, it is the most important line in the file. Instrumentation that can break the thing it instruments gets removed by the first person it inconveniences — the same reasoning as the DDL trigger that prints instead of throwing. Every failure path in this program still exits zero.

The body is a loop, and the loop is where SQL Server is in charge:

private static void run(XElement xmlhook) {
    var hook_id = Database.hook(xmlhook);                    //  agent.hook   -> hook_id

    foreach (var _ in Enumerable.Range(1, 200)) {
        var tool_id = Database.tool(hook_id, out var tool_name, out var xmltool, out var continue_on_error);
        if (tool_id == 0 || xmltool is null) break;          //  sql says: nothing to do

        try {
            var xmlresult = invokeTool(tool_name, xmltool);
            if (xmlresult?.Element("stdout")?.Value is string stdout && stdout.Length > 0) {
                Console.WriteLine(stdout);                  //  <-- becomes part of the prompt
            }
            Database.tool_end(tool_id, xmlresult);
        } catch (Exception ex) {
            Database.tool_error(tool_id, new("error", new XAttribute("message", ex.Message ?? "")));
            if (continue_on_error) continue;
            throw;
        }
    }

    Database.hook_end(hook_id);
}

Read it as a conversation. The program says “this happened.” SQL Server says “then run this tool, with this payload.” The program runs it, reports the result, and asks again — until SQL Server has nothing more to ask for. The .NET side never decides whether a tool should run, or which one, or what it should be given.

Even the dispatch is generic. There is no switch statement listing the tools:

var type = typeof(Program).Assembly.GetType($"hook.tools.{tool_name}", throwOnError: false)
    ?? throw new Exception($"Type 'hook.tools.{tool_name}' not found");

var method = type.GetMethod("run", BindingFlags.Static | BindingFlags.Public, [typeof(XElement)])
    ?? throw new Exception($"Method 'run' not found in type 'hook.tools.{tool_name}'");

method.Invoke(null, BindingFlags.DoNotWrapExceptions, null, [xmltool], null);

A tool is a class in a known namespace with a static run that takes XML and returns XML. Registering one is inserting a row. That is the same reflection-over-convention idea the other three databases are built on, arriving here in C# instead of T-SQL.

And the data layer is five stored procedure calls with no SQL text anywhere in the program: agent.hook, agent.tool, agent.tool_end, agent.tool_error, agent.hook_end. In the true spirit of a SQL SIG, .NET does only what only .NET can do — read a pipe, walk a process tree, write a file — and every decision lives where it can be read as SQL.

Every event, from every agent, at every depth

Here is where it gets more interesting than a log table. Claude Code does not run one agent — it spawns sub-agents, and those sub-agents spawn their own, nesting up to five levels deep. Each one is a separate process, each one fires its own hooks, and each one calls this same executable.

So the capture is complete by construction. But a flat log of events from twelve concurrent agents is close to useless unless you can tell which agent did what. That is what the process element is for:

//  walk the parent chain, capturing each process on the way up
for (int depth = 0; pid != 0 && depth < 12; depth++) {
    Process process;
    try { process = Process.GetProcessById(pid); } catch { break; }  //  parent already exited
    if (process.StartTime > childStart) break;                       //  pid recycled: not the real parent

    //  …id, name, path, commandLine, startTime, sessionId, threads, memory…

    childStart = process.StartTime;
    pid = parentPid(process.Handle);
}

Every hook row carries its own ancestry as nested XML, so a captured chain reads hook → claude → claude → explorer — this executable, the sub-agent that called it, the session that spawned that sub-agent, and the shell underneath. The walk is bounded at twelve levels, comfortably more than the five the tool allows, so the whole tree is always recoverable.

Two details in that loop are the kind of thing that separates working from correct. A parent that has already exited ends the walk rather than throwing. And the start-time comparison catches PID recycling — Windows reuses process identifiers, so a parent that appears to have started after its child is not the parent at all, it is a different process wearing a dead one’s number. Getting that wrong would attribute a sub-agent’s work to a stranger.

Capturing another process’s command line takes real effort, because no managed API exposes it. The helper reads it out of the target process’s environment block directly — three memory reads through NtQueryInformationProcess and ReadProcessMemory — which is the difference between knowing “a claude process” and knowing exactly which agent, with which arguments, was running.

What gets stored, and why the timestamps carry the weight

The timestamps are not bookkeeping. They are the only way to reconstruct concurrency after the fact. Twelve agents running in parallel produce one interleaved stream of rows; begin and end times turn that back into twelve timelines, showing which agent was waiting on what, where the time actually went, and which step was the bottleneck. Without them the log tells you what happened but never when relative to what else, which is the only question worth asking about parallel work.

They also measure the instrumentation itself, and this is the number that decides whether any of this is viable. Across roughly a hundred thousand captured hooks, the median cost of recording an event is around thirteen milliseconds — 14.8 ms average before a tool call, 12.4 ms after, 1.0 ms when a sub-agent starts. The one hook that does real work, the prompt submission, averages about 0.6 seconds because it is doing the job described next.

Instrumentation that costs thirteen milliseconds is instrumentation nobody turns off.

The working example: knowing what changed before being asked

This is where all four databases meet, and it is worth following end to end.

The problem it solves is a specific, maddening one. You are working with an agent on a database. You make a change in Management Studio — alter a procedure, add a column, drop an index. The agent has no idea. So either you tell it, every time, or it re-interrogates the database from scratch, or worse, it proceeds confidently on a picture of the schema that is now wrong.

A configuration table maps a tool to the hook it runs on. This one is registered against UserPromptSubmit — so it runs the moment you press enter, before the model sees anything:

--	which databases have changed since the last time this tool looked?
with databases as (
	select t.name as database_name
		from sys.databases t
		where t.database_id > 4 and t.state_desc = 'ONLINE'
			and t.source_database_id is null and has_dbaccess(t.name) = 1
)
, watermarks as (
	select t.database_name, max(t.to_exclusive_date) as from_inclusive_date
		from tool.SqlDatabaseHarvests t
		where t.end_date is not null
		group by t.database_name
)
insert into @databases (database_name, from_inclusive_date)
	select t.database_name, isnull(w.from_inclusive_date, '1900-01-01')
		from databases t
			left outer join watermarks w on t.database_name = w.database_name
		where w.from_inclusive_date is null                    --	never harvested: do it
			or exists (
				select *
					from ddl.events_range(w.from_inclusive_date, @to_exclusive_date) e
					where e.database_name = t.database_name    --	changed since: do it
			);

if not exists (select * from @databases) return;                --	nothing changed: say nothing

There is ddl.events_range, doing exactly what the half-open window was shaped for. The stored watermark is the last harvest’s end time. The question is “which databases had DDL between then and now?” The answer is usually none, and the whole thing costs a few milliseconds.

When something has changed, only the changed databases are scripted — using the generator from the previous section — and queued as XML for the console application:

execute smo.generate_scripts @database_name, @xmldatabase output;

insert into agent.tools (hook_id, name, correlation_id, xmltool, continue_on_error, created_date)
	values (@hook_id, 'SqlDatabaseHarvest', @harvest_id, @xmltool, 1, sysdatetime());

The console application picks that up on its next pass round the loop, writes the tree to disk, prunes files whose objects no longer exist, and reports. The file comparison is the file itself — no hashes, no timestamps to keep in sync — with line endings normalised on both sides so a formatting difference alone never counts as a change and never triggers a rewrite.

Why files, and why that is so much faster

The obvious objection: the agent could just query the database. It has a connection. Why write files at all?

Because of what a file affords that a query does not. A model reading text files is not reading them the way a person does — it is using the same tools a developer uses, and those tools are surgical. It can list a directory and see every object in a database as a filename. It can search across every file for a table name and get back line numbers. And critically, it can diff.

That last one is the whole game. If a procedure changed, the agent does not need to read the procedure. It needs the six lines that are different. Fetching a 400-line module from the catalog gives you 400 lines to read, understand, and hold in context; a diff against the previous file gives you six, with the surrounding lines for context, and nothing else. On a database with thousands of objects, that is the difference between an agent that spends its first several thousand tokens rebuilding a picture it had yesterday, and one that starts from “these two procedures changed, here are the exact lines.”

None of that requires the agent to be clever. It requires the files to be current — which is what the hook guarantees, on every single prompt, without anyone asking.

Speaking into the prompt — and knowing when to say nothing

Whatever the tool writes to standard output is captured by the hook and becomes part of the context the model reads, before it responds. This is a real message from a real session:

UserPromptSubmit hook success: harvest [cloud.amine.mail.storage]: 2 written, 0 pruned, 37 unchanged,
under D:\system\ai\sql-server-instances\Amine\MSSQLSERVER\cloud.amine.mail.storage
  written  sql-stored-procedures\[folder].[new].sql
  written  sql-stored-procedures\[recipient].[new].sql

The developer typed a question about something else entirely. Before the model read a word of it, it already knew two procedures had been added to a database since the last exchange, and exactly where to look. No one mentioned it. No one had to.

And now the detail that matters most in this entire section — the one that took a comment in the source to explain, because it is a design decision rather than an implementation:

//	silence when nothing changed: on UserPromptSubmit the stdout becomes Claude's context, so an
//	unchanged run carries counts but no text rather than repeating itself on every prompt.
if (written.Count == 0 && pruned.Count == 0) { return xmlresult; }

When nothing changed, the tool still records that it ran, still updates its watermark, and says nothing at all. No “no changes detected.” No reassuring status line. Nothing.

This is the difference between a tool that helps and a tool that becomes noise. A message on every prompt is a message that gets ignored by the third one and actively costs context on every one after that. A tool that speaks only when it has something to say is a tool whose output is always worth reading — and over a long session, that discipline is worth more than the information itself.

Why keep what the agents learn

Every one of those hundred thousand rows is something an agent worked out. What it was asked. What it read. What it tried. What came back. What it concluded. Under normal circumstances all of that evaporates when the session closes, and the next session starts from nothing.

That is an enormous amount of thrown-away work, and it is the reason to store it rather than merely log it.

The clearest demonstration is the STAR knowledge base. Rather than asking one agent to understand a practice-management system with roughly four thousand stored procedures and a thousand tables — which no single context window can hold — a fleet of sub-agents was launched across it in waves. They read every line of every procedure, every table definition, every view and every function, and wrote down only what they could prove, tying each finding back to the modelling work already done for the warehouse.

That corpus now runs to more than fifteen hundred documented markdown files — around eleven hundred and fifty describing tables, two hundred and fifty describing procedures, and the rest covering domains, foundations and cross-cutting behaviour. Each is evidence with a citation, not an opinion, and it exists because the work of the agents that produced it was captured rather than discarded.

The idea came from a question asked in another special interest group: is there something a firm could hand an AI so that it understands STAR well enough to give genuinely useful answers? This is one answer, still very much in progress, and the intention is to standardise it and share it once it is worth sharing.

It is also the plainest available explanation of retrieval-augmented generation. A model can only answer from what it has been given — ask it something it was never told and it cannot answer, however you phrase the question. Retrieval is the step that fetches the relevant facts from your own systems and attaches them to the question before the model ever sees it. That is the whole idea, and everything above is machinery for making sure the right facts are there to fetch.

The principle underneath all of it

It is worth naming the thing this section is actually about, because it generalises well beyond SQL Server.

A language model is extraordinary at problems that cannot be pre-programmed, and unreliable at problems that can. Ask it to reason about an unfamiliar schema and it will do something genuinely impressive. Ask it to perform the same mechanical sequence for the hundredth time and it will do it a hundred slightly different ways, get it wrong twice, correct itself, and charge you for the whole journey. That is not a flaw to be prompted away. It is what non-determinism means.

Which points at a clear division of labour. Anything you can describe precisely, you should build. Anything you cannot, leave to the model. Every repeated pattern you notice in how you work with an agent — every time you find yourself explaining the same thing, every time it rediscovers something it knew yesterday — is a deterministic process wearing a probabilistic costume, and it can be lifted out and written properly, once.

The payoff is not only reliability. Deterministic work done outside the model costs no tokens, takes no context, and cannot be got wrong on the third attempt. It runs in thirteen milliseconds and it runs the same way every time.

There is a pleasing recursion in the method, too: the model is very good at writing these tools. It just cannot see that they are needed, because it does not experience the repetition — every session is its first. Noticing the pattern is the architect’s job. Building it can be delegated to the thing that will eventually use it, without it ever knowing that is what it is doing.

And the recurring theme through all four databases, stated once more because this is where it lands hardest: the best tools are the ones that run when they are needed, so that nobody has to remember them. The mirror syncs on a schedule. The warehouse asserts its assumptions before it loads. The federated views rebuild themselves when a database appears. The harvest runs on every prompt. None of them has an operator. None of them is on anyone’s checklist. Each one tells you what you need to know at the moment you need it — and when there is nothing to say, it stays quiet.

Each thing made the next thing possible

Read the four databases in order and none of them is remarkable on its own. Read them as a sequence and the shape is clear: each one exists because the one before it made it affordable.

Nobody designed that chain up front. Each link was built to solve the irritation immediately in front of it, and each one turned out to be the precondition for the next. That is worth noticing as a working method in its own right: solve the thing that is actually annoying you, build it properly enough to keep, and the compounding takes care of itself.

Next month

September is a full session on AI and SQL Server, and it starts where this one stops.

There are now a hundred thousand rows in that database describing, in detail, everything a fleet of agents has ever done — every prompt, every file read, every tool result, every conclusion, with timestamps precise enough to reconstruct twelve of them working at once. It is all text. It is all queryable. And it is all sitting in a SQL Server 2025 instance, which happens to have gained a native vector type.

Which raises a question worth sitting with until September: what changes when an agent can search its own history by meaning rather than by keyword — and what changes when the database it is querying is also the database it is remembering with?

That is the meeting.