| account_id | string, UUID, 🔒 system | ✅ Yes | unique; generated via gen_random_uuid() on INSERT | — | Public immutable identifier for the account. Exposed in all API responses and external references. |
| account_external_id | string | ⚪ No | max length 255; partial unique index on (workspace_pk, account_external_id) WHERE deleted_at IS NULL AND account_external_id IS NOT NULL | — | Provider-assigned identifier for the account (e.g. Plaid account_id, Qonto account external id). Used as the deduplication key during connector sync to prevent re-creating an already-ingested account. |
| type | string (enum) | ✅ Yes | non-null; maps to native pg enum account_type_enum | deposit, credit, loan, investment, payroll, other | Top-level account classification. deposit and credit are the most common for operational bank accounts; investment covers treasury / brokerage accounts (e.g. Arc Treasury via Pershing). |
| subtype | string (enum) | ⚪ No | nullable; maps to native pg enum account_subtype_enum | checking account, savings account, money market account, cash management, certificate of deposit, electronic benefit transfer, health savings account, PayPal account, prepaid card (deposit); card (credit); auto, business, commercial, construction, consumer, home equity, home mortgage, line of credit, mortgage, overdraft, student (loan); 529 plan, 401a plan, 401k plan, 403b plan, 457b plan, brokerage account, cash isa, crypto exchange, education savings account, fixed annuity, guaranteed investment certificate, health reimbursement account, IRA, ISA, Keogh, lif, life insurance, LIRA, LRIF, LRSP, mutual fund, non custodial wallet, non taxable brokerage, other annuity, other insurance, pension, pension prif, profit sharing plan, QSHR, RDSP, RESP, retirement account, RLIF, ROTH, Roth 401k, RRIF, RRSP, SARSEP, sep IRA, simple IRA, SIPP, stock plan, TFSA, thrift savings plan, trust, UGMA, UTMA, variable annuity (investment); Roth IRA (payroll); other (generic) | Granular Plaid-compatible account subtype. Provides detailed classification within each account type. Not always populated for non-Plaid connectors. |
| account_name | string | ⚪ No | max length 255; user-editable in some connectors — must not be used as a unique key | — | Human-readable display name for the account, typically provided by the bank or fintech (e.g. ‘Qonto EUR — Operating’). May contain the bank name as free text; use as display label only, not as an identity signal. |
| iban | string | ⚪ No | max length 34; CHECK: iban IS NULL OR iban ~ ’^[A-Z]{2}[0-9]{2}[A-Z0-9]{1,30}$’ (ISO 13616) | — | International Bank Account Number in ISO 13616 format. Fully populated by the Qonto connector; absent for US-centric connectors (Plaid/Mercury). The IBAN prefix encodes the country and CIB (FR IBANs: positions 5–9 are the bank code). |
| account_number | string | ⚪ No | max length 50 | — | Domestic account number, used where IBAN is not applicable (e.g. US accounts). Stored as-is from the provider. |
| bic | string | ⚪ No | max length 11; CHECK: bic IS NULL OR bic ~ ’^[A-Z]{4}[A-Z]{2}[A-Z0-9]{2}([A-Z0-9]{3})?$’ (ISO 9362, 8 or 11 chars) | — | BIC/SWIFT code for the account’s bank in ISO 9362 format. First 4 characters are the institution code. Used for bank identity resolution and cross-workspace deduplication. |
| routing_number | string | ⚪ No | max length 9; CHECK: routing_number IS NULL OR routing_number ~ ’^[0-9]{9}$’ (US ABA 9-digit) | — | US ABA routing number. Maps deterministically to a US bank via the FedACH directory. Populated for some US-side accounts; absent for EU/UK accounts. |
| sort_code | string | ⚪ No | max length 6; CHECK: sort_code IS NULL OR sort_code ~ ’^[0-9]{6}$’ (UK 6-digit) | — | UK bank sort code. Maps to a specific UK bank and branch. Absent for non-UK accounts. |
| currency | string | ⚪ No | max length 3; CHECK: currency IS NULL OR currency ~ ’^[A-Z]{3}$’ (ISO 4217) | — | ISO 4217 three-letter currency code for the primary currency of the account. Helps disambiguate multiple accounts at the same institution (e.g. Mercury USD vs Mercury IO). |
| digital_wallet_provider | string (enum) | ⚪ No | nullable; maps to native pg enum digital_wallet_provider_enum | paypal, apple_pay, google_pay, samsung_pay, alipay, wechat_pay | Provider of the digital wallet when the account represents a digital wallet rather than a traditional bank account. |
| digital_wallet_id | string | ⚪ No | max length 255 | — | External identifier for the digital wallet account at the specified digital_wallet_provider. |
| digital_wallet_type | string (enum) | ⚪ No | nullable; maps to native pg enum digital_wallet_type_enum | personal, business, merchant | Classification of the digital wallet account by usage context. |
| ownership | string (enum) | ✅ Yes | non-null; default ‘unknown’; maps to native pg enum account_ownership_enum. Set at write time from structured signals only — never from string/regex matching. | workspace, counterparty, unknown | Classifies whether the account belongs to the workspace itself (a connector-synced bank account) or to a counterparty (extracted from an invoice or document payment means). ‘unknown’ is the safe default for legacy rows and rows lacking sufficient provenance signal. Backfilled to ‘workspace’ where source_workspace_connector_pk is set or where a PaymentMeans links the account back to the workspace’s own company. |
| raw_data | jsonb | ⚪ No | nullable; shape varies by connector — always branch on originating connector before parsing | — | Complete connector-native payload as received from the provider. For Plaid: contains institution_id, institution_name, official_name, and counterparty metadata. Shape is connector-specific; do not assume a fixed path without checking the source connector. |
| created_at | Date, 🔒 system | ✅ Yes | set once on INSERT via onCreate lifecycle hook | — | Timestamp of record creation. Set automatically by MikroORM; never supplied by callers. |
| updated_at | Date, 🔒 system | ⚪ No | set on INSERT and refreshed on every UPDATE via onCreate/onUpdate lifecycle hooks | — | Timestamp of last update. Refreshed automatically by MikroORM on every write. |
| deleted_at | Date | ⚪ No | nullable; soft-delete sentinel. All queries must filter deleted_at IS NULL. The partial index idx_accounts_workspace_external_id_active uses WHERE deleted_at IS NULL. | — | Soft-delete timestamp. When set, the account is logically deleted and excluded from all standard queries. Hard deletes are not performed on this entity. |
