46 lines
2.5 KiB
SQL
46 lines
2.5 KiB
SQL
-- A cash advance is money handed to a team up front so the team can go shopping
|
|
-- (kasbon in the Indonesian UI). It is deliberately not an expense: while the money
|
|
-- sits with the team it is still the outlet's, and what was actually spent is read
|
|
-- from the purchase orders and expenses charged back to the advance. Nothing about
|
|
-- that spending is copied here.
|
|
CREATE TABLE cash_advances (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
outlet_id UUID NOT NULL REFERENCES outlets(id) ON DELETE CASCADE,
|
|
code_number VARCHAR(50) NOT NULL,
|
|
-- Same team shape as purchase_orders, with one difference: an advance is handed
|
|
-- to a team, so there is no "no team chosen yet" state and team_scope is NOT NULL.
|
|
team_scope VARCHAR(20) NOT NULL,
|
|
team_category_id UUID REFERENCES categories(id) ON DELETE RESTRICT,
|
|
amount DECIMAL(15,2) NOT NULL DEFAULT 0,
|
|
-- Cash the team brought back unspent. Spending is not stored: it is summed from
|
|
-- the purchase orders and expenses that point at this advance.
|
|
returned_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
|
|
issued_date DATE NOT NULL,
|
|
due_date DATE,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft',
|
|
description TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
-- Written as a CASE for the same reason as purchase_orders: an OR chain would
|
|
-- evaluate to NULL for an unexpected scope and a CHECK only rejects FALSE.
|
|
CONSTRAINT chk_cash_advances_team CHECK (
|
|
CASE
|
|
WHEN team_scope = 'category' THEN team_category_id IS NOT NULL
|
|
WHEN team_scope = 'central' THEN team_category_id IS NULL
|
|
ELSE false
|
|
END
|
|
),
|
|
CONSTRAINT chk_cash_advances_amounts CHECK (amount >= 0 AND returned_amount >= 0)
|
|
);
|
|
|
|
-- Leading with organization_id means this also serves the plain per-organization
|
|
-- lookups, so there is no separate index on that column.
|
|
CREATE UNIQUE INDEX idx_cash_advances_organization_id_code_number ON cash_advances(organization_id, code_number);
|
|
CREATE INDEX idx_cash_advances_outlet_id ON cash_advances(outlet_id);
|
|
CREATE INDEX idx_cash_advances_team_category_id ON cash_advances(team_category_id);
|
|
CREATE INDEX idx_cash_advances_team_scope ON cash_advances(team_scope);
|
|
CREATE INDEX idx_cash_advances_issued_date ON cash_advances(issued_date);
|
|
CREATE INDEX idx_cash_advances_status ON cash_advances(status);
|