Gate PRs with schema validation and governance

A column named Ssn shipped to production with no data-classification tag on it. Nobody caught it in review — it was one line in a hundred-line table file, and the eyes that were supposed to catch it were tired. The audit caught it instead. That’s the expensive way to find out.

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith. Last time we read the fire before the quench with the pre-flight pair. Today we go one gate earlier — back to the pull request, before a byte reaches a database at all. We’re gonna make the schema files themselves refuse to let a bad change through: structural validation on every PR, and a governance contract that fails the build when someone forgets to classify a column. No database, no credentials, seconds to run.

The first gate: structural validation

Every package SchemaSmith generates ships a set of validation schemas in .json-schemas/ — one per content type, per platform, written straight from the live domain types. products.sqlserver.schema, templates.sqlserver.schema, tables.sqlserver.schema. They know exactly what a valid Product.json, Template.json, or table file looks like on this engine.

Point a JSON Schema validator at them and every table file gets checked. Here’s the CI step — the GrantBirki/json-yaml-validate action, no database anywhere in sight:

- name: Validate tables
  uses: GrantBirki/json-yaml-validate@v3.3.0
  with:
    ajv_strict_mode: "false"
    json_schema: sqlserver/Package/.json-schemas/tables.sqlserver.schema
    files: sqlserver/Package/Templates/**/Tables/**/*.json

Clean package, both tables pass. Now fat-finger a table — drop the DataType off a column — and open the PR. The gate fails:

must have required property 'DataType'

No database. No deploy. A structural typo caught in seconds, right on the pull request, before anyone downstream ever sees it. That’s the first gate. It’s free, and it’s the easy half.

Governance you own: the Extensions contract

Here’s the part most teams miss. Extensions is SchemaSmith’s open metadata bag — you hang custom properties on tables, columns, indexes, whatever you need. The generated schema deliberately puts no shape on it. That’s the point: it’s yours.

Which means you can make it a contract. Hand-edit tables.sqlserver.schema and add a fragment under properties.Extensions that says every table must declare an owning team, from a list you approve:

"Extensions": {
  "type": "object",
  "required": ["OwningTeam"],
  "properties": {
    "OwningTeam": { "type": "string", "enum": ["Identity", "Billing", "Compliance", "Platform"] }
  }
}

Add Extensions to the schema’s top-level required, and now a table with no owning team can’t merge:

must have required property 'Extensions'

Name a team that isn’t on the list — "Marketing" — and it’s just as dead:

/Extensions/OwningTeam must be equal to one of the allowed values

No new tooling. No database. The same validation step that catches your typos now enforces your governance, because it validates the whole schema file — fragment and all.

No column ships unclassified

Same move, one level down. Under properties.Columns.items.properties.Extensions, require every column to carry a data classification:

"Extensions": {
  "type": "object",
  "required": ["DataClassification"],
  "properties": {
    "DataClassification": { "type": "string", "enum": ["Public", "Internal", "Confidential", "PII", "Financial"] }
  }
}

Now go back to that Ssn column — the one that shipped unclassified. On a PR, it never ships:

/Columns/1 must have required property 'Extensions'

Classify it as something that isn’t on the approved list and the gate holds just the same:

/Columns/1/Extensions/DataClassification must be equal to one of the allowed values

That’s the whole game. Your reviewers stop squinting at hundred-line table files hoping to spot the one unclassified column. The gate spots it. Every time, on every PR, before the metal ever gets hot.

Check yourself: You add the column-level DataClassification fragment to tables.sqlserver.schema, and it enforces on every PR. Then you re-extract the package with SchemaTongs. Is your column-level rule still there?

Yes. Regeneration (--WriteSchemasOnly or any SchemaTongs extraction) preserves your Extensions fragments at every level — table, column, and deeper (indexes, foreign keys). Your column-level DataClassification rule comes back merged into the fresh schema, still enforcing (SchemaSmith #320). You set governance once; it rides through every regeneration, and you never re-apply it by hand.

The contract through regeneration

Say more about that, because it matters. Your .json-schemas aren’t frozen — you regenerate them whenever the model changes:

schematongs --WriteSchemasOnly

Your Extensions fragments survive that round-trip. Both the table-level OwningTeam rule and the column-level DataClassification rule come back merged into the fresh schema, still enforcing — regeneration rebuilds the generated shell around them without flattening what you authored (#320). So governance you set once, you keep: through --WriteSchemasOnly, through a full re-extraction, at every component level.

Keep the two regeneration moves straight, too: --WriteSchemasOnly only refreshes the editor .json-schemas from the model, the governance fragments included — it never touches a table’s data. A full schematongs table re-extraction is the bigger move, reconciling an authored variant set against whatever’s actually live on the source through gate-aware folding, covered in Module 6.

Wire it into CI

One workflow, one matrix, one entry per content type per package:

strategy:
  matrix:
    include:
      - { schema: sqlserver/Package/.json-schemas/products.sqlserver.schema,  files: sqlserver/Package/Product.json }
      - { schema: sqlserver/Package/.json-schemas/templates.sqlserver.schema, files: sqlserver/Package/Templates/*/Template.json }
      - { schema: sqlserver/Package/.json-schemas/tables.sqlserver.schema,    files: "sqlserver/Package/Templates/**/Tables/**/*.json" }
      # ...postgres and mysql entries, same shape

Every entry names a schema and a glob; the action validates them on each pull request and comments right on the diff when one fails. Add a new package or a new content type, add a line. The lab ships a complete copy-ready workflow, and the SchemaSmith repo runs this exact pattern at production scale in .github/workflows/validate-demo-schemas.yml — all four engines, every PR, no database containers anywhere.

No guesswork. No tired eyes. Just a gate that holds.

Per-engine notes

The workflow is identical on all four engines. What changes is only the native spelling — quoting, types, the schema file’s platform infix:

	SQL Server	PostgreSQL	MySQL
Schema file infix	`.sqlserver.schema`	`.postgresql.schema`	`.mysql.schema`
Identifier form	`dbo.` schema, `NVARCHAR(256)`	lowercase `public.`, `varchar(256)`	backtick-quoted, `VARCHAR(256)`
Table filename	`dbo.Customer.json`	`public.customer.json`	`Customer.json`
Governance fragment	identical	identical	identical

The switch names, the schema filenames, the CI action, the exit behavior — same everywhere. Write the governance once, carry it to every fire.

Structural validation catches the typo. The Extensions contract catches the thing a typo-checker never could — the column that’s shaped fine but governed wrong. Both run on the pull request. Both need no database. And when either one says no, the change never leaves the forge.

There’s one more no-database gate in this family, and it lives in Module 6. JSON Schema here validates each file’s shape; --Validate validates the package’s meaning across objects — a foreign key pointing at a table nobody included, an ungated duplicate column, a token nobody defined. Shape and meaning, both caught cold, both before a single connection opens.

Guarding a schema and not sure where the gate should sit? Email me at forgebarrett@schemasmith.com — I read every one.

More’s coming from the forge — packaging a patch down to only the objects that changed, and carrying it safely to one target at a time.

Until then, may every rule you carve into the schema stand guard at the gate, and nothing you didn’t mean to ship ever slip past it.

— Forge

Mark this lesson complete