LockInBroAPI/alembic/versions/001_initial_schema.py

"""Initial schema

Revision ID: 001
Revises:
Create Date: 2026-03-28
"""

from alembic import op

revision = "001"
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS public.users (
        id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        email           TEXT UNIQUE,
        password_hash   TEXT,
        apple_user_id   TEXT UNIQUE,
        display_name    TEXT,
        timezone        TEXT DEFAULT 'America/Chicago',
        distraction_apps TEXT[] DEFAULT '{}',
        preferences     JSONB DEFAULT '{}',
        created_at      TIMESTAMPTZ DEFAULT now(),
        updated_at      TIMESTAMPTZ DEFAULT now(),
        CONSTRAINT auth_method CHECK (email IS NOT NULL OR apple_user_id IS NOT NULL)
    );

    CREATE TABLE IF NOT EXISTS public.tasks (
        id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id         UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
        title           TEXT NOT NULL,
        description     TEXT,
        priority        INT DEFAULT 0,
        status          TEXT DEFAULT 'pending',
        deadline        TIMESTAMPTZ,
        estimated_minutes INT,
        source          TEXT DEFAULT 'manual',
        tags            TEXT[] DEFAULT '{}',
        plan_type       TEXT,
        brain_dump_raw  TEXT,
        created_at      TIMESTAMPTZ DEFAULT now(),
        updated_at      TIMESTAMPTZ DEFAULT now()
    );

    CREATE TABLE IF NOT EXISTS public.steps (
        id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        task_id         UUID NOT NULL REFERENCES public.tasks(id) ON DELETE CASCADE,
        sort_order      INT NOT NULL,
        title           TEXT NOT NULL,
        description     TEXT,
        estimated_minutes INT,
        status          TEXT DEFAULT 'pending',
        checkpoint_note TEXT,
        last_checked_at TIMESTAMPTZ,
        completed_at    TIMESTAMPTZ,
        created_at      TIMESTAMPTZ DEFAULT now()
    );

    CREATE TABLE IF NOT EXISTS public.sessions (
        id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id         UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
        task_id         UUID REFERENCES public.tasks(id) ON DELETE SET NULL,
        started_at      TIMESTAMPTZ DEFAULT now(),
        ended_at        TIMESTAMPTZ,
        status          TEXT DEFAULT 'active',
        checkpoint      JSONB DEFAULT '{}',
        created_at      TIMESTAMPTZ DEFAULT now()
    );

    CREATE TABLE IF NOT EXISTS public.distractions (
        id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id         UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
        session_id      UUID REFERENCES public.sessions(id) ON DELETE SET NULL,
        detected_at     TIMESTAMPTZ DEFAULT now(),
        distraction_type TEXT,
        app_name        TEXT,
        duration_seconds INT,
        confidence      FLOAT,
        vlm_summary     TEXT,
        nudge_shown     BOOLEAN DEFAULT false
    );

    CREATE TABLE IF NOT EXISTS public.distraction_patterns (
        id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id         UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
        pattern_type    TEXT,
        description     TEXT,
        frequency       INT DEFAULT 1,
        last_seen       TIMESTAMPTZ DEFAULT now(),
        metadata        JSONB DEFAULT '{}'
    );

    CREATE INDEX IF NOT EXISTS idx_tasks_user ON tasks(user_id, status);
    CREATE INDEX IF NOT EXISTS idx_steps_task ON steps(task_id, sort_order);
    CREATE INDEX IF NOT EXISTS idx_steps_status ON steps(task_id, status);
    CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id, started_at DESC);
    CREATE INDEX IF NOT EXISTS idx_sessions_active ON sessions(user_id, status) WHERE status = 'active';
    CREATE INDEX IF NOT EXISTS idx_distractions_user ON distractions(user_id, detected_at DESC);
    CREATE INDEX IF NOT EXISTS idx_distractions_app ON distractions(user_id, app_name, detected_at DESC);
    CREATE INDEX IF NOT EXISTS idx_distractions_hourly ON distractions(user_id, EXTRACT(HOUR FROM detected_at AT TIME ZONE 'UTC'));
    """)


def downgrade():
    op.execute("""
    DROP TABLE IF EXISTS public.distraction_patterns CASCADE;
    DROP TABLE IF EXISTS public.distractions CASCADE;
    DROP TABLE IF EXISTS public.sessions CASCADE;
    DROP TABLE IF EXISTS public.steps CASCADE;
    DROP TABLE IF EXISTS public.tasks CASCADE;
    DROP TABLE IF EXISTS public.users CASCADE;
    """)