Data Models, Persistence, and Schema Management

Overview

This part of the backend defines how the platform stores housing applicants, customer identities, consent grants, and consent-linked underwriting records. On the MySQL side, the code centers on SQLAlchemy-backed domain records for applicants, customers, sessions, housing applications, and consent grants, with service layers that enforce tenant boundaries and consent checks before writes.

For consented financial-data access, the platform also carries a separate Postgres-backed US consent schema. That schema stores the consent lifecycle, receipts, tokens, evidence, and decisions as durable records, while the service layer in services/us-api/src/services/consentService.ts maps API calls into database inserts and updates. Schema bootstrap in backend/src/main.py keeps the MySQL schema aligned with runtime expectations by creating tables and adding compatibility columns when legacy databases are missing them.

Architecture Overview

flowchart TD
    subgraph PresentationLayer [Presentation Layer]
        AppEndpoints[HTTP endpoints for applications and consents]
        SchemaDTOs[Request and response DTOs]
    end

    subgraph BusinessLayer [Business Layer]
        HousingUseCases[Housing application use cases]
        ApplicantService[ApplicantService]
        CustomerService[CustomerService]
        OpenBankingUseCases[Open banking consent use cases]
        USConsentService[US consent service]
    end

    subgraph DataAccessLayer [Data Access Layer]
        MySQLModels[MySQL model aliases and SQLAlchemy models]
        HousingRepo[HousingApplicationRepository]
        ConsentRepo[OpenBankingConsentRepository]
        ApplicantModelStore[Applicant and consent grant rows]
        CustomerModelStore[Customer and session rows]
        PrismaStore[Prisma consent tables]
    end

    subgraph SchemaLayer [Schema Layer]
        Bootstrap[ensure_database_schema]
        MysqlCompat[Legacy application column helpers]
        PostgresConsentDDL[20250101090000_create_us_consent_tables.sql]
    end

    SchemaDTOs --> AppEndpoints
    AppEndpoints --> HousingUseCases
    AppEndpoints --> ApplicantService
    AppEndpoints --> CustomerService
    AppEndpoints --> OpenBankingUseCases
    AppEndpoints --> USConsentService

    HousingUseCases --> HousingRepo
    ApplicantService --> MySQLModels
    CustomerService --> MySQLModels
    OpenBankingUseCases --> ConsentRepo
    USConsentService --> PrismaStore

    Bootstrap --> MySQLModels
    Bootstrap --> MysqlCompat
    PostgresConsentDDL --> PrismaStore

MySQL-Backed Domain Models

Applicant Model Alias

backend/src/models/applicant.py

This module re-exports ApplicantModel from src.infra.mysql.models so application code can import the applicant row model from the public src.models namespace.

Symbol Type Description
ApplicantModel SQLAlchemy model MySQL applicant row model used by applicant CRUD and consent validation flows.

Fields referenced in this section

Property Type Description
applicant_id str Stable applicant identifier generated at insert time.
customer_id str Tenant boundary used to scope applicant queries.
applicant_reference `str \ None`
user_accounts `dict \ None`
credit_data `dict \ None`
consent_grant_id `str \ None`
created_at `datetime \ None`

backend/src/models/consent.py

This module re-exports ConsentGrantModel from src.infra.mysql.models for consent-gated applicant intake.

Symbol Type Description
ConsentGrantModel SQLAlchemy model MySQL consent grant row used to authorize applicant creation.

Fields referenced in this section

Property Type Description
grant_id str Grant identifier used by ApplicantService.create_applicant.
customer_id str Tenant scope used to ensure the grant belongs to the current customer.
user_id str User reference associated with the grant.
data_categories dict[str, bool] Category map used to represent granted data scopes.
granted_at datetime Grant issuance timestamp.
is_active bool Active flag checked before applicant creation.
revoked_at `datetime \ None`

Customer Model and API Key Utilities

backend/src/models/customer.py

This module combines the customer row export with the API-key helper functions used by customer provisioning and authentication.

Symbol Type Description
API_KEY_PREFIX_LENGTH int Number of characters stored as the public prefix of a generated API key.
CustomerModel SQLAlchemy model MySQL customer row model used by CustomerService.

Public functions

Function Description
generate_api_key Returns a new key in the sk_live_<random> format.
hash_api_key Computes the SHA-256 hash stored in api_key_hash.
verify_api_key Compares a plaintext key against a stored hash with constant-time comparison.

Fields referenced in this section

Property Type Description
customer_id str Primary customer identifier used across MySQL tables.
customer_name str Human-readable customer label.
api_key_hash str SHA-256 hash of the current API key.
api_key_prefix str Truncated public prefix of the API key.
is_active bool Soft-disable flag used during authentication.

Customer Session Model Alias

backend/src/models/session.py

This module re-exports CustomerSessionModel from src.infra.mysql.models so session persistence can be imported from the public model namespace.

Symbol Type Description
CustomerSessionModel SQLAlchemy model MySQL session row model exported for session persistence consumers.

Housing Application Record Shape

backend/src/usecase/housing_application.py

submit_housing_application constructs the application record shape before it is persisted through the repository. The same shape is later returned by get_housing_application, list_housing_applications, and search_housing_applications_by_phone.

Property Type Description
id `int \ None`
email str Applicant email address.
phone str Applicant phone number.
country str Country captured during intake.
property_type str Property type entered by the applicant.
purchase_intent str Purchase or financing intent.
budget_range str Budget range string from the intake form.
savings str Self-reported savings amount.
income str Self-reported income value.
income_currency str Currency code for income.
employment_status str Employment status string.
financing_consent str Consent string captured from the intake flow.
full_name str Applicant full name.
agree_policy bool Privacy-policy acceptance gate.
receive_updates bool Marketing or status update preference.
skipped_connect_accounts bool Flag for skipping account connection.
connected_accounts dict[str, object] Stored connected-account payload.
created_at `datetime \ None`
archived bool Archive flag used by deletion flows and compatibility helpers.
review_status str Status initialized to pending on create.

Phone Application Payloads

backend/src/interface/schemas.py

These DTOs model the phone-only application shape used by the API layer.

Model Property Type Description
PhoneApplicationCreateRequest phone str Required phone number.
connected_accounts Dict[str, Any] Connected-account payload associated with the phone entry.
PhoneApplicationResponse id int Database identifier.
phone str Stored phone number.
connected_accounts Dict[str, Any] Persisted connected-account payload.
created_at datetime Creation timestamp.

Persistence Services

Applicant Service

backend/src/services/applicant_service.py

ApplicantService is the SQLAlchemy-backed service used to list, fetch, and create applicant rows for a customer. create_applicant performs consent-grant validation before it inserts an ApplicantModel.

Constructor dependencies

Type Description
Session SQLAlchemy session used for applicant and consent-grant queries and inserts.

Public methods

Method Description
list_applicants Returns all applicants scoped to a customer_id.
get_applicant Returns one applicant for a customer by applicant_id.
create_applicant Validates the consent grant and inserts a new applicant row.

create_applicant enforces all of the following before insert:

  • If consent_grant_id is present, it must match a ConsentGrantModel row for the same customer_id.
  • The matched grant must have is_active set to True.
  • The matched grant must have revoked_at unset.

Customer Service

backend/src/services/customer_service.py

CustomerService owns customer provisioning and API-key lifecycle operations for MySQL-backed customers.

Constructor dependencies

Type Description
Session SQLAlchemy session used for customer queries and writes.

Public methods

Method Description
create_customer Creates a new active customer row and returns the generated customer id and plaintext API key.
authenticate Verifies an API key against the hashed customer record and returns the active customer row when the hash matches.
rotate_api_key Replaces the stored API key hash and prefix with a newly generated key.
deactivate_customer Marks a customer inactive.

Housing Application Use Cases

backend/src/usecase/housing_application.py

These functions orchestrate application persistence through the repository boundary.

Method Description
submit_housing_application Builds a HousingApplication record with archived=False and review_status="pending" before persisting it.
list_housing_applications Returns recent application rows ordered from newest to oldest.
get_housing_application Returns an application by application_id after validating that the identifier is positive.
search_housing_applications_by_phone Looks up applications by phone number.
delete_all_housing_applications Removes all application rows and returns the deleted count.
archive_housing_application Archives an application by id after validating the identifier is positive.

backend/src/usecase/open_banking.py

These use cases turn application-connected account data into consent rows and token attachments.

Method Description
register_consent Deduplicates scopes and operator ids, creates an OpenBankingConsent, and persists it through the repository.
sync_application_plaid_consents Extracts Plaid connections from a housing application, creates or updates consent rows, and attaches Plaid tokens.
_ensure_active_consent Validates that a consent is active, operator-authorized, and token-backed before use.

sync_application_plaid_consents derives consent metadata from the housing application record:

  • customer_reference comes from application.email when present, otherwise application.id or application.phone.
  • consent_reference is derived from the application id and a SHA-256 token fingerprint suffix.
  • metadata includes application identifiers plus connection details such as connection_name, institution_name, and account_ids when present.
  • Existing consent rows are updated in place; missing rows are created through register_consent.

services/us-api/src/services/consentService.ts

This service implements the US API consent lifecycle on top of Prisma models and emits webhook notifications on revocation.

Public methods

Method Description
createConsent Creates a US consent row and a matching consent log entry.
revokeConsent Revokes the consent, revokes related tokens, disconnects related connections, writes a log entry, and attempts a webhook dispatch.
getConsentReceipt Reads a consent row and returns the receipt projection.
ensureConsentActive Verifies that a consent exists, is active, and has not expired.

API Integration

Create Housing Application

Create Housing Application

{
    "title": "Create Housing Application",
    "description": "Creates a housing application, stores the intake payload, syncs consent-linked account data, and returns the persisted application record",
    "method": "POST",
    "baseUrl": "<BackendApiBaseUrl>",
    "endpoint": "/api/v1/applications",
    "headers": [
        {
            "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\": \"Test Applicant\",\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            }\n        ]\n    },\n    \"metadata\": {\n        \"source\": \"web-intake\",\n        \"campaign\": \"spring-2026\"\n    },\n    \"identifier\": \"intake-123\"\n}",
    "formData": [],
    "rawBody": "",
    "responses": {
        "201": {
            "description": "Success",
            "body": "{\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\": \"Test Applicant\",\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            }\n        ]\n    },\n    \"metadata\": null,\n    \"identifier\": null,\n    \"id\": 101,\n    \"created_at\": \"2026-03-31T10:15:00Z\",\n    \"archived\": false,\n    \"review_status\": \"pending\",\n    \"login_credentials\": null,\n    \"credit_score\": 742\n}"
        },
        "400": {
            "description": "Validation failure such as a missing policy agreement",
            "body": "{\n    \"detail\": \"Privacy policy consent is required.\"\n}"
        }
    }
}
{
    "title": "Create Open Banking Consent",
    "description": "Creates an open banking consent for the current operator and persists it through the MySQL consent repository",
    "method": "POST",
    "baseUrl": "<BackendApiBaseUrl>",
    "endpoint": "/open-banking/consents",
    "headers": [
        {
            "key": "Authorization",
            "value": "Bearer <token>",
            "required": true
        },
        {
            "key": "Content-Type",
            "value": "application/json",
            "required": true
        }
    ],
    "queryParams": [],
    "pathParams": [],
    "bodyType": "json",
    "requestBody": "{\n    \"consent_reference\": \"consent-123\",\n    \"customer_reference\": \"customer-456\",\n    \"customer_name\": \"Test Customer\",\n    \"scopes\": [\n        \"balances:read\",\n        \"transactions:read\"\n    ],\n    \"expires_at\": \"2027-03-31T10:15:00Z\",\n    \"allowed_operator_ids\": [\n        \"[email protected]\"\n    ],\n    \"metadata\": {\n        \"application_id\": 101,\n        \"source\": \"manual\"\n    },\n    \"auto_refresh_enabled\": true,\n    \"auto_refresh_interval_minutes\": 720,\n    \"application_id\": 101\n}",
    "formData": [],
    "rawBody": "",
    "responses": {
        "201": {
            "description": "Success",
            "body": "{\n    \"consent_reference\": \"consent-123\",\n    \"customer_reference\": \"customer-456\",\n    \"customer_name\": \"Test Customer\",\n    \"application_id\": 101,\n    \"scopes\": [\n        \"balances:read\",\n        \"transactions:read\"\n    ],\n    \"expires_at\": \"2027-03-31T10:15:00Z\",\n    \"revoked_at\": null,\n    \"allowed_operator_ids\": [\n        \"[email protected]\"\n    ],\n    \"metadata\": {\n        \"application_id\": 101,\n        \"source\": \"manual\"\n    },\n    \"plaid_item_id\": null,\n    \"auto_refresh_enabled\": true,\n    \"auto_refresh_interval_minutes\": 720,\n    \"last_refreshed_at\": null,\n    \"created_at\": \"2026-03-31T10:15:00Z\",\n    \"updated_at\": \"2026-03-31T10:15:00Z\"\n}"
        },
        "409": {
            "description": "Consent reference already exists",
            "body": "{\n    \"detail\": \"Consent reference already exists\"\n}"
        }
    }
}

List Application Consents

{
    "title": "List Application Consents",
    "description": "Returns all consents for an application that the current operator is allowed to view",
    "method": "GET",
    "baseUrl": "<BackendApiBaseUrl>",
    "endpoint": "/open-banking/applications/{application_id}/consents",
    "headers": [
        {
            "key": "Authorization",
            "value": "Bearer <token>",
            "required": true
        }
    ],
    "queryParams": [],
    "pathParams": [
        {
            "key": "application_id",
            "value": "101",
            "required": true
        }
    ],
    "bodyType": "none",
    "requestBody": "",
    "formData": [],
    "rawBody": "",
    "responses": {
        "200": {
            "description": "Success",
            "body": "[\n    {\n        \"consent_reference\": \"consent-123\",\n        \"customer_reference\": \"customer-456\",\n        \"customer_name\": \"Test Customer\",\n        \"application_id\": 101,\n        \"scopes\": [\n            \"balances:read\",\n            \"transactions:read\"\n        ],\n        \"expires_at\": \"2027-03-31T10:15:00Z\",\n        \"revoked_at\": null,\n        \"allowed_operator_ids\": [\n            \"[email protected]\"\n        ],\n        \"metadata\": {\n            \"application_id\": 101,\n            \"source\": \"manual\"\n        },\n        \"plaid_item_id\": \"item-789\",\n        \"auto_refresh_enabled\": true,\n        \"auto_refresh_interval_minutes\": 720,\n        \"last_refreshed_at\": null,\n        \"created_at\": \"2026-03-31T10:15:00Z\",\n        \"updated_at\": \"2026-03-31T10:15:00Z\"\n    }\n]"
        }
    }
}

Sync Application Consents

{
    "title": "Sync Application Consents",
    "description": "Rebuilds application-linked Plaid consents for the current operator and returns the accessible consent set",
    "method": "POST",
    "baseUrl": "<BackendApiBaseUrl>",
    "endpoint": "/open-banking/applications/{application_id}/consents/sync",
    "headers": [
        {
            "key": "Authorization",
            "value": "Bearer <token>",
            "required": true
        }
    ],
    "queryParams": [],
    "pathParams": [
        {
            "key": "application_id",
            "value": "101",
            "required": true
        }
    ],
    "bodyType": "none",
    "requestBody": "",
    "formData": [],
    "rawBody": "",
    "responses": {
        "200": {
            "description": "Success",
            "body": "[\n    {\n        \"consent_reference\": \"application-101-8f7c2b2d7d10\",\n        \"customer_reference\": \"[email protected]\",\n        \"customer_name\": \"Test Applicant\",\n        \"application_id\": 101,\n        \"scopes\": [\n            \"balances:read\",\n            \"transactions:read\"\n        ],\n        \"expires_at\": \"2027-03-31T10:15:00Z\",\n        \"revoked_at\": null,\n        \"allowed_operator_ids\": [\n            \"[email protected]\"\n        ],\n        \"metadata\": {\n            \"application_id\": 101,\n            \"application_email\": \"[email protected]\",\n            \"application_phone\": \"+1 555 123 4567\",\n            \"token_fingerprint\": \"8f7c2b2d7d10f38b6a7cb2f1d5e5c3aa\",\n            \"connection_name\": \"Primary checking\",\n            \"institution_name\": \"Sandbox Bank\",\n            \"account_ids\": [\n                \"acc-1\"\n            ]\n        },\n        \"plaid_item_id\": \"item-789\",\n        \"auto_refresh_enabled\": true,\n        \"auto_refresh_interval_minutes\": 720,\n        \"last_refreshed_at\": null,\n        \"created_at\": \"2026-03-31T10:15:00Z\",\n        \"updated_at\": \"2026-03-31T10:15:00Z\"\n    }\n]"
        }
    }
}

Feature Flows

sequenceDiagram
    participant Client as Client
    participant ApplicantService as ApplicantService
    participant DB as MySQL Session
    participant ConsentGrant as ConsentGrantModel
    participant Applicant as ApplicantModel

    Client->>ApplicantService: create_applicant
    ApplicantService->>DB: query ConsentGrantModel
    DB-->>ApplicantService: grant row or none
    alt grant missing inactive or revoked
        ApplicantService-->>Client: ValueError Consent grant is invalid or inactive
    else grant valid
        ApplicantService->>DB: add ApplicantModel
        ApplicantService->>DB: commit
        ApplicantService->>DB: refresh ApplicantModel
        DB-->>ApplicantService: persisted applicant
        ApplicantService-->>Client: ApplicantModel
    end
sequenceDiagram
    participant Client as Client
    participant Endpoint as create_application
    participant HousingUseCase as submit_housing_application
    participant HousingRepo as SQLAlchemyHousingApplicationRepository
    participant ConsentUseCase as sync_application_plaid_consents
    participant ConsentRepo as SQLOpenBankingConsentRepository
    participant Response as ApplicationResponse

    Client->>Endpoint: POST /api/v1/applications
    Endpoint->>HousingUseCase: normalize fields and build payload
    HousingUseCase->>HousingRepo: create HousingApplication
    HousingRepo-->>HousingUseCase: persisted application
    Endpoint->>ConsentUseCase: sync_application_plaid_consents
    ConsentUseCase->>ConsentRepo: create or update consent rows
    ConsentRepo-->>ConsentUseCase: consent rows
    Endpoint->>Response: build response model
    Response-->>Client: persisted application data
sequenceDiagram
    participant Client as Client
    participant Service as consentService
    participant Prisma as Prisma Client
    participant Webhook as dispatchWebhook

    Client->>Service: createConsent
    Service->>Prisma: consent.create
    Service->>Prisma: consentLog.create
    Prisma-->>Service: consent record
    Service-->>Client: consent

    Client->>Service: revokeConsent
    Service->>Prisma: consent.findUnique
    Service->>Prisma: consent.update
    Service->>Prisma: token.updateMany
    Service->>Prisma: connection.updateMany
    Service->>Prisma: consentLog.create
    Service->>Webhook: dispatchWebhook
    Webhook-->>Service: best effort response
    Service-->>Client: consentId

[!NOTE] createConsent writes userReference: payload.user.user_reference ?? null, while the Postgres migration defines user_reference text NOT NULL. The service and table contract diverge, so a missing user_reference cannot be persisted cleanly under the documented schema.

db/migrations/20250101090000_create_us_consent_tables.sql creates the durable US consent data model on PostgreSQL. Every table uses text-based primary keys and timestamps with time zone, while the child tables attach records back to consents(id).

Table Definitions

Table Purpose Key columns Constraints
consents Stores the parent consent record and its lifecycle fields. id, partner_id, user_reference, country, purpose, categories, status, consent_text_version, expires_at, created_at, updated_at id is the primary key.
consent_receipts Stores request-level evidence that a consent was presented or disclosed. id, consent_id, partner_id, request_id, disclosure_hash, ip, user_agent, created_at id is the primary key. partner_id and request_id must be unique together. consent_id references consents(id).
tokens Stores access and refresh token material linked to a consent. id, consent_id, access_jti, refresh_token_hash, expires_at, revoked_at, created_at id is the primary key. consent_id references consents(id).
feature_evidence Stores the model features used for an underwriting or scoring decision. id, consent_id, feature_set, lookback_days, features_json, inputs_digest, created_at id is the primary key. consent_id references consents(id).
decisions Stores underwriting decisions and human-readable reasons. id, consent_id, applicant_reference, purpose, product_type, score, decision, approved_limit, model_name, model_version, reasons, evidence_ref, request_id, created_at id is the primary key. consent_id references consents(id). consent_id and request_id must be unique together.

Relationship View

erDiagram
    consents ||--o{ consent_receipts : has
    consents ||--o{ tokens : issues
    consents ||--o{ feature_evidence : records
    consents ||--o{ decisions : drives

    consents {
        string id PK
        string partner_id
        string user_reference
        string country
        string purpose
        string categories
        string status
        string consent_text_version
        string expires_at
        string created_at
        string updated_at
    }

    consent_receipts {
        string id PK
        string consent_id FK
        string partner_id
        string request_id
        string disclosure_hash
        string ip
        string user_agent
        string created_at
    }

    tokens {
        string id PK
        string consent_id FK
        string access_jti
        string refresh_token_hash
        string expires_at
        string revoked_at
        string created_at
    }

    feature_evidence {
        string id PK
        string consent_id FK
        string feature_set
        string lookback_days
        string features_json
        string inputs_digest
        string created_at
    }

    decisions {
        string id PK
        string consent_id FK
        string applicant_reference
        string purpose
        string product_type
        string score
        string decision
        string approved_limit
        string model_name
        string model_version
        string reasons
        string evidence_ref
        string request_id
        string created_at
    }

Data Semantics and Statuses

  • consents.status is the persisted lifecycle field used by the service layer.
  • createConsent initializes consent rows as PENDING_USER_ACTION.
  • ensureConsentActive only accepts consents whose status is ACTIVE and whose expiry timestamp is still in the future.
  • revokeConsent transitions the status to REVOKED and revokes related tokens.
stateDiagram-v2
    [*] --> PENDING_USER_ACTION
    PENDING_USER_ACTION --> ACTIVE
    PENDING_USER_ACTION --> REVOKED
    ACTIVE --> REVOKED

Schema Management and Legacy Compatibility

Database Bootstrap

[!IMPORTANT] ensureConsentActive does not introduce a separate expired state. When a consent is past expiresAt, the service rejects it with CONSENT_REVOKED, so expiry is enforced as a guard on the active state rather than as a stored lifecycle label.

backend/src/main.py

ensure_database_schema() is the runtime bootstrap path for the MySQL schema.

Function Description
ensure_database_schema Imports the SQLAlchemy models to register metadata, calls Base.metadata.create_all(bind=engine), runs compatibility column helpers, and logs plus re-raises SQLAlchemyError failures.

The function invokes these compatibility helpers after table creation:

  • ensure_application_archived_column
  • ensure_application_review_status_column
  • ensure_user_role_columns

Legacy Applications Table Compatibility

backend/tests/test_schema_migrations.py

The migration test documents the legacy applications table shape that compatibility code still supports.

Column Type Notes
id Integer Primary key in the legacy table.
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 Defaults to 0.
connected_accounts Text Nullable.
created_at DateTime(timezone=True) Defaults to CURRENT_TIMESTAMP.

The test also shows a legacy unique constraint:

  • uq_applications_full_name_phone

After ensure_application_archived_column runs, the archived column is present and existing rows read back with archived is False.

State Management

Application Record Flags

  • archived marks whether an application remains visible to application-listing flows.
  • review_status is seeded to pending when a housing application is created.
  • receive_updates and skipped_connect_accounts are persisted intake flags captured at submission time.
  • status stores the lifecycle state in the US consent schema.
  • revoked_at marks a token or consent as revoked without deleting the underlying row.
  • expires_at is enforced by service-layer checks before access is allowed.

Error Handling

Component Error path Behavior
ApplicantService.create_applicant Missing or inactive consent grant Raises ValueError("Consent grant is invalid or inactive").
create_application Blank email, blank full name, or agree_policy is false Returns HTTP 400 with a field-specific message.
create_application Repository validation failure Returns HTTP 400 with the original ValueError message.
create_consent Duplicate consent reference Returns HTTP 409 with Consent reference already exists.
ensure_database_schema SQLAlchemy failure Logs the error and re-raises it.
revokeConsent Missing consent Throws ApiError(404, 'NOT_FOUND').
ensureConsentActive Missing, inactive, or expired consent Throws ApiError(409, 'CONSENT_REVOKED').

Testing Considerations

  • backend/tests/test_applicants.py verifies that applicant creation succeeds when a matching active consent grant exists and returns 400 when the grant id is invalid.
  • backend/tests/test_applicants.py also verifies customer scoping by inserting two customers and ensuring each API key only sees its own applicants.
  • backend/tests/test_schema_migrations.py verifies that legacy application databases gain an archived column through the repository compatibility path.
  • backend/tests/test_schema_migrations.py documents the old applications table layout and the uq_applications_full_name_phone uniqueness rule.
  • services/us-api/src/services/consentService.ts should be exercised against both happy-path lifecycle transitions and the schema mismatch around user_reference.

Key Classes Reference

Class Location Responsibility
ApplicantModel applicant.py Re-export of the MySQL applicant row model.
ConsentGrantModel consent.py Re-export of the MySQL consent grant row model used for applicant gating.
CustomerModel customer.py Customer row export plus API key helper functions.
CustomerSessionModel session.py Re-export of the MySQL customer session row model.
ApplicantService applicant_service.py Applicant CRUD and consent-grant validation.
CustomerService customer_service.py Customer creation, authentication, key rotation, and deactivation.
HousingApplication housing_application.py Application record construction and repository orchestration.
OpenBankingConsent open_banking.py Consent record lifecycle for application-linked bank consents.
ApplicationCreateRequest schemas.py Housing application intake payload.
ApplicationResponse schemas.py Persisted housing application response shape.
PhoneApplicationCreateRequest schemas.py Phone-only application intake payload.
PhoneApplicationResponse schemas.py Phone application response shape.
CreateConsentRequest open_banking_endpoints.py Open banking consent creation payload.
ConsentResponse open_banking_endpoints.py Open banking consent response shape.
TransUnionBatchIngestionRequestSchema schemas.py Batch ingestion input payload.
TransUnionBatchIngestionResponseSchema schemas.py Batch ingestion response shape.
createConsent consentService.ts US consent creation and log persistence.
revokeConsent consentService.ts US consent revocation and downstream cleanup.
ensure_database_schema main.py MySQL schema bootstrap and legacy column compatibility.
20250101090000_create_us_consent_tables.sql 20250101090000_create_us_consent_tables.sql PostgreSQL consent, receipt, token, evidence, and decision schema.