Data Models, Persistence, and Schema Management

Overview

This section covers the database bootstrapping path used by the backend to make sure schema state matches application expectations before request handling begins. The startup path in backend/src/main.py replays SQLAlchemy metadata, then applies schema helper functions so tables created from older layouts can be upgraded in place.

The same behavior is validated by backend/tests/test_schema_migrations.py, which builds a legacy applications table, runs the schema helper, and verifies that the archived column is added without breaking existing rows. The persistence layer is therefore treated as schema-aware: long-lived environments must be explicitly brought forward with the schema application step after deploys so old tables can be reconciled with current code.

Architecture Overview

flowchart TD
    subgraph RuntimeBootstrap [Application Startup]
        MainEnsure[ensure_database_schema]
        LocalModels[Import src infra mysql models]
        CreateAll[Base metadata create_all]
        ArchivedColumn[ensure_application_archived_column]
        ReviewStatusColumn[ensure_application_review_status_column]
        UserRoleColumns[ensure_user_role_columns]
    end

    subgraph PersistenceLayer [Persistence Layer]
        Session[SQLAlchemy Session]
        Repo[SQLAlchemyHousingApplicationRepository]
        LegacyTable[applications table]
    end

    subgraph Validation [Schema Validation]
        Tests[test_schema_migrations]
        LegacySetup[_create_legacy_applications_table]
    end

    MainEnsure --> LocalModels
    LocalModels --> CreateAll
    CreateAll --> ArchivedColumn
    ArchivedColumn --> ReviewStatusColumn
    ReviewStatusColumn --> UserRoleColumns
    MainEnsure --> Session
    Session --> Repo
    Repo --> LegacyTable
    Tests --> LegacySetup
    Tests --> ArchivedColumn
    Tests --> Repo

Component Structure

1. Schema Bootstrapping

ensure_database_schema (backend/src/main.py)

[!IMPORTANT] The schema bootstrap is not just a one-time table creation path. It is also the operational mechanism that replays base schema creation and applies idempotent column upgrades for legacy databases.

ensure_database_schema is the startup-time schema entry point used by the FastAPI application. It imports src.infra.mysql.models locally so SQLAlchemy metadata is fully registered before Base.metadata.create_all(bind=engine) is called, then applies schema helper functions for column-level upgrades.

Behavior observed in code:

  • Replays all SQLAlchemy model metadata into the configured engine.
  • Applies ensure_application_archived_column(engine).
  • Applies ensure_application_review_status_column(engine).
  • Applies ensure_user_role_columns(engine).
  • Logs SQLAlchemyError failures and re-raises them.

Public methods

Method Description
ensure_database_schema Creates or reconciles the database schema at startup and fails fast if schema creation cannot be completed.

Failure handling

  • Any SQLAlchemyError is logged through logging.getLogger(__name__).error(...) with exc_info.
  • The exception is re-raised so startup does not continue with an incomplete schema.

Bootstrapping flow

sequenceDiagram
    participant App as Application startup
    participant Main as ensure_database_schema
    participant Models as src infra mysql models
    participant Meta as Base metadata
    participant Arch as ensure_application_archived_column
    participant Review as ensure_application_review_status_column
    participant Roles as ensure_user_role_columns

    App->>Main: call ensure_database_schema
    Main->>Models: import models
    Main->>Meta: create_all bind engine
    Main->>Arch: apply archived column upgrade
    Main->>Review: apply review status column upgrade
    Main->>Roles: apply user role column upgrade
    Main-->>App: schema ready

2. Schema Upgrade Helper

ensure_application_archived_column (backend/src/infra/mysql/schema.py)

The archived-column helper is the idempotent migration primitive exercised by the tests. It upgrades an existing applications table by adding the archived column when the column is missing, and it preserves existing records.

Behavior verified by tests:

  • Adds archived to a legacy applications table that does not already contain it.
  • Leaves existing rows unarchived after the upgrade.
  • Can be executed multiple times without changing the already-upgraded schema.

Public methods

Method Description
ensure_application_archived_column Adds the archived column to applications when missing and is safe to run more than once.

Idempotent migration flow

flowchart TD
    Start([Run ensure_application_archived_column])
    Check{archived column exists}
    Add[Add archived column]
    Seed[Keep existing rows unarchived]
    Done([Return successfully])
    Noop[No schema change]

    Start --> Check
    Check -->|No| Add
    Add --> Seed
    Seed --> Done
    Check -->|Yes| Noop
    Noop --> Done

3. Persistence Repository Behavior

SQLAlchemyHousingApplicationRepository (backend/src/infra/mysql/housing_application_repository.py)

The repository is exercised as the persistence boundary for the applications table. The schema tests prove that it is able to operate against a legacy database and still expose current application behavior after the archived column is introduced.

Constructor dependencies

Type Description
Session SQLAlchemy session used for database reads and writes.

Public methods

Method Description
create Persists a housing application record.
get_by_id Returns a housing application by primary key when present.
list_recent Returns recently created applications and triggers schema reconciliation for legacy storage layouts.
archive Removes the targeted row from the applications table and reports whether a row was deleted.

Schema-aware behavior proven by tests

  • list_recent() can run against a legacy applications table and still return data after the archived column is added.
  • archive(1) returns True when the row exists and is deleted.
  • A second archive(1) call returns False because the row is already gone.

Repository upgrade and archive flow

sequenceDiagram
    participant Test as test_schema_migrations
    participant Repo as SQLAlchemyHousingApplicationRepository
    participant Helper as ensure_application_archived_column
    participant DB as applications table

    Test->>Repo: list_recent()
    Repo->>Helper: reconcile archived column
    Helper->>DB: add archived column if missing
    Repo->>DB: query rows
    DB-->>Repo: legacy rows with archived state
    Repo-->>Test: applications list

    Test->>Repo: archive(1)
    Repo->>DB: delete row id 1
    DB-->>Repo: deleted true
    Repo-->>Test: True

    Test->>Repo: archive(1)
    Repo->>DB: delete row id 1
    DB-->>Repo: deleted false
    Repo-->>Test: False

4. Data Model: Legacy Applications Table

applications (backend/tests/test_schema_migrations.py)

The test helper _create_legacy_applications_table defines the pre-upgrade shape of the applications table. This is the schema that the helper and repository must accept before the archived column is present.

Column Type Description
id Integer Primary key.
email String(255) Applicant email address.
phone String(64) Applicant phone number.
country String(128) Applicant country.
property_type String(128) Property category submitted by the applicant.
purchase_intent String(64) Purchase intent value.
budget_range String(64) Budget range text.
savings String(64) Savings value.
income String(64) Income value.
income_currency String(16) Currency for income.
employment_status String(128) Employment status text.
financing_consent String(32) Financing consent indicator.
full_name String(255) Applicant full name.
agree_policy Boolean Privacy-policy consent flag.
receive_updates Boolean Updates opt-in flag.
connected_accounts Text JSON text for connected account payloads.
created_at DateTime(timezone=True) Creation timestamp with CURRENT_TIMESTAMP default.

Constraints

  • uq_applications_full_name_phone enforces uniqueness across full_name and phone.

Legacy seed data used in tests

  • The test inserts one row into this table with connected_accounts as "{}" and consent flags already populated.
  • That row is used to verify that upgrading the schema does not delete or corrupt existing data.

Schema Application and Environment Bootstrapping

The code path that is visible in the repository is the application startup bootstrap in backend/src/main.py. It replay-builds the database from current SQLAlchemy metadata and then applies post-create schema helpers. The test suite exercises the same upgrade path against a temporary SQLite database so the behavior is verified independently of deployment environment.

Operational behavior

  • New environments receive the current table layout through Base.metadata.create_all(bind=engine).
  • Legacy environments receive column-level reconciliation through ensure_application_archived_column, ensure_application_review_status_column, and ensure_user_role_columns.
  • The bootstrap is explicit and process-driven: schema reconciliation happens when the application invokes ensure_database_schema.

Failure handling

  • Schema bootstrap failures are treated as fatal.
  • The SQLAlchemyError path is logged and then re-raised, which prevents the service from continuing with partial schema state.

Environment-specific bootstrapping expectation

Long-lived environments require the explicit schema application step after deploys so current metadata and schema helpers can reconcile pre-existing tables before the service is relied on for writes or deletions.

Testing and Verification

backend/tests/test_schema_migrations.py

This file validates the upgrade path with a fully constructed legacy schema. The tests show that schema application is not just theoretical: it is exercised against a real database engine and inspected after migration.

Test functions

Method Description
_create_legacy_applications_table Creates the legacy applications table and seeds a row for upgrade verification.
test_repository_ensures_archived_column_when_missing Verifies repository access upgrades the table and exposes application rows with archived resolved to False.
test_ensure_application_archived_column_adds_column_when_missing Verifies the helper adds archived, preserves row state, and is safe to run twice.
test_repository_archive_call_deletes_existing_row_after_schema_upgrade Verifies delete behavior after schema upgrade and confirms repeated archive calls become no-ops.

Test flow: helper idempotence and row preservation

sequenceDiagram
    participant Test as test_ensure_application_archived_column_adds_column_when_missing
    participant Helper as ensure_application_archived_column
    participant DB as applications table
    participant Inspector as inspect

    Test->>Helper: ensure_application_archived_column(engine)
    Helper->>DB: add archived column
    Helper-->>Test: return
    Test->>Inspector: inspect columns
    Inspector-->>Test: archived present
    Test->>DB: select archived from row 1
    DB-->>Test: archived false

    Test->>Helper: ensure_application_archived_column(engine)
    Helper->>DB: no-op
    Helper-->>Test: return

Error Handling

The schema bootstrap path is defensive and fail-fast.

  • ensure_database_schema catches SQLAlchemyError, logs the failure with stack information, and re-raises.
  • The migration helper is tested for repeatability: running it a second time must not alter the already-upgraded database.
  • Repository archive behavior is explicit: once a row is deleted, a second archive call reports False instead of pretending the row still exists.

Dependencies

Runtime

  • SQLAlchemy
  • Base and engine from src.config.database
  • src.infra.mysql.models for metadata registration
  • logging for bootstrap failure reporting

Test support

  • create_engine
  • sessionmaker
  • inspect
  • text
  • SQLite in-memory and file-backed engines used for schema validation

Integration Points

  • backend/src/main.py performs the startup schema replay.
  • backend/tests/test_schema_migrations.py validates legacy-table compatibility and idempotent helper behavior.
  • backend/src/infra/mysql/housing_application_repository.py exercises the upgraded schema while listing and archiving applications.
  • backend/src/infra/mysql/schema.py contains the column-level upgrade helper used by both startup and repository flows.

Key Classes Reference

Class Responsibility
main.py Replays base metadata and applies schema helpers during application startup.
schema.py Hosts the idempotent ensure_application_archived_column migration helper.
housing_application_repository.py Reads and mutates housing applications while operating against upgraded legacy schemas.
test_schema_migrations.py Verifies legacy schema upgrades, idempotence, and archive behavior.