npi-2026/db/init/001_schema.sql

-- NPI Product Tracker Database Schema
-- All tables for tracking product lifecycle through NPI gates

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Product category enum
CREATE TYPE category AS ENUM ('I', 'IB', 'II', 'III');

-- Project status enum
CREATE TYPE project_status AS ENUM ('active', 'on_hold', 'completed', 'cancelled');

-- Gate status enum
CREATE TYPE gate_status AS ENUM ('not_started', 'in_progress', 'blocked', 'passed', 'skipped', 'failed');

-- RACI type enum
CREATE TYPE raci_type AS ENUM ('R', 'A', 'C', 'I');

-- Update type enum
CREATE TYPE update_type AS ENUM ('progress', 'blocker', 'decision', 'note');

-- PPPP status enum
CREATE TYPE pppp_status AS ENUM ('draft', 'in_review', 'approved', 'final');

-- Notification type enum
CREATE TYPE notification_type AS ENUM (
    'overdue_update', 'gate_review', 'weekly_digest',
    'assignment', 'gate_passed', 'blocker'
);

-- ============================================================
-- REFERENCE TABLES (seeded from Excel, rarely changed)
-- ============================================================

-- 13 gate templates (G0-G12)
CREATE TABLE gates (
    id SERIAL PRIMARY KEY,
    gate_number INTEGER NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    sort_order INTEGER NOT NULL
);

-- Which gates apply to which categories
CREATE TABLE gate_applicability (
    id SERIAL PRIMARY KEY,
    gate_id INTEGER NOT NULL REFERENCES gates(id) ON DELETE CASCADE,
    category category NOT NULL,
    is_required BOOLEAN DEFAULT true,
    is_trigger_based BOOLEAN DEFAULT false,
    UNIQUE(gate_id, category)
);

-- Steps within each gate per category (from Excel)
CREATE TABLE gate_steps (
    id SERIAL PRIMARY KEY,
    gate_id INTEGER NOT NULL REFERENCES gates(id) ON DELETE CASCADE,
    category category NOT NULL,
    step_name VARCHAR(500) NOT NULL,
    description TEXT,
    evidence_required TEXT,
    common_failure_modes TEXT,
    sort_order INTEGER NOT NULL DEFAULT 0
);

-- RACI assignment per step per role (reference)
CREATE TABLE gate_step_raci (
    id SERIAL PRIMARY KEY,
    gate_step_id INTEGER NOT NULL REFERENCES gate_steps(id) ON DELETE CASCADE,
    role_name VARCHAR(100) NOT NULL,
    raci raci_type NOT NULL,
    UNIQUE(gate_step_id, role_name, raci)
);

-- ============================================================
-- PROJECT TABLES (instance data)
-- ============================================================

-- Each product being tracked through NPI
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category category NOT NULL,
    current_gate INTEGER DEFAULT 0,
    status project_status DEFAULT 'active',
    -- Key owners stored as text (no users table yet)
    requestor_name VARCHAR(255),
    requestor_email VARCHAR(255),
    sponsor_name VARCHAR(255),
    sponsor_email VARCHAR(255),
    product_owner_name VARCHAR(255),
    product_owner_email VARCHAR(255),
    tech_lead_name VARCHAR(255),
    tech_lead_email VARCHAR(255),
    commercial_owner_name VARCHAR(255),
    commercial_owner_email VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Instance: a gate for a specific project
CREATE TABLE project_gates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    gate_id INTEGER NOT NULL REFERENCES gates(id) ON DELETE CASCADE,
    status gate_status DEFAULT 'not_started',
    target_start_date DATE,
    target_end_date DATE,
    actual_start_date DATE,
    actual_end_date DATE,
    approved_by VARCHAR(255),
    approved_at TIMESTAMPTZ,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(project_id, gate_id)
);

-- Instance: checklist items per project gate
CREATE TABLE project_gate_checklist (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_gate_id UUID NOT NULL REFERENCES project_gates(id) ON DELETE CASCADE,
    gate_step_id INTEGER REFERENCES gate_steps(id),
    step_name VARCHAR(500) NOT NULL,
    is_completed BOOLEAN DEFAULT false,
    completed_by VARCHAR(255),
    completed_at TIMESTAMPTZ,
    evidence_link TEXT,
    evidence_notes TEXT,
    is_applicable BOOLEAN DEFAULT true,
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- RACI assignments per project (who fills each role)
CREATE TABLE project_raci_assignments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    role_name VARCHAR(100) NOT NULL,
    assignee_name VARCHAR(255),
    assignee_email VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(project_id, role_name)
);

-- Trigger flags driving gate tailoring
CREATE TABLE project_triggers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    trigger_name VARCHAR(255) NOT NULL,
    is_triggered BOOLEAN DEFAULT false,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(project_id, trigger_name)
);

-- Status updates / progress notes
CREATE TABLE status_updates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    project_gate_id UUID REFERENCES project_gates(id) ON DELETE SET NULL,
    author_name VARCHAR(255) NOT NULL,
    author_email VARCHAR(255),
    content TEXT NOT NULL,
    update_type update_type DEFAULT 'progress',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- PPPP tracking (Category III commercial)
CREATE TABLE pppp_tracking (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    positioning TEXT,
    pricing TEXT,
    packaging TEXT,
    proposition TEXT,
    status pppp_status DEFAULT 'draft',
    version INTEGER DEFAULT 1,
    updated_by VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- In-app + email notifications
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    recipient_email VARCHAR(255) NOT NULL,
    recipient_name VARCHAR(255),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    type notification_type NOT NULL,
    subject VARCHAR(500),
    body TEXT,
    is_read BOOLEAN DEFAULT false,
    email_sent BOOLEAN DEFAULT false,
    email_sent_at TIMESTAMPTZ,
    mailgun_message_id VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Audit log
CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    actor_name VARCHAR(255),
    actor_email VARCHAR(255),
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    action VARCHAR(255) NOT NULL,
    entity_type VARCHAR(100),
    entity_id UUID,
    details JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- INDEXES
-- ============================================================

CREATE INDEX idx_project_gates_project ON project_gates(project_id);
CREATE INDEX idx_project_gates_status ON project_gates(status);
CREATE INDEX idx_checklist_project_gate ON project_gate_checklist(project_gate_id);
CREATE INDEX idx_checklist_completed ON project_gate_checklist(is_completed) WHERE is_applicable = true;
CREATE INDEX idx_raci_project ON project_raci_assignments(project_id);
CREATE INDEX idx_triggers_project ON project_triggers(project_id);
CREATE INDEX idx_updates_project ON status_updates(project_id, created_at DESC);
CREATE INDEX idx_notifications_recipient ON notifications(recipient_email, is_read);
CREATE INDEX idx_notifications_project ON notifications(project_id);
CREATE INDEX idx_audit_project ON audit_log(project_id, created_at DESC);
CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_projects_type ON projects(category);
CREATE INDEX idx_projects_gate ON projects(current_gate);

-- ============================================================
-- TRIGGERS (auto-update updated_at)
-- ============================================================

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_projects_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_project_gates_updated_at
    BEFORE UPDATE ON project_gates
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_checklist_updated_at
    BEFORE UPDATE ON project_gate_checklist
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_pppp_updated_at
    BEFORE UPDATE ON pppp_tracking
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();