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)
);