Deployment, Operations, and Observability - Database Rollout, Idempotent Schema Application, and Environment Drift Handling

Overview

This deployment path exists to keep production database state aligned with the codebase when the backing MySQL volume is long-lived and no longer starts from a clean datadir. The rollout is split between initial bootstrap SQL under platform/deployment/prod/initdb/, replayable migration files, and application-level schema guards in backend/src/main.py so that both fresh and existing environments can converge on the same schema.

The operational focus here is not just creation of tables, but safe reapplication. The schema application flow is designed to wait for MySQL health, replay migration steps without duplicating them, and repair legacy schema drift such as missing columns in the applications table. The tests in backend/tests/test_schema_migrations.py verify that the repository can upgrade an older schema in place and preserve existing rows.

Architecture Overview

flowchart TB
    subgraph ProdDeploy[Production Deployment]
        Operator[Release Operator]
        ApplySchema[platform database apply_schema script]
        Mysql[MySQL service with persistent volume]
        InitDb[Production initdb SQL]
        Migrations[Replayable migration SQL]
        AppBoot[Backend schema startup helper]
        LegacyTest[Schema migration tests]
    end

    Operator --> ApplySchema
    ApplySchema -->|waits for health| Mysql
    ApplySchema -->|bootstrap on fresh datadir| InitDb
    ApplySchema -->|replays safely| Migrations
    ApplySchema -->|keeps runtime aligned| AppBoot
    AppBoot -->|creates missing tables and columns| Mysql
    LegacyTest -->|creates legacy schema| Mysql
    LegacyTest -->|asserts drift repair| AppBoot

Rollout Runbook

Schema Rollout Script

platform/database/apply_schema.sh

This script is the operational entry point for database rollout. It is the place where the deployment process waits for MySQL readiness, then applies schema changes in a controlled order so reruns do not duplicate work.

Key responsibilities:

  • Wait for MySQL health before touching schema state.
  • Apply bootstrap SQL for a new database.
  • Replay migration files against existing databases.
  • Support explicit post-deploy schema application when the MySQL volume already exists.

The important operational detail is that this script is needed even after the application image has been deployed. A long-lived MySQL volume does not go back through first-time bootstrap automatically, so schema work must be run explicitly after release.

Production Init Database Bootstrap

platform/deployment/prod/initdb/1_create_database.sql

platform/deployment/prod/initdb/2_add_otp_verifications.sql

These files represent the first-time database bootstrap path for a fresh MySQL datadir. They are part of the initialization sequence, not the ongoing migration path for an already populated volume.

Operational characteristics:

  • 1_create_database.sql is the first bootstrap step.
  • 2_add_otp_verifications.sql is the follow-on bootstrap step.
  • Both belong to the fresh-volume initialization path.

Replayable Migration Files

[!IMPORTANT] Because these files are in an initdb path, they are for first boot of a new datadir. Existing production volumes need the explicit schema application flow, not a container restart, to pick up schema changes.

platform/database/migrations/002_customer_api_tables.sql

This file is part of the replayable migration set that the rollout script applies after bootstrap. Its role is to bring an existing database up to the current application schema without relying on a clean initialization path.

Operational characteristics:

  • Applied during schema replay, not only on initial bootstrap.
  • Should be safe to rerun through the schema application driver.
  • Fits into the production rollout path alongside the initdb files.

Database Schema

db/migrations/20250101090000_create_us_consent_tables.sql

This migration creates the US consent-related tables using CREATE TABLE IF NOT EXISTS, which makes the migration replay-friendly.

consents

Column Type Notes
id text Primary key
partner_id text Required
user_reference text Required
country text Required
purpose text Required
categories text[] Required
status text Required
consent_text_version text Required
expires_at timestamptz Required
created_at timestamptz Defaults to now()
updated_at timestamptz Defaults to now()
Column Type Notes
id text Primary key
consent_id text References consents(id)
partner_id text Required
request_id uuid Required
disclosure_hash text Required
ip inet Nullable
user_agent text Nullable
created_at timestamptz Defaults to now()

Constraints:

  • UNIQUE (partner_id, request_id)

tokens

Column Type Notes
id text Primary key
consent_id text References consents(id)
access_jti text Required
refresh_token_hash text Required
expires_at timestamptz Required
revoked_at timestamptz Nullable
created_at timestamptz Defaults to now()

feature_evidence

Column Type Notes
id text Primary key
consent_id text References consents(id)
feature_set text Required
lookback_days int Required
features_json jsonb Required
inputs_digest text Required
created_at timestamptz Defaults to now()

decisions

Column Type Notes
id text Primary key
consent_id text References consents(id)
applicant_reference text Required
purpose text Required
product_type text Required
score int Nullable
decision text Required
approved_limit int Nullable
model_name text Nullable
model_version text Nullable
reasons jsonb Required
evidence_ref text Nullable
request_id uuid Required
created_at timestamptz Defaults to now()

Constraints:

  • UNIQUE (consent_id, request_id)

Legacy Applications Table Shape Used in Migration Tests

backend/tests/test_schema_migrations.py

The test suite builds an older applications table shape to prove the schema repair path. This is the schema that exists before the archived column is introduced.

Column Type Notes
id Integer Primary key
email String(255) Required
phone String(64) Required
country String(128) Required
property_type String(128) Required
purchase_intent String(64) Required
budget_range String(64) Required
savings String(64) Required
income String(64) Required
income_currency String(16) Required
employment_status String(128) Required
financing_consent String(32) Required
full_name String(255) Required
agree_policy Boolean Required
receive_updates Boolean Required, default 0
connected_accounts Text Nullable
created_at DateTime(timezone=True) Defaults to CURRENT_TIMESTAMP

Constraints:

  • UniqueConstraint("full_name", "phone", name="uq_applications_full_name_phone")
  • The legacy shape intentionally omits archived

Application Startup Schema Helper

ensure_database_schema()

backend/src/main.py

This helper runs at application startup to make sure the database schema exists before the API serves traffic. It imports the SQLAlchemy model module locally so that metadata is fully registered before create_all executes.

Behavior observed in code:

  • Imports src.infra.mysql.models locally to register SQLAlchemy metadata.
  • Calls Base.metadata.create_all(bind=engine).
  • Applies schema repair helpers:
    • ensure_application_archived_column(engine)
    • ensure_application_review_status_column(engine)
    • ensure_user_role_columns(engine)
  • Logs Failed to ensure database schema and re-raises on SQLAlchemyError.

This helper is a runtime safety net, but it does not replace explicit post-deploy schema application for existing databases. It only helps once the application process is running and able to reach the database.

Schema Migration Verification

Schema Drift Repair Tests

backend/tests/test_schema_migrations.py

This file verifies that legacy database shapes are upgraded safely and that the upgrade path is idempotent.

test_ensure_application_archived_column_adds_column_when_missing

What it proves:

  • A legacy applications table without archived can be upgraded.
  • ensure_application_archived_column(engine) adds the missing column.
  • Existing rows remain present and are treated as unarchived.
  • Running the helper a second time is a no-op.

test_repository_ensures_archived_column_when_missing

What it proves:

  • SQLAlchemyHousingApplicationRepository.list_recent() upgrades the schema when it encounters a legacy table.
  • The returned application row has archived is False.
  • The archived column is present after the repository access.

test_repository_archive_call_deletes_existing_row_after_schema_upgrade

What it proves:

  • SQLAlchemyHousingApplicationRepository.archive(1) succeeds on a repaired schema.
  • Repeating archive(1) returns False once the row is already deleted.
  • The archived column remains present after the operation.
  • The original row is removed from applications.

Legacy Schema Repair Flow

sequenceDiagram
    participant Test as Schema Test
    participant Repo as SQLAlchemyHousingApplicationRepository
    participant Helper as ensure_application_archived_column
    participant Db as SQLite Legacy Database

    Test->>Db: Create applications table without archived
    Test->>Repo: list_recent
    Repo->>Helper: Repair missing archived column
    Helper->>Db: Add archived column
    Helper-->>Repo: Column ready
    Repo->>Db: Read existing rows
    Repo-->>Test: Return rows with archived false

    Test->>Repo: archive with existing id
    Repo->>Db: Delete row
    Repo-->>Test: archive returns true

    Test->>Repo: archive same id again
    Repo->>Db: Row not found
    Repo-->>Test: archive returns false

Feature Flows

1. Production Schema Rollout on a Long Lived Volume

sequenceDiagram
    participant Operator as Release Operator
    participant Script as apply_schema script
    participant Mysql as MySQL
    participant InitDb as initdb SQL
    participant Mig as migration SQL
    participant App as Backend schema helper

    Operator->>Script: Run schema rollout
    Script->>Mysql: Wait for health
    Mysql-->>Script: Ready

    alt New database volume
        Script->>InitDb: Apply bootstrap SQL
        InitDb->>Mysql: Create database and bootstrap objects
        Mysql-->>InitDb: Success
    else Existing database volume
        Script->>Mig: Replay schema migrations
        Mig->>Mysql: Apply replayable changes
        Mysql-->>Mig: Success
    end

    Script->>App: Start application with schema guard
    App->>Mysql: create_all and column repair helpers
    Mysql-->>App: Schema aligned

2. Legacy Applications Table Repair

sequenceDiagram
    participant Test as Migration Test
    participant Repo as SQLAlchemyHousingApplicationRepository
    participant Db as Legacy Applications Table
    participant Sql as ensure_application_archived_column

    Test->>Db: Create legacy applications table
    Test->>Db: Insert existing row
    Test->>Repo: list_recent or archive
    Repo->>Sql: Repair missing archived column
    Sql->>Db: Add archived column
    Db-->>Sql: Column added
    Sql-->>Repo: Schema ready
    Repo->>Db: Read or delete row
    Db-->>Repo: Result
    Repo-->>Test: Verified upgraded behavior

Environment Drift Handling

The code path for drift handling is centered on two facts:

  • Older databases may have an applications table without archived.
  • The repository and startup helper both repair missing schema elements before operating on those rows.

The tests prove the intended behavior:

  • schema repair adds archived
  • existing rows are preserved
  • repeated repair is safe
  • deletion behavior still works after the upgrade

Observability and Validation

[!NOTE] Treat database-name drift as a release blocker. If platform/database/apply_schema.sh, the initdb SQL files, and the target database reference do not point at the same canonical name, the rollout can bootstrap one database and migrate another. Resolve any lita-ehousing and dwilar naming mismatch before production execution.

Observed validation and observability points in this section are:

  • MySQL health gating before schema changes.
  • Error logging in ensure_database_schema() with logging.getLogger(__name__).error("Failed to ensure database schema", exc_info=exc).
  • Schema assertions in tests using sqlalchemy.inspect.
  • Post-upgrade checks that confirm data remains accessible after adding missing columns.

These checks give operators a direct signal that the rollout has either converged on a valid schema or failed in a visible way.

Error Handling

The schema path handles failure in two places that are visible in code:

  • ensure_database_schema() catches SQLAlchemyError, logs the failure, and re-raises it.
  • The test helper demonstrates that missing columns can be repaired without destroying existing rows.

The operational implication is that schema work should stop on database health or SQL errors rather than continuing with a partially applied rollout.

Dependencies

  • MySQL with a persistent volume for production application data.
  • platform/database/apply_schema.sh as the rollout driver.
  • platform/deployment/prod/initdb/1_create_database.sql and platform/deployment/prod/initdb/2_add_otp_verifications.sql for fresh-volume bootstrap.
  • platform/database/migrations/002_customer_api_tables.sql for replayable schema advancement.
  • db/migrations/20250101090000_create_us_consent_tables.sql for consent-related tables.
  • SQLAlchemy metadata and engine usage in backend/src/main.py.
  • src.infra.mysql.schema helpers used by both runtime and tests.
  • SQLite-based test coverage in backend/tests/test_schema_migrations.py for legacy schema reproduction.

Testing Considerations

Use these scenarios when validating database rollout changes:

  • Fresh database bootstrap from an empty volume.
  • Re-running the schema application script against an already initialized database.
  • Existing applications table without archived.
  • Repository access to a legacy schema that requires in-place repair.
  • Repeated invocation of the repair helper to confirm idempotency.
  • Archive deletion after the schema has been upgraded.
  • Mixed deployment state where bootstrap SQL exists but the volume is not new.
  • Database-name mismatch checks before production rollout.

Key Classes Reference

Class Location Responsibility
apply_schema.sh platform/database/apply_schema.sh Orchestrates health-gated, idempotent schema rollout
1_create_database.sql platform/deployment/prod/initdb/1_create_database.sql Bootstraps a fresh production database
2_add_otp_verifications.sql platform/deployment/prod/initdb/2_add_otp_verifications.sql Adds OTP verification bootstrap objects
002_customer_api_tables.sql platform/database/migrations/002_customer_api_tables.sql Replayable customer API schema migration
20250101090000_create_us_consent_tables.sql db/migrations/20250101090000_create_us_consent_tables.sql Creates consent, receipt, token, evidence, and decision tables
ensure_database_schema backend/src/main.py Creates missing tables and repairs required columns at app startup
test_schema_migrations.py backend/tests/test_schema_migrations.py Verifies legacy schema repair and idempotent column application