Data Model

About this document

Audience: agentic tools writing migrations and repository code; humans reasoning about schema.
Purpose: define every table, column, index, and constraint in the application's database.
Companion documents: Architecture for the rules behind the schema; API Conventions for how this data is exposed.

---

1. Conventions

These rules apply to every table unless explicitly stated.

1.1 Identifiers

• Every table has an id UUID PRIMARY KEY generated as UUIDv7 at the application layer (not via DB function — keeps DB engine choices open).
• Foreign keys to entities in the same schema are <entity>_id UUID NOT NULL REFERENCES <entity>(id).
• Foreign keys to entities in a different schema are <entity>_id UUID NOT NULL with no FK constraint. The application layer enforces referential integrity. This is per N-DAT-002.

1.2 Timestamps

Every table has:

created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()

Soft-deletable tables additionally have:

deleted_at  TIMESTAMPTZ NULL

Shared mutable entities additionally have:

version  INTEGER NOT NULL DEFAULT 0

1.3 Optimistic Locking

All shared mutable entities have version INTEGER NOT NULL DEFAULT 0. Updates use:

UPDATE <table> SET <fields>, version = version + 1
WHERE id = :id AND version = :expected_version;

Zero rows updated → HTTP 409 with current entity state in the response body.

Per N-DAT-007.

1.4 Money & Quantity

• Money: BIGINT minor units + CHAR(3) currency code. Decimal places resolved from the currency.currency table (USD = 2, JPY = 0, crypto capped at 6 in this app).
• Crypto/stock quantity: BIGINT representing value × 10^6 (six decimal places of precision).
• No DECIMAL, FLOAT, or NUMBER for money or quantity — ever.
• In JSON: { "minor": <integer>, "currency": "<code>" }. Never a decimal number.

1.5 Soft Delete Indexing

Tables with deleted_at use partial indexes for hot-path lookups:

CREATE INDEX <name> ON <table>(...) WHERE deleted_at IS NULL;

1.6 Cross-Schema References

When a column references an entity in another schema, there is no FK constraint. The column stores only the ID; referential integrity is enforced at the application layer. Document cross-schema references in column comments:

author_user_id UUID NOT NULL,  -- references identity.user(id), validated at app layer

1.7 Naming

• Schema names: lowercase, singular noun (identity, family, ledger, investment, currency, import, audit, notification, shared).
• Table names: lowercase, singular noun (user, account, transaction).
• Column names: lowercase snake_case.
• Enum types: <schema>_<purpose> (e.g., family_role, ledger_account_type).

1.8 Enums

Defined as Postgres enum types in the relevant schema. Values are documented inline.

---

2. Schema: identity

Owns users, credentials, sessions, OAuth links, Telegram links, recovery codes, API keys.

2.1 identity.user

Column	Type	Notes
id	UUID PK	UUIDv7
username	TEXT NOT NULL UNIQUE	Lowercase. For OAuth users, supplied by the provider.
display_name	TEXT NULL	User-editable.
locale	TEXT NOT NULL DEFAULT 'en'	BCP 47 tag.
timezone	TEXT NOT NULL DEFAULT 'UTC'	IANA name. Used for personal display preferences.
is_disabled	BOOLEAN NOT NULL DEFAULT false	Operator can disable.
created_at, updated_at, deleted_at	TIMESTAMPTZ	Soft-deletable.
version	INTEGER

Indexes:

• UNIQUE (username) WHERE deleted_at IS NULL

2.2 identity.credential

Stores password hashes and other authenticator-factor secrets. One user can have multiple credentials over time (e.g., password + future TOTP).

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL FK → identity.user
kind	identity_credential_kind NOT NULL	Enum: password, totp (D), webauthn (D)
secret_hash	TEXT NOT NULL	Argon2id hash for password; encrypted secret for TOTP.
metadata	JSONB NOT NULL DEFAULT '{}'	Algorithm params, etc.
is_active	BOOLEAN NOT NULL DEFAULT true
created_at, updated_at	TIMESTAMPTZ	Hard-deletable.

Indexes:

• (user_id, kind, is_active)

2.3 identity.recovery_code

12 single-use codes per user (R-IDN-005).

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL FK → identity.user
code_hash	TEXT NOT NULL	Hash of the plaintext code.
used_at	TIMESTAMPTZ NULL	NULL until consumed.
created_at	TIMESTAMPTZ

Indexes:

• (user_id) WHERE used_at IS NULL — unused codes for a user.

2.4 identity.session

One row per active refresh token.

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL FK → identity.user
refresh_token_hash	TEXT NOT NULL UNIQUE
user_agent	TEXT NULL	For session-listing UI.
ip_address	INET NULL	For session-listing UI.
expires_at	TIMESTAMPTZ NOT NULL
revoked_at	TIMESTAMPTZ NULL	NULL while active.
created_at, updated_at	TIMESTAMPTZ

Indexes:

• (user_id) WHERE revoked_at IS NULL — active sessions for a user (revoked sessions are hidden).
• (expires_at) WHERE revoked_at IS NULL — for cleanup.

2.5 identity.oauth_link

Maps a user to an external OAuth identity.

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL FK → identity.user
provider	identity_oauth_provider NOT NULL	Enum: google (more later via D).
provider_subject	TEXT NOT NULL	Stable provider-side ID.
email	TEXT NULL	For display only; not used for auth.
created_at, updated_at	TIMESTAMPTZ

Indexes:

• UNIQUE (provider, provider_subject) — one provider identity = one user.
• (user_id) — list user's links.

2.6 identity.telegram_link

Maps a Telegram chat to a user (R-IDN-004, R-BOT-001).

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL FK → identity.user
telegram_chat_id	BIGINT NOT NULL UNIQUE	Telegram's identifier.
telegram_username	TEXT NULL	At time of linking; for display.
linked_at	TIMESTAMPTZ NOT NULL DEFAULT now()

Indexes:

• UNIQUE (telegram_chat_id)
• (user_id)

2.7 identity.api_key

External-service API keys are stored in the database so the operator endpoints can manage them. The operator credential itself is an environment-variable token and is never persisted. The on-disk config file is the bootstrap source; runtime DB state is authoritative.

Column	Type	Notes
id	UUID PK
label	TEXT NOT NULL	Human-readable name (e.g., "official telegram bot").
key_hash	TEXT NOT NULL UNIQUE	Hash of the plaintext key.
scope	identity_api_key_scope NOT NULL	Enum: reader, writer.
rate_limit_per_minute	INTEGER NULL	NULL = use default.
is_active	BOOLEAN NOT NULL DEFAULT true
created_at, updated_at	TIMESTAMPTZ

Indexes:

• UNIQUE (key_hash)
• (is_active)

2.8 Enum types

CREATE TYPE identity_credential_kind AS ENUM ('password', 'totp', 'webauthn');
CREATE TYPE identity_oauth_provider AS ENUM ('google');
CREATE TYPE identity_api_key_scope AS ENUM ('reader', 'writer');

---

3. Schema: family

Owns families, memberships, invitations.

3.1 family.family

Column	Type	Notes
id	UUID PK
name	TEXT NOT NULL
base_currency	CHAR(3) NOT NULL	Used for net worth and cross-account reports.
timezone	TEXT NOT NULL DEFAULT 'UTC'	Used for report period boundaries.
is_private	BOOLEAN NOT NULL DEFAULT false	true for the auto-created private family per R-FAM-001; undeletable.
created_at, updated_at, deleted_at	TIMESTAMPTZ	Soft-deletable.
version	INTEGER

Indexes:

• (deleted_at) — for cleanup.

3.2 family.family_member

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL FK → family.family
user_id	UUID NOT NULL	Cross-schema → identity.user
role	family_role NOT NULL	Enum: owner, editor, viewer.
joined_at	TIMESTAMPTZ NOT NULL DEFAULT now()
created_at, updated_at	TIMESTAMPTZ	Hard-delete on removal; per R-FAM-015 the data they authored stays with attribution preserved.
version	INTEGER

Indexes:

• UNIQUE (family_id, user_id)
• (user_id) — list user's families.
• (family_id, role) — for "find owner of family X" lookups.

Constraints:

• A trigger or application-layer check ensures exactly one owner per family at all times.

3.3 family.family_invitation

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL FK → family.family
invited_user_id	UUID NULL	Cross-schema → identity.user. NULL when invite is via shareable link not yet claimed.
invited_by_user_id	UUID NOT NULL	Cross-schema → identity.user.
role	family_role NOT NULL	Role to grant on acceptance.
invitation_link_token_hash	TEXT NULL UNIQUE	Hash of the token for shareable links.
status	family_invitation_status NOT NULL DEFAULT 'pending'	Enum: pending, accepted, rejected, expired, revoked.
expires_at	TIMESTAMPTZ NULL	NULL = no expiry.
created_at, updated_at	TIMESTAMPTZ

Indexes:

• (invited_user_id) WHERE status = 'pending' AND invited_user_id IS NOT NULL — clients poll for their pending invites.
• (invitation_link_token_hash) WHERE invitation_link_token_hash IS NOT NULL AND status = 'pending' — token redemption lookup.
• (family_id, status) — owner views pending invites.

3.4 Enum types

CREATE TYPE family_role AS ENUM ('owner', 'editor', 'viewer');
CREATE TYPE family_invitation_status AS ENUM ('pending', 'accepted', 'rejected', 'expired', 'revoked');

---

4. Schema: ledger

Owns accounts, transactions, transfer groups, categories, tags.

4.1 ledger.account

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL	Cross-schema → family.family
name	TEXT NOT NULL
account_type	ledger_account_type NOT NULL	Enum: cash, debit, credit, investment, crypto (D).
currency	CHAR(3) NOT NULL	Account base currency; immutable.
balance_minor	BIGINT NOT NULL DEFAULT 0	Stored balance in account currency, minor units. Sync-updated per N-PRF-004.
credit_limit_minor	BIGINT NULL	NULL except for credit accounts. Negative balance allowed down to -credit_limit_minor.
description	TEXT NULL
created_by_user_id	UUID NOT NULL	Cross-schema → identity.user. For "editor deletes own only" rule per R-FAM-010.
created_at, updated_at, deleted_at	TIMESTAMPTZ	Soft-deletable.
version	INTEGER

Indexes:

• (family_id) WHERE deleted_at IS NULL
• (family_id, account_type) WHERE deleted_at IS NULL

Constraints:

• CHECK (account_type != 'cash' OR balance_minor >= 0) — cash accounts non-negative invariant per R-ACC-002. (Application also enforces; DB enforces structural minimum.)
• CHECK (credit_limit_minor IS NULL OR credit_limit_minor >= 0)

4.2 ledger.transaction

Non-investment transactions only: income, expense, transfer. Investment-specific transactions live in investment.investment_transaction (see §5.2). This split aligns storage with bounded-context ownership: the ledger context owns regular financial activity; the investment context owns investment activity. Most users never touch the investment table at all.

A transaction in this table is always associated with an account whose account_type is cash, debit, or credit. Investment accounts (investment, future crypto) have their transactions in investment.investment_transaction.

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL	Cross-schema → family.family. Denormalized for query performance and authorization.
account_id	UUID NOT NULL FK → ledger.account	Must reference a non-investment account.
transaction_kind	ledger_transaction_kind NOT NULL	Enum: income, expense, transfer.
logical_date	DATE NOT NULL	Calendar date; what the user sees on the statement.
amount_minor	BIGINT NOT NULL	In account currency, minor units. Sign convention: positive for inflows, negative for outflows.
category_id	UUID NULL FK → ledger.category	Required for income/expense. NULL for transfers.
note	TEXT NULL
status	ledger_transaction_status NOT NULL DEFAULT 'cleared'	Enum: pending, cleared.
transfer_group_id	UUID NULL	Links the two sides of a transfer; NULL for non-transfers. See 4.3. The transfer group may also link to an investment-side transaction via investment.investment_transaction.transfer_group_id (cross-account transfer between regular and investment accounts).
original_amount_minor	BIGINT NULL	For info-only original-currency display per R-TRX-006.
original_currency	CHAR(3) NULL
original_fx_rate	NUMERIC(20, 10) NULL	Info-only; conversion already baked into amount_minor.
author_user_id	UUID NOT NULL	Cross-schema → identity.user. For attribution and "editor deletes own" rule.
created_at, updated_at, deleted_at	TIMESTAMPTZ	Soft-deletable.
version	INTEGER

Indexes:

• (family_id, logical_date DESC) WHERE deleted_at IS NULL — primary list query.
• (account_id, logical_date DESC) WHERE deleted_at IS NULL
• (family_id, category_id, logical_date) WHERE deleted_at IS NULL — spend-by-category report.
• (transfer_group_id) WHERE transfer_group_id IS NOT NULL — transfer pair lookup.
• (author_user_id) WHERE deleted_at IS NULL — for "what did this user create" (export, deletion).

Constraints:

• CHECK that category_id IS NOT NULL when transaction_kind IN ('income', 'expense').
• CHECK that category_id IS NULL when transaction_kind = 'transfer'.
• CHECK that transfer_group_id IS NOT NULL when transaction_kind = 'transfer'.
• Application layer enforces that account_id references a non-investment account.

4.3 ledger.transfer_group

Joins the two transactions of a transfer. A transfer group can link transactions across both ledger.transaction and investment.investment_transaction tables (e.g., a cash deposit from a debit account into an investment account is a transfer where one side lives in each table). The transfer group lives in the ledger schema as the canonical owner of the cross-table linking concept.

The presence of this table (as opposed to just storing the link in the transaction rows) gives transfer-level metadata a home and supports the cross-table linking cleanly.

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL	Cross-schema → family.family.
fx_rate_used	NUMERIC(20, 10) NULL	The applied FX rate (if cross-currency).
created_by_user_id	UUID NOT NULL	Cross-schema → identity.user.
created_at, updated_at	TIMESTAMPTZ	Hard-delete only when both linked transactions are also deleted.
version	INTEGER

Indexes:

• (family_id, created_at DESC)

Note: the two linked sides are discovered by querying both ledger.transaction and investment.investment_transaction for transfer_group_id = :id. The application layer guarantees exactly two transactions per group at any time.

4.4 ledger.category

Two-level hierarchy: a category has either no parent (top-level) or one parent. Per R-CAT-001.

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL	Cross-schema → family.family
parent_id	UUID NULL FK → ledger.category	NULL = top-level.
name	TEXT NOT NULL
color	TEXT NULL	UI hint.
icon	TEXT NULL	UI hint.
is_seeded	BOOLEAN NOT NULL DEFAULT false	True for default categories, for distinguishing in UI.
created_at, updated_at, deleted_at	TIMESTAMPTZ	Soft-deletable.
version	INTEGER

Indexes:

• (family_id, parent_id) WHERE deleted_at IS NULL
• UNIQUE (family_id, parent_id, name) WHERE deleted_at IS NULL — no duplicate names within a parent.

Constraints:

• A parent category must have parent_id IS NULL itself (enforced by check + app layer): no three-level hierarchies.

4.5 ledger.tag

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL	Cross-schema → family.family
name	TEXT NOT NULL
color	TEXT NULL
created_at, updated_at	TIMESTAMPTZ	Hard-deletable per requirements (no audit need for tags themselves).

Indexes:

• UNIQUE (family_id, name)

4.6 ledger.transaction_tag

Many-to-many between non-investment transactions and tags. Investment transactions do not use tags in MVP (tagging is a regular-activity organization feature; investment activity has its own structure via symbols and asset classes).

Column	Type	Notes
transaction_id	UUID NOT NULL FK → ledger.transaction
tag_id	UUID NOT NULL FK → ledger.tag
created_at	TIMESTAMPTZ

Primary key:

• (transaction_id, tag_id)

Indexes:

• (tag_id, transaction_id) — find all transactions for a tag.

4.7 Enum types

CREATE TYPE ledger_account_type AS ENUM ('cash', 'debit', 'credit', 'investment', 'crypto');
CREATE TYPE ledger_transaction_kind AS ENUM ('income', 'expense', 'transfer');
CREATE TYPE ledger_transaction_status AS ENUM ('pending', 'cleared');

ledger_transaction_status is shared by both ledger.transaction and investment.investment_transaction (defined in the ledger schema as the canonical owner of the pending/cleared concept; the investment table imports it). Alternatively, the status enum can be duplicated per schema if cross-schema enum referencing proves awkward in the chosen migration tool.

---

5. Schema: investment

Owns symbols, positions, price history, and investment transactions. Investment transactions live in this schema (not in ledger) to align storage with bounded-context ownership: the investment context owns the rules, the schema, and the data.

5.1 investment.symbol

Per R-SYM-001 and R-SYM-002: symbols with external IDs are deduplicated instance-wide; user-defined symbols are private to the creating user.

Column	Type	Notes
id	UUID PK
ticker	TEXT NOT NULL	E.g., AAPL, BTC, USDT-TRC20.
asset_class	investment_asset_class NOT NULL	Enum: stock, etf, bond, crypto, mutual_fund, commodity, other.
natural_currency	CHAR(3) NULL	E.g., USD for AAPL. NULL for crypto where there's no single quote currency.
external_id	TEXT NULL	E.g., ISIN for stocks, CoinGecko ID for crypto.
external_id_type	investment_external_id_type NULL	Enum: isin, coingecko, cusip (D), figi (D). NULL when symbol has no known external ID.
display_name	TEXT NOT NULL	Human-readable name.
created_by_user_id	UUID NULL	Cross-schema → identity.user. NULL only for symbols created by import/seeding.
is_user_defined	BOOLEAN NOT NULL	true if the symbol is private to the creating user. Orthogonal to external_id_type: a user-defined symbol may still have a known external ID.
created_at, updated_at	TIMESTAMPTZ	Hard-deletable; rare.

Indexes:

• UNIQUE (external_id_type, external_id) WHERE external_id IS NOT NULL — instance-wide dedup for canonical symbols.
• (created_by_user_id, ticker) WHERE is_user_defined = true — user's private symbol lookup.
• (ticker) — for autocomplete (filtered by visibility at app layer).

5.2 investment.investment_transaction

All investment-account activity. Stored separately from ledger.transaction so each table is narrow, well-typed, and aligned with its bounded context. Most users (those without investment accounts) never read or write this table.

A row in this table is always associated with an account whose account_type is investment (or crypto in deferred scope).

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL	Cross-schema → family.family. Denormalized for query performance and authorization.
account_id	UUID NOT NULL	Cross-schema → ledger.account. Must reference an investment-type account.
transaction_kind	investment_transaction_kind NOT NULL	Enum: buy, sell, dividend, deposit_cash, withdraw_cash, fee, split (D).
logical_date	DATE NOT NULL	Calendar date.
amount_minor	BIGINT NOT NULL	Cash effect on the account, in account currency, minor units. Sign convention: positive for cash inflows (sell, dividend, deposit_cash), negative for cash outflows (buy, withdraw_cash, fee). For split, amount is 0.
status	ledger_transaction_status NOT NULL DEFAULT 'cleared'	Reuses the ledger status enum.
transfer_group_id	UUID NULL	Set when this transaction participates in a cross-account transfer pair. The other side lives in ledger.transaction (regular account) or another row in this table (transfers between two investment accounts).
symbol_id	UUID NULL FK → investment.symbol	Required for buy, sell, dividend, split. NULL for deposit_cash, withdraw_cash, fee.
quantity	BIGINT NULL	Units — 10^6. Required for buy, sell. NULL otherwise.
price_per_unit_minor	BIGINT NULL	Trade price per unit in trade_currency, minor units. Required for buy, sell. NULL otherwise.
trade_currency	CHAR(3) NULL	Required for buy, sell. May differ from account currency; conversion is via FX rate at trade time.
trade_fx_rate	NUMERIC(20, 10) NULL	FX rate from trade_currency to account currency at trade time. NULL when trade and account currencies match.
split_ratio_numerator	INTEGER NULL	(D) Required for split (e.g., 4 in "4-for-1").
split_ratio_denominator	INTEGER NULL	(D) Required for split.
note	TEXT NULL
author_user_id	UUID NOT NULL	Cross-schema → identity.user. For attribution and "editor deletes own" rule.
created_at, updated_at, deleted_at	TIMESTAMPTZ	Soft-deletable.
version	INTEGER

Indexes:

• (family_id, logical_date DESC) WHERE deleted_at IS NULL — family-level investment activity list.
• (account_id, logical_date DESC) WHERE deleted_at IS NULL — per-account activity list.
• (symbol_id, logical_date DESC) WHERE deleted_at IS NULL AND symbol_id IS NOT NULL — per-symbol activity (used by position recomputation and per-symbol history).
• (transfer_group_id) WHERE transfer_group_id IS NOT NULL — transfer pair lookup.
• (author_user_id) WHERE deleted_at IS NULL.

Constraints (kind-specific field requirements). The application layer is the primary enforcer; DB-level CHECKs are defensive:

• kind IN ('buy', 'sell') requires symbol_id, quantity, price_per_unit_minor, trade_currency to all be NOT NULL.
• kind = 'dividend' requires symbol_id NOT NULL; quantity, price_per_unit_minor, trade_currency, trade_fx_rate must be NULL.
• (D) kind = 'split' requires symbol_id, split_ratio_numerator, split_ratio_denominator NOT NULL; amount_minor must be 0; cash-only fields must be NULL.
• kind IN ('deposit_cash', 'withdraw_cash', 'fee') requires symbol_id, quantity, price_per_unit_minor, trade_currency, trade_fx_rate, split_ratio_* to all be NULL.
• kind IN ('deposit_cash', 'withdraw_cash') requires transfer_group_id NOT NULL when the transaction is part of a transfer pair (application layer); fee and pure standalone deposits/withdrawals leave it NULL.

5.3 investment.position

One row per (account, symbol); per R-INV-003 a closed position is soft-deleted.

Column	Type	Notes
id	UUID PK
account_id	UUID NOT NULL	Cross-schema → ledger.account; must be of type investment or crypto.
symbol_id	UUID NOT NULL FK → investment.symbol
quantity	BIGINT NOT NULL	Units — 10^6.
avg_cost_per_unit_minor	BIGINT NOT NULL	Average cost in cost_currency, minor units.
cost_currency	CHAR(3) NOT NULL	The currency basis for cost; usually account currency.
created_at, updated_at, deleted_at	TIMESTAMPTZ	Soft-deletable on full sale.
version	INTEGER

Indexes:

• UNIQUE (account_id, symbol_id) WHERE deleted_at IS NULL — one open position per (account, symbol).

5.4 investment.price_history

Column	Type	Notes
id	UUID PK
symbol_id	UUID NOT NULL FK → investment.symbol
as_of_date	DATE NOT NULL
price_minor	BIGINT NOT NULL	In price_currency, minor units.
price_currency	CHAR(3) NOT NULL	Usually the symbol's natural currency.
source	investment_price_source NOT NULL DEFAULT 'manual'	Enum: manual, coingecko (D), exchange (D).
recorded_by_user_id	UUID NULL	Cross-schema → identity.user. NULL when source != 'manual'.
created_at	TIMESTAMPTZ

Indexes:

• (symbol_id, as_of_date DESC) — current-price lookup.
• UNIQUE (symbol_id, as_of_date, source) — no dupes from same source on same date.

5.5 Enum types

CREATE TYPE investment_asset_class AS ENUM (
  'stock', 'etf', 'bond', 'crypto', 'mutual_fund', 'commodity', 'other'
);
CREATE TYPE investment_external_id_type AS ENUM (
  'isin', 'coingecko', 'cusip', 'figi'
);
CREATE TYPE investment_price_source AS ENUM ('manual', 'coingecko', 'exchange');
CREATE TYPE investment_transaction_kind AS ENUM (
  'buy', 'sell', 'dividend',
  'deposit_cash', 'withdraw_cash',
  'fee', 'split' -- (D)
);

---

6. Schema: currency

Owns currency definitions and FX rate history.

6.1 currency.currency

Seeded with major fiat currencies and common crypto.

Column	Type	Notes
code	CHAR(3) PK	ISO 4217 for fiat; well-known symbols for crypto.
name	TEXT NOT NULL	E.g., "United States Dollar".
symbol	TEXT NULL	E.g., "$", "₴".
decimal_places	SMALLINT NOT NULL	E.g., 2 for USD, 0 for JPY, 6 for crypto (capped).
kind	currency_kind NOT NULL	Enum: fiat, crypto.
is_active	BOOLEAN NOT NULL DEFAULT true

6.2 currency.fx_rate_history

Column	Type	Notes
id	UUID PK
from_currency	CHAR(3) NOT NULL FK → currency.currency
to_currency	CHAR(3) NOT NULL FK → currency.currency
as_of_date	DATE NOT NULL
rate	NUMERIC(20, 10) NOT NULL	Multiply from by this to get to.
source	currency_rate_source NOT NULL DEFAULT 'manual'	Enum: manual, nbu (D), ecb (D), fixer (D).
recorded_by_user_id	UUID NULL	Cross-schema → identity.user. NULL when source != 'manual'.
created_at	TIMESTAMPTZ

Indexes:

• (from_currency, to_currency, as_of_date DESC) — find latest known rate.
• UNIQUE (from_currency, to_currency, as_of_date, source)

Constraints:

• CHECK (from_currency != to_currency)
• CHECK (rate > 0)

6.3 Enum types

CREATE TYPE currency_kind AS ENUM ('fiat', 'crypto');
CREATE TYPE currency_rate_source AS ENUM ('manual', 'nbu', 'ecb', 'fixer');

---

7. Schema: import

Owns import job tracking and staging rows.

7.1 import.import_job

Column	Type	Notes
id	UUID PK
family_id	UUID NOT NULL	Cross-schema → family.family
account_id	UUID NOT NULL	Cross-schema → ledger.account
importer	TEXT NOT NULL	e.g., csv-app-format. Plugin-based per R-IMP-003.
source_file_path	TEXT NOT NULL	Path within FileStorage.
status	import_job_status NOT NULL DEFAULT 'queued'	Enum: queued, running, completed, failed.
total_rows	INTEGER NULL	Filled when parsing completes.
success_count	INTEGER NULL
error_count	INTEGER NULL
error_summary	JSONB NULL	Error details.
created_by_user_id	UUID NOT NULL	Cross-schema → identity.user
created_at, updated_at	TIMESTAMPTZ
started_at	TIMESTAMPTZ NULL
finished_at	TIMESTAMPTZ NULL

Indexes:

• (family_id, created_at DESC)
• (status) WHERE status IN ('queued', 'running')

7.2 import.import_row

Staging table for parsed rows pending application. Mostly transient — rows are deleted after job completion.

Column	Type	Notes
id	UUID PK
import_job_id	UUID NOT NULL FK → import.import_job
row_number	INTEGER NOT NULL	1-indexed source row.
parsed_payload	JSONB NOT NULL	Normalized fields from the importer.
status	import_row_status NOT NULL DEFAULT 'pending'	Enum: pending, applied, skipped, failed.
applied_transaction_id	UUID NULL	Cross-schema → ledger.transaction or investment.investment_transaction. Resolved by applied_transaction_table to disambiguate. Set when status=applied.
applied_transaction_table	TEXT NULL	Either 'ledger.transaction' or 'investment.investment_transaction'. NULL until applied.
error_message	TEXT NULL
created_at, updated_at	TIMESTAMPTZ

Indexes:

• (import_job_id, row_number)
• (import_job_id, status)

7.3 Enum types

CREATE TYPE import_job_status AS ENUM ('queued', 'running', 'completed', 'failed');
CREATE TYPE import_row_status AS ENUM ('pending', 'applied', 'skipped', 'failed');

---

8. Schema: audit

Owns the audit log per R-AUD-001 through R-AUD-005.

8.1 audit.audit_entry

Column	Type	Notes
id	UUID PK
family_id	UUID NULL	Cross-schema → family.family. NULL for non-family-scoped events (e.g., user-level changes).
actor_user_id	UUID NULL	Cross-schema → identity.user. NULL for system actions.
actor_api_key_id	UUID NULL	Cross-schema → identity.api_key. Set when action came via external service.
entity_type	TEXT NOT NULL	E.g., ledger.transaction, family.family_member.
entity_id	UUID NOT NULL
action	audit_action NOT NULL	Enum: created, updated, deleted, restored.
before_state	JSONB NULL	NULL on created.
after_state	JSONB NULL	NULL on deleted.
event_id	UUID NULL	Cross-schema → shared.events. The originating event; no FK constraint (cross-schema ref per §1.1).
occurred_at	TIMESTAMPTZ NOT NULL
created_at	TIMESTAMPTZ

Indexes:

• (family_id, occurred_at DESC) WHERE family_id IS NOT NULL — owner audit view.
• (actor_user_id, occurred_at DESC)
• (entity_type, entity_id, occurred_at DESC) — entity-history view.

8.2 Enum types

CREATE TYPE audit_action AS ENUM ('created', 'updated', 'deleted', 'restored');

---

9. Schema: notification

Owns in-app notifications. Channel adapters and per-user preferences are D.

9.1 notification.notification

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL	Cross-schema → identity.user. Recipient.
family_id	UUID NULL	Cross-schema → family.family. NULL for non-family notifications.
kind	notification_kind NOT NULL	Enum: family_invitation, import_completed, import_failed, large_transaction (D), other (D).
payload	JSONB NOT NULL	Kind-specific data.
status	notification_status NOT NULL DEFAULT 'unread'	Enum: unread, read, handled.
created_at, updated_at	TIMESTAMPTZ
read_at	TIMESTAMPTZ NULL
handled_at	TIMESTAMPTZ NULL

Indexes:

• (user_id, status, created_at DESC) WHERE status = 'unread' — pending-notifications query.
• (family_id, kind) — cleanup for orphaned notifications.

9.2 Enum types

CREATE TYPE notification_kind AS ENUM (
  'family_invitation', 'import_completed', 'import_failed', 'large_transaction'
);
CREATE TYPE notification_status AS ENUM ('unread', 'read', 'handled');

---

10. Schema: shared

Infrastructure-level tables used by the application framework itself.

10.1 shared.events

The durable record of all domain events. Per N-ARC-006.

Column	Type	Notes
id	UUID PK	UUIDv7. The event ID used for handler dedup.
event_type	TEXT NOT NULL	E.g., ledger.transaction.created.
event_version	INTEGER NOT NULL	Per N-ARC-007.
family_id	UUID NULL	When applicable.
user_id	UUID NULL	Actor when applicable.
occurred_at	TIMESTAMPTZ NOT NULL	When the event happened.
payload	JSONB NOT NULL	Event-type-specific.
created_at	TIMESTAMPTZ NOT NULL DEFAULT now()	When persisted.

Indexes:

• (event_type, occurred_at DESC) — replay by type.
• (family_id, occurred_at DESC) WHERE family_id IS NOT NULL
• (occurred_at DESC) — global recent-events view.

10.2 shared.event_handler_state

Tracks handler progress for retries and replay.

Column	Type	Notes
event_id	UUID NOT NULL FK → shared.events
handler_name	TEXT NOT NULL	Stable identifier of the handler.
status	event_handler_status NOT NULL	Enum: pending, succeeded, failed, dead_letter.
attempts	INTEGER NOT NULL DEFAULT 0
last_error	TEXT NULL
next_attempt_at	TIMESTAMPTZ NULL	For retry scheduling.
created_at, updated_at	TIMESTAMPTZ

Primary key:

• (event_id, handler_name)

Indexes:

• (status, next_attempt_at) WHERE status IN ('pending', 'failed') — retry scheduler.
• (status) WHERE status = 'dead_letter' — operator inspection.

10.3 shared.idempotency_key

Primary cache is Redis for speed; this table provides durability so an idempotency key isn't lost across Redis restarts. Per N-API-004.

Column	Type	Notes
key	TEXT PK	Format: <user_id>:<idempotency_key>.
user_id	UUID NOT NULL	Cross-schema → identity.user.
request_fingerprint	TEXT NOT NULL	Hash of the canonical request body, to detect collision.
response_status	INTEGER NOT NULL	HTTP status of the cached response.
response_body	JSONB NOT NULL
created_at	TIMESTAMPTZ NOT NULL DEFAULT now()
expires_at	TIMESTAMPTZ NOT NULL

Indexes:

• (expires_at) — for cleanup.
• (user_id, created_at DESC) — debugging.

10.4 Enum types

CREATE TYPE event_handler_status AS ENUM ('pending', 'succeeded', 'failed', 'dead_letter');

---

11. Cross-Cutting Concerns

11.1 Schema Initialization Order

When migrations run on a fresh database, schemas must be created in dependency order:

1. currency (no dependencies)
2. identity (no dependencies)
3. family (logical dep on identity, no FK)
4. shared (no dependencies)
5. ledger (logical deps on family, identity, currency)
6. investment (logical deps on identity, currency, ledger)
7. import (logical deps on family, identity, ledger)
8. audit (logical deps on family, identity, shared)
9. notification (logical deps on identity, family)

Since there are no cross-schema FK constraints, the DB itself doesn't enforce order — but seed data and reasoning depend on it.

11.2 Seeding

A migration step seeds:

• currency.currency rows for major fiat (USD, EUR, UAH, GBP, JPY, CHF, CAD, AUD) and common crypto (BTC, ETH, USDT, USDC).
• A default category set (loaded into a family on family creation; not into the DB at migration time, since categories are family-scoped).

11.3 Soft-Delete Cascade

When a ledger.account is soft-deleted, its ledger.transaction rows (or investment.investment_transaction rows for investment accounts) remain in place but are excluded from balance and report queries by the partial-index pattern. They continue to appear in audit and export outputs.

When a family.family is soft-deleted (cascade from owner deletion or explicit owner action), an event is emitted; subscribers in Ledger, Investment, etc. handle their own soft-cascade.

11.4 GDPR Hard Delete

GDPR deletion (R-GDP-003, scope D) is a separate process that hard-deletes user-owned data. The exact procedure is designed when R-GDP-003 is implemented; the schema accommodates it through nullable created_by_user_id / author_user_id columns where reasonable, allowing data to remain attributable to "deleted user" rather than orphaned.

11.5 Computed-vs-Stored Fields

Field	Approach	Why
ledger.account.balance_minor	Stored, sync-updated	O(1) reads; per N-PRF-004.
Net worth	Computed on read	Cached in Redis.
Position current value	Computed on read	Cached in Redis.
Spend by category	Computed on read	Cached in Redis.

---

12. Glossary of Tables

A condensed lookup for "where does X live?":

Concept	Schema.Table
User	identity.user
Password	identity.credential (kind=password)
TOTP secret (D)	identity.credential (kind=totp)
Recovery codes	identity.recovery_code
Active session	identity.session
Google OAuth link	identity.oauth_link
Telegram link	identity.telegram_link
External-service API key	identity.api_key
Family	family.family
Family membership	family.family_member
Pending invitation	family.family_invitation
Account	ledger.account
Regular transaction (income/expense/transfer)	ledger.transaction
Investment transaction (buy/sell/dividend/split (D)/...)	investment.investment_transaction
Transfer pairing (across both transaction tables)	ledger.transfer_group
Category	ledger.category
Tag	ledger.tag
Symbol	investment.symbol
Open position	investment.position
Price record	investment.price_history
Currency definition	currency.currency
FX rate record	currency.fx_rate_history
Import job	import.import_job
Import row	import.import_row
Audit entry	audit.audit_entry
Notification	notification.notification
Domain event	shared.events
Event handler progress	shared.event_handler_state
Idempotency cache	shared.idempotency_key

---

13. Where to Go Next

• Architectural rules behind this schema: Architecture
• How this data is exposed via API: API Conventions
• What requirements each table satisfies: Requirements (use the cross-references in inline comments)
• Domain term definitions: Glossary