# Grocery Ledger Schema

Canonical database: `dorian_groceries.db`

## Tables

### grocery_orders
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `order_id` TEXT UNIQUE NOT NULL
- `merchant` TEXT NOT NULL
- `order_date` TEXT NOT NULL
- `fulfillment_type` TEXT
- `order_total` TEXT NOT NULL
- `item_subtotal` TEXT NOT NULL
- `adjustment_total` TEXT NOT NULL
- `reconciliation_delta` TEXT NOT NULL
- `synced_at` TEXT DEFAULT (datetime('now'))

### grocery_items
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `order_id` TEXT NOT NULL
- `item_name` TEXT NOT NULL
- `quantity` TEXT
- `unit_price` TEXT
- `item_total` TEXT NOT NULL
- FOREIGN KEY (`order_id`) REFERENCES `grocery_orders`(`order_id`)

### grocery_adjustments
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `order_id` TEXT NOT NULL
- `adjustment_name` TEXT NOT NULL
- `adjustment_amount` TEXT NOT NULL
- `adjustment_type` TEXT
- FOREIGN KEY (`order_id`) REFERENCES `grocery_orders`(`order_id`)

### sync_log
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `synced_at` TEXT DEFAULT (datetime('now'))
- `merchant` TEXT NOT NULL
- `orders_found` INTEGER NOT NULL
- `items_written` INTEGER NOT NULL
- `status` TEXT NOT NULL
- `message` TEXT NOT NULL

## Constraints
- `order_id` is globally unique in the canonical ledger.
- Preserve reconciliation deltas exactly; do not fabricate balancing rows.
- Keep every visible adjustment line as a separate row.
- Use merchant-specific yearly CSV exports in addition to the canonical SQLite ledger.

---

# Purchase Tracker Schema (THR-28)

Canonical database: `/Users/openclaw/outputs/purchase-tracker/purchases.db`

## Tables

### purchase_orders
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `order_id` TEXT UNIQUE NOT NULL
- `merchant` TEXT NOT NULL
- `order_date` TEXT NOT NULL
- `fulfillment_type` TEXT
- `order_total` TEXT NOT NULL
- `item_subtotal` TEXT NOT NULL
- `adjustment_total` TEXT NOT NULL
- `reconciliation_delta` TEXT NOT NULL
- `synced_at` TEXT DEFAULT (datetime('now'))

### purchase_items
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `order_id` TEXT NOT NULL
- `item_name` TEXT NOT NULL
- `quantity` TEXT
- `unit_price` TEXT
- `item_total` TEXT NOT NULL
- FOREIGN KEY (`order_id`) REFERENCES `purchase_orders`(`order_id`)

### purchase_adjustments
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `order_id` TEXT NOT NULL
- `adjustment_name` TEXT NOT NULL
- `adjustment_amount` TEXT NOT NULL
- `adjustment_type` TEXT
- FOREIGN KEY (`order_id`) REFERENCES `purchase_orders`(`order_id`)

### purchase_sync_log
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `synced_at` TEXT DEFAULT (datetime('now'))
- `merchant` TEXT NOT NULL
- `orders_found` INTEGER NOT NULL
- `items_written` INTEGER NOT NULL
- `status` TEXT NOT NULL
- `message` TEXT NOT NULL

### purchase_order_exceptions
- `id` INTEGER PRIMARY KEY AUTOINCREMENT
- `run_id` TEXT NOT NULL
- `merchant` TEXT NOT NULL
- `order_id` TEXT NOT NULL
- `route_family` TEXT
- `order_date` TEXT
- `visible_order_total` TEXT
- `extracted_subtotal` TEXT
- `reconciliation_delta` TEXT
- `failure_reason` TEXT NOT NULL
- `failure_detail` TEXT
- `detail_url` TEXT
- `artifact_refs_json` TEXT
- `status` TEXT NOT NULL DEFAULT `open`
- `retry_count` INTEGER NOT NULL DEFAULT `0`
- `last_retry_at` TEXT
- `created_at` TEXT DEFAULT (datetime('now'))
- `updated_at` TEXT DEFAULT (datetime('now'))

## Notes
- `purchase_*` tables are the THR-28 canonical purchase tracker namespace.
- Legacy `grocery_*` tables in `dorian_groceries.db` are preserved as a backup and migration source only.
- Canonical cleanliness rule: `partial_sync`/untrusted orders are captured in `purchase_order_exceptions` and must not be inserted into canonical order/item/adjustment tables.