373 lines
No EOL
16 KiB
SQL
373 lines
No EOL
16 KiB
SQL
-- Financials Plugin Database Schema
|
|
-- This schema is isolated in the plugin_financials namespace
|
|
|
|
-- Chart of Accounts
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.chart_of_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
account_code VARCHAR(20) NOT NULL,
|
|
account_name VARCHAR(255) NOT NULL,
|
|
account_type VARCHAR(50) NOT NULL, -- asset, liability, equity, revenue, expense
|
|
parent_account_id UUID REFERENCES plugin_financials.chart_of_accounts(id),
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL,
|
|
UNIQUE(site_id, account_code)
|
|
);
|
|
|
|
-- Budgets
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.budgets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
fiscal_year INTEGER NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
total_amount DECIMAL(15,2) NOT NULL,
|
|
status VARCHAR(20) DEFAULT 'draft', -- draft, active, closed
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Budget Items
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.budget_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
budget_id UUID NOT NULL REFERENCES plugin_financials.budgets(id) ON DELETE CASCADE,
|
|
account_id UUID NOT NULL REFERENCES plugin_financials.chart_of_accounts(id),
|
|
planned_amount DECIMAL(15,2) NOT NULL,
|
|
actual_amount DECIMAL(15,2) DEFAULT 0,
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Transactions
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.transactions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
transaction_date DATE NOT NULL,
|
|
reference_number VARCHAR(50),
|
|
description TEXT NOT NULL,
|
|
transaction_type VARCHAR(20) NOT NULL, -- income, expense, transfer, opening_balance, adjustment
|
|
amount DECIMAL(15,2) NOT NULL,
|
|
currency VARCHAR(3) DEFAULT 'USD',
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, approved, rejected, posted
|
|
category VARCHAR(100),
|
|
vendor_id UUID,
|
|
unit_id UUID,
|
|
payment_method VARCHAR(50),
|
|
notes TEXT,
|
|
attachments JSONB,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL,
|
|
approved_by UUID,
|
|
approved_at TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
-- Transaction Lines (Double-entry accounting)
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.transaction_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
transaction_id UUID NOT NULL REFERENCES plugin_financials.transactions(id) ON DELETE CASCADE,
|
|
account_id UUID NOT NULL REFERENCES plugin_financials.chart_of_accounts(id),
|
|
debit_amount DECIMAL(15,2) DEFAULT 0,
|
|
credit_amount DECIMAL(15,2) DEFAULT 0,
|
|
description TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Expenses
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.expenses (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
expense_date DATE NOT NULL,
|
|
vendor_name VARCHAR(255),
|
|
vendor_id UUID,
|
|
category VARCHAR(100) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
amount DECIMAL(15,2) NOT NULL,
|
|
tax_amount DECIMAL(15,2) DEFAULT 0,
|
|
total_amount DECIMAL(15,2) NOT NULL,
|
|
payment_status VARCHAR(20) DEFAULT 'pending', -- pending, paid, overdue
|
|
payment_method VARCHAR(50),
|
|
receipt_url VARCHAR(500),
|
|
approved_by UUID,
|
|
approved_at TIMESTAMP WITH TIME ZONE,
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Revenue/Income
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.revenue (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
revenue_date DATE NOT NULL,
|
|
source VARCHAR(100) NOT NULL, -- maintenance_fees, late_fees, special_assessments, etc.
|
|
description TEXT NOT NULL,
|
|
amount DECIMAL(15,2) NOT NULL,
|
|
unit_id UUID,
|
|
resident_id UUID,
|
|
payment_status VARCHAR(20) DEFAULT 'pending', -- pending, received, overdue
|
|
payment_method VARCHAR(50),
|
|
receipt_url VARCHAR(500),
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Unit Balance Tracking
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.unit_balances (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
unit_id UUID NOT NULL,
|
|
site_id UUID NOT NULL,
|
|
current_balance DECIMAL(15,2) DEFAULT 0, -- Current total balance (calculated from transactions)
|
|
last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL,
|
|
UNIQUE(unit_id, site_id)
|
|
);
|
|
|
|
-- Invoices (for monthly fees, services, work orders, etc.)
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.invoices (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
unit_id UUID NOT NULL,
|
|
invoice_number VARCHAR(50) NOT NULL,
|
|
invoice_type VARCHAR(50) NOT NULL, -- monthly_fee, service, work_order, other
|
|
description TEXT NOT NULL,
|
|
amount DECIMAL(15,2) NOT NULL,
|
|
due_date DATE NOT NULL,
|
|
issue_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, paid, overdue, cancelled
|
|
work_order_id UUID, -- Reference to work order if applicable
|
|
payment_date DATE,
|
|
payment_method VARCHAR(50),
|
|
payment_reference VARCHAR(100),
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL,
|
|
UNIQUE(site_id, invoice_number)
|
|
);
|
|
|
|
-- Unit Monthly Fee Settings
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.unit_monthly_fees (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
unit_id UUID NOT NULL,
|
|
site_id UUID NOT NULL,
|
|
monthly_fee_amount DECIMAL(15,2) NOT NULL,
|
|
effective_from DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
effective_to DATE, -- NULL means currently active
|
|
fee_description TEXT DEFAULT 'Monthly maintenance fee',
|
|
auto_generate_invoice BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL,
|
|
UNIQUE(unit_id, effective_from)
|
|
);
|
|
|
|
-- Payments (tracks all payments made against invoices)
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.payments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
unit_id UUID NOT NULL,
|
|
invoice_id UUID REFERENCES plugin_financials.invoices(id),
|
|
payment_amount DECIMAL(15,2) NOT NULL,
|
|
payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
payment_method VARCHAR(50) NOT NULL, -- cash, check, bank_transfer, credit_card, etc.
|
|
payment_reference VARCHAR(100), -- check number, transaction ID, etc.
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Balance History (audit trail for balance changes)
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.balance_history (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
unit_id UUID NOT NULL,
|
|
site_id UUID NOT NULL,
|
|
change_type VARCHAR(50) NOT NULL, -- invoice_added, payment_made, adjustment, starting_balance
|
|
change_amount DECIMAL(15,2) NOT NULL, -- positive for charges, negative for payments
|
|
balance_before DECIMAL(15,2) NOT NULL,
|
|
balance_after DECIMAL(15,2) NOT NULL,
|
|
reference_id UUID, -- invoice_id, payment_id, etc.
|
|
reference_type VARCHAR(50), -- invoice, payment, adjustment
|
|
description TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_unit_balances_unit_id ON plugin_financials.unit_balances(unit_id);
|
|
CREATE INDEX IF NOT EXISTS idx_invoices_unit_id ON plugin_financials.invoices(unit_id);
|
|
CREATE INDEX IF NOT EXISTS idx_invoices_status ON plugin_financials.invoices(status);
|
|
CREATE INDEX IF NOT EXISTS idx_invoices_due_date ON plugin_financials.invoices(due_date);
|
|
CREATE INDEX IF NOT EXISTS idx_payments_unit_id ON plugin_financials.payments(unit_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payments_invoice_id ON plugin_financials.payments(invoice_id);
|
|
CREATE INDEX IF NOT EXISTS idx_balance_history_unit_id ON plugin_financials.balance_history(unit_id);
|
|
|
|
-- Financial Reports
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.reports (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
report_type VARCHAR(50) NOT NULL, -- income_statement, balance_sheet, cash_flow, budget_variance
|
|
report_name VARCHAR(255) NOT NULL,
|
|
report_date DATE NOT NULL,
|
|
parameters JSONB,
|
|
generated_by UUID NOT NULL,
|
|
generated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
file_url VARCHAR(500),
|
|
status VARCHAR(20) DEFAULT 'generated' -- generated, sent, archived
|
|
);
|
|
|
|
-- Tax Settings
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.tax_settings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
tax_name VARCHAR(100) NOT NULL,
|
|
tax_rate DECIMAL(5,4) NOT NULL,
|
|
tax_type VARCHAR(20) NOT NULL, -- percentage, fixed
|
|
is_active BOOLEAN DEFAULT true,
|
|
applies_to VARCHAR(100), -- all, expenses, specific_categories
|
|
effective_date DATE NOT NULL,
|
|
end_date DATE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Financial Analytics
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.analytics (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
metric_name VARCHAR(100) NOT NULL,
|
|
metric_value DECIMAL(15,2) NOT NULL,
|
|
metric_date DATE NOT NULL,
|
|
category VARCHAR(100),
|
|
metadata JSONB,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Special Assessments
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.special_assessments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
assessment_name VARCHAR(255) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
total_amount DECIMAL(15,2) NOT NULL,
|
|
amount_per_unit DECIMAL(15,2) NOT NULL,
|
|
due_date DATE NOT NULL,
|
|
unit_count INTEGER NOT NULL,
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, invoiced, collected, cancelled
|
|
project_type VARCHAR(100), -- renovation, repair, upgrade, emergency
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Bank Accounts
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.bank_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
account_name VARCHAR(255) NOT NULL,
|
|
bank_name VARCHAR(255) NOT NULL,
|
|
account_number VARCHAR(100),
|
|
routing_number VARCHAR(50),
|
|
account_type VARCHAR(50) DEFAULT 'checking', -- checking, savings, reserve
|
|
ledger_account_id UUID REFERENCES plugin_financials.chart_of_accounts(id),
|
|
current_balance DECIMAL(15,2) DEFAULT 0,
|
|
currency VARCHAR(3) DEFAULT 'USD',
|
|
is_active BOOLEAN DEFAULT true,
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Bank Statements
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.bank_statements (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
bank_account_id UUID NOT NULL REFERENCES plugin_financials.bank_accounts(id) ON DELETE CASCADE,
|
|
site_id UUID NOT NULL,
|
|
statement_date DATE NOT NULL,
|
|
opening_balance DECIMAL(15,2) NOT NULL,
|
|
closing_balance DECIMAL(15,2) NOT NULL,
|
|
total_deposits DECIMAL(15,2) DEFAULT 0,
|
|
total_withdrawals DECIMAL(15,2) DEFAULT 0,
|
|
statement_file_url VARCHAR(500),
|
|
is_reconciled BOOLEAN DEFAULT false,
|
|
reconciled_date DATE,
|
|
reconciled_by UUID,
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL
|
|
);
|
|
|
|
-- Bank Statement Transactions
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.bank_statement_transactions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
bank_statement_id UUID NOT NULL REFERENCES plugin_financials.bank_statements(id) ON DELETE CASCADE,
|
|
transaction_date DATE NOT NULL,
|
|
description TEXT NOT NULL,
|
|
amount DECIMAL(15,2) NOT NULL,
|
|
transaction_type VARCHAR(20) NOT NULL, -- deposit, withdrawal, fee, interest
|
|
reference_number VARCHAR(100),
|
|
is_matched BOOLEAN DEFAULT false,
|
|
matched_transaction_id UUID, -- Links to plugin_financials.transactions if matched
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Bank Reconciliation Records
|
|
CREATE TABLE IF NOT EXISTS plugin_financials.bank_reconciliation_records (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
site_id UUID NOT NULL,
|
|
bank_account_id UUID NOT NULL REFERENCES plugin_financials.bank_accounts(id) ON DELETE CASCADE,
|
|
statement_id UUID NOT NULL REFERENCES plugin_financials.bank_statements(id),
|
|
reconciliation_date DATE NOT NULL,
|
|
ledger_balance DECIMAL(15,2) NOT NULL,
|
|
bank_balance DECIMAL(15,2) NOT NULL,
|
|
outstanding_deposits DECIMAL(15,2) DEFAULT 0,
|
|
outstanding_withdrawals DECIMAL(15,2) DEFAULT 0,
|
|
adjustments DECIMAL(15,2) DEFAULT 0,
|
|
reconciled_balance DECIMAL(15,2) NOT NULL,
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by UUID NOT NULL,
|
|
approved_by UUID,
|
|
approved_at TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_chart_of_accounts_site_id ON plugin_financials.chart_of_accounts(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_chart_of_accounts_account_type ON plugin_financials.chart_of_accounts(account_type);
|
|
CREATE INDEX IF NOT EXISTS idx_budgets_site_id ON plugin_financials.budgets(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_budgets_fiscal_year ON plugin_financials.budgets(fiscal_year);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_site_id ON plugin_financials.transactions(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_date ON plugin_financials.transactions(transaction_date);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_type ON plugin_financials.transactions(transaction_type);
|
|
CREATE INDEX IF NOT EXISTS idx_expenses_site_id ON plugin_financials.expenses(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_expenses_date ON plugin_financials.expenses(expense_date);
|
|
CREATE INDEX IF NOT EXISTS idx_revenue_site_id ON plugin_financials.revenue(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_revenue_date ON plugin_financials.revenue(revenue_date);
|
|
CREATE INDEX IF NOT EXISTS idx_reports_site_id ON plugin_financials.reports(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_analytics_site_id ON plugin_financials.analytics(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_analytics_date ON plugin_financials.analytics(metric_date);
|
|
|
|
-- Bank Reconciliation Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_bank_accounts_site_id ON plugin_financials.bank_accounts(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bank_accounts_ledger_account ON plugin_financials.bank_accounts(ledger_account_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bank_statements_account ON plugin_financials.bank_statements(bank_account_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bank_statements_site_id ON plugin_financials.bank_statements(site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bank_statement_transactions_statement ON plugin_financials.bank_statement_transactions(bank_statement_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bank_reconciliation_account ON plugin_financials.bank_reconciliation_records(bank_account_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bank_reconciliation_site_id ON plugin_financials.bank_reconciliation_records(site_id); |