# SMSF SQLite Usage Notes

Database: `knowledge/projects/smsf/smsf.sqlite`

The SQLite database is now the structured ledger layer for audit/tax reporting support. Existing CSV, Markdown, workbook, and source documents remain in place.

## Key tables

- `transactions` — canonical transaction/event ledger imported from `transactions/actual-transactions.csv`.
- `trade_lots` — open acquisition lots auto-created from executed buy transactions.
- `cash_movements` — bank/platform cash movements, including the initial Stake FX deposit record.
- `fx_rates` — explicit FX rates used for AUD reporting.
- `source_documents` — indexed evidence files with SHA256 hashes.
- `transaction_documents` — links transactions to evidence.
- `decisions` — accounting/record-keeping decisions and assumptions.
- `audit_log` — migration/import events.

## Useful queries

```bash
sqlite3 -header -column knowledge/projects/smsf/smsf.sqlite \
  "SELECT * FROM v_holdings;"
```

```bash
sqlite3 -header -column knowledge/projects/smsf/smsf.sqlite \
  "SELECT financial_year, asset_class, total_currency, transaction_count, native_total, aud_total FROM v_financial_year_summary;"
```

```bash
sqlite3 -header -column knowledge/projects/smsf/smsf.sqlite \
  "SELECT transaction_id, trade_date, ticker, side, quantity, total_currency, total_cash_amount, total_cash_amount_aud, source_document FROM v_transactions_aud ORDER BY trade_date;"
```

```bash
sqlite3 -header -column knowledge/projects/smsf/smsf.sqlite \
  "SELECT path, sha256, size_bytes FROM source_documents ORDER BY path;"
```

## Rebuild process

The DB can be rebuilt from current project files with:

```bash
# Make a backup first, then explicitly force the rebuild
knowledge/projects/smsf/scripts/build_smsf_db.py --force
```

The script refuses to overwrite an existing database unless `--force` is supplied. It does not delete or move original files. It rebuilds `smsf.sqlite`, indexes source documents, imports the CSV ledger, recreates lots/views, and regenerates verification reports.

## Audit posture

- Keep source documents immutable.
- Record corrections/assumptions in `decisions`, transaction `notes`, or future adjustment tables rather than silently changing evidence.
- Accountant/auditor should confirm final tax classifications, FX treatment, and reporting format.