266 lines
13 KiB
SQL
266 lines
13 KiB
SQL
\set ON_ERROR_STOP on
|
|
|
|
CREATE SCHEMA IF NOT EXISTS th_hotel_booking AUTHORIZATION CURRENT_USER;
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.schema_migration (
|
|
version VARCHAR(32) PRIMARY KEY,
|
|
description VARCHAR(255) NOT NULL,
|
|
installed_by VARCHAR(128) NOT NULL DEFAULT CURRENT_USER,
|
|
installed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.source_message (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
hotel_id VARCHAR(64) NOT NULL,
|
|
provider VARCHAR(64) NOT NULL,
|
|
channel VARCHAR(32) NOT NULL,
|
|
external_message_id VARCHAR(256) NOT NULL,
|
|
external_conversation_id VARCHAR(256) NOT NULL,
|
|
message_sha256 CHAR(64) NOT NULL,
|
|
subject TEXT,
|
|
sender_summary TEXT,
|
|
source_sent_at TIMESTAMPTZ,
|
|
received_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
source_schema_version VARCHAR(64) NOT NULL,
|
|
original_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
retention_until TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '3 months'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uq_booking_source_actual_message
|
|
UNIQUE (hotel_id, provider, external_message_id),
|
|
CONSTRAINT ck_booking_source_sha256
|
|
CHECK (message_sha256 ~ '^[0-9a-f]{64}$'),
|
|
CONSTRAINT ck_booking_source_payload_object
|
|
CHECK (jsonb_typeof(original_payload) = 'object')
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_booking_source_conversation
|
|
ON th_hotel_booking.source_message (hotel_id, external_conversation_id, received_at, id);
|
|
CREATE INDEX IF NOT EXISTS idx_booking_source_retention
|
|
ON th_hotel_booking.source_message (retention_until);
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.source_attachment (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
source_message_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.source_message (id) ON DELETE CASCADE,
|
|
attachment_ordinal INTEGER NOT NULL,
|
|
file_name TEXT NOT NULL,
|
|
content_type VARCHAR(255),
|
|
size_bytes BIGINT NOT NULL,
|
|
content_sha256 CHAR(64) NOT NULL,
|
|
object_reference TEXT,
|
|
is_current_material BOOLEAN NOT NULL DEFAULT TRUE,
|
|
retention_until TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '3 months'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uq_booking_source_attachment_ordinal
|
|
UNIQUE (source_message_id, attachment_ordinal),
|
|
CONSTRAINT ck_booking_attachment_ordinal_positive
|
|
CHECK (attachment_ordinal > 0),
|
|
CONSTRAINT ck_booking_attachment_size_nonnegative
|
|
CHECK (size_bytes >= 0),
|
|
CONSTRAINT ck_booking_attachment_sha256
|
|
CHECK (content_sha256 ~ '^[0-9a-f]{64}$')
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_booking_attachment_source
|
|
ON th_hotel_booking.source_attachment (source_message_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.processing_run (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
source_message_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.source_message (id) ON DELETE CASCADE,
|
|
revision_number INTEGER NOT NULL,
|
|
run_status VARCHAR(32) NOT NULL,
|
|
catalog_version VARCHAR(64),
|
|
parser_version VARCHAR(64),
|
|
agent_profile_version VARCHAR(128),
|
|
input_fingerprint CHAR(64) NOT NULL,
|
|
warning_payload JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
completed_at TIMESTAMPTZ,
|
|
retention_until TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '3 months'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uq_booking_processing_revision
|
|
UNIQUE (source_message_id, revision_number),
|
|
CONSTRAINT ck_booking_processing_revision_positive
|
|
CHECK (revision_number > 0),
|
|
CONSTRAINT ck_booking_processing_status
|
|
CHECK (run_status IN ('RECEIVED', 'PARSING', 'AGENT_REVIEW', 'VALIDATED', 'TASKS_CREATED', 'FAILED')),
|
|
CONSTRAINT ck_booking_processing_fingerprint
|
|
CHECK (input_fingerprint ~ '^[0-9a-f]{64}$'),
|
|
CONSTRAINT ck_booking_processing_warnings_array
|
|
CHECK (jsonb_typeof(warning_payload) = 'array')
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_booking_processing_source
|
|
ON th_hotel_booking.processing_run (source_message_id, revision_number DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_booking_processing_status
|
|
ON th_hotel_booking.processing_run (run_status, started_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.booking_fact (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
processing_run_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.processing_run (id) ON DELETE CASCADE,
|
|
source_event_index INTEGER NOT NULL,
|
|
order_ref VARCHAR(160) NOT NULL,
|
|
event_type VARCHAR(48) NOT NULL,
|
|
booking_type VARCHAR(16),
|
|
names_payload JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
group_code VARCHAR(128),
|
|
group_name TEXT,
|
|
arrival_date DATE,
|
|
departure_date DATE,
|
|
room_items_payload JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
trace_items_payload JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
recognition_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
warning_payload JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
requires_review BOOLEAN NOT NULL DEFAULT TRUE,
|
|
retention_until TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '3 months'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uq_booking_fact_event_index
|
|
UNIQUE (processing_run_id, source_event_index),
|
|
CONSTRAINT ck_booking_fact_event_index_nonnegative
|
|
CHECK (source_event_index >= 0),
|
|
CONSTRAINT ck_booking_fact_event_type
|
|
CHECK (event_type IN ('NEW_BOOKING', 'UPDATE_BOOKING', 'CANCEL_BOOKING', 'TRACE_RESERVATION_NOTES', 'ROOMING_LIST', 'PAYMENT')),
|
|
CONSTRAINT ck_booking_fact_booking_type
|
|
CHECK (booking_type IS NULL OR booking_type IN ('FIT', 'GROUP')),
|
|
CONSTRAINT ck_booking_fact_names_array
|
|
CHECK (jsonb_typeof(names_payload) = 'array'),
|
|
CONSTRAINT ck_booking_fact_rooms_array
|
|
CHECK (jsonb_typeof(room_items_payload) = 'array'),
|
|
CONSTRAINT ck_booking_fact_traces_array
|
|
CHECK (jsonb_typeof(trace_items_payload) = 'array'),
|
|
CONSTRAINT ck_booking_fact_recognition_object
|
|
CHECK (jsonb_typeof(recognition_payload) = 'object'),
|
|
CONSTRAINT ck_booking_fact_warnings_array
|
|
CHECK (jsonb_typeof(warning_payload) = 'array'),
|
|
CONSTRAINT ck_booking_fact_stay_dates
|
|
CHECK (arrival_date IS NULL OR departure_date IS NULL OR departure_date > arrival_date)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_booking_fact_run_order
|
|
ON th_hotel_booking.booking_fact (processing_run_id, order_ref, source_event_index);
|
|
CREATE INDEX IF NOT EXISTS idx_booking_fact_type_review
|
|
ON th_hotel_booking.booking_fact (event_type, requires_review);
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.order_task (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
hotel_id VARCHAR(64) NOT NULL,
|
|
source_message_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.source_message (id) ON DELETE CASCADE,
|
|
processing_run_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.processing_run (id) ON DELETE CASCADE,
|
|
order_ref VARCHAR(160) NOT NULL,
|
|
idempotency_key VARCHAR(320) NOT NULL,
|
|
task_status VARCHAR(32) NOT NULL,
|
|
target_booking_type VARCHAR(16),
|
|
target_locator_type VARCHAR(32),
|
|
target_locator_value VARCHAR(256),
|
|
target_resolution_status VARCHAR(32) NOT NULL DEFAULT 'UNRESOLVED',
|
|
retention_until TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '3 months'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uq_booking_order_task_idempotency
|
|
UNIQUE (hotel_id, idempotency_key),
|
|
CONSTRAINT ck_booking_order_task_status
|
|
CHECK (task_status IN ('OPEN', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED')),
|
|
CONSTRAINT ck_booking_order_task_booking_type
|
|
CHECK (target_booking_type IS NULL OR target_booking_type IN ('FIT', 'GROUP')),
|
|
CONSTRAINT ck_booking_order_task_resolution
|
|
CHECK (target_resolution_status IN ('UNRESOLVED', 'RESOLVED', 'CONFLICT'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_booking_order_task_queue
|
|
ON th_hotel_booking.order_task (hotel_id, task_status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_booking_order_task_source
|
|
ON th_hotel_booking.order_task (source_message_id, processing_run_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.task_card (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
order_task_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.order_task (id) ON DELETE CASCADE,
|
|
source_event_index INTEGER,
|
|
card_type VARCHAR(64) NOT NULL,
|
|
event_type VARCHAR(48),
|
|
task_subtype VARCHAR(96),
|
|
card_status VARCHAR(32) NOT NULL,
|
|
review_status VARCHAR(32),
|
|
display_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
confirmed_payload JSONB,
|
|
manual_review_payload JSONB,
|
|
row_version BIGINT NOT NULL DEFAULT 0,
|
|
confirmed_by VARCHAR(128),
|
|
confirmed_at TIMESTAMPTZ,
|
|
retention_until TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '3 months'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uq_booking_task_card_source
|
|
UNIQUE (order_task_id, card_type, source_event_index),
|
|
CONSTRAINT ck_booking_task_card_status
|
|
CHECK (card_status IN ('READONLY', 'PENDING_CONFIRM', 'REVIEW_REQUIRED', 'CONFIRMED', 'CANCELLED')),
|
|
CONSTRAINT ck_booking_task_card_review
|
|
CHECK (review_status IS NULL OR review_status IN ('PENDING', 'RESOLVED')),
|
|
CONSTRAINT ck_booking_task_card_display_object
|
|
CHECK (jsonb_typeof(display_payload) = 'object'),
|
|
CONSTRAINT ck_booking_task_card_confirmed_object
|
|
CHECK (confirmed_payload IS NULL OR jsonb_typeof(confirmed_payload) = 'object'),
|
|
CONSTRAINT ck_booking_task_card_manual_review_object
|
|
CHECK (manual_review_payload IS NULL OR jsonb_typeof(manual_review_payload) = 'object'),
|
|
CONSTRAINT ck_booking_task_card_version_nonnegative
|
|
CHECK (row_version >= 0)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_booking_task_card_work_queue
|
|
ON th_hotel_booking.task_card (card_status, review_status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_booking_task_card_order
|
|
ON th_hotel_booking.task_card (order_task_id, id);
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.task_relation (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
source_order_task_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.order_task (id) ON DELETE CASCADE,
|
|
target_order_task_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.order_task (id) ON DELETE CASCADE,
|
|
relationship_type VARCHAR(96) NOT NULL,
|
|
relationship_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uq_booking_task_relation
|
|
UNIQUE (source_order_task_id, target_order_task_id, relationship_type),
|
|
CONSTRAINT ck_booking_task_relation_not_self
|
|
CHECK (source_order_task_id <> target_order_task_id),
|
|
CONSTRAINT ck_booking_task_relation_payload_object
|
|
CHECK (jsonb_typeof(relationship_payload) = 'object')
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_booking_task_relation_target
|
|
ON th_hotel_booking.task_relation (target_order_task_id, relationship_type);
|
|
|
|
CREATE TABLE IF NOT EXISTS th_hotel_booking.confirmation_action (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
order_task_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.order_task (id) ON DELETE CASCADE,
|
|
task_card_id BIGINT NOT NULL
|
|
REFERENCES th_hotel_booking.task_card (id) ON DELETE CASCADE,
|
|
action_type VARCHAR(32) NOT NULL,
|
|
field_overrides JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
reason TEXT,
|
|
actor_id VARCHAR(128) NOT NULL,
|
|
acted_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
retention_until TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '3 months'),
|
|
CONSTRAINT ck_booking_confirmation_action
|
|
CHECK (action_type IN ('CONFIRM', 'RESOLVE_REVIEW', 'REOPEN')),
|
|
CONSTRAINT ck_booking_confirmation_overrides_array
|
|
CHECK (jsonb_typeof(field_overrides) = 'array')
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_booking_confirmation_card_time
|
|
ON th_hotel_booking.confirmation_action (task_card_id, acted_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_booking_confirmation_actor_time
|
|
ON th_hotel_booking.confirmation_action (actor_id, acted_at DESC);
|
|
|
|
INSERT INTO th_hotel_booking.schema_migration (version, description)
|
|
VALUES ('001', 'bootstrap booking email to confirmation target model')
|
|
ON CONFLICT (version) DO NOTHING;
|
|
|