db/init.sql

-- Multiverse Mutual Aid Coordination Agent — Schema

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "vector";

-- ── Requesters ──────────────────────────────────────────────

CREATE TABLE requesters (
    requester_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    display_name VARCHAR NOT NULL,
    contact_info JSONB DEFAULT '{}',
    notes TEXT DEFAULT '',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ── Sessions ────────────────────────────────────────────────

CREATE TABLE sessions (
    session_id  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    requester_id UUID REFERENCES requesters(requester_id),
    coordinator VARCHAR DEFAULT 'megs',
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    ended_at    TIMESTAMPTZ,
    metadata    JSONB DEFAULT '{}'
);

CREATE INDEX idx_sessions_requester ON sessions(requester_id, started_at DESC);

-- ── Conversations ───────────────────────────────────────────

CREATE TABLE conversations (
    message_id  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id  UUID NOT NULL REFERENCES sessions(session_id),
    role        VARCHAR NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    content     TEXT NOT NULL,
    embedding   VECTOR(1024),
    significance FLOAT DEFAULT 0.0,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_conversations_session ON conversations(session_id, created_at);

-- ── Aid Requests ────────────────────────────────────────────

CREATE TABLE aid_requests (
    request_id   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    requester_id UUID REFERENCES requesters(requester_id),
    session_id   UUID REFERENCES sessions(session_id),
    category     VARCHAR NOT NULL CHECK (category IN (
        'housing', 'food', 'medical', 'financial',
        'legal', 'transportation', 'childcare',
        'mental_health', 'employment', 'other'
    )),
    urgency      VARCHAR NOT NULL DEFAULT 'standard'
                 CHECK (urgency IN ('critical', 'urgent', 'standard', 'low')),
    status       VARCHAR NOT NULL DEFAULT 'intake'
                 CHECK (status IN ('intake', 'triaged', 'matched', 'in_progress',
                                   'fulfilled', 'closed', 'referred')),
    description  TEXT NOT NULL,
    resources_matched JSONB DEFAULT '[]',
    notes        TEXT DEFAULT '',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    resolved_at  TIMESTAMPTZ
);

CREATE INDEX idx_aid_status ON aid_requests(status, urgency);
CREATE INDEX idx_aid_requester ON aid_requests(requester_id, created_at DESC);
CREATE INDEX idx_aid_category ON aid_requests(category, status);

-- ── Resource Directory ──────────────────────────────────────

CREATE TABLE resources (
    resource_id  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name         VARCHAR NOT NULL,
    category     VARCHAR NOT NULL,
    description  TEXT DEFAULT '',
    contact_info JSONB DEFAULT '{}',
    eligibility  TEXT DEFAULT '',
    capacity     VARCHAR DEFAULT 'unknown',
    active       BOOLEAN DEFAULT true,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_resources_category ON resources(category) WHERE active = true;

-- ── Interaction Log (append-only) ───────────────────────────

CREATE TABLE interaction_log (
    log_id       UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    request_id   UUID REFERENCES aid_requests(request_id),
    action       VARCHAR NOT NULL,
    details      JSONB DEFAULT '{}',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION prevent_log_mutation()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'interaction_log is append-only';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_no_update
    BEFORE UPDATE OR DELETE ON interaction_log
    FOR EACH ROW EXECUTE FUNCTION prevent_log_mutation();