406 lines
18 KiB
Python
Executable File
406 lines
18 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""Build/rebuild the SMSF SQLite ledger from current canonical project files.
|
|
|
|
Safe behaviour:
|
|
- Does not delete or move source files.
|
|
- Rebuilds smsf.sqlite from CSV/source-documents so imports are repeatable.
|
|
- Stores hashes for source documents.
|
|
- Generates read-only reports from DB for verification.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import argparse
|
|
import csv
|
|
import datetime as dt
|
|
import hashlib
|
|
import json
|
|
import re
|
|
import sqlite3
|
|
from decimal import Decimal, InvalidOperation
|
|
from pathlib import Path
|
|
from typing import Any
|
|
|
|
ROOT = Path(__file__).resolve().parents[1]
|
|
DB = ROOT / "smsf.sqlite"
|
|
CSV = ROOT / "transactions" / "actual-transactions.csv"
|
|
SOURCE_DOCS = ROOT / "source-documents"
|
|
REPORTS = ROOT / "reports"
|
|
|
|
|
|
def sha256(path: Path) -> str:
|
|
h = hashlib.sha256()
|
|
with path.open('rb') as f:
|
|
for chunk in iter(lambda: f.read(1024 * 1024), b''):
|
|
h.update(chunk)
|
|
return h.hexdigest()
|
|
|
|
|
|
def dec(v: str | None) -> str | None:
|
|
if v is None or str(v).strip() == "":
|
|
return None
|
|
try:
|
|
return str(Decimal(str(v).strip()))
|
|
except InvalidOperation:
|
|
return None
|
|
|
|
|
|
def text(v: str | None) -> str | None:
|
|
if v is None:
|
|
return None
|
|
v = v.strip()
|
|
return v if v else None
|
|
|
|
|
|
def infer_financial_year(date_s: str | None) -> str | None:
|
|
if not date_s:
|
|
return None
|
|
m = re.match(r"(\d{4})-(\d{2})-(\d{2})", date_s)
|
|
if not m:
|
|
return None
|
|
y, mo = int(m.group(1)), int(m.group(2))
|
|
return f"{y}-{(y+1)%100:02d}" if mo >= 7 else f"{y-1}-{y%100:02d}"
|
|
|
|
|
|
def parse_aud_equivalent(notes: str | None, currency: str | None, amount: str | None) -> str | None:
|
|
if currency == "AUD":
|
|
return dec(amount)
|
|
if not notes:
|
|
return None
|
|
m = re.search(r"AUD equivalent of total cash amount\s*=\s*A\$\s*([0-9,]+\.\d{2})", notes, re.I)
|
|
if not m:
|
|
m = re.search(r"AUD equivalent\s*(?:=|:)?\s*A\$\s*([0-9,]+\.\d{2})", notes, re.I)
|
|
return dec(m.group(1).replace(',', '')) if m else None
|
|
|
|
|
|
def parse_fx_rate(notes: str | None) -> str | None:
|
|
if not notes:
|
|
return None
|
|
m = re.search(r"US\$1\s*=\s*A\$\s*([0-9.]+)", notes)
|
|
return dec(m.group(1)) if m else None
|
|
|
|
|
|
def connect() -> sqlite3.Connection:
|
|
con = sqlite3.connect(DB)
|
|
con.execute("PRAGMA foreign_keys = ON")
|
|
con.row_factory = sqlite3.Row
|
|
return con
|
|
|
|
|
|
def create_schema(con: sqlite3.Connection) -> None:
|
|
con.executescript(
|
|
"""
|
|
DROP VIEW IF EXISTS v_holdings;
|
|
DROP VIEW IF EXISTS v_transactions_aud;
|
|
DROP VIEW IF EXISTS v_financial_year_summary;
|
|
DROP TABLE IF EXISTS audit_log;
|
|
DROP TABLE IF EXISTS decisions;
|
|
DROP TABLE IF EXISTS transaction_documents;
|
|
DROP TABLE IF EXISTS source_documents;
|
|
DROP TABLE IF EXISTS fx_rates;
|
|
DROP TABLE IF EXISTS cash_movements;
|
|
DROP TABLE IF EXISTS trade_lots;
|
|
DROP TABLE IF EXISTS transactions;
|
|
DROP TABLE IF EXISTS import_batches;
|
|
|
|
CREATE TABLE import_batches (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
imported_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
source_path TEXT NOT NULL,
|
|
source_sha256 TEXT,
|
|
row_count INTEGER NOT NULL,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE transactions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
transaction_id TEXT NOT NULL UNIQUE,
|
|
status TEXT NOT NULL,
|
|
asset_class TEXT,
|
|
instrument_type TEXT,
|
|
market TEXT,
|
|
ticker TEXT,
|
|
name TEXT,
|
|
side TEXT,
|
|
order_type TEXT,
|
|
trade_datetime TEXT,
|
|
timezone TEXT,
|
|
trade_date TEXT,
|
|
financial_year TEXT,
|
|
settlement_date TEXT,
|
|
quantity TEXT,
|
|
price_currency TEXT,
|
|
price TEXT,
|
|
gross_currency TEXT,
|
|
gross_amount TEXT,
|
|
brokerage_currency TEXT,
|
|
brokerage_and_gst TEXT,
|
|
total_currency TEXT,
|
|
total_cash_amount TEXT,
|
|
total_cash_amount_aud TEXT,
|
|
fx_rate_to_aud TEXT,
|
|
broker TEXT,
|
|
account TEXT,
|
|
source_document TEXT,
|
|
notes TEXT,
|
|
import_batch_id INTEGER REFERENCES import_batches(id),
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE trade_lots (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
transaction_id TEXT NOT NULL REFERENCES transactions(transaction_id) ON DELETE CASCADE,
|
|
lot_id TEXT NOT NULL UNIQUE,
|
|
asset_class TEXT,
|
|
ticker TEXT,
|
|
name TEXT,
|
|
acquisition_date TEXT,
|
|
quantity_acquired TEXT,
|
|
quantity_remaining TEXT,
|
|
native_currency TEXT,
|
|
native_cost_base TEXT,
|
|
aud_cost_base TEXT,
|
|
broker TEXT,
|
|
account TEXT,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE cash_movements (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
movement_id TEXT NOT NULL UNIQUE,
|
|
movement_date TEXT,
|
|
financial_year TEXT,
|
|
movement_type TEXT,
|
|
platform TEXT,
|
|
account TEXT,
|
|
currency TEXT,
|
|
amount TEXT,
|
|
amount_aud TEXT,
|
|
related_transaction_id TEXT REFERENCES transactions(transaction_id),
|
|
source_document TEXT,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE fx_rates (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
fx_id TEXT NOT NULL UNIQUE,
|
|
rate_date TEXT,
|
|
from_currency TEXT NOT NULL,
|
|
to_currency TEXT NOT NULL,
|
|
rate TEXT NOT NULL,
|
|
source TEXT,
|
|
source_document TEXT,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE source_documents (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
path TEXT NOT NULL UNIQUE,
|
|
doc_type TEXT,
|
|
sha256 TEXT NOT NULL,
|
|
size_bytes INTEGER NOT NULL,
|
|
captured_date TEXT,
|
|
description TEXT,
|
|
ocr_text TEXT,
|
|
notes TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE transaction_documents (
|
|
transaction_id TEXT NOT NULL REFERENCES transactions(transaction_id) ON DELETE CASCADE,
|
|
source_document_id INTEGER NOT NULL REFERENCES source_documents(id) ON DELETE CASCADE,
|
|
relationship TEXT NOT NULL DEFAULT 'evidence',
|
|
PRIMARY KEY (transaction_id, source_document_id, relationship)
|
|
);
|
|
|
|
CREATE TABLE decisions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
decision_date TEXT NOT NULL,
|
|
topic TEXT NOT NULL,
|
|
decision TEXT NOT NULL,
|
|
source TEXT,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE audit_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
event_time TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
event_type TEXT NOT NULL,
|
|
entity_type TEXT,
|
|
entity_id TEXT,
|
|
details TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_transactions_ticker ON transactions(ticker);
|
|
CREATE INDEX idx_transactions_trade_date ON transactions(trade_date);
|
|
CREATE INDEX idx_transactions_financial_year ON transactions(financial_year);
|
|
CREATE INDEX idx_transactions_broker ON transactions(broker);
|
|
CREATE INDEX idx_lots_ticker ON trade_lots(ticker);
|
|
CREATE INDEX idx_docs_sha ON source_documents(sha256);
|
|
|
|
CREATE VIEW v_transactions_aud AS
|
|
SELECT transaction_id, status, financial_year, trade_date, trade_datetime, timezone,
|
|
asset_class, instrument_type, market, ticker, name, side, quantity,
|
|
price_currency, price, total_currency, total_cash_amount,
|
|
total_cash_amount_aud, fx_rate_to_aud, brokerage_and_gst,
|
|
broker, account, source_document, notes
|
|
FROM transactions;
|
|
|
|
CREATE VIEW v_holdings AS
|
|
SELECT ticker, name, asset_class, instrument_type, market, broker, account,
|
|
SUM(CASE WHEN lower(side)='buy' THEN CAST(quantity AS REAL)
|
|
WHEN lower(side)='sell' THEN -CAST(quantity AS REAL)
|
|
ELSE 0 END) AS quantity,
|
|
SUM(CASE WHEN lower(side)='buy' THEN CAST(COALESCE(total_cash_amount_aud,total_cash_amount) AS REAL)
|
|
WHEN lower(side)='sell' THEN -CAST(COALESCE(total_cash_amount_aud,total_cash_amount) AS REAL)
|
|
ELSE 0 END) AS aud_cost_base
|
|
FROM transactions
|
|
WHERE status='executed' AND ticker IS NOT NULL
|
|
GROUP BY ticker, name, asset_class, instrument_type, market, broker, account;
|
|
|
|
CREATE VIEW v_financial_year_summary AS
|
|
SELECT financial_year, asset_class, total_currency,
|
|
COUNT(*) AS transaction_count,
|
|
SUM(CAST(total_cash_amount AS REAL)) AS native_total,
|
|
SUM(CAST(COALESCE(total_cash_amount_aud,total_cash_amount) AS REAL)) AS aud_total
|
|
FROM transactions
|
|
GROUP BY financial_year, asset_class, total_currency;
|
|
"""
|
|
)
|
|
|
|
|
|
def import_source_documents(con: sqlite3.Connection) -> dict[str, int]:
|
|
mapping: dict[str, int] = {}
|
|
for path in sorted(SOURCE_DOCS.rglob('*')):
|
|
if not path.is_file():
|
|
continue
|
|
rel = path.relative_to(ROOT).as_posix()
|
|
ext = path.suffix.lower()
|
|
doc_type = {'.jpg': 'screenshot/image', '.jpeg': 'screenshot/image', '.png': 'screenshot/image', '.csv': 'csv/export', '.pdf': 'pdf'}.get(ext, ext.lstrip('.') or 'unknown')
|
|
cur = con.execute(
|
|
"INSERT INTO source_documents(path, doc_type, sha256, size_bytes, description) VALUES (?, ?, ?, ?, ?)",
|
|
(rel, doc_type, sha256(path), path.stat().st_size, path.parent.name.replace('-', ' ')),
|
|
)
|
|
mapping[rel] = int(cur.lastrowid)
|
|
mapping['../' + rel] = int(cur.lastrowid)
|
|
return mapping
|
|
|
|
|
|
def import_transactions(con: sqlite3.Connection, doc_map: dict[str, int]) -> int:
|
|
rows = list(csv.DictReader(CSV.open(newline='')))
|
|
batch = con.execute(
|
|
"INSERT INTO import_batches(source_path, source_sha256, row_count, notes) VALUES (?, ?, ?, ?)",
|
|
(CSV.relative_to(ROOT).as_posix(), sha256(CSV), len(rows), 'Imported from existing canonical CSV during SQLite migration.'),
|
|
).lastrowid
|
|
for r in rows:
|
|
trade_datetime = text(r.get('trade_datetime'))
|
|
trade_date = trade_datetime[:10] if trade_datetime else None
|
|
fy = infer_financial_year(trade_date)
|
|
total_cur = text(r.get('total_currency'))
|
|
total_amt = dec(r.get('total_cash_amount'))
|
|
notes = text(r.get('notes'))
|
|
aud_equiv = parse_aud_equivalent(notes, total_cur, total_amt)
|
|
fx_rate = parse_fx_rate(notes)
|
|
vals = (
|
|
text(r.get('transaction_id')), text(r.get('status')) or 'unknown', text(r.get('asset_class')),
|
|
text(r.get('instrument_type')), text(r.get('market')), text(r.get('ticker')), text(r.get('name')),
|
|
text(r.get('side')), text(r.get('order_type')), trade_datetime, text(r.get('timezone')), trade_date, fy,
|
|
text(r.get('settlement_date')), dec(r.get('quantity')), text(r.get('price_currency')), dec(r.get('price')),
|
|
text(r.get('gross_currency')), dec(r.get('gross_amount')), text(r.get('brokerage_currency')), dec(r.get('brokerage_and_gst')),
|
|
total_cur, total_amt, aud_equiv, fx_rate, text(r.get('broker')), text(r.get('account')),
|
|
text(r.get('source_document')), notes, batch
|
|
)
|
|
con.execute(
|
|
"""INSERT INTO transactions (
|
|
transaction_id,status,asset_class,instrument_type,market,ticker,name,side,order_type,trade_datetime,timezone,trade_date,financial_year,
|
|
settlement_date,quantity,price_currency,price,gross_currency,gross_amount,brokerage_currency,brokerage_and_gst,total_currency,total_cash_amount,
|
|
total_cash_amount_aud,fx_rate_to_aud,broker,account,source_document,notes,import_batch_id
|
|
) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
|
|
vals,
|
|
)
|
|
source_doc = text(r.get('source_document'))
|
|
doc_id = doc_map.get(source_doc or '')
|
|
if doc_id:
|
|
con.execute("INSERT OR IGNORE INTO transaction_documents(transaction_id, source_document_id) VALUES (?, ?)", (text(r.get('transaction_id')), doc_id))
|
|
|
|
# For buys, create an open lot. Sells/disposals can be matched later.
|
|
if (text(r.get('status')) == 'executed') and (text(r.get('side')) or '').lower() == 'buy':
|
|
tid = text(r.get('transaction_id'))
|
|
con.execute(
|
|
"""INSERT INTO trade_lots(lot_id, transaction_id, asset_class, ticker, name, acquisition_date,
|
|
quantity_acquired, quantity_remaining, native_currency, native_cost_base, aud_cost_base, broker, account, notes)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
|
|
(f"LOT-{tid}", tid, text(r.get('asset_class')), text(r.get('ticker')), text(r.get('name')), trade_date,
|
|
dec(r.get('quantity')), dec(r.get('quantity')), total_cur, total_amt, aud_equiv or (total_amt if total_cur == 'AUD' else None),
|
|
text(r.get('broker')), text(r.get('account')), 'Auto-created from executed buy transaction during SQLite migration.'),
|
|
)
|
|
return len(rows)
|
|
|
|
|
|
def seed_fx_and_cash(con: sqlite3.Connection, doc_map: dict[str, int]) -> None:
|
|
# Known Stake FX confirmation captured in existing notes.
|
|
con.execute(
|
|
"""INSERT INTO fx_rates(fx_id, rate_date, from_currency, to_currency, rate, source, source_document, notes)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
|
|
('2026-05-08-STAKE-USD-AUD-001', '2026-05-08', 'USD', 'AUD', '1.3813833726', 'Stake deposit confirmation',
|
|
'source-documents/2026-05-08-us-fx/stake-fx-deposit-confirmation.jpg', 'Inverse of A$1 = US$0.723912; received US$17,960.30 from A$25,000.00 with US$137.50 fees.'),
|
|
)
|
|
con.execute(
|
|
"""INSERT INTO cash_movements(movement_id, movement_date, financial_year, movement_type, platform, account, currency, amount, amount_aud, source_document, notes)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
|
|
('2026-05-08-STAKE-FX-DEPOSIT-001', '2026-05-08', infer_financial_year('2026-05-08'), 'fx_deposit', 'Stake', 'SMSF', 'USD', '17960.30', '25000.00',
|
|
'source-documents/2026-05-08-us-fx/stake-fx-deposit-confirmation.jpg', 'Stake confirmation: sent A$25,000.00, received US$17,960.30, fees US$137.50, reference 3B08FFB9.'),
|
|
)
|
|
|
|
|
|
def seed_decisions(con: sqlite3.Connection) -> None:
|
|
decisions = [
|
|
('2026-05-14', 'SMSF record keeping', 'Create SQLite database as canonical structured ledger while preserving existing CSV/Markdown/source documents.', 'Telegram discussion', 'No original files deleted or moved.'),
|
|
('2026-05-08', 'US trade FX rate', 'Use Stake deposit confirmation rate A$1 = US$0.723912 / US$1 = A$1.3813833726 for initial US purchases unless accountant advises otherwise.', 'transactions/fx-rate-notes.md', None),
|
|
('2026-05-08', 'BTC exchange', 'BTC order-history rows are recorded as CoinSpot SMSF BTC/AUD buys.', 'transactions/btc-orderhistory-summary.md', 'Broker/exchange confirmed by Michael.'),
|
|
]
|
|
con.executemany("INSERT INTO decisions(decision_date, topic, decision, source, notes) VALUES (?, ?, ?, ?, ?)", decisions)
|
|
|
|
|
|
def write_reports(con: sqlite3.Connection) -> None:
|
|
REPORTS.mkdir(exist_ok=True)
|
|
rows = con.execute("SELECT * FROM v_holdings ORDER BY asset_class, ticker").fetchall()
|
|
lines = ["# SMSF Holdings from SQLite", "", "Generated from `smsf.sqlite`.", "", "| Ticker | Name | Asset class | Market | Quantity | AUD cost base | Broker |", "|---|---|---|---|---:|---:|---|"]
|
|
for r in rows:
|
|
lines.append(f"| {r['ticker']} | {r['name']} | {r['asset_class']} | {r['market']} | {r['quantity']:.11g} | {r['aud_cost_base']:.2f} | {r['broker']} |")
|
|
(REPORTS / "holdings-from-db.md").write_text("\n".join(lines) + "\n")
|
|
|
|
tx_count = con.execute("SELECT COUNT(*) FROM transactions").fetchone()[0]
|
|
doc_count = con.execute("SELECT COUNT(*) FROM source_documents").fetchone()[0]
|
|
lot_count = con.execute("SELECT COUNT(*) FROM trade_lots").fetchone()[0]
|
|
sums = con.execute("SELECT total_currency, COUNT(*) c, SUM(CAST(total_cash_amount AS REAL)) total FROM transactions GROUP BY total_currency ORDER BY total_currency").fetchall()
|
|
aud_sum = con.execute("SELECT SUM(CAST(COALESCE(total_cash_amount_aud,total_cash_amount) AS REAL)) FROM transactions").fetchone()[0]
|
|
vlines = ["# SMSF SQLite Migration Verification", "", f"Generated: {dt.datetime.now().astimezone().isoformat(timespec='seconds')}", "", "## Counts", "", f"- Transactions imported: **{tx_count}**", f"- Trade lots created: **{lot_count}**", f"- Source documents indexed: **{doc_count}**", "", "## Native totals by currency", ""]
|
|
for s in sums:
|
|
vlines.append(f"- {s['total_currency']}: {s['c']} transaction(s), total {s['total']:.2f}")
|
|
vlines += ["", f"## AUD reporting total where known", "", f"- Total AUD-equivalent transaction cash amount: **A${aud_sum:.2f}**", "", "## Notes", "", "- Existing source documents, Markdown files, CSV ledger, and workbook were not deleted or moved.", "- This database is record-keeping support only; accountant/auditor should confirm final classifications and reporting treatment."]
|
|
(REPORTS / "sqlite-migration-verification.md").write_text("\n".join(vlines) + "\n")
|
|
|
|
|
|
def main() -> None:
|
|
ap = argparse.ArgumentParser(description='Build/rebuild the SMSF SQLite ledger from current project files')
|
|
ap.add_argument('--force', action='store_true', help='Allow rebuilding an existing smsf.sqlite database')
|
|
args = ap.parse_args()
|
|
|
|
if DB.exists() and not args.force:
|
|
raise SystemExit(f"Refusing to rebuild existing {DB}. Use --force after making a backup.")
|
|
|
|
with connect() as con:
|
|
create_schema(con)
|
|
doc_map = import_source_documents(con)
|
|
count = import_transactions(con, doc_map)
|
|
seed_fx_and_cash(con, doc_map)
|
|
seed_decisions(con)
|
|
con.execute("INSERT INTO audit_log(event_type, entity_type, entity_id, details) VALUES (?, ?, ?, ?)", ('migration', 'database', 'smsf.sqlite', json.dumps({'transactions_imported': count, 'source_documents_indexed': len(doc_map)})))
|
|
con.commit()
|
|
write_reports(con)
|
|
print(f"Built {DB}")
|
|
|
|
if __name__ == '__main__':
|
|
main()
|