56 lines
2.3 KiB
Markdown
56 lines
2.3 KiB
Markdown
# 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.
|