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.sqlis the first bootstrap step.2_add_otp_verifications.sqlis the follow-on bootstrap step.- Both belong to the fresh-volume initialization path.
Replayable Migration Files
[!IMPORTANT] Because these files are in an
initdbpath, 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
US Consent Tables Migration
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() |
consent_receipts
| 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.modelslocally 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 schemaand re-raises onSQLAlchemyError.
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
applicationstable withoutarchivedcan 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
archivedcolumn 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)returnsFalseonce the row is already deleted. - The
archivedcolumn 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
applicationstable withoutarchived. - 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 anylita-ehousinganddwilarnaming 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()withlogging.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()catchesSQLAlchemyError, 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.shas the rollout driver.platform/deployment/prod/initdb/1_create_database.sqlandplatform/deployment/prod/initdb/2_add_otp_verifications.sqlfor fresh-volume bootstrap.platform/database/migrations/002_customer_api_tables.sqlfor replayable schema advancement.db/migrations/20250101090000_create_us_consent_tables.sqlfor consent-related tables.- SQLAlchemy metadata and engine usage in
backend/src/main.py. src.infra.mysql.schemahelpers used by both runtime and tests.- SQLite-based test coverage in
backend/tests/test_schema_migrations.pyfor 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
applicationstable withoutarchived. - 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 |