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
SQLAlchemyErrorfailures 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
SQLAlchemyErroris logged throughlogging.getLogger(__name__).error(...)withexc_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
archivedto a legacyapplicationstable 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 legacyapplicationstable and still return data after the archived column is added.archive(1)returnsTruewhen the row exists and is deleted.- A second
archive(1)call returnsFalsebecause 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_phoneenforces uniqueness acrossfull_nameandphone.
Legacy seed data used in tests
- The test inserts one row into this table with
connected_accountsas"{}"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, andensure_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
SQLAlchemyErrorpath 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_schemacatchesSQLAlchemyError, 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
Falseinstead of pretending the row still exists.
Dependencies
Runtime
SQLAlchemyBaseandenginefromsrc.config.databasesrc.infra.mysql.modelsfor metadata registrationloggingfor bootstrap failure reporting
Test support
create_enginesessionmakerinspecttext- SQLite in-memory and file-backed engines used for schema validation
Integration Points
backend/src/main.pyperforms the startup schema replay.backend/tests/test_schema_migrations.pyvalidates legacy-table compatibility and idempotent helper behavior.backend/src/infra/mysql/housing_application_repository.pyexercises the upgraded schema while listing and archiving applications.backend/src/infra/mysql/schema.pycontains 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. |