Data Models, Persistence, and Schema Management - Repository Behavior and Persistence-Layer Tests

Overview

This section covers the persistence boundary for housing applications, TransUnion batch ingestion, user creation, consent storage, and upload-session validation. The code here is built around repository contracts that keep domain logic insulated from SQLAlchemy sessions, legacy schema drift, and storage metadata checks.

The strongest guarantees come from the tests: persisted records round-trip through repository methods, batch ingestions retain row-level metrics and issues, legacy application tables are upgraded in place, and upload finalization validates object storage before any downstream processing continues. Operationally, this layer is also where storage and third-party API failures become visible as structured validation or metadata errors.

Architecture Overview

flowchart TD
    subgraph UseCases[Persistence Facing Use Cases]
        SubmitHousingApplication[submit_housing_application]
        ProcessTransUnionBatch[process_transunion_batch]
        SyncPlaidConsents[sync_application_plaid_consents]
        AttachPlaidTokens[attach_plaid_tokens]
        FinalizeUploadSession[finalize_upload_session]
        RegisterConsent[register_consent]
    end

    subgraph Repositories[Repository Contracts]
        HousingRepo[SQLAlchemyHousingApplicationRepository]
        BatchRepo[SQLTransUnionBatchRepository]
        UserRepo[SQLAlchemyUserRepository]
        ConsentRepo[OpenBankingConsentRepository]
        UploadRepo[UploadSessionRepository]
    end

    subgraph SchemaGuard[Schema Compatibility]
        ArchivedColumn[ensure_application_archived_column]
        LegacyFallback[_ensure_schema_columns fallback]
    end

    subgraph Infrastructure[Infrastructure Services]
        DbSession[SQLAlchemy Session]
        StorageClient[Storage client]
        TransUnionParser[parse_line and FieldSpec layout]
        PlaidGateway[PlaidKycGateway]
    end

    SubmitHousingApplication --> HousingRepo
    ProcessTransUnionBatch --> BatchRepo
    SyncPlaidConsents --> ConsentRepo
    SyncPlaidConsents --> HousingRepo
    AttachPlaidTokens --> ConsentRepo
    RegisterConsent --> ConsentRepo
    FinalizeUploadSession --> UploadRepo
    FinalizeUploadSession --> StorageClient

    HousingRepo --> DbSession
    BatchRepo --> DbSession
    UserRepo --> DbSession
    ConsentRepo --> DbSession
    UploadRepo --> DbSession

    ArchivedColumn --> DbSession
    LegacyFallback --> HousingRepo
    ProcessTransUnionBatch --> TransUnionParser
    SyncPlaidConsents --> PlaidGateway

Data Models

Housing Application

File path: backend/src/usecase/housing_application.py

Property Type Description
id `int \ None`
email str Applicant email used as the primary contact and consent customer reference fallback.
phone str Applicant phone number.
country str Applicant country input.
property_type str Declared property type.
purchase_intent str Declared purchase or rental intent.
budget_range str Budget input as submitted by the applicant.
savings str Savings amount as submitted by the applicant.
income str Income amount as submitted by the applicant.
income_currency str Currency code for income.
employment_status str Employment status input.
financing_consent str Consent value recorded for financing workflows.
full_name str Applicant full name.
agree_policy bool Policy acceptance flag.
receive_updates bool Marketing or status update opt-in flag.
skipped_connect_accounts bool Indicates the applicant skipped account connection.
connected_accounts dict[str, object] Stored connected-account payload, including Plaid references when present.
created_at `datetime \ None`
archived bool Archive flag maintained by repository schema compatibility logic.
review_status str Review state written as pending by the application use case.

TransUnion Batch Ingestion

File path: backend/src/usecase/transunion_batch.py

Property Type Description
id `int \ None`
file_name str Source batch filename.
checksum str Batch checksum used to identify the uploaded file.
status str Ingestion lifecycle state.
total_rows int Total input row count.
credit_as_of_date `date \ None`
metadata dict[str, object] Arbitrary batch metadata.
processed_rows int Populated by update_ingestion.
error_count int Populated by update_ingestion.
summary dict[str, object] Aggregate output summary written after processing.

TransUnion Batch Record

File path: backend/src/usecase/transunion_batch.py

Property Type Description
id `int \ None`
ingestion_id int Parent ingestion identifier.
perm_id str Permanent identifier from the TransUnion layout.
credit_as_of_date `date \ None`
consumer_id str Consumer identifier from the batch.
sequence_number str Row sequence value.
metrics dict[str, object] Parsed metric payload preserved as structured JSON.

TransUnion Batch Row Issue

File path: backend/src/usecase/transunion_batch.py

Property Type Description
id `int \ None`
ingestion_id int Parent ingestion identifier.
row_number int One-based input row number.
message str Parse or validation message.
raw_row str Raw row text that failed processing.

User

File path: backend/tests/infra/mysql/test_user_repository.py

Property Type Description
id `int \ None`
username str Unique username.
email str Unique email address.
hashed_password str Stored password hash.
created_at `datetime \ None`

Upload Session

File path: backend/src/usecase/credit_upload.py

Property Type Description
upload_session_id str Session identifier used for upload lookup.
user_id str Owning user identifier.
bureau str Bureau name tied to the upload session.
country str Country scope for the upload.
object_path str Storage path for the uploaded file.
content_type str Expected object content type.
file_size_bytes int Declared file size limit for the object.
expires_at datetime Session expiry timestamp.

File path: backend/src/usecase/open_banking.py

Property Type Description
consent_reference str Stable consent reference used for lookup and token attachment.
customer_reference str Customer reference derived from applicant data.
customer_name `str \ None`
scopes list[str] Consent scope list with duplicates removed.
expires_at datetime Consent expiration timestamp.
allowed_operator_ids list[str] Operator allow-list with duplicates removed.
metadata dict[str, object] Persisted metadata, including application and token fingerprints.
auto_refresh_enabled bool Auto-refresh flag.
auto_refresh_interval_minutes int Auto-refresh interval written at creation time.
application_id `int \ None`
plaid_access_token `str \ None`
plaid_item_id `str \ None`
plaid_consent_expires_at `datetime \ None`

Process TransUnion Batch Result

File path: backend/src/usecase/transunion_batch.py

Property Type Description
ingestion TransUnionBatchIngestion Updated ingestion header returned after the final persistence update.
summary dict[str, object] Aggregated batch summary.
sanitized_rows list[dict[str, object]] Normalized row payloads derived from processed records.
issues list[TransUnionBatchRowIssue] Parse issues captured during processing.

Repository Contracts

SQLAlchemy Housing Application Repository

File path: backend/src/infra/mysql/housing_application_repository.py

Type Description
Session SQLAlchemy session used for application CRUD and schema compatibility checks.
Method Description
create Persists a HousingApplication and returns the stored record with an assigned id.
get_by_id Fetches a housing application by identifier and returns None when missing.
list_recent Returns recent applications in repository order and continues to work when legacy columns are absent.
delete_all Deletes every application row and returns the number of deleted records.
archive Removes the application row identified by id and reports whether a row was archived.

The tests prove that the repository preserves application identity on round-trip, returns None for a missing identifier, and empties the table when delete_all is called. The legacy-schema test also proves that list_recent remains usable when the archived column is not present yet.

SQL TransUnion Batch Repository

File path: backend/src/infra/mysql/transunion_batch_repository.py

Type Description
Session SQLAlchemy session used for ingestion headers, batch rows, and batch issues.
Method Description
create_ingestion Persists a new ingestion header and returns it with an assigned id.
update_ingestion Updates ingestion status, processed counts, error counts, totals, and summary payloads.
add_rows Persists one or more TransUnionBatchRecord rows under the ingestion.
add_issues Persists one or more TransUnionBatchRowIssue records under the ingestion.
list_rows Returns persisted rows for the ingestion.
list_issues Returns persisted issues for the ingestion.

The repository contract keeps the ingest header and row-level artifacts separate, which allows a batch to succeed with partial issues while still writing a final summary.

SQLAlchemy User Repository

File path: backend/src/infra/mysql/user_repository.py

Type Description
Session SQLAlchemy session bound to the user tables created through Base.metadata.
Method Description
create_user Persists a User and returns it with an assigned id.

The test fixture builds the schema with Base.metadata.create_all, which verifies that the repository operates against the declarative model schema without extra bootstrap code in the test itself.

File path: backend/src/usecase/open_banking.py

Type Description
OpenBankingConsentRepository Persistence contract used by consent registration and Plaid token attachment.
Method Description
create Stores a new consent record.
save Persists in-place consent updates such as attached tokens and metadata merges.
get_by_reference Loads a consent by consent_reference.
list_by_application_id Returns all consents linked to an application identifier.

Upload Session Repository Contract

File path: backend/src/usecase/credit_upload.py

Type Description
UploadSessionRepository Persistence contract used to resolve active uploads before storage validation runs.
Method Description
get_active Loads an active upload session by upload_session_id, user_id, and bureau.

Persistence-Facing Use Cases

submit_housing_application

File path: backend/src/usecase/housing_application.py

Method Description
submit_housing_application Builds a HousingApplication with archived=False and review_status="pending", then persists it through the repository.

The use case is intentionally narrow: it only shapes the application object and delegates storage to the repository. The stored defaults are part of the persistence contract and are verified indirectly by application repository and endpoint tests.

sequenceDiagram
    participant Caller as Caller
    participant UseCase as submit_housing_application
    participant Repo as SQLAlchemyHousingApplicationRepository
    participant DB as Database

    Caller->>UseCase: submit data
    UseCase->>Repo: create HousingApplication
    Repo->>DB: insert application row
    DB-->>Repo: persisted row with id
    Repo-->>UseCase: created application
    UseCase-->>Caller: HousingApplication

process_transunion_batch

File path: backend/src/usecase/transunion_batch.py

Method Description
process_transunion_batch Creates the ingestion header, parses each row, stores records and issues separately, and writes the final summary back to the ingestion.

The flow is resilient to parse failures. Invalid rows are captured as TransUnionBatchRowIssue records and the overall ingestion still completes with status="completed" when persistence succeeds.

sequenceDiagram
    participant Caller as Caller
    participant UseCase as process_transunion_batch
    participant Repo as SQLTransUnionBatchRepository
    participant Parser as parse_line
    participant DB as Database

    Caller->>UseCase: file_name, checksum, rows
    UseCase->>Repo: create_ingestion received
    Repo->>DB: insert ingestion header
    DB-->>Repo: ingestion id
    Repo-->>UseCase: ingestion

    UseCase->>Repo: update_ingestion processing
    loop each row
        UseCase->>Parser: parse_line
        alt parsed row
            UseCase->>UseCase: build TransUnionBatchRecord
        else parse error
            UseCase->>UseCase: build TransUnionBatchRowIssue
        end
    end

    UseCase->>Repo: add_rows
    UseCase->>Repo: add_issues
    UseCase->>Repo: update_ingestion completed
    Repo-->>UseCase: updated ingestion
    UseCase-->>Caller: ProcessTransUnionBatchResult

File path: backend/src/usecase/open_banking.py

Method Description
register_consent Creates a consent with deduplicated scopes and operator IDs and persists it with the repository.
sync_application_plaid_consents Derives consent references from connected Plaid tokens, creates or updates consents, and attaches Plaid token metadata.
attach_plaid_tokens Loads a consent by reference, attaches the Plaid access token and item ID, and saves the updated record.

sync_application_plaid_consents is the bridge between housing application storage and open-banking consent storage. It pulls token-bearing entries from application.connected_accounts, hashes each token for a stable consent reference suffix, and writes metadata that preserves application identity and connection context.

sequenceDiagram
    participant Caller as Caller
    participant UseCase as sync_application_plaid_consents
    participant ConsentRepo as OpenBankingConsentRepository
    participant Housing as HousingApplication
    participant DB as Database

    Caller->>UseCase: application
    UseCase->>UseCase: derive consent reference
    UseCase->>ConsentRepo: get_by_reference
    alt existing consent
        UseCase->>UseCase: update consent fields
        UseCase->>ConsentRepo: save
    else new consent
        UseCase->>ConsentRepo: create
    end
    UseCase->>ConsentRepo: get_by_reference
    UseCase->>ConsentRepo: save token fields
    ConsentRepo->>DB: persist consent data
    ConsentRepo-->>UseCase: consent
    UseCase-->>Caller: list of updated consents

finalize_upload_session

File path: backend/src/usecase/credit_upload.py

Method Description
finalize_upload_session Validates an active upload session against storage metadata and returns the session when the object matches the declared constraints.
Validation step Behavior
Session lookup Requires an active session from UploadSessionRepository.get_active.
Object existence Raises UploadStorageError when the stored object is missing.
Object size Raises UploadStorageError when size is unavailable.
Declared file size Deletes the blob and raises UploadValidationError when the object exceeds the declared session size.
Global size limit Deletes the blob and raises UploadValidationError when the object exceeds MAX_UPLOAD_BYTES.
Content type Deletes the blob and raises UploadValidationError when the content type does not match the session.
sequenceDiagram
    participant Caller as Caller
    participant UseCase as finalize_upload_session
    participant Repo as UploadSessionRepository
    participant Storage as Storage client
    participant DB as Database

    Caller->>UseCase: upload_session_id, user_id, bureau
    UseCase->>Repo: get_active
    Repo->>DB: load upload session
    DB-->>Repo: UploadSession
    Repo-->>UseCase: session
    UseCase->>Storage: bucket blob exists reload
    alt object missing
        UseCase-->>Caller: UploadStorageError
    else metadata mismatch
        UseCase->>Storage: delete blob
        UseCase-->>Caller: UploadValidationError
    else valid object
        UseCase-->>Caller: UploadSession
    end

Schema Compatibility and Migration Guards

Archived Column Upgrade Path

[!NOTE] finalize_upload_session deletes the uploaded blob when size or content-type validation fails. That makes the storage object unavailable after a mismatch, so troubleshooting depends on the repository lookup and the validation error rather than the original object remaining in place.

File path: backend/src/infra/mysql/schema.py

Method Description
ensure_application_archived_column Adds the archived column to legacy applications tables and preserves existing rows as unarchived.

The schema migration tests prove three guarantees:

  • legacy tables gain the archived column when the helper runs,
  • existing rows are left with archived set to false,
  • running the helper again is a no-op.

Repository Compatibility Fallback

File path: backend/src/infra/mysql/housing_application_repository.py

The repository test suite exercises a second compatibility path: when the review-status column is missing, the repository still returns application rows and normalizes review_status to pending. This keeps list operations usable across older schemas without breaking the application list payload.

Operational Troubleshooting at the Persistence Edge

Plaid Transaction Handling

File path: backend/tests/infra/external_apis/test_plaid_transactions_handling.py

The Plaid handling test proves that PlaidKycGateway.get_transaction_data converts a Plaid ApiException with INVALID_PRODUCT into structured metadata instead of a raw crash. The resulting payload carries:

  • metadata.status = "not_supported"
  • metadata.error_code = "INVALID_PRODUCT"
  • a human-readable transaction_summary.error string

This behavior matters for persistence-adjacent workflows because it allows the open-banking path to report a supported failure mode when transaction retrieval cannot continue.

API Integration

Create TransUnion Batch Ingestion

File path: backend/src/interface/transunion_batch_endpoints.py

Create TransUnion Batch Ingestion

{
    "title": "Create TransUnion Batch Ingestion",
    "description": "Parses a fixed-width TransUnion batch payload, persists the ingestion header, stores row records and row issues, and returns the completed ingestion summary",
    "method": "POST",
    "baseUrl": "<TransUnionApiBaseUrl>",
    "endpoint": "/api/transunion/batch-ingestions",
    "headers": [
        {
            "key": "Content-Type",
            "value": "application/json",
            "required": true
        }
    ],
    "queryParams": [],
    "pathParams": [],
    "bodyType": "json",
    "requestBody": "{\n    \"file_name\": \"tu_batch.txt\",\n    \"checksum\": \"abc123\",\n    \"credit_as_of_date\": \"2023-04-30\",\n    \"metadata\": {\n        \"source\": \"upload\"\n    },\n    \"rows\": [\n        \"PERM0001    2023-04-30 100 01 710\",\n        \"PERM0002    2023-04-30 101 02 685\"\n    ],\n    \"layout\": [\n        {\n            \"key\": \"perm_id\",\n            \"start\": 1,\n            \"end\": 12,\n            \"normalised_key\": \"permId\",\n            \"description\": \"Permanent id\",\n            \"data_group\": \"consumer\",\n            \"field_name\": \"PERM ID\",\n            \"length\": 12,\n            \"data_type\": \"string\"\n        }\n    ]\n}",
    "formData": [],
    "rawBody": "",
    "responses": {
        "201": {
            "description": "Ingestion completed successfully",
            "body": "{\n    \"ingestion_id\": 1,\n    \"status\": \"completed\",\n    \"total_rows\": 2,\n    \"processed_rows\": 2,\n    \"error_count\": 0,\n    \"credit_as_of_date\": \"2023-04-30\",\n    \"summary\": {\n        \"creditModel\": \"VantageScore 4.0\",\n        \"creditScore\": 710\n    },\n    \"sanitized_rows\": [\n        {\n            \"permId\": \"PERM0001\",\n            \"creditAsOfDate\": \"2023-04-30\",\n            \"consumerId\": \"100\",\n            \"sequenceNumber\": \"01\",\n            \"metrics\": {\n                \"vantage_score\": {\n                    \"description\": \"VantageScore 4.0\",\n                    \"value\": 710,\n                    \"rawKey\": \"EVTG04.finscore\"\n                }\n            }\n        }\n    ],\n    \"issues\": [\n        {\n            \"row_number\": 2,\n            \"message\": \"Failed to parse row\"\n        }\n    ]\n}"
        },
        "400": {
            "description": "Validation error when no rows are supplied",
            "body": "{\n    \"detail\": \"At least one row is required\"\n}"
        }
    }
}

Persistence-Layer Tests

Housing Application Repository Tests

File path: backend/tests/infra/test_housing_application_repository.py

Test What it proves
test_delete_all_removes_every_application Two created applications are removed and list_recent() becomes empty.
test_delete_all_on_empty_table_is_a_noop Deleting from an empty table returns 0.
test_get_by_id_returns_application_when_present get_by_id returns the stored application with the same id and email.
test_get_by_id_returns_none_when_missing get_by_id returns None for an unknown identifier.

Schema Migration Tests

File path: backend/tests/test_schema_migrations.py

Test What it proves
test_ensure_application_archived_column_adds_column_when_missing The helper adds archived, leaves existing rows unarchived, and is idempotent.
test_repository_ensures_archived_column_when_missing The repository remains readable against a legacy database and returns applications with archived set to False.
test_repository_archive_call_deletes_existing_row_after_schema_upgrade archive succeeds on an existing row, returns False on a second call, and removes the row from the table.

TransUnion Batch Repository Tests

File path: backend/tests/infra/mysql/test_transunion_batch_repository.py

Test What it proves
test_repository_persists_ingestion_rows_and_issues The repository creates an ingestion header, stores a row, stores an issue, updates the ingestion summary, and returns rows and issues through the list methods.

The assertions confirm that JSON metric payloads survive storage intact and that row issues remain associated with the correct ingestion.

User Repository Tests

File path: backend/tests/infra/mysql/test_user_repository.py

Test What it proves
test_create_user create_user assigns an id and preserves username and email.

The fixture-backed SQLite schema creation proves the repository can run against an isolated in-memory database during test execution.

TransUnion Batch Use Case Tests

File path: backend/tests/usecase/test_transunion_batch.py

Test What it proves
test_process_transunion_batch_creates_summary The batch processor stores a completed ingestion, produces a creditModel summary, writes a numeric creditScore, sanitizes the row identifier, and returns no issues for a clean input row.

Housing Application Endpoint Compatibility Test

File path: backend/tests/interface/test_application_endpoint.py

Test What it proves
test_list_applications_falls_back_without_review_status_column The application listing continues to work when the repository is forced into a schema mode without review_status, and the returned payload uses review_status: pending.

Plaid Transaction Handling Test

File path: backend/tests/infra/external_apis/test_plaid_transactions_handling.py

Test What it proves
test_get_transaction_data_handles_invalid_product Plaid product unavailability is normalized into structured metadata and summary text instead of leaking a raw exception to callers.

Feature Flows

Housing Application Persistence Flow

sequenceDiagram
    participant Caller as Caller
    participant UseCase as submit_housing_application
    participant Repo as SQLAlchemyHousingApplicationRepository
    participant DB as Database

    Caller->>UseCase: application fields
    UseCase->>UseCase: build HousingApplication
    UseCase->>Repo: create
    Repo->>DB: insert row
    DB-->>Repo: assigned id
    Repo-->>UseCase: persisted application
    UseCase-->>Caller: HousingApplication

TransUnion Batch Persistence Flow

sequenceDiagram
    participant Caller as Caller
    participant UseCase as process_transunion_batch
    participant Repo as SQLTransUnionBatchRepository
    participant Parser as parse_line
    participant DB as Database

    Caller->>UseCase: file name checksum rows
    UseCase->>Repo: create_ingestion
    Repo->>DB: insert ingestion
    DB-->>Repo: ingestion id
    Repo-->>UseCase: ingestion id
    UseCase->>Repo: update_ingestion processing
    loop every row
        UseCase->>Parser: parse_line
        alt parsed
            UseCase->>Repo: add_rows
        else error
            UseCase->>Repo: add_issues
        end
    end
    UseCase->>Repo: update_ingestion completed
    Repo-->>UseCase: updated ingestion
    UseCase-->>Caller: result

Upload Session Validation Flow

sequenceDiagram
    participant Caller as Caller
    participant UseCase as finalize_upload_session
    participant Repo as UploadSessionRepository
    participant Storage as Storage client
    participant DB as Database

    Caller->>UseCase: session identifiers
    UseCase->>Repo: get_active
    Repo->>DB: load session
    DB-->>Repo: UploadSession
    Repo-->>UseCase: session
    UseCase->>Storage: bucket blob
    Storage-->>UseCase: object metadata
    alt invalid metadata
        UseCase->>Storage: delete blob
        UseCase-->>Caller: UploadValidationError
    else valid object
        UseCase-->>Caller: UploadSession
    end

State Management

State or Field Meaning
status = "received" Initial TransUnion ingestion state written when the header is created.
status = "processing" Intermediate ingestion state written before row parsing begins.
status = "completed" Final ingestion state written after rows, issues, and summary are persisted.
archived = False Default housing application state set by submit_housing_application.
review_status = "pending" Default housing application review state set by submit_housing_application and preserved by the repository compatibility fallback.
auto_refresh_enabled = True Consent state written when application Plaid consents are synchronized.

Error Handling

The persistence boundary prefers structured domain errors over silent failure. The repository and use case behaviors proven by the tests are:

  • process_transunion_batch captures parsing failures as TransUnionBatchRowIssue records and continues processing remaining rows.
  • process_transunion_batch raises RuntimeError if the ingestion header is not persisted and no id is returned.
  • attach_plaid_tokens raises OpenBankingConsentNotFound when a consent reference is missing.
  • finalize_upload_session raises UploadValidationError for size and content-type mismatches, and UploadStorageError for missing object metadata or missing objects.
  • create_transunion_batch_ingestion returns HTTP 400 when the request contains no rows.

Dependencies

  • SQLAlchemy Session for repository persistence.
  • Base.metadata for schema creation in the user repository test fixture.
  • sqlite:///:memory: and temporary SQLite files for repository and migration tests.
  • parse_line, FieldSpec, and converter_from_data_type for fixed-width TransUnion ingestion.
  • _get_storage_client() and storage blobs for upload-session validation.
  • PlaidKycGateway for transaction-fetch error normalization in the external API handling test.

Key Classes Reference

Class Location Responsibility
SQLAlchemyHousingApplicationRepository housing_application_repository.py Persists housing applications and keeps legacy application tables readable.
SQLTransUnionBatchRepository transunion_batch_repository.py Persists batch ingestions, rows, and issues for TransUnion imports.
SQLAlchemyUserRepository user_repository.py Persists user records with generated identifiers.
PlaidKycGateway plaid_client.py Normalizes Plaid transaction retrieval results and error payloads for upstream use cases.