20 lines
1005 B
SQL
20 lines
1005 B
SQL
-- 003: Wallet transactions (user-service)
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
wallet_id UUID NOT NULL REFERENCES wallets(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
|
type VARCHAR(20) NOT NULL CHECK (type IN ('deposit', 'withdraw', 'purchase', 'sale', 'transfer_in', 'transfer_out', 'fee', 'refund', 'breakage')),
|
|
amount NUMERIC(15,2) NOT NULL,
|
|
balance_after NUMERIC(15,2) NOT NULL,
|
|
reference_id UUID,
|
|
reference_type VARCHAR(30),
|
|
description VARCHAR(500),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'completed' CHECK (status IN ('pending', 'completed', 'failed', 'cancelled')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_transactions_wallet_id ON transactions(wallet_id);
|
|
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
|
|
CREATE INDEX idx_transactions_type ON transactions(type);
|
|
CREATE INDEX idx_transactions_created_at ON transactions(created_at DESC);
|