weijuesen
90 lines
3.1 KiB
SQL
90 lines
3.1 KiB
SQL
CREATE TABLE IF NOT EXISTS detection_tasks (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
source_key varchar(128) NOT NULL,
|
|
source_type varchar(32) NOT NULL,
|
|
source_id varchar(128),
|
|
room_id uuid,
|
|
batch_id uuid,
|
|
inspection_id uuid,
|
|
disease varchar(64) NOT NULL,
|
|
recommended_method varchar(32),
|
|
method varchar(32),
|
|
priority varchar(16) NOT NULL DEFAULT 'routine',
|
|
status varchar(16) NOT NULL DEFAULT 'pending',
|
|
assignee_id uuid,
|
|
assigned_at timestamptz,
|
|
result varchar(16),
|
|
resulted_at timestamptz,
|
|
cancelled_reason text,
|
|
created_by uuid,
|
|
note text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_detection_tasks_source_key ON detection_tasks (source_key);
|
|
CREATE INDEX IF NOT EXISTS idx_detection_tasks_status ON detection_tasks (status);
|
|
CREATE INDEX IF NOT EXISTS idx_detection_tasks_room_id ON detection_tasks (room_id);
|
|
CREATE INDEX IF NOT EXISTS idx_detection_tasks_inspection_id ON detection_tasks (inspection_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS samples (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
detection_task_id uuid NOT NULL,
|
|
sample_no varchar(64) NOT NULL,
|
|
room_id uuid,
|
|
batch_id uuid,
|
|
tray_id uuid,
|
|
sampled_by uuid,
|
|
sampled_at timestamptz,
|
|
collected_at timestamptz,
|
|
handed_over_at timestamptz,
|
|
handed_over_by uuid,
|
|
received_at timestamptz,
|
|
received_by uuid,
|
|
testing_started_at timestamptz,
|
|
consumed_at timestamptz,
|
|
disposed_at timestamptz,
|
|
state varchar(16) NOT NULL DEFAULT 'created',
|
|
note text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_samples_detection_task_id ON samples (detection_task_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_samples_sample_no ON samples (sample_no);
|
|
|
|
CREATE TABLE IF NOT EXISTS disease_events (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
source_key varchar(128) NOT NULL,
|
|
room_id uuid,
|
|
batch_id uuid,
|
|
detection_task_id uuid,
|
|
lamp_test_id uuid,
|
|
consultation_id uuid,
|
|
inspection_id uuid,
|
|
disease varchar(64) NOT NULL,
|
|
status varchar(16) NOT NULL DEFAULT 'suspected',
|
|
evidence jsonb,
|
|
confirmed_at timestamptz,
|
|
confirmed_by uuid,
|
|
loss_summary text,
|
|
measure text,
|
|
note text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_disease_events_source_key ON disease_events (source_key);
|
|
CREATE INDEX IF NOT EXISTS idx_disease_events_status ON disease_events (status);
|
|
CREATE INDEX IF NOT EXISTS idx_disease_events_room_id ON disease_events (room_id);
|
|
|
|
ALTER TABLE trace_records
|
|
ADD COLUMN IF NOT EXISTS disease_event_id uuid;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_trace_records_disease_event_id ON trace_records (disease_event_id);
|
|
|
|
ALTER TABLE lamp_tests
|
|
ADD COLUMN IF NOT EXISTS detection_task_id uuid;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_lamp_tests_detection_task_id ON lamp_tests (detection_task_id);
|