Database

Database kita pakai Neon Postgres — serverless PostgreSQL yang terintegrasi dengan Cloudflare Hyperdrive untuk connection pooling di edge. ORM-nya pakai Drizzle ORM + drizzle-kit untuk schema definition dan migrations.

Neon + Hyperdrive Setup

Hyperdrive di-bind ke Workers via wrangler.jsonc:

{
  "hyperdrive": [
    {
      "binding": "HYPERDRIVE",
      "id": "your-hyperdrive-config-id"
    }
  ]
}

Binding name HYPERDRIVE ini akan muncul sebagai env.HYPERDRIVE di Worker fetch handler. Hyperdrive menyediakan connection pooling dan cache untuk query ke Neon Postgres.

Untuk environment tanpa Hyperdrive (misalnya local dev tanpa binding), gunakan fallback DATABASE_URL dari environment variables.

Drizzle Configuration

Drizzle Kit Config

packages/db/drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema",
  out: "./migrations",
});

Database Client Factory

packages/db/src/index.ts:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

export function createDb(connectionString: string) {
  const client = postgres(connectionString);
  return drizzle(client, { schema });
}

export type DbClient = ReturnType<typeof createDb>;

Client dibuat per-request di apps/api/src/index.ts — tidak boleh di-share antar request. Gunakan env.HYPERDRIVE.connectionString bila tersedia, atau fallback ke env.DATABASE_URL.

Schema Structure

Auth Tables

Auth schema ada di packages/db/src/schema/auth.ts, di-generate oleh better-auth:

Table	Purpose
user	Data user: id, email, name, image, role
session	Session cookies: token, userId, expiresAt
account	OAuth accounts: provider, providerAccountId
verification	Email verification tokens

Role Column

Kolom role di tabel user di-manage oleh better-auth via additionalFields:

additionalFields: {
  role: {
    type: "string",
    defaultValue: "member",
    required: false,
  },
}

Valid values: member, admin, owner.

Business Tables

Table	Purpose
journal_entries	User journal entries
compatibility_results	Compatibility analysis results
deep_insights_purchases	One-time purchase records
transactions	Payment transaction ledger
usage	Per-user feature usage credits
audit_logs	Audit trail untuk semua data changes

transactions — Payment Ledger

Column	Type	Description
id	uuid	Primary key
user_id	uuid	References user.id
invoice_number	text	Doku invoice number (unique)
product_id	text	e.g. deep-insights, deep-insights-single
status	text	pending | paid | failed | cancelled | expired
amount	integer	Amount in IDR
entitlement_granted_at	timestamptz	Idempotency stamp — set atomically when plan upgrade + token credit succeed. NULL = not yet granted. Used by the cron reconciler and webhook to prevent double-grant.
paid_at	timestamptz	When status transitioned to paid
created_at	timestamptz	Row creation
updated_at	timestamptz	Last update (auto)

usage — Per-User Credit Ledger

Column	Type	Description
id	uuid	Primary key
user_id	uuid	References user.id (unique — one row per user)
deep_insights_tokens	integer	Purchased token balance. Default 0. Atomically incremented by the entitlement grant funnel via INSERT … ON CONFLICT DO UPDATE SET tokens = tokens + n.
free_analysis_remaining	integer	Free analysis quota. Default 1. Same upsert pattern as tokens.
daily_analysis_count	integer	How many analyses done today. Reset when daily_analysis_date changes.
daily_analysis_date	text	Date string (YYYY-MM-DD) of last analysis day.
created_at	timestamptz	Row creation
updated_at	timestamptz	Last update (auto, also updated on balance change)

One-row-per-user invariant: user_id has a unique index (usage_user_id_unique). The token-credit upsert is atomic — no read-then-write race window.

Dedupe migration note: Migration 0002_wide_kat_farrell.sql deduplicates any pre-constraint duplicate rows before adding the unique index, using a SUM merge rule (sum all deep_insights_tokens + free_analysis_remaining across duplicates, keep oldest row). Applying this migration to production is a human/deploy step.

Migrations

Generate Migration

cd packages/db
npx drizzle-kit generate

Apply Migration (Local & Production)

cd packages/db
npx drizzle-kit migrate

drizzle-kit migrate akan membaca DATABASE_URL dari environment dan menjalankan migrations yang belum di-apply. Untuk local development, pastikan DATABASE_URL di-set di .dev.vars atau environment lokal.

Schema Conventions

• Table names: snake_case, plural (e.g., journal_entries).
• Column names: camelCase di schema definition, Drizzle akan map ke snake_case di PostgreSQL.
• Timestamps: pakai createdAt dan updatedAt di setiap table.
• Soft delete: tambahin kolom deletedAt (nullable) buat data yang boleh di-restore.
• Foreign keys: selalu define dengan references dan onDelete rule.

Environment Variables

Variable	Required	Description
DATABASE_URL	Yes*	Direct connection string ke Neon Postgres. Wajib sebagai fallback bila Hyperdrive tidak tersedia.
HYPERDRIVE	No	Hyperdrive binding untuk connection pooling (di Workers via wrangler.jsonc).

*Di production via Workers, Hyperdrive binding direkomendasikan. DATABASE_URL tetap diperlukan untuk drizzle-kit migrate dan fallback.

References

• Drizzle ORM Docs
• Neon Docs
• Cloudflare Hyperdrive Docs
• Data Governance
• Auth Schema Details