Billing Schema¶

CREATE TABLE billing.billing_keys (
    id                UUID PRIMARY KEY,
    user_id           UUID NOT NULL REFERENCES identity.users(id),
    customer_key      TEXT NOT NULL,
    encrypted_key     BYTEA NOT NULL,
    key_nonce         BYTEA NOT NULL,
    card_company      TEXT NOT NULL,
    card_last4        TEXT NOT NULL,
    card_type         TEXT NOT NULL CHECK (card_type IN ('credit', 'check')),
    issued_at         TIMESTAMPTZ NOT NULL,
    deleted_at        TIMESTAMPTZ,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT billing_keys_customer_key_unique UNIQUE (customer_key),
    CONSTRAINT billing_keys_nonce_size CHECK (length(key_nonce) = 12),
    CONSTRAINT billing_keys_card_last4_format CHECK (card_last4 ~ '^[0-9]{4}$')
);

CREATE TABLE billing.subscriptions (
    id                     UUID PRIMARY KEY,
    license_id             UUID NOT NULL REFERENCES licensing.licenses(id),
    payer_user_id          UUID NOT NULL REFERENCES identity.users(id),
    guild_id               UUID NOT NULL REFERENCES guild.guilds(id),
    billing_key_id         UUID NOT NULL REFERENCES billing.billing_keys(id),
    plan_id                UUID NOT NULL REFERENCES licensing.plans(id),
    status                 TEXT NOT NULL CHECK (status IN ('pending', 'active', 'past_due', 'canceled', 'suspended')),
    current_period_start   TIMESTAMPTZ,
    current_period_end     TIMESTAMPTZ,
    next_billing_at        TIMESTAMPTZ,
    cycle_count            INTEGER NOT NULL DEFAULT 0,
    retry_count            INTEGER NOT NULL DEFAULT 0,
    cancel_at_period_end   BOOLEAN NOT NULL DEFAULT FALSE,
    canceled_at            TIMESTAMPTZ,
    suspended_at           TIMESTAMPTZ,
    suspended_reason       TEXT,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT subscriptions_retry_range CHECK (retry_count >= 0 AND retry_count <= 4),
    CONSTRAINT subscriptions_cycle_nonneg CHECK (cycle_count >= 0)
);

CREATE UNIQUE INDEX subscriptions_guild_active_unique
ON billing.subscriptions (guild_id)
WHERE status IN ('active', 'past_due');

CREATE TABLE billing.payment_attempts (
    id                  UUID PRIMARY KEY,
    subscription_id     UUID NOT NULL REFERENCES billing.subscriptions(id),
    order_id            TEXT NOT NULL,
    amount_krw          INTEGER NOT NULL CHECK (amount_krw > 0),
    status              TEXT NOT NULL CHECK (status IN ('pending', 'succeeded', 'failed')),
    toss_payment_key    TEXT,
    toss_approved_at    TIMESTAMPTZ,
    failure_code        TEXT,
    failure_message     TEXT,
    cycle               INTEGER NOT NULL,
    retry_number        INTEGER NOT NULL CHECK (retry_number >= 0 AND retry_number <= 3),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at        TIMESTAMPTZ,
    CONSTRAINT payment_attempts_order_id_unique UNIQUE (order_id),
    CONSTRAINT payment_attempts_succeeded_has_key CHECK (
        (status = 'succeeded' AND toss_payment_key IS NOT NULL AND toss_approved_at IS NOT NULL)
        OR status != 'succeeded'
    ),
    CONSTRAINT payment_attempts_failed_has_code CHECK (
        (status = 'failed' AND failure_code IS NOT NULL)
        OR status != 'failed'
    )
);

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

INSERT INTO billing.payment_attempts (
    id, subscription_id, order_id, amount_krw, status, cycle, retry_number
) VALUES ($1, $2, $3, $4, 'pending', $5, $6);

UPDATE billing.payment_attempts
SET status = 'succeeded',
    toss_payment_key = $1,
    toss_approved_at = $2,
    completed_at = NOW()
WHERE id = $3;

UPDATE billing.subscriptions
SET current_period_start = current_period_end,
    current_period_end = $1,      -- 새 주기 종료 (jitter 포함)
    next_billing_at = $2,         -- 다음 결제 시각
    cycle_count = cycle_count + 1,
    retry_count = 0,
    status = 'active',
    updated_at = NOW()
WHERE id = $3;

UPDATE billing.subscriptions
SET status = 'past_due',
    retry_count = retry_count + 1,
    next_billing_at = $1,         -- now + (24/48/72h)
    updated_at = NOW()
WHERE id = $2;