Desain Database untuk Sistem Bisnis

-- Tabel products
id | name          | price   | stock
---|---------------|---------|------
1  | Kopi Arabika  | 85000   | 100
2  | Teh Hijau     | 35000   | 200
3  | Gula Pasir    | 15000   | 500

categories: id | name
products:   id | category_id (FK) | name | price

orders:       id | customer_id | total
products:     id | name | price
order_items:  order_id (FK) | product_id (FK) | qty | unit_price

users:         id | email | password_hash
user_profiles: user_id (FK UNIQUE) | full_name | phone | address

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id)
    ON DELETE SET NULL    -- jika kategori dihapus, category_id jadi NULL
    ON UPDATE CASCADE     -- jika id kategori berubah, ikut berubah
);

-- Pendekatan 1: is_active BOOLEAN
-- Sederhana, tapi hanya tahu "aktif atau tidak" — tidak tahu kapan dihapus
is_active BOOLEAN NOT NULL DEFAULT true
 
-- Pendekatan 2: deleted_at TIMESTAMPTZ (direkomendasikan)
-- Tahu kapan dihapus, bisa undelete, bisa TTL cleanup otomatis
deleted_at TIMESTAMPTZ DEFAULT NULL  -- NULL = aktif, ada value = sudah dihapus
 
-- Pendekatan 3: status ENUM
-- Paling fleksibel jika record punya banyak state
CREATE TYPE product_status AS ENUM ('active', 'inactive', 'discontinued', 'deleted');
status product_status NOT NULL DEFAULT 'active'

-- ✗ Akan conflict jika produk lama dengan SKU yang sama sudah di-soft-delete
CREATE UNIQUE INDEX idx_products_sku ON products(sku);
 
-- ✓ Partial unique index — hanya enforce uniqueness untuk yang belum dihapus
CREATE UNIQUE INDEX idx_products_sku_active
  ON products(sku)
  WHERE deleted_at IS NULL;

-- Tanpa soft delete filter → data "hantu" ikut muncul
SELECT * FROM products WHERE category_id = 1;
 
-- ✓ Dengan filter
SELECT * FROM products WHERE category_id = 1 AND deleted_at IS NULL;
 
-- Buat index partial untuk performa
CREATE INDEX idx_products_active ON products(category_id)
  WHERE deleted_at IS NULL;

SMALLINT          -- -32,768 to 32,767 (2 bytes)
INTEGER           -- -2.1 billion to 2.1 billion (4 bytes)
BIGINT            -- sangat besar (8 bytes)
SERIAL            -- INTEGER auto-increment (shorthand)
BIGSERIAL         -- BIGINT auto-increment
 
NUMERIC(12, 2)    -- exact decimal: 12 digit total, 2 desimal
                  -- SELALU gunakan ini untuk uang/harga!
FLOAT / REAL      -- floating point (tidak akurat untuk uang)

-- SERIAL / BIGSERIAL — auto-increment integer
id SERIAL PRIMARY KEY       -- 1, 2, 3, 4, ...
id BIGSERIAL PRIMARY KEY    -- gunakan ini jika tabel bisa > 2 miliar baris
 
-- UUID — identifier acak 128-bit
id UUID PRIMARY KEY DEFAULT gen_random_uuid()  -- tersedia di PostgreSQL 13+

-- Rekomendasi praktis untuk sistem bisnis:
-- Gunakan BIGSERIAL untuk tabel internal (products, categories, users internal)
-- Gunakan UUID untuk ID yang diekspos ke luar (order ID, invoice ID, public token)
 
CREATE TABLE orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- diekspos ke client
  user_id     BIGINT NOT NULL REFERENCES users(id),        -- internal FK
  ...
);

CHAR(n)           -- fixed length, padding dengan spasi
VARCHAR(n)        -- variable length dengan batas maksimum
TEXT              -- unlimited length
 
-- Rekomendasi: gunakan TEXT atau VARCHAR(n) jika perlu batasan
-- CHAR(n) jarang dibutuhkan di era modern

DATE              -- tanggal saja: 2024-01-15
TIME              -- waktu saja: 14:30:00
TIMESTAMP         -- tanggal + waktu, tanpa timezone
TIMESTAMPTZ       -- tanggal + waktu, dengan timezone (SELALU gunakan ini!)
INTERVAL          -- durasi: '2 hours', '3 days'

BOOLEAN           -- true / false
UUID              -- universally unique identifier
JSONB             -- JSON binary (bisa di-index, lebih cepat dari JSON)
ARRAY             -- array dari tipe apapun: INTEGER[], TEXT[]
ENUM              -- set nilai tetap (hati-hati, susah di-alter)

-- 1. Schema per-baris berbeda (product attributes)
-- Laptop punya ram/cpu/storage, baju punya size/material/color
-- Membuat 20 kolom nullable tidak masuk akal
CREATE TABLE products (
  id         BIGSERIAL PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  category   VARCHAR(100) NOT NULL,
  attributes JSONB DEFAULT '{}'  -- fleksibel per kategori
);
 
-- Insert laptop:
-- attributes: {"ram": "16GB", "cpu": "Intel i7", "storage": "512GB SSD"}
-- Insert baju:
-- attributes: {"size": "XL", "material": "Cotton", "color": "Navy"}
 
-- 2. Data dari third-party API yang strukturnya tidak kamu kontrol
CREATE TABLE webhook_logs (
  id         BIGSERIAL PRIMARY KEY,
  source     VARCHAR(100) NOT NULL,
  payload    JSONB NOT NULL,   -- simpan raw payload apa adanya
  received_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- 3. Metadata opsional yang jarang di-query
CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  email       VARCHAR(255) NOT NULL UNIQUE,
  preferences JSONB DEFAULT '{}'  -- theme, language, notification settings
);

-- ✗ Field yang selalu ada, konsisten, dan sering di-query
-- Ini seharusnya kolom biasa
attributes JSONB  -- {"name": "Kopi", "price": 25000, "stock": 100}
-- Kamu akan selalu query: WHERE attributes->>'price' > '20000'
-- Lebih baik: kolom price NUMERIC(12,2), stock INTEGER
 
-- ✗ Relasi yang seharusnya jadi tabel terpisah
-- Ini membuat integrasi data jadi susah
order_items JSONB  -- [{"product_id": 1, "qty": 2}, ...]
-- Tidak bisa FK, tidak bisa query efisien, tidak bisa aggregate

-- Index untuk field yang sering di-query di dalam JSONB
CREATE INDEX idx_products_category_attrs
  ON products USING GIN(attributes);
 
-- Query
SELECT * FROM products
WHERE attributes @> '{"ram": "16GB"}';  -- GIN index akan dipakai

-- ✓ BENAR
price       NUMERIC(12, 2)     -- bukan FLOAT
created_at  TIMESTAMPTZ        -- bukan TIMESTAMP
is_active   BOOLEAN            -- bukan INTEGER (0/1)
user_id     BIGINT             -- bukan INTEGER jika bisa besar
phone       VARCHAR(20)        -- bukan INTEGER (leading zero!)
 
-- ✗ SALAH
price       FLOAT              -- tidak akurat untuk uang
created_at  TIMESTAMP          -- tidak timezone-aware
is_active   SMALLINT           -- gunakan BOOLEAN
phone       INTEGER            -- "0812345678" kehilangan leading zero

-- B-tree (default) — untuk equality dan range query
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
 
-- Partial index — hanya index baris yang memenuhi kondisi
-- Lebih kecil dan lebih cepat daripada full index
CREATE INDEX idx_active_products ON products(id) WHERE is_active = true;
CREATE INDEX idx_pending_orders ON orders(created_at) WHERE status = 'pending';
 
-- Composite index — index beberapa kolom sekaligus
-- Urutan kolom penting! (a, b) cocok untuk WHERE a dan WHERE a AND b
-- tapi tidak untuk WHERE b saja
CREATE INDEX idx_products_category_active ON products(category_id, is_active);
 
-- Unique index — sekaligus constraint
CREATE UNIQUE INDEX idx_products_sku_unique ON products(sku) WHERE sku IS NOT NULL;
 
-- GIN index — untuk full-text search dan JSONB
CREATE INDEX idx_products_search ON products USING GIN(to_tsvector('indonesian', name || ' ' || COALESCE(description, '')));

-- Lihat query plan (tanpa eksekusi)
EXPLAIN SELECT * FROM products WHERE sku = 'ABC-123';
 
-- Lihat query plan + waktu eksekusi aktual
EXPLAIN ANALYZE SELECT * FROM products WHERE sku = 'ABC-123';

// ✗ N+1 — 1 query untuk orders, lalu 1 query PER order untuk customer
const orders = await db.query('SELECT * FROM orders LIMIT 20');  // 1 query
 
for (const order of orders) {
  const customer = await db.query(                               // 20 query!
    'SELECT * FROM customers WHERE id = $1',
    [order.customer_id]
  );
  order.customer = customer.rows[0];
}
// Total: 21 query ke database untuk menampilkan 1 halaman

-- ✓ Solusi: satu JOIN — satu query, apapun jumlah barisnya
SELECT
  o.id,
  o.total_amount,
  o.created_at,
  c.name   AS customer_name,
  c.email  AS customer_email
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
ORDER BY o.created_at DESC
LIMIT 20;

-- OFFSET pagination — terlihat sederhana
SELECT * FROM transactions ORDER BY created_at DESC LIMIT 10 OFFSET 990;
 
-- Yang tidak terlihat: PostgreSQL tetap membaca 1000 baris pertama,
-- lalu membuang 990 di antaranya. Semakin dalam halamannya, semakin lambat.
-- OFFSET 10000 = scan 10.010 baris hanya untuk menampilkan 10.

-- Halaman pertama
SELECT * FROM transactions
ORDER BY created_at DESC, id DESC
LIMIT 10;
-- Simpan nilai created_at dan id dari baris terakhir sebagai cursor
 
-- Halaman berikutnya — tidak ada OFFSET, langsung dari cursor
SELECT * FROM transactions
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 10;
-- PostgreSQL langsung pakai index, tidak ada baris yang dibuang

-- Index yang dibutuhkan untuk cursor pagination
CREATE INDEX idx_transactions_cursor ON transactions(created_at DESC, id DESC);

-- ✗ Jangan pakai fungsi di kolom index (index tidak akan dipakai)
WHERE LOWER(email) = 'user@email.com'
-- ✓ Gunakan functional index atau simpan dalam lowercase
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
 
-- ✗ Jangan buat index di semua kolom (memperlambat write operations)
-- Index memiliki biaya: setiap INSERT/UPDATE/DELETE juga harus update index
 
-- ✗ Jangan pakai LIKE dengan leading wildcard (tidak pakai index)
WHERE name LIKE '%kopi%'
-- ✓ Gunakan full-text search untuk ini

-- ============================================================
-- PRODUK & INVENTORY
-- ============================================================
 
CREATE TABLE categories (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(100) NOT NULL UNIQUE,
  parent_id   INTEGER REFERENCES categories(id) ON DELETE SET NULL,
  is_active   BOOLEAN NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE products (
  id            SERIAL PRIMARY KEY,
  name          VARCHAR(255) NOT NULL,
  description   TEXT,
  sku           VARCHAR(100) UNIQUE,
  barcode       VARCHAR(100) UNIQUE,
  category_id   INTEGER REFERENCES categories(id) ON DELETE SET NULL,
  unit          VARCHAR(50) NOT NULL DEFAULT 'pcs',  -- pcs, kg, liter, lusin
  cost_price    NUMERIC(12, 2) NOT NULL DEFAULT 0,   -- harga modal
  selling_price NUMERIC(12, 2) NOT NULL,              -- harga jual
  stock         INTEGER NOT NULL DEFAULT 0,
  min_stock     INTEGER NOT NULL DEFAULT 0,           -- alert jika di bawah ini
  is_active     BOOLEAN NOT NULL DEFAULT true,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Riwayat pergerakan stok
CREATE TABLE stock_movements (
  id            BIGSERIAL PRIMARY KEY,
  product_id    INTEGER NOT NULL REFERENCES products(id),
  type          VARCHAR(50) NOT NULL,  -- 'sale', 'purchase', 'adjustment', 'return'
  quantity      INTEGER NOT NULL,       -- positif = masuk, negatif = keluar
  quantity_before INTEGER NOT NULL,
  quantity_after  INTEGER NOT NULL,
  reference_id  INTEGER,               -- ID transaksi/penyesuaian terkait
  reference_type VARCHAR(50),
  notes         TEXT,
  created_by    INTEGER REFERENCES users(id),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- ============================================================
-- PELANGGAN
-- ============================================================
 
CREATE TABLE customers (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  phone       VARCHAR(20),
  email       VARCHAR(255),
  address     TEXT,
  total_purchases NUMERIC(15, 2) NOT NULL DEFAULT 0,
  total_visits    INTEGER NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- ============================================================
-- TRANSAKSI PENJUALAN
-- ============================================================
 
CREATE TYPE payment_method AS ENUM ('cash', 'qris', 'debit_card', 'credit_card', 'transfer');
CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'cancelled', 'refunded');
 
CREATE TABLE transactions (
  id              BIGSERIAL PRIMARY KEY,
  invoice_number  VARCHAR(50) NOT NULL UNIQUE,  -- e.g., INV-20240115-0001
  customer_id     INTEGER REFERENCES customers(id) ON DELETE SET NULL,
  cashier_id      INTEGER NOT NULL REFERENCES users(id),
  status          transaction_status NOT NULL DEFAULT 'completed',
 
  -- Totals
  subtotal        NUMERIC(12, 2) NOT NULL,
  discount_amount NUMERIC(12, 2) NOT NULL DEFAULT 0,
  tax_amount      NUMERIC(12, 2) NOT NULL DEFAULT 0,
  total_amount    NUMERIC(12, 2) NOT NULL,
 
  -- Payment
  payment_method  payment_method NOT NULL,
  amount_paid     NUMERIC(12, 2) NOT NULL,
  change_amount   NUMERIC(12, 2) NOT NULL DEFAULT 0,
 
  notes           TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE transaction_items (
  id              BIGSERIAL PRIMARY KEY,
  transaction_id  BIGINT NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
  product_id      INTEGER NOT NULL REFERENCES products(id),
  product_name    VARCHAR(255) NOT NULL,  -- snapshot nama saat transaksi
  product_sku     VARCHAR(100),           -- snapshot SKU saat transaksi
  quantity        INTEGER NOT NULL CHECK (quantity > 0),
  unit_price      NUMERIC(12, 2) NOT NULL,  -- harga jual saat transaksi
  unit_cost       NUMERIC(12, 2) NOT NULL,  -- harga modal saat transaksi (untuk profit)
  discount_amount NUMERIC(12, 2) NOT NULL DEFAULT 0,
  subtotal        NUMERIC(12, 2) NOT NULL   -- (unit_price * quantity) - discount
);
 
-- ============================================================
-- INDEX
-- ============================================================
 
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_barcode ON products(barcode);
CREATE INDEX idx_products_active ON products(id) WHERE is_active = true;
CREATE INDEX idx_transactions_date ON transactions(created_at DESC);
CREATE INDEX idx_transactions_cashier ON transactions(cashier_id);
CREATE INDEX idx_transaction_items_product ON transaction_items(product_id);
CREATE INDEX idx_stock_movements_product ON stock_movements(product_id, created_at DESC);

-- Tambahkan di tabel yang perlu dilacak
CREATE TABLE products (
  ...
  created_by  INTEGER REFERENCES users(id),
  updated_by  INTEGER REFERENCES users(id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE audit_logs (
  id           BIGSERIAL PRIMARY KEY,
  table_name   VARCHAR(100) NOT NULL,
  record_id    BIGINT NOT NULL,
  action       VARCHAR(10) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  old_values   JSONB,              -- NULL untuk INSERT
  new_values   JSONB,              -- NULL untuk DELETE
  changed_by   INTEGER REFERENCES users(id),
  changed_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_audit_logs_table_record
  ON audit_logs(table_name, record_id, changed_at DESC);

CREATE OR REPLACE FUNCTION audit_trigger_fn()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_logs(table_name, record_id, action, new_values)
    VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_logs(table_name, record_id, action, old_values, new_values)
    VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_logs(table_name, record_id, action, old_values)
    VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;
 
-- Pasang trigger ke tabel yang perlu di-audit
CREATE TRIGGER products_audit
  AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

SELECT
  changed_at,
  old_values->>'selling_price' AS harga_lama,
  new_values->>'selling_price' AS harga_baru,
  changed_by
FROM audit_logs
WHERE table_name = 'products'
  AND record_id = 42
  AND action = 'UPDATE'
  AND old_values->>'selling_price' != new_values->>'selling_price'
ORDER BY changed_at DESC;

-- Penjualan hari ini
SELECT
  COUNT(*) AS total_transaksi,
  SUM(total_amount) AS total_pendapatan,
  SUM(total_amount - discount_amount) AS pendapatan_bersih,
  AVG(total_amount) AS rata_rata_per_transaksi
FROM transactions
WHERE
  status = 'completed'
  AND DATE(created_at AT TIME ZONE 'Asia/Jakarta') = CURRENT_DATE;
 
-- Produk terlaris bulan ini
SELECT
  ti.product_name,
  SUM(ti.quantity) AS total_terjual,
  SUM(ti.subtotal) AS total_pendapatan,
  SUM(ti.quantity * (ti.unit_price - ti.unit_cost)) AS total_profit
FROM transaction_items ti
JOIN transactions t ON ti.transaction_id = t.id
WHERE
  t.status = 'completed'
  AND DATE_TRUNC('month', t.created_at) = DATE_TRUNC('month', NOW())
GROUP BY ti.product_id, ti.product_name
ORDER BY total_terjual DESC
LIMIT 10;
 
-- Stok hampir habis
SELECT name, sku, stock, min_stock
FROM products
WHERE is_active = true AND stock <= min_stock
ORDER BY (stock::FLOAT / NULLIF(min_stock, 0));

-- ============================================================
-- TENANTS (Organisasi/Perusahaan)
-- ============================================================
 
CREATE TABLE tenants (
  id              SERIAL PRIMARY KEY,
  name            VARCHAR(255) NOT NULL,
  slug            VARCHAR(100) NOT NULL UNIQUE,  -- untuk subdomain: slug.app.com
  plan            VARCHAR(50) NOT NULL DEFAULT 'free',  -- free, starter, pro, enterprise
  status          VARCHAR(50) NOT NULL DEFAULT 'active',
  max_users       INTEGER NOT NULL DEFAULT 5,
  max_products    INTEGER NOT NULL DEFAULT 100,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- ============================================================
-- USERS & ROLES
-- ============================================================
 
CREATE TABLE users (
  id              SERIAL PRIMARY KEY,
  tenant_id       INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  email           VARCHAR(255) NOT NULL,
  password_hash   VARCHAR(255) NOT NULL,
  full_name       VARCHAR(255) NOT NULL,
  role            VARCHAR(50) NOT NULL DEFAULT 'member',  -- owner, admin, member
  is_active       BOOLEAN NOT NULL DEFAULT true,
  last_login_at   TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 
  -- Email unik per tenant (bukan global unique)
  UNIQUE(tenant_id, email)
);
 
-- ============================================================
-- CONTOH: FITUR UTAMA (gunakan tenant_id di semua tabel)
-- ============================================================
 
CREATE TABLE projects (
  id              SERIAL PRIMARY KEY,
  tenant_id       INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name            VARCHAR(255) NOT NULL,
  description     TEXT,
  status          VARCHAR(50) NOT NULL DEFAULT 'active',
  created_by      INTEGER NOT NULL REFERENCES users(id),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE tasks (
  id              BIGSERIAL PRIMARY KEY,
  tenant_id       INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  project_id      INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title           VARCHAR(500) NOT NULL,
  description     TEXT,
  status          VARCHAR(50) NOT NULL DEFAULT 'todo',
  priority        SMALLINT NOT NULL DEFAULT 2 CHECK (priority BETWEEN 1 AND 4),
  assigned_to     INTEGER REFERENCES users(id) ON DELETE SET NULL,
  due_date        DATE,
  created_by      INTEGER NOT NULL REFERENCES users(id),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- ============================================================
-- ROW-LEVEL SECURITY (RLS) — kunci keamanan multi-tenancy
-- ============================================================
 
-- Aktifkan RLS di setiap tabel multi-tenant
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
 
-- Policy: user hanya bisa akses data tenant mereka sendiri
-- (current_setting diset oleh aplikasi di awal setiap query)
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::INTEGER);
 
CREATE POLICY tenant_isolation ON tasks
  USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

// Setiap request, set tenant context sebelum query lain
async function withTenantContext(tenantId, callback) {
  const client = await pool.connect();
  try {
    await client.query(`SET LOCAL app.tenant_id = $1`, [tenantId]);
    return await callback(client);
  } finally {
    client.release();
  }
}
 
// Penggunaan di controller
export const getProjects = asyncHandler(async (req, res) => {
  const { tenantId } = req.user; // dari JWT
 
  const projects = await withTenantContext(tenantId, async (client) => {
    const result = await client.query(
      'SELECT * FROM projects WHERE status = $1 ORDER BY created_at DESC',
      ['active']
      // RLS secara otomatis menambahkan filter tenant_id!
    );
    return result.rows;
  });
 
  res.json({ success: true, data: projects });
});

-- Selalu mulai composite index dengan tenant_id
CREATE INDEX idx_projects_tenant ON projects(tenant_id, status);
CREATE INDEX idx_tasks_tenant_project ON tasks(tenant_id, project_id);
CREATE INDEX idx_tasks_tenant_assigned ON tasks(tenant_id, assigned_to) WHERE assigned_to IS NOT NULL;
CREATE INDEX idx_users_tenant_email ON users(tenant_id, email);

-- Step 1: Tambahkan kolom baru, isi dari kolom lama
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
UPDATE users SET full_name = name WHERE full_name IS NULL;
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
 
-- (Deploy kode baru yang nulis ke keduanya — name DAN full_name)
 
-- Step 2: Update kode untuk baca dari full_name
-- (Deploy kode yang hanya baca full_name)
 
-- Step 3: Hapus kolom lama
ALTER TABLE users DROP COLUMN name;

-- migrations/003_add_customer_loyalty.sql
 
-- Pastikan idempotent: bisa dijalankan berulang tanpa error
DO $$
BEGIN
  -- Tambah kolom loyalty_points jika belum ada
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'customers' AND column_name = 'loyalty_points'
  ) THEN
    ALTER TABLE customers ADD COLUMN loyalty_points INTEGER NOT NULL DEFAULT 0;
    ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(50) NOT NULL DEFAULT 'bronze';
  END IF;
END $$;
 
-- Index baru (CONCURRENTLY agar tidak lock tabel)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customers_loyalty
  ON customers(loyalty_tier, loyalty_points DESC);
 
-- Record bahwa migration ini sudah dijalankan
INSERT INTO schema_migrations (version, name, applied_at)
VALUES ('003', 'add_customer_loyalty', NOW())
ON CONFLICT (version) DO NOTHING;

CREATE TABLE IF NOT EXISTS schema_migrations (
  version     VARCHAR(20) PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  applied_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);