`migrations/001_initial_schema.sql`

124 lines
-- StudentLoanTracker Initial Schema Migration
-- Created: 2026-03-25
-- Database: dw_unified, Schema: slt

CREATE SCHEMA IF NOT EXISTS slt;

-- Users (synced from Supabase Auth or local)
CREATE TABLE IF NOT EXISTS slt.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT,
  phone TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  sms_consent BOOLEAN DEFAULT FALSE,
  sms_consent_at TIMESTAMPTZ,
  preferences JSONB DEFAULT '{}'
);

-- Loan records (manual entry or parsed from TXT)
CREATE TABLE IF NOT EXISTS slt.loans (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES slt.users(id),
  session_id TEXT,
  loan_type TEXT,
  servicer TEXT,
  original_balance NUMERIC(12,2),
  current_balance NUMERIC(12,2),
  interest_rate NUMERIC(5,3),
  disbursement_date DATE,
  status TEXT,
  repayment_plan TEXT,
  raw_data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Calculator results (snapshots)
CREATE TABLE IF NOT EXISTS slt.calc_results (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES slt.users(id),
  session_id TEXT,
  calc_type TEXT,
  inputs JSONB,
  outputs JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- PSLF tracking
CREATE TABLE IF NOT EXISTS slt.pslf_progress (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES slt.users(id),
  qualifying_payments INT DEFAULT 0,
  employer_type TEXT,
  employer_name TEXT,
  current_plan TEXT,
  start_date DATE,
  notes TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Document uploads (metadata only)
CREATE TABLE IF NOT EXISTS slt.documents (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES slt.users(id),
  session_id TEXT,
  filename TEXT,
  mime_type TEXT,
  classification TEXT,
  confidence NUMERIC(3,2),
  extracted_data JSONB,
  retained BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Servicer directory (admin-managed)
CREATE TABLE IF NOT EXISTS slt.servicers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT,
  website TEXT,
  official BOOLEAN DEFAULT TRUE,
  notes TEXT,
  last_verified DATE,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Seed official servicers
INSERT INTO slt.servicers (name, phone, website, official, last_verified, notes) VALUES
  ('Edfinancial', '1-855-337-6884', 'https://edfinancial.com', true, '2026-03-25', 'Official federal servicer'),
  ('MOHELA', '1-888-866-4352', 'https://www.mohela.com', true, '2026-03-25', 'Official federal servicer, commonly handles PSLF'),
  ('Aidvantage', '1-800-722-1300', 'https://aidvantage.com', true, '2026-03-25', 'Official federal servicer'),
  ('Nelnet', '1-888-486-4722', 'https://www.nelnet.com', true, '2026-03-25', 'Official federal servicer')
ON CONFLICT DO NOTHING;

-- SMS/email communications log
CREATE TABLE IF NOT EXISTS slt.comms_log (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES slt.users(id),
  channel TEXT,
  direction TEXT,
  content_summary TEXT,
  status TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Admin audit log
CREATE TABLE IF NOT EXISTS slt.audit_log (
  id SERIAL PRIMARY KEY,
  admin_user TEXT,
  action TEXT,
  target_table TEXT,
  target_id TEXT,
  details JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_loans_user ON slt.loans(user_id);
CREATE INDEX IF NOT EXISTS idx_loans_session ON slt.loans(session_id);
CREATE INDEX IF NOT EXISTS idx_calc_results_user ON slt.calc_results(user_id);
CREATE INDEX IF NOT EXISTS idx_calc_results_session ON slt.calc_results(session_id);
CREATE INDEX IF NOT EXISTS idx_pslf_user ON slt.pslf_progress(user_id);
CREATE INDEX IF NOT EXISTS idx_docs_user ON slt.documents(user_id);
CREATE INDEX IF NOT EXISTS idx_comms_user ON slt.comms_log(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_created ON slt.audit_log(created_at);