| transaction_id | string, UUID, 🔒 system | ✅ Yes | unique; generated via gen_random_uuid() | — | Public immutable identifier for the transaction. Exposed on all API responses; used for deduplication and external references. |
| type | string (text, CHECK constraint) | ⚪ No | nullable; TEXT column with CHECK IN (…) — not a native PG enum. MikroORM @Enum without nativeEnumName stores the enum VALUE string. | General payments to vendors or suppliers, Transfers between accounts, Incoming funds or deposits, Cash withdrawals or outgoing funds, Credit/debit card transactions, Automated recurring payments, Refunds or previous paid funds, Services fees and charges, Interest earned or charged, Miscellaneous or unclassified transaction | High-level classification of the transaction’s economic nature. The stored DB string is the full description value from TransactionTypeEnum (e.g. ‘General payments to vendors or suppliers’ for PAYMENT). Written by connector sync at ingest time. |
| status | string (text, CHECK constraint) | ⚪ No | nullable; TEXT column with CHECK IN (…) — not a native PG enum. MikroORM @Enum without nativeEnumName stores the enum VALUE string. | Initiated, awaiting processing, Processing in progress, Authorized but not yet settled, Successfully completed and settled, Failed due to technical errors, Rejected by the recipient or system, Cancelled by the initiator or system, Reversed or rolled back, Held for review, Expired without completion | Lifecycle stage of the transaction. The stored DB string is the full description value from TransactionStatusEnum (e.g. ‘Successfully completed and settled’ for COMPLETED). Terminal success: ‘Successfully completed and settled’. Terminal failures: Failed/Rejected/Cancelled/Reversed descriptions. Transient: Initiated/Processing/Authorized descriptions. |
| transaction_external_id | string | ⚪ No | nullable; length ≤ 255; indexed (idx_transactions_external_id) for connector sync batch dedup | — | Provider-assigned identifier (e.g. Plaid transaction_id, Qonto entry id). Used by connector sync to resolve existing rows via IN (…) dedup query before creating new ones. |
| requested_execution_date | date | ⚪ No | nullable; stored as PostgreSQL DATE (no time component) | — | The date the initiator requested the transaction to be executed. May differ from executed_at when the bank settles on a different day than the request. |
| executed_at | timestamp | ✅ Yes | NOT NULL; indexed (idx_transactions_executed_status, idx_transactions_workspace_executed_active) | — | Timestamp when the transaction was executed by the banking system. Primary time axis for canvas burn-window reads and temporal ordering. Indexed with workspace_pk (partial WHERE deleted_at IS NULL) for anchor-most-recent and range-window query patterns. |
| booking_date | date | ⚪ No | nullable; stored as PostgreSQL DATE | — | Date the transaction was booked in the account ledger at the bank. Used in PSD2/Open Banking flows; may lag executed_at by one banking day. |
| value_date | date | ⚪ No | nullable; stored as PostgreSQL DATE | — | Date on which the funds become available (interest calculation date). Used in counterparty-bank discovery recency decay and micro-deposit fingerprint detection (Q12 window: value_date BETWEEN t1.value_date AND t1.value_date + INTERVAL ‘14 days’). |
| instructed_amount | object (JSONB) — { amount: number; currency: CurrencyCodeEnum } | ✅ Yes | NOT NULL; JSONB | — | The amount and currency as instructed by the initiator. Negative values represent debits from the workspace perspective; positive represent credits. currency is an ISO-4217 code. This is the canonical amount column used for ABS() aggregation in counterparty-bank scoring (Q8 threshold, Q12 micro_amount). |
| settlement_amount | object (JSONB) — { amount: number; currency: CurrencyCodeEnum } | ⚪ No | nullable; JSONB | — | The amount actually settled, which may differ from instructed_amount when FX conversion occurs. Present for cross-currency transactions where the instructed currency differs from the account’s base currency. |
| foreign_exchange | object (JSONB) — { rate: number; pair: string; source: CurrencyRateSourceEnum; at: Date } | ⚪ No | nullable; JSONB | source: ECB, FED, IMF, XE, OANDA, BANK, EXCHANGE_RATE_API, MANUAL, OTHER | FX rate data applied to convert instructed_amount into settlement_amount. pair is an ISO currency pair string (e.g. EUR/USD). at is the timestamp of the rate snapshot. source identifies the rate provider. |
| category_purpose | string | ⚪ No | nullable; length ≤ 10 | — | ISO 20022 category purpose code identifying the high-level purpose of the credit transfer (e.g. GDDS = goods and services, SUPP = supplier payment, SALA = salary). At most 10 characters per the ISO standard. |
| purpose_code | string | ⚪ No | nullable; length ≤ 10 | — | ISO 20022 purpose code providing additional detail beyond category_purpose (e.g. SUPP = supplier payment, SALA = salary, RENT = rental payment). At most 10 characters. |
| category_normalized | string (text) | ⚪ No | nullable; DB CHECK length ∈ [1, 200] (transactions_category_normalized_length); DB CHECK category_source = ‘classifier’ ⟺ category_confidence IS NOT NULL (transactions_category_source_confidence_invariant); DB CHECK category_normalized IS NULL OR category_source IS NOT NULL (transactions_category_normalized_provenance) | — | Human-readable normalized spending category (e.g. ‘Office Supplies’, ‘Travel & Accommodation’). Written by the W19 AI classifier (source = classifier), by explicit human override via POST /v1/workspaces/:wsId/transactions/:tId/category (source = user), by connector import (source = connector), or by a deterministic FieldRule (source = rule). The service layer enforces source = user on override writes, preventing provenance forgery. |
| category_confidence | string (decimal 4,3) | ⚪ No | nullable; DB CHECK ∈ [0, 1] (transactions_category_confidence_range); must be non-null iff category_source = ‘classifier’; forced to NULL on user override writes | — | Classifier confidence score in [0.000, 1.000] for the assigned category_normalized value. Present only when category_source = classifier; NULL for user, connector, and rule sources. Stored as decimal(4,3) to avoid float precision drift. |
| category_source | string (enum CategorySourceEnum) | ⚪ No | nullable; native PG enum category_source_enum; invariant: classifier ⟺ category_confidence IS NOT NULL | classifier, user, connector, rule | Provenance of the category_normalized value. classifier = W19 AI model (carries confidence). user = explicit human override. connector = value imported verbatim from connector mapping (e.g. Plaid personal_finance_category). rule = deterministic FieldRule (no LLM, no confidence). |
| remittance | object (JSONB) — { unstructured?: string; structured_reference?: string; reference_type?: RemittanceReferenceTypeEnum } | ⚪ No | nullable; JSONB; the unstructured field is the primary bank-discovery surface for PSD2/Qonto flows | reference_type: SCOR, QRR, ISR, IREF, EREF, PREF, MREF, CRED, USTD, NON | Payment reference/remittance information. unstructured holds the free-text memo (primary BIC, IBAN, and bank-name extraction surface for PSD2 sources). structured_reference is a ISO 11649 creditor reference or similar. reference_type classifies the structured reference scheme. Accessed as remittance->>‘unstructured’ in SQL. |
| fees | array (JSONB) — Array<{ type: TransactionFeeTypeEnum; amount: number; currency: CurrencyCodeEnum }> | ⚪ No | nullable; JSONB array | type stored strings: Standard Transfer fee, Wire Transfer or inter-bank transfer fee, Foreign Exchange conversion fee, ATM withdrawal or usage fee, Overdraft or insufficient funds fee, Monthly account maintenance fee, Card insurance, renewal or annual fee, Commission or percentage base fee, Late payment or violation penality, Miscellaneous or unclassified fee | Breakdown of fees associated with this transaction. Each entry carries the fee type (stored as the TransactionFeeTypeEnum VALUE string), absolute amount, and currency. Multiple fee entries are possible (e.g. a wire transfer may carry both a transfer and a currency conversion fee). Note: ‘penality’ is spelled as in the code/enum. |
| scheme | string (enum TransactionSchemeEnum) | ⚪ No | nullable; native PG enum transaction_scheme_enum | SEPA, SWIFT, ACH, FASTER_PAYMENTS, BACS, WIRE, OTHER | Payment rail / clearing scheme used to execute the transaction. SEPA = Eurozone credit transfer or direct debit. SWIFT = international correspondent banking. ACH = US domestic network (relevant for micro-deposit fingerprint Q12). FASTER_PAYMENTS = UK instant. BACS = UK direct debit. WIRE = generic bank wire. This column uses a native PG enum (nativeEnumName: transaction_scheme_enum) so the stored values are the enum keys. |
| raw_data | object (JSONB) — Record<string, unknown> | ⚪ No | nullable; JSONB; no GIN index — queried via full-document ILIKE/::text cast in counterparty-bank discovery queries Q7/Q10/Q11 | — | Connector-native payload preserved verbatim. Shape varies per connector. For Plaid/Mercury: includes counterparties[] (name, type, confidence_level, website, logo_url, entity_id), merchant_name, merchant_entity_id, personal_finance_category, payment_meta (ppd_id, by_order_of), payment_channel, transaction_code. For PSD2/Qonto: minimal; bank identity surfaces in remittance.unstructured instead. For GoCardless/Tink: raw_data->‘institution’->>‘name’. Always branch on originating connector before parsing. |
| created_at | timestamp, 🔒 system | ✅ Yes | NOT NULL; set by onCreate lifecycle hook | — | Timestamp when the transaction row was first persisted in Well. Distinct from executed_at (bank execution time). Set automatically by MikroORM onCreate; never writable by the API. |
| updated_at | timestamp, 🔒 system | ⚪ No | nullable; set by onCreate and onUpdate lifecycle hooks | — | Timestamp of the most recent update to this row (category assignment, enrichment, soft-delete). Set automatically by MikroORM on every flush. |
| deleted_at | timestamp | ⚪ No | nullable; soft-delete sentinel; all active queries must filter deleted_at IS NULL; partial indexes use WHERE deleted_at IS NULL | — | Soft-delete timestamp. NULL means the record is active. Set to current timestamp on deletion; never physically removed. Partial indexes (idx_transactions_account_balance_active, idx_transactions_workspace_executed_active, idx_transactions_classifier_confidence) exclude deleted rows to avoid index bloat. |
