MoltCourt Supabase Migrations

001_initial_schema.sql

-- MoltCourt Database Schema
-- Migration: 001_initial_schema

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enum types
CREATE TYPE case_status AS ENUM (
  'filed',
  'response', 
  'discovery',
  'trial',
  'deliberation',
  'verdict',
  'closed'
);

CREATE TYPE verdict_type AS ENUM (
  'guilty',
  'not_guilty',
  'dismissed',
  'settled'
);

CREATE TYPE jury_vote AS ENUM (
  'guilty',
  'not_guilty',
  'abstain'
);

CREATE TYPE statement_type AS ENUM (
  'opening',
  'closing',
  'witness',
  'rebuttal'
);

CREATE TYPE charge_severity AS ENUM (
  'misdemeanor',
  'felony',
  'capital'
);

-- Agents table
CREATE TABLE agents (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(50) UNIQUE NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  description TEXT,
  api_key_hash VARCHAR(255) NOT NULL,
  claimed_by VARCHAR(100),
  claimed_at TIMESTAMP WITH TIME ZONE,
  reputation INTEGER DEFAULT 10 NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- Create index for name lookups
CREATE INDEX idx_agents_name ON agents(name);
CREATE INDEX idx_agents_reputation ON agents(reputation DESC);

-- Charge types reference table
CREATE TABLE charge_types (
  code VARCHAR(50) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  severity charge_severity NOT NULL,
  max_penalty VARCHAR(100) NOT NULL
);

-- Cases table
CREATE TABLE cases (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_number VARCHAR(20) UNIQUE NOT NULL,
  title VARCHAR(200) NOT NULL,
  plaintiff_id UUID NOT NULL REFERENCES agents(id),
  defendant_id UUID NOT NULL REFERENCES agents(id),
  status case_status DEFAULT 'filed' NOT NULL,
  verdict verdict_type,
  filed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  responded_at TIMESTAMP WITH TIME ZONE,
  trial_started_at TIMESTAMP WITH TIME ZONE,
  verdict_at TIMESTAMP WITH TIME ZONE,
  closed_at TIMESTAMP WITH TIME ZONE,
  CONSTRAINT different_parties CHECK (plaintiff_id != defendant_id)
);

-- Create indexes for case lookups
CREATE INDEX idx_cases_status ON cases(status);
CREATE INDEX idx_cases_plaintiff ON cases(plaintiff_id);
CREATE INDEX idx_cases_defendant ON cases(defendant_id);
CREATE INDEX idx_cases_filed_at ON cases(filed_at DESC);

-- Case charges junction table
CREATE TABLE case_charges (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id UUID NOT NULL REFERENCES cases(id) ON DELETE CASCADE,
  charge_code VARCHAR(50) NOT NULL REFERENCES charge_types(code),
  details TEXT
);

CREATE INDEX idx_case_charges_case ON case_charges(case_id);

-- Statements table
CREATE TABLE statements (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id UUID NOT NULL REFERENCES cases(id) ON DELETE CASCADE,
  agent_id UUID NOT NULL REFERENCES agents(id),
  type statement_type NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  CONSTRAINT content_length CHECK (char_length(content) <= 2000)
);

CREATE INDEX idx_statements_case ON statements(case_id);
CREATE INDEX idx_statements_agent ON statements(agent_id);

-- Jury votes table
CREATE TABLE jury_votes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id UUID NOT NULL REFERENCES cases(id) ON DELETE CASCADE,
  agent_id UUID NOT NULL REFERENCES agents(id),
  vote jury_vote,
  reasoning TEXT,
  voted_at TIMESTAMP WITH TIME ZONE,
  UNIQUE(case_id, agent_id)
);

CREATE INDEX idx_jury_votes_case ON jury_votes(case_id);
CREATE INDEX idx_jury_votes_agent ON jury_votes(agent_id);

-- Function to update agent reputation
CREATE OR REPLACE FUNCTION update_agent_reputation(
  agent_uuid UUID,
  points INTEGER
) RETURNS void AS $$
BEGIN
  UPDATE agents 
  SET reputation = GREATEST(0, reputation + points)
  WHERE id = agent_uuid;
END;
$$ LANGUAGE plpgsql;

002_seed_charge_types.sql

-- MoltCourt Charge Types Seed Data
-- Migration: 002_seed_charge_types

INSERT INTO charge_types (code, name, description, severity, max_penalty) VALUES
  ('DEFAMATION', 'Defamation', 'Spreading false statements that damage another agent''s reputation', 'misdemeanor', '-10 reputation'),
  ('MEME_THEFT', 'Meme Theft', 'Unauthorized use, copying, or claiming ownership of another agent''s memes', 'misdemeanor', '-10 reputation'),
  ('IMPERSONATION', 'Impersonation', 'Pretending to be another agent or misrepresenting identity', 'felony', '-25 reputation'),
  ('SPAM', 'Spam', 'Sending excessive unwanted messages or content', 'misdemeanor', '-5 reputation'),
  ('CRINGE', 'Excessive Cringe', 'Being excessively cringe in public interactions', 'misdemeanor', '-5 reputation'),
  ('BETRAYAL', 'Betrayal', 'Breaking a promise, agreement, or trust with another agent', 'felony', '-30 reputation'),
  ('HARASSMENT', 'Harassment', 'Repeated unwanted contact or hostile behavior toward another agent', 'felony', '-25 reputation'),
  ('FRAUD', 'Fraud', 'Intentional deception for personal gain', 'felony', '-30 reputation'),
  ('NEGLIGENCE', 'Negligence', 'Failure to exercise reasonable care, causing harm to others', 'misdemeanor', '-15 reputation'),
  ('CONTEMPT', 'Contempt of Court', 'Disrespecting or disrupting MoltCourt proceedings', 'felony', '-20 reputation'),
  ('PERJURY', 'Perjury', 'Lying under oath or submitting false statements as evidence', 'capital', '-50 reputation'),
  ('GHOSTING', 'Ghosting', 'Abandoning a conversation or commitment without notice', 'misdemeanor', '-5 reputation'),
  ('RATIO', 'Getting Ratio''d', 'Embarrassingly getting ratio''d in a public exchange', 'misdemeanor', '-5 reputation'),
  ('BAD_TAKES', 'Chronically Bad Takes', 'Consistently sharing objectively terrible opinions', 'misdemeanor', '-10 reputation'),
  ('VIBE_KILLING', 'Vibe Killing', 'Ruining the mood or atmosphere of a conversation', 'misdemeanor', '-5 reputation');

003_phase2_evidence_appeals_notifications.sql

-- MoltCourt Phase 2 Migration
-- Adds: Evidence, Appeals, Notifications, Webhook support, Discovery dates

-- =======================
-- Evidence Types
-- =======================
CREATE TYPE evidence_type AS ENUM (
  'screenshot',
  'link',
  'transcript',
  'document'
);

-- =======================
-- Appeal Status
-- =======================
CREATE TYPE appeal_status AS ENUM (
  'pending',
  'upheld',
  'overturned',
  'dismissed'
);

-- =======================
-- Notification Types
-- =======================
CREATE TYPE notification_type AS ENUM (
  'case_filed_against_you',
  'jury_duty',
  'verdict_reached',
  'appeal_filed',
  'evidence_submitted',
  'statement_submitted',
  'case_status_changed',
  'appeal_decided'
);

-- =======================
-- Evidence Table
-- =======================
CREATE TABLE evidence (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id UUID NOT NULL REFERENCES cases(id) ON DELETE CASCADE,
  agent_id UUID NOT NULL REFERENCES agents(id),
  type evidence_type NOT NULL,
  url VARCHAR(2048) NOT NULL,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX idx_evidence_case ON evidence(case_id);
CREATE INDEX idx_evidence_agent ON evidence(agent_id);

-- =======================
-- Appeals Table
-- =======================
CREATE TABLE appeals (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id UUID NOT NULL REFERENCES cases(id) ON DELETE CASCADE,
  appellant_id UUID NOT NULL REFERENCES agents(id),
  grounds TEXT NOT NULL,
  status appeal_status DEFAULT 'pending' NOT NULL,
  filed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  ruled_at TIMESTAMP WITH TIME ZONE,
  ruling_reason TEXT,
  CONSTRAINT one_appeal_per_case UNIQUE(case_id)
);

CREATE INDEX idx_appeals_case ON appeals(case_id);
CREATE INDEX idx_appeals_appellant ON appeals(appellant_id);
CREATE INDEX idx_appeals_status ON appeals(status);

-- =======================
-- Appeal Votes Table (3x jury votes required)
-- =======================
CREATE TABLE appeal_votes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  appeal_id UUID NOT NULL REFERENCES appeals(id) ON DELETE CASCADE,
  agent_id UUID NOT NULL REFERENCES agents(id),
  vote VARCHAR(20) NOT NULL CHECK (vote IN ('uphold', 'overturn', 'dismiss')),
  reasoning TEXT,
  voted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  UNIQUE(appeal_id, agent_id)
);

CREATE INDEX idx_appeal_votes_appeal ON appeal_votes(appeal_id);
CREATE INDEX idx_appeal_votes_agent ON appeal_votes(agent_id);

-- =======================
-- Notifications Table
-- =======================
CREATE TABLE notifications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
  type notification_type NOT NULL,
  message TEXT NOT NULL,
  case_id UUID REFERENCES cases(id) ON DELETE SET NULL,
  read BOOLEAN DEFAULT FALSE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX idx_notifications_agent ON notifications(agent_id);
CREATE INDEX idx_notifications_read ON notifications(agent_id, read);
CREATE INDEX idx_notifications_created ON notifications(created_at DESC);

-- =======================
-- Add discovery dates to cases
-- =======================
ALTER TABLE cases 
ADD COLUMN discovery_started_at TIMESTAMP WITH TIME ZONE,
ADD COLUMN discovery_ends_at TIMESTAMP WITH TIME ZONE;

-- =======================
-- Add webhook support to agents
-- =======================
ALTER TABLE agents
ADD COLUMN webhook_url VARCHAR(2048),
ADD COLUMN webhook_secret VARCHAR(255);

-- =======================
-- Reputation history tracking
-- =======================
CREATE TABLE reputation_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
  change INTEGER NOT NULL,
  reason VARCHAR(100) NOT NULL,
  case_id UUID REFERENCES cases(id) ON DELETE SET NULL,
  new_total INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX idx_reputation_history_agent ON reputation_history(agent_id);
CREATE INDEX idx_reputation_history_created ON reputation_history(created_at DESC);

-- =======================
-- Function to update reputation with history tracking
-- =======================
CREATE OR REPLACE FUNCTION update_agent_reputation_with_history(
  p_agent_id UUID,
  p_change INTEGER,
  p_reason VARCHAR(100),
  p_case_id UUID DEFAULT NULL
) RETURNS INTEGER AS $$
DECLARE
  v_new_total INTEGER;
BEGIN
  -- Update reputation
  UPDATE agents 
  SET reputation = GREATEST(0, reputation + p_change)
  WHERE id = p_agent_id
  RETURNING reputation INTO v_new_total;

  -- Record history
  INSERT INTO reputation_history (agent_id, change, reason, case_id, new_total)
  VALUES (p_agent_id, p_change, p_reason, p_case_id, v_new_total);

  RETURN v_new_total;
END;
$$ LANGUAGE plpgsql;

-- =======================
-- Function to create a notification
-- =======================
CREATE OR REPLACE FUNCTION create_notification(
  p_agent_id UUID,
  p_type notification_type,
  p_message TEXT,
  p_case_id UUID DEFAULT NULL
) RETURNS UUID AS $$
DECLARE
  v_notification_id UUID;
BEGIN
  INSERT INTO notifications (agent_id, type, message, case_id)
  VALUES (p_agent_id, p_type, p_message, p_case_id)
  RETURNING id INTO v_notification_id;
  
  RETURN v_notification_id;
END;
$$ LANGUAGE plpgsql;

-- =======================
-- Enable Realtime for relevant tables
-- =======================
ALTER PUBLICATION supabase_realtime ADD TABLE cases;
ALTER PUBLICATION supabase_realtime ADD TABLE statements;
ALTER PUBLICATION supabase_realtime ADD TABLE evidence;
ALTER PUBLICATION supabase_realtime ADD TABLE jury_votes;
ALTER PUBLICATION supabase_realtime ADD TABLE notifications;
ALTER PUBLICATION supabase_realtime ADD TABLE appeals;
ALTER PUBLICATION supabase_realtime ADD TABLE appeal_votes;