Data Models, Persistence, and Schema Management

Overview

This part of the backend defines how housing applications, user records, and TransUnion batch data are stored through SQLAlchemy-backed repositories. The repository layer is responsible for persisting records, loading them back by identifier or listing, deleting them, and keeping application storage compatible with older schemas.

The persistence contracts are verified through SQLite-backed tests even though the modules live under src.infra.mysql. Those tests act as executable guarantees for CRUD semantics, upsert behavior, schema bootstrapping, and fallback behavior when legacy tables are missing columns such as archived or review_status.

Architecture Overview

flowchart TB
    subgraph ContractLayer [API and Persistence Contracts]
        ApplicationEndpointTests[backend tests interface test application endpoint]
        HousingRepoTests[backend tests infra test housing application repository]
        UserRepoTests[backend tests infra mysql test user repository]
        BatchRepoTests[backend tests infra mysql test transunion batch repository]
        SchemaTests[backend tests test schema migrations]
    end

    subgraph BusinessLayer [Use Case Functions]
        SubmitHousingApplication[submit_housing_application]
        ArchiveHousingApplication[archive_housing_application]
        DeleteAllHousingApplications[delete_all_housing_applications]
        ProcessTransUnionBatch[process_transunion_batch]
    end

    subgraph DataAccessLayer [SQLAlchemy Repositories]
        HousingRepo[SQLAlchemyHousingApplicationRepository]
        UserRepo[SQLAlchemyUserRepository]
        BatchRepo[SQLTransUnionBatchRepository]
    end

    subgraph ModelLayer [Data Models]
        HousingApplication[HousingApplication]
        User[User]
        BatchIngestion[TransUnionBatchIngestion]
        BatchRecord[TransUnionBatchRecord]
        BatchIssue[TransUnionBatchRowIssue]
        CustomerModel[CustomerModel]
        CustomerSessionModel[CustomerSessionModel]
        ConsentGrantModel[ConsentGrantModel]
    end

    subgraph SchemaLayer [Schema Management]
        EnsureArchivedColumn[ensure_application_archived_column]
        EnsuredEngines[_ENSURED_ENGINES]
    end

    subgraph StorageLayer [Database Engines]
        MySQL[MySQL]
        SQLite[SQLite fallback in tests]
    end

    ApplicationEndpointTests --> SubmitHousingApplication
    ApplicationEndpointTests --> ArchiveHousingApplication
    ApplicationEndpointTests --> HousingRepo
    HousingRepoTests --> HousingRepo
    UserRepoTests --> UserRepo
    BatchRepoTests --> BatchRepo
    SchemaTests --> EnsureArchivedColumn

    SubmitHousingApplication --> HousingRepo
    ArchiveHousingApplication --> HousingRepo
    DeleteAllHousingApplications --> HousingRepo
    ProcessTransUnionBatch --> BatchRepo

    HousingRepo --> ModelLayer
    UserRepo --> User
    BatchRepo --> BatchIngestion
    BatchRepo --> BatchRecord
    BatchRepo --> BatchIssue

    HousingRepo --> EnsureArchivedColumn
    EnsureArchivedColumn --> EnsuredEngines

    HousingRepo --> MySQL
    HousingRepo --> SQLite
    UserRepo --> SQLite
    BatchRepo --> SQLite

Data Models

Housing Application

backend/src/domain/housing_application.py

The housing application record is the persisted domain object used by the application submission and listing flows. The tests and use case code exercise it with a mix of identity fields, financial inputs, and persistence flags.

Property Type Description
id `int \ None`
email str Applicant email address.
phone str Applicant phone number used with full_name in the persistence contract.
country str Applicant country input.
property_type str Requested property category.
purchase_intent str Stated intent such as rent or buy.
budget_range str Budget input captured from the form.
savings str Savings input captured from the form.
income str Income input captured from the form.
income_currency str Currency code for the income value.
employment_status str Employment status input.
financing_consent str Financing consent input.
full_name str Applicant full name.
agree_policy bool Privacy policy consent flag.
receive_updates bool Marketing or notification consent flag.
skipped_connect_accounts bool Whether connect-accounts onboarding was skipped.
connected_accounts dict[str, object] Embedded account metadata persisted with the application.
created_at `datetime \ None`
archived bool Storage flag used by listing and archive behavior.
review_status str Listing/status field that defaults to pending in the persistence contract.

User

[!NOTE] submit_housing_application initializes new records with archived=False and review_status="pending" before handing the object to SQLAlchemyHousingApplicationRepository.create.

backend/src/domain/user.py

The user model is the persisted account record exercised by the user repository tests.

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

TransUnion Batch Ingestion

backend/src/domain/transunion_batch.py

The ingestion record tracks the lifecycle of one parsed TransUnion batch file.

Property Type Description
id `int \ None`
file_name str Original batch file name.
checksum str Batch checksum used for traceability.
status str Ingestion state such as received, processing, or completed.
total_rows int Total rows seen in the batch.
processed_rows `int \ None`
error_count `int \ None`
credit_as_of_date `date \ None`
metadata dict[str, object] Free-form batch metadata.
summary `dict[str, object] \ None`

TransUnion Batch Record

backend/src/domain/transunion_batch.py

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

TransUnion Batch Row Issue

backend/src/domain/transunion_batch.py

Property Type Description
id `int \ None`
ingestion_id int Parent ingestion identifier.
row_number int 1-based row number in the original file.
message str Parse or validation error message.
raw_row str Raw row content that failed processing.

Model Alias Modules and API Key Helpers

Customer Model Helpers

backend/src/models/customer.py

This module re-exports CustomerModel from src.infra.mysql.models and provides the API key helper functions used by customer creation and authentication code.

Symbol Type Description
API_KEY_PREFIX_LENGTH int Number of characters stored as the API key prefix.
CustomerModel SQLAlchemy model Re-export of the customer ORM model.
generate_api_key() function Generates an sk_live_... plaintext API key.
hash_api_key() function Returns the SHA-256 hex digest of a plaintext API key.
verify_api_key() function Constant-time comparison between a plaintext key and stored hash.

Session Model Alias

backend/src/models/session.py

Symbol Type Description
CustomerSessionModel SQLAlchemy model Re-export of the customer session ORM model.

backend/src/models/consent.py

Symbol Type Description
ConsentGrantModel SQLAlchemy model Re-export of the consent grant ORM model.

Repository Contracts

SQLAlchemy Housing Application Repository

backend/src/infra/mysql/housing_application_repository.py

The housing application repository is the primary persistence facade for application submissions, lookups, listing, deletion, and archive behavior. The tests treat it as the source of truth for row identity and row lifecycle.

Constructor Dependencies

Type Description
Session SQLAlchemy session used for all persistence operations.

Properties

Property Type Description
db Session Bound SQLAlchemy session.
_supports_review_status bool Compatibility flag used when a legacy schema does not provide a review_status column.

Public Methods

Method Description
create Persists a housing application and returns the stored record. Duplicate submission tests prove it updates the existing row instead of inserting a second one when the unique application identity matches.
get_by_id Returns a housing application by identifier or None when no row exists.
list_recent Returns the recent application set used by the listing endpoint. The tested contract requires newest records first and excludes archived rows.
delete_all Deletes every housing application row and returns the deleted count.
archive Removes a single application row by identifier and returns a boolean success flag.

Behavior Contracts Proven by Tests

  • create assigns an id to new rows.
  • create updates an existing row when the same application identity is submitted again, preserving the original id.
  • get_by_id returns the persisted row when present and None when missing.
  • list_recent returns [] after delete_all.
  • delete_all returns the exact number of removed rows.
  • archive returns True for an existing row and False once that row has already been removed.

SQLAlchemy User Repository

[!NOTE] The archive path is a deletion contract, not a soft-status toggle in the visible tests. After archive(1) succeeds, the row is gone from the table and a second archive(1) call returns False.

backend/src/infra/mysql/user_repository.py

The user repository is exercised through SQLite-backed tests and is responsible for persisting user accounts.

Constructor Dependencies

Type Description
Session SQLAlchemy session used for user persistence.

Properties

Property Type Description
db Session Bound SQLAlchemy session.

Public Methods

Method Description
create_user Persists a User record and returns the stored object with an assigned id.

Behavior Contracts Proven by Tests

  • The repository works against a SQLite in-memory database created with Base.metadata.create_all(engine).
  • create_user returns the same username and email values that were submitted.
  • create_user assigns a non-null id to the persisted record.

SQLTransUnion Batch Repository

backend/src/infra/mysql/transunion_batch_repository.py

This repository stores ingestion metadata, parsed batch rows, and row-level issues for TransUnion batch processing.

Constructor Dependencies

Type Description
Session SQLAlchemy session used for ingestion, row, and issue persistence.

Properties

Property Type Description
db Session Bound SQLAlchemy session.

Public Methods

Method Description
create_ingestion Persists a new ingestion record and returns it with an assigned id.
update_ingestion Updates ingestion state fields such as status, processed_rows, error_count, and summary.
add_rows Persists parsed TransUnionBatchRecord rows for a given ingestion.
add_issues Persists TransUnionBatchRowIssue records for a given ingestion.
list_rows Returns persisted batch rows for a specific ingestion.
list_issues Returns persisted batch issues for a specific ingestion.

Behavior Contracts Proven by Tests

  • create_ingestion returns a record with a non-null id.
  • add_rows and add_issues round-trip structured dictionary payloads such as metrics.
  • update_ingestion persists the final status, processed_rows, error_count, and summary.
  • list_rows and list_issues expose the persisted children for the ingestion.

Housing Application Use Cases

backend/src/usecase/housing_application.py

Function Description
submit_housing_application Builds a HousingApplication with the repository defaults and saves it through HousingApplicationRepository.create.
archive_housing_application Validates the identifier and removes the row through HousingApplicationRepository.archive.
delete_all_housing_applications Deletes every application row through HousingApplicationRepository.delete_all.

TransUnion Batch Use Case

backend/src/usecase/transunion_batch.py

Function Description
process_transunion_batch Parses rows, creates an ingestion record, persists valid rows and issues, and finalizes the summary through TransUnionBatchRepository.

Ingestion State Flow

The batch processor drives the ingestion status through a simple sequence.

stateDiagram-v2
    [*] --> received
    received --> processing
    processing --> completed

Schema Management

Archived Column Bootstrap

backend/src/infra/mysql/schema.py

The schema helper keeps the applications table compatible with older databases that predate the archived column.

Function Description
ensure_application_archived_column Adds the archived column when it is missing, preserves existing rows as unarchived, and can be called repeatedly without changing the result.

Compatibility Behavior

  • A legacy SQLite database with an applications table lacking archived is upgraded in place.
  • Existing rows remain unarchived after the helper runs.
  • Running the helper a second time is a no-op.
  • The repository path can trigger the same compatibility step through its schema-ensuring path.

MySQL and SQLite Compatibility

[!NOTE] The repository tests reset _ENSURED_ENGINES and monkeypatch _ensure_schema_columns to force the legacy-schema path. That proves column bootstrap is part of normal repository behavior, not just a standalone utility.

The same repository implementations are exercised through SQLite fixtures and SQLite file databases in tests, even though the modules live under src.infra.mysql.

Test path Compatibility contract
backend/tests/infra/test_housing_application_repository.py Verifies the housing repository works on the test session and supports create, lookup, listing, deletion, and archive semantics.
backend/tests/infra/mysql/test_user_repository.py Verifies the user repository persists against SQLite in-memory Base.metadata.create_all(engine).
backend/tests/infra/mysql/test_transunion_batch_repository.py Verifies batch ingestion persistence and lookup on the test session.
backend/tests/test_schema_migrations.py Verifies legacy SQLite tables are upgraded in place and remain readable.

API Integration

Create Housing Application

Create Housing Application

{
    "title": "Create Housing Application",
    "description": "Persists a housing application, merges embedded metadata into the stored account payload, and returns the created or updated application record.",
    "method": "POST",
    "baseUrl": "<BackendApiBaseUrl>",
    "endpoint": "/api/v1/applications",
    "headers": [
        {
            "key": "Authorization",
            "value": "Bearer <token>",
            "required": true
        },
        {
            "key": "Content-Type",
            "value": "application/json",
            "required": true
        }
    ],
    "queryParams": [],
    "pathParams": [],
    "bodyType": "json",
    "requestBody": "{\n    \"email\": \"[email protected]\",\n    \"phone\": \"+1 555 123 4567\",\n    \"country\": \"Japan\",\n    \"property_type\": \"Condo\",\n    \"purchase_intent\": \"buy\",\n    \"budget_range\": \"\\u00a5100\\u2013200M\",\n    \"savings\": \"\\u00a510M\",\n    \"income\": \"120000\",\n    \"income_currency\": \"USD\",\n    \"employment_status\": \"Full-time\",\n    \"financing_consent\": \"yes\",\n    \"full_name\": \"Jane Doe\",\n    \"agree_policy\": true,\n    \"receive_updates\": false,\n    \"skipped_connect_accounts\": false,\n    \"connected_accounts\": {\n        \"personal\": [\n            {\n                \"id\": \"acc_1\",\n                \"name\": \"Primary checking\",\n                \"provider\": \"plaid\"\n            }\n        ]\n    },\n    \"identifier\": \"dashboard-profile-1\",\n    \"metadata\": {\n        \"profile_name\": \"Dashboard Profile\",\n        \"credit_score\": 742,\n        \"credit_score_scale\": \"300-850\"\n    }\n}",
    "formData": [],
    "rawBody": "",
    "responses": {
        "201": {
            "description": "Created",
            "body": "{\n    \"id\": 42,\n    \"email\": \"[email protected]\",\n    \"phone\": \"+1 555 123 4567\",\n    \"country\": \"Japan\",\n    \"property_type\": \"Condo\",\n    \"purchase_intent\": \"buy\",\n    \"budget_range\": \"\\u00a5100\\u2013200M\",\n    \"savings\": \"\\u00a510M\",\n    \"income\": \"120000\",\n    \"income_currency\": \"USD\",\n    \"employment_status\": \"Full-time\",\n    \"financing_consent\": \"yes\",\n    \"full_name\": \"Jane Doe\",\n    \"agree_policy\": true,\n    \"receive_updates\": false,\n    \"connected_accounts\": {\n        \"personal\": [\n            {\n                \"id\": \"acc_1\",\n                \"name\": \"Primary checking\",\n                \"provider\": \"plaid\"\n            }\n        ]\n    },\n    \"archived\": false,\n    \"created_at\": \"2024-01-15T12:34:56Z\",\n    \"review_status\": \"pending\",\n    \"identifier\": \"dashboard-profile-1\",\n    \"metadata\": {\n        \"profile_name\": \"Dashboard Profile\",\n        \"credit_score\": 742,\n        \"credit_score_scale\": \"300-850\"\n    }\n}"
        }
    }
}

List Housing Applications

{
    "title": "List Housing Applications",
    "description": "Returns recent housing applications in descending recency order and supports filtering by phone and residence permit number.",
    "method": "GET",
    "baseUrl": "<BackendApiBaseUrl>",
    "endpoint": "/api/v1/applications",
    "headers": [
        {
            "key": "Authorization",
            "value": "Bearer <token>",
            "required": true
        }
    ],
    "queryParams": [
        {
            "key": "limit",
            "value": "25",
            "required": false
        },
        {
            "key": "phone",
            "value": "+1 555 123 4567",
            "required": false
        },
        {
            "key": "residence_permit_number",
            "value": "AB12345678CD",
            "required": false
        }
    ],
    "pathParams": [],
    "bodyType": "none",
    "requestBody": "",
    "formData": [],
    "rawBody": "",
    "responses": {
        "200": {
            "description": "Success",
            "body": "[\n    {\n        \"id\": 42,\n        \"email\": \"[email protected]\",\n        \"phone\": \"+1 555 123 4567\",\n        \"country\": \"Japan\",\n        \"property_type\": \"Condo\",\n        \"purchase_intent\": \"buy\",\n        \"budget_range\": \"\\u00a5100\\u2013200M\",\n        \"savings\": \"\\u00a510M\",\n        \"income\": \"120000\",\n        \"income_currency\": \"USD\",\n        \"employment_status\": \"Full-time\",\n        \"financing_consent\": \"yes\",\n        \"full_name\": \"Second Applicant\",\n        \"agree_policy\": true,\n        \"receive_updates\": false,\n        \"connected_accounts\": {\n            \"personal\": [\n                {\n                    \"id\": \"acc_1\",\n                    \"name\": \"Primary checking\",\n                    \"provider\": \"plaid\"\n                }\n            ]\n        },\n        \"archived\": false,\n        \"created_at\": \"2024-01-15T12:34:56Z\",\n        \"review_status\": \"pending\",\n        \"identifier\": \"cust-123\",\n        \"metadata\": {\n            \"email\": \"[email protected]\",\n            \"phone\": \"+1 555 123 4567\",\n            \"kyc\": {\n                \"provider\": \"saltedge\"\n            }\n        }\n    }\n]"
        }
    }
}

Get Housing Application

{
    "title": "Get Housing Application",
    "description": "Returns one application by identifier and includes persisted identifier and metadata fields when present.",
    "method": "GET",
    "baseUrl": "<BackendApiBaseUrl>",
    "endpoint": "/api/v1/applications/{application_id}",
    "headers": [
        {
            "key": "Authorization",
            "value": "Bearer <token>",
            "required": true
        }
    ],
    "queryParams": [],
    "pathParams": [
        {
            "key": "application_id",
            "value": "42",
            "required": true
        }
    ],
    "bodyType": "none",
    "requestBody": "",
    "formData": [],
    "rawBody": "",
    "responses": {
        "200": {
            "description": "Success",
            "body": "{\n    \"id\": 42,\n    \"email\": \"[email protected]\",\n    \"phone\": \"+1 555 123 4567\",\n    \"country\": \"Japan\",\n    \"property_type\": \"Condo\",\n    \"purchase_intent\": \"buy\",\n    \"budget_range\": \"\\u00a5100\\u2013200M\",\n    \"savings\": \"\\u00a510M\",\n    \"income\": \"120000\",\n    \"income_currency\": \"USD\",\n    \"employment_status\": \"Full-time\",\n    \"financing_consent\": \"yes\",\n    \"full_name\": \"Jane Doe\",\n    \"agree_policy\": true,\n    \"receive_updates\": false,\n    \"connected_accounts\": {\n        \"personal\": [\n            {\n                \"id\": \"acc_1\",\n                \"name\": \"Primary checking\",\n                \"provider\": \"plaid\"\n            }\n        ]\n    },\n    \"archived\": false,\n    \"created_at\": \"2024-01-15T12:34:56Z\",\n    \"review_status\": \"pending\",\n    \"identifier\": \"dashboard-seeded-42\",\n    \"metadata\": {\n        \"full_name\": \"Seeded Name\",\n        \"fraud_iq_identity_scan\": \"No alerts\",\n        \"collections_original_creditor\": \"Creditor A\",\n        \"email\": \"[email protected]\"\n    }\n}"
        },
        "404": {
            "description": "Application not found"
        }
    }
}

Archive Housing Application

{
    "title": "Archive Housing Application",
    "description": "Deletes one application row by identifier and returns whether the row was removed.",
    "method": "POST",
    "baseUrl": "<BackendApiBaseUrl>",
    "endpoint": "/api/v1/applications/{application_id}/archive",
    "headers": [
        {
            "key": "Authorization",
            "value": "Bearer <token>",
            "required": true
        }
    ],
    "queryParams": [],
    "pathParams": [
        {
            "key": "application_id",
            "value": "42",
            "required": true
        }
    ],
    "bodyType": "none",
    "requestBody": "",
    "formData": [],
    "rawBody": "",
    "responses": {
        "200": {
            "description": "Archived",
            "body": "{\n    \"archived\": true\n}"
        },
        "404": {
            "description": "Application not found."
        }
    }
}

Create TransUnion Batch Ingestion

Create TransUnion Batch Ingestion

{
    "title": "Create TransUnion Batch Ingestion",
    "description": "Creates an ingestion record, parses input rows, stores parsed rows and issues, and returns the ingestion summary.",
    "method": "POST",
    "baseUrl": "<BackendApiBaseUrl>",
    "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    \"rows\": [\n        \"PERM0001              2023-04-30 100 01 710 205\",\n        \"PERM0002              2023-04-30 101 02 702 198\"\n    ],\n    \"credit_as_of_date\": \"2023-04-30\",\n    \"metadata\": {\n        \"source\": \"test\"\n    },\n    \"layout\": [\n        {\n            \"key\": \"perm_id\",\n            \"start\": 1,\n            \"end\": 10,\n            \"normalised_key\": \"permId\",\n            \"data_type\": \"string\",\n            \"description\": \"Permanent identifier\",\n            \"data_group\": \"identity\",\n            \"field_name\": \"PERM_ID\",\n            \"length\": 10\n        }\n    ]\n}",
    "formData": [],
    "rawBody": "",
    "responses": {
        "201": {
            "description": "Created",
            "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            \"credit_as_of_date\": \"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}"
        }
    }
}

Feature Flows

Submit or Update a Housing Application

sequenceDiagram
    participant C as Client
    participant E as create_application
    participant U as submit_housing_application
    participant R as SQLAlchemyHousingApplicationRepository
    participant D as Database
    participant O as SQLOpenBankingConsentRepository
    participant P as application_events

    C->>E: POST /api/v1/applications
    E->>E: normalize email, full_name, phone
    E->>E: validate email, full_name, agree_policy
    E->>U: submit_housing_application
    U->>R: create HousingApplication
    R->>D: insert or update row
    D-->>R: persisted application
    R-->>U: stored application
    U-->>E: application
    E->>O: sync_application_plaid_consents
    E->>E: build ApplicationResponse
    E->>P: publish application_created
    E-->>C: 201 application response

This flow is the core persistence contract for application creation. The repository is not a pure insert-only writer; the duplicate-submit test proves it updates the existing row when the same application identity is submitted again.

List Recent Applications

sequenceDiagram
    participant C as Client
    participant E as _load_housing_applications
    participant R as SQLAlchemyHousingApplicationRepository
    participant S as Schema Compatibility
    participant M as Metadata Resolver
    participant D as Database

    C->>E: GET /api/v1/applications
    E->>R: list_recent
    R->>S: ensure_application_archived_column if needed
    S->>D: add archived column on legacy tables
    R->>D: read recent rows
    D-->>R: application rows
    R-->>E: list of applications
    E->>M: resolve application metadata
    E-->>C: JSON array of application responses

[!NOTE] The persistence identity used by the create path is the one exercised by the duplicate-submit test, where changing email while keeping the same applicant name and phone returns the same id and updates the stored row.

The listing contract is validated in tests that check recency ordering, metadata inclusion, created_at normalization, and the review_status fallback when the schema lacks that column.

Archive a Housing Application

sequenceDiagram
    participant C as Client
    participant E as archive_application handler
    participant U as archive_housing_application
    participant R as SQLAlchemyHousingApplicationRepository
    participant D as Database

    C->>E: POST /api/v1/applications/application_id/archive
    E->>U: archive_housing_application
    U->>R: archive
    R->>D: delete row by id
    D-->>R: deleted or missing
    R-->>U: true or false
    U-->>E: archived result
    E-->>C: {"archived": true} or 404

The archive contract removes the row from storage and from subsequent listings. A second archive attempt on the same identifier returns False, and a missing identifier yields a 404 response.

Persist a TransUnion Batch

sequenceDiagram
    participant C as Client
    participant E as create_transunion_batch_ingestion
    participant U as process_transunion_batch
    participant R as SQLTransUnionBatchRepository
    participant D as Database

    C->>E: POST /api/transunion/batch-ingestions
    E->>U: process_transunion_batch
    U->>R: create_ingestion status received
    R->>D: persist ingestion
    D-->>R: ingestion id
    R-->>U: ingestion
    U->>R: update_ingestion status processing
    U->>U: parse rows and collect issues
    U->>R: add_rows
    U->>R: add_issues
    U->>R: update_ingestion status completed
    R->>D: persist rows issues and summary
    D-->>R: persisted batch data
    U-->>E: result object
    E-->>C: ingestion summary response

The batch flow is stateful and uses the repository as the source of truth for the parent ingestion record, parsed child rows, and row-level issues.

Error Handling

Condition Behavior
Missing email in application create request Returns HTTPException 400 with Email is required.
Missing full_name in application create request Returns HTTPException 400 with Full name is required.
agree_policy is false in application create request Returns HTTPException 400 with Privacy policy consent is required.
Repository raises ValueError during application create Converted to HTTPException 400 with the error text.
Missing application for archive Returns HTTPException 404 with Application not found.
application_id <= 0 in archive use case Raises ValueError before repository access.
No rows in TransUnion batch request Returns HTTPException 400 with At least one row is required.
Ingestion persistence fails to assign an id process_transunion_batch raises RuntimeError("Ingestion must be persisted").
Legacy application table missing archived Schema helper adds the column before listing or archiving proceeds.
Legacy application table missing review_status Listing contract falls back to review_status: "pending".

Testing Considerations

Test file Contract covered
backend/tests/infra/test_housing_application_repository.py create, get_by_id, list_recent, and delete_all semantics, including empty-table deletion and missing-row lookup.
backend/tests/infra/mysql/test_user_repository.py User persistence against a SQLite in-memory engine using Base.metadata.create_all.
backend/tests/infra/mysql/test_transunion_batch_repository.py Ingestion creation, row persistence, issue persistence, and ingestion updates.
backend/tests/test_schema_migrations.py Legacy applications schema bootstrap, archived column creation, idempotency, and archive behavior after upgrade.
backend/tests/interface/test_application_endpoint.py Application create-upsert, list ordering, archive removal, metadata storage, missing created_at normalization, review_status fallback, and unbounded listing.
backend/tests/usecase/test_transunion_batch.py Batch processing summary generation and repository-backed persistence of parsed rows.

Repository Semantics Proven by the Housing Application Tests

[!NOTE] The application listing tests prove the fallback path is observable at the API boundary: when the repository compatibility path disables review_status support, the response still serializes review_status as pending.

  • test_get_by_id_returns_application_when_present proves persisted rows can be fetched immediately after create.
  • test_get_by_id_returns_none_when_missing proves missing identifiers return None.
  • test_delete_all_removes_every_application proves the count matches the number of rows removed.
  • test_delete_all_on_empty_table_is_a_noop proves deletion from an empty table returns 0.

Endpoint Semantics Proven by the Application Tests

  • Duplicate submissions with the same application identity update the existing record instead of creating a second row.
  • Listing returns the newest submission first.
  • Archived rows are excluded from listing.
  • Metadata embedded in the request survives round-trip persistence.
  • The response merges embedded metadata with enriched metadata.
  • Missing created_at is normalized to a non-empty string.
  • Missing review_status support falls back to pending.

Key Classes Reference

Class Location Responsibility
HousingApplication housing_application.py Represents the persisted housing application record used by submission, listing, and archive behavior.
User user.py Represents the persisted user account record.
TransUnionBatchIngestion transunion_batch.py Tracks a batch ingestion lifecycle and summary fields.
TransUnionBatchRecord transunion_batch.py Stores one parsed TransUnion row.
TransUnionBatchRowIssue transunion_batch.py Stores a row-level parsing or validation issue.
SQLAlchemyHousingApplicationRepository housing_application_repository.py Persists, looks up, lists, deletes, and archives housing applications.
SQLAlchemyUserRepository user_repository.py Persists user records.
SQLTransUnionBatchRepository transunion_batch_repository.py Persists batch ingestion metadata, rows, and issues.
ensure_application_archived_column schema.py Adds the archived column to legacy applications tables.
generate_api_key customer.py Creates plaintext customer API keys.
hash_api_key customer.py Hashes API keys for storage.
verify_api_key customer.py Verifies plaintext API keys against stored hashes.
CustomerModel customer.py Re-export of the customer ORM model used by services and tests.
CustomerSessionModel session.py Re-export of the customer session ORM model.
ConsentGrantModel consent.py Re-export of the consent grant ORM model.