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:

  • CustomerService and ApplicantService operate on SQLAlchemy models tied to customer identity and applicant consent.
  • The US API consentService uses 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.

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`

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.

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`

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

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.
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:

  1. Import src.infra.mysql.models so SQLAlchemy metadata is registered.
  2. Call Base.metadata.create_all(bind=engine).
  3. Apply ensure_application_archived_column(engine).
  4. Apply ensure_application_review_status_column(engine).
  5. Apply ensure_user_role_columns(engine).

The migration tests verify the archived-column repair path:

  • ensure_application_archived_column(engine) adds archived when 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 returns False.

Application Schema Intersections

The open banking sync path reads and writes the application schema in three ways:

  • submit_housing_application stores connected_accounts, archived, and review_status.
  • sync_application_plaid_consents derives customer_reference from application.email, application.id, or application.phone.
  • sync_application_plaid_consents stores application_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_customer generates a plaintext key with generate_api_key.
  • The plaintext key is hashed with hash_api_key before persistence.
  • authenticate hashes the incoming secret, looks up an active customer by api_key_hash, and verifies the secret with verify_api_key.
  • rotate_api_key replaces the hash and prefix, invalidating the previous key at the stored record level.
  • authenticate logs 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:

  1. If consent_grant_id is present, query ConsentGrantModel.
  2. Match on grant_id and customer_id.
  3. Require is_active to be True.
  4. Require revoked_at to be None.
  5. Raise ValueError("Consent grant is invalid or inactive") when the grant is not valid.

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, and account_ids when 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, or access_token.
  • Throws ApiError(403, 'TOKEN_REQUIRED') when no token is present.
  • Verifies the JWT using config.jwtPublicKey and the RS256 algorithm.
  • Requires the claims consent_id, purpose, data_categories, and jti.
  • Copies those claims into request.accessClaims.

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 expiresAt from expires_at or access_duration_days.
  • Stores the consent with status: 'PENDING_USER_ACTION'.
  • Creates a consentLog row with action: '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 consentLog row with action: 'REVOKED'.
  • Calls dispatchWebhook and intentionally ignores delivery errors.

getConsentReceipt

Returns a compact receipt object with:

  • consent_id
  • status
  • purpose
  • data_categories
  • consent_disclosure_version
  • expires_at
  • created_at

ensureConsentActive

This method enforces the active-state gate before downstream data access can proceed.

Housing Application Use Cases

[!NOTE] createConsent writes new consents with status: 'PENDING_USER_ACTION', while ensureConsentActive only accepts status === 'ACTIVE'. In the visible code, no writer promotes a newly created consent to ACTIVE, so a consent created by createConsent will fail ensureConsentActive until 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

{
    "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
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
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
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 by authenticate.
  • api_key_prefix: updated during rotation so the active key can be identified without storing plaintext.
  • ConsentGrantModel.is_active: must be True when ApplicantService.create_applicant validates a grant.
  • ConsentGrantModel.revoked_at: must remain None for applicant creation to proceed.
  • consent_grant_id** on** ApplicantModel: links applicant persistence to the consent gate that authorized it.
  • expires_at: defines when a consent is no longer usable.
  • revoked_at: marks a consent as invalid after explicit revocation.
  • auto_refresh_enabled and auto_refresh_interval_minutes: control sync cadence for application-derived consents.
  • allowed_operator_ids: controls read access to returned consent records.
  • consents.status: written as PENDING_USER_ACTION at creation and REVOKED at revocation.
  • tokens.revoked_at: marks linked tokens invalid.
  • connection.status: moved to DISCONNECTED during 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.Session for customer and applicant persistence.
  • src.infra.mysql.models for CustomerModel, ApplicantModel, and ConsentGrantModel.
  • src.models.customer for API key generation, hashing, and verification.
  • Prisma client in the US API service for consent, consentLog, token, and connection.
  • jsonwebtoken and config.jwtPublicKey for US access token validation.
  • dispatchWebhook for consent revocation notification.
  • Base.metadata.create_all(bind=engine) plus schema repair helpers for database bootstrapping.
  • HousingApplication and SQLAlchemyHousingApplicationRepository for application-to-consent sync.
  • get_current_user_email for operator-scoped consent visibility in open banking routes.

Testing Considerations

  • test_create_applicant_validates_consent confirms that a valid grant allows creation and an invalid grant returns HTTP 400.
  • test_applicant_filtering_by_customer confirms applicant reads stay scoped to the authenticated customer.
  • test_repository_ensures_archived_column_when_missing confirms schema repair adds archived and preserves existing rows.
  • test_ensure_application_archived_column_adds_column_when_missing confirms the helper is idempotent.
  • test_repository_archive_call_deletes_existing_row_after_schema_upgrade confirms 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.