Database Design
Event Store · Read Models · Time-series

CREATE TABLE events (
    id              UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type  VARCHAR(100) NOT NULL,
    aggregate_id    UUID         NOT NULL,
    sequence_number BIGINT       NOT NULL,
    event_type      VARCHAR(200) NOT NULL,
    payload         JSONB        NOT NULL,
    metadata        JSONB        NOT NULL DEFAULT '{}',
    correlation_id  VARCHAR(100),
    causation_id    VARCHAR(100),
    tenant_id       VARCHAR(50)  NOT NULL DEFAULT 'default',
    occurred_at     TIMESTAMPTZ  NOT NULL,
    recorded_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    -- Optimistic locking: no two events same aggregate + sequence
    CONSTRAINT uq_events_aggregate_seq
        UNIQUE (aggregate_type, aggregate_id, sequence_number)
);

-- Indexes
CREATE INDEX idx_events_aggregate
    ON events (aggregate_type, aggregate_id, sequence_number);

CREATE INDEX idx_events_type
    ON events (event_type, occurred_at DESC);

CREATE INDEX idx_events_correlation
    ON events (correlation_id)
    WHERE correlation_id IS NOT NULL;

CREATE INDEX idx_events_tenant
    ON events (tenant_id, recorded_at DESC);

-- Partition by recorded_at for large-scale (optional Phase 3)
-- PARTITION BY RANGE (recorded_at)

CREATE TABLE snapshots (
    id             UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type VARCHAR(100) NOT NULL,
    aggregate_id   UUID        NOT NULL,
    at_sequence    BIGINT      NOT NULL,
    state          JSONB       NOT NULL,
    tenant_id      VARCHAR(50) NOT NULL DEFAULT 'default',
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Chỉ giữ snapshot mới nhất mỗi aggregate
    CONSTRAINT uq_snapshots_aggregate
        UNIQUE (aggregate_type, aggregate_id)
);

CREATE INDEX idx_snapshots_aggregate
    ON snapshots (aggregate_type, aggregate_id);

-- Trigger snapshot sau mỗi 500 events (thực hiện ở Application layer)

CREATE TABLE outbox (
    id             UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type VARCHAR(100) NOT NULL,
    aggregate_id   UUID        NOT NULL,
    event_type     VARCHAR(200) NOT NULL,
    payload        JSONB       NOT NULL,
    kafka_topic    VARCHAR(200) NOT NULL,
    kafka_key      VARCHAR(200) NOT NULL,
    -- PENDING | PUBLISHED | FAILED
    status         VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    retry_count    INT         NOT NULL DEFAULT 0,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    published_at   TIMESTAMPTZ
);

CREATE INDEX idx_outbox_pending
    ON outbox (created_at)
    WHERE status = 'PENDING';

-- Polling interval: 100ms. Debezium CDC là alternative tốt hơn.

CREATE TABLE production_orders (
    id             UUID         PRIMARY KEY,
    order_number   VARCHAR(50)  NOT NULL UNIQUE,
    product_id     UUID         NOT NULL,
    product_code   VARCHAR(100) NOT NULL,
    planned_qty    INT          NOT NULL,
    actual_qty     INT          NOT NULL DEFAULT 0,
    scrap_qty      INT          NOT NULL DEFAULT 0,
    -- PLANNED | IN_PROGRESS | ON_HOLD | COMPLETED | CANCELLED
    status         VARCHAR(30)  NOT NULL DEFAULT 'PLANNED',
    machine_id     UUID,
    machine_code   VARCHAR(50),
    operator_id    UUID,
    operator_name  VARCHAR(100),
    shift          VARCHAR(10),   -- MORNING | AFTERNOON | NIGHT
    tenant_id      VARCHAR(50)  NOT NULL DEFAULT 'default',
    planned_start  TIMESTAMPTZ,
    actual_start   TIMESTAMPTZ,
    completed_at   TIMESTAMPTZ,
    updated_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_prod_orders_status  ON production_orders (status, planned_start DESC);
CREATE INDEX idx_prod_orders_machine ON production_orders (machine_id, work_date);
CREATE INDEX idx_prod_orders_tenant  ON production_orders (tenant_id, updated_at DESC);

CREATE TABLE stock_levels (
    id             UUID         PRIMARY KEY,
    material_id    UUID         NOT NULL,
    material_code  VARCHAR(100) NOT NULL,
    material_name  VARCHAR(200) NOT NULL,
    location_id    UUID         NOT NULL REFERENCES inventory_locations(id),
    on_hand_qty    DECIMAL(15,4) NOT NULL DEFAULT 0,
    reserved_qty   DECIMAL(15,4) NOT NULL DEFAULT 0,
    -- available = on_hand - reserved
    available_qty  DECIMAL(15,4) GENERATED ALWAYS AS
                   (on_hand_qty - reserved_qty) STORED,
    reorder_point  DECIMAL(15,4) NOT NULL DEFAULT 0,
    unit           VARCHAR(20)  NOT NULL,
    tenant_id      VARCHAR(50)  NOT NULL DEFAULT 'default',
    updated_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_stock_material_location
        UNIQUE (material_id, location_id, tenant_id)
);

-- Cảnh báo reorder: query materialized view hàng giờ
CREATE MATERIALIZED VIEW mv_reorder_alerts AS
SELECT * FROM stock_levels
WHERE available_qty <= reorder_point
  AND reorder_point > 0;

CREATE UNIQUE INDEX ON mv_reorder_alerts (id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_reorder_alerts;

CREATE TABLE sensor_readings (
    id           BIGSERIAL,
    machine_code VARCHAR(50)   NOT NULL,
    reading_type VARCHAR(50)   NOT NULL,  -- cycle_count | good_count | reject_count | cycle_time_ms | power_kw | fault_code
    value        NUMERIC(18,4) NOT NULL,
    unit         VARCHAR(20),
    source       VARCHAR(100),
    tenant_id    VARCHAR(50)   NOT NULL DEFAULT 'default',
    recorded_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (recorded_at);

CREATE TABLE machine_uptime_events (
    id           BIGSERIAL,
    machine_code VARCHAR(50)   NOT NULL,
    event_type   VARCHAR(30)   NOT NULL,  -- start | stop | fault | resume | planned_stop
    reason       TEXT,
    source       VARCHAR(100),
    tenant_id    VARCHAR(50)   NOT NULL DEFAULT 'default',
    recorded_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (recorded_at);

-- Monthly partitions created by DO $$ block in migration (13 months)
-- Example:
CREATE TABLE sensor_readings_2026_07
    PARTITION OF sensor_readings
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE oee_daily_snapshots (
    id               BIGSERIAL PRIMARY KEY,
    machine_code     VARCHAR(50)    NOT NULL,
    snapshot_date    DATE           NOT NULL,
    shift_code       VARCHAR(20)    NOT NULL DEFAULT 'Morning',
    planned_minutes  NUMERIC(10,2)  NOT NULL DEFAULT 480,
    uptime_minutes   NUMERIC(10,2)  NOT NULL DEFAULT 0,
    downtime_minutes NUMERIC(10,2)  NOT NULL DEFAULT 0,
    cycle_count      BIGINT         NOT NULL DEFAULT 0,
    good_count       BIGINT         NOT NULL DEFAULT 0,
    reject_count     BIGINT         NOT NULL DEFAULT 0,
    ideal_cycle_sec  NUMERIC(10,4),
    -- Generated columns (auto-computed by DB)
    oee_availability NUMERIC(6,2) GENERATED ALWAYS AS
        (CASE WHEN planned_minutes > 0 THEN ROUND(uptime_minutes / planned_minutes * 100, 2) END) STORED,
    oee_quality      NUMERIC(6,2) GENERATED ALWAYS AS
        (CASE WHEN cycle_count > 0 THEN ROUND(good_count::NUMERIC / cycle_count * 100, 2) END) STORED,
    -- Stored columns (computed by OeeFormulas.cs + upserted)
    oee_performance  NUMERIC(6,2),
    oee_total        NUMERIC(6,2),
    tenant_id        VARCHAR(50)    NOT NULL DEFAULT 'default',
    calculated_at    TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_oee_snapshot UNIQUE (machine_code, snapshot_date, shift_code, tenant_id)
);

CREATE TABLE chart_of_accounts (
    id             UUID PRIMARY KEY,
    account_number VARCHAR(12)  NOT NULL,
    name           VARCHAR(200) NOT NULL,
    account_type   VARCHAR(20)  NOT NULL,   -- Asset|Liability|Equity|Revenue|Expense
    parent_number  VARCHAR(12),
    is_postable    BOOLEAN      NOT NULL DEFAULT TRUE,
    is_active      BOOLEAN      NOT NULL DEFAULT TRUE,
    tenant_id      VARCHAR(50)  NOT NULL,

    -- Cây tài khoản dựa trên TIỀN TỐ số hiệu: 111 -> 1111. Gán cha tuỳ ý làm việc
    -- cộng dồn lên cấp trên ra số vô nghĩa mà bảng cân đối vẫn cân — không ai phát hiện.
    CONSTRAINT chart_of_accounts_parent_prefix_check
        CHECK (parent_number IS NULL
               OR (length(account_number) > length(parent_number)
                   AND account_number LIKE parent_number || '%'))
);

CREATE TABLE accounting_periods (
    id                UUID PRIMARY KEY,
    year              INTEGER     NOT NULL,
    month             INTEGER     NOT NULL,
    fiscal_year_start DATE        NOT NULL,  -- số dư đầu kỳ của TK doanh thu/chi phí tính từ đây
    is_closed         BOOLEAN     NOT NULL DEFAULT FALSE,
    closed_at         TIMESTAMPTZ,
    closed_by         VARCHAR(100),
    tenant_id         VARCHAR(50) NOT NULL
);

CREATE TABLE gl_postings (
    entry_id       UUID          NOT NULL,
    line_number    INTEGER       NOT NULL,
    entry_number   VARCHAR(30)   NOT NULL,
    posting_date   DATE          NOT NULL,
    account_number VARCHAR(12)   NOT NULL,
    debit          NUMERIC(20,2) NOT NULL DEFAULT 0,
    credit         NUMERIC(20,2) NOT NULL DEFAULT 0,
    cost_center    VARCHAR(50),
    source         VARCHAR(20)   NOT NULL,
    tenant_id      VARCHAR(50)   NOT NULL,

    PRIMARY KEY (entry_id, line_number),
    -- Một dòng chỉ ghi MỘT bên; cho cả hai bên thì mọi phép cộng theo bên mất ý nghĩa.
    CONSTRAINT gl_posting_one_side_check
        CHECK (debit >= 0 AND credit >= 0 AND NOT (debit > 0 AND credit > 0))
);

CREATE UNIQUE INDEX ux_journal_auto_reference
    ON journal_entries (tenant_id, reference_type, reference_id)
    WHERE source <> 'Manual' AND reference_type IS NOT NULL;

-- Nghiệp vụ KHÔNG vào được sổ để lại một dòng ở đây kèm lý do, có đường ghi lại.
-- Sổ sách thiếu một nghiệp vụ mà không ai biết hỏng nặng hơn nhiều so với
-- một hàng đợi lỗi cần người xem.
CREATE TABLE ledger_posting_failures (
    id             UUID PRIMARY KEY,
    rule_code      VARCHAR(40)   NOT NULL,
    reference_type VARCHAR(50)   NOT NULL,
    reference_id   VARCHAR(100)  NOT NULL,
    amount         NUMERIC(20,2) NOT NULL DEFAULT 0,
    error_code     VARCHAR(50)   NOT NULL,
    error_message  TEXT          NOT NULL,
    payload        JSONB         NOT NULL,
    resolved_at    TIMESTAMPTZ,
    tenant_id      VARCHAR(50)   NOT NULL
);

ALTER TABLE outbox ADD COLUMN seq BIGSERIAL;

DROP INDEX idx_outbox_pending;
CREATE INDEX idx_outbox_pending ON outbox (seq) WHERE status = 'PENDING';
-- Publisher đổi ORDER BY created_at -> ORDER BY seq