Tables are generated, not written

A new table does not begin with CREATE TABLE. It begins with sstp_Database_CreateTable, which composes the statement, adds the identity and audit columns, attaches their defaults and creates the unique index. sstp_Database_CreateView then produces the matching secured view, and sstp_Database_CreateTriggers has its own family of insert, update and delete generators alongside it.

Call the procedure and a table arrives with its audit columns, its view and its triggers already in place. Skip it and you get a table that has none of them, is invisible to the tooling built on top, and drifts from every other table in the system on its first day.

This is the difference between a convention that is documented and one that is structural. Nobody has to remember to add CreatedBy to a new table, because nobody adds columns to a new table at all — the generator does, the same way, 1,113 times. See the schema shape this produces →

The database records its own history

strg_Database_ChangeLog is a trigger on the database itself rather than on any table. It is enabled, and it fires on table, view, function, procedure and trigger DDL events — which is to say, on every structural change anyone makes.

It reads SQL Server's EVENTDATA() and writes the complete text of the statement into stbl_Database_Versions, numbering each object's revisions in sequence. That table now holds 53,117 rows across 11,043 distinct objects, spanning 2007-12-18 to 2026-07-26 without a gap. More objects appear there than currently exist, because deleted ones keep their history.

That second table is why the schema is queryable as ordinary data rather than through system views, and it is the foundation the platform's generated documentation is built on.

The detail worth pausing on is what happens to an object whose name does not fit the convention. It is not rejected. The trigger reaches a prefix test, finds no match, and jumps to its exit label — the change succeeds and simply goes unrecorded. Naming your object correctly is not a rule enforced against you; it is how you opt in to having a history. Separately, the trigger checks that your login may perform DDL at all before it records anything. How the naming convention organises the procedure layer →

Two views per table, and why 2,283 is not a typo

2,283 views over 1,113 tables looks like duplication until you see where they come from. sstp_Database_CreateView derives a view name from a table name by substitution: tbl becomes tbv, or tbx. The two are not the same view twice. They read from different permission sources — the tbv_ form through sviw_System_MyPermissionsCurrentDomain, the tbx_ form through sviw_System_MyPermissions — so one is scoped to the tenant you are working in and the other is not.

The generated view also supports per-table row filters, and a table can inherit its permissions from a predecessor table by walking the foreign keys between them. Filtering is therefore configuration attached to a table, not logic written into each query.

The honest figure is the interesting one. 646 of the 918 table views still contain the exact permission reference the generator wrote. The remaining 272 were edited by hand, because the default did not fit that table. Generation gives you a floor and a starting point, not a guarantee that nobody ever needed something else.

What these views enforce, and the layered permission model behind them, is a subject in its own right. The permission model these views enforce, in full →

HTML assembled in T-SQL

The pattern that surprises people most is that a good deal of this application's markup is composed in the database, by 28 afnc_HTML_ functions and the procedures that call them.

At its smallest the pattern is completely unmysterious. afnc_HTML_TreeViewSPAN takes a width, and if that width exceeds 1000 it emits 100% instead of a pixel value, then returns an opening <SPAN> carrying its inline style. A responsive decision, made where the data is.

The argument for working this way is that markup is assembled next to the query that produced its content, in one round trip, with the filtering already applied. An element the current user has no business seeing is not hidden after the fact — it is never built.

Work with the HTML-in-SQL pattern →

What the framework does not do

Three limits are worth naming, because a reader who goes looking will find them anyway.

The generated view selects every column rather than a named list, so adding or renaming a column leaves dependent views describing the old shape until they are refreshed. sstp_Database_Maintenance_RefreshAllViews exists for precisely that reason, which tells you how routinely it comes up.

Four of the 1,113 tables carry none of the convention — no generated key, no audit columns, no history. They are not application tables, and they are a reminder that the guarantees on this page hold for everything created through the framework, not for everything that exists.

And stbl_Database_Permissions holds two rows. Two logins in the entire system may change the schema at all. That is a deliberate constraint on a database where structural change is recorded permanently and views are generated from tables, and it is also a real dependency on a very small number of people. Both things are true.