howto-develop-with-postgres

# 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

```typescript
// 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/