Personal Data Warehouse

Append-only ETL pipeline that consolidates six personal data sources, Obsidian daily-note tasks plus five Google Sheets (morning check-in form, workouts, LeetCode practice, purchases, job search), into a PostgreSQL warehouse, with a dashboard on top.

Architecture

• Sync (sync.py + pdw/): Python, no framework. Reads daily notes from disk and sheets via the Google Sheets REST API (OAuth refresh-token flow), upserts into Postgres using per-source natural keys so re-runs are idempotent.
• Postgres runs in Docker on a separate home server; the sync opens an SSH tunnel rather than exposing 5432 on the LAN.
• Scheduling: invoked nightly at 3am by an agent cron scheduler (no-agent script entry) with a Telegram alert on failure.
• Dashboard (dashboard/): read-only views over the warehouse.

Design docs

The repo keeps the planning artifacts the build was driven from: PRODUCT_BRIEF.md, ARCHITECTURE.md, BUILD_PLAN.md (tasks with executable Verify: commands), TEST_PLAN.md, USER_STORIES.md. The build followed an architect/contractor workflow: a frontier model wrote the plan, a local model executed tasks, each verified by shell command.

Setup

python3 -m venv .venv && .venv/bin/pip install psycopg2-binary
cp .env.example .env   # fill in DB credentials and sheet IDs
.venv/bin/python sync.py --source all

Google auth expects an OAuth token JSON (with spreadsheets scope) — path configured in pdw/config.py.