weijuesen
67 lines
2.4 KiB
SQL
67 lines
2.4 KiB
SQL
CREATE TABLE IF NOT EXISTS consultation_opinion_versions (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
consultation_id uuid NOT NULL,
|
|
version integer NOT NULL,
|
|
author_id uuid,
|
|
opinion text NOT NULL,
|
|
plan text NOT NULL,
|
|
reason text,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_consultation_opinion_versions_consultation
|
|
ON consultation_opinion_versions (consultation_id, version);
|
|
|
|
CREATE TABLE IF NOT EXISTS rule_engine_results (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
rule_version varchar(64) NOT NULL,
|
|
disease varchar(64) NOT NULL,
|
|
level varchar(16),
|
|
reason text,
|
|
input_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
missing jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
computed_at timestamptz NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_rule_engine_results_rule_version
|
|
ON rule_engine_results (rule_version, computed_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS knowledge_reviews (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
entity_type varchar(32) NOT NULL,
|
|
entity_id varchar(128) NOT NULL,
|
|
status varchar(16) NOT NULL,
|
|
reviewer_id uuid,
|
|
reviewed_at timestamptz,
|
|
note text,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_reviews_entity
|
|
ON knowledge_reviews (entity_type, entity_id);
|
|
|
|
ALTER TABLE consultations
|
|
ADD COLUMN IF NOT EXISTS assignee_id uuid,
|
|
ADD COLUMN IF NOT EXISTS assigned_at timestamptz,
|
|
ADD COLUMN IF NOT EXISTS accepted_at timestamptz,
|
|
ADD COLUMN IF NOT EXISTS needs_info_at timestamptz,
|
|
ADD COLUMN IF NOT EXISTS sla_deadline timestamptz,
|
|
ADD COLUMN IF NOT EXISTS overdue_at timestamptz,
|
|
ADD COLUMN IF NOT EXISTS last_sla_event_at timestamptz;
|
|
|
|
UPDATE consultations SET status = 'unassigned' WHERE status = 'pending';
|
|
UPDATE consultations SET status = 'assigned' WHERE status = 'consulting';
|
|
|
|
ALTER TABLE knowledge_articles
|
|
ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'published',
|
|
ADD COLUMN IF NOT EXISTS source text,
|
|
ADD COLUMN IF NOT EXISTS source_date date,
|
|
ADD COLUMN IF NOT EXISTS expert_confirmed_by uuid;
|
|
|
|
ALTER TABLE diseases
|
|
ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'published',
|
|
ADD COLUMN IF NOT EXISTS source text,
|
|
ADD COLUMN IF NOT EXISTS source_date date,
|
|
ADD COLUMN IF NOT EXISTS expert_confirmed_by uuid;
|