mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
441 lines
17 KiB
SQL
441 lines
17 KiB
SQL
|
|
-- ═══════════════════════════════════════════════════════════
|
|
-- CORE OPERATIONS TABLES
|
|
-- ═══════════════════════════════════════════════════════════
|
|
|
|
-- Projects
|
|
CREATE TABLE public.projects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
priority TEXT DEFAULT 'medium',
|
|
start_date DATE,
|
|
due_date DATE,
|
|
budget NUMERIC(12,2),
|
|
assigned_to TEXT,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Tasks
|
|
CREATE TABLE public.tasks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
priority TEXT DEFAULT 'medium',
|
|
due_date DATE,
|
|
assigned_to UUID,
|
|
project_id UUID REFERENCES public.projects(id) ON DELETE SET NULL,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Documents
|
|
CREATE TABLE public.documents (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
category TEXT DEFAULT 'general',
|
|
file_url TEXT,
|
|
file_name TEXT,
|
|
file_size BIGINT,
|
|
uploaded_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Call Log
|
|
CREATE TABLE public.call_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
caller_name TEXT NOT NULL,
|
|
caller_phone TEXT,
|
|
call_type TEXT DEFAULT 'inbound',
|
|
subject TEXT,
|
|
notes TEXT,
|
|
follow_up_required BOOLEAN DEFAULT false,
|
|
follow_up_date DATE,
|
|
taken_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ═══════════════════════════════════════════════════════════
|
|
-- FINANCIAL TABLES
|
|
-- ═══════════════════════════════════════════════════════════
|
|
|
|
-- Invoices
|
|
CREATE TABLE public.invoices (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
vendor_name TEXT NOT NULL,
|
|
invoice_number TEXT,
|
|
amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
issue_date DATE DEFAULT CURRENT_DATE,
|
|
due_date DATE,
|
|
paid_date DATE,
|
|
description TEXT,
|
|
category TEXT,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Payments (admin-level tracking)
|
|
CREATE TABLE public.admin_payments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
owner_id UUID REFERENCES public.owners(id) ON DELETE SET NULL,
|
|
amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
payment_method TEXT DEFAULT 'check',
|
|
reference_number TEXT,
|
|
payment_date DATE DEFAULT CURRENT_DATE,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'completed',
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Collections
|
|
CREATE TABLE public.collections (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
owner_id UUID REFERENCES public.owners(id) ON DELETE SET NULL,
|
|
amount_owed NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
last_notice_date DATE,
|
|
notes TEXT,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Payment Plans
|
|
CREATE TABLE public.payment_plans (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
owner_id UUID REFERENCES public.owners(id) ON DELETE SET NULL,
|
|
total_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
monthly_payment NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
start_date DATE,
|
|
end_date DATE,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
notes TEXT,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Payables
|
|
CREATE TABLE public.payables (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
vendor_name TEXT NOT NULL,
|
|
amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
due_date DATE,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
description TEXT,
|
|
invoice_id UUID REFERENCES public.invoices(id) ON DELETE SET NULL,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ═══════════════════════════════════════════════════════════
|
|
-- ADMINISTRATIVE TABLES
|
|
-- ═══════════════════════════════════════════════════════════
|
|
|
|
-- Inspections
|
|
CREATE TABLE public.inspections (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
inspection_type TEXT DEFAULT 'general',
|
|
scheduled_date DATE,
|
|
completed_date DATE,
|
|
status TEXT NOT NULL DEFAULT 'scheduled',
|
|
inspector TEXT,
|
|
notes TEXT,
|
|
unit_id UUID REFERENCES public.units(id) ON DELETE SET NULL,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Blocked Dates
|
|
CREATE TABLE public.blocked_dates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
reason TEXT,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Checklists
|
|
CREATE TABLE public.checklists (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
items JSONB DEFAULT '[]'::jsonb,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ═══════════════════════════════════════════════════════════
|
|
-- ASSOCIATION TABLES
|
|
-- ═══════════════════════════════════════════════════════════
|
|
|
|
-- Bids & Quotes
|
|
CREATE TABLE public.bids_quotes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
vendor_name TEXT NOT NULL,
|
|
project_id UUID REFERENCES public.projects(id) ON DELETE SET NULL,
|
|
amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
received_date DATE DEFAULT CURRENT_DATE,
|
|
expiry_date DATE,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Bill Approvals
|
|
CREATE TABLE public.bill_approvals (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
invoice_id UUID REFERENCES public.invoices(id) ON DELETE SET NULL,
|
|
vendor_name TEXT NOT NULL,
|
|
amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
approved_by UUID,
|
|
approved_date DATE,
|
|
notes TEXT,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Client Requests
|
|
CREATE TABLE public.client_requests (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
priority TEXT DEFAULT 'medium',
|
|
requester_name TEXT,
|
|
requester_email TEXT,
|
|
assigned_to UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Homeowner Requests
|
|
CREATE TABLE public.homeowner_requests (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
owner_id UUID REFERENCES public.owners(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
category TEXT DEFAULT 'general',
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
priority TEXT DEFAULT 'medium',
|
|
assigned_to UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Estoppels
|
|
CREATE TABLE public.estoppels (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
unit_id UUID REFERENCES public.units(id) ON DELETE SET NULL,
|
|
address TEXT,
|
|
status TEXT NOT NULL DEFAULT 'requested',
|
|
requested_date DATE DEFAULT CURRENT_DATE,
|
|
completed_date DATE,
|
|
fee NUMERIC(12,2),
|
|
requestor_name TEXT,
|
|
requestor_email TEXT,
|
|
notes TEXT,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Legal Matters
|
|
CREATE TABLE public.legal_matters (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
case_number TEXT,
|
|
attorney TEXT,
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
category TEXT DEFAULT 'general',
|
|
description TEXT,
|
|
opened_date DATE DEFAULT CURRENT_DATE,
|
|
closed_date DATE,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Status Updates
|
|
CREATE TABLE public.status_updates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
content TEXT,
|
|
status TEXT NOT NULL DEFAULT 'published',
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Owner Updates
|
|
CREATE TABLE public.owner_updates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
content TEXT,
|
|
visibility TEXT DEFAULT 'all',
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ═══════════════════════════════════════════════════════════
|
|
-- ACCOUNTING TABLES
|
|
-- ═══════════════════════════════════════════════════════════
|
|
|
|
-- Bank Accounts
|
|
CREATE TABLE public.bank_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
account_name TEXT NOT NULL,
|
|
account_number TEXT,
|
|
routing_number TEXT,
|
|
bank_name TEXT,
|
|
account_type TEXT DEFAULT 'checking',
|
|
current_balance NUMERIC(14,2) DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Budgets
|
|
CREATE TABLE public.budgets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
fiscal_year INTEGER NOT NULL,
|
|
category TEXT NOT NULL,
|
|
budgeted_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
actual_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Checks
|
|
CREATE TABLE public.checks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
bank_account_id UUID REFERENCES public.bank_accounts(id) ON DELETE SET NULL,
|
|
check_number TEXT,
|
|
payee TEXT NOT NULL,
|
|
amount NUMERIC(12,2) NOT NULL DEFAULT 0,
|
|
memo TEXT,
|
|
check_date DATE DEFAULT CURRENT_DATE,
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
printed BOOLEAN DEFAULT false,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Calendar Events
|
|
CREATE TABLE public.calendar_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE CASCADE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
start_date TIMESTAMPTZ NOT NULL,
|
|
end_date TIMESTAMPTZ,
|
|
all_day BOOLEAN DEFAULT false,
|
|
event_type TEXT DEFAULT 'meeting',
|
|
location TEXT,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ═══════════════════════════════════════════════════════════
|
|
-- ENABLE RLS ON ALL TABLES
|
|
-- ═══════════════════════════════════════════════════════════
|
|
|
|
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.call_logs ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.admin_payments ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.collections ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.payment_plans ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.payables ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.inspections ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.blocked_dates ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.checklists ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.bids_quotes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.bill_approvals ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.client_requests ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.homeowner_requests ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.estoppels ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.legal_matters ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.status_updates ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.owner_updates ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.bank_accounts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.budgets ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.checks ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.calendar_events ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- ═══════════════════════════════════════════════════════════
|
|
-- RLS POLICIES - Admin/Manager full access for all tables
|
|
-- ═══════════════════════════════════════════════════════════
|
|
|
|
DO $$
|
|
DECLARE
|
|
tbl TEXT;
|
|
BEGIN
|
|
FOR tbl IN SELECT unnest(ARRAY[
|
|
'projects','tasks','documents','call_logs','invoices','admin_payments',
|
|
'collections','payment_plans','payables','inspections','blocked_dates',
|
|
'checklists','bids_quotes','bill_approvals','client_requests',
|
|
'homeowner_requests','estoppels','legal_matters','status_updates',
|
|
'owner_updates','bank_accounts','budgets','checks','calendar_events'
|
|
])
|
|
LOOP
|
|
EXECUTE format(
|
|
'CREATE POLICY "Staff full access on %1$s" ON public.%1$s FOR ALL TO authenticated USING (public.has_role(auth.uid(), ''admin'') OR public.has_role(auth.uid(), ''manager'')) WITH CHECK (public.has_role(auth.uid(), ''admin'') OR public.has_role(auth.uid(), ''manager''))',
|
|
tbl
|
|
);
|
|
END LOOP;
|
|
END;
|
|
$$;
|