Desain Database dengan Claude Code: Workflow Review Praktis

Desain database bukan sekadar memilih nama tabel. Kamu harus memutuskan data apa yang berada bersama, kolom mana yang tidak boleh kosong, nilai apa yang harus unik, bagaimana relasi antarbaris dijaga, dan apakah query list, search, billing, serta reporting masih cepat saat produk bertambah besar. Schema yang lemah bisa terlihat baik pada demo pertama, lalu gagal karena akun duplikat, status subscription kacau, dashboard lambat, atau migrasi yang tidak bisa di-rollback.

Claude Code sangat membantu di tahap ini, tetapi jangan jadikan sebagai sumber kebenaran tunggal. Gunakan sebagai asisten desain dan reviewer. Ia bisa membuat draft schema, mencari constraint yang hilang, menulis SQL test, dan menandai migrasi berisiko. Keputusan akhir tetap harus dipegang manusia yang memahami aturan produk, query nyata, dokumentasi resmi, dan risiko operasional.

Untuk alur lanjutan, baca juga otomasi migrasi database dan Prisma ORM dengan Claude Code. Saat memutuskan constraint dan indeks, cek dokumentasi resmi PostgreSQL constraints dan PostgreSQL indexes. Jika audit log, search, atau reporting menyimpan data sensitif, gunakan juga perspektif OWASP Top 10.

Dasar yang perlu dipahami

Tabel adalah kumpulan record sejenis, seperti users, teams, dan projects. Kolom adalah field dalam record, seperti email, status, dan created_at. Primary key mengidentifikasi satu baris. Foreign key memastikan satu baris menunjuk ke parent yang benar-benar ada. Unique constraint mencegah nilai bisnis ganda, seperti email, slug dalam tim, atau ID customer dari payment provider. Indeks mempercepat query yang sering dipakai. Migrasi adalah script untuk mengubah schema, dan rollback adalah rencana kembali jika release bermasalah.

Kesalahan umum pemula adalah menaruh semua validasi hanya di aplikasi. Validasi form penting untuk UX, tetapi tidak cukup untuk request paralel atau batch job. Database perlu menjadi lapisan perlindungan terakhir. Claude Code berguna ketika kamu meminta ia mencari lapisan perlindungan yang belum ada di schema.

claude -p "Read docs/database/schema.sql and migrations/*.sql.
Act as a database design reviewer, not as the owner of truth.

Check for:
- Missing primary keys, foreign keys, unique constraints, and NOT NULL rules
- status or role columns that are too loose
- Indexes needed for WHERE, JOIN, and ORDER BY patterns
- Destructive migrations and rollback gaps
- PII leaking into audit logs or search/reporting tables
- API shapes that may trigger N+1 queries

Return findings first. Include why it matters, how to verify it, and only then suggest a minimal patch."

Empat use case konkret

Use case pertama adalah SaaS dengan user, team, dan project. Satu user bisa berada di banyak team, dan setiap team punya banyak project. slug project biasanya cukup unik di dalam team, bukan di seluruh produk. Karena itu UNIQUE (team_id, slug) lebih tepat daripada unique global.

Use case kedua adalah audit log. Kita ingin tahu siapa mengubah apa dan kapan, tetapi tabel audit tidak boleh menjadi tempat membuang semua JSON. Menaruh email, nomor telepon, alamat, atau token di metadata terasa mudah, tetapi menyulitkan retensi, akses, dan permintaan penghapusan.

Use case ketiga adalah billing dan subscription. ID customer dari provider tidak boleh duplikat, dan satu team tidak boleh memiliki dua subscription aktif. Namun riwayat canceled tetap perlu disimpan, sehingga partial unique index bisa lebih cocok.

Use case keempat adalah tabel search atau reporting. Tabel baca khusus bisa menghindari JOIN berat di setiap layar. Konsekuensinya, kamu harus mendesain refresh, indeks, dan verifikasi agar hasil tidak diam-diam basi.

DDL PostgreSQL yang bisa dijalankan

SQL ini hanya membuat ulang schema demo khusus, jadi aman untuk dicoba di PostgreSQL lokal.

DROP SCHEMA IF EXISTS claude_db_design_demo CASCADE;
CREATE SCHEMA claude_db_design_demo;
SET search_path = claude_db_design_demo;

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL,
  display_name TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT users_email_unique UNIQUE (email),
  CONSTRAINT users_email_has_at CHECK (position('@' in email) > 1)
);

CREATE TABLE teams (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT teams_slug_unique UNIQUE (slug)
);

CREATE TABLE team_members (
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (team_id, user_id)
);

CREATE TABLE projects (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
  created_by BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT projects_team_slug_unique UNIQUE (team_id, slug)
);

CREATE TABLE audit_logs (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  actor_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
  action TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT audit_action_not_blank CHECK (length(action) > 0),
  CONSTRAINT audit_metadata_no_email CHECK (
    metadata::text !~* '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}'
  )
);

CREATE TABLE subscriptions (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  provider TEXT NOT NULL CHECK (provider IN ('stripe', 'manual')),
  provider_customer_id TEXT NOT NULL,
  plan_code TEXT NOT NULL CHECK (plan_code IN ('free', 'pro', 'business')),
  status TEXT NOT NULL CHECK (status IN ('trialing', 'active', 'past_due', 'canceled')),
  current_period_end DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT subscriptions_provider_customer_unique UNIQUE (provider, provider_customer_id)
);

CREATE TABLE project_search_documents (
  project_id BIGINT PRIMARY KEY REFERENCES projects(id) ON DELETE CASCADE,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  search_text TEXT NOT NULL,
  refreshed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX subscriptions_one_open_per_team
  ON subscriptions(team_id)
  WHERE status IN ('trialing', 'active', 'past_due');

CREATE INDEX idx_team_members_user_id ON team_members(user_id);
CREATE INDEX idx_projects_team_status_created ON projects(team_id, status, created_at DESC);
CREATE INDEX idx_audit_logs_team_created ON audit_logs(team_id, created_at DESC);
CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_project_search_documents_search
  ON project_search_documents USING GIN (to_tsvector('simple', search_text));

Seed data dan test constraint

Schema yang baik harus menunjukkan data valid bisa masuk dan data buruk ditolak. Blok DO di bawah menangkap failure yang memang diharapkan.

SET search_path = claude_db_design_demo;

INSERT INTO users (email, display_name, password_hash) VALUES
  ('[email protected]', 'Masa', '$2b$demo-hash-1'),
  ('[email protected]', 'Aiko', '$2b$demo-hash-2'),
  ('[email protected]', 'Ren', '$2b$demo-hash-3');

INSERT INTO teams (name, slug) VALUES
  ('ClaudeCodeLab', 'clab'),
  ('Northstar Apps', 'northstar');

INSERT INTO team_members (team_id, user_id, role) VALUES
  (1, 1, 'owner'),
  (1, 2, 'admin'),
  (2, 3, 'owner');

INSERT INTO projects (team_id, name, slug, status, created_by) VALUES
  (1, 'Schema Review', 'schema-review', 'active', 1),
  (1, 'Billing Console', 'billing-console', 'active', 2),
  (2, 'Customer Portal', 'customer-portal', 'active', 3);

INSERT INTO audit_logs (team_id, actor_user_id, action, entity_type, entity_id, metadata) VALUES
  (1, 1, 'project.created', 'project', '1', '{"project_slug":"schema-review"}'::jsonb),
  (1, 2, 'billing.plan_changed', 'subscription', '1', '{"from":"free","to":"pro"}'::jsonb);

INSERT INTO subscriptions (team_id, provider, provider_customer_id, plan_code, status, current_period_end) VALUES
  (1, 'stripe', 'cus_demo_1', 'pro', 'active', DATE '2026-07-31'),
  (2, 'manual', 'manual_northstar_1', 'free', 'canceled', NULL);

INSERT INTO project_search_documents (project_id, team_id, title, search_text)
SELECT
  id,
  team_id,
  name,
  name || ' project notes for database design, billing, audit, and reporting.'
FROM projects;

SET search_path = claude_db_design_demo;

DO $$
BEGIN
  INSERT INTO users (email, display_name, password_hash)
  VALUES ('[email protected]', 'Duplicate Masa', '$2b$demo-hash-x');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK duplicate email blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO projects (team_id, name, slug, created_by)
  VALUES (1, NULL, 'bad-null', 1);
EXCEPTION WHEN not_null_violation THEN
  RAISE NOTICE 'OK NULL project name blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO team_members (team_id, user_id, role)
  VALUES (999, 1, 'member');
EXCEPTION WHEN foreign_key_violation THEN
  RAISE NOTICE 'OK missing team blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO projects (team_id, name, slug, created_by)
  VALUES (1, 'Duplicate Slug', 'schema-review', 1);
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK duplicate team slug blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO audit_logs (team_id, actor_user_id, action, entity_type, entity_id, metadata)
  VALUES (1, 1, 'project.invited', 'project', '1', '{"email":"[email protected]"}'::jsonb);
EXCEPTION WHEN check_violation THEN
  RAISE NOTICE 'OK email in audit metadata blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO subscriptions (team_id, provider, provider_customer_id, plan_code, status, current_period_end)
  VALUES (1, 'stripe', 'cus_demo_2', 'business', 'active', DATE '2026-08-31');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK second open subscription blocked: %', SQLERRM;
END $$;

SELECT p.id, p.name, count(a.id) AS audit_events
FROM projects p
LEFT JOIN audit_logs a
  ON a.entity_type = 'project'
 AND a.entity_id = p.id::text
WHERE p.team_id = 1
GROUP BY p.id, p.name
ORDER BY audit_events DESC, p.id;

SELECT project_id, title
FROM project_search_documents
WHERE to_tsvector('simple', search_text) @@ plainto_tsquery('simple', 'billing');

EXPLAIN (COSTS OFF)
SELECT *
FROM audit_logs
WHERE team_id = 1
ORDER BY created_at DESC
LIMIT 20;

Pitfall yang harus dicari

Pitfall pertama adalah unique constraint yang hilang. Email, akun OAuth, payment customer ID, dan slug per team sebaiknya dilindungi di database. Pitfall kedua adalah kolom nullable tanpa alasan jelas. Jika projects.name, team_members.role, atau subscriptions.status boleh kosong, UI, permission, dan billing menjadi rapuh.

Pitfall ketiga adalah migrasi destruktif. DROP COLUMN, perubahan tipe, NOT NULL pada data lama, dan default value di tabel besar bisa mengunci produksi atau menghilangkan data. Minta Claude Code membuat rencana expand-and-contract, lalu review secara manual. Pitfall keempat adalah N+1 query: list 100 project yang mengambil audit count satu per satu menjadi 101 query. Pitfall kelima adalah PII di log, search, atau reporting table.

Checklist migrasi dan rollback

# Migration checklist: add projects.archived_at

## Design
- [ ] Why this column is needed is written in the issue or PR.
- [ ] Existing rows and backfill behavior are defined.
- [ ] NOT NULL, default value, and CHECK constraint choices are explained.
- [ ] Index impact was checked against real WHERE / ORDER BY queries.

## Safety
- [ ] This migration does not drop data in the first deploy.
- [ ] Large-table locks were considered.
- [ ] The application can run during the expand-and-contract period.
- [ ] Backfill can be retried safely.

## Up SQL
    ALTER TABLE projects ADD COLUMN archived_at TIMESTAMPTZ;
    UPDATE projects
    SET archived_at = now()
    WHERE status = 'archived' AND archived_at IS NULL;
    ALTER TABLE projects
      ADD CONSTRAINT projects_archived_at_matches_status
      CHECK (
        (status = 'archived' AND archived_at IS NOT NULL)
        OR (status = 'active' AND archived_at IS NULL)
      ) NOT VALID;
    ALTER TABLE projects VALIDATE CONSTRAINT projects_archived_at_matches_status;

## Down SQL
    ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_archived_at_matches_status;
    ALTER TABLE projects DROP COLUMN IF EXISTS archived_at;

## Verification
- [ ] Constraint test SQL was run locally.
- [ ] Read queries still use expected indexes.
- [ ] Rollback was reviewed, including data-loss notes.
- [ ] Claude Code findings were reviewed by a human owner.

Baca rollback dengan kritis. Down migration yang dihasilkan tool bisa mengembalikan bentuk tabel, tetapi tidak mengembalikan data yang sudah dihapus. Jika rollback tidak lengkap, tulis di PR dan siapkan backup, rollout bertahap, atau recovery.

Bagikan desain dengan diagram

erDiagram
    users ||--o{ team_members : joins
    teams ||--o{ team_members : has
    teams ||--o{ projects : owns
    users ||--o{ projects : creates
    teams ||--o{ audit_logs : records
    users ||--o{ audit_logs : acts
    teams ||--o{ subscriptions : pays
    projects ||--|| project_search_documents : indexes

Diagram membantu diskusi, tetapi tidak menggantikan DDL, migration, dan test SQL. ER diagram yang rapi tetap tidak melindungi data jika constraint hilang.

CTA dan hasil dari Masa

Jika tim memakai Claude Code untuk desain database, tulis aturan di CLAUDE.md: jangan akses DB produksi langsung, migrasi destruktif harus bertahap, test constraint wajib, dan PII tidak boleh masuk log tanpa approval. ClaudeCodeLab membantu adoption Claude Code, template review migrasi, PR checklist, dan training dengan repository nyata. Untuk menerapkan ini pada produk yang sudah berjalan, mulai dari training dan konsultasi Claude Code. Untuk belajar mandiri, lihat cheatsheet gratis dan template produk.

Saat Masa mencoba workflow ini pada review schema nyata, bagian paling berguna bukan ER diagram pertama. Yang paling berguna adalah INSERT yang memang harus gagal: email duplikat, nama project null, team hilang, slug duplikat, email di audit metadata, dan subscription aktif kedua. Claude Code mempercepat checklist, tetapi keyakinan datang saat PostgreSQL benar-benar menolak data buruk.