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_applicationinitializes new records witharchived=Falseandreview_status="pending"before handing the object toSQLAlchemyHousingApplicationRepository.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. |
Consent Model Alias
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
createassigns anidto new rows.createupdates an existing row when the same application identity is submitted again, preserving the originalid.get_by_idreturns the persisted row when present andNonewhen missing.list_recentreturns[]afterdelete_all.delete_allreturns the exact number of removed rows.archivereturnsTruefor an existing row andFalseonce 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 secondarchive(1)call returnsFalse.
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_userreturns the sameusernameandemailvalues that were submitted.create_userassigns a non-nullidto 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_ingestionreturns a record with a non-nullid.add_rowsandadd_issuesround-trip structured dictionary payloads such asmetrics.update_ingestionpersists the finalstatus,processed_rows,error_count, andsummary.list_rowsandlist_issuesexpose 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
applicationstable lackingarchivedis 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_ENGINESand monkeypatch_ensure_schema_columnsto 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
idand 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_statussupport, the response still serializesreview_statusaspending.
test_get_by_id_returns_application_when_presentproves persisted rows can be fetched immediately aftercreate.test_get_by_id_returns_none_when_missingproves missing identifiers returnNone.test_delete_all_removes_every_applicationproves the count matches the number of rows removed.test_delete_all_on_empty_table_is_a_noopproves deletion from an empty table returns0.
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_atis normalized to a non-empty string. - Missing
review_statussupport falls back topending.
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. |