205 lines
12 KiB
PL/PgSQL
205 lines
12 KiB
PL/PgSQL
|
BEGIN;
|
||
|
|
|
||
|
|
CREATE TABLE conversation_turn (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
subject_id uuid NOT NULL,
|
||
|
|
conversation_id uuid NOT NULL,
|
||
|
|
turn_key varchar(255) NOT NULL,
|
||
|
|
sequence_no bigint NOT NULL CHECK (sequence_no > 0),
|
||
|
|
speaker varchar(24) NOT NULL CHECK (speaker IN ('subject', 'agent')),
|
||
|
|
start_at timestamptz NOT NULL,
|
||
|
|
end_at timestamptz NOT NULL,
|
||
|
|
final_text text NOT NULL CHECK (length(final_text) BETWEEN 1 AND 20000),
|
||
|
|
interrupted boolean NOT NULL DEFAULT false,
|
||
|
|
provider_ref varchar(255),
|
||
|
|
source_type varchar(24) NOT NULL CHECK (source_type IN ('provider', 'device')),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT conversation_turn_time_order CHECK (end_at >= start_at),
|
||
|
|
CONSTRAINT conversation_turn_conversation_tenant_fk FOREIGN KEY (conversation_id, tenant_id)
|
||
|
|
REFERENCES conversation (id, tenant_id),
|
||
|
|
CONSTRAINT conversation_turn_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
|
||
|
|
REFERENCES subject_profile (id, tenant_id),
|
||
|
|
CONSTRAINT conversation_turn_id_tenant_key UNIQUE (id, tenant_id),
|
||
|
|
CONSTRAINT conversation_turn_business_key UNIQUE (conversation_id, turn_key),
|
||
|
|
CONSTRAINT conversation_turn_sequence_key UNIQUE (conversation_id, sequence_no)
|
||
|
|
);
|
||
|
|
CREATE INDEX conversation_turn_subject_time_idx ON conversation_turn (tenant_id, subject_id, start_at DESC);
|
||
|
|
CREATE INDEX conversation_turn_conversation_page_idx ON conversation_turn (conversation_id, sequence_no, id);
|
||
|
|
|
||
|
|
CREATE TABLE conversation_summary (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
subject_id uuid NOT NULL,
|
||
|
|
conversation_id uuid NOT NULL,
|
||
|
|
summary_type varchar(32) NOT NULL CHECK (summary_type IN ('conversation_summary', 'open_topic')),
|
||
|
|
content text NOT NULL CHECK (length(content) BETWEEN 1 AND 20000),
|
||
|
|
source_turn_ids jsonb NOT NULL CHECK (jsonb_typeof(source_turn_ids)='array' AND jsonb_array_length(source_turn_ids)>0),
|
||
|
|
model_ref varchar(160) NOT NULL,
|
||
|
|
prompt_version varchar(80) NOT NULL,
|
||
|
|
generation_version bigint NOT NULL CHECK (generation_version > 0),
|
||
|
|
confidence numeric(5,4) CHECK (confidence BETWEEN 0 AND 1),
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'READY' CHECK (status IN ('PROCESSING', 'READY', 'FAILED', 'SUPERSEDED')),
|
||
|
|
last_error varchar(500),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT conversation_summary_conversation_tenant_fk FOREIGN KEY (conversation_id, tenant_id)
|
||
|
|
REFERENCES conversation (id, tenant_id),
|
||
|
|
CONSTRAINT conversation_summary_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
|
||
|
|
REFERENCES subject_profile (id, tenant_id),
|
||
|
|
CONSTRAINT conversation_summary_generation_key UNIQUE (conversation_id, summary_type, generation_version)
|
||
|
|
);
|
||
|
|
CREATE INDEX conversation_summary_conversation_idx ON conversation_summary (conversation_id, summary_type, generation_version DESC);
|
||
|
|
|
||
|
|
CREATE TABLE story_draft (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
subject_id uuid NOT NULL,
|
||
|
|
conversation_id uuid NOT NULL,
|
||
|
|
title varchar(300) NOT NULL CHECK (length(title)>0),
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'CANDIDATE'
|
||
|
|
CHECK (status IN ('CANDIDATE', 'IN_REVIEW', 'CONFIRMED', 'REJECTED', 'SUPERSEDED')),
|
||
|
|
confidence numeric(5,4) CHECK (confidence BETWEEN 0 AND 1),
|
||
|
|
sensitivity varchar(24) NOT NULL DEFAULT 'normal' CHECK (sensitivity IN ('normal', 'sensitive', 'high')),
|
||
|
|
generation_version bigint NOT NULL CHECK (generation_version > 0),
|
||
|
|
current_revision_id uuid,
|
||
|
|
reviewed_by uuid,
|
||
|
|
reviewed_at timestamptz,
|
||
|
|
review_reason varchar(500),
|
||
|
|
version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT story_draft_conversation_tenant_fk FOREIGN KEY (conversation_id, tenant_id)
|
||
|
|
REFERENCES conversation (id, tenant_id),
|
||
|
|
CONSTRAINT story_draft_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
|
||
|
|
REFERENCES subject_profile (id, tenant_id),
|
||
|
|
CONSTRAINT story_draft_reviewer_tenant_fk FOREIGN KEY (reviewed_by, tenant_id)
|
||
|
|
REFERENCES user_account (id, tenant_id),
|
||
|
|
CONSTRAINT story_draft_generation_key UNIQUE (conversation_id, generation_version)
|
||
|
|
);
|
||
|
|
CREATE INDEX story_draft_subject_status_idx ON story_draft (tenant_id, subject_id, status, created_at DESC);
|
||
|
|
|
||
|
|
CREATE TABLE story_revision (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
story_id uuid NOT NULL REFERENCES story_draft(id),
|
||
|
|
revision_no bigint NOT NULL CHECK (revision_no > 0),
|
||
|
|
content text NOT NULL CHECK (length(content) BETWEEN 1 AND 50000),
|
||
|
|
author_type varchar(24) NOT NULL CHECK (author_type IN ('model', 'user')),
|
||
|
|
author_id uuid,
|
||
|
|
edit_reason varchar(500),
|
||
|
|
base_revision_id uuid REFERENCES story_revision(id),
|
||
|
|
model_ref varchar(160),
|
||
|
|
prompt_version varchar(80),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT story_revision_number_key UNIQUE (story_id, revision_no)
|
||
|
|
);
|
||
|
|
ALTER TABLE story_draft ADD CONSTRAINT story_draft_current_revision_fk
|
||
|
|
FOREIGN KEY (current_revision_id) REFERENCES story_revision(id);
|
||
|
|
|
||
|
|
CREATE TABLE story_source (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
story_revision_id uuid NOT NULL REFERENCES story_revision(id) ON DELETE CASCADE,
|
||
|
|
conversation_turn_id uuid NOT NULL REFERENCES conversation_turn(id),
|
||
|
|
source_start_offset integer CHECK (source_start_offset >= 0),
|
||
|
|
source_end_offset integer CHECK (source_end_offset >= source_start_offset),
|
||
|
|
source_role varchar(24) NOT NULL CHECK (source_role IN ('subject', 'agent')),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT story_source_turn_key UNIQUE (story_revision_id, conversation_turn_id, source_start_offset, source_end_offset)
|
||
|
|
);
|
||
|
|
CREATE INDEX story_source_turn_idx ON story_source (conversation_turn_id);
|
||
|
|
|
||
|
|
CREATE TABLE fact_candidate (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
subject_id uuid NOT NULL,
|
||
|
|
story_id uuid NOT NULL REFERENCES story_draft(id),
|
||
|
|
fact_type varchar(64) NOT NULL,
|
||
|
|
content text NOT NULL CHECK (length(content) BETWEEN 1 AND 4000),
|
||
|
|
statement_basis varchar(24) NOT NULL CHECK (statement_basis IN ('subject_statement', 'model_inference')),
|
||
|
|
time_range jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
confidence numeric(5,4) CHECK (confidence BETWEEN 0 AND 1),
|
||
|
|
sensitivity varchar(24) NOT NULL DEFAULT 'normal' CHECK (sensitivity IN ('normal', 'sensitive', 'high')),
|
||
|
|
source_turn_ids jsonb NOT NULL CHECK (jsonb_typeof(source_turn_ids)='array' AND jsonb_array_length(source_turn_ids)>0),
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'CANDIDATE'
|
||
|
|
CHECK (status IN ('CANDIDATE', 'CONFIRMED', 'REJECTED', 'SUPERSEDED')),
|
||
|
|
generation_version bigint NOT NULL CHECK (generation_version > 0),
|
||
|
|
reviewed_by uuid,
|
||
|
|
reviewed_at timestamptz,
|
||
|
|
review_reason varchar(500),
|
||
|
|
version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT fact_candidate_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
|
||
|
|
REFERENCES subject_profile (id, tenant_id),
|
||
|
|
CONSTRAINT fact_candidate_reviewer_tenant_fk FOREIGN KEY (reviewed_by, tenant_id)
|
||
|
|
REFERENCES user_account (id, tenant_id)
|
||
|
|
);
|
||
|
|
CREATE INDEX fact_candidate_story_status_idx ON fact_candidate (story_id, status);
|
||
|
|
|
||
|
|
CREATE TABLE curated_memory (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
subject_id uuid NOT NULL,
|
||
|
|
current_version_id uuid,
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'ACTIVE'
|
||
|
|
CHECK (status IN ('ACTIVE', 'DISABLED', 'SUPERSEDED', 'PENDING_DELETE', 'DELETED')),
|
||
|
|
sensitivity varchar(24) NOT NULL DEFAULT 'normal' CHECK (sensitivity IN ('normal', 'sensitive', 'high')),
|
||
|
|
valid_from timestamptz,
|
||
|
|
valid_to timestamptz,
|
||
|
|
version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
deleted_at timestamptz,
|
||
|
|
CONSTRAINT curated_memory_valid_range CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to >= valid_from),
|
||
|
|
CONSTRAINT curated_memory_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
|
||
|
|
REFERENCES subject_profile (id, tenant_id)
|
||
|
|
);
|
||
|
|
CREATE INDEX curated_memory_subject_status_idx ON curated_memory (tenant_id, subject_id, status, updated_at DESC);
|
||
|
|
|
||
|
|
CREATE TABLE memory_version (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
memory_id uuid NOT NULL REFERENCES curated_memory(id),
|
||
|
|
version_no bigint NOT NULL CHECK (version_no > 0),
|
||
|
|
content text NOT NULL CHECK (length(content) BETWEEN 1 AND 50000),
|
||
|
|
source_refs jsonb NOT NULL CHECK (jsonb_typeof(source_refs)='array' AND jsonb_array_length(source_refs)>0),
|
||
|
|
confirmed_by uuid NOT NULL REFERENCES user_account(id),
|
||
|
|
confirmed_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
change_reason varchar(500),
|
||
|
|
content_digest char(64) NOT NULL,
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT memory_version_number_key UNIQUE (memory_id, version_no)
|
||
|
|
);
|
||
|
|
ALTER TABLE curated_memory ADD CONSTRAINT curated_memory_current_version_fk
|
||
|
|
FOREIGN KEY (current_version_id) REFERENCES memory_version(id);
|
||
|
|
|
||
|
|
CREATE TABLE memory_index_entry (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
subject_id uuid NOT NULL,
|
||
|
|
memory_id uuid NOT NULL REFERENCES curated_memory(id),
|
||
|
|
memory_version_id uuid NOT NULL REFERENCES memory_version(id),
|
||
|
|
index_type varchar(24) NOT NULL CHECK (index_type IN ('pgvector', 'provider')),
|
||
|
|
embedding vector(1536),
|
||
|
|
provider_ref varchar(300),
|
||
|
|
status varchar(24) NOT NULL DEFAULT 'PENDING'
|
||
|
|
CHECK (status IN ('PENDING', 'ACTIVE', 'STALE', 'FAILED', 'DELETED')),
|
||
|
|
indexed_at timestamptz,
|
||
|
|
last_attempt_at timestamptz,
|
||
|
|
last_error varchar(500),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT memory_index_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
|
||
|
|
REFERENCES subject_profile (id, tenant_id),
|
||
|
|
CONSTRAINT memory_index_version_key UNIQUE (memory_version_id, index_type)
|
||
|
|
);
|
||
|
|
CREATE INDEX memory_index_scope_idx ON memory_index_entry (tenant_id, subject_id, status, memory_id);
|
||
|
|
|
||
|
|
COMMIT;
|