Tables


SchemaSpy Analysis of aonestar.public

Generated on Fri Sep 25 23:00 ICT 2026

XML Representation
Insertion Order Deletion Order
TABLES 173
VIEWS 5
COLUMNS 2366
Constraints 243
Anomalies 39
Routines 703

Database Properties

Database Type: PostgreSQL - 18.3 (Debian 18.3-1.pgdg12+1)

Schema public

standard public schema

Tables

Table / View Children Parents Columns Rows Type Comments
calendar_event 0 2 13 0 Table

An event / holiday in a calendar. end_date NULL = single day; set for ranges (season / multi-day).
settings_log 0 0 18 96290 Table
tax_details 0 2 3 5 Table
followup_status 1 0 2 2 Table

Create by KenG (2020/03/10), Status of follow up
rate_daily 0 2 14 57427 Table
flyway_schema_history 0 0 10 451 Table
registration_rates 0 6 31 26867 Table
deposit 0 3 26 179 Table
registration_history 0 0 60 0 Table
guest_request 0 5 16 14547 Table
z_region 0 0 4 3887 Table
yield_rule 2 0 6 1 Table
z_country 0 0 3 230 Table
channel_mapping 0 0 7 1 Table
rate_channels 0 2 2 0 Table
guest_signature 0 4 10 0 Table

Signature per stay/transaction: register_id=register card, folio_item_id=folio payment, booking_id=pre-check-in/tablet before check-in.
inclusion 3 2 10 18 Table
channel_type 1 0 2 13 Table
registration 17 16 76 12127 Table
x_recap 0 0 40 4712 Table
age_range 0 0 4 5 Table

Guest-age bands for the age_range report segment. Must be a gap-free, non-overlapping partition with exactly one open-ended top band; maintained through rpc.masterfile_age_range_get / _save. Ages outside every band (and NULL guest_age) are folded into the reports’ id = 999999 / ‘Others/Unknown’ row.
guest_consent_logs 0 2 7 0 Table

ตารางบันทึกประวัติประทับตราเวลาการกดยอมรับนโยบายของแขกแต่ละคนตามกฎหมาย PDPA
language 5 0 5 92 Table
folio_details 0 2 3 394649 Table
room_type 11 1 26 8 Table

max_adult, max_child, max_infant, max_pax: * 0 = not allow for booking * NULL = always allow (no maximum check)
market 8 0 3 15 Table
guest_log 0 0 18 1 Table
sales_target 0 0 14 0 Table
cash_sale 0 1 10 53 Table
department 12 2 12 58 Table
intf_sync_channel_logs 0 1 14 57 Table
channel 10 7 30 3913 Table
registration_recent 0 0 60 0 Table
room_restriction 0 1 11 71 Table
wakeup_call 0 2 9 0 Table

PMS wakeup-call source records for PABX integration.
outstanding 0 0 20 3 Table
room_activity 0 0 14 2502 Table
rate_inclusion 0 3 5 9 Table
calendar 2 0 8 0 Table

A named calendar (e.g. “Thai Public Holidays”). Groups calendar_event rows.
guest_history 0 1 26 11695 Table

Permanent per-stay guest history (1 row = 1 checked-out stay). Written by sp_upsert_guest_history (hot path, public.* only) from sp_update_guest_stat at check-out / cancel-checkout, and backfilled in bulk by sp_rebuild_guest_history (public.* UNION backup.). Survives the backup. purge (ARCHIVE_PURGE_DAYS) so guest history is never lost. Only registrations with status O (checked out) are stored, so cancelled and no-show registrations (status X) never appear as a visit.
booking_msg_guests 0 1 5 0 Table
regis 0 0 60 0 Table
nationality 5 2 12 266 Table
party 1 0 3 0 Table
channel_rates 0 4 12 0 Table
roomactivity 0 0 51 0 Table
rate_group 1 0 2 7 Table
room_status 0 1 13 287 Table
inventory_log 0 0 18 325 Table
reservation_type 4 0 3 7 Table
precheckin_legal_document 0 1 10 24 Table

Localized terms/privacy document content for the pre-checkin page. is_translated=false marks rows that are an English fallback copy, not yet translated.
guest_social 0 2 3 37 Table
room_block 0 3 13 547 Table
yield_rule_tier 0 1 7 9 Table

Matrix occupancy × DBA. match เมื่อ occ_percent ∈ [occ_from, occ_to) และ dba ∈ [lead_from, lead_to]; tier บนสุด (occ_to = 100) เป็น catch-all รวม occ >= 100 (overbook)

[occ_from, occ_to) และ dba ∈ [lead_from, lead_to]: ./null
guest_document 1 3 23 319 Table
room_usage 0 0 7 15455 Table
z_sysmsg 0 0 5 96 Table
ipu_agent 0 0 17 6 Table
origin 7 0 4 12 Table
booking_rates_bak 0 0 29 6629 Table
room_location 3 0 3 14 Table
guest_messages 0 2 7 89 Table
itemizer_summary 0 1 7 176 Table
ipu_roominfo 0 0 13 6618 Table
room_exposure 2 0 3 4 Table
charge_schedule 0 4 13 33539 Table
property_info 0 0 31 1 Table
draft 0 0 7 6 Table
credit_card 1 1 7 48 Table
rate_logs 0 0 14 64293 Table
chart 0 0 5 4 Table
user_alerts 0 2 9 1049 Table
department_group 1 0 5 18 Table
room_image 0 1 3 0 Table
notification 0 0 9 461 Table

Create by KenG (2020/03/17), Notify to user, Base on user
city 3 1 4 4057 Table
rate_details 6 5 40 267 Table

Max occupant (max_XXX): * 0 = not allow for booking * NULL = always allow (no maximum check)
rate_restriction 0 1 7 0 Table
booking_guests 0 4 6 13842 Table
x_city 0 0 3 4090 Table
room_group 1 0 3 4 Table
x_country 0 1 3 258 Table
occupation 2 0 3 150 Table
room_summary 0 20 61 227907 Table
intf_rate_state 0 0 11 0 Table
sys_message_category 0 0 2 2 Table
contact 0 1 11 0 Table
followup_comments 0 1 7 38 Table

Create by KenG (2020/03/11), Detail table for follow up
booking_rates 0 4 30 16607 Table
precheckin_consent_logs 0 4 10 0 Table

Consent choices captured on the guest pre-checkin form, before a registration_id exists. Written by rpc.precheckin_guest_consents_save.
registration_log 0 0 18 2032 Table
tax 4 0 3 3 Table
alert 1 1 4 31 Table

Create by KenG (2020/03/17), Alert to page of front-end
intf_agent 1 0 14 7 Table
booking_log 0 0 18 5156 Table
cashier_log 0 0 18 1818 Table
country 10 1 7 253 Table
inventory 0 0 16 342 Table
social_media 1 0 6 5 Table
currencies 4 0 3 0 Table
group_info 2 5 18 8 Table
notification_followup 0 0 2 445 Table

Create by KenG (2020/03/23)
tax_item 1 0 5 3 Table
ooo_reason 1 0 3 406 Table
followup_seen 0 1 4 103 Table

Create by KenG (2020/03/12), This table for users_seen of topic(followup)
consent_master 3 0 6 4 Table

ตารางหลักเก็บหัวข้อความยินยอมและนโยบาย (PDPA/Hotel Policies)
intf_keycard_logs 0 3 13 22 Table
booking 13 9 57 14162 Table
transaction_summary 0 1 11 36 Table
allotment 0 2 4 0 Table
folio_pattern_seq 2 1 3 11 Table
leadtime_range 0 0 4 8 Table

Booking lead-time bands (in days) for the advance_book / lead_time report segments. Must be a gap-free, non-overlapping partition with exactly one open-ended top band; maintained through rpc.masterfile_leadtime_range_get / _save. Values outside every band are folded into the reports’ id = 999999 / ‘Others/Unknown’ row.
room_log 0 0 18 1876 Table
exchange_rate 0 0 9 0 Table

Currency exchange rate
guest 11 8 51 211612 Table
files 4 0 6 2 Table
meal_inclusion 0 2 2 0 Table
alert_page 2 0 2 6 Table

Create by KenG (2020/03/17), Masterfile Alert on Page of front-end, Front-End read here only, if want add data must add by developer
followup 3 2 16 113 Table

Create by KenG (2020/03/11), Master table for follow up
room_layout 0 1 5 14 Table
precheckin_legal_acceptance 0 3 9 0 Table

Audit trail of terms/privacy acceptance captured on the guest pre-checkin form, written by rpc.precheckin_legal_accept.
json_cache 0 0 3 4 Table
rsv_pickup 0 17 54 19267 Table
x_iso_country 0 0 4 249 Table
intf_room 0 2 11 936 Table
channel_restriction 0 3 9 0 Table
rate_plan_channels 0 2 3 3908 Table
record_lock 0 0 9 3 Table
x_block 0 0 11 59 Table
channel_group 1 0 2 3 Table
endday_tasks 0 0 5 25 Table
attachment 0 3 13 0 Table

ไฟล์แนบของ booking/registration — เก็บบน S3 (file_path) mint signed URL ตอน list; file_id (uuid -> public.files) เป็น legacy ไม่ใช้แล้ว; orphan S3 objects ถูกเก็บกวาดรายวันผ่าน fn_attachment_cleanup_orphans
yield_assignment 0 2 12 1 Table

ผูก yield_rule เข้ากับ rate_plan ตามช่วงวัน (from/to แบบ D/M/Y; ไม่ระบุปี = recurring ทุกปี) + DOW filter
endday 0 0 6 0 Table
intf_doorlock_logs 0 0 21 0 Table
contact_category 1 0 2 0 Table
transactions 5 8 39 1430 Table
alert_user 0 1 3 131 Table

Create by KenG (2020/03/17), alert to user (detail of alert)
sys_log 0 0 5 55 Table
consent_details 0 1 7 48 Table

ตารางเก็บรายละเอียดเนื้อหาข้อตกลง แยกตามรหัสภาษา (Localization)
building 2 0 3 3 Table
alert_followup 0 0 2 31 Table

Create by KenG (2020/03/20), Authorize between followup with alert table, Do not make foreign key because use trigger instead
room_upgrade 0 0 7 118 Table
ipu_agentcommand 0 0 2 23 Table
room_facility 0 0 3 15 Table
print_form 2 0 22 0 Table
bed_type 4 0 7 5 Table
recapitulate 0 0 75 4518 Table
followup_category 1 0 2 3 Table

Create by KenG (2020/03/10), Category of follow up
folio_items 2 2 18 39341 Table
sales_staff 5 0 3 4 Table
booking_items 5 7 43 16665 Table
x_room 0 0 11 258 Table
rate_plan 13 5 53 39 Table
itemizer 0 1 9 10 Table
x_booking 0 0 29 16160 Table
register_guests 0 2 2 139 Table
channel_market 0 2 2 11 Table
sys_messageXXX 0 0 3 285 Table
calendar_source 1 1 11 0 Table

External calendar feed config. source_type=ics_url pulls a Google/Outlook .ics URL into calendar_event.
folio_pattern 5 0 4 7 Table
folio 6 3 15 12210 Table
promotion 6 0 34 0 Table
interfaces 0 0 5 18 Table
meal_type 7 0 15 9 Table
precheckin_language 3 0 6 12 Table

Language options (code, display name, flag) shown on the guest pre-checkin page. Replaces the hardcoded VALUES(…) list previously in rpc.precheckin_lang_list.
title 1 0 6 11 Table
out_of_order 0 2 12 235 Table
x_countries 0 0 4 246 Table
folio_pattern_details 0 3 3 207 Table
vip 4 0 6 15 Table
room 9 6 12 287 Table
room_rate 0 1 6 0 Table
v_auto_post_result 0 0 15 0 View
sys_params 0 0 4 0 View
v_rate_details 0 0 38 0 View
v_room_activity 0 0 11 0 View
v_rateplan_link_paths 0 0 3 0 View