Database Conventions¶

PostgreSQL + sqlc + Atlas 스택에서의 SQL 작성, 쿼리 구성, 트랜잭션 관리 규칙. 스키마 변경 전략은 별도 data/migration-strategy.md 참조.

Why conventions matter¶

sqlc 는 SQL 을 Go 코드로 바꾸는 도구라 SQL 품질이 코드 품질을 결정한다. 일관된 네이밍, 쿼리 스타일, 인덱스 전략이 없으면 같은 패턴을 여러 도메인이 다르게 구현해 유지보수 비용이 커진다.

Stack¶

Schema-per-domain¶

각 도메인은 독립 PostgreSQL schema 소유 (ADR-0020):

identity / guild / member / licensing / billing / recovery / notification / audit / webhook / events

모든 FK 참조는 {schema}.{table}(column) 형식 명시. search_path 의존 금지.

-- ✓ explicit
guild_id UUID REFERENCES guild.guilds(id)

-- ❌ search_path dependent
guild_id UUID REFERENCES guilds(id)

Naming¶

Tables¶

• snake_case 복수형: subscriptions, payment_attempts, billing_keys
• 도메인 schema 안에 위치: billing.subscriptions

Columns¶

• snake_case: created_at, payer_user_id, current_period_end
• FK 는 {참조_테이블_단수}_id: guild_id, user_id, plan_id
• 다른 도메인 참조는 prefix 명확: payer_user_id (billing 입장에서 identity.users 참조)
• Boolean 은 is_ / has_ / can_ 접두 또는 enabled, cancel_at_period_end 같은 서술적

Indexes¶

{table}_{columns}_idx                  # 일반 인덱스
{table}_{columns}_unique               # UNIQUE 제약
{table}_{columns}_partial_idx          # 부분 인덱스
{table}_pkey                           # PRIMARY KEY (자동)

예: subscriptions_next_billing_idx, licenses_guild_active_unique.

Constraints¶

• CHECK: {table}_{rule}_check — subscriptions_retry_range_check
• FK: {table}_{column}_fkey (자동)
• UNIQUE: {table}_{columns}_unique

Primary keys¶

UUID v7 (default)¶

모든 테이블 PK 는 UUID v7 (ADR-0021). Go 에서 platform/uuid.NewV7() 호출.

id UUID PRIMARY KEY

• 시간순 정렬됨 (B-tree 인덱스 효율)
• 외부 노출 안전 (순차 ID 추론 방지)

BIGSERIAL 예외¶

events.outbox.id 만 BIGSERIAL. 이유:

• 순서 보장이 중요 (Poller 가 ORDER BY id ASC 로 스캔)
• 외부 참조 없음
• 페이지네이션 WHERE id > $last 단순

예외는 이 테이블 하나. 다른 곳은 UUID.

Column types¶

Preferred types¶

Avoid¶

• VARCHAR(n) — 성능은 TEXT 와 동일하고 길이 제한은 CHECK 가 명확
• TIMESTAMP without TIMEZONE — tz-naive 는 미래 버그 원인
• PostgreSQL ENUM — 값 추가/삭제가 ALTER TYPE 필요해서 마이그레이션 복잡

Nullable vs default¶

• Nullable 은 "없을 수 있음" 이 의미 있을 때만
• 안 정할 수 있으면 NOT NULL DEFAULT ... 선호

-- ✓ 의미 있는 nullable
canceled_at TIMESTAMPTZ,  -- 해지 안 했으면 NULL

-- ✓ default
retry_count INTEGER NOT NULL DEFAULT 0,

-- ❌ 모호한 nullable
cycle_count INTEGER,  -- 0 으로 충분

Constraints¶

CHECK¶

비즈니스 규칙을 DB 레벨 강제:

CREATE TABLE billing.subscriptions (
    ...
    status TEXT NOT NULL CHECK (status IN ('pending', 'active', 'past_due', 'canceled', 'suspended')),
    retry_count INTEGER NOT NULL DEFAULT 0 CHECK (retry_count >= 0 AND retry_count <= 4),
    cycle_count INTEGER NOT NULL DEFAULT 0 CHECK (cycle_count >= 0)
);

복합 규칙은 named constraint:

CONSTRAINT plans_free_no_billing CHECK (
    (code = 'FREE' AND price_krw IS NULL AND billing_cycle IS NULL)
    OR (code != 'FREE')
)

Partial unique index¶

조건부 유일성 (특정 상태에서만 유일):

-- Guild 당 active/past_due subscription 하나
CREATE UNIQUE INDEX subscriptions_guild_active_unique
ON billing.subscriptions (guild_id)
WHERE status IN ('active', 'past_due');

이 패턴 Licensing, Restore jobs 등에도 자주 등장.

FK on delete¶

• RESTRICT (기본) — 참조 중이면 삭제 불가. 안전.
• SET NULL — User 탈퇴처럼 "참조 주체는 사라지지만 데이터는 유지" 시
• CASCADE — 매우 제한적. 1:1 sub-table 정도 (예: GuildConfig → Guild)

-- ✓ Audit 에서 User 탈퇴 대응
actor_user_id UUID REFERENCES identity.users(id) ON DELETE SET NULL,

-- ✓ 1:1 관계 cascade
FOREIGN KEY (guild_id) REFERENCES guild.guilds(id) ON DELETE CASCADE

Indexes¶

설계 원칙¶

• 쓰기 경로의 hot query 만 인덱싱 (무분별한 인덱스는 쓰기 비용 증가)
• 복합 인덱스 컬럼 순서: 선택도 높은 것 먼저
• Partial index 로 성능 + 공간 절약

필수 인덱스 패턴¶

-- FK 는 대부분 인덱스 필요 (역참조 조회)
CREATE INDEX subscriptions_guild_idx ON billing.subscriptions (guild_id);

-- 시계열 조회
CREATE INDEX audit_events_guild_occurred_idx ON audit.events (guild_id, occurred_at DESC);

-- 상태 + 시간
CREATE INDEX subscriptions_due_idx
ON billing.subscriptions (next_billing_at)
WHERE status IN ('active', 'past_due');

인덱스 타입¶

• B-tree (default) — 대부분
• GIN — JSONB, array, full-text
• GIST — 공간, 범위 (이번 프로젝트에서 드묾)

GIN 예시:

CREATE INDEX members_role_ids_gin ON member.members USING GIN (role_ids);
-- role_ids && ARRAY['role_id'] 쿼리 최적화

SQL file organization¶

Location¶

db/queries/
├─ identity/
│  ├─ users.sql
│  └─ sessions.sql
├─ billing/
│  ├─ subscriptions.sql
│  ├─ billing_keys.sql
│  └─ payment_attempts.sql
└─ ...

도메인 schema = 하위 폴더. 파일은 테이블별.

sqlc annotation¶

-- name: GetSubscriptionByID :one
SELECT *
FROM billing.subscriptions
WHERE id = $1;

-- name: ListDueForCharge :many
SELECT *
FROM billing.subscriptions
WHERE status IN ('active', 'past_due')
  AND next_billing_at <= $1
ORDER BY next_billing_at ASC
LIMIT $2;

-- name: InsertSubscription :exec
INSERT INTO billing.subscriptions (
    id, license_id, payer_user_id, guild_id, billing_key_id, plan_id,
    status, current_period_start, current_period_end, next_billing_at,
    cycle_count, retry_count, cancel_at_period_end
) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13
);

-- name: UpdateSubscriptionStatus :exec
UPDATE billing.subscriptions
SET status = $2,
    retry_count = $3,
    next_billing_at = $4,
    updated_at = NOW()
WHERE id = $1;

sqlc directive:

• :one — single row 반환 (없으면 pgx.ErrNoRows)
• :many — 여러 row
• :exec — row 반환 안 함 (INSERT, UPDATE, DELETE)
• :execrows — affected row count 반환

Query 네이밍¶

• PascalCase 동사 + 명사: GetSubscriptionByID, ListDueForCharge, InsertSubscription, UpdateSubscriptionStatus
• 구체적으로: GetSubscriptionByID vs GetSubscription 보다 명확

복잡한 쿼리는 CTE¶

-- name: GetGuildSummary :one
WITH license_info AS (
    SELECT l.*, p.code AS plan_code
    FROM licensing.licenses l
    JOIN licensing.plans p ON p.id = l.plan_id
    WHERE l.guild_id = $1 AND l.status = 'active'
),
member_count AS (
    SELECT COUNT(*) AS count
    FROM member.members
    WHERE guild_id = $1 AND left_at IS NULL
)
SELECT
    g.id,
    g.name,
    g.slug,
    l.plan_code,
    l.expires_at,
    m.count AS member_count
FROM guild.guilds g
LEFT JOIN license_info l ON l.guild_id = g.id
CROSS JOIN member_count m
WHERE g.id = $1;

CTE 는 가독성 + 재사용. 단순 쿼리는 CTE 불필요.

sqlc config¶

sqlc.yaml 예시:

version: "2"
sql:
  - engine: "postgresql"
    queries: "db/queries/billing"
    schema: "db/schema"
    gen:
      go:
        package: "billingsqlc"
        out: "engine/billing/adapter/persistence/sqlc"
        emit_json_tags: false           # domain 에서만 JSON tag
        emit_prepared_queries: false
        emit_interface: true            # Queries interface 생성
        emit_empty_slices: true
        sql_package: "pgx/v5"
        sql_driver: "github.com/jackc/pgx/v5"
        overrides:
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"

각 도메인별 독립 패키지 생성. Import 충돌 없음.

Transactions¶

WithTx pattern¶

도메인 서비스가 여러 테이블 수정 시:

// platform/db/tx.go
type TxManager interface {
    WithTx(ctx context.Context, fn func(txRepos TxRepos) error) error
}

type TxRepos struct {
    Subs     port.SubscriptionRepository
    Keys     port.BillingKeyRepository
    Attempts port.PaymentAttemptRepository
    Events   port.EventPublisher
}

구현:

func (m *pgxTxManager) WithTx(ctx context.Context, fn func(TxRepos) error) error {
    tx, err := m.pool.Begin(ctx)
    if err != nil { return err }
    defer tx.Rollback(ctx)

    q := sqlc.New(tx)  // 같은 tx 를 사용

    repos := TxRepos{
        Subs:     subscriptionsqlc.NewRepo(q),
        Keys:     billingkeysqlc.NewRepo(q),
        Attempts: paymentattemptsqlc.NewRepo(q),
        Events:   outbox.NewPublisher(q),
    }

    if err := fn(repos); err != nil {
        return err  // deferred Rollback
    }

    return tx.Commit(ctx)
}

사용:

err := s.tx.WithTx(ctx, func(tx TxRepos) error {
    tx.Subs.Insert(ctx, sub)
    tx.Events.Publish(ctx, SubscriptionStartedEvent{ ... })
    return nil
})

트랜잭션 격리 수준¶

• 기본 READ COMMITTED 사용 (PostgreSQL default)
• 특수 케이스만 SERIALIZABLE (예: race 가능성이 매우 높은 재고 차감 — Umbra 에는 드묾)
• REPEATABLE READ 는 Snapshot 일관성 필요 시

트랜잭션 규칙¶

• 짧게 유지 — 외부 API 호출을 트랜잭션 안에 넣지 않기
• 단일 원자성 목적 — "A + B + event" 처럼 원자 단위만
• 재시도 가능성 대비 — Idempotent operations

// ❌ Long transaction
err := tx.WithTx(ctx, func(tx) error {
    tx.Subs.Insert(ctx, sub)
    tossResult := s.toss.Charge(ctx, ...)  // 외부 호출
    tx.Attempts.Insert(ctx, attempt)
    return nil
})

// ✓ Short transactions
sub := prepareSubscription(input)
err := tx.WithTx(ctx, func(tx) error {
    return tx.Subs.Insert(ctx, sub)
})
if err != nil { return err }

tossResult, err := s.toss.Charge(ctx, ...)  // 트랜잭션 밖
if err != nil { return err }

err = tx.WithTx(ctx, func(tx) error {
    tx.Attempts.Insert(ctx, attempt)
    tx.Subs.Update(ctx, sub.WithChargeResult(tossResult))
    return nil
})

Idempotency¶

ON CONFLICT¶

UPSERT 패턴 명시:

-- name: UpsertMember :exec
INSERT INTO member.members (
    id, guild_id, discord_user_id, user_id, joined_via, joined_at, role_ids
) VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (guild_id, discord_user_id) DO UPDATE SET
    user_id = EXCLUDED.user_id,
    joined_at = EXCLUDED.joined_at,
    role_ids = EXCLUDED.role_ids,
    left_at = NULL,
    updated_at = NOW();

GREATEST¶

멱등한 연장:

-- name: ExtendLicenseExpiresAt :exec
UPDATE licensing.licenses
SET expires_at = GREATEST(expires_at, $2),
    updated_at = NOW()
WHERE id = $1;

Outbox dedup¶

Audit / Notification 등에서 outbox event 중복 수신 대응:

-- name: InsertAuditEvent :exec
INSERT INTO audit.events (
    id, outbox_event_id, aggregate_type, aggregate_id,
    event_type, actor_user_id, actor_type, guild_id,
    payload, summary, occurred_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
ON CONFLICT (outbox_event_id) DO NOTHING;

Security¶

SQL injection¶

• sqlc generated code 는 parameterized query 자동 보장
• raw SQL 사용 금지 (조합 쿼리도 sqlc 안에서)

PII 처리¶

• 이메일, 개인 식별 정보는 최소 저장
• identity.users.username 은 Discord 공개 정보 캐시라 허용
• 결제 정보는 암호화 (AES-256-GCM)

Role / GRANT¶

MVP 는 단일 role 로 운영. Phase 2 에서 schema 별 GRANT 분리 (운영 권한과 애플리케이션 권한 분리).

Performance¶

Connection pool¶

config, _ := pgxpool.ParseConfig(databaseURL)
config.MaxConns = 20       // per process
config.MinConns = 2
config.MaxConnIdleTime = 5 * time.Minute
config.MaxConnLifetime = 1 * time.Hour

Fly.io 의 각 process 당 pool. Neon 의 connection 한도 고려.

N+1 query 회피¶

// ❌ N+1
for _, sub := range subs {
    plan, _ := plansRepo.Get(ctx, sub.PlanID)
    ...
}

// ✓ JOIN 또는 IN
plans, _ := plansRepo.ListByIDs(ctx, planIDs)

sqlc 의 :many + $1::uuid[] 패턴:

-- name: ListPlansByIDs :many
SELECT * FROM licensing.plans WHERE id = ANY($1::uuid[]);

EXPLAIN¶

복잡한 쿼리는 EXPLAIN ANALYZE 로 플랜 확인. 특히:

• Sequential scan 이 hot query 에 발생하면 인덱스 추가
• 예상보다 느린 쿼리

Monitoring¶

pgx instrumentation¶

config.ConnConfig.Tracer = otelpgx.NewTracer()

OpenTelemetry 로 쿼리 latency, connection pool 상태 추적.

Slow query log¶

Neon 설정 또는 애플리케이션 레이어에서 slow (>100ms) 쿼리 로깅:

start := time.Now()
result, err := q.GetSubscriptionByID(ctx, id)
dur := time.Since(start)
if dur > 100 * time.Millisecond {
    logger.Warn("slow query", "op", "GetSubscriptionByID", "duration", dur)
}

Do / Don't¶

Do¶

• ✅ Schema-per-domain
• ✅ UUID v7 PK (outbox 예외)
• ✅ TIMESTAMPTZ only
• ✅ CHECK 제약으로 enum 표현
• ✅ Partial unique index 로 조건부 유일성
• ✅ sqlc generated code 사용
• ✅ WithTx 래퍼로 트랜잭션
• ✅ ON CONFLICT 로 idempotency

Don't¶

• ❌ VARCHAR(n)
• ❌ TIMESTAMP (no tz)
• ❌ PostgreSQL ENUM type
• ❌ ORM (GORM 등)
• ❌ Raw SQL in handlers
• ❌ Long transactions (외부 API 호출 포함)
• ❌ Search_path 의존

See also¶

• backend-conventions.md
• hexagonal-pattern.md
• ../data/schema-overview.md
• ../data/migration-strategy.md
• ../adr/0004-database-neon-postgres.md
• ../adr/0005-db-layer-sqlc.md
• ../adr/0006-migration-atlas.md
• ../adr/0020-postgres-schema-per-domain.md
• ../adr/0021-pk-uuid-v7.md