20 lines
870 B
SQL
20 lines
870 B
SQL
CREATE TABLE IF NOT EXISTS participants (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
INSERT INTO participants (name) VALUES ('Me') ON CONFLICT DO NOTHING;
|
|
|
|
CREATE TABLE IF NOT EXISTS transaction_splits (
|
|
id SERIAL PRIMARY KEY,
|
|
transaction_id INTEGER NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
|
|
participant_id INTEGER NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
|
|
share_percent NUMERIC(5,2) NOT NULL CHECK (share_percent > 0 AND share_percent <= 100),
|
|
settled BOOLEAN DEFAULT FALSE,
|
|
settled_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE (transaction_id, participant_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_splits_txn ON transaction_splits(transaction_id);
|
|
CREATE INDEX IF NOT EXISTS idx_splits_participant ON transaction_splits(participant_id);
|