Summary
Design and implement the storage schema that underpins the entire application. Current storage is likely too shallow.
Schema
-- Import provenance
import_sessions(id, file_hash, filename, account_id, imported_at, total_rows, parsed_rows, error_rows)
-- Raw imported data (never modified)
raw_transactions(id, import_session_id, row_number, date, amount_cents, description, raw_data_json)
-- Resolved/canonical transactions
canonical_transactions(id, raw_transaction_id, date, amount_cents, merchant_id, category, confidence, reviewed)
-- Merchant resolution
merchants(id, canonical_name, category, created_at, updated_at)
merchant_aliases(id, merchant_id, alias_pattern, match_type, created_by)
-- Double-entry ledger
accounts(id, name, type, institution, currency, created_at)
postings(id, date, debit_account_id, credit_account_id, amount_cents, canonical_transaction_id, memo)
-- Recurring detection
recurring_series(id, merchant_id, account_id, interval_days, avg_amount_cents, confidence, status)
-- Review decisions
review_decisions(id, entity_type, entity_id, decision, decided_at, previous_value, new_value)
Principles
- Raw data is immutable — never modify imported rows
- All mutations go through canonical layer
- Every decision is logged in review_decisions
- Use PluresDB if available, SQLite as fallback
Acceptance
Summary
Design and implement the storage schema that underpins the entire application. Current storage is likely too shallow.
Schema
Principles
Acceptance