postgres-prisma-optimization

name	postgres-prisma-optimization
description	PostgreSQL + Prisma ORM optimization: schema design patterns (relations, enums, composite keys, indexes), migration workflow (dev/deploy/resolve), N+1 query prevention (include, select, raw queries), PostgreSQL indexing strategy (B-tree, GIN, partial, composite), query performance (EXPLAIN ANALYZE, connection pooling with PgBouncer), data modeling patterns (soft delete, auditing, versioning, polymorphic), migration safety (reversible migrations, data migration), Prisma transaction patterns (interactive, nested write).
version	1.0.0
author	opencode-agent-kit
agent	database-specialist

Postgres Prisma Optimization Skill

Skill khusus untuk agent @database — Database Specialist. Berisi pattern, strategi, workflow, dan best practices untuk optimasi PostgreSQL dengan Prisma ORM: schema design, indexing, query performance, migration safety, dan data modeling.

---

1. Prisma Schema Design Patterns

1.1 Relations — One-to-One

model User {
  id      String    @id @default(uuid()) @db.Uuid
  email   String    @unique
  profile Profile?
}

model Profile {
  id        String @id @default(uuid()) @db.Uuid
  fullName  String @map("full_name")
  bio       String?
  avatarUrl String? @map("avatar_url")
  userId    String @unique @map("user_id") @db.Uuid
  user      User   @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

Key points:

• Field userId harus @unique agar one-to-one enforced di database.
• Gunakan @map() untuk snake_case di database, camelCase di Prisma.
• Selalu tentukan onDelete (Cascade, Restrict, SetNull, NoAction).
• @@map("table_name") untuk nama tabel snake_case di PostgreSQL.

1.2 Relations — One-to-Many

model User {
  id    String @id @default(uuid()) @db.Uuid
  posts Post[]
}

model Post {
  id       String   @id @default(uuid()) @db.Uuid
  title    String
  content  String?
  authorId String   @map("author_id") @db.Uuid
  author   User     @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@index([authorId], name: "idx_post_author_id")
  @@map("posts")
}

Key points:

• Foreign key di sisi "many" (Post).
• Index foreign key secara eksplisit dengan @@index untuk JOIN performance.
• Naming convention: authorId → @map("author_id").

1.3 Relations — Many-to-Many (Junction Table)

model Post {
  id     String   @id @default(uuid()) @db.Uuid
  title  String
  tags   TagOnPost[]
}

model Tag {
  id    String      @id @default(uuid()) @db.Uuid
  name  String      @unique
  posts TagOnPost[]
}

model TagOnPost {
  postId String @map("post_id") @db.Uuid
  tagId  String @map("tag_id") @db.Uuid
  post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag    Tag    @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])
  @@index([tagId], name: "idx_tag_on_post_tag_id")
  @@map("tags_on_posts")
}

Key points:

• Junction table dengan @@id([postId, tagId]) — composite primary key.
• Index pada foreign key kedua (tagId) untuk reverse lookup.
• Nama tabel junction: plural + _on_ atau _ connector.

1.4 Enums

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

Gunakan Prisma enum (bukan native PostgreSQL enum) agar kompatibel dengan migration engine. Untuk PostgreSQL native enum, gunakan @pgEnum('status_type') attribute di Prisma 5+.

1.5 Composite Keys

model AuditLog {
  id        String   @id @default(uuid()) @db.Uuid
  entityId  String   @map("entity_id") @db.Uuid
  entityType String  @map("entity_type")
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz(3)

  // Composite unique constraint
  @@unique([entityId, entityType, createdAt], name: "uq_audit_entity_time")
  // Composite index for common query pattern
  @@index([entityType, createdAt], name: "idx_audit_entity_type_created_at")
  @@map("audit_logs")
}

1.6 Custom Indexes

model Order {
  id         String   @id @default(uuid()) @db.Uuid
  status     String?
  userId     String   @map("user_id") @db.Uuid
  totalAmount Decimal  @map("total_amount") @db.Decimal(12, 2)
  createdAt  DateTime @default(now()) @map("created_at") @db.Timestamptz(3)

  // Composite index with order
  @@index([userId, status, createdAt], name: "idx_order_user_status_created")
  // Partial index via raw SQL (not expressible in Prisma schema)
  @@index([status], name: "idx_order_status_active",   where: "status = 'ACTIVE'")
  @@map("orders")
}

Catatan: Partial index where clause didukung Prisma sejak versi 5.10+. Untuk versi lebih lama, buat via migration manual.

---

2. Migration Workflow

2.1 Development — prisma migrate dev

# Membuat migration baru berdasarkan perubahan schema
npx prisma migrate dev --name add_user_profile

# Reset database dan apply semua migration (destructive!)
npx prisma migrate reset

# Apply migration tanpa prompt
npx prisma migrate dev --name add_user_profile --skip-generate

# Hanya generate Prisma Client tanpa migration
npx prisma generate

Best practice:

1. Ubah schema.prisma terlebih dahulu.
2. Jalankan prisma migrate dev --name <description>.
3. Review file SQL migration yang dihasilkan di prisma/migrations/<timestamp>_<name>/migration.sql.
4. Jika ada data transformation, tambahkan raw SQL di migration file sebelum commit.
5. Generate Prisma Client: prisma generate.
6. Test query baru di aplikasi.

2.2 Production — prisma migrate deploy

# Apply semua migration yang belum dijalankan (read-only operation)
npx prisma migrate deploy

# Verifikasi status migration
npx prisma migrate status

Best practice:

• migrate deploy adalah operasi idempotent — aman dijalankan multiple times.
• Jangan pernah jalankan migrate dev di production.
• Selalu backup database sebelum deploy migration.
• Monitor log migration untuk error.

2.3 Resolving Migration Conflicts

Ketika migration conflict terjadi (baseline berbeda):

# 1. Resolve secara manual
# Edit migration.sql yang conflict untuk menyelaraskan state
npx prisma migrate resolve --applied "20250101000000_add_user_profile"

# 2. Baseline ulang (untuk development)
npx prisma migrate dev --create-only  # Buat migration kosong
npx prisma migrate --help              # Lihat opsi resolve

# 3. Jika sudah terlanjur apply di production
# Gunakan --rolled-back untuk menandai migration yang gagal
npx prisma migrate resolve --rolled-back "20250101000000_bad_migration"

Strategi conflict resolution:

1. Identifikasi migration mana yang berbeda baseline.
2. Gunakan migrate resolve --applied untuk menandai migration sebagai applied (tanpa menjalankannya).
3. Gunakan migrate resolve --rolled-back untuk menandai migration sebagai rolled back.
4. Buat migration korektif baru.

2.4 Migration SQL Review Checklist

Setiap migration harus di-review:

-- ❌ BURUK — Menghapus kolom dalam 1 step (breaking change)
ALTER TABLE "posts" DROP COLUMN "old_column";

-- ✅ BAIK — Additive change (backward compatible)
ALTER TABLE "posts" ADD COLUMN "new_column" TEXT;

-- ✅ BAIK — Buat index terpisah
CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_posts_author_id" ON "posts" ("author_id");

-- ❌ BURUK — Default value mengunci tabel
ALTER TABLE "posts" ADD COLUMN "views" INTEGER NOT NULL DEFAULT 0;

-- ✅ BAIK — Default tanpa lock (PostgreSQL 11+)
ALTER TABLE "posts" ADD COLUMN "views" INTEGER;
ALTER TABLE "posts" ALTER COLUMN "views" SET DEFAULT 0;

2.5 Migration Rollback Strategy

Prisma tidak memiliki migrate down built-in. Strategi:

1. Siapkan rollback SQL di dokumentasi migration.
2. Simpan rollback script sebagai file terpisah: prisma/migrations/<name>/rollback.sql.
3. Gunakan pendekatan reversible: Setiap migration additive harus memiliki migration "undo" terpisah.
4. Data migration harus memiliki script rollback data juga.

-- Contoh rollback.sql untuk migration tertentu
-- Step 1: Kembalikan data jika ada
UPDATE posts SET old_column = new_column WHERE new_column IS NOT NULL;
-- Step 2: Drop kolom baru
ALTER TABLE "posts" DROP COLUMN "new_column";

---

3. N+1 Query Prevention

3.1 The N+1 Problem

// ❌ N+1 — Setiap iterasi query user memicu 1 query tambahan
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
  // 1 query untuk users + N query untuk posts = N+1
}

3.2 Eager Loading dengan include

// ✅ Eager loading — 1 query dengan JOIN
const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: true,                     // One-to-many
    profile: true,                   // One-to-one
    tags: { include: { tag: true } }, // Nested include (many-to-many via junction)
  },
});

// Filtered include
const filteredPosts = await prisma.user.findMany({
  include: {
    posts: {
      where: { status: 'PUBLISHED' },
      orderBy: { createdAt: 'desc' },
      take: 10, // Batasi jumlah related records
    },
  },
});

3.3 Projection dengan select

// ✅ Select — Hanya fetching field yang diperlukan
const usersPartial = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    posts: {
      select: {
        id: true,
        title: true,
      },
      where: { status: 'PUBLISHED' },
    },
  },
});

Perbandingan include vs select:

Aspect	include	select
Penggunaan	Seluruh field dari relation	Field tertentu saja
Performance	Lebih berat (semua field)	Lebih ringan (subset field)
Use case	Detail view, full entity	List view, API response partial
Nested query	Mendukung	Mendukung

3.4 Batch Loading dengan Prisma

// ✅ Batch loading — Hindari loop query
const userIds = ['id-1', 'id-2', 'id-3'];
const usersInBatch = await prisma.user.findMany({
  where: { id: { in: userIds } },
  include: { posts: true },
});

// Group by user (manual)
const userMap = new Map(usersInBatch.map(u => [u.id, u]));

3.5 Raw Query untuk Complex Aggregation

// ✅ Raw query — Ketika Prisma query tidak cukup optimal
const result = await prisma.$queryRaw<Array<{
  id: string;
  email: string;
  post_count: bigint;
}>>`
  SELECT
    u.id,
    u.email,
    COUNT(p.id)::BIGINT as post_count
  FROM "users" u
  LEFT JOIN "posts" p ON p.author_id = u.id
  WHERE u.id = ANY(${userIds}::UUID[])
  GROUP BY u.id, u.email
  ORDER BY post_count DESC
`;

// ✅ Gunakan prisma.$queryRawUnsafe untuk dynamic query (hati-hati SQL injection)

3.6 Monitoring Query Count

// ✅ Log query count di development
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

// Atau middleware untuk count query
let queryCount = 0;
prisma.$use(async (params, next) => {
  queryCount++;
  console.log(`Query #${queryCount}: ${params.model}.${params.action}`);
  return next(params);
});

---

4. PostgreSQL Indexing Strategy

4.1 Index Type Reference

Index Type	Use Case	Contoh PostgreSQL DDL
B-tree (default)	Equality & range, ORDER BY, <, <=, >, >=, BETWEEN, LIKE (prefix)	CREATE INDEX ...
GIN	Full-text search, array contains, JSONB queries, trigram/pattern matching	CREATE INDEX ... USING GIN
GiST	Full-text search, geometric data, range types	CREATE INDEX ... USING GiST
BRIN	Large tables with naturally ordered data (log, time-series)	CREATE INDEX ... USING BRIN
Hash	Simple equality (jarang digunakan, B-tree lebih baik)	CREATE INDEX ... USING HASH

4.2 B-tree Index

// Prisma — B-tree (default)
model User {
  email    String @unique  // Unique index = B-tree
  status   String?
  @@index([status], name: "idx_user_status")
}

-- SQL equivalent
CREATE INDEX IF NOT EXISTS "idx_user_status" ON "users" ("status");

Best practice B-tree:

• Komposit index untuk multi-column query: @@index([status, createdAt]).
• ORDER BY field di akhir composite index.
• Unique constraint otomatis membuat B-tree index.

4.3 GIN Index

GIN untuk full-text search dan JSONB:

-- Full-text search GIN
CREATE INDEX IF NOT EXISTS "idx_posts_content_gin"
ON "posts" USING GIN (to_tsvector('english', "content"));

-- JSONB GIN index
CREATE INDEX IF NOT EXISTS "idx_products_metadata_gin"
ON "products" USING GIN ("metadata");

-- Array contains GIN
CREATE INDEX IF NOT EXISTS "idx_tags_array_gin"
ON "posts" USING GIN ("tags");

Di Prisma, GIN index harus dibuat via migration manual karena Prisma belum support @@index dengan using:

// schema.prisma — placeholder
model Post {
  id      String @id @default(uuid()) @db.Uuid
  title   String
  content String
}

-- migration.sql — tambahkan secara manual
CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_posts_content_search"
ON "posts" USING GIN (to_tsvector('english', "content"));

4.4 Partial Index

Partial index untuk subset data yang sering di-query:

// Prisma 5.10+ support partial index di schema
model Order {
  status  String?
  @@index([status], name: "idx_order_active", where: "status = 'ACTIVE'")
}

-- SQL equivalent (semua versi)
CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_order_active"
ON "orders" ("status")
WHERE status = 'ACTIVE';

Use case partial index:

• Filter WHERE status = 'ACTIVE' pada tabel dengan mayoritas data inactive.
• Filter WHERE deleted_at IS NULL untuk soft delete query.
• Filter WHERE is_verified = true pada user verification.

4.5 Composite Index

model Transaction {
  userId    String   @map("user_id") @db.Uuid
  status    String?
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz(3)
  amount    Decimal  @map("amount") @db.Decimal(12, 2)

  // Query: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
  @@index([userId, status, createdAt], name: "idx_txn_user_status_created")
}

Composite index rules:

• Leftmost prefix rule: Index [A, B, C] bisa digunakan untuk A, A+B, A+B+C, tapi tidak untuk B saja.
• Order matters: Letakkan kolom dengan high cardinality (banyak unique values) di depan.
• Order BY: Kolom ORDER BY harus di posisi paling kanan dari index.
• Range condition: Kolom dengan range operator (<, >, BETWEEN) harus di posisi paling kanan.

4.6 Indexing Decision Matrix

Query Pattern	Index Type	Example
WHERE id = ?	B-tree (PK)	@id
WHERE email = ?	B-tree unique	@unique
WHERE user_id = ?	B-tree	@@index([userId])
WHERE user_id = ? AND status = ?	B-tree composite	@@index([userId, status])
WHERE content ILIKE '%keyword%'	GIN (trigram)	USING GIN (content gin_trgm_ops)
WHERE metadata @> '{"key":"val"}'	GIN JSONB	USING GIN (metadata)
WHERE status = 'ACTIVE'	Partial B-tree	WHERE status = 'ACTIVE'
WHERE created_at BETWEEN ? AND ?	B-tree (range)	@@index([createdAt])
WHERE deleted_at IS NULL	Partial B-tree	WHERE deleted_at IS NULL
Full-text search	GIN	USING GIN (to_tsvector(...))

4.7 Anti-Patterns Indexing

• ❌ Over-indexing: Setiap index tambahan memperlambat INSERT/UPDATE/DELETE.
• ❌ Index kolom boolean atau low cardinality — selectivity terlalu rendah.
• ❌ Duplicate index — misal: @@unique + @@index on same column.
• ❌ Index pada kolom yang jarang di-query — storage waste.
• ❌ Composite index dengan urutan kolom salah (low cardinality di depan).

---

5. Query Performance

5.1 EXPLAIN ANALYZE

-- Analisis query plan
EXPLAIN ANALYZE
SELECT u.id, u.email, COUNT(p.id) as post_count
FROM "users" u
LEFT JOIN "posts" p ON p.author_id = u.id
WHERE u.status = 'ACTIVE'
GROUP BY u.id, u.email
ORDER BY post_count DESC
LIMIT 20;

Apa yang harus diperhatikan dari output EXPLAIN ANALYZE:

Indicator	Arti	Tindakan
Seq Scan	Sequential scan pada large table	Tambah index
Est. rows vs Actual rows	Estimasi cardinality salah	Update ANALYZE atau adjust stats
Sort Method: external merge	Sort overflow ke disk	Tambah index sorting
Nested Loop dengan banyak row	Join strategi salah	Tambah index atau ubah join
Rows Removed by Filter tinggi	Filter tidak efisien	Index partial atau composite
Shared Hit Blocks rendah	Data tidak di cache	Tingkatkan shared_buffers

5.2 Prisma Query Profiling

// Profiling dengan middleware
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'info' },
  ],
});

prisma.$on('query', (e) => {
  console.log('Query:', e.query);
  console.log('Duration:', e.duration, 'ms');
  console.log('Params:', e.params);
});

// Query timing
async function queryWithTiming<T>(label: string, fn: () => Promise<T>): Promise<T> {
  const start = performance.now();
  const result = await fn();
  const duration = performance.now() - start;
  console.log(`[${label}] ${duration.toFixed(2)}ms`);
  return result;
}

5.3 Slow Query Detection

-- Cari slow queries dari pg_stat_statements
SELECT
  query,
  calls,
  total_exec_time / calls AS avg_time_ms,
  rows,
  shared_blks_hit,
  shared_blks_read
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_%'
ORDER BY total_exec_time DESC
LIMIT 20;

Aktifkan pg_stat_statements:

-- Di postgresql.conf atau ALTER SYSTEM
shared_preload_libraries = 'pg_stat_statements';
pg_stat_statements.track = all;

-- Atau di session
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

5.4 Connection Pooling dengan PgBouncer

// Prisma + PgBouncer configuration
const prisma = new PrismaClient({
  datasources: {
    db: {
      // Gunakan PgBouncer transaction mode port (6432)
      url: process.env.DATABASE_URL, // postgresql://user:pass@host:6432/db?pgbouncer=true
    },
  },
});

PgBouncer URL:

# Transaction mode (recommended untuk Prisma)
postgresql://user:pass@pgbouncer-host:6432/dbname?pgbouncer=true

PgBouncer Key Config:

[databases]
dbname = host=postgres-host port=5432 dbname=dbname

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 5.0
server_idle_timeout = 600
query_timeout = 30

Prisma Connection Pool Best Practice:

Environment	Pool Size	Connection Limit
Development	5-10	10
Staging	10-20	20
Production (low traffic)	20-50	50
Production (high traffic)	50-100+	100+

// Prisma connection management
const prisma = new PrismaClient({
  // connection_limit via datasource URL
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '&connection_limit=20',
    },
  },
});

// Graceful shutdown
process.on('SIGTERM', async () => {
  await prisma.$disconnect();
});

5.5 Query Performance Checklist

• Gunakan EXPLAIN ANALYZE untuk memverifikasi index usage.
• Hindari N+1 dengan include atau select.
• Batasi result dengan take + pagination.
• Gunakan select untuk hanya mengambil field yang diperlukan.
• Index foreign key untuk JOIN performance.
• Monitor slow query dengan pg_stat_statements.
• Gunakan connection pooling di production.
• Set connection_limit sesuai dengan kapasitas database.
• Gunakan @db.Uuid untuk UUID fields (binary storage, lebih cepat dari text UUID).

---

6. Data Modeling Patterns

6.1 Soft Delete

model User {
  id        String    @id @default(uuid()) @db.Uuid
  email     String    @unique
  deletedAt DateTime? @map("deleted_at") @db.Timestamptz(3)

  @@index([deletedAt], name: "idx_user_deleted_at")
  @@map("users")
}

Query pattern:

// Semua query harus include filter deleted_at IS NULL
const activeUsers = await prisma.user.findMany({
  where: { deletedAt: null },
});

// Soft delete
await prisma.user.update({
  where: { id: userId },
  data: { deletedAt: new Date() },
});

// Hard delete (permanent)
await prisma.user.delete({ where: { id: userId } });

Partial index untuk performance:

-- Buat partial index yang mencakup hanya non-deleted records
CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_user_active"
ON "users" ("email")
WHERE deleted_at IS NULL;

6.2 Auditing

model AuditLog {
  id         String   @id @default(uuid()) @db.Uuid
  entityId   String   @map("entity_id") @db.Uuid
  entityType String   @map("entity_type")
  action     String                     // CREATE, UPDATE, DELETE
  oldValue   Json?    @map("old_value") @db.JsonB
  newValue   Json?    @map("new_value") @db.JsonB
  changes    Json?    @db.JsonB         // Only changed fields
  actorId    String?  @map("actor_id") @db.Uuid
  actorEmail String?  @map("actor_email")
  ipAddress  String?  @map("ip_address")
  createdAt  DateTime @default(now()) @map("created_at") @db.Timestamptz(3)

  @@index([entityType, entityId], name: "idx_audit_entity")
  @@index([actorId], name: "idx_audit_actor")
  @@index([createdAt], name: "idx_audit_created_at")
  @@map("audit_logs")
}

Implementasi di Prisma middleware:

prisma.$use(async (params, next) => {
  const result = await next(params);

  if (['create', 'update', 'delete'].includes(params.action)) {
    await prisma.auditLog.create({
      data: {
        entityId: result?.id,
        entityType: params.model!,
        action: params.action.toUpperCase(),
        actorId: currentUserId,
        changes: params.args.data,
      },
    });
  }

  return result;
});

6.3 Versioning (Optimistic Locking)

model Document {
  id        String   @id @default(uuid()) @db.Uuid
  title     String
  content   String?
  version   Int      @default(1)
  updatedAt DateTime @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(3)

  @@map("documents")
}

Optimistic locking pattern:

// Update with version check
async function updateDocument(id: string, data: Partial<Document>, expectedVersion: number) {
  const result = await prisma.document.updateMany({
    where: {
      id,
      version: expectedVersion, // Only update if version matches
    },
    data: {
      ...data,
      version: { increment: 1 },
    },
  });

  if (result.count === 0) {
    throw new Error('Document was modified by another user. Please refresh and try again.');
  }

  return prisma.document.findUnique({ where: { id } });
}

6.4 Polymorphic Relations

Prisma tidak mendukung polymorphic relation secara native. Gunakan pattern berikut:

Option 1: Junction per entity type

model Comment {
  id        String   @id @default(uuid()) @db.Uuid
  content   String
  authorId  String   @map("author_id") @db.Uuid
  author    User     @relation(fields: [authorId], references: [id])

  // Polymorphic: commentable
  postId        String?  @map("post_id") @db.Uuid
  documentId    String?  @map("document_id") @db.Uuid
  post          Post?    @relation(fields: [postId], references: [id])
  document      Document? @relation(fields: [documentId], references: [id])

  @@index([postId], name: "idx_comment_post")
  @@index([documentId], name: "idx_comment_document")
  @@map("comments")
}

Option 2: Entity type + Entity ID (manual union)

model Comment {
  id            String   @id @default(uuid()) @db.Uuid
  content       String
  entityType    String   @map("entity_type")  // 'POST', 'DOCUMENT', 'IMAGE'
  entityId      String   @map("entity_id")    // UUID of the parent entity
  authorId      String   @map("author_id") @db.Uuid
  author        User     @relation(fields: [authorId], references: [id])

  @@index([entityType, entityId], name: "idx_comment_entity")
  @@map("comments")
}

Option 3: Template table with JSONB

model Activity {
  id          String   @id @default(uuid()) @db.Uuid
  action      String
  targetType  String   @map("target_type")
  targetId    String   @map("target_id") @db.Uuid
  metadata    Json?    @db.JsonB   // Flexible, store any target-specific data

  @@index([targetType, targetId], name: "idx_activity_target")
  @@index([metadata], name: "idx_activity_metadata", type: Gin)
  @@map("activities")
}

6.5 Data Modeling Checklist

• Normalization sampai 3NF kecuali justified denormalization.
• Soft delete pattern untuk data yang perlu retention.
• Audit trail untuk data critical (financial, user data, settings).
• Versioning untuk concurrent update protection.
• Polymorphic pattern untuk entity yang memiliki relasi ke multiple types.
• JSONB untuk data semi-structured (metadata, settings, config).
• Index pada foreign key dan query pattern utama.
• @updatedAt untuk tracking modification time.
• Default timestamps (@default(now())) untuk created_at.

---

7. Migration Safety

7.1 Safe Migration Principles

Principle	Description	Example
Additive first	Tambah kolom/tabel baru sebelum drop yang lama	ADD COLUMN sebelum DROP COLUMN
Expand-Contract	Phase 1: add new column & backfill → Phase 2: deploy code → Phase 3: drop old column	3-step migration
Backward compatible	Schema baru tidak boleh break query yang sudah ada	Default value, nullable new column
Data integrity	Setiap data transformation harus preserve integrity	Test rollback
Performance aware	Migration tidak boleh menyebabkan downtime	CREATE INDEX CONCURRENTLY

7.2 Expand-Contract Pattern

-- Phase 1: Add new column (additive)
ALTER TABLE "users" ADD COLUMN "display_name" TEXT;
UPDATE "users" SET "display_name" = "username";  -- Backfill
-- Deploy code that writes to both username and display_name

-- Phase 2 (later migration): Drop old column
ALTER TABLE "users" DROP COLUMN "username";
-- Deploy code that uses only display_name

7.3 Data Migration

-- Batch data migration dengan chunking
DO $$
DECLARE
  batch_size CONSTANT INT := 1000;
  offset_val INT := 0;
  affected_rows INT;
BEGIN
  LOOP
    UPDATE "posts"
    SET "search_vector" = to_tsvector('english', "title" || ' ' || COALESCE("content", ''))
    WHERE "id" IN (
      SELECT "id" FROM "posts"
      WHERE "search_vector" IS NULL
      ORDER BY "id"
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );

    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    COMMIT;  -- Commit setiap batch

    EXIT WHEN affected_rows = 0;
    offset_val := offset_val + batch_size;
    RAISE NOTICE 'Processed % rows', offset_val;
  END LOOP;
END;
$$;

Prisma + raw untuk data migration:

async function migrateData(batchSize = 1000) {
  let processed = 0;
  while (true) {
    const result = await prisma.$executeRaw`
      UPDATE "posts"
      SET "search_vector" = to_tsvector('english', "title" || ' ' || COALESCE("content", ''))
      WHERE "id" IN (
        SELECT "id" FROM "posts"
        WHERE "search_vector" IS NULL
        ORDER BY "id"
        LIMIT ${batchSize}
        FOR UPDATE SKIP LOCKED
      )
    `;
    if (result === 0) break;
    processed += result;
    console.log(`Processed ${processed} rows`);
  }
}

7.4 Reversible Migrations

Setiap migration harus memiliki rollback script:

-- Forward migration (migration.sql)
ALTER TABLE "users" ADD COLUMN "display_name" TEXT;
UPDATE "users" SET "display_name" = "username" WHERE "display_name" IS NULL;

-- Rollback script (rollback.sql — simpan di folder migration)
ALTER TABLE "users" DROP COLUMN "display_name";

Rollback template:

## Migration: add_display_name
### Forward
- ADD COLUMN display_name TEXT
- Backfill display_name from username
### Rollback
- DROP COLUMN display_name
### Data Loss
- Ya: Semua data display_name akan hilang
### Downtime
- Tidak (additive)

7.5 CREATE INDEX CONCURRENTLY

-- Selalu gunakan CONCURRENTLY untuk production
CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_orders_user_id"
ON "orders" ("user_id");

-- Jangan lakukan ini di production (lock table):
-- CREATE INDEX "idx_orders_user_id" ON "orders" ("user_id");

Catatan:

• CONCURRENTLY tidak bisa dijalankan dalam transaction block.
• Pisahkan CREATE INDEX CONCURRENTLY di statement terpisah (di luar transaction).
• Gagal menjalankan CONCURRENTLY akan meninggalkan "invalid" index — monitor dan drop jika perlu.

7.6 Migration Safety Checklist

• Migration additive? (ADD COLUMN, CREATE TABLE, CREATE INDEX)
• Backward compatible? (Old query masih bekerja)
• Rollback script sudah disiapkan?
• Data transformation sudah di-test?
• Data loss implications sudah di-dokumentasi?
• Production large table? Gunakan CONCURRENTLY.
• Migration bisa di-run dalam waktu singkat? (batch jika perlu)
• Sudah di-test di staging environment?
• Monitor rencana (alerting, metric) sudah siap?
• Backup database sudah dilakukan?

---

8. Prisma Transaction Patterns

8.1 Interactive Transaction

// ✅ Interactive transaction — multi-step atomic operation
const result = await prisma.$transaction(async (tx) => {
  // Step 1: Create order
  const order = await tx.order.create({
    data: {
      userId: userId,
      totalAmount: total,
      status: 'PENDING',
    },
  });

  // Step 2: Update inventory (reduce stock)
  for (const item of items) {
    const inventory = await tx.inventory.update({
      where: { productId: item.productId },
      data: { stock: { decrement: item.quantity } },
    });

    if (inventory.stock < 0) {
      throw new Error(`Insufficient stock for product ${item.productId}`);
    }
  }

  // Step 3: Create order items
  await tx.orderItem.createMany({
    data: items.map(item => ({
      orderId: order.id,
      productId: item.productId,
      quantity: item.quantity,
      price: item.price,
    })),
  });

  return order;
}, {
  timeout: 10000,       // Max 10 detik
  maxWait: 5000,        // Max 5 detik untuk mendapatkan connection
  isolationLevel: Prisma.TransactionIsolationLevel.Serializable, // Serializable isolation
});

Isolation levels:

enum TransactionIsolationLevel {
  ReadUncommitted  = 'ReadUncommitted',
  ReadCommitted    = 'ReadCommitted',   // Default PostgreSQL
  RepeatableRead   = 'RepeatableRead',
  Serializable     = 'Serializable',    // Strictest, use for financial transactions
}

8.2 Nested Write (Batch Write)

// ✅ Nested write — create related records in 1 query
const user = await prisma.user.create({
  data: {
    email: '[e-mail]',
    profile: {
      create: {
        fullName: 'John Doe',
        bio: 'Software Engineer',
      },
    },
    posts: {
      create: [
        { title: 'Post 1', content: 'Content 1' },
        { title: 'Post 2', content: 'Content 2' },
      ],
    },
  },
  include: {
    profile: true,
    posts: true,
  },
});

8.3 Batch Operations

// ✅ Bulk create (atomic, 1 query)
await prisma.post.createMany({
  data: [
    { title: 'Post A', authorId: userId },
    { title: 'Post B', authorId: userId },
  ],
  skipDuplicates: true, // Skip if unique constraint violation
});

// ✅ Bulk update
await prisma.post.updateMany({
  where: { authorId: userId },
  data: { status: 'PUBLISHED' },
});

// ✅ Bulk delete
await prisma.post.deleteMany({
  where: { authorId: userId, status: 'ARCHIVED' },
});

8.4 Retry Logic (Deadlock / Serialization)

// ✅ Retry transaction on serialization failure
async function withRetry<T>(
  fn: (tx: Prisma.TransactionClient) => Promise<T>,
  maxRetries = 3,
  isolationLevel = Prisma.TransactionIsolationLevel.Serializable,
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await prisma.$transaction(fn, {
        isolationLevel,
        timeout: 10000,
      });
    } catch (error) {
      // P2034: Transaction failed due to write conflict
      if (error instanceof Prisma.PrismaClientKnownRequestError && error.code === 'P2034') {
        if (attempt === maxRetries) throw error;
        console.warn(`Transaction conflict, retrying (${attempt}/${maxRetries})...`);
        continue;
      }
      throw error;
    }
  }
  throw new Error('Max retries exceeded');
}

8.5 Transaction Best Practices

Pattern	Use Case	Notes
$transaction([...])	Multiple independent queries	Batch of reads, batch of writes independent
$transaction(async (tx) => {})	Related multi-step operation	Interactive, reads depend on writes
createMany	Bulk insert	1 query, faster than loop
updateMany	Bulk update	1 query, atomic
Retry logic	Serializable isolation	Handle P2034 error
Connection pool	Production	Set maxWait realistic

8.6 Pitfalls to Avoid

// ❌ BURUK — Sequential create inside transaction (N+1 writes)
await prisma.$transaction(async (tx) => {
  for (const item of items) {
    await tx.orderItem.create({ data: item }); // N queries!
  }
});

// ✅ BAIK — Batch create
await prisma.$transaction(async (tx) => {
  await tx.orderItem.createMany({ data: items }); // 1 query!
});

// ❌ BURUK — No timeout
await prisma.$transaction(async (tx) => {
  // Long operation — bisa hold connection forever
});

// ✅ BAIK — With timeout
await prisma.$transaction(async (tx) => {
  // Safe operation with timeout
}, { timeout: 5000 });

---

9. Error Reference (Prisma Known Error Codes)

Code	Error	Cause	Fix
P2002	Unique constraint violation	Duplicate value	Handle with try-catch, return 409
P2025	Record not found	update/delete on non-existent record	Check result === null or use updateMany
P2014	Relation violation	Foreign key constraint failed	Ensure referenced record exists
P2003	Foreign key constraint	Invalid reference ID	Validate ID before write
P2034	Transaction conflict	Write conflict on serializable isolation	Implement retry logic
P1001	Connection refused	Database not accessible	Check DB host/port/credentials
P1000	Authentication failed	Wrong credentials	Verify DATABASE_URL
P1017	Server closed connection	Pool exhaustion or idle timeout	Increase pool size, set idle timeout

---

10. Quick Reference — Command Cheatsheet

# Prisma CLI
npx prisma migrate dev --name <description>     # Dev migration
npx prisma migrate deploy                        # Prod migration
npx prisma migrate status                        # Check status
npx prisma migrate resolve                       # Resolve conflict
npx prisma generate                              # Generate client
npx prisma db push                               # Push schema directly (dev only)
npx prisma db pull                               # Introspect DB to schema
npx prisma studio                                # GUI data browser

# Database
psql -U user -d dbname -c "EXPLAIN ANALYZE <query>"
CREATE INDEX CONCURRENTLY IF NOT EXISTS ...
pgbouncer -d /etc/pgbouncer/pgbouncer.ini

# Monitoring
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC;
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_locks WHERE NOT granted;