Sanitizing the database

sanitize-db.ps1 turns a production dump into something safe to take out of the building: a PII-free artifact for CI fixtures, or a FERPA-safe database for demonstrating the system to people outside the institution.

It never touches a real database. Before writing anything, the Go tool underneath refuses to start unless the target database name contains sanitiz or scratch, refuses unless -confirm repeats that name exactly, and refuses to run twice against the same database (a second pass would double-shift every birth date). It refuses outright when ENVIRONMENT=production. The wrapper script will not pg_dump an artifact until a completed row exists in core.sanitization_manifest.

# Fixture refresh for CI, from last night's production backup
.\sanitize-db.ps1 -FromProd

# A demo database, loaded and ready to point a server at
.\sanitize-db.ps1 -FromProd -Mode Demo `
    -RestoreTo sis_demo -ConfirmReplace sis_demo

# Re-run against a dump already on this machine
.\sanitize-db.ps1 -SourceDump db\sis_prod_<stamp>.dump

The pipeline is: fetch the source → recreate a scratch database → pg_restore → bring the scratch schema current → run cmd/sanitize-db → check the manifest → VACUUM (ANALYZE) → pg_dump the artifact → drop the scratch database. With -RestoreTo it also loads the artifact into a demo database and creates one known superuser login.

Where the source comes from

-FromProd pulls the newest nightly backup of sis_prod — the /records instance, which is the true production database — off the server. Those backups are what scripts/backup-db.sh writes to ~/apps/sdasis/backups/ at 05:30 daily; nothing dumps production live for this.

The pick is by filename, not ls -t: sis_prod_YYYYMMDD_HHMM sorts chronologically, and the stamp is the value whose age gets reported, so a file that was merely copied or touched cannot make that report a lie. The age itself is computed from the file's mtime rather than its stamp, because backup-db.sh stamps in the server's timezone. The run warns above 36 hours old, and warns separately when the name and the mtime disagree by more than six hours.

The download skips itself when a local file of the same name and size is already present, and lands via a .part rename so an interrupted scp cannot leave something that looks like a complete dump.

What arrives in db\ is real production data with real PII. It is gitignored (db/sis_*.dump), and deploy.ps1 explicitly excludes ^sis_prod from its newest-dump auto-pick so it can never be restored over sis, but it is still a production dump sitting on a laptop. Delete it when you are done with it.

-SourceDump <file> takes a local dump instead. Exactly one of the two is required.

Two modes

	Fixtures (default)	Demo
Names	every person faked	every person faked
Gender	assigned M or F to everyone	assigned M or F to everyone
Demographics	redrawn from the observed distribution	redrawn from the observed distribution
External identifiers	remapped	remapped
Photos	none (table truncated)	generated placeholders
Consumer	CI, local test runs	people outside the institution

The only difference left is photos. Everything else — every name faked, gender assigned, demographics redrawn, identifiers remapped, free text emptied — happens in both. The two profiles are kept because that difference is what distinguishes a CI fixture from a demo artifact, and ci/fixtures-db/build.ps1 refuses to build a fixture image out of the wrong one.

An earlier version of Demo kept the names of faculty and academic admin, because those are already public on the institution's website, and left Fixtures demographics untouched. Neither is true now: no name survives sanitizing, whatever roles the person holds, so a demo shows fake instructor names against real courses.

What is rewritten

Identities are deterministic: the same seed and the same person_id always produce the same fake person, so refreshing a fixture does not churn every row. The generator lives in internal/sanitize/identity.go, and its draw order is a contract pinned by TestDeriveIdentityGolden — reordering the draws rotates every generated value.

Names come from gender-matched pools (internal/sanitize/names.go) selected by the gender the sanitizer assigned the person, so a row's name and its gender always agree. gofakeit ships a single mixed name list — which produces female "Keith" rows — which is why this is our own data rather than a library call.

Gender is assigned, not preserved. Every person gets M or F, drawn from the seed at 57.19% F — the share among the 190,938 people who had a gender recorded in the August 2026 source. That includes the 47,395 who had none, so a sanitized copy has no unrecorded genders at all, and NB, O and U are never assigned even though they exist in core.lookup_gender.

Two consequences worth knowing before you demo from one of these:

• The IPEDS data-quality screen's "People missing gender" figure is permanently zero, and the Fall Enrollment and Completions pivots have no unknown-gender column. See IPEDS reporting.
• people holds organizations as well as people, and they are assigned a gender along with everything else. They already received a person's name, so this adds no new kind of wrongness, but it is there.

The gender is resolved to a real core.lookup_gender id before anything is written, and the run fails if either the M or the F row is missing, or if either code appears twice. That is not defensiveness for its own sake: the whole pipeline runs with session_replication_role = replica, so the people.gender_id foreign key is not enforced while it works, and a bad id would be written without complaint.

Birth dates move twice: within the year, and across years. A day-only shift leaves the birth year intact, and a birth year plus a program plus a cohort can single out one student even when every name is fake. Dates landing outside the -birth-year-floor/-birth-year-ceil band (1946–2011 by default, roughly ages 80 and 15 against the 2026 epoch) are pulled back inside it, because the extreme tails are where a person becomes unique. The band is fixed years rather than an age computed from now() on purpose: an age band would make output depend on the day the tool ran, and determinism across refreshes is part of the contract.

Identifiers are format-aware per type. SSNs land in the 9XX block, which is never issued. Sensitive types are stored encrypted, so the tool encrypts the fake value with this environment's key and clears the plaintext column — rewriting only the plaintext would leave the real ciphertext sitting in the dump, invisible to every SQL-level check.

Emails and phones are rewritten for everyone, public-role people included. Faculty email is public information, but a demo instance holding live addresses can send mail to real people. Every address ends in sanitized.example.edu (IANA-reserved, undeliverable) and every phone sits in the fictional (AAA) 555-XXXX block. Every auth_users row gets the password SanitizedDev1!.

External identifiers are the ones that used to survive: students.student_number is the number on the ID card, and colleague_stc_id, colleague_sal_id, legacy_academic_credits.colleague_id and the 27 legacy_id columns are source-system record keys. Any of them lets a peer institution running Colleague re-identify a row. Keys that are displayed or joined on go through a width-preserving bijection (multiply mod a prime), which is one-to-one — so UNIQUE(student_number) holds — and identical everywhere, so two tables holding the same student's number still agree afterwards. Keys that only exist to talk to an external system are cleared; nothing in a fixture or a demo calls back into Colleague or Canvas.

Free text is emptied wholesale rather than scrubbed per-key. This is where a FERPA leak survives a name rewrite: a registrar typing "spoke with her mother Janet about the AP credit" puts a real name in a column no format check would flag. Most of those columns are empty today because the data is imported rather than hand-entered — the point of listing them in redact.go is that they stay that way once someone starts using them. Institutional free text is deliberately kept: course and material notes, hold-type instructions and fee notes describe the institution, not a student.

Audit history and live credentials are truncated: every audit log, the grade change log, access tokens, LMS tokens, sync logs. Per-key redaction of a full-row JSONB snapshot is a game you lose once.

Demographics are redrawn from their own distribution

Ethnicity, race, religion, marital status, veteran status, language and citizenship are not directory information — at an SDA institution religion is among the most sensitive fields in the database — and combined with a program and a cohort they are weak identifiers.

Each is redrawn per person, at random, weighted by that column's own observed distribution (internal/sanitize/demographics.go). The draw is seeded, so the same seed against the same dump deals everyone the same values, but it is not a permutation: nobody receives anybody else's values.

NULL is an ordinary drawable outcome, and has to be. Three of these columns are entirely NULL in the source and ethnicity is 61% NULL; treating NULL as "nothing to draw" would invent demographics for people who never had any.

What this trades away. The previous implementation swapped the whole tuple between people through a bijection, which preserved the full joint distribution — every crosstab of these columns came out exactly right. Drawing each column independently preserves only the marginals: a two-column crosstab now approximates the product of its margins.

What it buys is the reason to prefer it anyway. Under a swap, a rare tuple — veteran, Farsi-speaking, Iranian citizenship — still exists in the data, merely attached to a different name. Someone identifiable by the rarity of their own demographic profile therefore had that profile survive sanitizing intact. An independent draw destroys rare tuples instead of relocating them.

Two exceptions are drawn as tuples, because their members are not independent facts about a person:

• ethnicity_id with the person's whole race set. IPEDS categories are a function of the pair — "Hispanic or Latino: any race, if ethnicity is Hispanic/Latino", "Two or More Races: non-Hispanic, two or more races selected" — so drawing them apart would preserve both margins and still move every cell of the report. Drawing the set whole also preserves the share of people with no race rows at all (the empty set is one of the outcomes).

• citizenship_country_id with citizenship_country_code. One is the code form of the other; independent draws let the id say Iran while the code says US.

  Note what drawing the pair does not do: it reproduces the source's own asymmetries rather than tidying them. In the August 2026 data citizenship_country_code is NULL for all 238,333 rows while 7,283 carry an id, so every drawable tuple has a NULL code and the sanitized copy comes out with ~7,300 rows holding an id and no code. That is fidelity, not a bug — do not "check" it by asserting the two columns are always both set or both NULL, because the real database has never satisfied that.

country_of_birth_id and primary_language_id correlate with citizenship in reality but are not constrained by it, so they are drawn independently. That is a choice, not an oversight.

A column whose every row holds the same value is skipped rather than rewritten to what it already says — which today is the three that are 100% NULL.

gender_id is not part of this. It is assigned in the identity stage, because the name pool follows it.

Two things check it, and they check different things:

• The pre-draw distribution of every column is recorded on the manifest, and verification recomputes it and compares within a statistical band (below). A value that was not in the source is a hard failure with no tolerance.
• Whether the draw ran at all is asserted directly against the staged values, in demographics.go rather than in the verification suite, so -SkipVerify cannot get past it. It has to be separate: an UPDATE whose join matched nothing leaves every distribution perfect, so no distribution check of any kind could catch it.

Photos are manufactured

core.person_photos is truncated like every other table that references files on disk, which leaves a demo with an empty avatar on every roster — the most visible way a demo looks broken. Demo mode generates a flat colored tile bearing the person's (already fake) initials, rendered through photosync.Derive and written through photosync.Cache so the files land exactly where the app looks for them. -PhotoLimit decides how many students get one: 0 none, a positive number that many (500 by default), negative every student. 0 means none rather than all deliberately — asking for no photos is the common case, and the opposite reading turns a plausible flag value into 128,766 generated files.

Recipients are picked by lowest student_id

Which means they land on the oldest records. In one 200-photo build every recipient was Inactive or Graduated and born between 1948 and 1952, while only 1,507 of 128,766 students are ACTIVE — so the students someone would actually click in a demo had empty avatars. Fix the ordering in insertPlaceholderRows to prefer ACTIVE students before relying on photos in a demo.

The photo share is real faces

The official photo repository is an SMB share that internal/photosync mirrors into PHOTO_STORAGE_PATH, and it holds real students' faces. A database sanitizer cannot protect files it does not own.

A demo host must get a fresh, empty PHOTO_STORAGE_PATH and must not hold credentials for the share. The generator refuses to write into a directory that already contains an official/ subtree, but that check only covers the machine it runs on.

Reloading an artifact without re-sanitizing

The load is the last of eight steps, so a failure there — a client holding the target database open, a full disk — throws away twenty minutes of sanitizing to produce a file that already exists. -LoadOnly skips straight to the restore, treating -SourceDump as an already-sanitized artifact:

.\sanitize-db.ps1 -SourceDump db\sanitized\sis_demo_20260824_2054.dump -Mode Demo `
    -RestoreTo sis_demo -ConfirmReplace sis_demo -LoadOnly

It refuses any dump that does not contain core.sanitization_manifest, because a flag that skips sanitization is otherwise the fastest route to putting real PII into a database called "demo".

The wrapper also checks up front that nothing is connected to the restore target. dropdb fails on a single connection, and a GUI client left pointing at the demo database is the usual cause — worth knowing before the run starts, not after.

The restored database needs its search_path set

search_path is a database-level setting here (ALTER DATABASE sis SET search_path = core, records, ...), and pg_dump does not carry those: they live in pg_db_role_setting, not in the dump. A freshly restored database therefore comes up with the default "$user", public, and because none of this schema is in public, the backend fails every query with relation "auth_users" does not exist.

The sanitizer never notices — it sets search_path per session — so this surfaces only when an app connects. -RestoreTo applies the setting and then proves it by running one unqualified query. Restoring an artifact by hand needs it too:

ALTER DATABASE sis_demo SET search_path = core, records, admissions, studentfinance, financialaid, public;

The artifact carries the dump's schema

A sanitized artifact is a copy of whatever schema the source dump had, and of its core.schema_migrations ledger — the ledger rides inside pg_dump, so the artifact believes its source's applied-set is its own.

This now bites the fixtures path as well as the demo one. sis_prod is a separate instance from sis and can sit behind it, and CI's fixtures:check asserts that a fixture's ledger covers every file in database/migrations/ (it asserts rather than migrating, deliberately — see continuous integration). A fixture built from a behind source fails the next pipeline.

So the wrapper reports the scratch database's pending migrations before sanitizing, and -MigrateScratch applies them. It defaults on for -Mode Fixtures and off for -Mode Demo, which is migrated after loading instead. It is not fatal when it fails: a migration written against sis's data can fail against a sis_prod copy, and throwing there would discard the whole restore. Add -AllowBulk when the source is more than ten migrations behind, which is db-migrate.ps1's own limit.

Migrating happens before sanitizing rather than after, for three reasons: the drift sweep then sweeps the schema CI will really run against, redactAll and truncateAll cover any column a migration just added, and the migrations' own trigger writes land in audit_log, which truncateAll empties afterwards.

A demo database still needs bringing up to date after loading:

.\scripts\db-migrate.ps1 -Database sis_demo -Status
.\scripts\db-migrate.ps1 -Database sis_demo -Apply

Refreshing the deployed demo

Two different operations, often confused:

• reset — scripts/reset-demo.sh, on the server, weekly at Sun 02:00. Reloads sis_demo from the artifact already sitting in db/, undoing a week of visitors, then applies every migration the artifact predates (db-migrate.ps1 -Target Server -AllowBulk). It needs nothing from a workstation.
• refresh — producing a new artifact from current data. That needs pwsh, the Go toolchain and a local PostgreSQL, so it is a manual workstation job.

The refresh, end to end:

# 1. Build it. -FromProd takes the newest sis_prod nightly; -PhotoLimit 0 skips
#    the placeholder tiles, which nothing ships to the server anyway (see Photos).
.\sanitize-db.ps1 -FromProd -Mode Demo -PhotoLimit 0

# 2. Ship it. Note the destination: db/, NOT db/sanitized/.
scp db\sanitized\sis_demo_<stamp>.dump `
    rvyhmeister@sdasis.puc.edu:/home/rvyhmeister/apps/sdasis/db/

# 3. Reload. No argument: it picks the newest db/sis_demo_*.dump. It also
#    applies the migrations the artifact predates, so the schema matches the code.
ssh rvyhmeister@sdasis.puc.edu "cd ~/apps/sdasis && ./scripts/reset-demo.sh"

The schema has to be migrated after every reload, not just after a refresh. An artifact's ledger stops at the day it was cut, while demo-backend runs whatever was last deployed. Until the reset did this itself, every Sunday put the schema behind the code again: in October 2026 sis_demo sat at migration 258 under an image built at 268, and the Audit Trail list failed on a column the database did not have. If the reset's migrate step fails, the demo still comes up (on the older schema), the run exits non-zero and cron-run.sh mails it. To finish by hand: .\scripts\db-migrate.ps1 -Target Prod -Database sis_demo -Apply.

deploy.ps1 cannot carry the artifact for you. Its scp targets the right directory, but it is inseparable from a restore over the PRIMARY database, and sis_demo_*.dump is explicitly excluded from its newest-dump auto-pick for exactly that reason. Step 2 is a plain scp on purpose.

The db/ vs db/sanitized/ trap. sanitize-db.ps1 writes to db\sanitized\, but reset-demo.sh globs db/sis_demo_*.dump at the top level of db/. An artifact uploaded into a sanitized/ subdirectory is invisible to it, and the reset silently reloads the previous artifact instead.

Identifier keys must match. The sanitizer re-encrypts the fake SSNs with the keys it runs under, and the demo reads them with SIS_DEMO_IDENTIFIER_AES_KEY / SIS_DEMO_IDENTIFIER_HMAC_KEY from the server's .env.local. If the workstation .env differs, every click-to-reveal on the demo errors on ciphertext it cannot decrypt. cmd/sanitize-db reads these through godotenv.Load, which does not override an already-set environment variable, so exporting them before the run is how you build against the demo's keys.

When comparing the two, compare the values, not a quick shell hash of them: .env.local may carry CRLF line endings, and IFS='=' read mangles base64 padding. Both make identical keys look different.

Nothing migrates sis_demo on an ordinary deploy. A bare deploy.ps1 prints "Not migrating sis_demo" and moves on, by design, so that deploying sis is not hostage to the demo's state. Step 4 above is the light path; deploy.ps1 -Include demo also works but rebuilds images unnecessarily.

Dashboard matviews may come up empty. dashboard.* materialized views are not sanitized and do not need to be: pg_dump never carries matview rows, only the definition and a REFRESH, so their contents are always recomputed at restore time from whatever base tables the artifact holds — the sanitized ones. What can go wrong is the refresh itself. Each has a UNIQUE index, and any duplicate in its defining query fails the build and leaves it unpopulated:

view	fails when
dim_students	a person has two is_current AND is_primary rows in core.person_addresses
fact_term_enrollments	a student has two overlapping is_primary rows in records.student_majors (1,504 students as of Sep 2026)

Those are production data conditions, not sanitizer bugs, and they break the /records dashboards the same way whenever anything refreshes them. An empty matview is dumped WITH NO DATA, so the failure does not propagate into the demo's restore — it just leaves those screens blank.

Who has been using the demo is answered by scripts/demo-logins.ps1, run from a workstation. It is read-only. Over ssh it reads every POST /demo/api/v1/auth/login from the Caddy access log (time, client IP, status, user agent; that log outlives the weekly reset but not a recreate of the caddy container, and it never names the account), and attaches the exact account and roles from sis_demo's audit trail — a successful login is the audited UPDATE of core.auth_users.last_login, which carries the client address since migration 264. The Sunday reset wipes that audit trail, so a login from before the last reset gets a role inferred from the screens the session went on to open instead. External addresses are resolved to reverse DNS on the server and to their registered owner via RDAP from the workstation (-NoLookup skips the RDAP lookups; -Days sets how far back to read, default 30).

One published login per role

The demo exists to be tried by people who have no account, so its login page lists one working login for every active role, with the password they share (GET /core/demo-accounts, empty unless the backend runs with DEMO_MODE=true).

Every auth_users row in an artifact already carries the dev password, so the accounts always worked — what was missing was any way to tell which of ~88,800 faked emails is a registrar. Each role's account is renamed to demo.<role_code>@sanitized.example.edu, lowercased.

The logic lives in internal/shared/demoaccounts, and it runs in the running instance — cmd/api, at startup and then hourly, on demo-backend only.

It deliberately does not run in the sanitizer, which is where it started. Publishing at artifact-build time only ever reached a demo when somebody produced a new artifact, which needs pwsh, the Go toolchain, a local PostgreSQL and a production dump. The deployed demo sat with an empty login page and no way in; the feature looked broken because for practical purposes it was. The sanitizer still calls PasswordHash for its all-rows password pass, so every account in an artifact is usable the moment an instance starts.

Where the accounts come from — and why most are created

First choice is somebody who genuinely holds that role and nothing else: permissions and the menu come from the acting role alone, so a single-role account cannot land somewhere other than advertised. Failing that it adopts an account with no roles; failing that it creates one.

Holding the role is not the same as having data. The pick used to be the lowest auth_user_id, and ids follow person-UUID order, so in Oct 2026 demo.student was an undeclared student on the 2019 catalog, status COMPLETED, with no enrollment at all. The audit was empty and the Plan dialog listed no sections, which looked like the demo had no future courses (it has sections through 30/FA). For STUDENT, FACULTY and ADVISOR, holderRank in publish.go now orders holders by:

role	"live" (sorts first)	then, richest first
STUDENT	enrolled in a term that has not ended and an active primary major with requirement blocks	enrollment count
FACULTY	teaching a section in a term that has not ended	that section count
ADVISOR	an active advisee enrolled in a term that has not ended	those advisees

Ties go to fewer roles, then lowest id. "Not ended" is end_date >= NOW(), the same test the planning dialog uses.

ADVISOR is why these three roles relax the single-role rule: every advisor with enrolled advisees also teaches, so the sweep derives FACULTY for all of them, and the strict rule could only ever pick the one advisor with no advisees. For the ranked roles a holder may also carry grants the sweep owns (not manual, on an AUTO_DERIVED role), and ensureAccount leaves those grants in place. If it stripped them, the sweep would put FACULTY back at 03:00, the publisher would remove it again within the hour, and that would repeat every day. The acting role keeps the login on ADVISOR. The login page and the tests list only the grant the email names (demo.<role>), so demo.advisor is not also shown under Faculty. Every other role keeps the strict rule.

A deployed demo keeps whoever already holds a published email (see below), so a picker change reaches it at the next Sunday reset, not before.

Creation is the common path, not a fallback, and the numbers are the opposite of what the design first assumed. Measured against a real Aug 2026 artifact (88,745 accounts, 18 active roles):

	roles
clean single-role holder	4 — REGISTRAR, ADVISOR, FACULTY, STUDENT
holders exist, but all hold several roles	9
no holder at all	5 — Cashier, ENROLLMENT, HEALTH_SERVICES, RECORDS_OFFICE, STUDENT_SUCCESS

…against one role-less account in the entire database to adopt, because the derived-role sweep grants STUDENT to all 87,954 people with a student record. So without creation, 13 of 18 roles would have no login and the page would list five.

Created accounts get person_id = NULL, deliberately. Attaching a fabricated person would assert that this invented student is the Title IX officer. Worse, the 03:00 sweep derives roles from the person. If the invented link had a student record, the sweep would add a STUDENT grant, which the publisher now keeps like any derived grant, and the Health Services login would carry somebody's transcript. A NULL person has nothing to derive from. The cost is no name beside those roles on the login page, which person_name being optional already allows for.

This is also why the sanitizer cannot publish: inserting accounts moves the auth_users row count, and verifyCountInvariants fails when it does. Keeping that guard honest is worth more than labelling an artifact at birth.

Repairing in place, keyed on the published email

Running hourly forces one design choice worth understanding. The question asked first is "who currently holds this role's published email" — in any state at all — and that account is repaired. The obvious alternative, "find a usable account for this role", breaks the moment a visitor deactivates a published account: it is no longer usable, so a different account gets picked and renamed onto an email the deactivated row still holds, and UNIQUE(email) rejects it. Every hour.

Keying on the email means a visitor who deactivates an account, revokes its role or switches its acting role is undone on the next run rather than at the weekly reload.

Every step reads before it writes, so a settled demo writes nothing — which matters because auth_users and user_roles are both audited.

Two further details are load-bearing:

• Every grant carries is_manual_override = true. The 03:00 DeriveAllRoles sweep runs in the demo container too and deletes any AUTO_DERIVED grant it cannot re-derive — without it the STUDENT, FACULTY, ADVISOR, PROXY and ACADEMIC_ADMIN logins would resolve to no role at all overnight.
• ADMITTED_STUDENT is skipped. It is a SYSTEM pseudo-role the workflow engine evaluates dynamically; a user_roles row would be ignored and would misrepresent how it works. The exclusion is data in demoaccounts.go, with its reason, so the gap in the list is visible.

Every password is put back hourly

The same job restores the shared password — on every account, not only the published ones. The sanitizer puts it on every row, so signing in as anybody is the demo's real state, and a visitor holding the published superuser login can change other people's passwords as easily as their own.

It resets to DEMO_PASSWORD, the same value the login page publishes: one field feeding both, so the page and the job cannot come to disagree. If they ever did, the page would advertise a password that does not work and the reset would enforce that rather than fix it.

The hash is deterministic, and that is not optional

A demo has ~88,800 auth_users rows. Hashing with a random salt would produce a different string every process start, so the UPDATE would rewrite every one of them on every container restart — and 88,800 core.audit_log entries with them, for a demo nobody had touched.

demoaccounts.PasswordHash therefore uses a fixed salt and a low iteration count. A restored artifact already holds exactly that string, so the steady state is an UPDATE matching nothing. TestPasswordHashIsGolden pins the output against a literal value: if it ever changes, every artifact in circulation holds the old one.

The weak iteration count protects a password printed on the front page of a public demo. CheckPassword honours the stored count, so these accounts interoperate with the real random-salt hasher.

What keeps the writes safe

Between them these statements rewrite emails, role grants and every password in the database, so three things gate them:

1. The job only starts when DemoMode is set (DEMO_MODE=true, on demo-backend alone).
2. It skips with an error if DEMO_PASSWORD is empty, which would otherwise set 88,800 accounts to an empty password.
3. Every run first checks the database carries a completed core.sanitization_manifest — the same marker scripts/reset-demo.sh uses to decide a dump is sanitized. A real database has none, so DEMO_MODE set on the wrong instance refuses (ErrNotSanitized) instead of renaming staff accounts and wiping every password.

Guard 3 is what earns the unfiltered writes; without it the only thing between them and production would be one environment variable.

No SAML on the demo

The artifact carries the institution's sso_identity_providers rows, so /demo used to offer a real "Sign in with PUC" button. Two problems, and the second is what made the page unusable:

• The flow ends in JIT provisioning, so a real person authenticating against the real IdP would be created as a user on a database of fabricated people.
• The login page hides its password fields behind a "Sign in with password" link whenever any provider exists. With the credential list empty, the page was a logo, one SSO button, and no way in.

Under DemoMode, GET /auth/sso/providers answers an empty list and the three routes that actually admit someone — saml/{slug}/login, .../acs, .../metadata — are not registered at all, the same posture as /auth/dev-login outside development. Pinned by TestSAMLAbsentOnDemo.

Admin CRUD under /auth/sso/idps stays: it is permission-gated, it is a screen worth exploring, and removing it would leave a sidebar entry that 404s.

The tests copy the real schema

demoaccounts_live_test.go builds its fixture with CREATE TABLE ... (LIKE core.<table> INCLUDING ALL) rather than a hand-written CREATE TABLE, so column types, NOT NULL, defaults, identity and indexes come from the live tables.

That is not tidiness. The hand-written version was laxer than reality twice, and the second time it shipped: user_roles.created_by is uuid, the fixture called it text, and the job wrote the label "demo-job" into it. It failed with SQLSTATE 22P02 on the first role, published nothing, and left the demo login page empty — with all 13 tests green. Those columns name a person and are NULL for a system job; provenance lives in core.audit_log under actor_source = 'SYSTEM'.

LIKE does not copy foreign keys or triggers, which is what keeps the scratch tables standalone. Two things follow:

• Serial columns copy a default naming the original sequence, so a run advances those sequences slightly in the target database. Harmless on a sanitized demo, and one more reason the DSN must never be a real database.
• The audit trigger is not copied, so these tests do not exercise it. That path rests on production evidence instead: the 04:00 RecomputeStudentStatuses job writes to audited records.students by bare ExecContext nightly, and the audited driver wraps bare writes in BEGIN → set_config → … → COMMIT (internal/shared/db/audit_driver.go).

Run it against both schema vintages — one artifact with user_active_role.active_admin_id (migration 206) and one without — since the column is guarded by columnExists and each DSN exercises one branch:

$env:SIS_LIVE_DB_TEST=1
go test ./internal/shared/demoaccounts/ -v
$env:SIS_TEST_DSN="host=127.0.0.1 port=5432 user=postgres password=postgres dbname=sis_demo sslmode=disable"
go test ./internal/shared/demoaccounts/ -count=1

Every role is usable from anywhere

Production restricts administrative roles to campus subnets and ZPA (migration 291, Admin > Role Networks). A demo is shown from wherever its audience is, and a CI fixture is driven from 127.0.0.1, so both modes rewrite those tables (internal/sanitize/networks.go): every network and range is deleted, a single Anywhere network (0.0.0.0/0 and ::/0) is created, and every role gets one ALLOW on it. The ranges go as well as the rules, because campus subnets and the ZPA App Connector addresses describe the internal network and have no place in an artifact that leaves the building.

A dump taken before migration 291 has no such tables and the step is skipped. Applying 291 to that demo later seeds production's two-tier rules, which is why the demo container also runs with ROLE_NETWORK_ENFORCEMENT=off.

Verification

-verify (on by default through the wrapper) runs the whole suite, collects every finding, and fails as a set. A non-zero exit means the artifact must not be shipped — and the wrapper's manifest gate means it will not be.

Check	Proves
Formats	emails, phones and birth-date bands are all in the synthetic ranges
Encrypted identifiers	each ciphertext decrypts to a fake value whose search hash matches — the only way to prove the real ciphertext is gone
Gender assigned	every person has a gender, it is M or F, and the F share is within 5σ of the 57.19% the draw targets
Name/gender	every stored first name is in the pool its person's assigned gender selects; a name in no pool is a name the sanitizer never wrote
Identity changed	each faked name row differs from its pre-rewrite hash — the check that catches an UPDATE that matched nothing
External IDs	cleared keys are NULL, and student_number scattered out of its real dense range
Emptiness	every truncated table is actually empty
Free text	every redacted column holds its redaction value, driven by the same list redactAll writes from
Photos	every row is a LOCAL placeholder whose stored_filename is its own photo_id
Demographics	every post-draw distribution is within a 5σ band of the manifest snapshot, and no value appears that was not in the source
Demographic draw applied	every person holds exactly the values drawn for them — a hard error in demographics.go, not a collected finding
Count invariants	rewritten tables have the same row counts as before
Drift sweep	no PII-named or external-ID-named column exists that the tool does not handle
Role networks	Anywhere is the only network and covers 0.0.0.0/0 and ::/0, there is no DENY, and every role ALLOWs it

Four of these deserve explanation.

The identity-change check is the one that tests removal. Everything else tests the shape of what is in the database; this hashes every name row before the rewrite and compares afterwards, so it fails when a rewrite silently applied to nothing — the failure mode where every format assertion still passes because the real data was already well-formed. Its threshold is 100 rather than zero: a generated name is drawn from a finite pool, so roughly one row in 170,000 keeps its real name by coincidence (one or two in this database), while a skipped UPDATE leaves tens of thousands.

The student-number check is a leakage detector, not a format check. Real student numbers are dense and sequential from 0000001, so nearly all are below 1,000,000; the bijection scatters them across the full range, leaving about 10% there. If the UPDATE never ran, the low bucket stays near 100% — which matters because a real number and a fake one are indistinguishable by shape. The threshold is 25%.

The demographic band is a tolerance, not an equality. The draw is per person and independent, so counts move: the check asks whether each bucket is still within 5σ + 20 rows of where it started, using the exact binomial deviation sqrt(want · (1 − want/N)) rather than sqrt(want) (they differ by a factor of two at a bucket holding most of the population, which is where ethnicity's and religion's NULL buckets sit). The constant is 5 and not 3 because a real run tallies 414 buckets: at 3σ two-sided that is over one false failure per refresh, and because the draw is seeded a false failure would not be flaky — it would be permanent for that seed. Measured, not assumed: the worst bucket on a healthy 238,332-person run came in at z = 2.84, which 3σ would have nearly failed. The +20 floor covers small buckets, where the normal approximation is poor; the cost is a blind spot, pinned by a test, in which a bucket of under 59 rows could vanish unnoticed. That is acceptable because the draw can only ever produce values that were already in the source — and a value that was not is a hard failure with no tolerance at all. The draw applied check, described above, is what tests removal here; the band cannot.

The drift sweep is how this stays correct. It matches every column name in the app schemas against a PII pattern and an external-identifier pattern, and fails on anything not explicitly redacted, truncated, covered or justified. When a migration adds a PII-shaped column, the next run of this tool fails until someone decides what to do with it. Institutional false positives go in knownSafeColumns with a justification comment — for example student_holds.reason, whose 93,548 populated rows hold one of 29 machine-generated strings of the form Imported from Colleague restriction FA001.

-verify-only runs the suite and nothing else. Because it writes nothing, the scratch-name and -confirm rails do not apply to it, which is what lets you check the database you actually deployed. It reads the profile off the manifest rather than trusting the flag, so a demo database is not judged against fixture expectations, and it skips the checks that cannot hold post-hoc: the row counts (the wrapper adds the demo admin after the restore), the identity-change comparison (its snapshot is a session-scoped temp table), and the demographic draw-applied assertion (same reason — and because it lives in the sanitize stage, there is nothing to skip and no misleading pass reported).

cd sis_backendgo
go run ./cmd/sanitize-db -db-name sis_demo -verify-only `
    -assert-absent-file ..\assert-absent.txt

Proving it against real values

-AssertAbsentFile takes a local, never-committed file of known real values — student numbers, emails, phone numbers, identifiers pulled from the source database — one per line, and fails if any of them still appears. It never echoes the value it found.

Use high-entropy values. A common surname produces a false positive: a name held by 696 real people is also in the generator's surname pool, so it reappears on hundreds of different, synthetic people, and the check only knows the value, not whose it was. In testing, 4 of 6 sampled surnames tripped it this way while the distinctive ones came back clean. The identity-change check above is what actually proves no person kept their own name; this file is for proving specific identifiers are gone.

What is deliberately NOT sanitized

• Gender (no longer — it is assigned now). It used to sit in this list on the grounds that gender alone is not identifying. It is rewritten because the generated name has to agree with it; the cost is that "gender not recorded" cannot be demonstrated at all.
• Academic records — grades, enrollments, transcripts, GPAs, test scores. They are the product being demonstrated, and they hang off synthetic identities.
• Institutional data — courses, sections, schedules, rooms, terms, the catalog, fee structures, degree requirements.
• pell_recipient, first_gen_flag, ipeds_* — weak identifiers kept because the reporting demo needs them. Accepted residual risk.
• Small-cell uniqueness — a one-student major is still a population of one. Suppressing it would gut the demo, and the identity it attaches to is synthetic.

Adding coverage

1. Add the column to redactions (free text), truncateTables (whole-table), nulledExternalIDs / remappedExternalIDs (identifiers), or write a stage.
2. If it is institutional rather than personal, add it to knownSafeColumns with a comment saying how you know.
3. Run go test ./internal/sanitize/... and then the pipeline with -verify. Verification is driven by the same lists, so most new coverage is checked automatically.

Bumping DefaultSeed rotates every generated identity. Do it deliberately — and when you do, re-pin TestDeriveIdentityGolden in the same commit.