Skip to main content

Schema Revisions Runbook

db/schema-revisions/ contains the immutable, checked-in chain of canonical schema-revision packages for Gateway's Cloud Spanner (PostgreSQL dialect) database. //tools/sqldelight:schema_revision_tool generates and verifies each package from the SQLDelight source tree; scripts/schema-revisions/run.sh is the sole deployment entrypoint, gated by CI and executed automatically by deploy.yml before any Cloud Run revision is promoted. This page covers the day-to-day authoring flow and the expand/contract pattern for unsafe changes.

TL;DR​

  1. Edit the SQLDelight .sq sources under libs/schema/src/main/sqldelight/.
  2. Write db/schema-revisions/<revision>/revision.yaml (base_schema_digest plus any companions) and generate its artifacts with bazel run //tools/sqldelight:schema_revision_tool -- generate --src-dir libs/schema/src/main/sqldelight --package com.myriad.gateway.schema.generated --database GatewayDatabase --previous-schema <prior target-schema.sql> --revision db/schema-revisions/<revision>/revision.yaml --out-dir db/schema-revisions/<revision> (add typed manual-ddl/backfill companions for anything the compiler can't derive automatically — see db/schema-revisions/README.md).
  3. Run scripts/schema-revisions/run.sh --dry-run against staging.
  4. Open the PR — CI runs the canonical compiler, runner, and CLI test targets, which reject unsupported/destructive structural transitions unless a correctly typed manual-ddl companion names its rollout phase and postcondition.
  5. Merge + release. deploy.yml runs scripts/schema-revisions/run.sh before flipping Cloud Run traffic; a failed revision aborts the deploy.

Revision tracking​

Every applied revision package is recorded so reruns are idempotent — already-applied revisions are skipped. Each package's manifest.json and .sha256 sidecars pin its structural.sql/target-schema.sql content; verification rejects live source drift against the package's target-schema.sql snapshot. When no revision.yaml package exists yet, baseline-manifest.json and baseline-target-source.sq/.sha256 attest the checked-in canonical-sqldelight-snapshot.sha256 as revision zero — the required predecessor of the first incremental package (see scripts/schema-revisions/attest.sh).

Authoring guidelines​

  • Additive first. New tables, columns, indexes are safe to apply before any code change ships, and safe to leave in place during a rollback.
  • No backfill in the same structural statement. Large data backfills belong in a typed manual-ddl/backfill companion, not inline in structural.sql.
  • PostgreSQL-dialect, Spanner-compatible. Avoid unsupported constructs (sequences, stored procedures). The canonical compiler derives structural.sql/target-schema.sql directly from the .sq files under libs/schema/src/main/sqldelight/.
  • Name revisions descriptively (e.g. add-organization-tax-runtime-configs), not with a monotonic version prefix — the revision chain is ordered by revision.yaml's base_schema_digest linkage, not filename order.

The canonical compiler/runner/CLI gate​

The canonical schema_revision_tool test targets (//tools/sqldelight:spanner_schema_revision_tool_test, //tools/sqldelight:spanner_revision_runner_test, //tools/sqldelight:spanner_revision_cli_test) run in CI and reject structural transitions that break rolling deploys where current and replacement Cloud Run revisions serve traffic simultaneously — unsupported column-type changes, drops, and renames — unless the transition is expressed as a typed manual-ddl companion.

Expand/contract pattern​

Breaking schema changes ship over multiple releases:

  1. Expand. Add the replacement shape alongside the current one. New column, new table, dual-write from the application. Deploy.
  2. Migrate. Backfill the replacement shape from the current one via a typed backfill companion. Read path learns to prefer the replacement shape; current shape becomes a fallback.
  3. Flip. Readers stop touching the retained shape entirely. Deploy. Wait at least one Cloud Run revision lifecycle in production so no retired revisions are still scheduling work against it.
  4. Contract. Drop the retained shape in a new revision, expressed as a manual-ddl companion with phase: contract, a risk, and a reason justifying the drop and linking the expand-phase revision it contracts.

Running locally​

# Default: production instance/database (requires appropriate IAM)
scripts/schema-revisions/run.sh --git-sha "$(git rev-parse HEAD)" --dry-run

# Staging (separate project, same instance/database names)
SPANNER_PROJECT=peakgateway-staging \
SPANNER_INSTANCE=gateway-spanner \
SPANNER_DATABASE=gateway-db \
  scripts/schema-revisions/run.sh --git-sha "$(git rev-parse HEAD)" --dry-run

# Apply
scripts/schema-revisions/run.sh --git-sha "$(git rev-parse HEAD)"

The script prints the pending revision chain, applies each package in order, and records it. If every tracked revision is already recorded, it exits successfully without applying anything.

CI/CD integration​

deploy.yml runs scripts/schema-revisions/run.sh before any Cloud Run revision is promoted:

  • Staging: runs on every push to main, dry-running then applying against gateway-db in the peakgateway-staging project for the exact main SHA.
  • Production: runs on every release event, targeting the production gateway-db and replaying the complete chain for the checkout SHA. A failed revision fails the job and the subsequent deploy-production job does not start.

The runner uses the same WIF service account as the deploy, so no long-lived Spanner credentials are stored in GitHub.

Incident response​

If a revision fails mid-deploy:

  1. The deploy job exits non-zero; Cloud Run traffic is not flipped.
  2. Check the runner log for the failing statement.
  3. If the failure is partial (some DDL landed, some didn't):
    • Inspect the target database against the package's manifest.json to see what's recorded as applied.
    • The runner only marks a package applied after it lands cleanly, so a partially-applied package will not be marked done. Fix the companion/structural DDL, push a corrected revision, and re-run the deploy.
  4. For data loss scenarios, consult Spanner Disaster Recovery.