Migrations and generated types
Toolora prepares schema changes as forward SQL files in supabase/migrations/.
The same active chain builds local databases and supplies the pending files for
the persistent shared database. Generated TypeScript types and the reviewed
schema catalogue are derived contracts that must agree with that chain.
Use this procedure for an actual intended schema change. Local preparation and
shared publication are separate stages. The shared schema runbook
owns operational commands; feature-specific compatibility steps add to that path.
Schema delivery owns the wider
application-compatibility subject. The runbook’s historical production name
identifies AX42 infrastructure; Toolora’s shared environment remains pre-launch.
Prerequisites and targets
Section titled “Prerequisites and targets”Work in the task’s clean Git worktree with the repository’s development dependencies,
Docker and pinned Supabase CLI. The Makefile uses supabase@2.116.0; an available
CLI upgrade is not part of a migration change. Check command help for that version
before using additional flags. Use Node 24 for frontend and handbook verification.
The local project identity and ports come from supabase/config.toml. Several
worktrees using its unchanged project ID can address the same local stack. Check
existing projects and ports before starting, resetting or stopping one. Give an
isolated stack its own deliberate configuration; a different directory alone does
not establish ownership. The populated upgrade target creates its own disposable
project and ports.
Database test/seed/reset commands obtain their connection from that stack’s
supabase status. Do not substitute a shared database URL. Backend test execution
requires absent backend/frontend dotenv files, including symlinks, a cleared
environment and synthetic credentials as described in the development guide.
Shared functional checks use the saved Vi Målar identities; follow the shared-access
preparation when those checks are required.
The shared database uses the existing AX42 operator path and installed component broker. Keep credentials, connection strings, backup contents and private business data out of commands copied into review artifacts. Vi Målar is bound to its real Fortnox company; migration verification does not authorize a provider export, financial attest, payroll-triggering approval or customer send.
1. Create the forward change
Section titled “1. Create the forward change”-
Fetch the intended
mainand inspect its newest active migration. Keep the task on an unmerged branch; after its PR merges, start from currentmain. -
Create the new file through the repository command, replacing the example name with the intended change:
Terminal window make migration NAME=describe_intended_change -
Edit the generated UTC
YYYYMMDDHHMMSS_<name>.sql. Its version must be unique and sort after the newest version onmain. If an unmerged new file becomes out of order, regenerate it after updating the branch and recheck the change. -
Keep migration-level transaction control out of the file. The CLI runs each complete file in its own transaction. SQL function-body blocks do not give the migration permission to commit the file halfway through.
-
Keep compatible application changes, generated types, catalogue changes and any cutover instructions in the same reviewed change.
A migration already on main is immutable. New corrections use new forward files;
never rename an applied file, replay the shared baseline or repair history merely
to obtain a passing check. The CI guard checks changed/deleted merged files,
new-file names/order, duplicate versions and explicit transaction-control lines.
It is not a complete SQL correctness or data-migration analysis.
The guard retains four exact historical hash pairs: the three earlier pre-release corrections for worker conversations, hour provenance and credit provenance, plus the separately approved TotalToPay correction. The runbook records all four as already applied. They grant no new amendment permission. Do not require their versions to be pending on a later release or copy their bypasses into a new change.
2. Build locally and regenerate the contracts
Section titled “2. Build locally and regenerate the contracts”Use the checkout-owned stack. make start applies migrations when creating its
volume and then compares actual version IDs with the checkout. A reused volume is
not considered current merely because its migration count matches.
make startmake resetmake typesmake db-testmake reset destroys that local database and rebuilds the active chain. Run it
only when that local stack may be discarded. make db-test also rebuilds when
committed tenant rows remain, because baseline assertions require the state left
by migrations. Integration/seed fixtures belong to the local stack; neither reset
is a shared-database operation.
make types generates the public-schema contract at
frontend/src/types/supabase.ts. It pipes CLI output through
normalize_supabase_types.py, writes a temporary file and replaces the tracked
file only after successful generation. Normalization removes selected monthly
partition children and stabilizes known CLI formatting differences; it does not
supply missing business rules. make types-check regenerates and requires no Git
diff. Inspect and commit the generated result; do not edit it manually.
Supabase’s type-generation guidance
explains introspection. Toolora’s Make target supplies the local target, schema
selection and normalization.
Types describe row/insert/update shapes, relationships and callable signatures. They do not prove authorization, function execution identity, trigger behaviour or safe transformation of existing data. Keep the relevant RLS and transaction checks.
The schema test compares the built catalogue with supabase/schema_catalogue.txt.
After an intended schema change, regenerate it from the same local stack and
review the diff. This template obtains DB_URL from the pinned local CLI inside
a subshell; it does not use a caller-supplied connection:
( eval "$(npx --yes supabase@2.116.0 status -o env)" backend/.venv/bin/python scripts/db/schema_catalogue.py \ --db-url "$DB_URL" --write supabase/schema_catalogue.txt)The renderer compares selected public/private objects and Toolora’s Storage
policies, normalizing runtime partition details and hashing function bodies.
This is a schema representation, not a dump, a row-value comparison or a complete
policy audit. Toolora uses it because db diff cannot reliably express this
schema’s RLS differences. Never regenerate a baseline merely to conceal an
unintended difference. Rerun the affected contract checks after reviewing the change.
3. Validate both a clean build and a populated upgrade
Section titled “3. Validate both a clean build and a populated upgrade”| Command or evidence | Target and effect | Expected result |
|---|---|---|
make ci-migrations BASE=origin/main |
Git comparison; no database mutation | Forward-only/order/name/transaction-line guards accept the candidate. |
make start / make reset |
Owned local stack; reset discards its data | Actual migration IDs match the checkout; the complete chain builds. |
make db-test |
Owned local database, possibly rebuilt first | Schema, grants, RLS and functional contracts pass without skips. |
make types / make types-check |
Local public schema; rewrites generated types | Reviewed generated file is current; check leaves no diff. |
make upgrade-test SHA=<full-sha> FROM=<observed-version> |
Disposable local project, synthetic committed history | Complete pending files preserve tested values, ledger and target catalogue; JSON names the commit/tree/prefix. |
make ci-plan BASE=origin/main and selected make ci-* |
Candidate’s affected surfaces | Required local checks pass with their prerequisites; record actual results. |
scripts/ops/release.sh <full-sha> --check |
AX42 operator, current ledger and publication guards | Prints the candidate’s real pending set; no backup, pause or publication. |
scripts/ops/release.sh <full-sha> |
Authorized AX42 shared release | Verified schema/status and compatible components, with phase evidence. |
A clean build proves that the chain creates an acceptable empty schema. A
populated upgrade tests a different starting state. make upgrade-test defaults
to the candidate’s supabase/published-schema-version; that reviewed baseline is
independent of the current API/frontend commit. Use an observed exact prefix for
release recovery or when the shared schema has advanced.
The rehearsal creates a unique local Postgres 17 project, applies that prefix,
commits synthetic legacy invoice/credit data for two companies, then applies the
complete suffix through the pinned CLI. Only construction of legacy fixtures
uses a temporary trigger bypass. The actual upgrade runs with normal triggers
as non-superuser postgres, and the checks verify values, history and catalogue.
Known cost-allocation scenarios and an additional historical start exercise
specific backfills. The project is stopped and removed afterwards.
These scenarios do not represent every historical record. Add a focused case
when a new defect establishes one. Do not rehearse on the shared database inside
a rollback transaction. Use make ci-plan to select the full required surfaces;
make check is a local subset. A passing type file or empty-schema run does not
replace populated upgrade evidence.
4. Review and publish the exact candidate
Section titled “4. Review and publish the exact candidate”Follow the review and publication path.
Commit and push the first change through a draft PR. Before making it ready,
inspect the complete diff and run the selected local checks. Record checked SHA,
commands, outcomes and concrete gaps. Require fresh candidate CI. A schema
candidate must reach reviewed main with green CI before the operator release;
application-only publication has its own pre-launch branch policy.
On AX42, check out the exact pushed full SHA and read the current runbook and
feature-specific cutover instructions. Run --check first to inspect the actual
ledger prefix and pending set. It is an inspection, not the backup/release phase.
Then execute the authorized release command shown in the table.
The operator holds /srv/.toolora-release.lock across the window. Component
publishers and rollbacks share that lock and verify inherited lock identity.
Direct SQL and other writers remain separate concerns. The installed schema CLI
reads the fixed broker projection, accepts the loopback pooler’s session port,
uses the pinned binary and separates the password from its command arguments.
Do not use --linked, a local Infisical login or an ad hoc connection string as
another release route.
Run the operator shell as the existing operator account. Its reviewed commands
invoke fixed installed privileged helpers through their specified sudo path;
those helpers are not a template for running application probes as root. Ordinary
API-configuration commands on AX42 run as dev with toolora-secrets, following
AGENTS.md. Keep any new files under /home/dev readable by their intended owner.
For pending SQL, the command finds matching successful CI upgrade evidence. It
verifies workflow/repository, artifact digest, full candidate tree and the actual
starting prefix. CI retains the artifact for 14 days. A byte-identical squash
merge tree can match; an arbitrary green branch or a rehearsal from another
prefix cannot. If evidence expired or the prefix changed, rehearse again with
SHA/FROM, transfer artifacts/upgrade-result.json to the release checkout and
pass --upgrade-result as the runbook specifies.
5. Follow the guarded release phases
Section titled “5. Follow the guarded release phases”sequenceDiagram participant D as Developer participant L as Local stack and CI participant O as AX42 operator participant S as Shared database participant A as API and frontend D->>L: Forward files, types, catalogue and upgrade checks L-->>D: Checked candidate and upgrade result D->>O: Reviewed main candidate, exact full SHA O->>S: Read actual ledger under release lock O->>O: Validate matching tree and starting prefix O->>O: Request and verify fresh shared backup O->>A: Stop old API writer O->>S: Invalidate status, push complete pending files S-->>O: Per-file commits and actual ledger O->>S: Verify catalogue and publish schema status O->>A: Publish API forward-only, then frontend A-->>O: Component health and version observations
| Phase | Guard and observed result | Important boundary |
|---|---|---|
| Candidate/prefix | Exact checkout, pushed source, component ancestry and real ledger prefix | Refusal occurs before schema work if the candidate would remove published work. |
| Upgrade evidence | Full tree and actual prefix agree with successful retained evidence | A newer or older tested starting prefix is not interchangeable. |
| Fresh backup preflight | Existing toolora-db-backup.service, successful result, complete marker, checksums and dump catalogues |
Normal release requests freshness within one hour. The standalone preflight default is 26 hours. This does not perform a restore test. |
| Writer pause/status invalidation | Old API stopped and public schema status invalidated | Direct browser Supabase clients and other writers are not stopped; follow feature-specific cutovers. |
| Pending files | CLI applies every pending file and actual history must match the candidate | Each file commits independently; the whole pending set is not one transaction. |
| Verification/status | Catalogue agrees, then installed projector reads actual ledger and publishes status | An old local receipt is not the status source. |
| Components | Matching API publishes with forward-only handling before frontend; component health/readiness checks apply | A schema failure cannot be fixed by silently restarting an older incompatible API. |
With no pending SQL, this path does not request a schema backup or stop the writer; unchanged components are skipped. Application publication and schema migration are not a single atomic operation.
6. Recover from a refused or interrupted release
Section titled “6. Recover from a refused or interrupted release”| Synthetic situation | What remains authoritative | Recovery |
|---|---|---|
New file sorts before the newest file on main |
Merged files and applied versions | Recreate the unmerged new file in order; do not rename applied history. |
| A schema change leaves stale TypeScript types | Local migrated schema and intended API contract | Regenerate through make types, inspect the diff and rerun type/consumer checks. |
| Catalogue differs unexpectedly | Actual built objects and reviewed migration intent | Diagnose first. Regenerate only the intended normalized catalogue. |
| Upgrade result has the wrong tree/prefix | Current candidate tree and actual shared ledger | Rehearse that exact combination and provide fresh evidence; do not widen acceptance. |
| Backup preflight fails or the release lock is held | Current shared state; mutation has not started at that phase | Resolve the actual failure or wait for the other owner. Do not bypass the lock or substitute an old backup receipt. |
| File two fails after file one committed | File one remains applied; file two remains pending | Read observed history/error, correct forward and rehearse from the new prefix before retrying. |
| All SQL committed before interruption | Actual ledger and catalogue | The operator can verify the catalogue, rebuild status and finish component publication without replaying applied files. |
The release writes phase evidence under artifacts/releases/<UTC-time>-<sha>/
and stops at a failed phase. After a schema failure the API stays stopped and
status remains invalid, blocking ordinary publication. There is no automatic
reverse migration or history repair. Application rollback retains the schema
and refuses a different migration set; schema recovery requires a compatible
forward correction or the separately authorized backup-restore route.
After a completed schema release, record exact commit, fresh backup marker,
pending/applied versions, catalogue comparison and observed component versions.
Advance supabase/published-schema-version through the receipt PR only to the
observed latest applied version. Do not advance it for an incomplete release.
API and frontend versions can differ when only one component changed.
The delivery PR distinguishes executed checks from source inspection and reused results with identical inputs. Operator tests substitute host commands and ledger state; they prove refusal/phase ordering in their scenarios, not a fresh backup or successful live restore. Existing populated upgrade evidence covers its synthetic fixtures and named version. No shared mutation is implied by this article.