Data Models, Persistence, and Schema Management - Customer API, consent-grant, and session migration evolution
Overview
This schema family is the persistence backbone for external API access in Lita’s e-housing platform. The visible code connects three layers of identity and authorization state: customer API keys, applicant-level consent grants, and consent records that can be exchanged, refreshed, revoked, and audited through the open banking and US consent flows.
The repository’s current implementation shows the evolution from application-facing persistence in CustomerModel, ConsentGrantModel, and ApplicantModel to a broader consent ledger in the US API migration (consents, consent_receipts, tokens, feature_evidence, decisions). Application records intersect with this family through connected_accounts, application_id, archived, and review_status, which are used to synthesize or validate consent state and to keep legacy tables compatible with newer code.
Architecture Overview
flowchart TB
subgraph PresentationLayer [Presentation Layer]
ApplicantRoutes[Applicant API routes]
OpenBankingRoutes[Open Banking routes]
USApiRoutes[US API routes]
end
subgraph BusinessLayer [Business Layer]
CustomerServiceNode[CustomerService]
ApplicantServiceNode[ApplicantService]
OpenBankingUseCases[open_banking use cases]
ConsentServiceNode[consentService]
AccessTokenMiddleware[requireAccessToken]
end
subgraph PersistenceLayer [Persistence Layer]
LegacyAppSchema[001_initial_schema.sql]
CustomerApiSchema[002_customer_api_tables.sql]
USConsentSchema[20250101090000_create_us_consent_tables.sql]
SQLAlchemyModels[CustomerModel ApplicantModel ConsentGrantModel]
PrismaModels[consent consentLog token connection]
HousingTables[applications housing applications]
end
subgraph ExternalAccess [External API Access]
PartnerClients[Partner clients]
BearerTokens[Bearer tokens]
Webhooks[Webhook delivery]
end
ApplicantRoutes --> ApplicantServiceNode
OpenBankingRoutes --> OpenBankingUseCases
USApiRoutes --> AccessTokenMiddleware
USApiRoutes --> ConsentServiceNode
CustomerServiceNode --> SQLAlchemyModels
ApplicantServiceNode --> SQLAlchemyModels
OpenBankingUseCases --> HousingTables
OpenBankingUseCases --> SQLAlchemyModels
ConsentServiceNode --> PrismaModels
AccessTokenMiddleware --> BearerTokens
ConsentServiceNode --> Webhooks
LegacyAppSchema --> CustomerApiSchema
CustomerApiSchema --> USConsentSchema
HousingTables --> OpenBankingUseCases
Schema Family Evolution
The repository groups the auth-and-consent lineage across three migration files:
| Migration file | Visible role in the schema family |
|---|---|
backend/migrations/001_initial_schema.sql |
Earlier baseline for the backend schema family that precedes the customer API evolution. |
platform/database/migrations/002_customer_api_tables.sql |
The next schema step in the customer API lineage, used alongside the customer-facing persistence code. |
db/migrations/20250101090000_create_us_consent_tables.sql |
The current US consent ledger migration that creates consent, receipt, token, evidence, and decision tables. |
The current code uses that lineage in two directions:
CustomerServiceandApplicantServiceoperate on SQLAlchemy models tied to customer identity and applicant consent.- The US API
consentServiceuses Prisma tables to record consent issuance, revocation, token invalidation, and webhook-visible state transitions. - Application schema helpers keep the housing application table compatible with newer fields such as
archived, which are required by the current UI and repository logic.
Data Models
Customer Record
File: backend/src/infra/mysql/models.py as consumed by backend/src/services/customer_service.py and backend/tests/test_auth.py
CustomerModel is the persisted identity record behind customer API keys. The visible code creates customers, authenticates them by API key hash, and rotates the secret without exposing plaintext after creation.
| Property | Type | Description |
|---|---|---|
customer_id |
str |
Stable customer identifier generated as a UUID string. |
customer_name |
str |
Display name stored at customer creation time. |
api_key_hash |
str |
SHA-256 hash of the plaintext API key. |
api_key_prefix |
str |
Short prefix used for key identification. |
is_active |
bool |
Gate used by authentication and customer deactivation. |
Customer API Key Utilities
File: backend/src/models/customer.py
| Property | Type | Description |
|---|---|---|
API_KEY_PREFIX_LENGTH |
int |
Prefix length used when storing customer API keys. |
| Method | Description |
|---|---|
generate_api_key |
Generates a secure sk_live_-prefixed API key. |
hash_api_key |
Hashes an API key with SHA-256. |
verify_api_key |
Compares a plaintext key to a stored hash with constant-time comparison. |
Consent Grant Record
File: backend/src/infra/mysql/models.py as consumed by backend/src/services/applicant_service.py and backend/tests/test_applicants.py
ConsentGrantModel is the customer-scoped consent gate that must be active before an applicant can be created with consent_grant_id.
| Property | Type | Description |
|---|---|---|
grant_id |
str |
Grant identifier passed in applicant creation requests. |
customer_id |
str |
Customer ownership key used to scope consent validation. |
user_id |
str |
User reference recorded with the grant. |
data_categories |
dict[str, bool] |
Category-level grant flags, such as reports. |
granted_at |
datetime |
Timestamp for the original grant event. |
is_active |
bool |
Must be True for applicant creation to proceed. |
revoked_at |
`datetime | None` |
Applicant Record
File: backend/src/infra/mysql/models.py as consumed by backend/src/services/applicant_service.py, backend/tests/test_applicants.py, and backend/src/models/applicant.py
ApplicantModel stores per-customer applicant data and links an applicant row back to an optional consent grant.
| Property | Type | Description |
|---|---|---|
applicant_id |
str |
UUID-like identifier assigned at creation. |
customer_id |
str |
Customer that owns the applicant record. |
applicant_reference |
`str | None` |
user_accounts |
`dict | None` |
credit_data |
`dict | None` |
consent_grant_id |
`str | None` |
created_at |
`datetime | None` |
Open Banking Consent Record
File: backend/src/domain/open_banking.py as serialized in backend/src/interface/open_banking_endpoints.py and populated by backend/src/usecase/open_banking.py
OpenBankingConsent is the consent object that the open banking endpoints return after creating or syncing consent state from an application.
| Property | Type | Description |
|---|---|---|
consent_reference |
str |
External consent key, unique within the repository. |
customer_reference |
str |
Customer-side identifier derived from application data or caller input. |
customer_name |
`str | None` |
application_id |
`int | None` |
scopes |
list[str] |
Permission scopes stored on the consent. |
expires_at |
datetime |
Consent expiry timestamp. |
revoked_at |
`datetime | None` |
allowed_operator_ids |
list[str] |
Email or operator identifiers allowed to view the consent. |
metadata |
dict[str, Any] |
Arbitrary metadata persisted with the consent. |
plaid_item_id |
`str | None` |
auto_refresh_enabled |
bool |
Enables automatic refresh for synced application consents. |
auto_refresh_interval_minutes |
int |
Refresh cadence used during sync. |
last_refreshed_at |
`datetime | None` |
created_at |
`datetime | None` |
updated_at |
`datetime | None` |
Access Claims
File: services/us-api/src/middleware/accessToken.ts
AccessClaims is injected into FastifyRequest after a bearer token is validated.
| Property | Type | Description |
|---|---|---|
consent_id |
string |
Consent identifier extracted from the JWT. |
purpose |
Purpose |
Consent purpose claim. |
data_categories |
DataCategory[] |
Authorized data categories. |
jti |
string |
Token identifier used for replay-sensitive access control. |
Open Banking Consent Request
File: backend/src/interface/open_banking_endpoints.py
| Property | Type | Description |
|---|---|---|
consent_reference |
str |
Unique consent identifier. |
customer_reference |
str |
Customer-side reference. |
customer_name |
`str | None` |
scopes |
list[str] |
Requested consent scopes. Defaults to balances:read and transactions:read. |
expires_at |
datetime |
Expiration timestamp supplied by the caller. |
allowed_operator_ids |
list[str] |
Operator identifiers that may view the consent. |
metadata |
`dict[str, Any] | None` |
auto_refresh_enabled |
bool |
Enables automatic refresh handling. |
auto_refresh_interval_minutes |
int |
Cadence, constrained between 30 and 43200. |
application_id |
`int | None` |
Open Banking Consent Response
File: backend/src/interface/open_banking_endpoints.py
| Property | Type | Description |
|---|---|---|
consent_reference |
str |
Consent identifier returned by the API. |
customer_reference |
str |
Customer-side reference. |
customer_name |
`str | None` |
application_id |
`int | None` |
scopes |
list[str] |
Consent scopes. |
expires_at |
datetime |
Expiry timestamp. |
revoked_at |
`datetime | None` |
allowed_operator_ids |
list[str] |
Operators allowed to access the record. |
metadata |
dict[str, Any] |
Persisted metadata blob. |
plaid_item_id |
`str | None` |
auto_refresh_enabled |
bool |
Auto-refresh flag. |
auto_refresh_interval_minutes |
int |
Auto-refresh cadence. |
last_refreshed_at |
`datetime | None` |
created_at |
`datetime | None` |
updated_at |
`datetime | None` |
Application Create Request
File: backend/src/interface/schemas.py
This model is the bridge between application storage and consent sync because it carries connected_accounts, metadata, and the optional identifier that the open banking code later reuses.
| Property | Type | Description |
|---|---|---|
email |
str |
Applicant email. |
phone |
str |
Applicant phone. |
country |
str |
Country submitted with the application. |
property_type |
str |
Property category. |
purchase_intent |
str |
Intent string. |
budget_range |
str |
Budget range. |
savings |
str |
Savings amount string. |
income |
str |
Income string. |
income_currency |
str |
Currency code for income. |
employment_status |
str |
Employment state. |
financing_consent |
str |
Financing consent string. |
full_name |
str |
Applicant full name. |
agree_policy |
bool |
Policy consent gate. |
receive_updates |
bool |
Marketing or update consent flag. |
skipped_connect_accounts |
bool |
Tracks whether account linking was skipped. |
connected_accounts |
dict[str, Any] |
Persisted account payload used later by consent sync and coverage detection. |
metadata |
`dict[str, Any] | None` |
identifier |
`str | None` |
Application Response
File: backend/src/interface/schemas.py
| Property | Type | Description |
|---|---|---|
id |
int |
Application identifier. |
created_at |
datetime |
Creation timestamp. |
archived |
bool |
Archive marker used by schema helpers and repository behavior. |
metadata |
`dict[str, Any] | None` |
identifier |
`str | None` |
review_status |
str |
Review state used by the applicants UI and filtering logic. |
login_credentials |
`UserCredentialsResponse | None` |
credit_score |
`int | None` |
Persistence and Schema Management
US Consent Tables
File: db/migrations/20250101090000_create_us_consent_tables.sql
The US migration family defines the durable consent ledger used by the Fastify API. Each table is keyed for request-level idempotency and auditability.
consents
| Column | Type | Purpose |
|---|---|---|
id |
text |
Primary key for the consent row. |
partner_id |
text |
Partner that owns the consent. |
user_reference |
text |
User reference supplied by the partner. |
country |
text |
Country context for the consent. |
purpose |
text |
Business purpose for the consent. |
categories |
text[] |
Authorized data categories. |
status |
text |
Current consent status. |
consent_text_version |
text |
Disclosure version accepted at creation time. |
expires_at |
timestamptz |
Consent expiry. |
created_at |
timestamptz |
Creation timestamp. |
updated_at |
timestamptz |
Update timestamp. |
consent_receipts
| Column | Type | Purpose |
|---|---|---|
id |
text |
Primary key. |
consent_id |
text |
Foreign key to consents(id). |
partner_id |
text |
Partner that created the receipt. |
request_id |
uuid |
Request id used to prevent duplicate receipts. |
disclosure_hash |
text |
Hash of the disclosure content. |
ip |
inet |
Optional IP address. |
user_agent |
text |
Optional user agent. |
created_at |
timestamptz |
Creation timestamp. |
tokens
| Column | Type | Purpose |
|---|---|---|
id |
text |
Primary key. |
consent_id |
text |
Foreign key to consents(id). |
access_jti |
text |
Access token identifier. |
refresh_token_hash |
text |
Hashed refresh token. |
expires_at |
timestamptz |
Token expiry. |
revoked_at |
timestamptz |
Revocation timestamp. |
created_at |
timestamptz |
Creation timestamp. |
feature_evidence
| Column | Type | Purpose |
|---|---|---|
id |
text |
Primary key. |
consent_id |
text |
Foreign key to consents(id). |
feature_set |
text |
Feature set name. |
lookback_days |
int |
Feature window size. |
features_json |
jsonb |
Serialized feature payload. |
inputs_digest |
text |
Digest of the feature inputs. |
created_at |
timestamptz |
Creation timestamp. |
decisions
| Column | Type | Purpose |
|---|---|---|
id |
text |
Primary key. |
consent_id |
text |
Foreign key to consents(id). |
applicant_reference |
text |
Applicant reference used in the decision. |
purpose |
text |
Purpose string. |
product_type |
text |
Product context. |
score |
int |
Optional score value. |
decision |
text |
Decision outcome. |
approved_limit |
int |
Optional limit value. |
model_name |
text |
Optional model name. |
model_version |
text |
Optional model version. |
reasons |
jsonb |
Decision reasons. |
evidence_ref |
text |
Optional evidence reference. |
request_id |
uuid |
Request id used for idempotency. |
created_at |
timestamptz |
Creation timestamp. |
The visible unique constraints enforce request-level deduplication:
consent_receipts:UNIQUE (partner_id, request_id)decisions:UNIQUE (consent_id, request_id)
Schema Bootstrap Helpers
File: backend/src/main.py and backend/tests/test_schema_migrations.py
ensure_database_schema creates all SQLAlchemy metadata and then applies schema repair helpers so newer application code can run against older databases.
| Function | Description |
|---|---|
ensure_database_schema |
Creates SQLAlchemy tables and then applies post-create column repair helpers. |
The visible bootstrap sequence is:
- Import
src.infra.mysql.modelsso SQLAlchemy metadata is registered. - Call
Base.metadata.create_all(bind=engine). - Apply
ensure_application_archived_column(engine). - Apply
ensure_application_review_status_column(engine). - Apply
ensure_user_role_columns(engine).
The migration tests verify the archived-column repair path:
ensure_application_archived_column(engine)addsarchivedwhen missing.- Existing rows remain unarchived after the repair.
- Re-running the helper is idempotent.
SQLAlchemyHousingApplicationRepository.archive(1)deletes the row after the schema upgrade, and a second call returnsFalse.
Application Schema Intersections
The open banking sync path reads and writes the application schema in three ways:
submit_housing_applicationstoresconnected_accounts,archived, andreview_status.sync_application_plaid_consentsderivescustomer_referencefromapplication.email,application.id, orapplication.phone.sync_application_plaid_consentsstoresapplication_id,application_email,application_phone, and token fingerprints in consent metadata.
backend/src/interface/http_endpoints.py also merges incoming application metadata into connected_accounts, which makes the application row the source of truth for later consent sync and coverage analysis.
Business Layer
Customer Service
File: backend/src/services/customer_service.py
CustomerService owns customer creation, key verification, key rotation, and deactivation for the customer API family.
| Property | Type | Description |
|---|---|---|
db |
Session |
SQLAlchemy session used for customer persistence. |
| Method | Description |
|---|---|
create_customer |
Creates a customer row and returns the new customer_id plus the plaintext API key. |
authenticate |
Authenticates a plaintext API key against the stored hash and active flag. |
rotate_api_key |
Replaces the stored API key hash and prefix for a customer. |
deactivate_customer |
Marks a customer inactive. |
Authentication and Rotation Flow
create_customergenerates a plaintext key withgenerate_api_key.- The plaintext key is hashed with
hash_api_keybefore persistence. authenticatehashes the incoming secret, looks up an active customer byapi_key_hash, and verifies the secret withverify_api_key.rotate_api_keyreplaces the hash and prefix, invalidating the previous key at the stored record level.authenticatelogs a warning if the stored hash and the supplied plaintext do not match during verification.
Applicant Service
File: backend/src/services/applicant_service.py
ApplicantService persists applicant records and enforces consent-grant validity before allowing a linked applicant to be created.
| Property | Type | Description |
|---|---|---|
db |
Session |
SQLAlchemy session used for applicant and consent-grant persistence. |
| Method | Description |
|---|---|
list_applicants |
Returns every applicant for a customer. |
get_applicant |
Returns one applicant scoped to both customer and applicant identifiers. |
create_applicant |
Creates an applicant after validating the consent grant when one is supplied. |
create_applicant applies the following checks before inserting a row:
- If
consent_grant_idis present, queryConsentGrantModel. - Match on
grant_idandcustomer_id. - Require
is_activeto beTrue. - Require
revoked_atto beNone. - Raise
ValueError("Consent grant is invalid or inactive")when the grant is not valid.
Open Banking Consent Use Cases
File: backend/src/usecase/open_banking.py
These use cases bridge application storage and open banking consent persistence.
| Method | Description |
|---|---|
register_consent |
Creates an OpenBankingConsent and persists it through the repository. |
sync_application_plaid_consents |
Derives consents from application connected_accounts, updates or creates them, and attaches Plaid tokens. |
sync_application_plaid_consents uses the application row as the source of consent state:
- It extracts Plaid connections from
application.connected_accounts. - It hashes each access token to derive a consent reference suffix.
- It creates metadata with
application_id,application_email,application_phone, and token fingerprints. - It adds
connection_name,institution_name, andaccount_idswhen those values exist. - It reuses an existing consent when
repository.get_by_reference(consent_reference)finds one. - It enables auto refresh and sets the application id on the synced consent.
Access Token Middleware
File: services/us-api/src/middleware/accessToken.ts
requireAccessToken enforces bearer-style access to the US API consent endpoints and attaches the decoded claims to the request.
| Property | Type | Description |
|---|---|---|
request.accessClaims |
`AccessClaims | undefined` |
| Method | Description |
|---|---|
requireAccessToken |
Validates the token from the authorization header, x-access-token, or access_token query parameter. |
The middleware behavior is explicit:
- Accepts the token from
Authorization: Bearer ...,x-access-token, oraccess_token. - Throws
ApiError(403, 'TOKEN_REQUIRED')when no token is present. - Verifies the JWT using
config.jwtPublicKeyand theRS256algorithm. - Requires the claims
consent_id,purpose,data_categories, andjti. - Copies those claims into
request.accessClaims.
US Consent Service
File: services/us-api/src/services/consentService.ts
This service persists the US consent lifecycle and the audit trail around it.
| Method | Description |
|---|---|
createConsent |
Creates a consent row and a corresponding consent log entry. |
revokeConsent |
Marks a consent revoked, revokes tokens, disconnects linked connections, logs the action, and attempts webhook delivery. |
getConsentReceipt |
Returns a receipt view of a consent. |
ensureConsentActive |
Validates that a consent is active and not expired. |
createConsent
- Generates a new consent id with the
cst_us_prefix. - Computes
expiresAtfromexpires_atoraccess_duration_days. - Stores the consent with
status: 'PENDING_USER_ACTION'. - Creates a
consentLogrow withaction: 'CREATED'.
revokeConsent
- Loads the consent by
id. - Throws
ApiError(404, 'NOT_FOUND')when the row is missing. - Updates the consent status to
REVOKED. - Revokes all linked tokens by setting
revokedAt. - Updates linked connections to
DISCONNECTED. - Writes a
consentLogrow withaction: 'REVOKED'. - Calls
dispatchWebhookand intentionally ignores delivery errors.
getConsentReceipt
Returns a compact receipt object with:
consent_idstatuspurposedata_categoriesconsent_disclosure_versionexpires_atcreated_at
ensureConsentActive
This method enforces the active-state gate before downstream data access can proceed.
Housing Application Use Cases
[!NOTE]
createConsentwrites new consents withstatus: 'PENDING_USER_ACTION', whileensureConsentActiveonly acceptsstatus === 'ACTIVE'. In the visible code, no writer promotes a newly created consent toACTIVE, so a consent created bycreateConsentwill failensureConsentActiveuntil another process updates the row.
File: backend/src/usecase/housing_application.py
| Method | Description |
|---|---|
submit_housing_application |
Creates a HousingApplication with archived=False and review_status="pending". |
list_housing_applications |
Returns recent applications. |
get_housing_application |
Returns a single application by id. |
search_housing_applications_by_phone |
Returns applications that match a phone query. |
delete_all_housing_applications |
Deletes every application row. |
archive_housing_application |
Deletes a housing application by id through the repository. |
The application entity is the join point between applicant data and consent sync because connected_accounts is later mined to create or update open banking consent rows.
API Integration
Create Open Banking Consent
Create Open Banking Consent
{
"title": "Create Open Banking Consent",
"description": "Creates an open banking consent for a customer and automatically includes the current operator in the allowed operator list",
"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-2025-0001\",\n \"customer_reference\": \"customer-001\",\n \"customer_name\": \"Lita Housing Ltd\",\n \"scopes\": [\n \"balances:read\",\n \"transactions:read\"\n ],\n \"expires_at\": \"2026-01-01T00:00:00Z\",\n \"allowed_operator_ids\": [\n \"[email protected]\"\n ],\n \"metadata\": {\n \"application_id\": 42,\n \"source\": \"platform\"\n },\n \"auto_refresh_enabled\": true,\n \"auto_refresh_interval_minutes\": 720,\n \"application_id\": 42\n}",
"formData": [],
"rawBody": "",
"responses": {
"201": {
"description": "Created",
"body": "{\n \"consent_reference\": \"consent-2025-0001\",\n \"customer_reference\": \"customer-001\",\n \"customer_name\": \"Lita Housing Ltd\",\n \"application_id\": 42,\n \"scopes\": [\n \"balances:read\",\n \"transactions:read\"\n ],\n \"expires_at\": \"2026-01-01T00:00:00Z\",\n \"revoked_at\": null,\n \"allowed_operator_ids\": [\n \"[email protected]\",\n \"[email protected]\"\n ],\n \"metadata\": {\n \"application_id\": 42,\n \"source\": \"platform\"\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\": \"2025-01-01T09:00:00Z\",\n \"updated_at\": \"2025-01-01T09:00:00Z\"\n}"
},
"409": {
"description": "Conflict",
"body": "{\n \"detail\": \"Consent reference already exists\"\n}"
}
}
}
List Application Consents
List Application Consents
{
"title": "List Application Consents",
"description": "Lists all consents attached to a housing application that the current operator is authorised 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": "42",
"required": true
}
],
"bodyType": "none",
"requestBody": "",
"formData": [],
"rawBody": "",
"responses": {
"200": {
"description": "Success",
"body": "[\n {\n \"consent_reference\": \"application-42-3b8d7f4c2a11\",\n \"customer_reference\": \"[email protected]\",\n \"customer_name\": \"Legacy Applicant\",\n \"application_id\": 42,\n \"scopes\": [\n \"balances:read\",\n \"transactions:read\"\n ],\n \"expires_at\": \"2026-01-01T09:00:00Z\",\n \"revoked_at\": null,\n \"allowed_operator_ids\": [\n \"[email protected]\"\n ],\n \"metadata\": {\n \"application_id\": 42,\n \"application_email\": \"[email protected]\",\n \"application_phone\": \"+1 555 123 4567\",\n \"token_fingerprint\": \"3b8d7f4c2a11ab...\"\n },\n \"plaid_item_id\": \"item-123\",\n \"auto_refresh_enabled\": true,\n \"auto_refresh_interval_minutes\": 720,\n \"last_refreshed_at\": \"2025-01-02T09:00:00Z\",\n \"created_at\": \"2025-01-01T09:00:00Z\",\n \"updated_at\": \"2025-01-02T09:00:00Z\"\n }\n]"
},
"403": {
"description": "Forbidden",
"body": "{\n \"detail\": \"You are not authorised to view these consents.\"\n}"
},
"404": {
"description": "Application not found",
"body": "{\n \"detail\": \"Application not found\"\n}"
}
}
}
Sync Application Consents
Sync Application Consents
{
"title": "Sync Application Consents",
"description": "Synchronises Plaid-backed consents for a housing application 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
},
{
"key": "Content-Type",
"value": "application/json",
"required": true
}
],
"queryParams": [],
"pathParams": [
{
"key": "application_id",
"value": "42",
"required": true
}
],
"bodyType": "none",
"requestBody": "",
"formData": [],
"rawBody": "",
"responses": {
"200": {
"description": "Success",
"body": "[\n {\n \"consent_reference\": \"application-42-3b8d7f4c2a11\",\n \"customer_reference\": \"[email protected]\",\n \"customer_name\": \"Legacy Applicant\",\n \"application_id\": 42,\n \"scopes\": [\n \"balances:read\",\n \"transactions:read\"\n ],\n \"expires_at\": \"2026-01-01T09:00:00Z\",\n \"revoked_at\": null,\n \"allowed_operator_ids\": [\n \"[email protected]\"\n ],\n \"metadata\": {\n \"application_id\": 42,\n \"application_email\": \"[email protected]\",\n \"application_phone\": \"+1 555 123 4567\",\n \"token_fingerprint\": \"3b8d7f4c2a11ab...\"\n },\n \"plaid_item_id\": \"item-123\",\n \"auto_refresh_enabled\": true,\n \"auto_refresh_interval_minutes\": 720,\n \"last_refreshed_at\": \"2025-01-02T09:00:00Z\",\n \"created_at\": \"2025-01-01T09:00:00Z\",\n \"updated_at\": \"2025-01-02T09:00:00Z\"\n }\n]"
},
"403": {
"description": "Forbidden",
"body": "{\n \"detail\": \"You are not authorised to view these consents.\"\n}"
},
"404": {
"description": "Application not found",
"body": "{\n \"detail\": \"Application not found\"\n}"
}
}
}
Feature Flows
Customer API Key Lifecycle
sequenceDiagram
participant Partner as Partner Client
participant Service as CustomerService
participant Db as SQLAlchemy Session
participant Model as CustomerModel
Partner->>Service: create_customer
Service->>Service: generate_api_key
Service->>Service: hash_api_key
Service->>Db: add CustomerModel
Service->>Db: commit
Db-->>Service: persisted customer_id
Service-->>Partner: customer_id and plaintext api key
Partner->>Service: authenticate
Service->>Service: hash_api_key
Service->>Db: query CustomerModel by api_key_hash and is_active
Db-->>Service: matching customer row
Service->>Service: verify_api_key
Service-->>Partner: CustomerModel or none
Applicant Creation with Consent Grant Validation
sequenceDiagram
participant Client as API Client
participant Service as ApplicantService
participant Db as SQLAlchemy Session
participant Grant as ConsentGrantModel
participant Applicant as ApplicantModel
Client->>Service: create_applicant
alt consent_grant_id present
Service->>Db: query ConsentGrantModel by grant_id and customer_id
Db-->>Service: grant row or none
Service->>Service: check is_active and revoked_at
end
alt grant invalid
Service-->>Client: ValueError Consent grant is invalid or inactive
else grant valid
Service->>Db: add ApplicantModel
Service->>Db: commit
Service->>Db: refresh
Db-->>Service: persisted applicant row
Service-->>Client: ApplicantModel
end
Application Consent Sync
sequenceDiagram
participant Client as API Client
participant Route as open_banking endpoint
participant Auth as get_current_user_email
participant Repo as SQLOpenBankingConsentRepository
participant AppRepo as SQLAlchemyHousingApplicationRepository
participant UseCase as sync_application_plaid_consents
participant Db as Database
Client->>Route: POST /open-banking/applications/id/consents/sync
Route->>Auth: resolve current_user_email
Route->>AppRepo: get_housing_application
AppRepo-->>Route: HousingApplication or none
alt application missing
Route-->>Client: 404 Application not found
else application found
Route->>UseCase: sync_application_plaid_consents
UseCase->>Db: read application connected_accounts
UseCase->>Repo: get_by_reference
alt consent exists
UseCase->>Repo: save updated consent
else new consent
UseCase->>Repo: create via register_consent
end
UseCase->>Repo: attach_plaid_tokens
Route->>Repo: list_by_application_id
Repo-->>Route: consent list
Route->>Route: filter by operator access
alt no accessible consents
Route-->>Client: 403 not authorised
else accessible consents
Route-->>Client: serialised ConsentResponse list
end
end
US Consent Revocation and Token Invalidation
sequenceDiagram
participant Partner as Partner Client
participant Middleware as requireAccessToken
participant Service as consentService
participant Prisma as Prisma Client
participant Webhook as dispatchWebhook
Partner->>Middleware: bearer token or x-access-token
Middleware->>Middleware: verify JWT with RS256
Middleware-->>Partner: request.accessClaims populated
Partner->>Service: revokeConsent
Service->>Prisma: findUnique consent
alt consent missing
Service-->>Partner: ApiError 404 NOT_FOUND
else consent found
Service->>Prisma: update consent status REVOKED
Service->>Prisma: updateMany tokens revokedAt
Service->>Prisma: updateMany connections DISCONNECTED
Service->>Prisma: create consentLog REVOKED
Service->>Webhook: dispatchWebhook consent_revoked
Webhook-->>Service: delivered or ignored error
Service-->>Partner: consentId
end
State Management
Customer Access State
CustomerModel.is_active: gates customer authentication.api_key_hash: the stored authentication secret used byauthenticate.api_key_prefix: updated during rotation so the active key can be identified without storing plaintext.
Consent Grant State
ConsentGrantModel.is_active: must beTruewhenApplicantService.create_applicantvalidates a grant.ConsentGrantModel.revoked_at: must remainNonefor applicant creation to proceed.consent_grant_id** on**ApplicantModel: links applicant persistence to the consent gate that authorized it.
Open Banking Consent State
expires_at: defines when a consent is no longer usable.revoked_at: marks a consent as invalid after explicit revocation.auto_refresh_enabledandauto_refresh_interval_minutes: control sync cadence for application-derived consents.allowed_operator_ids: controls read access to returned consent records.
US Consent Ledger State
consents.status: written asPENDING_USER_ACTIONat creation andREVOKEDat revocation.tokens.revoked_at: marks linked tokens invalid.connection.status: moved toDISCONNECTEDduring consent revocation.request.accessClaims: populated only after JWT verification succeeds.
Application Table State
archived: added by schema repair helpers so older tables remain compatible with newer repository behavior.review_status: used by application rendering and later status changes.connected_accounts: the bridge between application persistence and consent sync.
Error Handling
| Component | Error path | Result |
|---|---|---|
CustomerService.authenticate |
Empty API key or non-matching hash | Returns None. |
CustomerService.authenticate |
Hash mismatch after lookup | Logs a warning with the customer id. |
CustomerService.rotate_api_key |
Missing customer | Raises ValueError. |
ApplicantService.create_applicant |
Invalid or inactive consent grant | Raises ValueError("Consent grant is invalid or inactive"). |
create_consent in open banking |
Duplicate consent reference | Converts IntegrityError into HTTP 409. |
sync_application_consents |
Missing application | Returns HTTP 404. |
sync_application_consents |
No accessible consent after operator filtering | Returns HTTP 403. |
requireAccessToken |
Missing or invalid token | Throws ApiError(403, 'TOKEN_REQUIRED') or ApiError(403, 'INVALID_TOKEN'). |
revokeConsent |
Missing consent | Throws ApiError(404, 'NOT_FOUND'). |
revokeConsent |
Webhook failure | Ignores the error and still completes revocation. |
ensureConsentActive |
Non-active or expired consent | Throws ApiError(409, 'CONSENT_REVOKED'). |
Dependencies
sqlalchemy.orm.Sessionfor customer and applicant persistence.src.infra.mysql.modelsforCustomerModel,ApplicantModel, andConsentGrantModel.src.models.customerfor API key generation, hashing, and verification.Prismaclient in the US API service forconsent,consentLog,token, andconnection.jsonwebtokenandconfig.jwtPublicKeyfor US access token validation.dispatchWebhookfor consent revocation notification.Base.metadata.create_all(bind=engine)plus schema repair helpers for database bootstrapping.HousingApplicationandSQLAlchemyHousingApplicationRepositoryfor application-to-consent sync.get_current_user_emailfor operator-scoped consent visibility in open banking routes.
Testing Considerations
test_create_applicant_validates_consentconfirms that a valid grant allows creation and an invalid grant returns HTTP 400.test_applicant_filtering_by_customerconfirms applicant reads stay scoped to the authenticated customer.test_repository_ensures_archived_column_when_missingconfirms schema repair addsarchivedand preserves existing rows.test_ensure_application_archived_column_adds_column_when_missingconfirms the helper is idempotent.test_repository_archive_call_deletes_existing_row_after_schema_upgradeconfirms the repository can still archive rows after the schema upgrade.- The open banking consent flow should be exercised with both an allowed and disallowed operator email because the visibility filter is enforced after consent sync.
Key Classes Reference
| Class | Location | Responsibility |
|---|---|---|
CustomerService |
customer_service.py |
Creates customers, authenticates API keys, rotates keys, and deactivates customers. |
ApplicantService |
applicant_service.py |
Creates and reads applicants while enforcing consent-grant validity. |
CustomerModel |
customer.py |
Customer API key helper and model alias used by the customer service. |
ApplicantModel |
applicant.py |
Applicant model alias used by applicant persistence code. |
ConsentGrantModel |
consent.py |
Consent grant model alias used to gate applicant creation. |
open_banking.py |
open_banking.py |
Registers and synchronizes application-derived open banking consents. |
consentService.ts |
consentService.ts |
Creates, revokes, and receipts US consent records while maintaining audit logs. |
accessToken.ts |
accessToken.ts |
Validates access tokens and attaches decoded claims to the Fastify request. |
main.py |
main.py |
Boots schema creation and repair helpers for the SQLAlchemy database. |
test_schema_migrations.py |
test_schema_migrations.py |
Verifies archived-column migration behavior and repository compatibility. |