Schema Atlas

One human merge. Staging and prod on Cloud SQL are migrated, the schema map is re-verified and packaged, and the code that broke is fixed and merged across 500 tenants, with Postgres as the judge.

Comment

Inspiration

Every agent that touches a database depends on one document nobody maintains: what the production schema looks like right now. A migration renames a column, the database is right, and the doc, the agents that read it, and the code written against it are quietly wrong until someone finds out in production. The MIT base project, okf-pg-tenant, could describe a multi-tenant Postgres, but it left its verified field empty because nothing verified anything. That empty field is why this exists.

What it does

A migration renames payments.captured_at to funds_captured_at. The database is right. The schema doc every agent reads is not, and neither is the job that still queries the old name. Someone finds out in production.

Schema Atlas keeps the database, the published knowledge about it, and the code that queries it in step, with no human after the merge:

  1. A human merges a migration.
  2. CI applies it to a throwaway Postgres, then to Cloud SQL staging (atlas_staging: migrate, verify, check-queries), then to Cloud SQL prod, re-reads pg_catalog, diffs the result against the published map, and publishes the map as a package versioned by its hash.
  3. A GitLab Duo flow, running as its own service account, commits the new verified map and opens an issue saying what changed and which code reads the old name.
  4. If the change is a rename and the only hits are SQL constants in application code, the flow opens a fix MR.
  5. CI runs check-queries: Postgres plans every SQL constant against every tenant schema. Postgres is the judge, not the agent.
  6. A merge policy job on main checks the MR (steward's branch, steward's commits, only examples/ changed, check-queries passed on that exact sha) and merges it. The issue closes.
  7. Staging and prod re-check the merged main, and a release named for the map summarizes the run and links its package.

A nightly schedule re-reads prod. A hand edit nobody merged shows up as drift, and the steward reports it to a person instead of "fixing" it.

The map is served to humans as a site and to agents over MCP, from the same JSON, with its hash and verification time on every answer.

Who it's for

Teams running a multi-tenant Postgres (schema per tenant) whose services, report jobs and AI agents query the database directly. For them, one renamed column means a broken job, and one hand edit in prod means one tenant quietly differs from the rest.

Before vs after

Before (manual, fragmented) After (Schema Atlas on GitLab)
Schema docs are edited by hand and go stale within weeks Every merge re-verifies the map against Cloud SQL prod and publishes it with a hash
A renamed column breaks a job; someone finds out in production The broken query is found, fixed, proven by Postgres on all 500 tenants, and merged in under 8 minutes
A hand edit to one tenant in prod goes unnoticed The nightly check catches it and opens an issue for a person
Agents guess at the schema from old docs or code Agents ask the MCP server and get an answer with the map hash and verification time
A person must review and merge every follow-up fix A merge policy on main merges only steward fixes that pass, and the merge commit says why

Proof: the hands-off run (2026-10-23, UTC)

Recorded on 2026-10-23, UTC, on 500 tenant schemas. Every link is public.

Time What happened Who Evidence
11:37:45 Human merges migration 0008 (rename orders.subtotal_cents → order_subtotal_cents in every tenant) person !12
11:38–11:39 Tests, SAST, secret detection; throwaway Postgres: migrate from zero + verify CI pipeline 2980901739
11:41:38 Staging (Cloud SQL, database atlas_staging): migrated, verified, check-queries: 2 queries x 500 tenants: 500 failed in 0.7s. Expected: a consumer still reads the old name. Advisory, so prod still runs CI staging job
11:42:01 Prod (Cloud SQL, database atlas): migrated, pg_catalog re-read, diffed with the published map: breaking, rename suspected. Map packaged by its hash CI prod job, package job
11:43:20 Steward flow commits the new verified map Duo flow 5d30dfd
11:43:42 Steward opens an issue: what changed, which code reads the old name Duo flow #9
11:44:03 Steward opens a one-word fix MR for examples/revenue_report.py Duo flow !13, commit 4a5f961
11:44–11:45 SAST, secret detection, tests, check-queries on the fix: 0 failed on 500 tenants CI pipeline 2980924446, MR pipeline 2980924804
11:45:34 Merge policy checks the MR and merges that exact commit; Closes #9 closes the issue CI policy policy job, merge commit fc00a6f
11:47:11 Staging re-checks merged main: 2 queries x 500 tenants: 0 failed in 0.7s CI staging job
11:47:35 Prod re-verifies: map identical to the published one CI prod job
11:48:10 Summary: a release for map acdf7563b34c, linking its package, staging and prod jobs CI release map-acdf7563b34c

Both are GitLab environments: staging and production. The staging job in the first pipeline shows as failed (allowed): it exits 3 when check-queries fails, which is a warning by design.

Merge to issue closed: 7:49. Merge to summary release: 10:25. Earlier runs: !5 → !6 (2026-10-22, 4:58) and !3 → !4 (5:00), with 3 tenants and before staging existed.

Hands-off, word by word (issue → MRs → scans → fix → merge → staging → validate → prod → summary):

Word Where it happened Link
issue Steward opens the impact issue #9
MRs The human's migration MR and the steward's fix MR !12, !13
scans SAST and secret detection on the fix branch semgrep-sast, secret_detection
fix The steward's one-word code fix 4a5f961
merge The merge policy job merges that exact sha policy job, fc00a6f
staging Cloud SQL atlas_staging, before prod and again after the fix staging (migration), staging (after fix), environments
validate Postgres plans every query on every tenant; throwaway verify check-queries on the fix, verify
prod Cloud SQL atlas migrated, then re-verified after the fix prod (migration), prod (after fix)
summary Release for the map, linking package, staging and prod release map-acdf7563b34c

Proof: 9 stages

Stage What runs Evidence
Plan Steward opens an issue per map change; the fix MR says Closes #N #9, #10
Create Steward commits the map and writes the code fix 5d30dfd, !13
Verify verify and check-queries against real Postgres: throwaway, staging, prod check-queries, staging
Secure SAST, secret detection, keyless WIF, pinned proxy checksum semgrep-sast, secret_detection
Package Generic package schema-atlas-map, one version per map content hash packages, package job
Release A release per verified map, linking its package map-acdf7563b34c, releases
Configure Staging and production environments on Cloud SQL; Pages site and Google Cloud mirror from map.json environments, live map, mirror
Monitor Nightly schedule re-reads Cloud SQL and reports drift pipeline 2980944257, #10
Govern Merge policy: path, author, sha-pinned proof, protected token policy job, merge commit fc00a6f, merge_policy.py

Proof: drift nobody merged, at 500 tenants

Five seeded tenants were picked at random (seed 1792756171) and hand-edited on Cloud SQL, one distinct edit each (three added columns, one added NOT NULL column, one SET NOT NULL). The nightly schedule, run on demand, flagged exactly those five and no others out of 500:

Time (UTC) What happened Evidence
11:49:33 Five hand edits applied outside any migration (a local script, by design not in the repo)
11:49:39 Nightly pipeline: verdict drift, 5 new drift entries, one per edited tenant pipeline 2980944257, prod job
11:52:56 Steward publishes the drifted map commit 9ec82d3
11:53:12 Steward opens an issue listing the five tenants; no fix MR issue #10

The five edits, applied at 2026-10-23T11:49:33 UTC (seed 1792756171, pool of 497 seeded tenants):

Tenant Table Edit
a1d40a1f-2895-4e43-89a4-7c70ecac4731 orders ADD COLUMN gift_note text
a5dc99b2-4faa-46d8-89c3-5c10b24298f7 customers ADD COLUMN phone text
ae06c110-c543-416a-828b-a9a0868e0021 payments ADD COLUMN refund_ref text
a41ce52d-e34b-4279-8077-ecac9ec1b36b customers ALTER COLUMN display_name SET NOT NULL
a73c4bdf-f32c-47c4-8617-cd5fdfa61f6a orders ADD COLUMN priority smallint NOT NULL DEFAULT 0

Issue #10 lists the same five tenants and no others.

Earlier, with 3 tenants: a hand-added orders.rush_shipping was caught by the nightly run (2976198329) and reported in #6.

Proof: an agent asks, before and after

Duo CLI, through the schema-atlas MCP server, same question before and after migration 0008. Before (local map 1d2121f0b80c): subtotal_cents. After the loop republished (map acdf7563b34c): order_subtotal_cents. Each answer cites the map hash it came from, so the stale one is visibly stale. Shown in the video at 1:45.

Scale

  • 500 synthetic tenant schemas (migration 0006).
  • check-queries, 2 queries x 500 tenants: 0.7 s on Cloud SQL staging (job), 0.3 s in CI (job). The loop runs inside Postgres in one call; one round trip per EXPLAIN took 235 s.
  • Drift: 5 random tenants hand-edited, exactly those 5 flagged, no others (pipeline 2980944257, #10).
  • The Google Cloud mirror, the Pages site, the repo and the package all serve map sha256:663921390538….

How we built it

The base (MIT, vendored unmodified). okf-pg-tenant reads a multi-tenant Postgres catalog, collapses schema-per-tenant into one logical table set, detects per-tenant drift, and renders OKF Markdown. It leaves the verified block empty because nothing in it verifies anything. That is why this entry exists.

Added after 2026-10-05:

  • map.json with a SHA-256 content hash, and a verdict engine (added, removed, retyped, narrowed, renamed, tenant drift).
  • atlas migrate: append-only migrations, each recorded with its hash; an edited migration is refused.
  • atlas check-queries: extracts SQL constants from Python with ast, and EXPLAINs each one per tenant schema.
  • The Cloud SQL deploy path: keyless Workload Identity Federation from GitLab OIDC, a SHA-pinned Cloud SQL Auth Proxy, no authorized networks; a staging database and job before prod, both GitLab environments.
  • Map packages (generic package per content hash) and a summary release per closed loop.
  • The merge policy (src/schema_atlas/merge_policy.py): one pure decision, replay-tested on the real evidence of fix !6 and mutation-checked; it runs only on main with a protected token.
  • A stdio MCP server (five tools), a one-file site, a Google Cloud Storage mirror.
  • The Duo flow and the CI loop. 64 tests, lint-clean in CI.

How GitLab Duo Agent Platform is used

  • Custom flow flows/atlas-steward.yml, triggered by a passed pipeline on main (a merge or the nightly schedule).
  • Service account ai-schema-atlas-steward-gitlab-ai-hackathon writes the map commits, issues, fix branches and fix commits.
  • Tools: run_command, read_file, list_dir, create_issue, get_issue, create_branch, create_commit, create_merge_request, get_merge_request, create_merge_request_note.
  • Judgement: the flow decides whether a verdict is a fixable rename or a decision for a person.
  • MCP: the verified map is the agent surface in Duo CLI and IDE.

Challenges we ran into

  • A flow can't merge. Its token has the ai_workflows scope, and merge returns 403 insufficient_scope. We moved merging into a CI policy job on main with a protected token, so the policy can't be changed by the branch it judges.
  • Flow runs can't see protected CI variables, because they run on refs/workloads/.... That's good for safety, so prod access lives in CI, not in the agent.
  • Same-project bridge triggers fail (invalid_bridge_trigger), and trigger-API variables were rejected. The fix MR now asks main to run a variable-free merge pipeline, which finds steward MRs itself.
  • Flows don't get MCP servers (excludeMcpTools: true), so the flow reads the CI artifact and MCP serves the published map.
  • Windows line endings changed migration hashes. The hash now normalizes \r\n.
  • check-queries was too slow at 500 tenants (235 s, one round trip per EXPLAIN). It now runs as one PL/pgSQL call: 0.7 s.
  • The merge token can merge but not comment on issues (403). The run summary is a release made with the job's own token instead.

Who did what, plainly

The steward service account writes the map commits, issues, fix commits and fix MRs. GitLab lists the flow's owner as the author of MRs a flow opens. The merge is done by the policy job using a protected token that belongs to the project owner. The merge commit message records the policy and its checks. The author check is metadata; the real guarantees are the examples/-only path rule, check-queries on the exact sha, and that the policy and token live only on main. The staging/production deployments and the map-* summary releases use the job's own token, so GitLab shows the account that triggered that pipeline (the merge-token owner, since the policy's merge started it); no person clicked anything.

Accomplishments that we're proud of

  • A real one-merge loop through staging and prod on Cloud SQL, 500 tenants, with a public link for every word of the Hands-off definition.
  • An outside judge (Postgres EXPLAIN on every tenant) instead of an agent grading itself.
  • No Google key anywhere; prod access is per-job and keyless.
  • Every limit is printed: defaults, keys, constraints, indexes, foreign keys, identity, triggers and RLS are not compared.

What we learned

  • An agent should not grade its own work. Postgres planning every query on every tenant is a better judge than any model.
  • Platform limits are design inputs: flows can't merge and can't see protected variables, so the merge lives in a policy job on main, and that made it safer.
  • Scale shows up in round trips, not rows: 500 tenants made one EXPLAIN per round trip take 235 s; one call inside Postgres takes under a second.
  • A verified artifact with a hash is a different product from generated documentation: an agent can say which map it read and when it was verified.

What's next

  • Compare constraints and indexes.
  • Comment the verdict on the migration MR before merge.
  • Fix renames in more places than examples/ (ORM models, dbt), each with its own judge.

Built With

Share this project:

Updates

Submission history