LifeOS Data Backend: Findings + Plan, 2026-06-15¶

Investigation into where the "true" data lives for tasks, habits, inbox, and project management, and whether Notion-as-system-of-record is the right call or a more robust backend (SQLite / Postgres) is warranted.

Findings: the current state¶

Three data domains, two patterns. Productivity is the outlier.

Domain	System of record	Forge-owned spine	Notes
Personal finance	`data/finance/finance.db`	Yes (SQLite)	8,575 txns, 62 accounts, sync timers + dashboard
Commerce (all brands)	`data/datalayer/datalayer.db`	Yes (SQLite)	5,677 customers, 4,690 orders, Shopify/ERPNext connectors
Tasks / habits / inbox / projects	Notion only	None	The one domain still wholly SaaS-owned

The local files that touch productivity (data/telegram_checklists/, data/time_blocks_db.json, data/habits_callback_ledger.jsonl) are transient UI/cache state, not a store of record. Notion is the truth, full stop. Writes go through forge_notion_api.py, forge_telegram_checklist.py, forge_habits_*.py, and the bot brain.

Why Notion-as-truth is fine for intent, risky as the only copy¶

Notion should stay the human write surface. The doctrine routing test (tap-on-phone-to-think to Notion) puts tasks/habits/project notes squarely in Notion's lane: mobile-first, formula-driven (habits "Hit goal" is a Notion formula), low-friction capture. Replacing the Notion UI with a raw DB would be a downgrade.

But Notion as the sole copy violates two hard rules at once (single-source-of-truth-you-own, robust-over-quick), and the scars are already logged in memory: - Brain property contract (reference_brain_property_contract.md): rename a Notion property and Python writes fail silently. - Free plan, no SLA, API rate limits, no Notion AI, plus the calc-render and view-render bugs already hit. - Not greppable, no joins, no local backup. If the API hiccups or the account locks, automation and /recall go blind with no mirror to fall back on.

Recommendation: mirror, don't migrate¶

Keep Notion as the write surface for intent. Add the missing spine as a Notion -> SQLite one-way mirror, same shape as finance and datalayer:

forge_lifeos_db.py: schema init + upsert helpers (mirrors forge_finance_db / forge_datalayer_db). Tables: tasks, projects, habits, habit_log, inbox, keyed on Notion page id.
forge_lifeos_notion_sync.py: idempotent Notion -> SQLite upsert on a systemd timer, fail-loud notify on sync failure.
Consumers (automation, dashboards, coordinator bot, /recall) query data/lifeos/lifeos.db locally instead of hammering the Notion API.

What it buys: durable backup, fast SQL/joins, offline AI query, and it kills the silent-write-fail blast radius (mirror reads are read-only, cannot corrupt Notion).

Go to forge-as-SoR with Notion as a projected view (two-way sync) only if Notion friction actually bites at scale: rate limits throttling the bots, or outgrowing the Free plan. Two-way sync is a real project; do not pay for it until the one-way mirror proves it is needed.

SQLite vs Postgres: the engine decision¶

Postgres is the right eventual engine for the commerce spine under concurrency, but the wrong default for the LifeOS mirror. Decision logic for this fleet:

Signal	Stay SQLite	Move to Postgres
Writers	One process at a time	Many concurrent writers
Access	Local file on Console	Network clients, multiple boxes
Data shape	Rows, simple joins	Concurrent ingest, heavy analytics, JSONB, full-text, pgvector
Ops cost	Zero (a file)	A service to run, back up, patch, secure

LifeOS mirror -> SQLite. Single writer, read-mostly, one machine. Postgres there is pure overhead.
finance.db -> stays SQLite forever. Never multi-writer, never leaves Console.
datalayer.db -> SQLite now, pre-planned Postgres cut. Promote this one DB to Postgres the moment a concurrent reader (the email engine) reads while sync timers write. SQLite serializes writers; under real concurrency that surfaces as database is locked. The data-layer plan itself names Postgres only as an upgrade path "if concurrency/scale demands it"; it does not yet.

The pattern: SQLite until concurrency or multi-host forces your hand, then promote the specific DB that needs it. Fleet-wide Postgres "to be robust" is cargo-culting: one more service to secure and back up against the robust-not-heavy doctrine.

datalayer Postgres trigger conditions (when, not yet)¶

Promote datalayer to Postgres when ANY of these fire: 1. The email engine + dashboard + bot tools hit the DB concurrently with sync writes (database is locked errors). 2. You want it queryable from multiple boxes (dashboard LXC, bots on Console, ERPNext), not one file. SQLite over a network share is a footgun. 3. You want pgvector (recall/segment search in the same engine) or JSONB for messy Shopify payloads.

datalayer Postgres migration steps (when triggered)¶

Stand up postgres:17 in a dedicated LXC (forge-db) behind Tailscale / CF Access, never internet-exposed. Tokens/creds in ~/.forge-secrets/.
Keep the canonical schema identical so connectors barely change (swap the DB adapter in forge_datalayer_db.py, keep upsert keys).
Dump-and-load existing rows; verify counts match (customers, orders, order_lines).
Repoint connectors + query lib + bot tools at the Postgres DSN. Run both in parallel for one sync cycle, diff, then cut over.
Add nightly pg_dump to the backup set.

Next steps¶

(If approved) run /feature-plan to produce the numbered, file-path-exact build spec for the SQLite LifeOS mirror (forge_lifeos_db.py + forge_lifeos_notion_sync.py + timer).
Leave datalayer on SQLite; revisit Postgres only when a trigger condition above fires (the email-engine build is the likely catalyst).
finance.db: no change.

Relation to other plans¶

forge-data-layer-2026-06-09: the commerce spine this references for the SQLite-then-Postgres path.
consolidation-roadmap-2026-06-08: the email engine is the consumer that triggers the datalayer Postgres cut.
Doctrine: single-source-of-truth-you-own + robust-over-quick (FORGE-DOCTRINE 9.5); Forge-vs-Notion routing test (feedback_forge_vs_notion_separation.md).

[Claude Code, 2026-06-15]