Lompat ke konten
·30 menit membaca

Desain Database Schema: Pola yang Bertahan Lama

Aturan normalisasi, konvensi penamaan, soft deletes, audit trails, pola multi-tenancy, strategi versioning, dan keputusan schema yang saya sesali. Fokus PostgreSQL.

databasepostgresqlarchitecturebackendsql
Bagikan:X / TwitterLinkedIn

Schema adalah kontrak dengan dirimu di masa depan. Setiap kolom yang kamu tambahkan, setiap constraint yang kamu lewatkan, setiap "kita rapikan nanti" — semuanya menumpuk. Saya pernah bekerja di sistem di mana satu keputusan schema yang buruk tiga tahun lalu sekarang menghabiskan biaya satu sprint penuh per kuartal untuk tim dalam bentuk workaround.

Database bertahan lebih lama dari segalanya. Framework frontend-mu akan berubah. Layer API-mu akan ditulis ulang. Strategi deployment-mu akan berevolusi. Tapi datanya? Data tetap ada. Dan bentuk yang kamu berikan pada hari pertama akan mengikutimu selamanya, karena migrasi tabel dengan 200 juta baris tidak sama dengan refactoring komponen React.

Ini yang telah saya pelajari tentang membuat keputusan schema yang tidak menghantuimu. Fokus PostgreSQL, karena itu yang saya gunakan dan percaya, tapi sebagian besar pola ini berlaku untuk database relasional mana pun.

Konvensi Penamaan: Hal Membosankan yang Paling Penting#

Saya sudah melihat lebih banyak perdebatan tentang konvensi penamaan daripada tentang keputusan arsitektur yang sebenarnya. Inilah yang telah saya tetapkan setelah bertahun-tahun berpindah konteks antar proyek:

Snake_case untuk semuanya. Tabel, kolom, indeks, constraint. Tidak ada camelCase, tidak ada PascalCase. PostgreSQL melipat identifier yang tidak dikutip menjadi huruf kecil, jadi createdAt menjadi createdat kecuali kamu mengutipnya dua kali di mana-mana. Jangan melawan database.

Nama tabel jamak. Sebuah tabel menampung banyak baris. users bukan user. orders bukan order. order_items bukan order_item. Ini terbaca alami dalam query: SELECT * FROM users WHERE ... — kamu memilih dari sebuah koleksi.

Akhiran _id untuk foreign key. user_id, order_id, tenant_id. Primary key hanya id. Ini tidak ambigu. Saat kamu join tabel, users.id = orders.user_id terbaca seperti bahasa sehari-hari.

Akhiran _at untuk timestamp. created_at, updated_at, deleted_at, published_at, expires_at. Kamu selalu tahu itu adalah titik waktu.

Awalan is_ untuk boolean. is_active, is_verified, is_published. Beberapa orang menggunakan has_ untuk boolean kepemilikan (has_mfa_enabled), tapi saya membuatnya sederhana dan menggunakan is_ untuk semuanya.

Akhiran _count untuk penghitung yang didenormalisasi. comment_count, follower_count. Memperjelas bahwa ini adalah angka yang di-cache, bukan kalkulasi langsung.

Inilah poinnya: konsistensi mengalahkan kesempurnaan. Saya pernah melihat tim menghabiskan berminggu-minggu berdebat apakah seharusnya email_address atau email atau email_addr. Pilih saja satu pola dan terapkan di mana-mana. Konvensi penamaan terburuk adalah yang diterapkan secara tidak konsisten.

sql
-- Baik: konsisten, terbaca, tidak ada kejutan
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL,
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    login_count     INTEGER NOT NULL DEFAULT 0,
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Buruk: casing tidak konsisten, konvensi campur aduk, nama ambigu
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- ini 0/1? level verifikasi?
    lastLogin       TIMESTAMP,       -- camelCase di dunia snake_case
    created         TIMESTAMP        -- created apa? kapan?
);

Satu hal lagi: jangan pernah menggunakan kata-kata cadangan sebagai nama kolom. type, order, user, group, table — semuanya berfungsi jika kamu mengutipnya, tapi akan membakarmu di ORM, query builder, dan setiap tool pembangkitan SQL dinamis. Gunakan kind alih-alih type, sort_order alih-alih order. Dirimu di masa depan akan berterima kasih.

Kolom Standar: Apa yang Didapat Setiap Tabel#

Setiap tabel dalam schema saya dimulai dengan kerangka yang sama. Tanpa pengecualian.

sql
CREATE TABLE things (
    id          BIGSERIAL PRIMARY KEY,
    -- ... kolom domain ...
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Debat UUID vs BIGSERIAL#

Ini adalah salah satu keputusan yang menghasilkan jauh lebih banyak panas daripada cahaya. Berikut pertukaran yang sebenarnya:

BIGSERIAL (auto-incrementing integer):

  • 8 byte. Ringkas. Cepat untuk diindeks dan di-join.
  • Berurutan — kamu bisa sort berdasarkan id untuk mendapatkan urutan penyisipan.
  • Dapat diprediksi — pengguna bisa menebak ID lain dengan menaikkan milik mereka.
  • Tidak bekerja dengan baik di sistem terdistribusi (membutuhkan koordinasi).

UUID v4 (acak):

  • 16 byte. Indeks lebih besar, join lebih lambat (tapi jarang menjadi bottleneck).
  • Tidak dapat diprediksi — tidak ada kebocoran informasi.
  • Bekerja di sistem terdistribusi tanpa koordinasi.
  • Lokalitas indeks yang buruk — UUID acak memecah indeks B-tree.

UUID v7 (terurut waktu, RFC 9562):

  • 16 byte, tapi terurut waktu sehingga lokalitas B-tree sangat baik.
  • Cukup tidak dapat diprediksi untuk penggunaan eksternal.
  • Relatif baru, tapi PostgreSQL 17+ memiliki gen_random_uuid() dan kamu bisa menggunakan uuid_generate_v7() dengan ekstensi.

Pendirian saya saat ini: BIGSERIAL untuk tabel internal, UUID v7 untuk apa pun yang terekspos ke dunia luar. Jika sebuah ID pernah muncul di URL, respons API, atau payload webhook, gunakan UUID. Jika itu murni tabel join yang pengguna tidak pernah lihat, BIGSERIAL sudah cukup.

sql
-- Untuk resource yang menghadap API
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Untuk tabel mapping internal
CREATE TABLE project_members (
    id          BIGSERIAL PRIMARY KEY,
    project_id  UUID NOT NULL REFERENCES projects(id),
    user_id     UUID NOT NULL REFERENCES users(id),
    role        TEXT NOT NULL DEFAULT 'member',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, user_id)
);

Timestamp: Selalu TIMESTAMPTZ#

Gunakan TIMESTAMPTZ, bukan TIMESTAMP. Versi "TZ" menyimpan nilai dalam UTC dan mengkonversi saat dibaca berdasarkan timezone sesi. Versi non-TZ menyimpan apa pun yang kamu berikan tanpa konteks timezone — yang berarti jika dua server di timezone berbeda menulis ke tabel yang sama, kamu mendapatkan korupsi data diam-diam.

sql
-- Selalu ini
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- Jangan pernah ini
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

Trigger updated_at#

PostgreSQL tidak memiliki ON UPDATE CURRENT_TIMESTAMP milik MySQL. Kamu memerlukan trigger:

sql
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Terapkan ke setiap tabel yang memiliki updated_at
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
 
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Ya, kamu perlu satu trigger per tabel. Ya, itu membosankan. Tulis helper migrasi yang membuat trigger secara otomatis saat kamu menambahkan tabel. Ini layak dilakukan, karena alternatifnya adalah mengingat untuk mengatur updated_at = NOW() di setiap query UPDATE di seluruh aplikasimu — dan kamu akan lupa.

Normalisasi: Kapan Melanggar Aturan#

Setiap mata kuliah ilmu komputer mengajarkan normalisasi sampai 3NF (Third Normal Form). Aturannya adalah:

  • 1NF: Setiap kolom menyimpan satu nilai atomik. Tidak ada array, tidak ada daftar yang dipisahkan koma.
  • 2NF: Setiap kolom non-kunci bergantung pada seluruh primary key (relevan untuk kunci komposit).
  • 3NF: Tidak ada dependensi transitif. Jika kolom A menentukan kolom B, dan B menentukan C, maka C seharusnya tidak berada di tabel yang sama dengan A.

Dalam praktik, 3NF adalah titik manis untuk tabel transaksional. Kamu harus memulai dari situ dan menyimpang hanya ketika kamu memiliki alasan spesifik yang terukur.

Berikut sistem order yang dinormalisasi dengan benar:

sql
CREATE TABLE customers (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    display_name    TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    sku             TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    price_cents     INTEGER NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customers(id),
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_cents     INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE order_items (
    id              BIGSERIAL PRIMARY KEY,
    order_id        BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id      BIGINT NOT NULL REFERENCES products(id),
    quantity        INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Perhatikan unit_price_cents pada order_items. Ini adalah denormalisasi yang disengaja. Kita menyimpan snapshot harga pada saat pesanan, karena harga produk mungkin berubah nanti. Ini adalah salah satu alasan paling umum dan benar untuk melakukan denormalisasi.

Kapan Denormalisasi Adalah Pilihan yang Tepat#

Tabel pelaporan. Jika dashboard analitikmu perlu join 8 tabel untuk di-render, buat tabel pelaporan yang didenormalisasi dan isi dengan background job. Schema transaksionalmu tetap bersih, dan query pelaporanmu tetap cepat.

sql
-- Tabel pelaporan yang didenormalisasi, diisi oleh cron job
CREATE TABLE order_reports (
    id                  BIGSERIAL PRIMARY KEY,
    order_id            BIGINT NOT NULL,
    customer_email      TEXT NOT NULL,
    customer_name       TEXT NOT NULL,
    product_names       TEXT[] NOT NULL,        -- array boleh di sini
    item_count          INTEGER NOT NULL,
    total_cents         INTEGER NOT NULL,
    order_status        TEXT NOT NULL,
    ordered_at          TIMESTAMPTZ NOT NULL,
    report_generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Materialized view. MATERIALIZED VIEW PostgreSQL kurang diapresiasi. Ini adalah snapshot denormalisasi yang kamu refresh sesuai permintaan. Sempurna untuk dashboard.

sql
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total_cents) AS revenue_cents,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
WHERE o.status != 'cancelled'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;
 
-- Refresh setiap malam
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Kolom JSON untuk atribut dinamis. Ketika baris yang berbeda membutuhkan field yang berbeda — varian produk, pengiriman formulir, preferensi pengguna — kolom JSONB sering kali lebih baik daripada tabel EAV (Entity-Attribute-Value) yang membuat pusing. Lebih lanjut tentang ini nanti.

Satu Aturan yang Tidak Pernah Saya Langgar#

Jangan pernah mendenormalisasi tabel sumber kebenaran (source-of-truth). Denormalisasi salinan, snapshot, laporan, dan cache. Data kanonik tetap dinormalisasi. Ketika salinan yang didenormalisasi menjadi basi atau rusak (dan itu akan terjadi), kamu membangunnya kembali dari sumber yang dinormalisasi.

Foreign Key & Constraint: Kode Terbaik yang Tidak Perlu Kamu Tulis#

Saya sudah mendengar setiap alasan untuk melewatkan foreign key. "Mereka memperlambat penulisan." "Kita menegakkannya di aplikasi." "Kita butuh fleksibilitas."

Semuanya salah.

Foreign key adalah satu-satunya hal yang paling berdampak yang bisa kamu tambahkan ke schema. Mereka mencegah seluruh kategori bug yang tidak bisa ditangkap oleh kode aplikasi sebanyak apa pun — race condition, kegagalan parsial, baris yatim piatu dari transaksi yang gagal. Foreign key adalah jaminan dari engine database itu sendiri, ditegakkan di level penyimpanan. Hook beforeDelete ORM-mu hanyalah saran.

sql
-- Selalu lakukan ini
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- Alternatif "kita tangani di kode":
-- Harapan. Harapan bukan strategi.

Strategi ON DELETE#

Di sinilah hal menjadi bernuansa. Apa yang terjadi ketika kamu menghapus baris parent?

RESTRICT (default): Penghapusan gagal jika ada baris child. Gunakan ini untuk sebagian besar relasi. Kamu tidak bisa menghapus customer yang punya pesanan — itu logika bisnis yang dikodekan dalam schema.

sql
-- Customer tidak bisa dihapus selama mereka punya pesanan
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Baris child secara otomatis dihapus. Gunakan ini secara hemat dan deliberatif. Baik untuk relasi "bagian-dari" di mana child tidak memiliki makna tanpa parent.

sql
-- Menghapus pesanan menghapus item barisnya — mereka tidak bermakna sendiri
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Menghapus proyek menghapus keanggotaannya
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: Kolom foreign key diatur ke NULL. Gunakan ini ketika relasi bersifat opsional dan baris child masih bermakna sendiri.

sql
-- Jika manajer keluar, bawahannya masih ada — hanya tanpa penugasan
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

Aturan praktis saya: default ke RESTRICT, gunakan CASCADE untuk relasi komposisi, gunakan SET NULL untuk asosiasi opsional. Jika kamu tidak yakin, RESTRICT selalu pilihan yang aman — lebih mudah melonggarkan constraint daripada memulihkan data yang terhapus.

Check Constraint: Asuransi Murah#

Check constraint hampir tidak memakan biaya saat menulis dan mencegah data sampah masuk ke sistemmu selamanya:

sql
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    discount_pct    NUMERIC(5,2) CHECK (discount_pct BETWEEN 0 AND 100),
    weight_kg       NUMERIC(10,3) CHECK (weight_kg > 0),
    status          TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Setiap check constraint yang kamu tambahkan adalah satu bug validasi lebih sedikit yang harus kamu debug di produksi. Database adalah garis pertahanan terakhir. Gunakan.

Unique Constraint dan Partial Unique Index#

Unique constraint cukup mudah untuk kasus sederhana:

sql
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Tapi menjadi menarik dengan partial index — constraint unik yang hanya berlaku untuk baris tertentu:

sql
-- Hanya satu langganan aktif per pengguna (tapi mereka bisa punya banyak yang dibatalkan)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Hanya satu alamat utama per pengguna
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

Ini adalah salah satu fitur unggulan PostgreSQL. Gunakan.

Soft Delete: Pola yang Semua Orang Suka Benci#

Soft delete sederhana konsepnya: alih-alih DELETE FROM users WHERE id = 42, kamu melakukan UPDATE users SET deleted_at = NOW() WHERE id = 42. Baris tetap di database tapi difilter dari query normal.

sql
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    display_name TEXT NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Partial index: filter baris yang dihapus secara efisien
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Setiap query di aplikasimu sekarang perlu WHERE deleted_at IS NULL. Setiap. Satu. Query. ORM membantu dengan ini (Prisma punya middleware, Drizzle punya .where(isNull(deletedAt))), tapi ini adalah pajak pada setiap operasi baca. Lewatkan sekali dan kamu menampilkan data "terhapus" ke pengguna.

Masalah Unique Constraint#

Di sinilah soft delete menjadi jelek. Jika kamu punya UNIQUE (email) dan pengguna melakukan soft-delete akunnya, email mereka masih di tabel. Mereka tidak bisa mendaftar ulang dengan email yang sama. Pengguna baru dengan email itu juga tidak bisa mendaftar.

Solusinya adalah partial unique index:

sql
-- Email harus unik, tapi hanya di antara pengguna yang tidak dihapus
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Ini berhasil, tapi sekarang kamu perlu mengingat pola ini untuk setiap kolom unik di setiap tabel yang bisa di-soft-delete. Bisa dikelola dengan disiplin, tapi ini adalah kompleksitas yang kamu pilih untuk ditanggung.

Masalah Foreign Key#

Soft delete berinteraksi buruk dengan foreign key. Jika orders.user_id merujuk users.id dengan ON DELETE RESTRICT, dan kamu melakukan soft-delete pengguna... tidak terjadi apa-apa. FK tidak terpicu karena kamu tidak benar-benar menghapus baris. Pengguna "hilang" dari perspektif aplikasi tapi masih sangat ada di database.

Ini berarti kode aplikasimu harus menangani kasus di mana entitas yang dirujuk sudah di-soft-delete. Setiap join, setiap lookup, setiap kali kamu mengikuti foreign key — kamu perlu memeriksa deleted_at IS NULL di tabel yang dirujuk juga. Atau tidak, dan aplikasimu menampilkan "Pesanan oleh [pengguna dihapus]" yang merupakan bug atau fitur tergantung siapa yang kamu tanya.

Pendekatan Saya#

Saya menggunakan soft delete hanya untuk entitas yang menghadap pengguna di mana pemulihan adalah kebutuhan bisnis — akun pengguna, proyek, dokumen. Hal-hal di mana agen dukungan mungkin perlu memulihkan penghapusan. Untuk yang lainnya, saya menggunakan hard delete dengan audit trail (lebih lanjut tentang ini berikutnya).

sql
-- Soft delete: menghadap pengguna, dapat dipulihkan
CREATE TABLE documents (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT NOT NULL,
    content     TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_documents_active ON documents (owner_id) WHERE deleted_at IS NULL;
 
-- Hard delete: internal, tidak dapat dipulihkan dari UI (tapi diaudit)
CREATE TABLE api_keys (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_hash    TEXT NOT NULL,
    last_used_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Saat dihapus, hilang. audit_log mencatat bahwa itu pernah ada.

Audit Trail: Ketahui Apa yang Berubah dan Siapa yang Melakukannya#

Setiap aplikasi non-trivial perlu jawaban untuk "apa yang terjadi pada record ini?" di suatu titik. Audit trail adalah cara kamu menyediakan jawaban itu tanpa menggali log aplikasi.

Pola Tabel Audit Terpisah#

Pendekatan paling sederhana: satu tabel audit_log yang mencatat setiap perubahan.

sql
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- text untuk menangani PK UUID dan BIGINT
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- NULL untuk INSERT
    new_values      JSONB,                  -- NULL untuk DELETE
    changed_fields  TEXT[],                 -- kolom mana yang berubah (untuk UPDATE)
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ip_address      INET,
    user_agent      TEXT
);
 
CREATE INDEX idx_audit_log_table_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_log_changed_by ON audit_log (changed_by);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Tantangannya adalah mengisinya. Kamu bisa melakukannya di kode aplikasi (eksplisit, tapi mudah lupa) atau dengan trigger (otomatis, tapi lebih sulit untuk meneruskan konteks seperti changed_by).

Pendekatan Berbasis Trigger#

Trigger menangkap setiap perubahan secara otomatis, bahkan dari SQL mentah atau operasi admin database:

sql
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    old_data JSONB;
    new_data JSONB;
    changed  TEXT[];
    col      TEXT;
BEGIN
    IF TG_OP = 'DELETE' THEN
        old_data := to_jsonb(OLD);
        INSERT INTO audit_log (table_name, record_id, operation, old_values, changed_at)
        VALUES (TG_TABLE_NAME, OLD.id::TEXT, 'DELETE', old_data, NOW());
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        old_data := to_jsonb(OLD);
        new_data := to_jsonb(NEW);
        -- Temukan field yang berubah
        FOR col IN SELECT key FROM jsonb_each(new_data)
        LOOP
            IF old_data->col IS DISTINCT FROM new_data->col THEN
                changed := array_append(changed, col);
            END IF;
        END LOOP;
        -- Hanya log jika ada yang benar-benar berubah
        IF array_length(changed, 1) > 0 THEN
            INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_fields, changed_at)
            VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'UPDATE', old_data, new_data, changed, NOW());
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        new_data := to_jsonb(NEW);
        INSERT INTO audit_log (table_name, record_id, operation, new_values, changed_at)
        VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'INSERT', new_data, NOW());
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- Terapkan ke tabel yang ingin diaudit
CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
 
CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Kelemahannya: trigger tidak tahu pengguna aplikasi mana yang melakukan perubahan. Kamu bisa mengatasinya dengan variabel sesi:

sql
-- Di aplikasimu, sebelum query:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- Di fungsi trigger:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Ini berhasil tapi terasa rapuh. Dalam praktik, saya menggunakan pendekatan hibrida: trigger untuk penangkapan data, dan kode aplikasi untuk mengatur konteks sesi.

Pola Tabel Riwayat#

Untuk tabel di mana kamu membutuhkan riwayat versi lengkap (bukan hanya "apa yang berubah" tapi "bagaimana keadaan pada waktu T"), tabel riwayat khusus lebih bersih:

sql
CREATE TABLE documents (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL DEFAULT 1,
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE document_history (
    id              BIGSERIAL PRIMARY KEY,
    document_id     UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL,
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (document_id, version)
);

Sebelum setiap update ke documents, salin keadaan saat ini ke document_history dan naikkan versi. Sekarang kamu bisa merekonstruksi dokumen pada titik waktu mana pun, menampilkan diff antar versi, dan bahkan memulihkan versi lama.

Pertukaran biayanya adalah penyimpanan. Jika kolom content-mu besar dan sering berubah, tabel riwayat bisa tumbuh cepat. Untuk kebanyakan aplikasi, ini baik-baik saja — penyimpanan murah dan kamu bisa mengarsipkan versi lama ke cold storage jika diperlukan.

Multi-Tenancy: Tiga Pendekatan, Pilih Deritamu#

Multi-tenancy adalah salah satu hal yang mudah ditambahkan di awal dan hampir mustahil ditambahkan nanti. Jika ada kemungkinan aplikasimu akan melayani banyak organisasi, bangun dari hari pertama.

Row-Level: tenant_id di Setiap Tabel#

Pendekatan paling umum. Setiap tabel memiliki kolom tenant_id, dan setiap query memfilter berdasarkan itu.

sql
CREATE TABLE tenants (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL UNIQUE,
    plan        TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE tasks (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    project_id  UUID NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'todo',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Setiap indeks harus menyertakan tenant_id untuk performa query
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

Risikonya adalah kebocoran data — satu WHERE tenant_id = ... yang terlewat dan kamu menampilkan data Tenant A ke Tenant B. Row-Level Security (RLS) PostgreSQL menghilangkan kelas bug ini:

sql
-- Aktifkan RLS pada tabel
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Buat policy berdasarkan variabel sesi
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Paksa RLS bahkan untuk pemilik tabel
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Sekarang, bahkan jika kode aplikasimu lupa klausa WHERE tenant_id = ..., PostgreSQL menambahkannya secara otomatis. Ini adalah pertahanan berlapis, dan ini adalah salah satu argumen terkuat untuk PostgreSQL dalam sistem multi-tenant.

sql
-- Di middleware koneksi aplikasimu:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Sekarang semua query pada tabel yang RLS-nya aktif secara otomatis difilter
SELECT * FROM projects;
-- PostgreSQL secara internal menambahkan: WHERE tenant_id = 'tenant-uuid-here'

Kelebihan: Satu database, ops sederhana, penggunaan resource efisien, query lintas-tenant mudah untuk admin. Kekurangan: Membutuhkan disiplin (atau RLS), setiap query menyentuh tenant_id, lebih sulit memberikan tenant backup/restore sendiri.

Schema-Per-Tenant#

Setiap tenant mendapat schema PostgreSQL sendiri. Semua schema berbagi database yang sama, tapi tabel diisolasi berdasarkan namespace.

sql
-- Buat schema untuk setiap tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Tabel berada di schema tenant
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Set search_path untuk tenant saat ini
SET search_path TO tenant_acme, public;
 
-- Sekarang query tanpa kualifikasi menuju schema tenant
SELECT * FROM projects;  -- query tenant_acme.projects

Kelebihan: Isolasi kuat, tidak ada risiko kebocoran data lintas-tenant, backup/restore per-tenant mudah, tenant bisa memiliki variasi schema. Kekurangan: Kompleksitas migrasi schema (kamu perlu migrasi N schema), manajemen connection pool, PostgreSQL memiliki batas praktis sekitar ~10.000 schema.

Database-Per-Tenant#

Setiap tenant mendapat database sendiri. Isolasi maksimum.

Kelebihan: Isolasi lengkap, scaling independen, backup/restore mudah, bisa menempatkan tenant besar di hardware dedicated. Kekurangan: Manajemen koneksi yang merepotkan, query lintas-tenant tidak mungkin, migrasi harus dijalankan N kali, overhead operasional signifikan.

Mana yang Dipilih?#

Untuk kebanyakan aplikasi SaaS: mulai dengan row-level + RLS. Ini paling sederhana untuk dioperasikan, dan RLS memberikanmu isolasi yang cukup kuat untuk sebagian besar kasus penggunaan. Pindah ke schema-per-tenant hanya jika kamu memiliki persyaratan isolasi kontraktual (pelanggan enterprise, industri teregulasi). Database-per-tenant adalah untuk saat kamu benar-benar harus menjamin isolasi fisik — dan bahkan saat itu, pertimbangkan managed database di mana beban operasional ditangani untukmu.

Kolom JSON/JSONB: Pintu Darurat#

JSONB PostgreSQL luar biasa. Ini memberikanmu fleksibilitas document-database di dalam sistem relasional. Tapi seperti tool yang powerful, mudah untuk disalahgunakan.

Kapan JSONB Adalah Pilihan yang Tepat#

Atribut dinamis yang ditentukan pengguna. Platform e-commerce di mana setiap kategori produk memiliki atribut berbeda — sepatu punya size dan color, elektronik punya voltage dan wattage. Alih-alih tabel EAV atau kolom untuk setiap atribut yang mungkin:

sql
CREATE TABLE products (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    category_id     UUID NOT NULL REFERENCES categories(id),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    attributes      JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Contoh data:
-- Sepatu: {"size": "10", "color": "black", "material": "leather"}
-- Laptop: {"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

Pengaturan dan konfigurasi aplikasi. Preferensi pengguna, feature flag, pengaturan notifikasi — hal-hal yang sering berubah bentuknya dan tidak membutuhkan integritas relasional.

sql
CREATE TABLE user_settings (
    user_id     UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    preferences JSONB NOT NULL DEFAULT '{
        "theme": "system",
        "notifications": {"email": true, "push": false},
        "locale": "en",
        "timezone": "UTC"
    }',
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Payload webhook, respons API, data event. Apa pun di mana schema dikendalikan oleh sistem eksternal dan mungkin berubah tanpa pemberitahuan.

GIN Index pada JSONB#

Tanpa indeks, query di dalam JSONB membutuhkan full table scan. GIN index membuatnya cepat:

sql
-- Index seluruh kolom JSONB (baik untuk query containment @>)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Query: temukan semua produk dengan color = "black"
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Query: temukan produk dengan salah satu dari atribut ini
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Untuk path spesifik yang sering kamu query, indeks tertarget lebih efisien:

sql
-- Index path spesifik
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Sekarang ini adalah lookup B-tree biasa
SELECT * FROM products
WHERE attributes->>'color' = 'black';

Jebakan Migrasi#

Di sinilah JSONB menggigitmu: kamu tidak bisa dengan mudah menambahkan constraint NOT NULL atau nilai default ke field di dalam JSON. Jika kamu menambahkan field baru yang wajib ke atribut produk, kamu perlu backfill setiap baris yang ada. Dengan kolom biasa, migrasi menangani ini secara atomik. Dengan JSONB, kamu menulis UPDATE yang menyentuh setiap baris dan berharap kode aplikasimu menangani field yang hilang dengan baik sampai backfill selesai.

sql
-- Menambahkan kolom baru: bersih, atomik, satu statement
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Menambahkan field JSONB baru: berantakan, membutuhkan backfill
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Plus update kode aplikasi untuk menangani weight_kg yang hilang
-- Plus update logika validasi
-- Plus update setiap respons API yang menyertakan attributes

Aturan saya: jika kamu melakukan query field JSONB di klausa WHERE lebih dari dua kali seminggu, itu mungkin harus menjadi kolom. JSONB adalah pintu darurat yang bagus. Tapi default yang buruk.

Migrasi Schema: Jangan Rusak Produksi Jam 3 Pagi#

Migrasi schema adalah tempat di mana teori bertemu kenyataan. Schema-mu terlihat bagus di atas kertas, tapi sekarang kamu perlu mengubah tabel dengan 50 juta baris selama jam kerja tanpa downtime sama sekali.

Tool Migrasi#

Saya sudah menggunakan sebagian besarnya. Ulasan singkat:

Drizzle (TypeScript): Favorit saya saat ini. Schema-as-code, query type-safe yang dihasilkan dari schema, SQL migrasi yang bersih. Perintah push untuk development cepat.

Prisma (TypeScript): DX yang bagus untuk schema sederhana. Kesulitan dengan fitur PostgreSQL lanjutan (partial index, custom type, RLS). Engine migrasinya bisa membuat keputusan yang mengejutkan.

Flyway (Java/CLI): Kokoh, sudah teruji pertempuran, SQL-first. Jika kamu menulis migrasi SQL mentah, Flyway melacaknya dengan andal. Tidak ada sihir, tidak ada kejutan.

golang-migrate (Go/CLI): Mirip Flyway tapi lebih ringan. Bagus untuk proyek Go atau ketika kamu hanya ingin migration runner up/down yang sederhana.

Masalah Zero-Downtime#

Perubahan schema paling berbahaya adalah yang mengunci tabel. Di PostgreSQL, ALTER TABLE ... ADD COLUMN dengan nilai default dulunya mengunci seluruh tabel selama durasi rewrite. Sejak PostgreSQL 11, default sederhana (konstanta) bersifat metadata-only dan instan. Tapi operasi lain masih mengunci:

sql
-- AMAN: metadata-only, instan (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- BERBAHAYA: menulis ulang seluruh tabel, kunci tabel penuh
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- BERBAHAYA: memindai seluruh tabel untuk memvalidasi
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

Menambahkan Kolom NOT NULL dengan Aman#

Kamu tidak bisa begitu saja menambahkan NOT NULL ke tabel yang sudah ada dengan data — gagal karena baris yang ada tidak punya nilai. Pendekatan naif:

sql
-- Ini mengunci tabel dan menulis ulangnya. Jangan lakukan ini di tabel besar.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Sejak PostgreSQL 11, ini sebenarnya aman untuk default konstanta — bersifat metadata-only. Tapi jika default-mu adalah fungsi atau kamu perlu backfill dengan nilai yang dihitung, gunakan pola expand-contract.

Pola Expand-Contract#

Ini adalah standar emas untuk perubahan schema zero-downtime. Tiga fase:

Fase 1: Expand — Tambahkan kolom baru sebagai nullable. Deploy kode aplikasi yang menulis ke kolom lama dan baru.

sql
-- Migrasi 1: Tambahkan kolom nullable
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Fase 2: Migrate — Backfill baris yang ada secara batch. Aplikasimu sudah menulis ke kolom baru untuk data baru.

sql
-- Migrasi 2: Backfill secara batch (jangan lakukan ini dalam satu statement untuk tabel besar)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Ulangi sampai semua baris ter-backfill

Fase 3: Contract — Setelah semua baris ter-backfill, tambahkan constraint NOT NULL dan hapus kolom lama (jika berlaku).

sql
-- Migrasi 3: Tambahkan constraint (gunakan NOT VALID untuk menghindari full table scan, lalu validasi terpisah)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migrasi 4: Validasi constraint (mengambil ShareUpdateExclusiveLock, bukan AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

Trik NOT VALID + VALIDATE CONSTRAINT itu krusial. Menambahkan check constraint secara normal memindai seluruh tabel sambil menahan kunci berat. NOT VALID menambahkan constraint tanpa memindai (hanya berlaku untuk penulisan baru), dan VALIDATE CONSTRAINT memindai dengan kunci yang lebih ringan yang tidak memblokir pembacaan atau penulisan.

Pembuatan Indeks#

Membuat indeks pada tabel besar memblokir penulisan secara default. Selalu gunakan CONCURRENTLY:

sql
-- MEMBLOKIR PENULISAN: jangan lakukan ini di tabel yang aktif
CREATE INDEX idx_users_email ON users (email);
 
-- NON-BLOCKING: gunakan ini sebagai gantinya
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY memakan waktu lebih lama dan tidak bisa berjalan di dalam transaksi, tapi tidak mengunci tabel. Pertukaran biayanya selalu layak di produksi.

Keputusan yang Saya Sesali#

Setiap schema punya penyesalan. Ini milik saya.

Menggunakan SERIAL Alih-alih UUID untuk ID Eksternal#

Di awal sebuah proyek, saya menggunakan primary key SERIAL dan mengeksposnya langsung di URL: /users/42, /orders/1337. Ini membocorkan informasi (kompetitor bisa menebak jumlah pengguna kami), membuat paginasi bisa ditebak, dan rusak ketika kami perlu menggabungkan database dari dua region. Beralih ke UUID untuk ID yang menghadap eksternal membutuhkan migrasi berbulan-bulan.

Pelajaran: gunakan UUID untuk apa pun yang terekspos di luar database-mu. Gunakan SERIAL/BIGSERIAL untuk tabel join internal jika mau, tapi jangan pernah biarkan integer auto-incrementing muncul di URL.

Tanpa Constraint ("Kita Tambahkan Nanti")#

Kami meluncurkan tabel tanpa check constraint karena "kita bergerak cepat dan akan menambahkannya nanti." Dalam dua minggu, data sudah memiliki harga negatif, nama kosong, dan nilai order_status berupa "oof" yang seseorang ketik saat testing dan tidak pernah dibersihkan.

Menambahkan constraint setelahnya membutuhkan:

  1. Menemukan semua data yang tidak valid
  2. Memutuskan apa yang harus dilakukan dengannya (perbaiki, hapus, atau grandfather)
  3. Menulis migrasi yang memperbaiki/backfill data DAN menambahkan constraint

Ini memakan waktu lebih lama daripada menambahkan constraint di hari pertama. Mulai dengan constraint. Longgarkan jika perlu. Jangan pernah sebaliknya.

String yang Dipisahkan Koma Alih-alih Array atau Tabel Join#

Saya pernah menyimpan tag sebagai string yang dipisahkan koma: "javascript,react,nextjs". Query-nya adalah mimpi buruk:

sql
-- Ini cara kamu melakukan query nilai yang dipisahkan koma. Jangan lakukan ini.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- Ini juga mencocokkan "react-native" dan "preact"
 
-- Yang seharusnya saya lakukan:
CREATE TABLE post_tags (
    post_id     UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id      UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Atau setidaknya, gunakan tipe array native PostgreSQL:

sql
CREATE TABLE posts (
    id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title   TEXT NOT NULL,
    tags    TEXT[] NOT NULL DEFAULT '{}',
    -- ...
);
 
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
 
-- Query yang bersih
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

Array bisa diterima untuk daftar sederhana yang tidak membutuhkan atributnya sendiri. Saat kamu membutuhkan metadata pada relasi (seperti "siapa yang menambahkan tag ini" atau "kapan ditambahkan"), kamu membutuhkan tabel join.

Menggunakan "type" sebagai Nama Kolom#

sql
-- Terlihat baik-baik saja saat itu
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Lalu kamu coba melakukan query di ORM mana pun:
-- notification.type  ← konflik dengan sistem tipe setiap bahasa
-- "type" adalah kata cadangan di sebagian besar dialek SQL
-- Kamu akhirnya mengutipnya di mana-mana: SELECT "type" FROM notifications

Gunakan kind, category, atau notification_type. Apa saja kecuali type. Demikian pula, hindari order (gunakan sort_order atau position), user (gunakan account atau berikan awalan), dan group (gunakan team atau group_name).

Tidak Menambahkan created_at dari Awal#

Satu tabel tidak mendapat created_at karena "kita tidak membutuhkannya." Tiga bulan kemudian, kami perlu men-debug masalah dan tidak tahu kapan record dibuat. Menambahkannya secara retroaktif berarti semua baris yang ada mendapat timestamp yang sama (timestamp migrasi), membuat data historis tidak berguna.

Setiap tabel mendapat created_at. Tanpa pengecualian. Biayanya satu kolom. Biaya tidak memilikinya tidak diketahui sampai kamu membutuhkannya.

Menaruh Logika Bisnis di Database View#

Saya pernah membuat rantai view — active_users memfilter users, premium_active_users memfilter active_users, dan view pelaporan meng-join semuanya. Ini bekerja bagus sampai seseorang mengubah tabel users dan ketiga view rusak secara diam-diam. Query planner juga kesulitan mengoptimalkan melalui beberapa lapisan view.

View bagus untuk kenyamanan dan pola akses read-only. Mereka buruk sebagai tempat untuk mengodekan logika bisnis yang berubah. Simpan aturan bisnis di kode aplikasi di mana mereka berversi, diuji, dan bisa di-deploy. Gunakan view untuk shortcut pelaporan, bukan sebagai blok bangunan arsitektural.

Menyatukan Semuanya#

Berikut tampilan schema yang dirancang dengan baik ketika kamu menggabungkan pola-pola ini. Sistem manajemen proyek yang disederhanakan:

sql
-- Ekstensi
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- untuk gen_random_uuid()
 
-- Fungsi trigger updated_at (dapat digunakan ulang)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Tenant
CREATE TABLE tenants (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    slug            TEXT NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9-]+$'),
    plan            TEXT NOT NULL DEFAULT 'free'
                    CHECK (plan IN ('free', 'pro', 'enterprise')),
    settings        JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tenants
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Pengguna
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL CHECK (LENGTH(display_name) > 0),
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    last_login_at   TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE UNIQUE INDEX uq_users_email_per_tenant
    ON users (tenant_id, email)
    WHERE deleted_at IS NULL;
 
CREATE INDEX idx_users_tenant ON users (tenant_id) WHERE deleted_at IS NULL;
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Aktifkan RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Proyek
CREATE TABLE projects (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'archived', 'deleted')),
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_projects_owner ON projects (owner_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Task
CREATE TABLE tasks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    project_id      UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title           TEXT NOT NULL CHECK (LENGTH(title) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'todo'
                    CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
    priority        INTEGER NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 4),
    assignee_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    due_at          TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);
CREATE INDEX idx_tasks_assignee ON tasks (assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX idx_tasks_status ON tasks (tenant_id, status) WHERE status != 'done';
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tasks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Komentar task
CREATE TABLE task_comments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    task_id         UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    author_id       UUID NOT NULL REFERENCES users(id),
    body            TEXT NOT NULL CHECK (LENGTH(body) > 0),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_task_comments_task ON task_comments (task_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON task_comments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE task_comments ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON task_comments
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Audit log (tanpa RLS — tabel khusus admin)
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,
    new_values      JSONB,
    changed_fields  TEXT[],
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_audit_log_tenant_table ON audit_log (tenant_id, table_name, record_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Perhatikan polanya:

  • Setiap tabel memiliki tenant_id dan RLS diaktifkan (kecuali tenants dan audit_log).
  • Setiap tabel memiliki created_at dan updated_at dengan trigger.
  • UUID untuk semua primary key (entitas yang menghadap eksternal).
  • Check constraint pada enum status, panjang, dan rentang.
  • Foreign key dengan perilaku ON DELETE yang sesuai.
  • Indeks yang dirancang untuk pola akses (tenant + project, tenant + status).
  • Soft delete hanya pada users (di mana pemulihan akun diperlukan), arsip berbasis status pada projects.

Pemikiran Akhir#

Desain schema tidak glamor. Tidak ada yang pernah memberikan presentasi konferensi berjudul "Saya Menambahkan Check Constraint ke Setiap Tabel dan Itu Menyelamatkan Kami Enam Bulan Debugging." Tapi persis itulah yang dilakukan desain schema yang baik — mencegah masalah dengan sangat tenang sehingga kamu tidak pernah tahu masalah itu akan ada.

Pola-pola dalam posting ini bukan hal baru. Mereka adalah hasil bertahun-tahun menulis migrasi, men-debug korupsi data, dan merefaktor schema di bawah beban produksi. Setiap pola ada karena saya, atau seseorang yang bekerja dengan saya, melakukannya dengan cara lain terlebih dahulu dan membayar harganya.

Mulai dengan constraint. Gunakan foreign key. Tambahkan created_at ke semuanya. Pilih konvensi penamaan dan terapkan tanpa ampun. Gunakan RLS untuk multi-tenancy. Berhati-hatilah dengan JSONB. Uji migrasimu terhadap data berukuran produksi sebelum men-deploy-nya.

Database adalah fondasi. Lakukan dengan benar, dan semua yang dibangun di atasnya menjadi lebih sederhana. Lakukan dengan salah, dan tidak ada kode aplikasi yang seberapa cerdas pun bisa menyelamatkanmu.

Artikel Terkait