jarca-ledger-mcp/docs/data-model.md

계좌 평가 이력과 자산 마감 데이터 모델

목적과 범위

계좌 평가 증거, 수동 자산, append-only 자산 마감과 기존 ledger entities의 실제 SQLite 정본을 기록한다.

• 포함 범위: accounts, transactions, manual_assets, account_valuations, asset_snapshots, asset_snapshot_runs, reconciliation_sessions, review_decisions, import_batches, schema_meta
• 제외 범위: auth cookie/session, external price/FX store, inferred transactions, actual credentials/data

정본 엔터티

accounts — 기존 계좌

• 책임: 계좌 identity/current display balance와 account type/liquidity metadata
• 식별자: id
• 생명 주기: 기존 CRUD; valuation history는 별도 append-only

필드

필드	타입	필수 여부	기본값	허용 값·형식	단위·정밀도	민감정보
id	TEXT	필수	없음	generated account id	식별자	아니오
name	TEXT	필수	없음	non-empty	없음	아니오
type	TEXT	필수	bank	bank/card/cash/investment/loan/real_estate/other	enum	아니오
currency	TEXT	필수	KRW	supported currency	ISO code	아니오
last_balance	INTEGER	선택	NULL	exact integer	currency unit	아니오
last_balance_basis_date	TEXT	선택	''	YYYY-MM-DD or unknown	date of the evidence represented by last_balance; empty/unknown is explicit	아니오
last_synced_at	TEXT	선택	''	ISO date/time	timestamp	아니오
statement_status	TEXT	필수	unknown	existing status enum	없음	아니오
liquidity_bucket	TEXT	필수	other	cash/deposit/investment/real_estate/other/loan	enum	아니오
account_number_masked	TEXT	필수	''	masked only	redacted	민감 가능

account-valuations — 기준일 계좌 평가

• 책임: 날짜가 지정된 평가 evidence와 선택/correction provenance. investment principal은 reported principal amount이고 loan principal은 outstanding principal이다.
• 식별자: id
• 생명 주기: insert-only; correction은 supersedes_id child

필드

필드	타입	필수 여부	기본값	허용 값·형식	단위·정밀도	민감정보
id	TEXT	필수	없음	generated valuation id	식별자	아니오
account_id	TEXT FK	필수	없음	accounts.id	관계	아니오
basis_date	TEXT	필수	없음	YYYY-MM-DD	date	아니오
amount	INTEGER	필수	없음	exact signed integer	currency unit	아니오
currency	TEXT	필수	KRW	supported currency	ISO code	아니오
valuation_kind	TEXT	필수	balance	balance/cash_balance/total_value/loan/loan_balance	purpose	아니오
source_type	TEXT	필수	manual	manual/import/account_write/reconciliation/snapshot	enum	아니오
source_ref	TEXT	필수	''	source/hash/reference	opaque	민감 가능
verification_status	TEXT	필수	unverified	verified/unverified/rejected	enum	아니오
recorded_at	TEXT	필수	없음	ISO timestamp	timestamp	아니오
memo	TEXT	필수	''	operator note	없음	synthetic only
idempotency_key	TEXT	필수	''	caller key	opaque	아니오
supersedes_id	TEXT FK	선택	NULL	parent valuation	one child	아니오
correction_reason	TEXT	필수	''	required for correction	없음	아니오
as_of_at/timezone	TEXT	함께 선택	NULL	timezone-aware ISO + IANA	timestamp/zone	아니오
cost_basis,net_contributed,profit,principal,interest,fee	INTEGER	선택	NULL	exact integer; NULL distinct from 0	currency unit	아니오
reported_principal_kind,reported_principal_label,return_basis	TEXT	선택	NULL	source-declared	없음	아니오
return_rate	REAL	선택	NULL	finite number	ratio	아니오
representative_liability_definition	TEXT	loan 필수	NULL	principal_only/principal_interest_fees	enum	아니오

select_account_valuation 선택 규칙과 기존 인덱스 매핑

• select_account_valuation은 account_id is mandatory인 단일 평가 선택 query다. currency and valuation_kind/purpose are optional filters이며, basis_date exact match or <= upper bound 조건을 지원한다.
• 선택 결과는 verified leaf valuations only다. 즉 superseded parent가 아닌 leaf만 권위로 사용하며, future-dated and unverified valuations are excluded한다.
• idx_account_valuations_account_date supports account/date selection; idx_account_valuations_status_date supports status/date selection. neither index covers account + currency + purpose이므로 해당 조합의 전체 선택 조건을 기존 인덱스가 대신 보장한다고 해석하지 않는다.
• idempotency and single_correction indexes enforce write integrity, not selection이다. 이 인덱스들은 재기록 방지와 correction 단일 child 불변조건을 위한 것이며 select_account_valuation의 후보 검색 인덱스가 아니다.

manual-assets — 수동 자산/부채

• 책임: transaction이 아닌 평가 source
• 식별자: id
• 생명 주기: update/deactivate; closed snapshot은 별도 immutable copy

필드

필드	타입	필수 여부	기본값	허용 값·형식	단위·정밀도	민감정보
id	TEXT	필수	없음	generated asset id	식별자	아니오
name	TEXT	필수	없음	non-empty	없음	아니오
liquidity_bucket	TEXT	필수	other	normalized bucket	enum	아니오
valuation_amount	INTEGER	선택	NULL	exact integer; NULL unvalued	currency unit	아니오
valuation_date	TEXT	필수	''	YYYY-MM-DD	date	아니오
currency	TEXT	필수	KRW	supported currency	ISO code	아니오
source_note,memo	TEXT	필수	''	synthetic/redacted note	없음	민감 가능
active	INTEGER	필수	1	0/1	boolean	아니오

asset-snapshots — closed snapshot rows

• 책임: basis-date historical immutable item copy
• 식별자: id
• 생명 주기: insert-only; correction run creates new rows

필드

필드	타입	필수 여부	기본값	허용 값·형식	단위·정밀도	민감정보
id,basis_date,source_type,name,created_at	TEXT	필수	없음	generated/date/account/manual_asset	identity/date	아니오
source_id,currency,liquidity_bucket,valuation_source,source_note,memo	TEXT	필수	''/KRW/other	source metadata	없음	가능
amount	INTEGER	선택	NULL	exact integer	currency unit	아니오
snapshot_run_id	TEXT	필수	''	asset_snapshot_runs.id	relation	아니오
correction_of_run_id,correction_reason	TEXT	필수	''	prior run/reason	provenance	아니오
is_correction	INTEGER	필수	0	0/1	boolean	아니오
closed_at	TEXT	필수	''	ISO timestamp	timestamp	아니오
valuation_id,selection_policy,verification_status,evidence_kind,exclusion_reason	TEXT	additive	empty/exact/unverified/applied/empty	provenance enum	없음	아니오

asset-snapshot-runs — close 실행 증거

• 책임: approval, selection, applied/excluded/carried evidence와 run identity
• 식별자: id
• 생명 주기: insert-only; correction points to prior run

필드

필드	타입	필수 여부	기본값	허용 값·형식	단위·정밀도	민감정보
id,basis_date,close_status	TEXT	필수	없음	complete_exact/incomplete_draft/conditional	identity/date/status	아니오
selection_policy	TEXT	필수	exact	exact/as_of	enum	아니오
idempotency_key	TEXT	필수	''	unique when non-empty	opaque	아니오
approved_by,approved_at,approval_reason,approval_reference	TEXT	필수	empty	explicit approval metadata	provenance	아니오
correction_of_run_id,correction_reason	TEXT	필수	empty	prior run/reason	provenance	아니오
applied_evidence,excluded_evidence,carried_evidence	TEXT JSON	필수	[]	redacted evidence arrays	JSON	가능
created_at	TEXT	필수	없음	ISO timestamp	timestamp	아니오

transactions, import-batches, reconciliation-sessions, review-decisions, schema-meta — 기존 entities

기존 필드와 관계를 보존한다. import는 거래를 기록할 수 있지만 balance valuation candidate는 dated unverified row만 추가한다. loan cache writes without an explicit liability definition do not append an account_valuations candidate; current cache와 transaction import는 그대로 완료하며 정의를 추정하지 않는다. reconciliation은 calculated balance provenance와 VALUATION_MANAGED 상태를 보존하며 investment valuation 때문에 transaction을 생성하지 않는다. schema_meta.schema_version은 12다.

현재 계좌 잔액 캐시와 기준일

• last_balance is the current cache value: it is the latest current balance observed by an import or account write, not a historical valuation and not opening evidence.
• last_balance_basis_date is the basis field for the evidence represented by that cache. It is a date when known; legacy or date-less writes store unknown/NULL and must not be inferred from last_synced_at or a valuation row.
• last_synced_at is the execution/sync timestamp: when the import or account write advanced the cache, not the evidence basis date.
• imports and account writes advance the account cache; non-loan writes may also append a dated account_valuations candidate. loan cache writes without an explicit liability definition do not append historical evidence, and updating the cache does not rewrite historical rows.
• account_valuations is the historical authority. A dated valuation remains append-only evidence even when it is older than the current cache; older evidence never rolls back the current cache.
• Every valuation attached to a loan account has an explicit principal_only or principal_interest_fees representative_liability_definition. A non-loan to loan conversion with any undefined or invalid attached valuation is rejected without changing the account or history; conversion with no valuations is allowed.

관계와 불변조건

관계

출발 엔터티	관계	대상 엔터티	카디널리티	필수 여부	소유권	삭제 규칙
accounts	has valuations	account-valuations	1:N	required	account service	no cascade/history preserved
account-valuations	supersedes	account-valuations	0..1 child	optional	valuation service	append-only
asset-snapshot-runs	contains	asset-snapshots	1:N/possibly zero	required	snapshot service	no delete
account-valuations	evidence for	asset-snapshots	0..N	optional	snapshot run	copy only
import-batches	produces candidate	account-valuations	0..N	optional	import service	candidate preserved
accounts	has reconciliation	reconciliation-sessions	1:N	optional	reconciliation service	no valuation side effect

불변조건

• idx_account_valuations_single_correction ensures one direct child; correction parent scope is same account/date/currency/purpose. Cyclic or self parent, second child, missing reason, and conflict are no-write failures.
• source_ref is account-scoped evidence identity even when an explicit key is supplied; key/source mismatch conflicts. This is evidence idempotency. There is no generic label; only source-declared reported_principal_kind and reported_principal_label are physical fields.
• only verified leaves are authority; unknown/future/unverified are excluded or warning evidence. last_balance is external current comparison, not opening evidence; missing balance_after/opening evidence is INCOMPLETE_CALCULATION.
• preview does not insert; close inserts run and rows atomically; latest run is report authority. Reports expose actual valuation_date, age, carry, verification, run, approval, correction, warnings, and exclusions across close response, live, closed, timeseries, and monthly.
• monthly_source_snapshot_id is a read-model alias of snapshot_run_id, not a persistence field. Investment unavailable and unavailable investment, loan purpose/definition/ambiguity, and reconciliation completeness remain explicit; no fake writes.
• asset_buckets - loan is the net-worth formula; representative loan only, no fake transactions.

저장과 마이그레이션

• 저장 위치: resolved SQLite path from jarca_ledger_mcp.infrastructure.sqlite.connection
• 직렬화 형식: SQLite tables; evidence arrays JSON UTF-8
• 스키마 버전: schema 11 → schema 12 additive
• 기존 데이터 처리: migrate_db() creates missing tables/columns/indexes; this is an additive migration with no guessed migration or deletion. legacy rows with unknown basis remain unknown; no inference is permitted.
• 하위 호환성: existing ledger/auth/menu/MCP contracts and legacy rows remain readable
• 롤백 조건: pre-migration backup restore boundary; do not down-migrate or delete valuation history in application code

검증

• 정상 데이터: tests/test_account_valuations.py, tests/test_asset_snapshots.py
• 경계 데이터: NULL/0, stale carry-forward, correction, empty snapshot tables
• 오류 데이터: invalid currency/kind, missing loan definition, idempotency/correction conflict
• 저장 후 재조회: service list/select/report and contract regression tests
• 마이그레이션: test_*schema* tests plus python -m pytest tests/test_account_valuations.py tests/test_asset_snapshots.py

settlement-drafts — 월정산 초안

Schema 13 adds two additive tables; init_db()/migrate_db() remain the only schema writers and schema 11→12 history is unchanged. No draft backfill is inferred. Exact migrated columns (from infrastructure/sqlite/migrations.py) are listed below; SCHEMA_SQL and migration DDL are the schema authority.

Table	Columns (SQLite types; ? means nullable)	Indexes / constraints
settlement_drafts	id TEXT PK, month TEXT NOT NULL, basis_date TEXT NOT NULL, status TEXT NOT NULL, version INTEGER NOT NULL, created_by TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, memo TEXT DEFAULT '', create_key TEXT UNIQUE NOT NULL, correction_of_run_id TEXT DEFAULT '', correction_reason TEXT DEFAULT '', final_run_id TEXT DEFAULT '', final_response TEXT DEFAULT '', cancelled_by TEXT DEFAULT '', cancellation_reason TEXT DEFAULT '', cancelled_at TEXT DEFAULT '', approval_fingerprint TEXT DEFAULT '', approval_version INTEGER DEFAULT 0, approved_by TEXT DEFAULT '', approved_at TEXT DEFAULT '', approval_reason TEXT DEFAULT '', approval_reference TEXT DEFAULT '', approval_flags TEXT DEFAULT '{}', additive original_request_hash TEXT DEFAULT ''	idx_settlement_drafts_active_month(month) WHERE status='active' unique; idx_settlement_drafts_month(month,created_at)
settlement_draft_items	id TEXT PK, draft_id TEXT NOT NULL FK, source_type TEXT NOT NULL, source_id TEXT NOT NULL, valuation_id TEXT DEFAULT '', selection_mode TEXT DEFAULT 'exact', selection_reason TEXT DEFAULT '', included INTEGER DEFAULT 1, exclusion_reason TEXT DEFAULT '', version INTEGER DEFAULT 1, selected_by TEXT DEFAULT '', selected_at TEXT DEFAULT '', evidence_json TEXT DEFAULT '{}'	UNIQUE(draft_id,source_type,source_id)

settlement_drafts therefore owns generated draft ID, YYYY-MM month, fixed month-end basis_date, active/completed/cancelled status, positive version, creator/timestamps/memo, create idempotency key+request fingerprint, correction parent/reason, approval actor/time/reason/reference/flags/fingerprint/version, final run ID and persisted response. The partial unique active-month constraint is shared by normal/correction drafts. settlement_draft_items owns stable target type/ID, inclusion/reason, selected valuation and selection mode/reason, item version/audit, frozen source evidence/hash and correction-child identity. Monetary authority is derived from eligible frozen evidence, not editable amount columns.

Modes are exact, carried_before, and manual_substitute. exact requires confirmed same-day evidence; carried_before requires a verified prior valuation and reason; manual_substitute requires explicit valuation ID, reason and review/approval even when valuation date is future. Preserve date_difference_days = valuation_date - basis_date; age_days retains its historical basis-minus-valuation meaning. Unverified candidates remain visible with candidate_amount but authority amount NULL. Missing is distinct from substitution/exclusion. Manual evidence freezes semantic fields and hash; only explicit refresh/reselection changes it.

Frozen/public evidence retains valuation_date, original recorded_at, derived date_difference_direction (after/before/same/unknown) and age_days; loan evidence retains representative_liability_definition. recorded_at may be later than month end while valuation_date is exactly month end. These names are preserved through finalized response, reports, timeseries and monthly coverage; date differences and semantic hashes are derived from frozen JSON rather than separate draft-item columns. Actor/version/timestamps of approval are cleared together on draft mutation.

Review fingerprint binds draft version, target/inclusion state, full valuation/manual semantic evidence, correction child, warnings, amounts/classification/date and prior run. Approved fingerprint/version become invalid on mutation or external drift. Finalize checks idempotency replay before active status, then validates version, approval, fingerprint, current evidence and correction parent, and inserts run+rows+draft completion/link in one BEGIN IMMEDIATE. Rollback on any SQLite failure; replay returns persisted response.

KRW only aggregation; non-KRW is warned/excluded, never converted. NULL is not zero; no targets yields provisional zero plus completeness and unregistered-liability warning, not a complete close. Existing snapshot rows preserve full frozen evidence, approval, date/mode/difference and actors; substitution, carried and approved-missing flags are orthogonal. monthly_source_snapshot_id remains a read-model alias, never a persistence field.

Issue #18 — schema 15 comprehensive settlement additions

asset_snapshot_runs cashflow field contract

All twelve comprehensive-run columns are nullable with no SQL default. This preserves NULL on historical asset-only runs; a new comprehensive write supplies all twelve fields. SQLite reports id (TEXT PRIMARY KEY) nullable because DDL omits explicit NOT NULL, although a run logically requires its identifier.

Column	Type	Nullability	Default
settlement_month	TEXT	NULL	none
currency	TEXT	NULL	none
income_total	INTEGER	NULL	none
expense_total	INTEGER	NULL	none
net_cashflow	INTEGER	NULL	none
total_assets	INTEGER	NULL	none
total_liabilities	INTEGER	NULL	none
net_worth	INTEGER	NULL	none
cashflow_policy_json	TEXT	NULL	none
aggregation_version	TEXT	NULL	none
snapshot_schema_version	INTEGER	NULL	none
source_snapshot_json	TEXT	NULL	none

settlement_drafts cashflow field contract

Column	Type	Nullability	Default
cashflow_policy_json	TEXT	NOT NULL	'{}'
aggregation_version	TEXT	NOT NULL	'comprehensive-v1'

settlement_cashflow_snapshots cashflow field contract

Column	Type	Nullability	Default
id	TEXT	NULL	none
monthly_settlement_id	TEXT	NULL	none
settlement_run_id	TEXT	NULL	none
cashflow_kind	TEXT	NOT NULL	none
category	TEXT	NOT NULL	''
subcategory	TEXT	NOT NULL	''
amount	INTEGER	NOT NULL	none
transaction_count	INTEGER	NOT NULL	none
currency	TEXT	NOT NULL	none

id is logically required although SQLite reports no explicit NOT NULL flag for this TEXT primary key. Both parent columns are individually nullable: monthly_settlement_id references monthly_settlement(id) ON DELETE RESTRICT, and settlement_run_id references asset_snapshot_runs(id) ON DELETE RESTRICT. An exclusive-parent CHECK requires exactly one parent to be non-NULL. Separate UNIQUE constraints apply to (monthly_settlement_id,cashflow_kind,category,subcategory,currency) and (settlement_run_id,cashflow_kind,category,subcategory,currency). transaction_count > 0 and amount/count are integers. Fresh schema uses idx_settlement_cashflow_snapshots_month for canonical-month lookup; upgraded run-parent storage retains idx_settlement_cashflow_snapshots_run for legacy-run lookup.

Comprehensive snapshot evidence inputs

source_snapshot_json stores an object with transactions and assets arrays. Transaction inputs preserve id, transaction_date, type, original signed amount, currency, category, subcategory, originalcashflow_kind, applied cashflow_kind, applied_amount, decision, sourceprovenance, category_cashflow_kind and category_active. Refunds keep their original positive amount while the expense contribution in applied_amount is negative; unresolved classifications keep applied_amount=NULL and block approval. The actual stored alias is originalcashflow_kind, not a renamed persistence field.

Asset inputs preserve source_id, source_type, valuation_id, valuation_date, basis_date, currency, authority_source, liquidity_bucket, is_liability, selection_mode, exclusion_reason, status, verification_status, original amount and applied_amount. Missing/excluded evidence retains NULL applied amounts and its recorded status/reason; fixed included rows remain linked to the same run. The run header's cashflow_policy_json, aggregation_version, snapshot_schema_version and these source inputs together reproduce the original selection/aggregation decision without rereading today's ledger. A legacy asset-only run retains NULL comprehensive fields and is reported as legacy: no historical backfill or invented zero. The existing approval, draft, correction and idempotency links remain unchanged.

asset_snapshot_runs remains the run header. Schema 13→14 adds settlement month, currency, six nullable legacy-compatible INTEGER totals (income_total, expense_total, net_cashflow, total_assets, total_liabilities, net_worth), cashflow_policy_json, aggregation_version, snapshot_schema_version, and source_snapshot_json; preserve existing basis/approval/draft/correction/idempotency fields. Old asset-only run totals remain NULL; no current-ledger backfill. Migration is additive/idempotent; old unfinished drafts require fresh comprehensive review/approval.

settlement_cashflow_snapshots: nine columns id, monthly_settlement_id, settlement_run_id, cashflow_kind, category, subcategory, amount, transaction_count, currency; cashflow_kind is income/expense/excluded, amount signed INTEGER, count positive, and category fields are NOT NULL (empty string is explicit). The nullable parent columns reference monthly_settlement.id and asset_snapshot_runs.id with RESTRICT, while the exclusive-parent CHECK requires exactly one parent. The table has separate UNIQUE constraints on (monthly_settlement_id,cashflow_kind,category,subcategory,currency) and (settlement_run_id,cashflow_kind,category,subcategory,currency), so rows remain grouped under exactly one canonical month or legacy run without merging the two parent domains.

Policy is stored on draft and header. Savings means only explicit category/subcategory pairs; transfer/voucher exclusion has priority; debt_payment is expense; refund is negative expense in refund month/original category. Unknown unresolved blocks approval; asset conditional flags cannot override it. KRW only/no FX. Source JSON freezes transaction values/classification/policy/decision and selected asset evidence/provenance. Six equations are validated against same-run details; excluded/duplicate/unknown amount do not sum. Header, both detail areas, evidence, completed draft linkage/response commit atomically; rollback all on failure. Runs are immutable; corrections append after latest-parent/single-child validation.

Additive schema 15 monthly lifecycle

monthly_settlement owns canonical month status, version, approvals and nullable approved_cashflow_fingerprint. account_snapshot.monthly_settlement_id is a nullable FK for canonical account evaluations. Legacy asset_snapshots, asset_snapshot_runs, manual_assets, and account_valuations retain their rows and contracts. settlement_cashflow_snapshots has nullable settlement_run_id and monthly_settlement_id foreign keys, with exactly one parent required and separate uniqueness constraints. Run corrections retain separate frozen groups; migration never guesses a monthly parent from dates. Schema 11–14 upgrades and schema 15 initialization are atomic and idempotent; failed copies or foreign-key checks roll back all DDL and metadata.

Immutable monthly settlement history read model

asset_snapshot_runs is the header source for append-only immutable settlement history. Each public history row identifies run_id, settlement_month, frozen draft version, closed_at, close_status, and optional correction lineage through correction_of_run_id (published as correction_target) plus correction_reason (published as reason). The exact-record read additionally returns the frozen basis date and persisted cashflow/net-worth totals. Reads are ordered newest-first and never synthesize totals from current transactions, account values, or mutable draft state. A correction appends a new run and retains the referenced run unchanged.