File size: 4,514 Bytes
f4ae8d9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | -- 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();
|