Wyndham-RSVN-0918/database/postgresql

PostgreSQL target schema: th_hotel_booking

This package is the isolated PostgreSQL V0.1 target model for the booking-email workflow. The current Spring runtime remains on its existing MySQL/H2 migration line; these scripts must not be added to that Flyway location or executed against public.

Scope

• Owns only schema th_hotel_booking and objects qualified with that schema.
• Covers source mail/revision evidence, deterministic processing runs, normalized facts, order tasks/cards, task relations and confirmation history.
• Keeps explicit retention_until values with a three-month default. No deletion scheduler is included in V0.1.
• Does not contain PMS/Opera execution tables or commands.
• Does not create roles, extensions, databases, objects in public, or objects in another project schema.

Safe execution order

1. Connect to the explicitly approved database with an interactive password prompt or a secret environment injected outside the repository.
2. Run preflight_read_only.sql and retain its output.
3. Confirm can_create_schema=true and inspect any existing th_hotel_booking objects. If an unexpected object exists, stop.
4. Run V001__bootstrap_th_hotel_booking.sql with ON_ERROR_STOP=1 in one transaction.
5. Run postflight_read_only.sql and compare non-project schema counts with the preflight output.

Example (password is prompted and never placed in the command):

psql -X -W -h <host> -p <port> -U <user> -d <database> -v ON_ERROR_STOP=1 -f database/postgresql/preflight_read_only.sql
psql -X -W -h <host> -p <port> -U <user> -d <database> -v ON_ERROR_STOP=1 -1 -f database/postgresql/V001__bootstrap_th_hotel_booking.sql
psql -X -W -h <host> -p <port> -U <user> -d <database> -v ON_ERROR_STOP=1 -f database/postgresql/postflight_read_only.sql

Primary supported queries

• Find one actual email and all processing revisions by (hotel_id, provider, external_message_id).
• Read a conversation in received order through external_conversation_id.
• List open/review-required tasks and cards by hotel, type and status.
• Follow linked Trace/Allotment relationships through task_relation.
• Reconstruct who confirmed or overrode a card and when.
• Find records whose three-month retention window has expired without deleting them automatically.