76 lines
3.2 KiB
PL/PgSQL
76 lines
3.2 KiB
PL/PgSQL
-- Explicit immutable OHIP JSON sources; existing XML and legacy deliveries remain valid.
|
|
BEGIN;
|
|
DO $$ BEGIN
|
|
IF current_database() <> 'booking_test' THEN
|
|
RAISE EXCEPTION 'ARR migration is allowed only in booking_test';
|
|
END IF;
|
|
IF to_regclass('ingestion.daily_review_cases') IS NULL THEN
|
|
RAISE EXCEPTION 'ARR migration 018 must be applied first';
|
|
END IF;
|
|
END $$;
|
|
ALTER TABLE ingestion.artifacts DROP CONSTRAINT artifacts_artifact_kind_check;
|
|
ALTER TABLE ingestion.artifacts ADD CONSTRAINT artifacts_artifact_kind_check CHECK (artifact_kind IN (
|
|
'booking_source_md', 'booking_excel', 'opera_xml', 'ohip_json', 'daily_xlsx', 'monthly_xlsx',
|
|
'channel_detail_xlsx', 'company_ten_day_xlsx', 'exception_xlsx', 'result_json',
|
|
'structured_result_json', 'manual_override_json'
|
|
));
|
|
ALTER TABLE ingestion.processing_runs DROP CONSTRAINT processing_runs_terminal_shape;
|
|
ALTER TABLE ingestion.processing_runs
|
|
ADD CONSTRAINT processing_runs_terminal_shape CHECK (
|
|
(
|
|
run_status = 'accepted'
|
|
AND failure_code IS NULL
|
|
AND validated_at IS NOT NULL
|
|
AND finished_at IS NOT NULL
|
|
AND (
|
|
pipeline_type <> 'opera_daily'
|
|
OR (
|
|
business_date IS NOT NULL
|
|
AND delivered_processor_version IS NOT NULL
|
|
AND delivered_rule_set_sha256 IS NOT NULL
|
|
AND result_schema_version IS NOT NULL
|
|
AND delivery_sha256 IS NOT NULL
|
|
AND (
|
|
(
|
|
result_delivery_mode = 'artifact_callback'
|
|
AND result_artifact_id IS NOT NULL
|
|
)
|
|
OR (
|
|
result_delivery_mode = 'direct_mcp'
|
|
AND result_artifact_id IS NULL
|
|
)
|
|
)
|
|
)
|
|
)
|
|
)
|
|
OR (
|
|
run_status IN ('rejected', 'failed')
|
|
AND failure_code IS NOT NULL
|
|
AND finished_at IS NOT NULL
|
|
)
|
|
OR (run_status = 'cancelled' AND finished_at IS NOT NULL)
|
|
OR (
|
|
run_status = 'awaiting_review'
|
|
AND pipeline_type = 'opera_daily'
|
|
AND result_delivery_mode = 'artifact_callback'
|
|
AND result_artifact_id IS NOT NULL
|
|
AND business_date IS NOT NULL
|
|
AND delivered_processor_version IS NOT NULL
|
|
AND delivered_rule_set_sha256 IS NOT NULL
|
|
AND result_schema_version IN ('4.0', '5.0')
|
|
AND delivery_sha256 IS NOT NULL
|
|
AND validated_at IS NOT NULL
|
|
AND finished_at IS NULL
|
|
)
|
|
OR run_status IN ('received', 'queued', 'running', 'validating')
|
|
);
|
|
ALTER TABLE ingestion.processing_runs DROP CONSTRAINT processing_runs_uploaded_filename_check;
|
|
ALTER TABLE ingestion.processing_runs ADD CONSTRAINT processing_runs_uploaded_filename_check CHECK (
|
|
uploaded_filename IS NULL OR (
|
|
char_length(uploaded_filename) BETWEEN 1 AND 255 AND uploaded_filename NOT IN ('.', '..')
|
|
AND uploaded_filename !~ '[[:cntrl:]/\\]'
|
|
AND (right(lower(uploaded_filename), 4) = '.xml' OR uploaded_filename = 'ARR.json')
|
|
)
|
|
);
|
|
COMMIT;
|