linear-db-migrations

name	linear-db-migrations
description	Canonical linear DB migrations under infra/k8s/base/ops/source/database/linear-migrations, ops bundles, env keys, and runners. Use when adding or changing SQL migrations, K8s DB bootstrap, or scripts/database in this repo.
version	1.0.0

Linear database migrations (Metaboost)

When to use

• Adding or changing files under infra/k8s/base/db/source/, infra/k8s/base/ops/, or scripts/database/.
• Wiring DB credentials in Compose, Kubernetes manifests, or env templates/examples.

Greenfield-only SQL authoring

Treat each chain as ordered fresh applies: migration NNNN may assume schema and data from all earlier files in that chain only.

• Prefer plain CREATE, ALTER … ADD, DROP, INSERT when existence/absence is guaranteed by predecessors.
• Avoid CREATE … IF NOT EXISTS, DROP … IF EXISTS, seed INSERT … ON CONFLICT, and INSERT … WHERE NOT EXISTS unless an earlier migration leaves real ambiguity.
• Avoid DO $$ blocks that probe information_schema solely to support legacy shapes removed from the canonical chain.
• The migration runner creates linear_migration_history before applying files; do not add another CREATE TABLE linear_migration_history inside 0001_* (or later) that would duplicate it.

Single source of truth

• Canonical forward-only SQL: infra/k8s/base/ops/source/database/linear-migrations/app/ and infra/k8s/base/ops/source/database/linear-migrations/management/ (ordered 0001_*.sql, ...).
• Bootstrap init (docker-entrypoint-initdb.d): infra/k8s/base/db/source/bootstrap/ — shell steps 0001 / 0002, then 0003_apply_linear_baselines.sh with generated 0003a_app_linear_baseline.sql.gz / 0003b_management_linear_baseline.sql.gz.
• Generated bootstrap artifacts:
  • infra/k8s/base/db/source/bootstrap/0003a_app_linear_baseline.sql.gz
  • infra/k8s/base/db/source/bootstrap/0003b_management_linear_baseline.sql.gz
  • Generated by scripts under scripts/database/; do not hand-edit.

Runner and validation

• Apply migrations: bash scripts/database/run-linear-migrations.sh --database app|management (always pass --database; there is no default).
• K8s schema reset (checksum mismatch): bash scripts/database/run-ops-db-schema-reset-k8s.sh or npm run db:ops:schema-reset:k8s (requires K8S_NAMESPACE; runs drop → rebootstrap → migrate → verify → superuser-create jobs).
• Credentials: app migrations use DB_APP_MIGRATOR_USER, DB_APP_MIGRATOR_PASSWORD, DB_APP_NAME, DB_HOST, and DB_PORT. Management migrations use DB_MANAGEMENT_MIGRATOR_USER, DB_MANAGEMENT_MIGRATOR_PASSWORD, DB_MANAGEMENT_NAME, DB_HOST, and DB_PORT. Optional: infra/config/local/db.env when keys are unset before sourcing.
• K8s wrapper: bash scripts/database/run-linear-migrations-k8s.sh (--database required); validates the same keys from Secrets.
• Validate: bash scripts/database/validate-linear-migrations.sh (and --check-db to compare on-disk checksums to linear_migration_history when a DB is available).
• Regenerate baseline artifacts:
  • bash scripts/database/generate-linear-baseline.sh
• Verify generated artifacts are committed and up to date:
  • bash scripts/database/verify-linear-baseline.sh
• Runner bootstraps linear_migration_history before numbered .sql files run (see Greenfield-only SQL authoring above).

Ops bundle (cache busting)

• infra/k8s/base/ops/kustomization.yaml must list every .sql file under the app and management source directories so the ops jobs ConfigMaps stay in sync.
• The ops migration-runtime ConfigMap also bundles verify-bootstrap-contract.sh and rebootstrap-full-bootstrap.sh for suspended CronJobs metaboost-db-verify-bootstrap-contract, metaboost-db-rebootstrap-roles, and metaboost-db-drop-everything.
• Kustomize may load paths outside the ops directory; when building, use e.g. kubectl kustomize infra/k8s/base/ops --load-restrictor LoadRestrictionsNone.
• Local operator check: make db_verify_bootstrap_contract (wraps scripts/database/verify-bootstrap-contract.sh).

Environment keys (admin vs image)

• Authoritative in secrets and generated env: owner keys (DB_APP_OWNER_*, DB_MANAGEMENT_OWNER_*) for bootstrap, migrator keys (DB_APP_MIGRATOR_*, DB_MANAGEMENT_MIGRATOR_*) for linear migrations, plus read-write and read keys and DB_APP_NAME / DB_MANAGEMENT_NAME (see infra/config/env-templates/db.env.example and local infra/config/local/db.env after scripts/local-env/setup.sh).
• The postgres container image still reads POSTGRES_USER / POSTGRES_PASSWORD / POSTGRES_DB at runtime—map those from DB_APP_OWNER_USER / DB_APP_OWNER_PASSWORD and DB_APP_NAME in Compose or Deployment env, not the reverse.

Cross-repo invariants

• Canonical forward-only trees under infra/k8s/base/ops/source/database/linear-migrations/.
• Generated bootstrap baseline artifacts 0003a and 0003b are machine-derived and committed.
• Same npm script naming model in root package.json (db:migrate:linear:*, db:validate:linear, etc.). Product-specific naming differences are expected.

Documentation

• docs/development/DB-MIGRATIONS.md – full runbook and contracts.

Related skills

• database-schema-naming – snake_case schema rules.
• argocd-gitops-push – push reminder when k8s paths change.
• generate-data-sync – seeders when schema changes.