Data Model¶
Database¶
SQLite via sql.js (pure JavaScript, no native compilation needed). Stored at accounting/data/accounting.db. Auto-created on first run. Auto-saved after every write (500ms debounce).
Entity Relationship¶
accounts ──< transactions
assets ──< depreciation_entries
subscriptions (standalone, linked to accounts)
Tables¶
accounts¶
The chart of accounts. Every financial bucket is an account.
| Column | Type | Description |
|---|---|---|
| id | INTEGER | Primary key |
| name | TEXT | Account name |
| type | TEXT | asset, liability, equity, revenue, expense |
| category | TEXT | Sub-category (e.g., cash, fixed_asset, prop_firm) |
| is_active | INTEGER | 1=active, 0=soft deleted |
| created_at | TEXT | Timestamp |
transactions¶
Double-entry style. Every transaction has a debit and credit account.
| Column | Type | Description |
|---|---|---|
| id | INTEGER | Primary key |
| date | TEXT | Transaction date (YYYY-MM-DD) |
| description | TEXT | What happened |
| amount | REAL | Always positive |
| debit_account_id | INTEGER | FK → accounts (receives value) |
| credit_account_id | INTEGER | FK → accounts (gives value) |
| category | TEXT | Optional category tag |
| tags | TEXT | Optional tags |
| subscription_id | INTEGER | FK → subscriptions (if recurring) |
| asset_id | INTEGER | FK → assets (if asset-related) |
| created_at | TEXT | Timestamp |
assets¶
| Column | Type | Description |
|---|---|---|
| id | INTEGER | Primary key |
| name | TEXT | What it is |
| category | TEXT | computer, monitor, furniture, software |
| purchase_date | TEXT | When acquired |
| purchase_price | REAL | Original cost |
| useful_life_years | INTEGER | Expected lifespan |
| depreciation_method | TEXT | straight_line or declining_balance |
| salvage_value | REAL | Value at end of life (default 0) |
| disposal_date | TEXT | When sold/disposed (null if held) |
| disposal_amount | REAL | Sale price |
| notes | TEXT | |
| created_at | TEXT | Timestamp |
depreciation_entries¶
| Column | Type | Description |
|---|---|---|
| id | INTEGER | Primary key |
| asset_id | INTEGER | FK → assets |
| period_start | TEXT | Start of period |
| period_end | TEXT | End of period |
| amount | REAL | Depreciation amount |
| accumulated | REAL | Running total |
| book_value | REAL | Purchase price - accumulated |
| transaction_id | INTEGER | FK → transactions (journal entry) |
subscriptions¶
| Column | Type | Description |
|---|---|---|
| id | INTEGER | Primary key |
| name | TEXT | Service name |
| amount | REAL | Cost per period |
| frequency | TEXT | monthly or annual |
| category | TEXT | software, data, infra |
| start_date | TEXT | When it started |
| renewal_date | TEXT | Next renewal |
| cancel_date | TEXT | When cancelled (null if active) |
| status | TEXT | active, cancelled, trial |
| account_id | INTEGER | FK → accounts |
| notes | TEXT | |
| created_at | TEXT | Timestamp |
Pre-seeded Accounts (27)¶
Assets (7)¶
Current Account, Savings Account, Broker Cash, Accounts Receivable, Computer Equipment, Office Equipment, Software Assets
Liabilities (5)¶
Credit Card, Accounts Payable, Tax Payable, Loans, Investor Loans
Equity (4)¶
Owner's Capital, Investor Capital, Retained Earnings, Owner's Draws
Revenue (3)¶
Trading Income, Service Income, Other Income
Expenses (9)¶
Software & Subscriptions, Hardware & Equipment, Data & Market Feeds, Education & Training, Infrastructure, Professional Services, Prop Firm Challenges, Depreciation Expense, Other Expenses
Computed Values (Not Stored)¶
| Value | Formula |
|---|---|
| Net worth | Sum(asset balances) - Sum(liability balances) |
| Monthly burn | Sum(active subscriptions monthly equivalent) |
| Asset book value | Purchase price - accumulated depreciation |
| Monthly P&L | Sum(revenue) - Sum(expenses) for period |
| Subscription monthly equiv. | Annual: amount/12, Monthly: amount |
Migrations¶
New accounts are added via migration block in db.js. If the DB already exists and is missing newer accounts, they're inserted automatically on startup.