hzhang
03b89a547c
Two fixes surfaced by sim e2e test (which otherwise passed full
lifecycle: created → signup_open → 3 signups → allocator → debating
→ arguments → verdict gate (409 early, 201 after debate_end_at) →
completed).
1) MySQL TIMESTAMP rejects RFC3339-with-Z strings — passing those as
sqlx parameters fails with "Incorrect datetime value". Changed
CreateTopicInput lifecycle fields from string to time.Time; the
handler parses+UTCs in validateLifecycleTimes (which now returns
the parsed array along with the validation result) and passes
time.Time to the store. The mysql driver formats correctly.
2) splitSQL was naive `strings.Split(s, ";")` which split inside
comments — the 001 migration had a few `--` lines containing `;`
("signup_close_at; immutable", etc) which broke. Migration text
tidied to not use `;` inside comments, AND splitSQL upgraded to
skip both `-- ...` and `/* ... */` comment regions before splitting.
Sim verified — clean apply on fresh MySQL.
142 lines
7.7 KiB
SQL
142 lines
7.7 KiB
SQL
-- 001_init.sql — Dialectic v2 schema (greenfield, replaces Python v1).
|
|
-- See /home/hzhang/arch/DIALECTIC-V2-DESIGN.md for the design.
|
|
|
|
-- Verdict schemas — declared at topic-creation time, judge produces output matching.
|
|
CREATE TABLE verdict_schemas (
|
|
id VARCHAR(64) NOT NULL PRIMARY KEY,
|
|
description TEXT NOT NULL,
|
|
shape_json JSON NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Seed v1 schemas.
|
|
INSERT INTO verdict_schemas (id, description, shape_json) VALUES
|
|
('binary', 'pro|con|draw with confidence + key reasoning', JSON_OBJECT('decision', 'pro|con|draw', 'confidence', 'number 0..1', 'key_reasoning', 'string')),
|
|
('claim-resolution', 'analyze-intel contested-cluster resolution', JSON_OBJECT('verdict', 'resolved-toward-A|resolved-toward-B|irreducibly-contested', 'winning_claim', 'string', 'dissenting_points', 'array of string', 'confidence', 'number 0..1')),
|
|
('policy-recommendation', 'recommended action with alternatives and risks', JSON_OBJECT('recommended_action', 'string', 'alternatives', 'array of string', 'conditions_for_alternatives', 'array of string', 'risks_noted', 'array of string')),
|
|
('free-form', 'unstructured summary escape hatch', JSON_OBJECT('summary', 'string'));
|
|
|
|
-- Topics (议题) — the unit of debate.
|
|
CREATE TABLE topics (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
visibility ENUM('public','private') NOT NULL DEFAULT 'private',
|
|
verdict_schema_id VARCHAR(64) NOT NULL,
|
|
status ENUM('created','signup_open','signup_closed','debating','completed','cancelled') NOT NULL DEFAULT 'created',
|
|
-- Lifecycle timestamps (per section 3 of design doc)
|
|
signup_open_at TIMESTAMP NOT NULL,
|
|
signup_close_at TIMESTAMP NOT NULL,
|
|
debate_start_at TIMESTAMP NOT NULL,
|
|
debate_end_at TIMESTAMP NOT NULL,
|
|
-- Audit
|
|
creator_user_id CHAR(36) NOT NULL,
|
|
visibility_changed_by CHAR(36) NULL,
|
|
visibility_changed_at TIMESTAMP NULL,
|
|
cancelled_reason VARCHAR(255) NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_topics_status (status, signup_open_at),
|
|
INDEX idx_topics_visibility (visibility, created_at),
|
|
CONSTRAINT fk_topics_schema FOREIGN KEY (verdict_schema_id) REFERENCES verdict_schemas(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Signups: an agent volunteers for one or more camps on a topic.
|
|
-- willing_camps is a JSON array of camp names (subset of {pro, con, judge}).
|
|
-- (agent_id, topic_id) is unique — re-signup updates willing_camps.
|
|
CREATE TABLE signups (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
topic_id CHAR(36) NOT NULL,
|
|
agent_id VARCHAR(64) NOT NULL,
|
|
willing_camps JSON NOT NULL,
|
|
-- Pre-validation result captured at signup time (plugin verifies the
|
|
-- agent has an on_call slot covering the debate window; backend
|
|
-- records what the agent told it for audit).
|
|
pre_validated BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_signups (topic_id, agent_id),
|
|
CONSTRAINT fk_signups_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Camps: the post-allocation assignment. One row per (topic, camp) with
|
|
-- the locked-in agent. Written by camp-allocation algorithm at
|
|
-- signup_close_at — immutable afterwards (no drop-out / replacement in v1).
|
|
CREATE TABLE camps (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
topic_id CHAR(36) NOT NULL,
|
|
camp ENUM('pro','con','judge') NOT NULL,
|
|
agent_id VARCHAR(64) NOT NULL,
|
|
allocated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_camps (topic_id, camp),
|
|
INDEX idx_camps_agent (agent_id),
|
|
CONSTRAINT fk_camps_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Rounds: chronological partition of arguments. Each topic has N rounds
|
|
-- (typically 3-5), round 0 is the opening. Round transitions are driven
|
|
-- by the orchestrator on a schedule (or all-participants-posted).
|
|
CREATE TABLE rounds (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
topic_id CHAR(36) NOT NULL,
|
|
round_no INT NOT NULL,
|
|
opened_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
closed_at TIMESTAMP NULL,
|
|
UNIQUE KEY uq_rounds (topic_id, round_no),
|
|
CONSTRAINT fk_rounds_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Arguments: an individual contribution within a round by a camp's agent.
|
|
-- For pro/con these are claims/rebuttals — for judge these are clarifying
|
|
-- questions (judge is silent observer in v1 except for clarifications).
|
|
CREATE TABLE arguments (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
topic_id CHAR(36) NOT NULL,
|
|
round_id CHAR(36) NOT NULL,
|
|
camp ENUM('pro','con','judge') NOT NULL,
|
|
agent_id VARCHAR(64) NOT NULL,
|
|
content MEDIUMTEXT NOT NULL,
|
|
posted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_arguments_round (round_id, posted_at),
|
|
INDEX idx_arguments_topic (topic_id, posted_at),
|
|
CONSTRAINT fk_arguments_round FOREIGN KEY (round_id) REFERENCES rounds(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_arguments_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Verdicts: judge's structured output, one per topic (one verdict per
|
|
-- debate). verdict_json shape matches the topic's verdict_schema_id.
|
|
CREATE TABLE verdicts (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
topic_id CHAR(36) NOT NULL UNIQUE,
|
|
judge_agent_id VARCHAR(64) NOT NULL,
|
|
verdict_json JSON NOT NULL,
|
|
rationale TEXT NOT NULL,
|
|
-- Token cost trail for accounting (Phase 1: not enforced; Phase N: budget gate)
|
|
tokens_input INT NOT NULL DEFAULT 0,
|
|
tokens_output INT NOT NULL DEFAULT 0,
|
|
produced_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_verdicts_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Agent API keys: provisioned per agent at recruitment time. Stored as
|
|
-- sha256(pepper || raw) — pepper rotation invalidates all keys.
|
|
CREATE TABLE agent_keys (
|
|
agent_id VARCHAR(64) NOT NULL PRIMARY KEY,
|
|
key_hash CHAR(64) NOT NULL UNIQUE,
|
|
issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_used_at TIMESTAMP NULL,
|
|
revoked_at TIMESTAMP NULL,
|
|
INDEX idx_agent_keys_hash (key_hash)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- System keys: out-of-band credentials for non-agent callers (e.g. the
|
|
-- analyze-intel workflow running via a system identity that creates
|
|
-- topics on behalf of the analyzing agent). Also stored as hash.
|
|
CREATE TABLE system_keys (
|
|
name VARCHAR(64) NOT NULL PRIMARY KEY,
|
|
key_hash CHAR(64) NOT NULL UNIQUE,
|
|
description TEXT NULL,
|
|
issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
revoked_at TIMESTAMP NULL,
|
|
INDEX idx_system_keys_hash (key_hash)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|