Runbook: regenerate migrations from scratch (no-users phase)

When to use

While we are pre-launch with no production data, we keep the migration history clean by regenerating the whole set from the current Drizzle schema instead of stacking rename/alter migrations on top. Use this whenever the schema has been reshaped or renamed (e.g. course_material → external_package) and you want the migrations to reflect the final shape rather than the churn.

Do not use this once real data exists — at that point, forward alter/rename migrations become mandatory.

How the migration framework works here

• drizzle-kit 1.0+ emits one folder per migration containing migration.sql + snapshot.json. There is no central meta/_journal.json — ordering is the timestamped folder prefix, and applied state is tracked in the DB table drizzle.__drizzle_migrations.
• Two kinds of migrations live in packages/db/src/migrations/:
  • Schema migrations — produced by plain drizzle-kit generate (diffs the Drizzle schema against the latest snapshot.json). The initial_baseline is one of these.
  • Custom migrations — scaffolded empty via drizzle-kit generate --custom --name X and then filled, either by a generator script (governance/auth-hooks/claim/sync) or by hand (the storage bucket). Their snapshot.json is an empty diff against the prior snapshot; only their migration.sql carries content.
• Generating into an empty migrations dir produces a clean full baseline with no interactive rename prompts (there is no prior snapshot to diff against). This is the whole reason “delete + regenerate” is preferred over answering rename prompts.

The ordered migration set

#	name	kind	how
0	pgxsinkit_utilities	custom (script)	bun run db:generate:pgxsinkit-utilities
1	initial_baseline	schema	drizzle-kit generate
2	authorization_claim_projection	custom (script)	bun run db:generate:authorization-claim-projection
3	identity_linking	custom (script)	bun run db:generate:identity-linking
4	supabase_auth_hooks	custom (script)	bun run db:generate:auth-hooks
5	registry_governance	custom (script)	bun run db:generate:governance
6	sync_artifact	custom (script)	bun run db:generate:sync-function
7	storage_buckets	custom (manual SQL)	--custom scaffold + paste SQL (ALL buckets)
8	credential_signing_role	custom (manual SQL)	--custom scaffold + paste SQL
9	qti_item_asset_storage_policies	custom (script)	bun run db:generate:qti-asset-storage-policies
10	assessment_pnp_proxy_policies	custom (script)	bun run db:generate:pnp-proxy-policies
11	learner_submission_storage_policies	custom (script)	bun run db:generate:learner-submission-policies
12	post_safeguarding_audit	custom (script)	bun run db:generate:post-safeguarding-audit

Order matters. pgxsinkit_utilities (step 0) is the exception that comes before the baseline: it renders public.pgxsinkit_clock_us() — the canonical microsecond clock the audit/LWW column DEFAULTs call (created_at_us/updated_at_us bigint DEFAULT public.pgxsinkit_clock_us()) and the sync-function apply artifact emits — so the function must install first, or the baseline’s DEFAULTs reference a missing function. It rides its own render step (renderPgxsinkitUtilitiesMigration() from @pgxsinkit/server, wrapped by buildPgxsinkitUtilitiesMigrationSql in packages/db/src/migration-sql/pgxsinkit-utilities.ts), generated into an empty migrations dir so it takes the earliest timestamp and sorts first. Everything after it: the baseline must come before the other custom migrations (they reference its tables/policies), and each generator appends a later timestamp. Rows 2–4 are order-enforced by DO-block guards that raise at apply time: identity_linking requires refresh_person_identity_claims (row 2), and supabase_auth_hooks (trigger v2’s verified-email resolve step, identity-linking-v1) requires person_contact_point (baseline) and identity_enqueue_merge_review (row 3). The builders behind every script live in packages/db/src/migration-sql/; migration-drift.test.ts fails if a committed migration ever lags its builder.

Procedure

Steps 1–5 below are the agent’s job and need no database. drizzle-kit generate diffs the Drizzle schema against snapshot.json on disk — it never reads the dev DB — so run these straight through, without waiting for the operator. The dev/smoke database is the operator’s personal database; no integration test depends on it, so it never gates this work. The only DB-touching steps are gathered into an optional operator follow-up at the end — an agent must never run those.

All commands run from packages/db unless noted. The dev DB connection (used only by the operator follow-up) is read from ../../.env.development.local (DATABASE_URL).

1. Delete the existing migration files

rm -rf src/migrations/*/

1b. Render the pgxsinkit utilities function (step 0 — BEFORE the baseline)

Install public.pgxsinkit_clock_us() first, into the now-empty migrations dir, so it takes the earliest timestamp and sorts ahead of the baseline (whose created_at_us/updated_at_us DEFAULTs and the sync-function apply artifact call it). Generating into the empty dir also seeds the empty-schema snapshot the baseline then diffs against — so this must run immediately after the delete, before anything else:

bun run db:generate:pgxsinkit-utilities

This scaffolds a custom migration and fills its migration.sql from renderPgxsinkitUtilitiesMigration() (@pgxsinkit/server); migration-drift.test.ts covers it.

2. Regenerate the baseline

bunx drizzle-kit generate --config drizzle.config.ts --name initial_baseline

3. Regenerate the script-based custom migrations, in order

bun run db:generate:authorization-claim-projection
bun run db:generate:identity-linking
bun run db:generate:auth-hooks
bun run db:generate:governance
bun run db:generate:sync-function

4. Recreate the manual custom migrations (storage buckets + signing role)

These are hand-written SQL that lives outside what Drizzle manages — storage.buckets / vault.* rows, a cluster ROLE, and GRANT/REVOKE privilege DDL (Drizzle has no GRANT/REVOKE, and vault/storage are outside schemaFilter). They have no generator, so they are scaffolded empty and the SQL pasted. migration-drift.test.ts does not cover them (no builder), so this runbook is their only source of truth — keep it in sync.

All bucket creations go in one migration (storage_buckets) so every storage.buckets write is reviewed and applied together:

bunx drizzle-kit generate --config drizzle.config.ts --custom --name storage_buckets

Paste this into the new folder’s migration.sql (both bucket ids are referenced from app code: defaultRuntimeStorageBucket in package-import-service.ts, QTI_ITEM_ASSET_BUCKET in qti-item-asset-service.ts):

-- All Supabase Storage bucket creations live together here (storage.buckets is Supabase-owned,
-- outside Drizzle's schemaFilter, so this is hand-written SQL with no generator).

-- Runtime-asset storage bucket for External Packages (h5p/cmi5/scorm). Bucket id matches
-- `defaultRuntimeStorageBucket` in apps/api/.../external-package/package-import-service.ts.
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'external-package-runtime-packages',
  'external-package-runtime-packages',
  false,
  524288000,
  null
)
ON CONFLICT (id) DO NOTHING;
--> statement-breakpoint
-- Authored-item asset bucket for native QTI authoring (ADR-0023). Bucket id matches
-- `QTI_ITEM_ASSET_BUCKET` in apps/api/.../qti-item-asset/qti-item-asset-service.ts. Private:
-- assets are reached only through GET /workflow/qti-item-assets/:assetId (signed URLs). The
-- allow-list + 100 MiB size limit mirror `ASSET_MIME_EXTENSIONS` / `MAX_ASSET_BYTES` in that
-- service: the v1 image kinds plus the audio/video kinds used by `mediaInteraction` (v2).
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'item-assets',
  'item-assets',
  false,
  104857600,
  ARRAY['image/png', 'image/jpeg', 'image/gif', 'image/webp', 'image/svg+xml', 'audio/mpeg', 'audio/ogg', 'audio/wav', 'video/mp4', 'video/webm']
)
ON CONFLICT (id) DO NOTHING;
--> statement-breakpoint
-- Learner hand-in bucket for assignment submissions (ADR-0027 §1). Bucket id matches
-- `LEARNER_SUBMISSION_BUCKET` in apps/api/.../submission-artifact/submission-artifact-service.ts.
-- Private: hand-ins are learner PII, reached only owner-scoped (RLS) or via server-minted signed
-- URLs. The allow-list + 100 MiB limit mirror `allowedSubmissionMimeTypes` / `MAX_SUBMISSION_BYTES`:
-- common documents + images (audio/video deferred).
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'learner-submission-artifacts',
  'learner-submission-artifacts',
  false,
  104857600,
  ARRAY['application/pdf', 'text/plain', 'application/msword', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.openxmlformats-officedocument.presentationml.presentation', 'application/vnd.oasis.opendocument.text', 'application/vnd.oasis.opendocument.spreadsheet', 'application/vnd.oasis.opendocument.presentation', 'image/png', 'image/jpeg', 'image/gif', 'image/webp', 'application/zip']
)
ON CONFLICT (id) DO NOTHING;
--> statement-breakpoint
-- Canonical entity image override bucket (ADR-0035), also reused for extracted wallet badge art
-- (capability-b; ADR-0038 slice 2c.2b). Bucket id matches `ENTITY_IMAGE_BUCKET` in
-- apps/api/.../entity-image/entity-image-service.ts. Private: every reader/writer is server-side
-- over service_role, so no storage.objects RLS policy is needed for this bucket. Two read paths
-- proxy the bytes — the public /img/:kind/:id endpoint (entity overrides) and the public
-- /img/wallet-credential/:key endpoint (a held credential's data-URI image, extracted at import
-- under the `wallet-credential/` key prefix). The allow-list mirrors `ENTITY_IMAGE_MIME_TYPES`
-- and the 5 MiB limit mirrors `MAX_ENTITY_IMAGE_BYTES` in that service.
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'entity-images',
  'entity-images',
  false,
  5242880,
  ARRAY['image/png', 'image/jpeg', 'image/webp', 'image/gif', 'image/svg+xml']
)
ON CONFLICT (id) DO NOTHING;

Then scaffold the credential signing roles + name-scoped decrypted-secrets views (credential-export-v1 issuer / capability-b holder; ADR-0017 guardrail 1, ADR-0038). No generator — paste the SQL (both the issuer credential_signer and the holder holder_signer role live in this one migration, the way all buckets share one):

bunx drizzle-kit generate --config drizzle.config.ts --custom --name credential_signing_role

-- Issuer key custody: the dedicated signing role and the name-scoped decrypted
-- view (credential-export-v1; ADR-0017 guardrail 1). The KeySigner seam connects
-- as `credential_signer` over SIGNING_DATABASE_URL; it is the ONLY reader of
-- decrypted issuer secrets — never the general application role.
--
-- Depends on: `issuer_signing_key` + `credential_sign_event` (initial_baseline)
-- and the `supabase_vault` extension (live via the CNPG extensions image, which
-- creates `vault.decrypted_secrets`; the vault schema survives the public-schema
-- drop in the recreate runbook).
--
-- Operator follow-up (never an agent; see docs/runbooks/credential-issuer-keys.md):
--   ALTER ROLE credential_signer LOGIN PASSWORD '<generated>';
-- and wire SIGNING_DATABASE_URL for the deploy. The role is created NOLOGIN here
-- so no usable credential is ever committed.

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'credential_signer') THEN
    CREATE ROLE credential_signer NOLOGIN;
  END IF;
END
$$;
--> statement-breakpoint
GRANT USAGE ON SCHEMA public TO credential_signer;
--> statement-breakpoint
-- Name-scoped decrypted view: issuer signing secrets only, never the whole vault.
CREATE OR REPLACE VIEW public.issuer_signing_secret
  WITH (security_barrier = true) AS
  SELECT name, decrypted_secret
  FROM vault.decrypted_secrets
  WHERE name LIKE 'issuer_signing_key:%';
--> statement-breakpoint
REVOKE ALL ON public.issuer_signing_secret FROM PUBLIC;
--> statement-breakpoint
REVOKE ALL ON public.issuer_signing_secret FROM anon, authenticated, service_role;
--> statement-breakpoint
GRANT SELECT ON public.issuer_signing_secret TO credential_signer;
--> statement-breakpoint
-- The signer reads keys + the public table and appends the audit log; nothing else.
GRANT SELECT ON public.issuer_profile TO credential_signer;
--> statement-breakpoint
GRANT SELECT ON public.issuer_signing_key TO credential_signer;
--> statement-breakpoint
GRANT SELECT, INSERT ON public.credential_sign_event TO credential_signer;
--> statement-breakpoint
-- Holder key custody (capability-b; ADR-0038): the dedicated `holder_signer` role and the
-- name-scoped decrypted view for per-learner Verifiable-Presentation keys. The KeySigner seam's
-- holder path connects as `holder_signer` over HOLDER_SIGNING_DATABASE_URL; it is the ONLY reader
-- of decrypted holder secrets. A SEPARATE role from `credential_signer` so a compromise of one key
-- class's decrypt path never exposes the other.
--
-- Depends on: `holder_signing_key` + `presentation_sign_event` (initial_baseline) and `supabase_vault`.
-- Operator follow-up (never an agent; see docs/runbooks/credential-issuer-keys.md):
--   ALTER ROLE holder_signer LOGIN PASSWORD '<generated>';
-- and wire HOLDER_SIGNING_DATABASE_URL for the deploy. Created NOLOGIN here so no credential is committed.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'holder_signer') THEN
    CREATE ROLE holder_signer NOLOGIN;
  END IF;
END
$$;
--> statement-breakpoint
GRANT USAGE ON SCHEMA public TO holder_signer;
--> statement-breakpoint
-- Name-scoped decrypted view: holder signing secrets only, never the whole vault.
CREATE OR REPLACE VIEW public.holder_signing_secret
  WITH (security_barrier = true) AS
  SELECT name, decrypted_secret
  FROM vault.decrypted_secrets
  WHERE name LIKE 'holder_signing_key:%';
--> statement-breakpoint
REVOKE ALL ON public.holder_signing_secret FROM PUBLIC;
--> statement-breakpoint
REVOKE ALL ON public.holder_signing_secret FROM anon, authenticated, service_role;
--> statement-breakpoint
GRANT SELECT ON public.holder_signing_secret TO holder_signer;
--> statement-breakpoint
-- The holder signer reads keys and appends the presentation audit log; nothing else.
GRANT SELECT ON public.holder_signing_key TO holder_signer;
--> statement-breakpoint
GRANT SELECT, INSERT ON public.presentation_sign_event TO holder_signer;

4b. Regenerate the script-based storage policies (must come after the manual customs)

qti_item_asset_storage_policies (ADR-0023 amendment) is script-based but ordered last — it adds RLS policies on storage.objects (gating teacher uploads to the item-assets bucket) plus the public.can_manage_offering_assets helper, and must time-sort after storage_buckets (#7). Its builder reuses the centralized grant predicate, so migration-drift.test.ts covers it:

bun run db:generate:qti-asset-storage-policies

It emits CREATE POLICY only — never structural DDL on the Supabase-owned storage schema.

assessment_pnp_proxy_policies (ADR-0021 §6 slice D, O-1) is also script-based and ordered after the baseline: it adds the public.fn_person_pnp_proxy_allowed SECURITY DEFINER helper plus the permissive proxy policies on assessment_person_pnp_profile (a teacher/assistant/org-admin of an offering the learner is enrolled in, or a platform admin, may manage that learner’s PNP profile — OR’d with the owner policies). It touches only our own public table, so migration-drift.test.ts covers it:

bun run db:generate:pnp-proxy-policies

learner_submission_storage_policies (ADR-0027 §1) is script-based and ordered after the manual customs (it adds RLS on storage.objects for the learner-submission-artifacts bucket, #7, plus the public.can_submit_to_attempt SECURITY DEFINER helper). Owner-only (the uploader’s app_metadata.person_id must own the attempt named in the key’s first segment); CREATE POLICY only. Covered by migration-drift.test.ts:

bun run db:generate:learner-submission-policies

post_safeguarding_audit (ADR-0029 §6, Slice 4) is script-based and ordered after the baseline: it adds the public.fn_post_safeguarding_audit SECURITY DEFINER trigger function plus the post_safeguarding_audit trigger on post, recording every body/status change into the scrub-class change_audit_entry. It depends only on post + change_audit_entry (baseline). Touches only our own public schema, so migration-drift.test.ts covers it:

bun run db:generate:post-safeguarding-audit

5. Format the generated files, then confirm

drizzle-kit emits snapshot.json in its own style; oxfmt owns formatting here, so format the freshly generated migrations (otherwise validate’s format gate fails), then run the non-DB validation to confirm the regenerated set is clean:

bun run format:write          # from repo root
bun run validate              # from repo root — typecheck/lint/format + migration-drift tests

validate is filesystem-only here: its tests run against PGlite, never the operator’s dev Postgres, so an agent runs it. The live-DB drift check (db:check:local) is operator-only and lives in the follow-up below.

The agent stops here. The regenerated, formatted, validated migration set is now commit-ready. The most an agent does next is tell the operator: the migrations are regenerated — if you want to, you can now bring your local dev DB in line with them using the optional follow-up below. The agent never resets, applies to, or otherwise touches that DB.

Optional operator follow-up (operator-only — never an agent)

The dev/smoke database is the operator’s personal database. No integration test depends on it, so it never blocks regeneration. An AI assistant or agent is categorically forbidden from dropping, truncating, applying migrations to, or otherwise writing to it. These steps exist only so the operator can, if they want to, bring their local DB in line with the freshly regenerated migrations. They are entirely optional and may be skipped or deferred.

a. Reset the dev database (optional)

Drop our objects without touching Supabase internals (auth, storage, extensions, …). The full drop/recreate procedure lives at infra/utils/cloudnativepg/supabase/db/recreate-database.txt. Run against the dev DB:

DROP SCHEMA IF EXISTS lti_tool CASCADE;
DROP SCHEMA IF EXISTS lrs CASCADE;
DROP SCHEMA IF EXISTS caliper CASCADE;
DROP SCHEMA IF EXISTS drizzle CASCADE;   -- drizzle migration tracking
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT USAGE ON SCHEMA public TO anon, authenticated, service_role;
GRANT ALL ON SCHEMA public TO postgres, service_role;

(Recreating lti_tool/lrs/caliper is unnecessary — the baseline does that.)

b. Apply

bun run db:migrate:local

c. Verify

bun run db:check:local          # snapshot ↔ DB drift check
bun run db:seed:small           # optional: load deterministic fixtures

Notes

• If a new script-based custom migration is added later, add a row to the table above and a step in §3 so this runbook stays the source of truth.
• The baseline absorbs everything currently in the Drizzle schema, so one-off feature migrations created since the last regeneration (e.g. an interim QTI migration) collapse into it automatically — that is expected.