Course 2 · Module 6 — Custom properties: teach the tools your metadata

Your schema knows the shape of your data, but not what it means to your team. Which columns are PII? Who owns this table? What’s the retention policy? That knowledge lives in a spreadsheet, a wiki page, or someone’s head — somewhere outside the schema, drifting out of date the moment a column is added. You’ve spent this whole course pulling truth into the package and out of the wiki. Your own metadata is the last piece still on the outside. Let’s bring it in.

Your metadata, on the object

Every schema component — table, column, index, foreign key, check constraint — carries an open Extensions bag. Put whatever you want in it:

{
  "Schema": "dbo",
  "Name": "Customer",
  "Extensions": { "OwningTeam": "Identity" },
  "Columns": [
    { "Name": "CustomerId", "DataType": "INT" },
    { "Name": "Email", "DataType": "NVARCHAR(256)", "Extensions": { "Classification": "PII" } },
    { "Name": "Ssn", "DataType": "CHAR(11)", "Extensions": { "Classification": "PII" } },
    { "Name": "DisplayName", "DataType": "NVARCHAR(128)", "Extensions": { "Classification": "Internal" } }
  ]
}

OwningTeam on the table, Classification on the columns — your governance metadata, declared right where the object is defined, versioned in the same package, surviving extraction (SchemaTongs preserves Extensions on every round-trip). It’s an open JSON bag: strings, numbers, nested objects, arrays — whatever you track. These are custom properties.

Declaring metadata is half the story. The other half is making the tools act on it. There are two ways.

Path one: metadata as a token

Anything in a component’s Extensions becomes a token in that component’s own expression fields. Anything in the table’s Extensions is available to its child components as a {{Table.Name}} token. So an index can gate itself on the table’s metadata:

{
  "Name": "[IX_Customer_Email]",
  "IndexColumns": "[Email]",
  "ShouldApplyExpression": "'{{Table.OwningTeam}}' = 'Identity'"
}

{{Table.OwningTeam}} resolves from the parent table’s Extensions, in any of the expression fields you met across this course — ShouldApplyExpression, Default, CheckExpression, FilterExpression. (Nested values flatten with dots — {{Retention.Policy}}; arrays join with commas; names are case-insensitive.) The metadata you declared once drives the expression, with no value copied into the script.

Path two: read the whole model with `{{TableSchema}}`

Remember {{TableSchema}} from Module 4 — the system token that hands a script your entire table model as JSON? Here’s the payoff: that JSON includes your Extensions. So a single deploy-time script can read every table’s and column’s metadata and do something with it — generate audit triggers for tables tagged for auditing, apply masking by Classification, or build a catalog.

That’s the lab. A DataCatalog table, and an [ALWAYS] after-script that reads {{TableSchema}}, walks every table and column, and records each column’s Classification plus its table’s OwningTeam — for every table carrying an OwningTeam tag:

-- SQL Server (the heart of it)
INSERT INTO [dbo].[DataCatalog] ([TableName], [ColumnName], [Classification], [OwningTeam])
SELECT t.[TableName], c.[ColumnName], c.[Classification], t.[OwningTeam]
  FROM OPENJSON(N'{{TableSchema}}')
       WITH ([TableName] NVARCHAR(256) '$.Name',
             [OwningTeam] NVARCHAR(64) '$.Extensions.OwningTeam',
             [Columns] NVARCHAR(MAX) '$.Columns' AS JSON) t
 CROSS APPLY OPENJSON(t.[Columns])
       WITH ([ColumnName] NVARCHAR(256) '$.Name',
             [Classification] NVARCHAR(64) '$.Extensions.Classification') c
 WHERE t.[OwningTeam] IS NOT NULL;

Note '$.Extensions.OwningTeam' and '$.Extensions.Classification' — the script is reading the exact metadata keys you put in your JSON. Deploy it:

schemaquench --ConfigFile:deploy.settings.json

Then read the catalog:

TableName | ColumnName  | Classification | OwningTeam
Customer  | CustomerId  | (none)         | Identity
Customer  | DisplayName | Internal       | Identity
Customer  | Email       | PII            | Identity
Customer  | Ssn         | PII            | Identity

There it is — the governance metadata you declared in JSON, now a live, queryable data dictionary. DataCatalog itself isn’t in the list: it carries no OwningTeam tag, so the walk skips it. The catalog holds exactly the tables you marked as owned.

The aha: it maintains itself

Because the script rebuilds the catalog from the model on every deploy, the catalog can never drift from the schema. Change a column’s tag — say DisplayName from Internal to Public — and re-quench:

Customer | DisplayName | Public | Identity

You didn’t touch the catalog table. You changed the metadata on the column, and the next ordinary deploy re-derived the catalog from it. Add a column with a Classification, tag a new table with an OwningTeam — each shows up on the next quench. The dictionary maintains itself because it’s computed from the one source of truth: your declared model. That’s the whole arc of this course in one move — the thing that used to live in a drifting spreadsheet now lives in the package and updates itself.

Per-engine notes

The Extensions mechanism and {{TableSchema}} are identical on all three engines — same metadata, same JSON, same keys ($.Name, $.Extensions.Classification). Only the JSON-walk SQL differs:

	SQL Server	PostgreSQL	MySQL
JSON walk	`OPENJSON` + `CROSS APPLY`	`jsonb_array_elements`	`JSON_TABLE` + `NESTED PATH`
Object names in the model	bare (`Customer`)	folded lowercase (`customer`)	backticked (`Customer`) — strip with `REPLACE`

The metadata you author and the keys you read are the same everywhere; the only per-engine work is the dialect’s way of shredding JSON.

Check yourself: How do you attach your own metadata to a schema object and have the tools act on it?

Define a custom property in the object’s Extensions bag (e.g., "Classification": "PII" on a column, "OwningTeam": "Identity" on a table), then reference it where a tool consumes it — as a {{PropName}} / {{Table.PropName}} token in that component’s expression field, or by reading the whole model (Extensions included) through the {{TableSchema}} system token in a deploy-time script.

Think of custom properties as the maker’s marks you stamp into your own work — not the standard dimensions every part has, but the notes that say what this piece is for, who forged it, how it’s meant to be handled. SchemaSmith carries those marks with the metal and hands them back to any script that asks. Declare what your data means, once, on the object itself — and let the tools read your intent instead of guessing it.

That closes Course 2. You came in with the core declare-quench-converge loop and you’re leaving with the deep kit: products that draw boundaries, templates that fan out, conditional deployment, script tokens, data delivery, and now your own metadata driving the tools. Want the deeper detail on this last one? The custom-properties reference covers every supported component, the token-scope rules, and validation; run the lab on all three engines to build the catalog yourself.

Got metadata you’re trying to pull out of a spreadsheet and into your schema — classification, ownership, retention, anything? Email me at forgebarrett@schemasmith.com — I read every one.

Until then, may every mark you set in the metal mean what you meant, and the tools always read it true.

— Forge

Mark this lesson complete

Course 2 · Module 6 — Custom properties: teach the tools your metadata

Your metadata, on the object

Path one: metadata as a token

Path two: read the whole model with {{TableSchema}}

The aha: it maintains itself

Per-engine notes

Path two: read the whole model with `{{TableSchema}}`