Banking

Banking entities separate logical business accounts from physical open-banking sessions. Logical accounts are used by contracts, properties, holders, and transactions. Integration sessions are provider-specific connection records.

bank_accounts

Purpose

Logical representation of a bank account.

Suggested attributes

Field	Description
id	Primary identifier.
bank_name	Bank name.
iban	IBAN.
bic	BIC/SWIFT code.
account_name	Human-readable account name.
currency	Account currency.
initial_balance	Initial balance entered when a bank account is created manually. Default 0.00.
initial_balance_currency	Currency of the initial balance. Defaults to account currency.
current_balance	Last account balance amount fetched during direct bank transaction synchronization. Nullable for manual accounts or providers that do not return balances.
current_balance_currency	Currency returned with the balance snapshot. Falls back to account currency when the provider omits it.
current_balance_at	Timestamp associated with the balance snapshot, or the sync timestamp when the provider omits a reference date.
current_balance_source	Provider balance type selected for display, for example interimAvailable, interimBooked, or closingBooked.
current_balance_raw_json	Raw provider balance object selected for the persisted snapshot.
is_dedicated_to_rentals	Whether the account is dedicated to rental operations.
notes	Free-form notes.
created_at	Creation timestamp.
updated_at	Last update timestamp.

Relationships

Relationship	Description
bank_accounts to bank_account_holders	One account can have many holders.
bank_accounts to property_bank_accounts	One account can be linked to many properties.
bank_accounts to bank_account_connections	One account can be linked to provider sessions.
bank_accounts to bank_transactions	One account has many imported transactions.

Notes and design rationale

Logical account data should remain stable even if the open-banking provider, session, or provider account identifier changes. For direct connections, the current balance fields are refreshed from the provider during transaction sync. For manually created accounts with CSV imports, banking-service recalculates current_balance as initial_balance + SUM(bank_transactions.amount) after each successful CSV import. These balance fields are a display snapshot, not a ledger or source of truth for historical balances.

bank_account_holders

Purpose

Tracks holders and co-holders of bank accounts.

Suggested attributes

Field	Description
id	Primary identifier.
bank_account_id	Linked logical bank account.
party_id	Holder party.
role	HOLDER, CO_HOLDER, or AUTHORIZED_USER.
ownership_percentage	Account ownership or beneficial share when relevant.
notes	Free-form notes.

Relationships

Relationship	Description
bank_account_holders.bank_account_id to bank_accounts.id	Many holders can belong to one account.
bank_account_holders.party_id to parties.id	One party can hold many accounts.

Notes and design rationale

This table supports co-owned accounts and delegated users without duplicating bank account records.

property_bank_accounts

Purpose

Links logical bank accounts to properties.

Suggested attributes

Field	Description
id	Primary identifier.
property_id	Linked property.
bank_account_id	Linked logical bank account.
purpose	RENT_COLLECTION, EXPENSES, DEPOSIT, or GENERIC.
start_date	Link start date.
end_date	Link end date.
is_default	Whether this is the default account for the purpose.
notes	Free-form notes.

Relationships

Relationship	Description
property_bank_accounts.property_id to properties.id	One property can use many accounts.
property_bank_accounts.bank_account_id to bank_accounts.id	One account can serve many properties.

Notes and design rationale

One bank account may be used for several properties. One property may use different accounts for rent collection, deposits, and expenses.

banking_sessions

Purpose

Persists OAuth AIS authorization sessions created via Enable Banking. One record per user per bank per environment. Survives service restarts and allows audit, status tracking, and session recovery without re-authorization.

Attributes

Field	Type	Description
id	INT UNSIGNED	Primary identifier.
user_id	VARCHAR(128)	Platform user who authorized the session. References users.id.
env	VARCHAR(16)	Runtime environment (LOCAL, TEST, LIVE). Part of the unique key to isolate environments.
bank_name	VARCHAR(100)	Bank name as returned by Enable Banking (e.g. UniCredit).
country	CHAR(2)	ISO 3166-1 alpha-2 country code of the bank (e.g. IT).
session_id	VARCHAR(128)	Enable Banking session identifier. Used to call /sessions/{session_id} and DELETE /sessions/{session_id}.
authorization_id	VARCHAR(128)	Enable Banking authorization flow identifier. Nullable.
valid_until	DATETIME	Consent expiry date as declared by Enable Banking (typically 90 days from authorization).
authorized_at	DATETIME	Timestamp when the OAuth callback completed successfully.
status	ENUM	AUTHORIZED, EXPIRED, REVOKED.
accounts	JSON	Array of Enable Banking account UIDs authorized in this session.
session_json	JSON	Full session payload as returned by the callback flow. Used for complete recovery after service restart.
request_count	INT UNSIGNED	Number of API calls made using this session. Observability field. Default 0.
last_accessed_at	DATETIME	Timestamp of the last API call using this session. Nullable.
last_action	VARCHAR(64)	Last operation performed (balances, transactions, details, authorized). Nullable.
last_account_id	VARCHAR(128)	Enable Banking UID of the last account accessed. Nullable.
created_at	DATETIME	Row creation timestamp.
updated_at	DATETIME	Last row update timestamp. Auto-managed by the database.

Constraints

Constraint	Fields	Description
PRIMARY KEY	id	Auto-increment surrogate key.
UNIQUE uq_user_bank	user_id, bank_name, country, env	One active session per user per bank per environment. A new authorization for the same bank overwrites via upsert.
INDEX idx_session_id	session_id	Fast lookup by Enable Banking session ID for revocation.
INDEX idx_user_id	user_id	Fast lookup of all sessions for a user.
INDEX idx_valid_until	valid_until	Supports expiry sweeps.
INDEX idx_status	status	Filters active vs revoked sessions.

Relationships

Relationship	Description
banking_sessions.user_id to users.id	Each session belongs to one platform user.
banking_sessions to bank_account_connections	A session can expose or connect to many logical bank accounts.

Notes and design rationale

banking_sessions represents integration and authorization state, not a business bank account. The user_id column links the OAuth consent to a specific platform user, enabling per-user session management and audit.

The session_json column stores the complete callback payload so that account details, IBAN data, and authorization metadata can be recovered after a service restart without requiring the user to re-authorize.

The status field is the authoritative state for the consent lifecycle. A session transitions from AUTHORIZED to REVOKED only through an explicit DELETE /sessions/{session_id} call to Enable Banking. EXPIRED is derived at query time from valid_until when no explicit revocation has occurred.

bank_account_connections

Purpose

Links logical bank accounts to physical or open-banking sessions.

Suggested attributes

Field	Description
id	Primary identifier.
bank_account_id	Logical bank account.
banking_session_id	Physical/open-banking session.
provider_account_id	Account identifier from the provider.
connection_status	Current connection status.
last_sync_at	Last successful synchronization timestamp.
notes	Free-form notes.

Relationships

Relationship	Description
bank_account_connections.bank_account_id to bank_accounts.id	One logical account can have many integration connections over time.
bank_account_connections.banking_session_id to banking_sessions.id	One session can connect several logical accounts.

Notes and design rationale

This avoids mixing business account data with provider-specific connection state.

bank_csv_import_templates

Purpose

Stores reusable CSV bank statement import mappings. Templates can be private to one user or public after administrative approval.

Suggested attributes

Field	Description
id	Primary identifier.
name	Human-readable template name.
bank_name	Bank or issuer name.
visibility	PRIVATE or PUBLIC.
owner_user_id	Owner for private templates.
source_template_id	Original private template when a public clone is approved.
delimiter, encoding, has_header, skip_rows	CSV parsing options.
date_format, decimal_separator, thousands_separator, amount_mode	Field parsing conventions.
mapping_json	Mapping from CSV columns to canonical bank transaction fields.
transform_json	Optional transformation rules.
dedupe_json	Deduplication strategy.
sample_headers_json	Non-sensitive header sample for recognition and review.

Canonical CSV bank transaction model

CSV templates do not map columns directly to the physical bank_transactions table. They map source columns to a canonical movement model owned by banking-service; the service then converts canonical records into database rows. This keeps frontend builders stable when the physical schema changes.

The canonical amount is signed. During persistence, banking-service stores an absolute amount and derives transaction_type as DEBIT for negative values and CREDIT for positive values, unless an explicit valid type is supplied.

Canonical field	Required	CSV mappable	Physical target	Notes
booking_date	Yes	Yes	transaction_date	Date normalized as YYYY-MM-DD.
value_date	No	Yes	value_date	Defaults to booking_date.
amount	Yes	Yes	amount, transaction_type	Signed decimal in the canonical model.
currency	No	Yes	currency	ISO 4217 code. Defaults to EUR.
description	No	Yes	description	Bank statement description.
counterparty_name	No	Yes	counterparty_name	Counterparty display name.
counterparty_iban	No	Yes	counterparty_iban	Counterparty IBAN.
reference	No	Yes	external_reference	Bank reference, CRO, TRN, mandate reference, or equivalent.
external_id	No	Yes	external_transaction_id	Stable transaction identifier from the bank when available.
transaction_type	No	Yes	transaction_type	Optional CREDIT or DEBIT; normally derived from amount.
balance_after	No	Yes	raw_json.balance_after	Kept in raw_json until a dedicated physical column is needed.
category_hint	No	Yes	raw_json.category_hint	Optional bank-provided classification hint.
property_id	No	No	property_id	Import context field, not mapped from CSV by default.
raw_row	No	No	raw_json.raw_row	Parsed raw CSV row snapshot for audit/debugging.

The code-level contract is exposed by backend/banking-service/modules/bankTransactionCanonicalModel.js.

CSV import backend APIs

banking-service owns CSV parsing and template persistence. Browser-facing flows must call banking-service; the service validates account write access through data-service before any endpoint writes or synchronizes transactions for a bank_account_id.

Endpoint	Purpose
POST /csv-import/preview	Parses a CSV payload and returns detected columns, preview rows, inferred formats, canonical mapping suggestions, and warnings. Accepts JSON { csvText, options } or raw text/csv.
POST /csv-import/validate-mapping	Converts CSV rows to the canonical bank transaction model, validates required fields, returns normalized preview, row-level errors, and duplicate estimates.
POST /csv-import/runs	Creates an import run, inserts non-duplicate bank_transactions, stores row errors, and returns inserted/duplicate/rejected totals.
GET /csv-import/runs	Lists import runs for the authenticated user, optionally filtered by bank_account_id.
GET /csv-import/runs/:id	Returns one import run with row errors and transactions created by that run.
GET /csv-import/templates	Lists active private CSV templates owned by the authenticated user plus approved public templates.
POST /csv-import/templates	Creates a private template owned by the authenticated user. visibility is forced to PRIVATE.
POST /csv-import/templates/:id/publication-requests	Submits an owned private template for administrative publication review. Duplicate open submissions return the existing request.
PUT /csv-import/templates/:id	Updates an active private template owned by the authenticated user and increments its version.
DELETE /csv-import/templates/:id	Deletes an unused private template, or archives it when import runs already reference it.
GET /csv-import/template-publication-requests	Admin-only review queue for publication requests. Supports optional status.
GET /csv-import/template-publication-requests/:id	Admin-only detail including requester, reviewed template, and public clone when available.
POST /csv-import/template-publication-requests/:id/approve	Admin-only approval. Clones the submitted private template as PUBLIC.
POST /csv-import/template-publication-requests/:id/reject	Admin-only rejection with optional review note.

CSV preview supports ;, ,, tab, and pipe delimiters, optional header rows, skipped rows, row/size limits, and basic inference for dates, decimal separators, amount columns, and common bank-statement headers.

CSV import deduplication is skip-only. Existing transactions are never updated by CSV import. When external_transaction_id is present, it is checked first for the same bank_account_id; otherwise the importer uses deterministic import_hash. Duplicates inside the same uploaded file are also skipped.

Public CSV templates are created only by cloning an approved private template. The public clone stores parsing options, canonical field mapping, and sanitized header samples; it does not store uploaded bank statement rows, transaction values, import-run payloads, or live banking connection data.

CSV import hardening applies at multiple points: parser requests enforce size and row limits, uploaded file names are sanitized before audit persistence, public-template samples are limited to header names, and import logs are row/error oriented so they avoid exposing full statement contents unless needed for private run debugging.

bank_csv_import_runs

Purpose

Audits each CSV import execution against a bank account.

Suggested attributes

Field	Description
id	Primary identifier.
template_id	Template used for the import.
bank_account_id	Target logical account.
uploaded_by_user_id	User who started the import.
original_file_name	Source file name.
file_sha256	File hash for audit and duplicate detection.
status	Import lifecycle status.
total_rows, parsed_rows, inserted_rows, duplicate_rows, rejected_rows	Execution counters.
summary_json, preview_json, error_json	Structured execution details.

bank_csv_import_run_errors

Purpose

Stores row-level warnings and errors for CSV imports.

Suggested attributes

Field	Description
id	Primary identifier.
run_id	Import run.
row_number	CSV row number.
severity	WARNING or ERROR.
error_code	Machine-readable code.
error_message	Human-readable description.
source_column	CSV column involved when relevant.
raw_row_json	Raw row snapshot for audit/debugging.

bank_csv_import_template_publication_requests

Purpose

Tracks requests to promote a private CSV import template to a public reusable template.

Suggested attributes

Field	Description
id	Primary identifier.
template_id	Private template submitted for review.
requested_by_user_id	User who submitted the request.
reviewed_by_user_id	Admin reviewer.
status	SUBMITTED, APPROVED, REJECTED, or CANCELLED.
request_note, review_note	Free-form notes.
public_template_id	Public clone created on approval.

bank_transactions

Purpose

Represents actual imported bank movements.

Suggested attributes

Field	Description
id	Primary identifier.
bank_account_id	Logical account where the transaction occurred.
property_id	Optional property attribution. Nullable at import time.
source_import_run_id	CSV import run that created the movement, when applicable.
transaction_date	Booking transaction date.
value_date	Value date.
amount	Signed or absolute transaction amount, according to implementation convention.
currency	Transaction currency.
description	Bank description.
counterparty_name	Counterparty name.
counterparty_iban	Counterparty IBAN.
transaction_type	CREDIT or DEBIT.
external_transaction_id	Provider or bank transaction identifier when available.
external_reference	Provider or bank reference.
import_hash	Deterministic hash used to skip duplicate CSV-imported transactions per account.
classification_status	UNCLASSIFIED, AUTO_CLASSIFIED, or MANUALLY_CLASSIFIED.
raw_json	Raw canonical/import row for CSV/manual imports.
raw_payload	Original provider payload for audit/debugging.
created_at	Creation timestamp.
updated_at	Last update timestamp.

Relationships

Relationship	Description
bank_transactions.bank_account_id to bank_accounts.id	Each transaction belongs to one account.
bank_transactions.property_id to properties.id	Optional direct property classification.
bank_transactions.source_import_run_id to bank_csv_import_runs.id	Links transactions back to a CSV import run.
bank_transactions to bank_transaction_splits	One transaction can be split into many accounting lines.
bank_transactions to rent_payment_matches	Transactions can reconcile rent installments.
bank_transactions to expense_payment_matches	Transactions can reconcile expected expenses.

Notes and design rationale

property_id may be null when a transaction is first imported. Later classification can assign a property and create one or more split lines.

Graphical local diagrams

bank_accounts

bank_account_holders

property_bank_accounts

banking_sessions

bank_account_connections

bank_transactions