# Usage reconciliation

A working usage ledger with account-scoped ingestion, exact decimal quantities, linked corrections, immutable monthly closes, reconciliation, and JSON/CSV exports. The dark operator console can ingest usage, record corrections, review late input, and close a period.

This is a Python/SQLite implementation for one host. The earlier brief proposed PostgreSQL; that distributed deployment is not included. SQLite's single writer serializes ingest, adjustment approval, and close transactions across account-periods. It is stronger and less concurrent than an account-period row lock would be.

## Run

Python 3.12 or newer. No external packages.

```sh
python app.py --demo
```

Open **http://127.0.0.1:9090/**. Account: `demo`. Operator token: `demo-operator-token`. The API binds only to loopback. Three synthetic January 2026 usage events are preloaded.

Try a correction to `batch-worker / job-1001`, close January, then ingest another January event with a new ID. That late event appears in Pending adjustments. Post it to February, close February, and download both exports from Period totals. The January export is unchanged.

Other demo tokens are `demo-ingest-token` and `demo-viewer-token`. The ingest role can add events and read the account. The viewer can only read. `other-operator-token` is limited to account `other`; it cannot read or mutate `demo`.

The service preserves `ledger.sqlite` on restart. Stop with Ctrl+C. To reset the synthetic data, stop the process and remove `ledger.sqlite` and its matching `-wal`/`-shm` files. Tests and the evidence scenario use temporary databases.

## Tests and evidence

```sh
python verify.py
python demo.py
python build_report.py
```

Open `report.html` directly. `verification.json` and `results/test-log.txt` record the executed tests. The demo produces two verified frozen JSON exports, `results/entries.csv`, `results/audit.json`, `results/pending-before-approval.json`, and `results/summary.json`.

The scenario closes January at **19.750 compute minutes and 1,250 API calls**. A late 3.125-minute event and a correction of −2.000 minutes are approved for February. With 8.250 minutes of February usage, its close is **9.375 compute minutes**. The January hash remains unchanged. Six source events generate seven ledger entries; the correction contributes separate reversal and replacement entries.

## Accounting rules

| Unit | Accepted precision | Example |
| --- | --- | --- |
| `api_call` | Integer | `"1250"` |
| `compute_minute` | Up to three decimal places | `"12.500"` |
| `storage_gib_hour` | Up to three decimal places | `"0.125"` |

Quantities must be nonnegative decimal **strings**. Floats, exponents, negative input, excess precision, and more than 10^15 base units per event are rejected. No rounding occurs. Base quantities and cached totals are decimal integer strings; arithmetic uses Python integers. Totals can exceed JavaScript's exact-number range without losing units. Display and CSV quantities use fixed precision.

An event identity is `(account, source, event_id)`. Identical normalized content is idempotent. A conflicting duplicate returns 409. Timestamps require an explicit timezone and whole-second precision. They are normalized to UTC before choosing a calendar month. No local-time or daylight-saving billing calendar is inferred.

A correction identifies the previous event and supplies a replacement quantity plus a reason. It appends `-previous_amount` and `replacement_amount`; it does not overwrite the original. One correction can follow each revision. To correct it again, reference the most recent correction's source and ID. Open-period corrections post immediately to the parent's period. Closed-period corrections and late usage stay pending.

An operator approves a pending event into a later open period. A pending correction's parent must already be posted, and its target must be later than the parent's posting period and its original event-time period. This is an explicit carry-forward policy, not backdating. Corrections can yield a negative adjustment total. The console shows the net adjustment separately from the stored replacement quantity. Approval cannot be repeated.

## Closing and reconciliation

Each write begins with `BEGIN IMMEDIATE`. Ingest and close cannot both observe an open period and then commit conflicting decisions. If ingest obtains the lock first, its posting is included. If close obtains it first, the later event is ingested as pending. Both interleavings are exercised by tests that hold the first transaction and start the second on another connection.

Closing stores version 1, the rule version, an ingest sequence cutoff, a posting sequence cutoff, exact totals, and a SHA-256 digest of the canonical export. The posting cutoff is separate because a previously ingested pending event may be posted later. Repeating close returns the same version. Closed versions are never reopened or restated in this implementation; corrections carry forward to another period.

Reconciliation rebuilds totals from append-only postings and entries inside a read snapshot and compares them with the incremental total cache. A mismatch blocks close. Export rebuilds the closed rows at the stored posting cutoff and verifies the digest and totals. Database triggers reject update/delete on source events, entries, postings, closes, and audit records. The database owner can still change schema or files; these controls are not a tamper-proof external audit system.

JSON exports preserve exact source text. CSV exports prefix formula-like user-entered text with an apostrophe for spreadsheet safety. Audit records identify the actor, operation, subject, and time. UI history shows the latest 500 events and 100 audit records; pending adjustments show the oldest 500 separately so newer activity does not hide an old unresolved event. The database retains full history.

## API

All API routes require a bearer token scoped to the account in the path.

| Route | Role and input |
| --- | --- |
| `POST /api/accounts/{account}/events` | Ingest/operator: `{source,event_id,unit,quantity,occurred_at}` |
| `POST /api/accounts/{account}/corrections` | Operator: `{source,event_id,original_source,original_event_id,replacement_quantity,reason}` |
| `POST /api/accounts/{account}/approve` | Operator: `{event_seq,period}` |
| `POST /api/accounts/{account}/close` | Operator: `{period}` |
| `GET /api/accounts/{account}/state` | Any account role |
| `GET /api/accounts/{account}/reconcile?period=2026-01` | Any account role |
| `GET /api/accounts/{account}/export?period=2026-01` | Any account role; verified JSON |
| `GET /api/accounts/{account}/csv?period=2026-01` | Any account role; spreadsheet-safe CSV |

Example:

```sh
curl -sS http://127.0.0.1:9090/api/accounts/demo/events \
  -H 'Authorization: Bearer demo-ingest-token' \
  -H 'Content-Type: application/json' \
  --data '{"source":"gateway","event_id":"batch-02","unit":"api_call","quantity":"500","occurred_at":"2026-01-20T12:00:00Z"}'
```

For non-demo data, copy `examples/tokens.json` to a private file and replace all example keys with random tokens of at least 16 characters:

```sh
python -c "import secrets; print(secrets.token_urlsafe(32))"
python app.py --tokens private-tokens.json --db private-ledger.sqlite
```

The token file defines exact account, role, and actor claims. Store it and the SQLite directory with restricted filesystem permissions. Tokens are local credentials, not a substitute for a production identity provider.

## Scope

`ledger.py` contains the storage and reconciliation rules. `app.py` supplies the HTTP API and dark console. `test_ledger.py` covers duplicate/concurrent ingestion, precision, timezone boundaries, correction chains, late events, close races, immutable exports, corrupted cache detection, account isolation, and role checks.

There is no pricing, currency, tax, invoice generation, payment collection, or monetary rounding. There is no event-stream connector, bulk import UI, distributed locking, rule migration, closed-period restatement, enterprise identity, production TLS termination or load-test certification. Checked periodic local snapshots are supplied in `backup_schedule.py`. Keep SQLite on local storage. This package is a complete local ledger implementation with documented boundaries, not a claim of production financial-system readiness.

Technical references: [SQLite isolation](https://www.sqlite.org/isolation.html), [SQLite WAL](https://www.sqlite.org/wal.html), [Python SQLite interface](https://docs.python.org/3.12/library/sqlite3.html).

## Completed operations rehearsal

The operations run sends 800 requests for 400 distinct events through eight writers, kills processes before ingest and close commits, restores a frozen January export, and replays two post-snapshot records twice. February closes at the expected 2.625-minute adjustment, while January stays unchanged.

```sh
python operations_rehearsal.py
```

Read `operations-report.html` for expected and observed outcomes, or `completion.json` for source hashes and machine-readable checks. `OPERATIONS.md` contains local startup, online snapshot, restore, periodic backup and explicit recovery assumptions. `operations.py` and `backup_schedule.py` are runnable tools, not pseudocode. The rehearsal uses temporary data and stops all of its services before exiting.