![[01] Building a Personal ALM System — Your Life as a Database Schema](https://media2.dev.to/dynamic/image/width=1200,height=627,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2Frjjjzyf2bca766kqz3u0.png)
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.
What We Built
- A 7-table SQLite schema covering assets, liabilities, expenses, and income
- A data loader for real portfolio positions
- A balance sheet generator with margin safety analysis
- A daily snapshot recorder for cron automation
This database is the foundation. Every article that follows reads from it.
DE
Source
This article was originally published by DEV Community and written by soy.
Read original article on DEV Community