/howto-develop-with-postgres

name: howto-develop-with-postgres
description: Use when writing database access code, creating schemas, or managing transactions with PostgreSQL - enforces transaction safety with TX_ naming, read-write separation, type safety for UUIDs/JSONB, and snake_case conventions to prevent data corruption and type errors
user-invocable: false

PostgreSQL Development Patterns

Overview

Enforce transaction safety, type safety, and naming conventions to prevent data corruption and runtime errors.

**Core principles:**

• Transactions prevent partial updates (data corruption)
• Type safety catches errors at compile time
• Naming conventions ensure consistency
• Read-write separation prevents accidental mutations

**For TypeScript/Drizzle implementations:** See [typescript-drizzle.md](./typescript-drizzle.md) for concrete patterns.

Transaction Management

TX_ Prefix Rule (STRICT ENFORCEMENT)

**Methods that START transactions:**

• Prefix method name with `TX_`
• Must NOT accept connection/executor parameter
• Call `connection.transaction()` or `db.transaction()` internally

**Methods that PARTICIPATE in transactions:**

• No `TX_` prefix
• MUST accept connection/executor parameter with default value
• Execute queries using the provided executor

// GOOD: Starts transaction, has TX_ prefix, no executor parameter
async TX_createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => {
    const user = await this.createUser(userData, tx);
    await this.createProfile(user.id, profileData, tx);
    return user;
  });
}

// GOOD: Participates in transaction, no TX_ prefix, takes executor
async createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.insert(USERS).values(userData).returning();
}

// BAD: Starts transaction but missing TX_ prefix
async createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => { /* ... */ });
}

// BAD: Has TX_ prefix but takes executor parameter (allows nesting)
async TX_createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.transaction(async (tx) => { /* ... */ });
}

**What DOES NOT count as "starting a transaction":**

• Single INSERT/UPDATE/DELETE operations
• Atomic operations like `onConflictDoUpdate`
• SELECT queries

Type Safety

Primary Keys

**Default: ULID stored as UUID**

• When in doubt, use ULID: "Most things can leak in some way"
• Prevents ID enumeration attacks
• Time-sortable for indexing efficiency

**Exceptions (context-dependent):**

• Pure join tables (composite PK from both FKs)
• Small lookup tables (serial/identity acceptable)
• Internal-only tables with no user visibility (serial/identity acceptable)

**Rule:** If unsure whether data will be user-visible, use ULID.

Financial Data

**Use exact decimal types (numeric/decimal) for monetary values:**

• Never use float/double for money (causes rounding errors)
• Use numeric/decimal with appropriate precision and scale
• Example: `numeric(19, 4)` for general financial data

**Why:** Floating-point types accumulate rounding errors. Exact decimal types prevent financial discrepancies.

JSONB Columns

**ALWAYS type JSONB columns in your ORM/schema:**

• Use typed schema when structure is known
• Use `Record<string, unknown>` if truly schemaless
• Never leave JSONB untyped

**Why:** Prevents runtime errors from accessing undefined properties or wrong types.

Read-Write Separation

**Maintain separate client types at compile time:**

• Read-write client: Full mutation capabilities
• Read-only client: Mutation methods removed at type level
• Default to read-only for query methods
• Use read-write only when mutations needed

**Why:** Prevents accidental writes to replica, enforces deliberate mutation choices.

Naming Conventions

Database Identifiers

**All database objects use snake_case:**

• Tables: `user_preferences`, `order_items`
• Columns: `created_at`, `user_id`, `is_active`
• Indexes: `idx_tablename_columns` (e.g., `idx_users_email`)
• Foreign keys: `fk_tablename_reftable` (e.g., `fk_orders_users`)

**Application code:** Map to idiomatic case (camelCase in TypeScript, etc.)

Schema Patterns

**Standard mixins:**

• `created_at`, `updated_at` timestamps on all tables
• `deleted_at` for soft deletion when needed
• `tenant_id` for multi-tenant tables (project-dependent)

**Proactive indexing:**

• All foreign key columns
• Columns used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY

Concurrency

**Default isolation (Read Committed) for most operations.**

**Use stricter isolation when:**

• Financial operations: Serializable isolation
• Inventory/count operations: Serializable isolation
• Critical sections: Pessimistic locking (`SELECT ... FOR UPDATE`)

Migrations

**Always use generate + migrate workflow:** 1. Change schema in code 2. Generate migration file 3. Review migration SQL 4. Apply migration to database

**Never use auto-push workflow in production.**

Common Mistakes

| Mistake | Reality | Fix | |---------|---------|-----| | "This is one operation, doesn't need transaction" | Multi-step operations without transactions cause partial updates and data corruption | Wrap in transaction with TX_ prefix | | "Single atomic operation needs TX_ prefix" | TX_ is for explicit transaction blocks, not atomic operations | No TX_ for single INSERT/UPDATE/DELETE | | "UUID is just a string" | Type confusion causes runtime errors (wrong ID formats, failed lookups) | Use strict UUID type in language | | "I'll type JSONB later when schema stabilizes" | Untyped JSONB leads to undefined property access and type errors | Type immediately with known fields or Record<string, unknown> | | "Read client vs write client doesn't matter" | Using wrong client bypasses separation, allows accidental mutations | Use read-