**Life OS v2** Data Migration Plan Old Schema to New Schema Mapping + New Database DDL -------------------- -------------------------------------------------- Document Version 1.0 Date February 2026 Old System Supabase (PostgreSQL) on Render New System Self-hosted PostgreSQL on Hetzner VM (defiant-01) Old Schema Tables 11 New Schema Tables \~50 -------------------- -------------------------------------------------- **1. Migration Overview** This document defines the data migration from Life OS v1 (Supabase/Render) to Life OS v2 (self-hosted PostgreSQL on Hetzner). The v1 schema and data remain untouched on Supabase for reference. The v2 schema is a completely separate database with new tables, new conventions, and expanded capabilities. **Strategy:** Export v1 data via pg_dump, transform using a Python migration script, import into the v2 database. V1 remains read-only as a reference. No shared database, no incremental sync. **Key principle:** The new schema is NOT an evolution of the old schema. It is a redesign. Some tables map 1:1 (domains, areas). Others split, merge, or gain significant new columns. Some v2 tables have no v1 equivalent at all. **2. Old Schema (R0 State)** The v1 system has 11 tables. All PKs are UUID via gen_random_uuid(). Timestamps are TIMESTAMPTZ. --------------- ---------- --------------------------------------------- **Table** **Row **Purpose** Est.** domains 3-5 Top-level life categories (Work, Personal, Sintri) areas 5-10 Optional grouping within a domain projects 10-20 Unit of work within domain/area tasks 50-200 Atomic actions with priority, status, context notes 10-50 Markdown documents attached to project/domain links 10-30 Named URL references files 5-20 Binary files in Supabase Storage with metadata daily_focus 30-100 Date-scoped task commitment list capture 10-50 Raw text capture queue context_types 6 GTD execution mode lookup (deep_work, quick, etc.) reminders 0 Schema exists but no UI or delivery built --------------- ---------- --------------------------------------------- **3. Table-by-Table Migration Mapping** Each v1 table is mapped to its v2 equivalent(s) with column-level transformations noted. Universal columns added to all v2 tables: updated_at, is_active (BOOLEAN DEFAULT true), sort_order (INT DEFAULT 0). **3.1 domains -\> domains** **Mapping:** Direct 1:1. Preserve UUIDs. ----------------- ----------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy Preserve UUID, all FKs depend on it name name Copy color color Copy created_at created_at Copy (none) updated_at Generate Set to created_at for initial import (none) is_active Default true (none) sort_order Generate Assign sequential 10, 20, 30\... (none) description Default NULL - new optional field (none) icon Default NULL - new optional field ----------------- ----------------- ------------ --------------------------- **3.2 areas -\> areas** **Mapping:** Direct 1:1. Preserve UUIDs. ----------------- ----------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy Preserve UUID domain_id domain_id Copy FK preserved name name Copy description description Copy created_at created_at Copy (none) updated_at Generate Set to created_at (none) is_active Default true (none) sort_order Generate Sequential per domain (none) icon Default NULL (none) color Default NULL - inherit from domain or set later ----------------- ----------------- ------------ --------------------------- **3.3 projects -\> projects** **Mapping:** Direct 1:1 with new columns. Preserve UUIDs. ----------------- ----------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy Preserve UUID domain_id domain_id Copy area_id area_id Copy Nullable preserved name name Copy description description Copy status status Map v1 \'archived\' -\> v2 \'archived\' (kept as-is) due_date target_date Rename Column rename only, same DATE type created_at created_at Copy updated_at updated_at Copy (none) start_date Default NULL (none) priority Default 3 (normal) (none) is_active Default true (none) sort_order Generate Sequential per area/domain (none) color Default NULL (none) release_id Default NULL - no releases in v1 ----------------- ----------------- ------------ --------------------------- **3.4 tasks -\> tasks** **Mapping:** Direct 1:1 with significant new columns. Preserve UUIDs. This is the most data-rich migration. ------------------- ------------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy Preserve UUID - many FKs depend on this domain_id domain_id Copy project_id project_id Copy Nullable preserved parent_id parent_id Copy Self-ref FK for subtasks title title Copy description description Copy priority priority Copy 1-4 scale preserved status status Copy Same enum values due_date due_date Copy deadline deadline Copy recurrence recurrence Copy tags tags Copy TEXT\[\] preserved context context Copy is_custom_context is_custom_context Copy created_at created_at Copy updated_at updated_at Copy completed_at completed_at Copy (none) assigned_to Default NULL - FK to contacts (none) estimated_minutes Default NULL (none) actual_minutes Default NULL (none) energy_level Default NULL (low/medium/high) (none) is_active Default true (none) sort_order Generate Sequential per project (none) template_id Default NULL ------------------- ------------------- ------------ --------------------------- **3.5 notes -\> notes** **Mapping:** Direct 1:1. Preserve UUIDs. ----------------- ----------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy Preserve UUID domain_id domain_id Copy project_id project_id Copy task_id task_id Copy title title Copy body body Copy Markdown content preserved as-is content_format content_format Copy tags tags Copy created_at created_at Copy updated_at updated_at Copy (none) is_pinned Default false (none) is_active Default true (none) sort_order Default 0 ----------------- ----------------- ------------ --------------------------- **3.6 links -\> bookmarks** **Mapping:** Renamed table. v2 expands links into a full bookmark/weblink directory. Preserve UUIDs. ----------------- ----------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy Preserve UUID domain_id domain_id Copy project_id project_id Copy task_id task_id Copy label label Copy url url Copy description description Copy created_at created_at Copy (none) updated_at Generate Set to created_at (none) folder_id Default NULL - bookmark folders are new in v2 (none) favicon_url Default NULL (none) is_active Default true (none) sort_order Default 0 (none) tags Default NULL - new in v2 ----------------- ----------------- ------------ --------------------------- **3.7 files -\> files** **Mapping:** 1:1 with storage path transformation. Files must be downloaded from Supabase Storage and re-uploaded to local disk on defiant-01. ------------------- ------------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy Preserve UUID domain_id domain_id Copy project_id project_id Copy task_id task_id Copy capture_id capture_id Copy filename filename Copy Internal UUID-prefixed name original_filename original_filename Copy storage_path storage_path Transform Rewrite from Supabase path to local path mime_type mime_type Copy size_bytes size_bytes Copy description description Copy tags tags Copy created_at created_at Copy updated_at updated_at Copy (none) note_id Default NULL - new FK in v2 (none) is_active Default true ------------------- ------------------- ------------ --------------------------- **File storage migration:** Use the Supabase Python client to iterate the life-os-files bucket, download each file, and save to /opt/lifeos/storage/files/ on defiant-01. Update storage_path values to reflect the new local path. **3.8 daily_focus -\> daily_focus** **Mapping:** Direct 1:1. Preserve UUIDs. ----------------- ----------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy focus_date focus_date Copy task_id task_id Copy slot slot Copy v2 removes the 3-item limit completed completed Copy note note Copy created_at created_at Copy (none) domain_id Derive Look up from task_id -\> tasks.domain_id ----------------- ----------------- ------------ --------------------------- **3.9 capture -\> capture** **Mapping:** 1:1 with enrichment for new capture context fields. ----------------- ----------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy raw_text raw_text Copy processed processed Copy task_id task_id Copy created_at created_at Copy (none) domain_id Default NULL - new optional context during capture (none) project_id Default NULL (none) source Default \'web\' - v2 tracks capture source (web/voice/telegram) (none) updated_at Generate Set to created_at ----------------- ----------------- ------------ --------------------------- **3.10 context_types -\> context_types** **Mapping:** Direct copy. Small reference table. ----------------- ----------------- ------------ --------------------------- **v1 Column** **v2 Column** **Action** **Notes** id id Copy v1 uses UUID, v2 keeps UUID for consistency value value Copy label label Copy is_system is_system Copy (none) is_active Default true (none) sort_order Default Sequential ----------------- ----------------- ------------ --------------------------- **3.11 reminders -\> reminders (redesigned)** **Mapping:** v1 reminders is task-only with 0 rows. v2 redesigns reminders as polymorphic (can remind about tasks, events, projects, or arbitrary items). Since v1 has no data, this is seed-only with no migration. v2 reminders table adds: entity_type (TEXT), entity_id (UUID), recurrence, snoozed_until, and removes the task_id-only FK in favor of polymorphic reference. **4. New Tables in v2 (No v1 Data)** These tables exist only in v2 and will be empty after migration. They are populated through normal application use. ------------------- ---------------------------------------------------- **Table** **Purpose** contacts People for task assignment and project management contact_groups Grouping contacts (team, family, etc.) lists Named checklists and note lists list_items Individual items within a list calendar_events Appointments, meetings, date-based items time_entries Time tracking records against tasks time_blocks Scheduled time blocks (Pomodoro, deep work) time_budgets Weekly/monthly time allocation targets releases Release/version grouping for projects milestones Project milestones with target dates task_dependencies Task-to-task dependency relationships task_templates Reusable task templates note_links Cross-references between notes and other entities bookmark_folders Hierarchical folder structure for bookmarks tags Normalized tag table (replaces TEXT\[\] arrays eventually) entity_tags Junction table for normalized tagging activity_log Audit trail of entity changes user_settings Application preferences and configuration saved_views Custom filtered/sorted views the user saves search_index Full-text search materialized view / helper ------------------- ---------------------------------------------------- **5. Migration Script Approach** **5.1 Prerequisites** 1\. pg_dump export of v1 Supabase database saved as life_os_v1_backup.sql 2\. v2 PostgreSQL database created on defiant-01 (lifeos_dev for testing, lifeos_prod for final) 3\. v2 schema DDL applied to the target database (see Section 6) 4\. Supabase Storage files downloaded to a local staging directory 5\. Python 3.11+ with psycopg2 and supabase client libraries **5.2 Script Structure** migrate_v1_to_v2.py 1\. Connect to v1 (read-only) and v2 (read-write) 2\. For each table in dependency order: a\. SELECT \* FROM v1 table b\. Transform each row per mapping rules above c\. INSERT INTO v2 table 3\. Download files from Supabase Storage 4\. Verify row counts match 5\. Run FK integrity checks on v2 Table order (respects FK dependencies): domains areas projects context_types tasks notes capture bookmarks (from links) files daily_focus **5.3 Transformation Rules Summary** For all tables with missing updated_at: set to created_at. For all tables with missing is_active: set to true. For all tables with missing sort_order: assign sequential values (10, 20, 30) within their parent scope. For projects.due_date: rename to target_date, no value change. For links -\> bookmarks: table rename, add updated_at = created_at. For files.storage_path: rewrite from Supabase bucket URL to local filesystem path. For daily_focus: derive domain_id by joining through task_id to tasks.domain_id. **5.4 Validation Checklist** After migration, verify: 1\. Row counts: v2 table row count \>= v1 for every mapped table 2\. UUID preservation: SELECT id FROM v2.domains EXCEPT SELECT id FROM v1.domains should be empty 3\. FK integrity: No orphaned foreign keys in v2 4\. File accessibility: Every file in v2.files table can be served from local storage 5\. Note content: Spot-check 5 notes for body content integrity 6\. Task hierarchy: Verify parent_id chains are intact **6. Platform Migration Summary** ----------------------- ----------------------- ------------------------ **Component** **v1 (Old)** **v2 (New)** Database Supabase (managed Self-hosted PostgreSQL PostgreSQL) on Hetzner Application Server Render (web service) Docker container on Hetzner VM Reverse Proxy Render (built-in) Nginx on defiant-01 File Storage Supabase Storage Local filesystem (S3-backed) (/opt/lifeos/storage/) Data Access Layer supabase Python client SQLAlchemy + psycopg2 (REST) (direct SQL) Templating Jinja2 Jinja2 (unchanged) Backend Framework FastAPI FastAPI (unchanged) Frontend Vanilla HTML/CSS/JS Vanilla HTML/CSS/JS (redesigned UI) Dev/Prod Separation Separate Supabase Docker Compose with projects dev/prod configs Backups Manual pg_dump Automated cron pg_dump to /opt/lifeos/backups/ Domain/SSL \*.onrender.com lifeos.invixiom.com with Let\'s Encrypt ----------------------- ----------------------- ------------------------ **7. Data Access Layer Migration** Every Supabase client call in the v1 routers must be replaced. The pattern is consistent: \# v1 (Supabase REST client) data = supabase.table(\'tasks\').select(\'\*\').eq(\'project_id\', pid).execute() rows = data.data \# v2 (SQLAlchemy / raw SQL) rows = db.execute( text(\'SELECT \* FROM tasks WHERE project_id = :pid\'), {\'pid\': pid} ).fetchall() This transformation applies to every router file. The Jinja2 templates remain unchanged because they consume the same data shape (list of dicts). The migration is purely at the data access layer. **8. Rollback Plan** v1 on Supabase/Render remains untouched and running throughout the migration. If v2 has issues: 1\. Point DNS back to Render (or simply use the .onrender.com URL directly) 2\. v1 database on Supabase is read-only but intact - no data was deleted 3\. Any data created in v2 after migration would need manual reconciliation if rolling back Recommended approach: run v1 and v2 in parallel for 1-2 weeks. Cut over to v2 only after confirming data integrity and feature parity on the critical path (tasks, focus, notes, capture). Life OS v2 Migration Plan // Generated February 2026