james
7a2745fb16
One balance row per (user, kind, currency): available and frozen carry the platform base currency, points carries none. Monetary and points rows use separate partial unique indexes because a plain UNIQUE lets NULL repeat. Balance changes go through transactional primitives that debits guard with a conditional update, credits add atomically, and freeze/release move both sides in one transaction after locking rows by primary key. Every change appends an immutable entry holding its resulting balance. Registration and a migration backfill create the zero rows; GET /api/me/stats is the only public surface and no endpoint mutates a balance. The mall buyer center and points page drop the USER_STATS fixture for the shared contract; the fixture stays exported so the fixed-data adapter can still serve the account domain as a rollback path. Implements openspec change add-customer-accounts.
57 lines
2.3 KiB
SQL
57 lines
2.3 KiB
SQL
-- Customer accounts: one balance row per (user, kind, currency) plus an
|
|
-- append-only entry ledger. Monetary kinds carry a currency; points do not.
|
|
-- The CHECK plus the two partial unique indexes keep those pairs honest and
|
|
-- prevent duplicates (a plain UNIQUE would let NULL currencies repeat).
|
|
|
|
CREATE TYPE customer_account_kind AS ENUM ('available', 'frozen', 'points');
|
|
|
|
CREATE TABLE customer_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
kind customer_account_kind NOT NULL,
|
|
currency CHAR(3) REFERENCES currencies (code),
|
|
balance_minor BIGINT NOT NULL DEFAULT 0 CHECK (balance_minor >= 0),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT customer_accounts_kind_currency CHECK (
|
|
(kind = 'points' AND currency IS NULL)
|
|
OR (kind <> 'points' AND currency IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX customer_accounts_monetary_idx
|
|
ON customer_accounts (user_id, kind, currency)
|
|
WHERE currency IS NOT NULL;
|
|
|
|
CREATE UNIQUE INDEX customer_accounts_points_idx
|
|
ON customer_accounts (user_id, kind)
|
|
WHERE currency IS NULL;
|
|
|
|
CREATE TABLE customer_account_entries (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
account_id UUID NOT NULL REFERENCES customer_accounts (id) ON DELETE CASCADE,
|
|
delta_minor BIGINT NOT NULL CHECK (delta_minor <> 0),
|
|
balance_minor BIGINT NOT NULL CHECK (balance_minor >= 0),
|
|
reason TEXT NOT NULL,
|
|
reference_type TEXT,
|
|
reference_id UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX customer_account_entries_account_idx
|
|
ON customer_account_entries (account_id, created_at DESC);
|
|
|
|
-- Backfill zero rows for users that already exist; new registrations create
|
|
-- their own rows in the same transaction as the user insert.
|
|
INSERT INTO customer_accounts (user_id, kind)
|
|
SELECT id, 'points' FROM users
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
INSERT INTO customer_accounts (user_id, kind, currency)
|
|
SELECT u.id, k.kind, c.code
|
|
FROM users u
|
|
CROSS JOIN (VALUES ('available'::customer_account_kind),
|
|
('frozen'::customer_account_kind)) AS k (kind)
|
|
CROSS JOIN (SELECT code FROM currencies WHERE is_base LIMIT 1) AS c
|
|
ON CONFLICT DO NOTHING;
|