[01] Building a Personal ALM System — Your Life as a Database Schema

What Is ALM and Why Should You Care?

ALM — Asset-Liability Management — is how banks avoid going bankrupt. They model every asset (loans they've made, securities they hold) alongside every liability (deposits, bonds they've issued) and stress-test the relationship between them.

Your personal finances have the same structure. You just never drew the diagram.

YOU, INC.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ASSETS                    LIABILITIES
├─ Stocks (portfolio)     ├─ Margin loan
├─ Cash reserves          ├─ Credit lines
├─ Real estate            ├─ Monthly expenses (recurring)
├─ Future dividends       ├─ Taxes (deferred)
└─ Labor income (opt.)    └─ Inflation (hidden)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
              NET WORTH = Δ

Most people track the left side. This series tracks both.

The Schema

We start with SQLite — no servers, no dependencies, one file forever. This is the database that every subsequent article in this series will read from.

# alm_schema.py
import sqlite3
from datetime import date

DB_PATH = "life_alm.db"

SCHEMA = """
-- ASSETS: What you own
CREATE TABLE IF NOT EXISTS holdings (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker      TEXT NOT NULL,
    name        TEXT,
    shares      INTEGER NOT NULL,
    cost_basis  REAL,              -- total cost, not per-share
    asset_class TEXT DEFAULT 'equity',
    added_at    TEXT DEFAULT (date('now'))
);

-- Market prices (daily snapshot)
CREATE TABLE IF NOT EXISTS prices (
    ticker      TEXT NOT NULL,
    price_date  TEXT NOT NULL,
    close_price REAL NOT NULL,
    PRIMARY KEY (ticker, price_date)
);

-- Dividend history and projections
CREATE TABLE IF NOT EXISTS dividends (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker      TEXT NOT NULL,
    ex_date     TEXT,
    pay_date    TEXT,
    amount_per_share REAL NOT NULL,
    is_projection    INTEGER DEFAULT 0  -- 1 = forecast, 0 = actual
);

-- LIABILITIES: What you owe
CREATE TABLE IF NOT EXISTS loans (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT NOT NULL,       -- 'securities_margin', 'credit_line', etc.
    balance     REAL NOT NULL,
    rate        REAL NOT NULL,       -- annual interest rate (0.02 = 2%)
    collateral_type TEXT,            -- 'portfolio', 'real_estate', NULL (unsecured)
    margin_call_pct  REAL,           -- ratio that triggers margin call (e.g., 0.70)
    forced_liq_pct   REAL,           -- ratio that triggers forced liquidation (e.g., 0.85)
    freeze_pct       REAL,           -- ratio that freezes new borrowing (e.g., 0.60)
    updated_at  TEXT DEFAULT (date('now'))
);

-- BURN RATE: Recurring liabilities
CREATE TABLE IF NOT EXISTS expenses (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    category    TEXT NOT NULL,       -- 'housing', 'food', 'insurance', 'tax', etc.
    monthly_amount REAL NOT NULL,
    is_fixed    INTEGER DEFAULT 1,   -- 1 = fixed, 0 = variable
    notes       TEXT
);

-- INCOME: Active earnings (optional/side-FIRE)
CREATE TABLE IF NOT EXISTS income (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    source      TEXT NOT NULL,       -- 'ai_consulting', 'spouse', etc.
    monthly_amount REAL NOT NULL,
    is_guaranteed  INTEGER DEFAULT 0,
    notes       TEXT
);

-- SNAPSHOTS: Daily balance sheet
CREATE TABLE IF NOT EXISTS snapshots (
    snapshot_date TEXT PRIMARY KEY,
    total_assets  REAL,
    total_liabilities REAL,
    net_worth     REAL,
    margin_ratio  REAL,              -- total_loan_balance / portfolio_value
    monthly_cashflow REAL,
    fire_ratio    REAL               -- passive_income / expenses
);
"""

def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.executescript(SCHEMA)
    conn.close()
    print(f"Database initialized: {DB_PATH}")

if __name__ == "__main__":
    init_db()

Run it once. You now have a financial database.

$ python alm_schema.py
Database initialized: life_alm.db

Loading Your Data

Here's a loader that populates the database with your actual positions. Replace the numbers with your own.

# load_portfolio.py
import sqlite3
from alm_schema import DB_PATH

def load_sample_data():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()

    # === HOLDINGS ===
    holdings = [
        ("2674", "Hard Off Corp",    15000, 22_500_000, "equity"),
        ("8291", "Nissan Tokyo HD",  50000, 20_000_000, "equity"),
        ("5869", "Waseda Gakken",    20000, 18_000_000, "equity"),
        ("2411", "Gendai Agency",    50000, 15_000_000, "equity"),
        ("7201", "Nissan Motor",     50000, 12_000_000, "equity"),
    ]
    c.executemany(
        "INSERT INTO holdings (ticker, name, shares, cost_basis, asset_class) VALUES (?,?,?,?,?)",
        holdings
    )

    # === CURRENT PRICES ===
    prices = [
        ("2674", "2026-04-08", 2057.0),
        ("8291", "2026-04-08", 515.0),
        ("5869", "2026-04-08", 1326.0),
        ("2411", "2026-04-08", 475.0),
        ("7201", "2026-04-08", 365.2),
    ]
    c.executemany(
        "INSERT INTO prices (ticker, price_date, close_price) VALUES (?,?,?)",
        prices
    )

    # === LOANS ===
    loans = [
        ("securities_margin", 50_000_000, 0.02, "portfolio", 0.70, 0.85, 0.60),
        ("consumer_credit",    8_000_000, 0.02, None,        None, None, None),
    ]
    c.executemany(
        """INSERT INTO loans (name, balance, rate, collateral_type,
           margin_call_pct, forced_liq_pct, freeze_pct) VALUES (?,?,?,?,?,?,?)""",
        loans
    )

    # === MONTHLY EXPENSES ===
    expenses = [
        ("housing",    30_000, 1, "Mortgage paid off — maintenance only"),
        ("food",      100_000, 0, None),
        ("insurance",  50_000, 1, None),
        ("utilities",  30_000, 1, None),
        ("transport",  40_000, 0, None),
        ("education", 100_000, 1, "Children"),
        ("misc",      150_000, 0, None),
        ("tax_social", 300_000, 1, "Income tax + social insurance"),
    ]
    c.executemany(
        "INSERT INTO expenses (category, monthly_amount, is_fixed, notes) VALUES (?,?,?,?)",
        expenses
    )

    # === INCOME ===
    income = [
        ("ai_consulting", 1_000_000, 0, "Side-FIRE: work by choice"),
        ("spouse",          200_000, 1, None),
    ]
    c.executemany(
        "INSERT INTO income (source, monthly_amount, is_guaranteed, notes) VALUES (?,?,?,?)",
        income
    )

    conn.commit()
    conn.close()
    print("Portfolio data loaded.")

if __name__ == "__main__":
    load_sample_data()

The Balance Sheet Query

Now the payoff — a single query that gives you your complete financial picture:

# balance_sheet.py
import sqlite3
from alm_schema import DB_PATH

def get_balance_sheet(price_date="2026-04-08"):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()

    # Total portfolio value
    c.execute("""
        SELECT SUM(h.shares * p.close_price)
        FROM holdings h
        JOIN prices p ON h.ticker = p.ticker
        WHERE p.price_date = ?
    """, (price_date,))
    portfolio_value = c.fetchone()[0] or 0

    # Cash reserves (simplified — you'd track this separately)
    cash = 4_500_000  # or query from a cash table

    # Total assets
    total_assets = portfolio_value + cash

    # Total loan balances
    c.execute("SELECT SUM(balance) FROM loans")
    total_loans = c.fetchone()[0] or 0

    # Monthly expenses
    c.execute("SELECT SUM(monthly_amount) FROM expenses")
    monthly_expenses = c.fetchone()[0] or 0

    # Monthly income
    c.execute("SELECT SUM(monthly_amount) FROM income")
    monthly_income = c.fetchone()[0] or 0

    # Annual dividends (projected)
    c.execute("""
        SELECT SUM(d.amount_per_share * h.shares)
        FROM dividends d
        JOIN holdings h ON d.ticker = h.ticker
        WHERE d.is_projection = 1
    """)
    annual_dividends = c.fetchone()[0] or 0

    # Derived metrics
    net_worth = total_assets - total_loans
    margin_ratio = total_loans / portfolio_value if portfolio_value > 0 else float('inf')
    monthly_cashflow = monthly_income - monthly_expenses
    monthly_passive = annual_dividends / 12
    fire_ratio = (annual_dividends + 0) / (monthly_expenses * 12) if monthly_expenses > 0 else 0

    # Margin loan specific
    c.execute("""
        SELECT balance, freeze_pct, margin_call_pct, forced_liq_pct
        FROM loans WHERE collateral_type = 'portfolio'
    """)
    margin_loan = c.fetchone()

    conn.close()

    report = f"""
╔══════════════════════════════════════════════════╗
║           PERSONAL BALANCE SHEET                 ║
║           {price_date}                           ║
╠══════════════════════════════════════════════════╣
║ ASSETS                                           ║
║   Portfolio          ¥{portfolio_value:>14,.0f}   ║
║   Cash               ¥{cash:>14,.0f}             ║
║   Total Assets       ¥{total_assets:>14,.0f}     ║
╠══════════════════════════════════════════════════╣
║ LIABILITIES                                      ║
║   Total Loans        ¥{total_loans:>14,.0f}      ║
║   Monthly Burn       ¥{monthly_expenses:>14,.0f}  ║
╠══════════════════════════════════════════════════╣
║ NET WORTH            ¥{net_worth:>14,.0f}         ║
╠══════════════════════════════════════════════════╣
║ KEY RATIOS                                       ║
║   Margin Ratio         {margin_ratio:>8.1%}       ║
║   Monthly Cashflow   ¥{monthly_cashflow:>14,.0f}  ║
║   FIRE Ratio           {fire_ratio:>8.1%}         ║
╚══════════════════════════════════════════════════╝
"""
    print(report)

    # Margin safety analysis
    if margin_loan:
        bal, freeze, call, liq = margin_loan
        print("MARGIN SAFETY ANALYSIS")
        print("─" * 50)
        for label, threshold in [("Freeze", freeze), ("Margin Call", call), ("Forced Liq", liq)]:
            if threshold:
                trigger_value = bal / threshold
                drop_pct = (1 - trigger_value / portfolio_value) * 100
                print(f"  {label:.<20} ratio>{threshold:.0%}  triggers at ¥{trigger_value:,.0f} ({drop_pct:+.1f}%)")

if __name__ == "__main__":
    get_balance_sheet()

Running this prints:

MARGIN SAFETY ANALYSIS
──────────────────────────────────────────────────
  Freeze.............. ratio>60%  triggers at ¥83,333,333 (-33.4%)
  Margin Call......... ratio>70%  triggers at ¥71,428,571 (-42.9%)
  Forced Liq.......... ratio>85%  triggers at ¥58,823,529 (-53.0%)

One command. Complete picture. Every morning.

The Snapshot Recorder

Automate daily snapshots with cron. This builds the historical record that powers the trend analysis in later articles.

# snapshot.py
import sqlite3
from datetime import date
from alm_schema import DB_PATH

def record_snapshot():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()

    today = date.today().isoformat()

    # Get latest prices for each ticker
    c.execute("""
        SELECT SUM(h.shares * p.close_price)
        FROM holdings h
        JOIN prices p ON h.ticker = p.ticker
        WHERE p.price_date = (SELECT MAX(price_date) FROM prices)
    """)
    portfolio = c.fetchone()[0] or 0

    c.execute("SELECT SUM(balance) FROM loans")
    loans = c.fetchone()[0] or 0

    c.execute("SELECT SUM(monthly_amount) FROM income")
    income = c.fetchone()[0] or 0

    c.execute("SELECT SUM(monthly_amount) FROM expenses")
    expenses = c.fetchone()[0] or 0

    net_worth = portfolio - loans
    margin_ratio = loans / portfolio if portfolio > 0 else 0
    cashflow = income - expenses

    c.execute("""
        INSERT OR REPLACE INTO snapshots
        (snapshot_date, total_assets, total_liabilities, net_worth,
         margin_ratio, monthly_cashflow)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (today, portfolio, loans, net_worth, margin_ratio, cashflow))

    conn.commit()
    conn.close()
    print(f"Snapshot recorded: {today} | NW: ¥{net_worth:,.0f} | Margin: {margin_ratio:.1%}")

if __name__ == "__main__":
    record_snapshot()

Add to cron:

# crontab -e
30 16 * * 1-5 cd /home/user/alm && python snapshot.py

Market closes at 15:00 JST. Snapshot at 16:30 after data settles.

Why SQLite?

You might wonder why not PostgreSQL, or a spreadsheet, or a SaaS tool.

SQLite is the only database that will still work in 20 years without maintenance. It's a single file. No server process. No authentication. No network dependency. Backup is cp. Migration is scp.

For a system that manages your life's finances, that property — zero operational burden — is not a nice-to-have. It's a requirement.

Your FIRE system should be the most boring piece of infrastructure you own. Boring means reliable. Reliable means you trust it. Trust means you act on it.