wyndham-ARR/DATABASE_ERD.md

ARR 数据落库 MVP v1 ERD

状态：与测试库已执行的 008_arr_mvp_v1_rebuild.sql 一致。

erDiagram
    INGESTION_ARTIFACTS ||--o{ INGESTION_PROCESSING_RUNS : "source/result"
    INGESTION_PROCESSING_RUNS ||--o{ INGESTION_PROCESSING_ATTEMPTS : retries
    INGESTION_PROCESSING_RUNS ||--o{ INGESTION_PROCESSING_DELIVERIES : receives
    INGESTION_PROCESSING_ATTEMPTS o|--o{ INGESTION_PROCESSING_DELIVERIES : delivers

    INGESTION_ARTIFACTS ||--o| BOOKING_SOURCE_BATCHES : "booking source"
    BOOKING_SOURCE_BATCHES ||--o{ BOOKING_SOURCE_ROWS : contains
    BOOKING_SOURCE_ROWS ||--o{ BOOKING_PARSE_VERSIONS : "Agent versions"
    BOOKING_PARSE_VERSIONS ||--o{ BOOKING_ROOM_ITEMS : splits
    BOOKING_SOURCE_ROWS ||--o| BOOKING_CURRENT_ROW_PARSES : current
    BOOKING_PARSE_VERSIONS ||--o| BOOKING_CURRENT_ROW_PARSES : accepted

    INGESTION_PROCESSING_RUNS ||--o| FINANCE_DAILY_VERSIONS : produces
    INGESTION_ARTIFACTS ||--o{ FINANCE_DAILY_VERSIONS : "source/outputs"
    FINANCE_DAILY_VERSIONS ||--o{ FINANCE_DAILY_RECORDS : "all outcomes"
    FINANCE_DAILY_RECORDS o|--o{ FINANCE_DAILY_RECORDS : "duplicate of"
    FINANCE_DAILY_VERSIONS ||--o{ FINANCE_DAILY_CHANNEL_METRICS : channels
    FINANCE_DAILY_VERSIONS ||--o| FINANCE_CURRENT_DAILY_VERSIONS : current
    FINANCE_DAILY_VERSIONS o|--o{ INGESTION_PROCESSING_DELIVERIES : commits

    INGESTION_ARTIFACTS {
        bigint id PK
        text artifact_kind
        text storage_provider
        text bucket_alias
        text object_key
        char sha256
    }
    INGESTION_PROCESSING_RUNS {
        bigint id PK
        text run_key UK
        text pipeline_type
        text run_status
        date business_date
        char delivery_sha256
    }
    INGESTION_PROCESSING_ATTEMPTS {
        bigint id PK
        bigint processing_run_id FK
        int attempt_no
        char idempotency_key UK
        text attempt_status
    }
    INGESTION_PROCESSING_DELIVERIES {
        bigint id PK
        text delivery_key UK
        bigint processing_run_id FK
        bigint attempt_id FK
        char envelope_sha256
        text delivery_status
        bigint daily_version_id FK
    }
    INGESTION_OUTBOX_EVENTS {
        bigint id PK
        text event_key UK
        text aggregate_type
        bigint aggregate_id
        text event_type
        jsonb payload
    }
    BOOKING_SOURCE_BATCHES {
        bigint id PK
        bigint source_artifact_id FK
        text source_kind
        text batch_status
        int source_rows
    }
    BOOKING_SOURCE_ROWS {
        bigint id PK
        bigint source_batch_id FK
        text source_worksheet
        int source_row_no
        text group_code_raw
        text group_code_key
        text type_of_room_raw
        int no_of_rooms
    }
    BOOKING_PARSE_VERSIONS {
        bigint id PK
        bigint source_row_id FK
        int version_no
        text parse_status
        jsonb result_json
    }
    BOOKING_ROOM_ITEMS {
        bigint id PK
        bigint parse_version_id FK
        int item_no
        text room_type_raw
        text room_type_code
        int quantity
        numeric unit_price
    }
    BOOKING_CURRENT_ROW_PARSES {
        bigint source_row_id PK,FK
        bigint parse_version_id UK,FK
    }
    FINANCE_DAILY_VERSIONS {
        bigint id PK
        date business_date
        int version_no
        bigint processing_run_id FK
        text version_status
        char result_sha256
    }
    FINANCE_DAILY_RECORDS {
        bigint id PK
        bigint daily_version_id FK
        int source_sequence
        text outcome
        text res_comment
        text group_code_key
        numeric real_price
        numeric total_price
    }
    FINANCE_DAILY_CHANNEL_METRICS {
        bigint daily_version_id PK,FK
        text channel_key PK
        int channel_order
        int row_count
    }
    FINANCE_CURRENT_DAILY_VERSIONS {
        date business_date PK
        bigint daily_version_id FK
    }

ingestion.outbox_events.aggregate_id 是按 aggregate_type 解释的逻辑引用，因此图中不画物理 FK。

只读投影

flowchart LR
    C["finance.current_daily_versions"] --> A["finance.v_active_daily_facts<br/>current + retained"]
    D["finance.daily_records"] --> A
    B["booking.v_group_booking_rooms"] --> M["finance.v_monthly_report_rows"]
    B --> H["finance.v_channel_details"]
    B --> T["finance.v_company_report_source"]
    A --> M
    A --> H
    A --> T
    D --> Q["finance.v_daily_processing_audit<br/>all outcomes"]

• 预订来源行从不按 Group Code 合并；只有查询视图做汇总。
• 月报/渠道/公司报表行不重复存储。
• 正式业务查询只消费 current + retained；审计视图保留全部 outcome。
• 第一版单酒店，不含账户、权限、邮件或 RSVN 任务卡表。