schema.sql

-- ============================================================================
-- Local PIA Database Architecture (local_pia.db)
-- Engine: SQLite 3.x with WAL Concurrency & Foreign Key Integrity
-- ============================================================================

-- Enable Write-Ahead Logging for high-throughput concurrent reads/writes
PRAGMA journal_mode = WAL;

-- Enforce strict relational foreign key constraints
PRAGMA foreign_keys = ON;

-- Set default text encoding
PRAGMA encoding = "UTF-8";

-- ----------------------------------------------------------------------------
-- 1. USERS TABLE (System Roles & Authentication)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT UNIQUE NOT NULL, -- e.g., 'USR-AUD-001'
    username TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('AUDITOR', 'FRONTEND_USER', 'ADMIN')),
    department TEXT,
    must_change_password INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ----------------------------------------------------------------------------
-- 2. PIA_RECORDS TABLE (Assessment Master Records & Risk Scores)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pia_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fid TEXT UNIQUE NOT NULL, -- Frontend Server ID: e.g., 'PIA-FE-2026-A8F9K2L1'
    bid TEXT UNIQUE,          -- Backend Audit ID: e.g., 'PIA-BE-IN-2026-000412'
    project_name TEXT NOT NULL,
    industry_sector TEXT NOT NULL CHECK(industry_sector IN (
        'BANKING', 'HOSPITALS', 'RETAIL', 'CORPORATE', 'TRADE', 'AGRICULTURE', 'PHARMA'
    )),
    status TEXT NOT NULL CHECK(status IN (
        'DRAFT', 'SUBMITTED', 'IN_REVISION', 'APPROVED', 'REJECTED'
    )) DEFAULT 'DRAFT',
    created_by_user_id TEXT NOT NULL,
    current_version TEXT DEFAULT 'v1.0',
    impact_score REAL,
    likelihood_score REAL,
    total_risk_score REAL,
    overall_risk_rating TEXT CHECK(overall_risk_rating IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
    sme_override_risk_rating TEXT CHECK(sme_override_risk_rating IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
    sme_rationale TEXT,
    initiative_type TEXT CHECK(initiative_type IN ('PROJECT', 'PROCESS', 'APPLICATION', 'POC', 'PILOT', 'AI_INITIATIVE')) DEFAULT 'PROJECT',
    rai_required INTEGER DEFAULT 0,
    rai_acknowledged INTEGER DEFAULT 0,
    data_flow_description TEXT,
    business_context TEXT,
    scope_of_work TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by_user_id) REFERENCES users(user_id) ON DELETE RESTRICT
);

-- ----------------------------------------------------------------------------
-- 3. PIA_QUESTIONNAIRE_ANSWERS TABLE (Individual Questionnaire Responses)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pia_questionnaire_answers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fid TEXT NOT NULL,
    question_id TEXT NOT NULL, -- e.g., 'Q1_PI_TYPE', 'Q4_LOCATION_STORAGE'
    section TEXT NOT NULL,     -- e.g., 'SENSITIVITY', 'LAWFUL_BASIS', 'AI_USE_CASE'
    score_value INTEGER NOT NULL CHECK(score_value BETWEEN 1 AND 5),
    response_text TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fid) REFERENCES pia_records(fid) ON DELETE CASCADE,
    UNIQUE(fid, question_id)
);

-- ----------------------------------------------------------------------------
-- 4. PROVENANCE_LOGS TABLE (Immutable Audit Lineage & SHA-256 Hashes)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS provenance_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fid TEXT NOT NULL,
    bid TEXT,
    version TEXT NOT NULL,
    action TEXT NOT NULL CHECK(action IN (
        'INITIAL_SUBMISSION', 'SME_REVISION', 'REGULATORY_TAILORING', 'SIGN_OFF', 'DRAFT_SAVED'
    )),
    modified_by_user_id TEXT NOT NULL,
    delta_log_json TEXT NOT NULL, -- JSON delta log string
    sha256_hash TEXT NOT NULL,   -- Cryptographic verification hash
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fid) REFERENCES pia_records(fid) ON DELETE CASCADE
);

-- ----------------------------------------------------------------------------
-- 5. REGULATORY_TAILORING TABLE (Framework Alignment & Compliance Notes)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS regulatory_tailoring (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bid TEXT NOT NULL,
    framework_name TEXT NOT NULL CHECK(framework_name IN (
        'DPDP_ACT_2023', 'GDPR', 'ISO_42001', 'NIST_AI_RMF', 'PCI_DSS', 'HIPAA', 'GLBA'
    )),
    tailored_notes TEXT,
    override_risk_level TEXT CHECK(override_risk_level IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
    auditor_signoff_by TEXT,
    applied_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bid) REFERENCES pia_records(bid) ON DELETE CASCADE
);

-- ----------------------------------------------------------------------------
-- INDEXES FOR PERFORMANCE OPTIMIZATION
-- ----------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_pia_fid ON pia_records(fid);
CREATE INDEX IF NOT EXISTS idx_pia_bid ON pia_records(bid);
CREATE INDEX IF NOT EXISTS idx_pia_sector ON pia_records(industry_sector);
CREATE INDEX IF NOT EXISTS idx_pia_status ON pia_records(status);
CREATE INDEX IF NOT EXISTS idx_provenance_fid ON provenance_logs(fid);
CREATE INDEX IF NOT EXISTS idx_answers_fid ON pia_questionnaire_answers(fid);

-- ----------------------------------------------------------------------------
-- TRIGGERS FOR AUTO-UPDATING TIMESTAMP
-- ----------------------------------------------------------------------------
CREATE TRIGGER IF NOT EXISTS update_pia_records_timestamp 
AFTER UPDATE ON pia_records
BEGIN
    UPDATE pia_records SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

-- ----------------------------------------------------------------------------
-- INITIAL MASTER SEED DATA (Default Auditor & Admin)
-- ----------------------------------------------------------------------------
INSERT OR IGNORE INTO users (user_id, username, password_hash, role, department, must_change_password)
VALUES 
('USR-AUD-001', 'admin', 'pbkdf2:sha256:260000$admin_salt$admin_hash_placeholder', 'ADMIN', 'Data Governance', 0),
('USR-AUD-002', 'dpdp_auditor', 'pbkdf2:sha256:260000$sme_salt$sme_hash_placeholder', 'AUDITOR', 'Privacy Office', 0);