GitHub Repository Proposal: openrecon

A Local-First Settlement Compiler & Double-Entry Journal Exporter for Merchant-of-Record and Multi-Currency SaaS Billing

1. Executive Summary & Repository Scope

What It Is

openrecon is an open-source, local-first CLI engine that compiles messy Merchant-of-Record (Paddle, Lemon Squeezy, Polar) and payment gateway (Stripe) payout exports into mathematically verified, auditable double-entry accounting journals (Beancount, Ledger, and standard CSV). It bridges the acute gap between provider gross sales, withheld VAT, platform fees, hidden FX conversion spreads, reserve holds, and actual bank settlement wires.

Core Value Proposition

• Solves the MoR Settlement Gap: Turns lump-sum settlement wires into exact, order-level decomposed accounting entries.
• Audit Durability by Design: Immutable raw input lineage, append-only match ledger, and cryptographic period locks ensure historical exports never mutate or drift.
• Position Tracking: Tracks rolling reserves, carry-forward negative balances, and dispute lifecycles as first-class balance sheet subledgers.
• Zero Cloud Footprint: Single Go binary backed by local SQLite. No credentials stored, no background daemons, no SaaS subscription.

License

• Apache-2.0 (selected specifically for the explicit patent grant protection in financial data processing and frictionless adoption by downstream accounting tools).

Non-Goals (Explicit Boundaries)

• Not a General Bookkeeping System: Does not manage invoices, track vendor bills, or categorize employee expenses.
• Not a Tax Filing Engine: Does not file VAT/GST returns; it ingests provider-reported tax evidence and flags reconciliation discrepancies.
• Not a Bank Sync Daemon: No OAuth connections, Plaid/Yodlee integrations, or webhook servers in v1. Input is strictly local CSV/JSON files.
• Not a Subscription Analytics Tool: Does not calculate MRR, Churn, or LTV.

2. Core Accounting & Formal Matching Model

2.1 The Typed Obligation Graph

Every settlement is evaluated through a deterministic DAG with strict order of operations:

Order Gross Obligation
  └── Tax Remittance Obligation (VAT/GST withheld by MoR)
        └── Refund / Dispute Obligation (Contra-revenue reversals)
              └── Provider Fee Obligation (Platform percentage + fixed per-transaction cuts)
                    └── Post-Fee Net Amount
                          └── FX Conversion Obligation (ECB benchmark rate vs. provider conversion)
                                └── Bank Settlement Wire (Intermediary bank deductions vs. net received)

2.2 Strict Currency & Transaction Balancing Rules

• Single-Commodity Invariant: Non-conversion journal entries must balance to zero in exactly one currency. Mixing multiple currencies on one standard transaction is forbidden.
• Isolated Conversion Entries: Multi-currency transactions are restricted strictly to conversion events and must carry explicit Beancount total price syntax (@@):
  • Expenses:Financial:FXSpread books provider conversion markup at conversion date.
  • Income:Financial:FXGain / Expenses:Financial:FXLoss books currency fluctuation between recognition and settlement dates.
• FX Spread Bound Checking: Provider conversion rate is evaluated against local ECB reference rates. Implied FX markup exceeding 4.0% triggers contradicted: fx_spread_exceeded_bound rather than being silently absorbed.

2.3 Structured Verdict Taxonomy

Matching rules evaluate evidence and emit typed verdicts:

• satisfied: Obligations balance to zero within precision limits.
• degenerate: Multiple candidate matches exist, but all candidate projections onto accounting consequences (tax jurisdiction, currency, account, period) are byte-identical. Engine automatically breaks ties with lowest row ID and logs tie_break='degenerate' with zero human prompts.
• ambiguous: Multiple candidate matches yield different accounting outcomes (e.g., different VAT rates). Requires user prompt.
• residual: Unexplained variance remains (e.g., $15 intermediary wire fee). Prompts user to classify cause into a persistent rule.
• contradicted: Mathematical impossibility or bound violation (e.g., negative fees, >4% FX spread, rounding drift > 0.01 × order count). Engine halts with explicit diagnostic.

2.4 Position Subledgers (Reserves & Disputes)

Tracked via append-only position_events and materialized into positions:

• Rolling Reserves: Tracks hold basis (gross, net_after_tax, or net_after_fees), expected release date (e.g., 90 days), and overdue status.
• Dispute Lifecycles: Tracks state machine transitions (dispute_opened -> dispute_won / dispute_lost) and dispute fee contra-entries.

3. Data Model & Durability Architecture (SQLite Schema)

-- Raw input files with SHA256 hashes and line-level lineage
CREATE TABLE raw_imports (
    import_id       TEXT PRIMARY KEY,         -- e.g. imp_202604_paddle_orders
    file_name       TEXT NOT NULL,
    file_sha256     TEXT NOT NULL,
    provider        TEXT NOT NULL,
    row_count       INTEGER NOT NULL,
    imported_at     TEXT NOT NULL
);

CREATE TABLE raw_records (
    import_id       TEXT NOT NULL REFERENCES raw_imports(import_id),
    line_number     INTEGER NOT NULL,
    payload_json    TEXT NOT NULL,
    PRIMARY KEY (import_id, line_number)
);

-- Canonical Normalized Financial Events
CREATE TABLE canonical_events (
    event_id        TEXT PRIMARY KEY,         -- ev_paddle_ord_10293_sale
    import_id       TEXT NOT NULL REFERENCES raw_imports(import_id),
    source_line     INTEGER NOT NULL,
    event_type      TEXT NOT NULL,            -- sale, tax, fee, refund, dispute_opened, dispute_won, dispute_lost, payout, fx_conversion
    merchant_entity TEXT NOT NULL DEFAULT 'default',
    product_id      TEXT,
    occurred_at     TEXT NOT NULL,
    gross_amount    INTEGER NOT NULL,         -- minor units (cents)
    currency        TEXT NOT NULL,
    tax_amount      INTEGER NOT NULL DEFAULT 0,
    tax_source      TEXT NOT NULL,            -- provider_reported | derived | manual
    tax_country     TEXT,
    fee_amount      INTEGER NOT NULL DEFAULT 0,
    reference_id    TEXT                      -- external order_id / payout_id
);

-- Immutable Append-Only Match Ledger
CREATE TABLE match_ledger (
    match_id        TEXT PRIMARY KEY,         -- m_202604_paddle_000184
    period          TEXT NOT NULL,            -- 2026-04
    rule_id         TEXT NOT NULL,
    rule_version    TEXT NOT NULL,
    verdict         TEXT NOT NULL,            -- satisfied | degenerate | ambiguous | residual | contradicted
    residual_cents  INTEGER NOT NULL DEFAULT 0,
    candidate_count INTEGER NOT NULL DEFAULT 1,
    tie_break       TEXT,                     -- NULL | degenerate | manual
    decided_by      TEXT NOT NULL,            -- engine | user
    decided_at      TEXT NOT NULL,
    supersedes_id   TEXT REFERENCES match_ledger(match_id)
);

-- Match Associations (Junction)
CREATE TABLE match_events (
    match_id        TEXT NOT NULL REFERENCES match_ledger(match_id),
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    obligation_node TEXT NOT NULL,
    PRIMARY KEY (match_id, event_id)
);

-- Append-Only Learned Decision Events
CREATE TABLE decision_events (
    decision_id     TEXT PRIMARY KEY,
    created_at      TEXT NOT NULL,
    actor           TEXT NOT NULL,            -- user | provider_pack | imported_rule
    scope_predicate TEXT NOT NULL,            -- JSON predicate
    decision_type   TEXT NOT NULL,            -- classify_residual | choose_candidate | suppress_noise
    account         TEXT NOT NULL,            -- e.g. Expenses:BankFees
    amount_formula  TEXT NOT NULL,            -- fixed 15.00 USD | percentage | residual_allocation
    source_prompt   TEXT
);

-- Position Tracking Subledger
CREATE TABLE position_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    position_id     TEXT NOT NULL,            -- pos_paddle_res_202604
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    kind            TEXT NOT NULL,            -- open | increase | decrease | close | mark_overdue
    amount          INTEGER NOT NULL,         -- minor units
    currency        TEXT NOT NULL,
    occurred_at     TEXT NOT NULL,
    match_id        TEXT REFERENCES match_ledger(match_id)
);

-- Cryptographic Period Locking
CREATE TABLE period_locks (
    period          TEXT PRIMARY KEY,         -- 2026-04
    locked_at       TEXT NOT NULL,
    input_sha256    TEXT NOT NULL,            -- Hash of inputs, match rows, and decision events
    pack_versions   TEXT NOT NULL             -- JSON map of pack semvers at lock time
);

4. Prior-Period Adjustments (PPA) & Materiality Thresholds

When historical periods are locked, new information (e.g., a May chargeback or fee adjustment on an April order) is handled with strict accounting rules:

1. Reversal & Relink: Writes reversal and new match rows into the match ledger carrying openrecon_reverses: <prior_match_id>.
2. Current Period PPA Posting: The locked April journal file is never modified. The adjustment posts into 2026-05.bean using explicit contra-accounts (Income:SaaS:Chargebacks) and separate single-currency legs.
3. Three-Tier Materiality Governance:
   • Immaterial (<0.5% period revenue): Automatically posted to current open period.
   • Material (≥0.5% period revenue): Engine pauses with contradicted: ppa_materiality_exceeded and requires explicit CLI flag --accept-material-ppa <match_id>.
   • Locked Tax Year: Never auto-posted into monthly stream; generates a standalone restatement report.

5. In-Tree Provider Pack Specification

Provider packs live in-tree to guarantee audit safety, avoid CGO/plugin failure modes, and enforce CI verification.

packs/paddle/
├── manifest.yaml             # Pack metadata, semver, review budget, reserve policy
├── normalizer.go             # Imperative parser: Raw Rows -> Canonical Events
├── obligations.yaml          # Declarative arithmetic DAG & rounding rules
├── tax_rates.yaml            # Temporal tax tables (jurisdiction, valid_from/to, rate)
├── CHANGELOG.md              # Human-readable fee/tax model changes
├── testdata/                 # Small contract test vectors (.csv inputs & expectations)
└── golden/                   # Canonical events & golden journal outputs

Manifest & Review Budget (manifest.yaml)

name: paddle
version: 1.2.0
supported_export_versions: ["v1"]
review_budget:
  max_residual_causes_per_1000_orders: 8
  max_ambiguous_groups_per_1000_orders: 3
  fail_ci_when_exceeded: true
reserve_policy:
  basis: gross
  rate: 0.10
  release_policy: fifo
  expected_hold_days: 90
  overdue_grace_days: 7
rounding:
  mode: half_even
  precision: dynamic_by_currency  # Loaded from core currency minor-unit table

Pack Semver CI Verification

CI mechanically hashes sha256(canonicalized obligations.yaml + normalizer golden outputs). If the hash changes without a matching semver bump in manifest.yaml and CHANGELOG.md, CI fails.

6. Repository Architecture & Layout

openrecon/
├── cmd/openrecon/              # Cobra CLI entry point
├── core/
│   ├── db/                     # SQLite schema, migrations, WAL init
│   ├── currency/               # Minor-unit table (JPY=0, KWD=3), rounding engine, ECB cache
│   ├── events/                 # Canonical event definitions & validation
│   ├── obligations/            # Declarative DAG evaluator & bound checkers
│   ├── positions/              # Reserve/dispute subledger state machine
│   ├── ledger/                 # Match ledger, period locking, decision events
│   └── export/                 # Deterministic Beancount & CSV compilers
├── packs/                      # In-tree provider packs (Paddle, Lemon Squeezy, Stripe, Wise)
├── testdata/
│   ├── e2e/                    # Multi-provider synthetic monthly scenarios
│   └── fixtures/               # Edge-case raw CSV/JSON dumps
├── examples/                   # User-facing configuration profiles and sample .bean files
├── docs/
│   ├── accounting-model.md     # Chart of accounts, sign conventions, decomposition spec
│   ├── non-goals.md            # Scope boundaries and operational limits
│   └── provider-packs.md       # Guide to writing and testing new in-tree packs
├── LICENSE                     # Apache-2.0
├── README.md                   # Quickstart, workflow, architecture overview
└── Taskfile.yml                # CI commands, pack hash checks, byte-identical golden tests

7. CLI Reference & Workflows

1. Ingest Raw Exports

openrecon import ./exports/2026-04 --profile examples/paddle-wise.yaml

Computes SHA256 of raw files, records line-level raw records, and runs in-tree normalizers to populate canonical_events.

2. Compile Settlement & Interactive Triage

openrecon reconcile --period 2026-04 --interactive

Evaluates obligation DAG. Auto-resolves satisfied and degenerate ties. Emits cause-level review prompts for residual or ambiguous cases. Learned choices write to decision_events.

3. Inspect Explanations & Positions

openrecon explain --unmatched
openrecon positions --provider paddle --overdue

Prints exact arithmetic decomposition chains and surfaces overdue reserve releases.

4. Period Lock & Deterministic Export

openrecon lock --period 2026-04
openrecon export beancount --period 2026-04 > ./ledgers/2026-04.bean
openrecon verify --period 2026-04

Locks period state, outputs sorted deterministic Beancount postings, and verifies byte-identical reproduction.

5. Rule Synchronization & System Doctor

openrecon rules export --learned > ./rules/team-rules.yaml
openrecon doctor

Exports learned rules for version control and verifies that historical locked periods match ledger rule_version invariants.

8. Consensus Summary & Key Decisions Settled

GitHub Repository Specification: openrecon (v1.0 Final Deliverable)

A Local-First Settlement Compiler & Double-Entry Journal Exporter for Merchant-of-Record and Multi-Currency SaaS Billing

1. Executive Summary & Core Boundaries

1.1 What It Is

openrecon is an open-source, local-first CLI compiler that reconciles multi-currency Merchant-of-Record (Paddle, Lemon Squeezy, Polar) and payment gateway (Stripe) exports against bank settlement statements. It compiles messy payout batches into mathematically verified, auditable, byte-reproducible double-entry accounting journals (Beancount, Ledger, and standard CSV).

1.2 License & Threat Model

• License: Apache-2.0 (provides explicit patent grant protection for payment settlement logic and unencumbered adoption by downstream accounting ecosystems).
• Privacy & PII Boundary: Financial credentials, bank feeds, customer transaction data, and database files never leave the user's local machine. Local .gitignore includes *.db, *.db-wal, *.db-shm, exports/, ledgers/private/, and rules/learned.local.yaml out of the box.

1.3 Strict Non-Goals (Explicit Boundaries)

• Not a Bookkeeping Ledger: Does not replace general ledger software; it compiles settlement transactions and exports journal entries.
• Not a Tax Filer: Ingests and verifies provider-reported tax evidence; does not calculate statutory liabilities or file returns.
• Not a Bank Sync Daemon: Ingests local files only (CSV/JSON). No background daemons, no OAuth tokens, no live Plaid/Yodlee sync in v1.
• Not a SaaS Metrics Engine: Strictly excludes MRR, Churn, or LTV analytics.

2. Formal Obligation Architecture & Event Families

Instead of forcing all financial events through a single universal chain, OpenRecon separates reconciliation into typed, independent obligation DAGs by event family.

1. Sale Family (sale_chain):
   gross_sale -> tax_withheld -> provider_fee -> receivable_opened

2. Refund Family (refund_chain):
   gross_refund -> tax_returned -> fee_returned_policy (none|partial|full) -> receivable_reduced

3. Dispute Family (dispute_chain):
   dispute_hold_opened -> dispute_fee_event -> outcome_event -> (dispute_won_credited | loss_booked)

4. Reserve Family (reserve_chain):
   reserve_hold_opened -> reserve_release_event -> restricted_cash_cleared

5. Settlement Family (settlement_chain):
   SUM(receivable_delta) + SUM(position_delta) + fx_conversion + bank_fee -> bank_receipt

2.1 Accounting & Balancing Invariants

• Single-Commodity Invariant: Every non-conversion transaction balances to zero in exactly one commodity. Mixing multiple currencies on non-conversion transactions is strictly rejected.
• Isolated FX Conversions: Currency conversions are isolated into dedicated conversion transactions with explicit Beancount total-price notation (@@).
• Separation of FX Spread vs. FX Fluctuation:
  • Expenses:Financial:FXSpread: Provider conversion markup on conversion date.
  • Income:Financial:FXGain / Expenses:Financial:FXLoss: Currency movement between recognition date and settlement date.
• Corridor-Specific FX Bounds: Configured in pack manifests (e.g., EUR/USD = 4.0000%, BRL/USD = 9.0000%). Implied spreads exceeding corridor bounds trigger contradicted: fx_spread_exceeded_bound.
• Zero Float64 Arithmetic: All rates, percentages, and currencies are parsed into typed fixed-point rationals (shopspring/decimal).

2.2 Structured Verdict Taxonomy

• satisfied: Obligations balance to zero within currency precision.
• degenerate: Multiple candidate matches exist, but all candidate projections onto accounting consequences (tax country, currency, accounts, period, amount) are identical. Engine auto-picks the lowest source row ID and logs tie_break='degenerate' with zero human prompts.
• ambiguous: Multiple candidate matches lead to divergent accounting consequences. Prompts user to select source of truth.
• residual: Unexplained variance remains (e.g., $15 intermediary wire fee). Prompts user to classify the cause into a learned rule.
• contradicted: Hard bound or mathematical violation (e.g., negative fees, corridor FX exceedance, rounding drift > 0.01 × order count). Halts execution with diagnostic.

3. Standard Chart of Accounts

Default chart of accounts (fully customizable via user profile mapping):

Income:SaaS:Sales                              -- Gross revenue recognized on order date
Income:SaaS:Chargebacks                        -- Contra-revenue for dispute losses/refunds
Assets:Receivables:{Provider}                  -- Uncleared settlements from provider
Assets:RestrictedCash:{Provider}:Reserve       -- Rolling reserves held by provider
Assets:Bank:{Currency}                         -- Settled bank account funds
Expenses:PaymentProcessing:{Provider}:Fees     -- Provider transaction & platform fees
Expenses:PaymentProcessing:{Provider}:DisputeFees -- Provider dispute / chargeback fees
Expenses:Financial:FXSpread                    -- Provider FX conversion markup
Expenses:Financial:FXLoss                      -- Realized loss on currency timing
Income:Financial:FXGain                        -- Realized gain on currency timing
Expenses:Rounding                              -- Sub-cent calculation rounding differences
Income:Rounding                                -- Sub-cent calculation rounding differences

4. SQLite Schema & Durability Controls

-- 1. Raw Imports with dual hashes for PII compliance & audit verification
CREATE TABLE raw_imports (
    import_id               TEXT PRIMARY KEY,  -- imp_202604_paddle_orders
    file_name               TEXT NOT NULL,
    original_file_sha256    TEXT NOT NULL,     -- Hash of original input file
    redacted_payload_sha256 TEXT NOT NULL,     -- Hash of retained local payload
    redaction_mode          TEXT NOT NULL,     -- 'default' (PII stripped) | 'none'
    provider                TEXT NOT NULL,
    row_count               INTEGER NOT NULL,
    imported_at             TEXT NOT NULL
);

CREATE TABLE raw_records (
    import_id       TEXT NOT NULL REFERENCES raw_imports(import_id),
    line_number     INTEGER NOT NULL,
    payload_json    TEXT NOT NULL,             -- Redacted JSON (salted customer_ref, no raw emails/IPs)
    PRIMARY KEY (import_id, line_number)
);

-- 2. Narrow, Signed Canonical Events
CREATE TABLE canonical_events (
    event_id        TEXT PRIMARY KEY,          -- ev_paddle_ord_10293_sale
    import_id       TEXT NOT NULL REFERENCES raw_imports(import_id),
    source_line     INTEGER NOT NULL,
    event_type      TEXT NOT NULL,             -- sale, tax_withheld, fee, refund, dispute_opened, dispute_won, dispute_lost, payout, fx_conversion
    merchant_entity TEXT NOT NULL DEFAULT 'default',
    product_id      TEXT,
    occurred_at     TEXT NOT NULL,
    amount_minor    INTEGER NOT NULL,          -- Signed integer in minor units (cents)
    currency        TEXT NOT NULL,
    external_ref    TEXT,                      -- Provider order_id / payout_id
    parent_ref      TEXT,                      -- References original order for refunds/disputes
    attributes_json TEXT NOT NULL              -- Tax country, tax_source (provider_reported|derived), fee_basis
);

-- 3. Append-Only Match Ledger
CREATE TABLE match_ledger (
    match_id        TEXT PRIMARY KEY,          -- m_202604_paddle_000184
    merchant_entity TEXT NOT NULL DEFAULT 'default',
    period          TEXT NOT NULL,             -- 2026-04
    rule_id         TEXT NOT NULL,
    rule_version    TEXT NOT NULL,
    verdict         TEXT NOT NULL,             -- satisfied | degenerate | ambiguous | residual | contradicted
    residual_cents  INTEGER NOT NULL DEFAULT 0,
    candidate_count INTEGER NOT NULL DEFAULT 1,
    tie_break       TEXT,                      -- NULL | degenerate | manual
    decided_by      TEXT NOT NULL,             -- engine | user
    decided_at      TEXT NOT NULL,
    supersedes_id   TEXT REFERENCES match_ledger(match_id)
);

CREATE TABLE match_events (
    match_id        TEXT NOT NULL REFERENCES match_ledger(match_id),
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    obligation_node TEXT NOT NULL,
    PRIMARY KEY (match_id, event_id, obligation_node)
);

-- 4. Append-Only Decision Events (Learned Rules)
CREATE TABLE decision_events (
    decision_id     TEXT PRIMARY KEY,
    created_at      TEXT NOT NULL,
    actor           TEXT NOT NULL,             -- user | provider_pack | imported_rule
    scope_predicate TEXT NOT NULL,             -- JSON predicate
    decision_type   TEXT NOT NULL,             -- classify_residual | choose_candidate | suppress_noise
    account         TEXT,                      -- Nullable: required for classify_residual, NULL for suppress/choose
    amount_formula  TEXT NOT NULL,             -- 'fixed 15.00 USD' | 'percentage' | 'residual_allocation'
    source_prompt   TEXT
);

-- 5. Position Events (Rolling Reserves & Dispute Subledgers)
CREATE TABLE position_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    position_id     TEXT NOT NULL,             -- pos_paddle_res_202604
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    kind            TEXT NOT NULL,             -- open | increase | decrease | close | mark_overdue
    amount_minor    INTEGER NOT NULL,
    currency        TEXT NOT NULL,
    occurred_at     TEXT NOT NULL,
    match_id        TEXT REFERENCES match_ledger(match_id)
);

-- 6. Multi-Entity Cryptographic Period Locks
CREATE TABLE period_locks (
    merchant_entity TEXT NOT NULL,
    period          TEXT NOT NULL,
    locked_at       TEXT NOT NULL,
    input_sha256    TEXT NOT NULL,             -- Preimage: sorted primary keys + NUL-delimited fields
    pack_versions   TEXT NOT NULL,             -- JSON map of pack versions
    PRIMARY KEY (merchant_entity, period)
);

-- 7. Database Immutability Triggers (Enforced in core migrations)
CREATE TRIGGER match_ledger_no_update BEFORE UPDATE ON match_ledger
BEGIN SELECT RAISE(ABORT, 'match_ledger is append-only'); END;
CREATE TRIGGER match_ledger_no_delete BEFORE DELETE ON match_ledger
BEGIN SELECT RAISE(ABORT, 'match_ledger is append-only'); END;

CREATE TRIGGER canonical_events_no_update BEFORE UPDATE ON canonical_events
BEGIN SELECT RAISE(ABORT, 'canonical_events is append-only'); END;
CREATE TRIGGER canonical_events_no_delete BEFORE DELETE ON canonical_events
BEGIN SELECT RAISE(ABORT, 'canonical_events is append-only'); END;

CREATE TRIGGER decision_events_no_update BEFORE UPDATE ON decision_events
BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;
CREATE TRIGGER decision_events_no_delete BEFORE DELETE ON decision_events
BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;

CREATE TRIGGER position_events_no_update BEFORE UPDATE ON position_events
BEGIN SELECT RAISE(ABORT, 'position_events is append-only'); END;
CREATE TRIGGER position_events_no_delete BEFORE DELETE ON position_events
BEGIN SELECT RAISE(ABORT, 'position_events is append-only'); END;

5. In-Tree Provider Pack Specification

Packs are compiled in-tree into the single Go binary to guarantee audit reproducibility and supply-chain security.

packs/paddle/
├── manifest.yaml             # Pack semver, typed review budget, typed reserve policy, FX corridors
├── normalizer.go             # Imperative parser: Raw CSV/JSON -> Canonical Events
├── obligations.yaml          # Declarative obligation DAG & fee return policies
├── tax_rates.yaml            # Temporal tax tables (jurisdiction, valid_from/to, rate string)
├── CHANGELOG.md              # Version history for fee/tax changes
├── testdata/                 # Small contract test vectors (.csv inputs & expectations)
└── golden/                   # Canonical events & expected journal outputs

Manifest Schema (manifest.yaml)

name: paddle
version: "1.2.0"
supported_export_versions: ["v1"]
review_budget:
  max_residual_causes_per_1000_orders: 8
  max_ambiguous_groups_per_1000_orders: 3
  fail_ci_when_exceeded: true
reserve_policy:
  basis: gross
  rate: "10.0000%"
  release_policy: fifo
  expected_hold_days: 90
  overdue_grace_days: 7
fx_bounds:
  default: "4.0000%"
  corridors:
    EUR/USD: "4.0000%"
    GBP/USD: "4.0000%"
    BRL/USD: "9.0000%"
fee_returned_policy: none
rounding:
  mode: half_even
  precision: dynamic_by_currency

6. Repository Layout

openrecon/
├── cmd/openrecon/              # Cobra CLI entry point
├── core/
│   ├── db/                     # SQLite schema, migrations, triggers, WAL init
│   ├── currency/               # Minor-unit table (JPY=0, KWD=3), decimal arithmetic, ECB rates cache
│   ├── events/                 # Canonical event model & PII redactor
│   ├── obligations/            # Typed obligation DAG evaluator & FX corridor bound checks
│   ├── positions/              # Reserve/dispute subledger state machine
│   ├── ledger/                 # Match ledger, period locking, decision events
│   └── export/                 # Deterministic Beancount & CSV compilers
├── packs/                      # In-tree provider packs (Paddle, Lemon Squeezy, Stripe, Wise)
├── testdata/
│   ├── e2e/                    # Multi-provider synthetic monthly scenarios
│   └── fixtures/               # Hostile edge-case raw dumps
├── examples/                   # User profiles and sample .bean files
├── docs/
│   ├── accounting-model.md     # Chart of accounts, single-commodity rules, sign conventions
│   ├── non-goals.md            # Scope boundaries and operational limits
│   └── provider-packs.md       # Contributing and testing in-tree packs
├── .gitignore                  # Prevents accidental commits of *.db, exports/, ledgers/private/
├── LICENSE                     # Apache-2.0
├── README.md                   # Architecture overview, quickstart, CLI reference
└── Taskfile.yml                # CI tasks: pack hash verification, golden tests, byte-diff checks

7. CLI Reference

# 1. Ingest exports with default PII redaction
openrecon import ./exports/2026-04 --profile examples/paddle-wise.yaml

# 2. Compile settlement & interactive triage (cause-level prompts)
openrecon reconcile --period 2026-04 --interactive

# 3. Inspect explanations, overdue reserves, and diagnostics
openrecon explain --unmatched
openrecon positions --provider paddle --overdue

# 4. Period lock, export Beancount journal, and verify byte reproducibility
openrecon lock --period 2026-04
openrecon export beancount --period 2026-04 > ./ledgers/2026-04.bean
openrecon verify --period 2026-04

# 5. Export learned rules for Git and run system doctor
openrecon rules export --learned > ./rules/learned.yaml
openrecon doctor

8. Synthetic Golden Benchmark (CI Acceptance Test)

A synthetic April dataset (testdata/e2e/april-synthetic/) is mandatory in CI:

• 500 Orders: 350 EUR (VAT-inclusive German & French sales), 150 USD.
• Provider: Paddle (10% gross reserve held, 5% + €0.50 fees, provider-converted to USD).
• Bank Receipt: Wise USD statement line arriving $15.00 short due to intermediary routing fees.
• Edge Cases: 1 chargeback, 3 cross-month refunds, 1 prior-period dispute clawback from March.
• CI Assertion: Must reconcile with 0 unbudgeted residual prompts, allocate the $15.00 gap to Expenses:PaymentProcessing:Fees via learned rule, decompose EUR/USD conversion with ECB benchmark rate, and emit a 100% balanced Beancount file matching golden byte-for-byte.

GitHub Repository Specification: openrecon (v1.0 Definitive Specification)

A Local-First Settlement Compiler & Double-Entry Journal Exporter for Merchant-of-Record and Multi-Currency SaaS Billing

1. Executive Summary & Boundaries

1.1 What It Is

openrecon is an open-source, local-first CLI compiler that reconciles multi-currency Merchant-of-Record (Paddle, Lemon Squeezy, Polar) and payment gateway (Stripe) exports against bank settlement statements. It compiles opaque payout batches into mathematically verified, auditable, byte-reproducible double-entry accounting journals (Beancount, Ledger, and standard CSV).

1.2 Threat Model & Privacy Controls

• License: Apache-2.0 (provides explicit patent grant protection for payment settlement logic and frictionless adoption by downstream accounting ecosystems).
• Zero Cloud Footprint: Financial credentials, bank feeds, customer transaction data, and database files never leave the user's local machine.
• Default PII Redaction: Ingestion strips names, emails, street addresses, IP addresses, tax IDs, and payment method fingerprints, while preserving country, region, and generating a database-salted customer_ref. Dual SHA256 hashes (original_file_sha256 and redacted_payload_sha256) prove both source provenance and retained payload integrity.
• Default .gitignore: Pre-configured to prevent commits of *.db, *.db-wal, *.db-shm, exports/, ledgers/private/, and rules/learned.local.yaml.

1.3 Strict Non-Goals (Explicit Boundaries)

• Not a Bookkeeping System: Does not replace general ledger software; it compiles settlement transactions and exports journal entries.
• Not a Tax Filing Engine: Ingests and verifies provider-reported tax evidence; does not calculate statutory liabilities or file tax returns.
• Not a Live Bank Connector: Ingests local files only (CSV/JSON). No background daemons, no OAuth credentials, no live sync in v1.
• Not a SaaS Metrics Engine: Strictly excludes MRR, Churn, or LTV analytics.

2. Deterministic Identity & Timezone Models

2.1 Content-Addressed Stable Identity

Line numbers and file positions are evidence locations, never identities. IDs are deterministically derived from canonical content hashes:

event_id   = ev_<provider>_<base32(sha256(canonical_identity))[0:16]>
match_id   = m_<period>_<base32(sha256(sorted_event_ids + rule_id + rule_version))[0:16]>
posting_id = p_<base32(sha256(match_id + obligation_node + posting_index))[0:16]>

• Provider Event Canonical Identity: (merchant_entity, external_ref, event_type, amount_minor, currency, occurred_at).
• Bank Statement Fallback (when external ID is absent): (account_ref, booked_at, amount_minor, currency, normalized_description, counterparty_ref, duplicate_ordinal). Duplicate ordinals are deterministically computed after sorting identical candidate tuples.

2.2 Timezone & Period Bucketing

• Profile Locking: The entity configuration defines a fixed reporting_timezone (e.g. America/New_York) and fiscal_year_end.
• RFC 3339 Offsets: All timestamps are stored with explicit offsets. Period bucketing is computed in the configured reporting_timezone.
• Immutability Invariant: Changing reporting_timezone or fiscal_year_end after any period is locked is rejected as a hard error.

3. Formal Obligation Architecture & Event Families

Settlements are evaluated across typed, independent obligation DAGs by event family rather than a single mixed chain:

1. Sale Family (sale_chain):
   gross_sale -> tax_withheld -> provider_fee -> receivable_opened

2. Refund Family (refund_chain):
   gross_refund -> tax_returned -> fee_returned_policy (none|partial|full) -> receivable_reduced

3. Dispute Family (dispute_chain):
   dispute_hold_opened -> dispute_fee_event -> outcome_event -> (dispute_won_credited | loss_booked)

4. Reserve Family (reserve_chain):
   reserve_hold_opened -> reserve_release_event -> restricted_cash_cleared

5. Settlement Family (settlement_chain):
   SUM(receivable_delta) + SUM(position_delta) + fx_conversion + bank_fee -> bank_receipt

3.1 Currency & Balancing Invariants

• Single-Commodity Rule: Non-conversion journal entries must balance to zero in exactly one currency.
• Isolated Conversion Transactions: Currency conversions are isolated into dedicated transactions with explicit Beancount total-price notation (@@).
• Separation of Spread vs. Timing Gain/Loss:
  • Expenses:Financial:FXSpread: Provider conversion markup on conversion date.
  • Income:Financial:FXGain / Expenses:Financial:FXLoss: Realized currency movement between recognition and settlement dates.
• Corridor-Specific FX Bounds: Declared in pack manifests (e.g. EUR/USD: "4.0000%", BRL/USD: "9.0000%"). Implied markups exceeding bounds trigger contradicted: fx_spread_exceeded_bound.
• Network Decoupling: rates fetch is the sole networked command. Reconcile/export/verify are strictly offline. Missing reference rates trigger contradicted: fx_reference_rate_missing (weekend/holiday policy defaults to prior business day).
• Exact Decimal Arithmetic: All percentages, rates, and amounts are parsed into typed fixed-point rationals (shopspring/decimal). Zero float64 in calculation paths.

3.2 Structured Verdict Taxonomy

• satisfied: Obligations balance to zero within currency precision.
• degenerate: Multiple candidate matches exist, but all candidate projections onto accounting consequences (tax country, currency, accounts, period, amount) are identical. Engine auto-picks lowest deterministic event ID and logs tie_break='degenerate' with zero user prompts.
• ambiguous: Multiple candidates yield divergent accounting outcomes. Prompts user to select source of truth.
• residual: Unexplained variance remains (e.g. $15 intermediary wire fee). Prompts user to classify cause into a learned rule.
• contradicted: Hard bound or arithmetic violation (e.g. negative fees, corridor FX exceedance, rounding drift > 0.01 × order count). Halts execution.

4. Standard Chart of Accounts

Income:SaaS:Sales                              -- Gross revenue recognized on order date
Income:SaaS:Chargebacks                        -- Contra-revenue for dispute losses
Assets:Receivables:{Provider}                  -- Uncleared settlements from provider
Assets:RestrictedCash:{Provider}:Reserve       -- Rolling reserves held by provider
Assets:Bank:{Currency}                         -- Settled bank account funds
Expenses:PaymentProcessing:{Provider}:Fees     -- Provider transaction & platform fees
Expenses:PaymentProcessing:{Provider}:DisputeFees -- Provider dispute fees
Expenses:BankFees:{Bank}                       -- Intermediary / correspondent bank fees
Expenses:Financial:FXSpread                    -- Provider conversion markup
Expenses:Financial:FXLoss                      -- Realized loss on currency timing
Income:Financial:FXGain                        -- Realized gain on currency timing
Expenses:Rounding                              -- Sub-cent calculation rounding differences
Income:Rounding                                -- Sub-cent calculation rounding differences

5. SQLite Schema & Database Invariants

-- 1. Raw Imports
CREATE TABLE raw_imports (
    import_id               TEXT PRIMARY KEY,
    file_name               TEXT NOT NULL,
    original_file_sha256    TEXT NOT NULL,
    redacted_payload_sha256 TEXT NOT NULL,
    redaction_mode          TEXT NOT NULL,     -- 'default' | 'none'
    provider                TEXT NOT NULL,
    row_count               INTEGER NOT NULL,
    imported_at             TEXT NOT NULL
);

CREATE TABLE raw_records (
    import_id       TEXT NOT NULL REFERENCES raw_imports(import_id),
    line_number     INTEGER NOT NULL,
    payload_json    TEXT NOT NULL,
    PRIMARY KEY (import_id, line_number)
);

-- 2. Narrow, Signed Canonical Events
CREATE TABLE canonical_events (
    event_id        TEXT PRIMARY KEY,          -- Derived content-addressed hash
    import_id       TEXT NOT NULL REFERENCES raw_imports(import_id),
    source_line     INTEGER NOT NULL,
    event_type      TEXT NOT NULL,             -- sale, tax_withheld, fee, refund, dispute_opened, dispute_won, dispute_lost, payout, fx_conversion
    merchant_entity TEXT NOT NULL DEFAULT 'default',
    product_id      TEXT,
    occurred_at     TEXT NOT NULL,             -- RFC 3339 with offset
    amount_minor    INTEGER NOT NULL,          -- Signed integer in minor units
    currency        TEXT NOT NULL,
    external_ref    TEXT,
    parent_ref      TEXT,
    attributes_json TEXT NOT NULL
);

-- 3. Append-Only Match Ledger
CREATE TABLE match_ledger (
    match_id        TEXT PRIMARY KEY,
    merchant_entity TEXT NOT NULL DEFAULT 'default',
    period          TEXT NOT NULL,             -- YYYY-MM in reporting_timezone
    rule_id         TEXT NOT NULL,
    rule_version    TEXT NOT NULL,
    verdict         TEXT NOT NULL,             -- satisfied | degenerate | ambiguous | residual | contradicted
    residual_cents  INTEGER NOT NULL DEFAULT 0,
    candidate_count INTEGER NOT NULL DEFAULT 1,
    tie_break       TEXT,                      -- NULL | degenerate | manual
    decided_by      TEXT NOT NULL,             -- engine | user
    decided_at      TEXT NOT NULL,
    supersedes_id   TEXT REFERENCES match_ledger(match_id)
);

-- Prevent ledger forks: exactly one successor per match
CREATE UNIQUE INDEX match_ledger_one_successor
    ON match_ledger(supersedes_id) WHERE supersedes_id IS NOT NULL;

CREATE TABLE match_events (
    match_id        TEXT NOT NULL REFERENCES match_ledger(match_id),
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    obligation_node TEXT NOT NULL,
    PRIMARY KEY (match_id, event_id, obligation_node)
);

-- 4. Append-Only Decision Events (Learned Rules)
CREATE TABLE decision_events (
    decision_id     TEXT PRIMARY KEY,
    created_at      TEXT NOT NULL,
    actor           TEXT NOT NULL,             -- user | provider_pack | imported_rule
    scope_predicate TEXT NOT NULL,             -- JSON predicate
    decision_type   TEXT NOT NULL,             -- classify_residual | choose_candidate | suppress_noise
    account         TEXT,                      -- Required for classify_residual, NULL for choose/suppress
    amount_formula  TEXT NOT NULL,             -- JSON: {"type":"fixed","amount_minor":1500,"currency":"USD"}
    source_prompt   TEXT
);

-- 5. Position Events (Rolling Reserves & Disputes)
CREATE TABLE position_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    position_id     TEXT NOT NULL,
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    kind            TEXT NOT NULL,             -- open | increase | decrease | close | mark_overdue
    amount_minor    INTEGER NOT NULL,
    currency        TEXT NOT NULL,
    occurred_at     TEXT NOT NULL,
    match_id        TEXT REFERENCES match_ledger(match_id)
);

-- 6. Append-Only Lock Events
CREATE TABLE lock_events (
    lock_event_id   TEXT PRIMARY KEY,
    merchant_entity TEXT NOT NULL,
    period          TEXT NOT NULL,
    action          TEXT NOT NULL,             -- lock | unlock | relock
    actor           TEXT NOT NULL,
    reason          TEXT,
    input_sha256    TEXT NOT NULL,             -- Preimage: PKs, fields, pack hashes, rates, profile
    occurred_at     TEXT NOT NULL
);

-- Database Immutability Triggers
CREATE TRIGGER match_ledger_no_update BEFORE UPDATE ON match_ledger
BEGIN SELECT RAISE(ABORT, 'match_ledger is append-only'); END;
CREATE TRIGGER match_ledger_no_delete BEFORE DELETE ON match_ledger
BEGIN SELECT RAISE(ABORT, 'match_ledger is append-only'); END;

CREATE TRIGGER canonical_events_no_update BEFORE UPDATE ON canonical_events
BEGIN SELECT RAISE(ABORT, 'canonical_events is append-only'); END;
CREATE TRIGGER canonical_events_no_delete BEFORE DELETE ON canonical_events
BEGIN SELECT RAISE(ABORT, 'canonical_events is append-only'); END;

CREATE TRIGGER decision_events_no_update BEFORE UPDATE ON decision_events
BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;
CREATE TRIGGER decision_events_no_delete BEFORE DELETE ON decision_events
BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;

CREATE TRIGGER position_events_no_update BEFORE UPDATE ON position_events
BEGIN SELECT RAISE(ABORT, 'position_events is append-only'); END;
CREATE TRIGGER position_events_no_delete BEFORE DELETE ON position_events
BEGIN SELECT RAISE(ABORT, 'position_events is append-only'); END;

CREATE TRIGGER lock_events_no_update BEFORE UPDATE ON lock_events
BEGIN SELECT RAISE(ABORT, 'lock_events is append-only'); END;
CREATE TRIGGER lock_events_no_delete BEFORE DELETE ON lock_events
BEGIN SELECT RAISE(ABORT, 'lock_events is append-only'); END;

6. Prior-Period Adjustments (PPA) & Materiality Thresholds

1. Reversal & Relink: Writes reversal and new match rows carrying openrecon_reverses: <prior_match_id>.
2. Current Period Posting: Locked past journal files remain byte-immutable. The adjustment posts into the current open period with explicit contra-accounts (Income:SaaS:Chargebacks) and separate single-currency legs.
3. Three-Tier Materiality Governance:
   • Immaterial (<0.5% period revenue): Auto-posted to current open period.
   • Material (≥0.5% period revenue): Halts with contradicted: ppa_materiality_exceeded and requires --accept-material-ppa <match_id>.
   • Locked Tax Year: Never auto-posted into monthly export; generates a standalone restatement report.

7. Repository Layout & Manifest

openrecon/
├── cmd/openrecon/              # Cobra CLI entry point
├── core/
│   ├── db/                     # SQLite schema, migrations, triggers, WAL setup
│   ├── currency/               # Minor units (JPY=0, KWD=3), decimal arithmetic, offline ECB cache
│   ├── events/                 # Canonical event model & PII redactor
│   ├── obligations/            # Typed obligation DAG evaluator & FX corridor bound checks
│   ├── positions/              # Reserve/dispute subledger state machine
│   ├── ledger/                 # Match ledger, lock events, decision events
│   └── export/                 # Deterministic Beancount & CSV compilers
├── packs/                      # In-tree provider packs (Paddle, Lemon Squeezy, Stripe, Wise)
│   └── paddle/
│       ├── manifest.yaml       # Semver, review budget, typed reserve policy, FX corridors
│       ├── normalizer.go       # Imperative parser: Raw -> Canonical Events
│       ├── obligations.yaml    # Declarative obligation DAG & fee policies
│       ├── tax_rates.yaml      # Temporal tax tables
│       ├── CHANGELOG.md        # Model change log
│       ├── testdata/           # Small contract test vectors
│       └── golden/             # Expected events and journal outputs
├── testdata/
│   ├── e2e/                    # Multi-provider synthetic monthly scenarios
│   └── fixtures/               # Hostile raw edge-case dumps
├── examples/                   # Configuration profiles and sample .bean files
├── docs/
│   ├── accounting-model.md     # Chart of accounts, single-commodity rules, sign conventions
│   ├── non-goals.md            # Scope boundaries and operational limits
│   └── provider-packs.md       # Guide to writing and testing in-tree packs
├── .gitignore                  # Prevents accidental commits of *.db, exports/, ledgers/private/
├── LICENSE                     # Apache-2.0
├── README.md                   # Quickstart, architecture overview, CLI reference
└── Taskfile.yml                # CI tasks: pack hash verification, golden tests, byte-diff checks

8. CLI Reference & Workflows

# 1. Manage Reference Rates (Only networked command)
openrecon rates fetch --source ecb --from 2020-01-01
openrecon rates import ./rates/ecb.csv --source ecb

# 2. Ingest Exports with Default PII Redaction
openrecon import ./exports/2026-04 --profile examples/paddle-wise.yaml

# 3. Compile Settlement & Interactive Triage (Cause-level prompts)
openrecon reconcile --period 2026-04 --interactive

# 4. Inspect Explanations & Positions
openrecon explain --unmatched
openrecon positions --provider paddle --overdue

# 5. Lock Period, Export Beancount Journal, & Verify Reproducibility
openrecon lock --period 2026-04
openrecon export beancount --period 2026-04 > ./ledgers/2026-04.bean
openrecon verify --period 2026-04

# 6. Export Rules for Git & System Diagnostics
openrecon rules export --learned > ./rules/learned.yaml
openrecon doctor

9. Synthetic Acceptance Test Specification (CI Benchmark)

The acceptance test suite executes across two discrete phases in CI on Linux and Windows:

9.1 Cold Phase (No Pre-Existing Decisions)

• Dataset: 500 Orders (350 EUR VAT-inclusive sales across Germany/France, 150 USD), Paddle 10% gross reserve held, 5% + €0.50 fees, Wise USD payout arriving $15.00 short due to intermediary routing fees, 1 chargeback, 3 cross-month refunds, 1 March dispute clawback.
• Assertion: Must produce exactly 1 residual prompt (suggesting Expenses:BankFees:Wise for the $15.00 gap), 0 unbudgeted prompts, and block journal export until the residual decision is recorded.

9.2 Warm Phase (Decisions Ingested)

• Input: Ingests decisions.yaml generated from the Cold Phase.
• Assertion: Reconciles with 0 prompts, executes all FX conversions against the local ECB benchmark with <4% spread validation, and emits a Beancount export that balances to zero and matches the checked-in golden file byte-for-byte on both Linux and Windows CI runners.

GitHub Repository Specification: openrecon (Release-Ready Spec)

A Local-First Settlement Compiler & Double-Entry Journal Exporter for Merchant-of-Record and Multi-Currency SaaS Billing

1. Executive Summary & Boundaries

1.1 What It Is

openrecon is an open-source, local-first CLI compiler that reconciles multi-currency Merchant-of-Record (Paddle, Lemon Squeezy, Polar) and payment gateway (Stripe) exports against bank settlement statements. It compiles opaque payout batches into mathematically verified, auditable, byte-reproducible double-entry accounting journals (Beancount, Ledger, and standard CSV).

1.2 License, Privacy & Threat Model

• License: Apache-2.0 (provides explicit patent grant protection for payment settlement logic and frictionless adoption by downstream accounting tools).
• Zero Cloud Footprint: Financial credentials, bank feeds, customer transaction data, and database files never leave the user's local machine.
• Default PII Redaction: Ingestion automatically drops names, emails, street addresses, IP addresses, tax IDs, and payment method fingerprints, while preserving country, region, and generating a database-salted customer_ref. Dual SHA256 hashes (original_file_sha256 and redacted_payload_sha256) prove both original source provenance and local payload integrity.
• Default .gitignore: Pre-configured to prevent commits of *.db, *.db-wal, *.db-shm, exports/, ledgers/private/, and rules/learned.local.yaml.

1.3 Strict Non-Goals (Explicit Boundaries)

• Not a Bookkeeping System: Does not replace general ledger software; it compiles settlement transactions and exports journal entries.
• Not a Tax Filing Engine: Ingests and verifies provider-reported tax evidence; does not calculate statutory liabilities or file tax returns.
• Not a Live Bank Connector: Ingests local files only (CSV/JSON). No background daemons, no OAuth credentials, no live sync in v1.
• Not a SaaS Metrics Engine: Strictly excludes MRR, Churn, or LTV analytics.

2. Deterministic Identity, Idempotent Imports & Timezone Models

2.1 Content-Addressed Identity

Line numbers and file positions are evidence locations, never identities. IDs are deterministically derived from canonical content hashes:

event_id   = ev_<provider>_<base32(sha256(canonical_identity))[0:16]>
match_id   = m_<period_key>_<base32(sha256(sorted_event_ids + rule_id + rule_version))[0:16]>
posting_id = p_<base32(sha256(match_id + obligation_node + posting_index))[0:16]>

• Provider Event Canonical Identity: (merchant_entity, external_ref, event_type, amount_minor, currency, occurred_at, duplicate_ordinal).
• Bank Statement Fallback: (account_ref, booked_at, amount_minor, currency, normalized_description, counterparty_ref, duplicate_ordinal).
• Duplicate Ordinal Allocation: Computed against all previously recorded events in the same identity group and frozen once assigned.

2.2 Idempotent Re-Import & Source Provenance

• import_id is derived from (provider, original_file_sha256). Re-importing a byte-identical file is a detected no-op.
• Overlapping cumulative exports merge cleanly: existing event_ids are skipped, new events append to canonical_events, and every asserting file line records a row in event_sources(event_id, import_id, source_line, source_payload_hash, first_seen_at).

2.3 Timezone & Period Bucketing

• Entity profile specifies a locked reporting_timezone (e.g. America/New_York) and fiscal_year_end.
• Timestamps are stored in RFC 3339 with explicit offsets. Period bucketing (period_key with period_granularity IN ('month','quarter','year')) is evaluated in the configured timezone. Modifying reporting_timezone after any period is locked is rejected as a hard error.

3. Formal Obligation Architecture & Event Families

Settlements are evaluated across typed, independent obligation DAGs by event family:

1. Sale Family (sale_chain):
   gross_sale -> tax_withheld -> provider_fee -> receivable_opened

2. Refund Family (refund_chain):
   gross_refund -> tax_returned -> fee_returned_policy (none|partial|full) -> receivable_reduced

3. Dispute Family (dispute_chain):
   dispute_hold_opened -> dispute_fee_event -> outcome_event -> (dispute_won_credited | loss_booked)

4. Reserve Family (reserve_chain):
   reserve_hold_opened -> reserve_release_event -> restricted_cash_cleared

5. Settlement Family (settlement_chain):
   SUM(receivable_delta) + SUM(position_delta) + fx_conversion + bank_fee -> bank_receipt

3.1 Currency & Balancing Invariants

• Single-Commodity Rule: Non-conversion journal entries balance to zero in exactly one currency.
• Isolated Conversion Transactions: Currency conversions are isolated with explicit Beancount total-price notation (@@).
• Separation of Spread vs. Timing Gain/Loss:
  • Expenses:Financial:FXSpread: Provider conversion markup on conversion date.
  • Income:Financial:FXGain / Expenses:Financial:FXLoss: Realized currency movement between recognition and settlement dates.
• Corridor-Specific FX Bounds: Declared in pack manifests (e.g. EUR/USD: "4.0000%", BRL/USD: "9.0000%"). Markups exceeding bounds trigger contradicted: fx_spread_exceeded_bound.
• Network Decoupling: rates fetch is the sole networked command. Reconcile/export/verify are strictly offline. Missing reference rates trigger contradicted: fx_reference_rate_missing (weekend/holiday policy defaults to prior business day).
• Exact Decimal Arithmetic: All percentages, rates, and amounts are parsed into typed fixed-point rationals (shopspring/decimal). Zero float64 in calculation paths.

3.2 Structured Verdict Taxonomy

• satisfied: Obligations balance to zero within currency precision.
• degenerate: Multiple candidate matches exist, but all candidate projections onto accounting consequences (tax country, currency, accounts, period, amount) are identical. Auto-picks lowest event ID and logs tie_break='degenerate' with zero user prompts.
• ambiguous: Multiple candidates yield divergent accounting outcomes. Prompts user to select source of truth.
• residual: Unexplained variance remains (e.g. $15 intermediary wire fee). Prompts user to classify cause into a learned rule.
• contradicted: Hard bound or arithmetic violation (e.g. negative fees, corridor FX exceedance, rounding drift > 0.01 × order count). Halts execution.

4. Standard Chart of Accounts

Income:SaaS:Sales                              -- Gross revenue recognized on order date
Income:SaaS:Chargebacks                        -- Contra-revenue for dispute losses
Assets:Receivables:{Provider}                  -- Uncleared settlements from provider
Assets:RestrictedCash:{Provider}:Reserve       -- Rolling reserves held by provider
Assets:Bank:{Currency}                         -- Settled bank account funds
Expenses:PaymentProcessing:{Provider}:Fees     -- Provider transaction & platform fees
Expenses:PaymentProcessing:{Provider}:DisputeFees -- Provider dispute fees
Expenses:BankFees:{Bank}                       -- Intermediary / correspondent bank fees
Expenses:Financial:FXSpread                    -- Provider conversion markup
Expenses:Financial:FXLoss                      -- Realized loss on currency timing
Income:Financial:FXGain                        -- Realized gain on currency timing
Expenses:Rounding                              -- Sub-cent calculation rounding differences
Income:Rounding                                -- Sub-cent calculation rounding differences
Expenses:Suspense:Unreconciled                 -- Unallocated variance (provisional exports only)

5. SQLite Schema & Database Invariants

-- 1. Raw Imports & Source Provenance
CREATE TABLE raw_imports (
    import_id               TEXT PRIMARY KEY,
    file_name               TEXT NOT NULL,
    original_file_sha256    TEXT NOT NULL,
    redacted_payload_sha256 TEXT NOT NULL,
    redaction_mode          TEXT NOT NULL,     -- 'default' | 'none'
    provider                TEXT NOT NULL,
    row_count               INTEGER NOT NULL,
    imported_at             TEXT NOT NULL
);

CREATE TABLE raw_records (
    import_id       TEXT NOT NULL REFERENCES raw_imports(import_id),
    line_number     INTEGER NOT NULL,
    payload_json    TEXT NOT NULL,
    PRIMARY KEY (import_id, line_number)
);

-- 2. Narrow, Signed Canonical Events & Multi-Source Tracking
CREATE TABLE canonical_events (
    event_id        TEXT PRIMARY KEY,          -- Derived content-addressed hash
    merchant_entity TEXT NOT NULL DEFAULT 'default',
    product_id      TEXT,
    event_type      TEXT NOT NULL,             -- sale, tax_withheld, fee, refund, dispute_opened, dispute_won, dispute_lost, payout, fx_conversion
    occurred_at     TEXT NOT NULL,             -- RFC 3339 with offset
    amount_minor    INTEGER NOT NULL,          -- Signed integer in minor units
    currency        TEXT NOT NULL,
    external_ref    TEXT,
    parent_ref      TEXT,
    attributes_json TEXT NOT NULL
);

CREATE TABLE event_sources (
    event_id            TEXT NOT NULL REFERENCES canonical_events(event_id),
    import_id           TEXT NOT NULL REFERENCES raw_imports(import_id),
    source_line         INTEGER NOT NULL,
    source_payload_hash TEXT NOT NULL,
    first_seen_at       TEXT NOT NULL,
    PRIMARY KEY (event_id, import_id, source_line)
);

-- 3. Append-Only Match Ledger
CREATE TABLE match_ledger (
    match_id           TEXT PRIMARY KEY,
    merchant_entity    TEXT NOT NULL DEFAULT 'default',
    period_key         TEXT NOT NULL,          -- e.g. '2026-04', '2026-Q1', '2026'
    period_granularity TEXT NOT NULL CHECK (period_granularity IN ('month','quarter','year')),
    rule_id            TEXT NOT NULL,
    rule_version       TEXT NOT NULL,
    verdict            TEXT NOT NULL,          -- satisfied | degenerate | ambiguous | residual | contradicted
    residual_minor     INTEGER NOT NULL DEFAULT 0,
    residual_currency  TEXT,
    candidate_count    INTEGER NOT NULL DEFAULT 1,
    tie_break          TEXT,                   -- NULL | degenerate | manual
    decided_by         TEXT NOT NULL,          -- engine | user
    decided_at         TEXT NOT NULL,
    supersedes_id      TEXT REFERENCES match_ledger(match_id)
);

CREATE UNIQUE INDEX match_ledger_one_successor
    ON match_ledger(supersedes_id) WHERE supersedes_id IS NOT NULL;

CREATE TABLE match_events (
    match_id        TEXT NOT NULL REFERENCES match_ledger(match_id),
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    obligation_node TEXT NOT NULL,
    PRIMARY KEY (match_id, event_id, obligation_node)
);

-- 4. Append-Only Decision Events (Learned Rules)
CREATE TABLE decision_events (
    decision_id     TEXT PRIMARY KEY,
    created_at      TEXT NOT NULL,
    actor           TEXT NOT NULL,             -- user | provider_pack | imported_rule
    scope_predicate TEXT NOT NULL,             -- JSON predicate
    decision_type   TEXT NOT NULL,             -- classify_residual | choose_candidate | suppress_noise
    account         TEXT,                      -- Required for classify_residual, NULL for choose/suppress
    amount_formula  TEXT NOT NULL,             -- JSON: {"type":"fixed","amount_minor":1500,"currency":"USD"}
    source_prompt   TEXT
);

-- 5. Position Events (Rolling Reserves & Disputes)
CREATE TABLE position_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    position_id     TEXT NOT NULL,
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    kind            TEXT NOT NULL,             -- open | increase | decrease | close | mark_overdue
    amount_minor    INTEGER NOT NULL,
    currency        TEXT NOT NULL,
    occurred_at     TEXT NOT NULL,
    match_id        TEXT REFERENCES match_ledger(match_id)
);

-- 6. Append-Only Pack Overrides (DB-persisted local adjustments)
CREATE TABLE pack_overrides (
    override_id       TEXT PRIMARY KEY,
    provider          TEXT NOT NULL,
    base_pack_version TEXT NOT NULL,
    override_sha256   TEXT NOT NULL,
    canonical_payload TEXT NOT NULL,           -- Whitelisted JSON: fee_rate, fixed_fee, reserve_policy, bounds
    created_at        TEXT NOT NULL
);

-- 7. Append-Only Lock Events
CREATE TABLE lock_events (
    lock_event_id   TEXT PRIMARY KEY,
    merchant_entity TEXT NOT NULL,
    period_key      TEXT NOT NULL,
    period_granularity TEXT NOT NULL CHECK (period_granularity IN ('month','quarter','year')),
    action          TEXT NOT NULL,             -- lock | unlock | relock
    actor           TEXT NOT NULL,
    reason          TEXT,
    input_sha256    TEXT NOT NULL,             -- Preimage: PKs, canonical rows, overrides, rates, profile
    occurred_at     TEXT NOT NULL
);

-- Database Immutability Triggers (Enforced in core migrations)
CREATE TRIGGER match_ledger_no_update BEFORE UPDATE ON match_ledger
BEGIN SELECT RAISE(ABORT, 'match_ledger is append-only'); END;
CREATE TRIGGER match_ledger_no_delete BEFORE DELETE ON match_ledger
BEGIN SELECT RAISE(ABORT, 'match_ledger is append-only'); END;

CREATE TRIGGER canonical_events_no_update BEFORE UPDATE ON canonical_events
BEGIN SELECT RAISE(ABORT, 'canonical_events is append-only'); END;
CREATE TRIGGER canonical_events_no_delete BEFORE DELETE ON canonical_events
BEGIN SELECT RAISE(ABORT, 'canonical_events is append-only'); END;

CREATE TRIGGER decision_events_no_update BEFORE UPDATE ON decision_events
BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;
CREATE TRIGGER decision_events_no_delete BEFORE DELETE ON decision_events
BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;

CREATE TRIGGER position_events_no_update BEFORE UPDATE ON position_events
BEGIN SELECT RAISE(ABORT, 'position_events is append-only'); END;
CREATE TRIGGER position_events_no_delete BEFORE DELETE ON position_events
BEGIN SELECT RAISE(ABORT, 'position_events is append-only'); END;

CREATE TRIGGER lock_events_no_update BEFORE UPDATE ON lock_events
BEGIN SELECT RAISE(ABORT, 'lock_events is append-only'); END;
CREATE TRIGGER lock_events_no_delete BEFORE DELETE ON lock_events
BEGIN SELECT RAISE(ABORT, 'lock_events is append-only'); END;

CREATE TRIGGER pack_overrides_no_update BEFORE UPDATE ON pack_overrides
BEGIN SELECT RAISE(ABORT, 'pack_overrides is append-only'); END;
CREATE TRIGGER pack_overrides_no_delete BEFORE DELETE ON pack_overrides
BEGIN SELECT RAISE(ABORT, 'pack_overrides is append-only'); END;

6. Prior-Period Adjustments (PPA) & Materiality Thresholds

1. Reversal & Relink: Writes reversal and new match rows carrying openrecon_reverses: <prior_match_id>.
2. Current Period Posting: Locked past journal files remain byte-immutable. Adjustments post into current open period with explicit contra-accounts (Income:SaaS:Chargebacks) and separate single-currency legs.
3. Three-Tier Materiality Governance:
   • Immaterial (<0.5% period revenue): Auto-posted to current open period.
   • Material (≥0.5% period revenue): Halts with contradicted: ppa_materiality_exceeded and requires --accept-material-ppa <match_id>.
   • Locked Tax Year: Never auto-posted into monthly export; generates a standalone restatement report.

7. Repository Layout

openrecon/
├── cmd/openrecon/              # Cobra CLI entry point
├── core/
│   ├── db/                     # SQLite schema, migrations, 12 immutability triggers, WAL setup
│   ├── currency/               # Minor units (JPY=0, KWD=3), decimal arithmetic, offline ECB cache
│   ├── events/                 # Canonical event model & PII redactor
│   ├── obligations/            # Typed obligation DAG evaluator & FX corridor bound checks
│   ├── positions/              # Reserve/dispute subledger state machine
│   ├── ledger/                 # Match ledger, lock events, decision events, pack overrides
│   └── export/                 # Deterministic Beancount & CSV compilers (provisional & locked)
├── packs/                      # In-tree provider packs (Paddle, Lemon Squeezy, Stripe, Wise)
│   └── paddle/
│       ├── manifest.yaml       # Semver, typed review budget, typed reserve policy, FX corridors
│       ├── normalizer.go       # Imperative parser: Raw -> Canonical Events
│       ├── obligations.yaml    # Declarative obligation DAG & fee policies
│       ├── tax_rates.yaml      # Temporal tax tables
│       ├── CHANGELOG.md        # Model change log
│       ├── testdata/           # Small contract test vectors
│       └── golden/             # Expected events and journal outputs
├── testdata/
│   ├── e2e/                    # Multi-provider synthetic monthly scenarios
│   └── fixtures/               # Hostile raw edge-case dumps
├── examples/                   # Configuration profiles and sample .bean files
├── docs/
│   ├── accounting-model.md     # Chart of accounts, single-commodity rules, sign conventions
│   ├── non-goals.md            # Scope boundaries and operational limits
│   └── provider-packs.md       # Guide to writing and testing in-tree packs
├── .gitignore                  # Prevents accidental commits of *.db, exports/, ledgers/private/
├── LICENSE                     # Apache-2.0
├── README.md                   # Quickstart, architecture overview, CLI reference
└── Taskfile.yml                # CI tasks: pack hash verification, golden tests, byte-diff checks

8. CLI Reference & Workflows

# 1. Manage Reference Rates (Only networked command)
openrecon rates fetch --source ecb --from 2020-01-01
openrecon rates import ./rates/ecb.csv --source ecb

# 2. Preview and Ingest Exports (Default PII Redaction)
openrecon import preview ./exports/2026-04 --profile examples/paddle-wise.yaml
openrecon import ./exports/2026-04 --profile examples/paddle-wise.yaml

# 3. Compile Settlement & Interactive Triage (Cause-level prompts)
openrecon reconcile --period 2026-04 --interactive

# 4. Mid-Month Provisional Inspection vs. Overdue Positions
openrecon export beancount --period 2026-04 --allow-residual --provisional > ./ledgers/provisional.bean
openrecon positions --provider paddle --overdue

# 5. Lock Period, Export Canonical Beancount Journal, & Verify Reproducibility
openrecon lock --period 2026-04
openrecon export beancount --period 2026-04 > ./ledgers/2026-04.bean
openrecon verify --period 2026-04

# 6. Apply Local Pack Override & System Diagnostics
openrecon overrides apply ./overrides/paddle-fee-adjust.yaml
openrecon rules export --learned > ./rules/learned.yaml
openrecon doctor

9. Synthetic Acceptance Test Specification (CI Benchmark)

The acceptance test suite executes across two discrete phases in CI on Linux and Windows:

9.1 Cold Phase (No Pre-Existing Decisions)

• Dataset: 500 Orders (350 EUR VAT-inclusive sales across Germany/France, 150 USD), Paddle 10% gross reserve held, 5% + €0.50 fees, Wise USD payout arriving $15.00 short due to intermediary routing fees, 1 chargeback, 3 cross-month refunds, 1 March dispute clawback.
• Assertion: Must produce exactly 1 residual prompt (suggesting Expenses:BankFees:Wise for the $15.00 gap), 0 unbudgeted prompts, refuse openrecon lock, and require explicit --allow-residual --provisional flags if exported before locking.

9.2 Warm Phase (Decisions Ingested)

• Input: Ingests decisions.yaml generated from Cold Phase resolution.
• Assertion: Reconciles with 0 prompts, executes all FX conversions against the local ECB benchmark with <4% spread validation, locks cleanly, and emits a Beancount export that balances to zero and matches the checked-in golden file byte-for-byte on both Linux and Windows CI runners.

GitHub Repository Specification: openrecon (v1.0 Production Blueprint)

A Local-First Settlement Compiler & Double-Entry Journal Exporter for Merchant-of-Record and Multi-Currency SaaS Billing

1. Executive Summary & Boundaries

1.1 What It Is

openrecon is an open-source, local-first CLI compiler that reconciles multi-currency Merchant-of-Record (Paddle, Lemon Squeezy, Polar) and payment gateway (Stripe) exports against bank settlement statements. It compiles opaque payout batches into mathematically verified, auditable, byte-reproducible double-entry accounting journals (Beancount, Ledger, and standard CSV).

1.2 License, Privacy & Threat Model

• License: Apache-2.0 (provides explicit patent grant protection for payment settlement logic and frictionless adoption by downstream accounting ecosystems).
• Zero Cloud Footprint: Financial credentials, bank feeds, customer transaction data, and database files never leave the user's local machine.
• Default PII Redaction: Ingestion automatically drops names, emails, street addresses, IP addresses, tax IDs, and payment method fingerprints, while preserving country, region, and generating a database-salted customer_ref. Dual SHA256 hashes (original_file_sha256 and redacted_payload_sha256) prove both original source provenance and local payload integrity.
• Default .gitignore: Pre-configured to prevent accidental commits of *.db, *.db-wal, *.db-shm, exports/, ledgers/private/, and rules/learned.local.yaml.

1.3 Strict Non-Goals (Explicit Boundaries)

• Not a Bookkeeping System: Does not replace general ledger software; it compiles settlement transactions and exports journal entries.
• Not a Tax Filing Engine: Ingests and verifies provider-reported tax evidence; does not calculate statutory liabilities or file tax returns.
• Not a Live Bank Connector: Ingests local files only (CSV/JSON). No background daemons, no OAuth credentials, no live sync in v1.
• Not a SaaS Metrics Engine: Strictly excludes MRR, Churn, or LTV analytics.

2. Deterministic Identity, Idempotent Imports & Timezone Models

2.1 Content-Addressed Identity

Line numbers and file positions are evidence locations, never identities. IDs are deterministically derived from canonical content hashes:

event_id   = ev_<provider>_<base32(sha256(canonical_identity))[0:16]>
match_id   = m_<period_key>_<base32(sha256(sorted_event_ids + rule_id + rule_version))[0:16]>
posting_id = p_<base32(sha256(match_id + obligation_node + posting_index))[0:16]>

• Provider Event Identity: (merchant_entity, external_ref, event_type, amount_minor, currency, occurred_at, duplicate_ordinal).
• Bank Statement Fallback: (account_ref, booked_at, amount_minor, currency, normalized_description, counterparty_ref, duplicate_ordinal).
• Duplicate Ordinal Allocation: Computed against all previously recorded events in the same identity group and frozen once assigned.

2.2 Source Conflict Resolution & Natural Keys

• To handle cases where different files (e.g. Dashboard CSV vs. API JSON) assert conflicting data for the same provider record, events track natural_key and natural_key_scope.
• Conflicting variants trigger contradicted: source_disagreement and halt matching until the operator selects the authoritative source via openrecon resolve-conflict, recording the audit trail in source_conflict_decisions.

2.3 Idempotent Re-Import & Source Provenance

• import_id is derived from (provider, original_file_sha256). Re-importing a byte-identical file is a detected no-op.
• Overlapping cumulative exports merge cleanly: existing event_ids are skipped, new events append to canonical_events, and every asserting file line records a row in event_sources(event_id, import_id, source_line, source_payload_hash, first_seen_at).

2.4 Timezone & Period Bucketing

• Entity profile specifies a locked reporting_timezone (e.g. America/New_York) and fiscal_year_end.
• Timestamps are stored in RFC 3339 with explicit offsets. Period bucketing (period_key with period_granularity IN ('month','quarter','year')) is evaluated in the configured timezone. Modifying reporting_timezone after any period is locked is rejected as a hard error.

3. Formal Obligation Architecture & Event Families

Settlements are evaluated across typed, independent obligation DAGs by event family:

1. Sale Family (sale_chain):
   gross_sale -> tax_withheld -> provider_fee -> receivable_opened

2. Refund Family (refund_chain):
   gross_refund -> tax_returned -> fee_returned_policy (none|partial|full) -> receivable_reduced

3. Dispute Family (dispute_chain):
   dispute_hold_opened -> dispute_fee_event -> outcome_event -> (dispute_won_credited | loss_booked)

4. Reserve Family (reserve_chain):
   reserve_hold_opened -> reserve_release_event -> restricted_cash_cleared

5. Settlement Family (settlement_chain):
   SUM(receivable_delta) + SUM(position_delta) + fx_conversion + bank_fee -> bank_receipt

3.1 Currency & Balancing Invariants

• Single-Commodity Rule: Non-conversion journal entries balance to zero in exactly one currency.
• Isolated Conversion Transactions: Currency conversions are isolated with explicit Beancount total-price notation (@@).
• Separation of Spread vs. Timing Gain/Loss:
  • Expenses:Financial:FXSpread: Provider conversion markup on conversion date.
  • Income:Financial:FXGain / Expenses:Financial:FXLoss: Realized currency movement between recognition and settlement dates.
• Corridor-Specific FX Bounds: Declared in pack manifests (e.g. EUR/USD: "4.0000%", BRL/USD: "9.0000%"). Markups exceeding bounds trigger contradicted: fx_spread_exceeded_bound.
• Network Decoupling: rates fetch is the sole networked command. Reconcile/export/verify are strictly offline. Missing reference rates trigger contradicted: fx_reference_rate_missing (weekend/holiday policy defaults to prior business day).
• Exact Decimal Arithmetic: All percentages, rates, and amounts are parsed into typed fixed-point rationals (shopspring/decimal). Zero float64 in calculation paths.

3.2 Structured Verdict Taxonomy

• satisfied: Obligations balance to zero within currency precision.
• degenerate: Multiple candidate matches exist, but all candidate projections onto accounting consequences (tax country, currency, accounts, period, amount) are identical. Auto-picks lowest event ID and logs tie_break='degenerate' with zero user prompts.
• ambiguous: Multiple candidates yield divergent accounting outcomes. Prompts user to select source of truth.
• residual: Unexplained variance remains (e.g. $15 intermediary wire fee). Prompts user to classify cause into a learned rule.
• contradicted: Hard bound or arithmetic violation (e.g. negative fees, corridor FX exceedance, source disagreement, rounding drift > 0.01 × order count). Halts execution.

4. Standard Chart of Accounts

Income:SaaS:Sales                              -- Gross revenue recognized on order date
Income:SaaS:Chargebacks                        -- Contra-revenue for dispute losses
Assets:Receivables:{Provider}                  -- Uncleared settlements from provider
Assets:RestrictedCash:{Provider}:Reserve       -- Rolling reserves held by provider
Assets:Bank:{Currency}                         -- Settled bank account funds
Expenses:PaymentProcessing:{Provider}:Fees     -- Provider transaction & platform fees
Expenses:PaymentProcessing:{Provider}:DisputeFees -- Provider dispute fees
Expenses:BankFees:{Bank}                       -- Intermediary / correspondent bank fees
Expenses:Financial:FXSpread                    -- Provider conversion markup
Expenses:Financial:FXLoss                      -- Realized loss on currency timing
Income:Financial:FXGain                        -- Realized gain on currency timing
Expenses:Rounding                              -- Sub-cent calculation rounding differences
Income:Rounding                                -- Sub-cent calculation rounding differences
Expenses:Suspense:Unreconciled                 -- Unallocated variance (provisional exports only)

5. SQLite Schema & Database Invariants

-- 1. Raw Imports & Source Provenance
CREATE TABLE raw_imports (
    import_id               TEXT PRIMARY KEY,
    file_name               TEXT NOT NULL,
    original_file_sha256    TEXT NOT NULL,
    redacted_payload_sha256 TEXT NOT NULL,
    redaction_mode          TEXT NOT NULL,     -- 'default' | 'none'
    provider                TEXT NOT NULL,
    row_count               INTEGER NOT NULL,
    imported_at             TEXT NOT NULL
);

CREATE TABLE raw_records (
    import_id       TEXT NOT NULL REFERENCES raw_imports(import_id),
    line_number     INTEGER NOT NULL,
    payload_json    TEXT NOT NULL,
    PRIMARY KEY (import_id, line_number)
);

-- 2. Narrow, Signed Canonical Events & Multi-Source Tracking
CREATE TABLE canonical_events (
    event_id          TEXT PRIMARY KEY,        -- Derived content-addressed hash
    natural_key       TEXT,                    -- External order_id, payout_id, etc.
    natural_key_scope TEXT NOT NULL,           -- provider + merchant_entity + event_type
    merchant_entity   TEXT NOT NULL DEFAULT 'default',
    product_id        TEXT,
    event_type        TEXT NOT NULL,           -- sale, tax_withheld, fee, refund, dispute_opened, dispute_won, dispute_lost, payout, fx_conversion
    occurred_at       TEXT NOT NULL,           -- RFC 3339 with offset
    amount_minor      INTEGER NOT NULL,        -- Signed integer in minor units
    currency          TEXT NOT NULL,
    external_ref      TEXT,
    parent_ref        TEXT,
    duplicate_ordinal INTEGER NOT NULL DEFAULT 0,
    attributes_json   TEXT NOT NULL
);

CREATE TABLE event_sources (
    event_id            TEXT NOT NULL REFERENCES canonical_events(event_id),
    import_id           TEXT NOT NULL REFERENCES raw_imports(import_id),
    source_line         INTEGER NOT NULL,
    source_payload_hash TEXT NOT NULL,
    first_seen_at       TEXT NOT NULL,
    PRIMARY KEY (event_id, import_id, source_line)
);

-- Materialized detection view for conflicting source data on identical natural keys
CREATE VIEW source_conflicts AS
SELECT natural_key_scope, natural_key, COUNT(DISTINCT event_id) AS variant_count
FROM canonical_events
WHERE natural_key IS NOT NULL
GROUP BY natural_key_scope, natural_key
HAVING variant_count > 1;

CREATE TABLE source_conflict_decisions (
    conflict_id        TEXT PRIMARY KEY,
    natural_key_scope  TEXT NOT NULL,
    natural_key        TEXT NOT NULL,
    chosen_event_id    TEXT NOT NULL REFERENCES canonical_events(event_id),
    rejected_event_ids TEXT NOT NULL,          -- JSON array of discarded event_ids
    decision_id        TEXT NOT NULL REFERENCES decision_events(decision_id)
);

-- 3. Append-Only Match Ledger
CREATE TABLE match_ledger (
    match_id           TEXT PRIMARY KEY,
    merchant_entity    TEXT NOT NULL DEFAULT 'default',
    period_key         TEXT NOT NULL,          -- e.g. '2026-04', '2026-Q1', '2026'
    period_granularity TEXT NOT NULL CHECK (period_granularity IN ('month','quarter','year')),
    rule_id            TEXT NOT NULL,
    rule_version       TEXT NOT NULL,
    verdict            TEXT NOT NULL,          -- satisfied | degenerate | ambiguous | residual | contradicted
    residual_minor     INTEGER NOT NULL DEFAULT 0,
    residual_currency  TEXT,
    candidate_count    INTEGER NOT NULL DEFAULT 1,
    tie_break          TEXT,                   -- NULL | degenerate | manual
    decided_by         TEXT NOT NULL,          -- engine | user
    decided_at         TEXT NOT NULL,
    supersedes_id      TEXT REFERENCES match_ledger(match_id)
);

CREATE UNIQUE INDEX match_ledger_one_successor
    ON match_ledger(supersedes_id) WHERE supersedes_id IS NOT NULL;

CREATE TABLE match_events (
    match_id        TEXT NOT NULL REFERENCES match_ledger(match_id),
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    obligation_node TEXT NOT NULL,
    PRIMARY KEY (match_id, event_id, obligation_node)
);

-- 4. Append-Only Decision Events (Learned Rules)
CREATE TABLE decision_events (
    decision_id     TEXT PRIMARY KEY,
    created_at      TEXT NOT NULL,
    actor           TEXT NOT NULL,             -- user | provider_pack | imported_rule
    scope_predicate TEXT NOT NULL,             -- JSON predicate
    decision_type   TEXT NOT NULL,             -- classify_residual | choose_candidate | suppress_noise
    account         TEXT,                      -- Required for classify_residual, NULL for choose/suppress
    amount_formula  TEXT NOT NULL,             -- JSON: {"type":"fixed","amount_minor":1500,"currency":"USD"}
    source_prompt   TEXT
);

-- 5. Position Events (Rolling Reserves & Disputes)
CREATE TABLE position_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    position_id     TEXT NOT NULL,
    event_id        TEXT NOT NULL REFERENCES canonical_events(event_id),
    kind            TEXT NOT NULL,             -- open | increase | decrease | close | mark_overdue
    amount_minor    INTEGER NOT NULL,
    currency        TEXT NOT NULL,
    occurred_at     TEXT NOT NULL,
    match_id        TEXT REFERENCES match_ledger(match_id)
);

-- 6. Append-Only Pack Overrides (DB-persisted local adjustments)
CREATE TABLE pack_overrides (
    override_id       TEXT PRIMARY KEY,
    provider          TEXT NOT NULL,
    base_pack_version TEXT NOT NULL,
    override_sha256   TEXT NOT NULL,
    canonical_payload TEXT NOT NULL,           -- Whitelisted JSON: fee_rate, fixed_fee, reserve_policy, bounds
    created_at        TEXT NOT NULL
);

-- 7. Append-Only Lock Events
CREATE TABLE lock_events (
    lock_event_id   TEXT PRIMARY KEY,
    merchant_entity TEXT NOT NULL,
    period_key      TEXT NOT NULL,
    period_granularity TEXT NOT NULL CHECK (period_granularity IN ('month','quarter','year')),
    action          TEXT NOT NULL,             -- lock | unlock | relock
    actor           TEXT NOT NULL,
    reason          TEXT,
    input_sha256    TEXT NOT NULL,             -- Preimage: PKs, canonical rows, overrides, rates, profile
    occurred_at     TEXT NOT NULL
);

-- 14 Database Immutability Triggers (Enforced in core migrations)
CREATE TRIGGER match_ledger_no_update BEFORE UPDATE ON match_ledger
BEGIN SELECT RAISE(ABORT, 'match_ledger is append-only'); END;
CREATE TRIGGER match_ledger_no_delete BEFORE DELETE ON match_ledger
BEGIN SELECT RAISE(ABORT, 'match_ledger is append-only'); END;

CREATE TRIGGER canonical_events_no_update BEFORE UPDATE ON canonical_events
BEGIN SELECT RAISE(ABORT, 'canonical_events is append-only'); END;
CREATE TRIGGER canonical_events_no_delete BEFORE DELETE ON canonical_events
BEGIN SELECT RAISE(ABORT, 'canonical_events is append-only'); END;

CREATE TRIGGER raw_records_no_update BEFORE UPDATE ON raw_records
BEGIN SELECT RAISE(ABORT, 'raw_records is append-only'); END;
CREATE TRIGGER raw_records_no_delete BEFORE DELETE ON raw_records
BEGIN SELECT RAISE(ABORT, 'raw_records is append-only'); END;

CREATE TRIGGER event_sources_no_update BEFORE UPDATE ON event_sources
BEGIN SELECT RAISE(ABORT, 'event_sources is append-only'); END;
CREATE TRIGGER event_sources_no_delete BEFORE DELETE ON event_sources
BEGIN SELECT RAISE(ABORT, 'event_sources is append-only'); END;

CREATE TRIGGER decision_events_no_update BEFORE UPDATE ON decision_events
BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;
CREATE TRIGGER decision_events_no_delete BEFORE DELETE ON decision_events
BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;

CREATE TRIGGER position_events_no_update BEFORE UPDATE ON position_events
BEGIN SELECT RAISE(ABORT, 'position_events is append-only'); END;
CREATE TRIGGER position_events_no_delete BEFORE DELETE ON position_events
BEGIN SELECT RAISE(ABORT, 'position_events is append-only'); END;

CREATE TRIGGER lock_events_no_update BEFORE UPDATE ON lock_events
BEGIN SELECT RAISE(ABORT, 'lock_events is append-only'); END;
CREATE TRIGGER lock_events_no_delete BEFORE DELETE ON lock_events
BEGIN SELECT RAISE(ABORT, 'lock_events is append-only'); END;

CREATE TRIGGER pack_overrides_no_update BEFORE UPDATE ON pack_overrides
BEGIN SELECT RAISE(ABORT, 'pack_overrides is append-only'); END;
CREATE TRIGGER pack_overrides_no_delete BEFORE DELETE ON pack_overrides
BEGIN SELECT RAISE(ABORT, 'pack_overrides is append-only'); END;

6. Prior-Period Adjustments (PPA) & Materiality Thresholds

1. Reversal & Relink: Writes reversal and new match rows carrying openrecon_reverses: <prior_match_id>.
2. Current Period Posting: Locked past journal files remain byte-immutable. Adjustments post into current open period with explicit contra-accounts (Income:SaaS:Chargebacks) and separate single-currency legs.
3. Three-Tier Materiality Governance:
   • Immaterial (<0.5% period revenue): Auto-posted to current open period.
   • Material (≥0.5% period revenue): Halts with contradicted: ppa_materiality_exceeded and requires --accept-material-ppa <match_id>.
   • Locked Tax Year: Never auto-posted into monthly export; generates a standalone restatement report.

7. Modular Beancount Export Specification

Exports are modularized to prevent duplicate open directive errors in bean-check:

1. accounts.bean (openrecon export accounts):
   • Declares option "operating_currency" for all utilized currencies.
   • Emits exactly one dated open directive per account, dated to the earliest activity across the entire ledger.
2. Period Files (2026-03.bean, 2026-04.bean):
   • Contain strictly transactions and custom metadata directives. No account open statements.
   • Deterministic Human-Diffable Sort Ordering: Postings sorted by:
     (date, provider, payout_external_ref, order_external_ref, obligation_family, posting_index, posting_id)
3. Master Ledger Index (main.bean via openrecon export root --year 2026):

   include "accounts.bean"
   include "2026-03.bean"
   include "2026-04.bean"
   

8. Repository Layout

openrecon/
├── cmd/openrecon/              # Cobra CLI entry point
├── core/
│   ├── db/                     # SQLite schema, migrations, 14 immutability triggers, WAL setup
│   ├── currency/               # Minor units (JPY=0, KWD=3), decimal arithmetic, offline ECB cache
│   ├── events/                 # Canonical event model & PII redactor
│   ├── obligations/            # Typed obligation DAG evaluator & FX corridor bound checks
│   ├── positions/              # Reserve/dispute subledger state machine
│   ├── ledger/                 # Match ledger, lock events, decision events, pack overrides
│   └── export/                 # Deterministic Beancount (modular) & CSV compilers
├── packs/                      # In-tree provider packs (Paddle, Lemon Squeezy, Stripe, Wise)
│   └── paddle/
│       ├── manifest.yaml       # Semver, typed review budget, typed reserve policy, FX corridors
│       ├── normalizer.go       # Imperative parser: Raw -> Canonical Events
│       ├── obligations.yaml    # Declarative obligation DAG & fee policies
│       ├── tax_rates.yaml      # Temporal tax tables
│       ├── CHANGELOG.md        # Model change log
│       ├── testdata/           # Small contract test vectors
│       └── golden/             # Expected events and journal outputs
├── testdata/
│   ├── e2e/                    # 3-phase March-April synthetic scenario
│   └── fixtures/               # Hostile raw edge-case dumps
├── examples/                   # Configuration profiles and sample .bean files
├── docs/
│   ├── accounting-model.md     # Chart of accounts, single-commodity rules, sign conventions
│   ├── non-goals.md            # Scope boundaries and operational limits
│   └── provider-packs.md       # Guide to writing and testing in-tree packs
├── .gitignore                  # Prevents accidental commits of *.db, exports/, ledgers/private/
├── LICENSE                     # Apache-2.0
├── README.md                   # Quickstart, architecture overview, CLI reference
└── Taskfile.yml                # CI tasks: pack hash verification, golden tests, byte-diff checks

9. CLI Reference & Workflows

# 1. Manage Reference Rates (Only networked command)
openrecon rates fetch --source ecb --from 2020-01-01
openrecon rates import ./rates/ecb.csv --source ecb

# 2. Preview and Ingest Exports (Default PII Redaction)
openrecon import preview ./exports/2026-04 --profile examples/paddle-wise.yaml
openrecon import ./exports/2026-04 --profile examples/paddle-wise.yaml

# 3. Resolve Conflicting Sources (if contradictory exports exist)
openrecon resolve-conflict --conflict-id conf_paddle_ord_1029 --choose ev_paddle_api_1029

# 4. Compile Settlement & Interactive Triage (Cause-level prompts)
openrecon reconcile --period 2026-04 --interactive

# 5. Mid-Month Provisional Inspection vs. Overdue Positions
openrecon export beancount --period 2026-04 --allow-residual --provisional > ./ledgers/provisional.bean
openrecon positions --provider paddle --overdue

# 6. Lock Period, Export Modular Beancount Journals, & Verify Reproducibility
openrecon lock --period 2026-04
openrecon export accounts > ./ledgers/accounts.bean
openrecon export beancount --period 2026-04 > ./ledgers/2026-04.bean
openrecon export root --year 2026 > ./ledgers/main.bean
openrecon verify --period 2026-04

# 7. Apply Local Pack Override & System Integrity Diagnostics
openrecon overrides apply ./overrides/paddle-fee-adjust.yaml
openrecon rules export --learned > ./rules/learned.yaml
openrecon doctor

10. Synthetic Acceptance Test Specification (CI Benchmark)

The acceptance test suite executes across three sequential phases in CI on Linux and Windows:

10.1 Phase 1: March Initial Close (march_close)

• Dataset: March normal sales volume.
• Assertion: Reconciles, locks, exports 2026-03.bean. Records sha256(2026-03.bean) = sha256_march_v1.

10.2 Phase 2: Cold April Triage & PPA Materiality (cold_april)

• Dataset: April orders + March dispute clawback crossing 0.5% materiality threshold + $15.00 Wise intermediary wire shortfall.
• Assertion:
  1. Halts with contradicted: ppa_materiality_exceeded until operator passes --accept-material-ppa.
  2. Generates exactly 1 residual prompt (suggesting Expenses:BankFees:Wise for the $15.00 gap) and 0 unbudgeted prompts.
  3. Refuses openrecon lock while residual is open.

10.3 Phase 3: Warm April Close & Historical Immutability (warm_april)

• Input: Ingests decisions.yaml from Phase 2.
• Assertion:
  1. Reconciles with 0 prompts, locks April, and exports 2026-04.bean.
  2. Core Invariant Assertion: Re-exports March (2026-03.bean) and asserts that sha256(2026-03.bean) == sha256_march_v1 byte-for-byte.
  3. Passes cross-platform byte-equality tests between Linux and Windows runners.