Skip to main content

Data Model & Financial Invariants

The database schema in Cloudflare D1 consists of 6 business tables and 3 technical tables. All tables enforce composite primary keys starting with owner_id.

Business Tables​

1. categories​

Hierarchical category structure allowing parent groups like Auto to aggregate subcategories (Combustible, Peajes, Seguro).

  • owner_id: Tenant UUID.
  • id: Unique category key (e.g. cat_auto).
  • name: Category label.
  • parent_id: Self-referencing FK (CHECK (id != parent_id)).
  • active: 1 for active, 0 for archived.

2. accounts​

Financial accounts, balances, and payment instruments.

  • owner_id: Tenant UUID.
  • id: Account key (e.g. acc_bbva_ars).
  • name: Display name (UNIQUE(owner_id, name)).
  • type: cash, bank, wallet_balance, credit_card, investment.
  • currency: ISO-4217 currency code (e.g. ARS, USD).
  • currency_exponent: Default 2.

3. movements​

The central ledger of financial events.

  • kind: income, expense, transfer_own, card_payment, refund.
  • status: pending, estimated, confirmed.
  • amount_minor: Positive integer in minor units (e.g. 4500000 = $45,000.00).
  • reporting_amount_minor: Converted reporting value in standard currency.
  • occurred_on: Date string YYYY-MM-DD. Never auto-appends artificial 00:00:00Z.
  • settled_on: Effective date of bank/wallet cash movement.
  • duplicate_of: FK pointing to canonical movement if deduplicated.

Binds refunds directly to either an original purchase or a parent category.

  • refund_movement_id: The refund movement row.
  • original_movement_id: The historical expense being compensated.
  • allocated_amount_minor: Allocated compensation. Invariant: SUM(allocated) <= original expense.

5. installments​

Projections of future credit card billing cycles.

  • purchase_movement_id: Initial purchase transaction.
  • number: Installment sequence (e.g. 1, 2, 3).
  • total: Total installments (e.g. 3).
  • amount_minor: Minor amount per quota.
  • due_on: Scheduled payment date.
  • paid_on: Settlement timestamp when settled.

6. budgets​

Monthly category caps.

  • category_id: Category bound to budget.
  • month: Month formatted as YYYY-MM-01.
  • limit_minor: Allocated limit.
  • basis: adjusted_original or gross_incurred.

Technical Tables​

  1. movement_sources: Maps external identifiers (Notion page ID, Apple Wallet transaction ID) to the canonical movement_id.
  2. ingestion_requests: Stores request hashes and durable response receipts for exact idempotent replay.
  3. ingestion_events: Audit ledger enforcing monotonic revisions and CAS conflict prevention.
  4. api_keys: Manages hashed API keys for GrokBot and Mango ingestion.