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` |
Consent Grant Model Alias
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_idis present, it must match aConsentGrantModelrow for the samecustomer_id. - The matched grant must have
is_activeset toTrue. - The matched grant must have
revoked_atunset.
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. |
Open Banking Consent Use Cases
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_referencecomes fromapplication.emailwhen present, otherwiseapplication.idorapplication.phone.consent_referenceis derived from the application id and a SHA-256 token fingerprint suffix.metadataincludes application identifiers plus connection details such asconnection_name,institution_name, andaccount_idswhen present.- Existing consent rows are updated in place; missing rows are created through
register_consent.
US Consent Service
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}"
}
}
}
Open Banking Consent Endpoints
Create Open Banking Consent
{
"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
Applicant Consent Validation Flow
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
Application Submission and Consent Sync Flow
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
US Consent Lifecycle Flow
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
PostgreSQL US Consent Schema
[!NOTE]
createConsentwritesuserReference: payload.user.user_reference ?? null, while the Postgres migration definesuser_reference text NOT NULL. The service and table contract diverge, so a missinguser_referencecannot 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.statusis the persisted lifecycle field used by the service layer.createConsentinitializes consent rows asPENDING_USER_ACTION.ensureConsentActiveonly accepts consents whose status isACTIVEand whose expiry timestamp is still in the future.revokeConsenttransitions the status toREVOKEDand revokes related tokens.
US Consent State Lifecycle
stateDiagram-v2
[*] --> PENDING_USER_ACTION
PENDING_USER_ACTION --> ACTIVE
PENDING_USER_ACTION --> REVOKED
ACTIVE --> REVOKED
Schema Management and Legacy Compatibility
Database Bootstrap
[!IMPORTANT]
ensureConsentActivedoes not introduce a separate expired state. When a consent is pastexpiresAt, the service rejects it withCONSENT_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_columnensure_application_review_status_columnensure_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
archivedmarks whether an application remains visible to application-listing flows.review_statusis seeded topendingwhen a housing application is created.receive_updatesandskipped_connect_accountsare persisted intake flags captured at submission time.
Consent Lifecycle Fields
statusstores the lifecycle state in the US consent schema.revoked_atmarks a token or consent as revoked without deleting the underlying row.expires_atis 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.pyverifies that applicant creation succeeds when a matching active consent grant exists and returns 400 when the grant id is invalid.backend/tests/test_applicants.pyalso verifies customer scoping by inserting two customers and ensuring each API key only sees its own applicants.backend/tests/test_schema_migrations.pyverifies that legacy application databases gain anarchivedcolumn through the repository compatibility path.backend/tests/test_schema_migrations.pydocuments the oldapplicationstable layout and theuq_applications_full_name_phoneuniqueness rule.services/us-api/src/services/consentService.tsshould be exercised against both happy-path lifecycle transitions and the schema mismatch arounduser_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. |