06 Database

06. Database Architecture & Supabase Migrations

Specifications for the PostgreSQL relational database schema and Supabase backend.

---

🗄️ Database Architecture Overview

PACT OS relies on PostgreSQL 15+ hosted on Supabase:

• Primary Key Standard: All tables use uuid primary keys (gen_random_uuid()).
• Foreign Key Cascades: User data cascades on auth.users delete (ON DELETE CASCADE).
• Timestamps: Standardized created_at and updated_at timestamps using PostgreSQL TIMESTAMPTZ (UTC).
• Row Level Security (RLS): Enforced on 100% of tables ensuring multi-tenant isolation.

---

📜 Complete Migration Index (26 Migrations)

The database schema is defined across 26 frozen SQL files in supabase/migrations/:

#	Migration File	Description
1	20260906000000_create_profiles_table.sql	User profiles table & auth sync trigger
2	20260907000000_create_core_domain_tables.sql	Core goals, projects, and tasks tables
3	20260907010000_task_lifecycle_engine.sql	Status constraint triggers & state transitions
4	20260907020000_accountability_foundation.sql	commitments & consequences schema
5	20260907030000_commitment_assignment_engine.sql	Task-to-commitment binding functions
6	20260907040000_consequence_activation_engine.sql	Consequence payload masking & activation triggers
7	20260908000000_accountability_resolution_engine.sql	Resolution status transitions & verification
8	20260908010000_accountability_hardening_and_edge_cases.sql	Edge case validation & constraint checks
9	20260909000000_create_calendar_events.sql	Time-blocking calendar_events table
10	20260910000000_create_finance_tables.sql	Finance accounts, categories, transactions, budgets
11	20260911000000_deadline_sweeper_engine.sql	Deadline sweeper RPC functions for cron jobs
12	20260911010000_create_notifications_table.sql	Multi-channel notifications queue
13	20260911020000_google_calendar_sync.sql	Google Calendar external sync mapping
14	20260911030000_external_proof_of_work.sql	external_proof_submissions schema
15	20260911040000_financial_discipline.sql	Recurring finance transactions & budget checks
16	20260911050000_user_onboarding.sql	user_onboarding_state tracking table
17	20260911060000_production_hardening_and_cron_schedule.sql	Production index tuning & pg_cron schedule
18	20260911070000_focus_sessions_engine.sql	focus_sessions deep work session ledger
19	20260911080000_habits_and_routines_engine.sql	habits, habit_logs, daily_routines tables
20	20260911090000_weekly_reviews_engine.sql	weekly_reviews reflection storage
21	20260912000000_passkey_credentials.sql	WebAuthn passkey_credentials table
22	20260913000000_accountability_circles_and_pledges.sql	Social circles & charity pledge records
23	20260914000000_production_resilience_and_telemetry.sql	Performance logging & telemetry tables
24	20260915000000_daily_rituals_and_account_governance.sql	Daily ritual settings & governance rules
25	20260916000000_multi_device_sync_and_replication.sql	Offline sync delta log (sync_deltas)
26	20260917000000_discipline_intelligence_and_enterprise_sso.sql	Discipline scoring views & SSO mapping

---

🔒 Row Level Security (RLS) Policy Pattern

Every user table enforces strict user isolation:

-- Enable RLS
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

-- Select Policy
CREATE POLICY "Users can view own tasks"
  ON public.tasks FOR SELECT
  USING (auth.uid() = user_id);

-- Insert Policy
CREATE POLICY "Users can insert own tasks"
  ON public.tasks FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Update Policy
CREATE POLICY "Users can update own tasks"
  ON public.tasks FOR UPDATE
  USING (auth.uid() = user_id);

-- Delete Policy
CREATE POLICY "Users can delete own tasks"
  ON public.tasks FOR DELETE
  USING (auth.uid() = user_id);

---

⚡ Protected Migration Policy

All 26 SQL migration files are FROZEN. New schema requirements must be added as incremental, timestamped migration files without mutating existing historical migrations.