Hybrid schema — column cứng + JSONB, khi nào migrate, zero-downtime pattern

-- DDL canonical TaskFlow voi hybrid schema
CREATE TABLE tasks (
  id          BIGSERIAL PRIMARY KEY,
  project_id  BIGINT NOT NULL REFERENCES projects(id),

  -- Common field (>50% query touch) -> column cung
  -- Type safety, B-tree index, FK constraint, planner statistics tot
  status      TEXT NOT NULL CHECK (status IN ('todo','doing','done','archived')),
  priority    TEXT CHECK (priority IN ('low','med','high','critical')),
  assignee_id BIGINT REFERENCES users(id),
  due_at      TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Per-tenant flexible field -> JSONB
  -- Moi tenant co the them key rieng ma khong can ALTER TABLE
  task_metadata JSONB NOT NULL DEFAULT '{}'
);

-- Index strategy: B-tree cho common field, GIN cho JSONB
CREATE INDEX idx_tasks_status_priority ON tasks(status, priority);
CREATE INDEX idx_tasks_assignee      ON tasks(assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX idx_tasks_due_at        ON tasks(due_at)      WHERE due_at IS NOT NULL;
CREATE INDEX idx_tasks_metadata      ON tasks USING gin(task_metadata jsonb_path_ops);

-- BUOC 1: Add column NULLABLE (khong NOT NULL -> khong block write)
-- ALTER TABLE ADD COLUMN tren PG 11+ la instant cho column NULLABLE co DEFAULT
ALTER TABLE tasks ADD COLUMN priority_v2 TEXT;

-- BUOC 2: Backfill bat dong bo (chunk nho de tranh lock lau)
-- Trong production: chia chunk theo id de tranh exclusive lock toan bang
UPDATE tasks
SET priority_v2 = task_metadata->>'priority'
WHERE task_metadata ? 'priority'
  AND priority_v2 IS NULL;
-- Lap lai voi chunk nho hon neu bang co >100k row:
-- WHERE id BETWEEN 1 AND 50000 AND task_metadata ? 'priority'

-- BUOC 3: Verify spot-check (ket qua phai la 0)
SELECT count(*)
FROM tasks
WHERE task_metadata ? 'priority'
  AND priority_v2 IS DISTINCT FROM task_metadata->>'priority';
-- Expect: 0 -- moi row co priority trong metadata deu match column moi

-- BUOC 4: App switch READ: doc tu priority_v2 thay vi task_metadata->>'priority'
-- Deploy app version moi doc tu column (van ghi ca 2)

-- BUOC 5: Dual-write window (vai ngay)
-- App ghi ca task_metadata->>'priority' va priority_v2 dong thoi
-- Dam bao backward compat neu can rollback ve app version cu

-- BUOC 6: App stop write JSONB key (sau khi confirm stable)
-- Deploy app version khong con ghi priority vao task_metadata

-- BUOC 7: Drop JSONB key (optional, sau khi confirm app stable)
UPDATE tasks
SET task_metadata = task_metadata - 'priority'
WHERE task_metadata ? 'priority';
-- Giam storage + tranh confusion doc 2 nguon data

-- BUOC 8: Rename + add constraint
ALTER TABLE tasks RENAME COLUMN priority_v2 TO priority;

-- NOT NULL chi add sau khi chac chan moi row co gia tri:
-- SELECT count(*) FROM tasks WHERE priority IS NULL; -- phai = 0
ALTER TABLE tasks ALTER COLUMN priority SET NOT NULL;

-- CHECK enum sau khi verify tat ca gia tri deu hop le:
-- SELECT DISTINCT priority FROM tasks; -- xem truoc khi add constraint
ALTER TABLE tasks ADD CONSTRAINT chk_tasks_priority
  CHECK (priority IN ('low','med','high','critical'));

-- Index B-tree cho column moi (neu chua co)
CREATE INDEX CONCURRENTLY idx_tasks_priority ON tasks(priority);

-- Dem so row co moi key trong task_metadata
SELECT
  key,
  count(*)                                   AS occurrence,
  round(count(*) * 100.0 / (SELECT count(*) FROM tasks), 1) AS pct_rows
FROM tasks,
     jsonb_object_keys(task_metadata) AS key
GROUP BY key
ORDER BY occurrence DESC
LIMIT 20;
-- Key co pct_rows > 50% -> candidate column
-- Key co pct_rows < 5%  -> giup trong JSONB, khong can migrate

-- Dem distinct value per key (gio candidate CHECK enum)
SELECT
  key,
  count(DISTINCT task_metadata ->> key) AS distinct_values,
  array_agg(DISTINCT task_metadata ->> key
            ORDER BY task_metadata ->> key) AS sample_values
FROM tasks,
     jsonb_object_keys(task_metadata) AS key
GROUP BY key
HAVING count(DISTINCT task_metadata ->> key) < 20
ORDER BY distinct_values;
-- distinct_values < 10 voi sample_values ro rang -> candidate CHECK enum

-- Dem query frequency tu pg_stat_statements (neu extension bat)
-- Tim cac query dung task_metadata->>'key' trong WHERE clause
SELECT query, calls, total_exec_time / calls AS avg_ms
FROM pg_stat_statements
WHERE query LIKE '%task_metadata%'
  AND query LIKE '%priority%'
ORDER BY calls DESC
LIMIT 10;
-- Nhieu calls -> field duoc query thuong xuyen -> migrate sang column

-- Common field cross tenant -> column
-- Custom field per tenant -> JSONB
CREATE TABLE tasks (
  id         BIGSERIAL PRIMARY KEY,
  tenant_id  BIGINT NOT NULL,
  project_id BIGINT NOT NULL,
  status     TEXT NOT NULL CHECK (status IN ('todo','doing','done','archived')),
  priority   TEXT CHECK (priority IN ('low','med','high','critical')),
  assignee_id BIGINT REFERENCES users(id),
  due_at     TIMESTAMPTZ,
  -- Tenant A: {"compliance_tag":"GDPR","reviewer_count":3}
  -- Tenant B: {"client_code":"ACME-001","sla_hours":48}
  -- Tenant C: {"sprint":12,"story_points":8,"epic":"security"}
  custom_fields JSONB NOT NULL DEFAULT '{}'
);

-- Khi 1 custom field tro thanh common cross-tenant (vu du "sla_hours"):
-- Audit thay 60% tenant dung key nay -> migrate len column theo 8 buoc

-- name/price/stock -> column (luon co, query thuong xuyen)
-- attributes -> JSONB (khac nhau tuy loai san pham)
CREATE TABLE products (
  id       BIGSERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  price    NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  stock    INT NOT NULL DEFAULT 0,
  -- Quan ao: {"color":"red","size":"M","material":"cotton"}
  -- Dien tu: {"brand":"Apple","storage":"256GB","warranty_months":12}
  -- Sach:    {"isbn":"978-...", "author":"Nguyen Van A","pages":320}
  attributes JSONB NOT NULL DEFAULT '{}'
);
-- Khong can 50 nullable column cho moi loai san pham

-- event_type/user_id/occurred_at -> column (query, filter, partition)
-- payload -> JSONB (schema khac nhau tuy event)
CREATE TABLE audit_logs (
  id          BIGSERIAL PRIMARY KEY,
  event_type  TEXT NOT NULL,
  user_id     BIGINT REFERENCES users(id),
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- task.update: {"task_id":42,"old":{"status":"todo"},"new":{"status":"done"}}
  -- user.login:  {"ip":"1.2.3.4","user_agent":"Mozilla/5.0","method":"oauth"}
  -- file.upload: {"filename":"report.pdf","size_bytes":1048576}
  payload     JSONB NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_audit_event_type ON audit_logs(event_type, occurred_at DESC);