f8cd37ac8c
Includes: API (Express/TypeScript), Astro site, Python workers, document generators, FCC compliance tools, Canada CRTC formation, Ansible infrastructure, and deployment scripts. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
93 lines
4.3 KiB
PL/PgSQL
93 lines
4.3 KiB
PL/PgSQL
-- 012_accounting_support.sql
|
|
-- Accounting support system: freelance accountants, per-client time tracking,
|
|
-- first 3 hours free, $75/hr after, conversation monitoring for bypass attempts.
|
|
|
|
BEGIN;
|
|
|
|
-- Freelance accounting advisors
|
|
CREATE TABLE IF NOT EXISTS accounting_advisors (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
email TEXT NOT NULL UNIQUE,
|
|
phone TEXT,
|
|
specialties JSONB DEFAULT '[]', -- ["tax", "bookkeeping", "GST/HST", "payroll", "T2"]
|
|
erpnext_user TEXT, -- ERPNext username for portal login
|
|
hourly_rate_cents INTEGER DEFAULT 7500, -- what we charge clients ($75/hr)
|
|
our_cost_cents INTEGER DEFAULT 0, -- what we pay the freelancer (admin only)
|
|
active BOOLEAN DEFAULT TRUE,
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
-- Per-client accounting support entitlement
|
|
CREATE TABLE IF NOT EXISTS accounting_support_accounts (
|
|
id SERIAL PRIMARY KEY,
|
|
client_email TEXT NOT NULL,
|
|
client_name TEXT,
|
|
order_number TEXT NOT NULL, -- linked Canada CRTC order
|
|
order_type TEXT DEFAULT 'canada_crtc',
|
|
-- Hours tracking
|
|
free_hours_total NUMERIC(5,2) DEFAULT 3.00, -- 3 hours free
|
|
free_hours_used NUMERIC(5,2) DEFAULT 0.00,
|
|
billable_hours_used NUMERIC(5,2) DEFAULT 0.00,
|
|
total_billed_cents INTEGER DEFAULT 0,
|
|
-- Assigned advisor
|
|
assigned_advisor_id INTEGER REFERENCES accounting_advisors(id),
|
|
-- Access control (client grants/revokes)
|
|
access_granted BOOLEAN DEFAULT FALSE,
|
|
access_granted_at TIMESTAMPTZ,
|
|
access_revoked_at TIMESTAMPTZ,
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE(client_email, order_number)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_acct_support_client ON accounting_support_accounts(client_email);
|
|
CREATE INDEX IF NOT EXISTS idx_acct_support_order ON accounting_support_accounts(order_number);
|
|
|
|
-- Time entries per issue
|
|
CREATE TABLE IF NOT EXISTS accounting_time_entries (
|
|
id SERIAL PRIMARY KEY,
|
|
support_account_id INTEGER NOT NULL REFERENCES accounting_support_accounts(id),
|
|
advisor_id INTEGER NOT NULL REFERENCES accounting_advisors(id),
|
|
issue_reference TEXT NOT NULL, -- ERPNext Issue name
|
|
-- Time
|
|
hours NUMERIC(5,2) NOT NULL,
|
|
description TEXT,
|
|
-- Billing split
|
|
complimentary_hours NUMERIC(5,2) DEFAULT 0, -- portion covered by free hours
|
|
billable_hours NUMERIC(5,2) DEFAULT 0, -- portion billed at $75/hr
|
|
billable_amount_cents INTEGER DEFAULT 0,
|
|
-- Invoice
|
|
invoiced BOOLEAN DEFAULT FALSE,
|
|
invoice_reference TEXT, -- ERPNext Sales Invoice name
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_time_entries_account ON accounting_time_entries(support_account_id);
|
|
CREATE INDEX IF NOT EXISTS idx_time_entries_advisor ON accounting_time_entries(advisor_id);
|
|
|
|
-- Conversation monitoring flags
|
|
CREATE TABLE IF NOT EXISTS conversation_flags (
|
|
id SERIAL PRIMARY KEY,
|
|
issue_reference TEXT NOT NULL,
|
|
flagged_user TEXT NOT NULL, -- email of who sent the flagged message
|
|
user_type TEXT NOT NULL CHECK (user_type IN ('client', 'advisor')),
|
|
client_email TEXT NOT NULL,
|
|
advisor_email TEXT NOT NULL,
|
|
flagged_pattern TEXT NOT NULL, -- which regex pattern matched
|
|
flagged_text TEXT NOT NULL, -- the flagged portion of the message (truncated)
|
|
flag_count_for_pair INTEGER DEFAULT 1, -- running count for this client-advisor pair
|
|
warning_sent BOOLEAN DEFAULT TRUE,
|
|
admin_alerted BOOLEAN DEFAULT TRUE,
|
|
admin_reviewed BOOLEAN DEFAULT FALSE,
|
|
admin_action TEXT CHECK (admin_action IN ('dismissed', 'warned', 'escalated', 'advisor_removed', NULL)),
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_flags_issue ON conversation_flags(issue_reference);
|
|
CREATE INDEX IF NOT EXISTS idx_flags_pair ON conversation_flags(client_email, advisor_email);
|
|
|
|
COMMIT;
|