100 lines
3.3 KiB
SQL
100 lines
3.3 KiB
SQL
-- Migration 012: Create financial tables
|
|
-- Labor: rate per account address (exactly ONE active per location, non-overlapping dates)
|
|
-- Revenue: expected revenue per account (exactly ONE active, non-overlapping dates)
|
|
-- Invoice: billing document with Wave integration
|
|
|
|
-- Labor rate for account address
|
|
CREATE TABLE labor (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
account_address_id UUID REFERENCES account_addresses(id) ON DELETE CASCADE,
|
|
|
|
amount DECIMAL(10, 2) NOT NULL,
|
|
|
|
-- Validity period (non-overlapping per location)
|
|
start_date DATE NOT NULL,
|
|
end_date DATE
|
|
);
|
|
|
|
-- Constraint: non-overlapping labor rates per account address
|
|
ALTER TABLE labor ADD CONSTRAINT labor_no_overlap
|
|
EXCLUDE USING gist (
|
|
account_address_id WITH =,
|
|
daterange(start_date, COALESCE(end_date, '9999-12-31'::DATE), '[]') WITH &&
|
|
);
|
|
|
|
-- Revenue expectation for account
|
|
CREATE TABLE revenues (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
|
|
amount DECIMAL(10, 2) NOT NULL,
|
|
|
|
-- Validity period (non-overlapping per account)
|
|
start_date DATE NOT NULL,
|
|
end_date DATE,
|
|
|
|
-- Wave accounting integration
|
|
wave_service_id VARCHAR(255)
|
|
);
|
|
|
|
-- Constraint: non-overlapping revenues per account
|
|
ALTER TABLE revenues ADD CONSTRAINT revenues_no_overlap
|
|
EXCLUDE USING gist (
|
|
account_id WITH =,
|
|
daterange(start_date, COALESCE(end_date, '9999-12-31'::DATE), '[]') WITH &&
|
|
);
|
|
|
|
-- Customer invoice
|
|
CREATE TABLE invoices (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
|
|
date DATE NOT NULL,
|
|
status invoice_status NOT NULL DEFAULT 'DRAFT',
|
|
date_paid DATE,
|
|
payment_type payment_type,
|
|
|
|
-- Wave accounting integration
|
|
wave_invoice_id VARCHAR(255)
|
|
);
|
|
|
|
-- M2M: Invoice to projects
|
|
CREATE TABLE invoice_projects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
|
|
UNIQUE (invoice_id, project_id)
|
|
);
|
|
|
|
-- M2M: Invoice to revenues
|
|
CREATE TABLE invoice_revenues (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
|
|
revenue_id UUID NOT NULL REFERENCES revenues(id) ON DELETE CASCADE,
|
|
|
|
UNIQUE (invoice_id, revenue_id)
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_labor_address ON labor(account_address_id);
|
|
CREATE INDEX idx_revenues_account ON revenues(account_id);
|
|
CREATE INDEX idx_invoices_customer ON invoices(customer_id);
|
|
CREATE INDEX idx_invoices_date ON invoices(date);
|
|
CREATE INDEX idx_invoices_status ON invoices(status);
|
|
CREATE INDEX idx_invoice_projects_invoice ON invoice_projects(invoice_id);
|
|
CREATE INDEX idx_invoice_revenues_invoice ON invoice_revenues(invoice_id);
|