FA Module Security Considerations

Version: 1.0.0
Last Updated: 2025-11-26
Constitution Reference: Section 4 (Security Rules), Section 7 (What AI Must Never Do)

1. Overview

The Finance & Accounting (FA) module handles highly sensitive financial data including bank account numbers, tax IDs, credit card details, and personally identifiable financial information (PII). This document outlines security requirements, encryption standards, and compliance considerations for all FA implementations.

2. Data Classification

2.1 Highly Sensitive Data (Encryption Required)

The following fields MUST be encrypted at rest using AES-256-GCM or equivalent: Vendor Information (FA-04):

fa_vendors.tax_id_encrypted - EIN/SSN
fa_vendors.bank_account_encrypted - Bank account numbers
fa_vendors.routing_number_encrypted - Bank routing numbers
fa_vendors.credit_card_encrypted - Corporate credit card numbers (if stored)

Customer Information (FA-06):

fa_customers.tax_id_encrypted - EIN/SSN
fa_customers.payment_method_encrypted - Stored payment methods (ACH, cards)

Employee Payroll (Future):

fa_payroll.ssn_encrypted - Social Security Numbers
fa_payroll.bank_account_encrypted - Direct deposit account numbers

Implementation:

-- Use PostgreSQL pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt before insert
INSERT INTO fa_vendors (
  tax_id_encrypted
) VALUES (
  pgp_sym_encrypt('12-3456789', current_setting('app.settings.encryption_key'))
);

-- Decrypt when authorized
SELECT 
  pgp_sym_decrypt(tax_id_encrypted::bytea, current_setting('app.settings.encryption_key'))::text
FROM fa_vendors
WHERE id = 'uuid';

Key Management:

Encryption keys MUST be stored in Supabase Vault (NOT in code or environment variables)
Keys MUST be rotated annually
Separate keys for production, staging, and development environments

2.2 Sensitive Data (Access Control Required)

The following data requires strict RLS but does NOT require encryption: Financial Balances:

fa_accounts.current_balance - Account balances
fa_journal_entries - All journal entry data
fa_vendor_bills.amount - Invoice amounts
fa_invoices.total_amount - Customer invoice amounts

Access Control:

Only users with org_admin or platform_admin roles
Finance staff explicitly granted access via pf_user_roles
RLS enforced via fa_is_finance_admin() function

2.3 Restricted Logging

NEVER LOG:

Full tax IDs (log last 4 digits only: ***-**-1234)
Bank account numbers (log last 4 digits only: *****6789)
Credit card numbers (log last 4 digits only: ****1234)
Encryption keys or decrypted sensitive data

Safe Logging:

// ❌ WRONG
console.log('Processing vendor:', vendor.tax_id);

// ✅ CORRECT
console.log('Processing vendor:', vendor.id, 'Tax ID:', maskTaxId(vendor.tax_id));

function maskTaxId(taxId: string): string {
  if (!taxId || taxId.length < 4) return '***';
  return `***-**-${taxId.slice(-4)}`;
}

3. Row-Level Security (RLS) Patterns

3.1 Standard Finance Admin Access

Pattern: Only finance admins (org_admin, platform_admin, or explicitly granted finance staff) can view/modify financial data.

-- View Policy (FA tables)
CREATE POLICY "fa_table_view"
  ON fa_table FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM pf_user_roles
      WHERE user_id = auth.uid()
        AND organization_id = fa_table.organization_id
        AND role IN ('org_admin', 'platform_admin')
    )
  );

-- Modify Policy (FA tables)
CREATE POLICY "fa_table_modify"
  ON fa_table FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM pf_user_roles
      WHERE user_id = auth.uid()
        AND organization_id = fa_table.organization_id
        AND role IN ('org_admin', 'platform_admin')
    )
  );

Helper Function:

CREATE FUNCTION fa_is_finance_admin(_user_id UUID, _org_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pf_user_roles
    WHERE user_id = _user_id
      AND organization_id = _org_id
      AND role IN ('org_admin', 'platform_admin')
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

3.2 Department Manager Limited Access

Use Case: Department managers can view budget data for their department only.

CREATE POLICY "fa_budget_department_manager_view"
  ON fa_budget_lines FOR SELECT
  USING (
    department_id IN (
      SELECT department_id FROM hr_employees
      WHERE profile_id = auth.uid() AND is_manager = true
    )
    OR fa_is_finance_admin(auth.uid(), organization_id)
  );

3.3 Recursive RLS Anti-Pattern (AVOID)

Problem: RLS policies that query tables with their own RLS create infinite recursion.

-- ❌ WRONG: Causes infinite recursion
CREATE POLICY "fa_document_org_access" ON fa_documents
FOR SELECT USING (
  organization_id IN (
    SELECT organization_id FROM pf_user_roles  -- pf_user_roles has RLS!
    WHERE user_id = auth.uid()
  )
);

Solution: Use SECURITY DEFINER functions to bypass RLS in helper queries.

-- ✅ CORRECT: Helper function bypasses RLS
CREATE FUNCTION has_fa_access(org_id UUID, user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pf_user_roles
    WHERE pf_user_roles.organization_id = org_id
      AND pf_user_roles.user_id = user_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Policy uses helper function
CREATE POLICY "fa_document_org_access" ON fa_documents
FOR SELECT USING (has_fa_access(organization_id, auth.uid()));

4. Audit Logging

4.1 Automatic Audit Triggers

All FA tables MUST have audit logging via pf_audit_logs:

CREATE TRIGGER log_fa_journal_entries_audit
  AFTER INSERT OR UPDATE OR DELETE ON fa_journal_entries
  FOR EACH ROW
  EXECUTE FUNCTION log_audit_event();

Audit Log Contents:

user_id - Who made the change
organization_id - Tenant context
module - ‘fa’
action - ‘create’, ‘update’, ‘delete’
table_name - ‘fa_journal_entries’
record_id - Primary key of changed record
old_values - Full record before change (JSON)
new_values - Full record after change (JSON)
timestamp - When change occurred

Retention:

Audit logs MUST be retained for 7 years (financial compliance)
Audit logs MUST be immutable (no UPDATE/DELETE policies)

4.2 High-Risk Actions (Additional Logging)

Certain actions require ADDITIONAL logging beyond standard audit: Period Close/Reopen:

// Log period close to separate critical events table
await supabase.from('fa_critical_events').insert({
  organization_id: orgId,
  event_type: 'period_close',
  fiscal_period_id: periodId,
  performed_by: userId,
  metadata: {
    period_name: period.period_name,
    entry_count: entryCount,
    total_debits: totalDebits,
    total_credits: totalCredits
  }
});

Journal Entry Voids:

// Require reason and dual approval
await supabase.from('fa_journal_entry_voids').insert({
  entry_id: entryId,
  voided_by: userId,
  approved_by: approverId,
  reason: voidReason,
  original_amount: originalAmount
});

5. Input Validation & SQL Injection Prevention

5.1 Parameterized Queries

ALWAYS use Supabase client methods (NOT raw SQL):

// ✅ CORRECT: Parameterized via Supabase client
const { data, error } = await supabase
  .from('fa_accounts')
  .select('*')
  .eq('account_number', accountNumber);

// ❌ WRONG: Raw SQL vulnerable to injection
const { data } = await supabase.rpc('execute_sql', {
  query: `SELECT * FROM fa_accounts WHERE account_number = '${accountNumber}'`
});

5.2 Amount Validation

Prevent numeric overflow and precision errors:

// Validate amounts before database insert
function validateAmount(amount: number): void {
  if (!Number.isFinite(amount)) {
    throw new Error('Amount must be a finite number');
  }
  
  if (Math.abs(amount) > 9999999999.99) {
    throw new Error('Amount exceeds maximum allowed value');
  }
  
  // Check decimal precision (2 places max)
  const decimalPlaces = (amount.toString().split('.')[1] || '').length;
  if (decimalPlaces > 2) {
    throw new Error('Amount cannot have more than 2 decimal places');
  }
}

5.3 Date Validation

Prevent posting to invalid periods:

// Validate posting date against fiscal periods
async function validatePostingDate(
  entryDate: Date, 
  orgId: string,
  moduleSettings: FAModuleSettings
): Promise<void> {
  // Check if date is in an open period
  const { data: period } = await supabase
    .from('fa_fiscal_periods')
    .select('*')
    .eq('organization_id', orgId)
    .lte('start_date', entryDate.toISOString())
    .gte('end_date', entryDate.toISOString())
    .eq('status', 'open')
    .single();
  
  if (!period) {
    throw new Error('No open fiscal period found for this date');
  }
  
  // Check if posting to prior periods is restricted
  if (moduleSettings.restrict_prior_period_posting) {
    const daysBack = differenceInDays(new Date(), entryDate);
    if (daysBack > (moduleSettings.days_back_posting_allowed || 0)) {
      throw new Error(`Cannot post entries more than ${moduleSettings.days_back_posting_allowed} days in the past`);
    }
  }
}

6. Multi-Tenancy Security

6.1 Organization Isolation

CRITICAL: All FA queries MUST filter by organization_id to prevent cross-tenant data leaks.

// ✅ CORRECT: Organization filter enforced
const { data } = await supabase
  .from('fa_accounts')
  .select('*')
  .eq('organization_id', userOrgId);

// ❌ WRONG: No organization filter (potential data leak)
const { data } = await supabase
  .from('fa_accounts')
  .select('*');

RLS Enforcement:

EVERY FA table has organization_id column
EVERY RLS policy checks has_org_access(auth.uid(), organization_id)
NEVER disable RLS on FA tables (even temporarily)

6.2 Site-Level Security

Some organizations segment data by site. Respect site_id filtering:

-- Site-specific access
CREATE POLICY "fa_site_access"
  ON fa_table FOR SELECT
  USING (
    has_org_access(auth.uid(), organization_id)
    AND (
      site_id IS NULL  -- Org-wide records
      OR site_id IN (
        SELECT site_id FROM pf_user_roles
        WHERE user_id = auth.uid()
          AND organization_id = fa_table.organization_id
      )
    )
  );

7. API Security

7.1 Edge Function Authentication

All FA edge functions MUST verify JWT tokens:

// supabase/functions/execute-report/index.ts
import { createClient } from '@supabase/supabase-js';

Deno.serve(async (req) => {
  // CRITICAL: Verify JWT token
  const authHeader = req.headers.get('Authorization');
  if (!authHeader) {
    return new Response('Missing authorization', { status: 401 });
  }
  
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_ANON_KEY')!,
    { global: { headers: { Authorization: authHeader } } }
  );
  
  // Get authenticated user
  const { data: { user }, error } = await supabase.auth.getUser();
  if (error || !user) {
    return new Response('Unauthorized', { status: 401 });
  }
  
  // Verify user has finance admin access
  const { data: roles } = await supabase
    .from('pf_user_roles')
    .select('role, organization_id')
    .eq('user_id', user.id)
    .in('role', ['org_admin', 'platform_admin']);
  
  if (!roles || roles.length === 0) {
    return new Response('Forbidden - Finance admin access required', { status: 403 });
  }
  
  // Process request...
});

7.2 Rate Limiting

Critical endpoints require rate limiting:

// Edge function: check-budget-alerts
// Rate limit: 1 call per minute per organization
const rateLimitKey = `budget_alerts:${orgId}:${Date.now() / 60000 | 0}`;
const { count } = await supabase
  .from('fa_rate_limits')
  .select('count')
  .eq('key', rateLimitKey)
  .single();

if (count && count >= 1) {
  return new Response('Rate limit exceeded', { status: 429 });
}

8. Secrets Management

8.1 Encryption Keys

NEVER store encryption keys in:

Environment variables (.env files)
Source code
Database tables
Edge function code

CORRECT Storage:

-- Store in Supabase Vault
SELECT vault.create_secret('fa_encryption_key', 'your-256-bit-key-here');

-- Retrieve in SECURITY DEFINER functions
SELECT decrypted_secret FROM vault.decrypted_secrets 
WHERE name = 'fa_encryption_key';

8.2 Third-Party API Keys

Payment processors, bank integrations, tax services:

// ✅ CORRECT: Store in Supabase secrets
// Add via Lovable UI: Settings → Cloud → Secrets
PLAID_CLIENT_ID
PLAID_SECRET
STRIPE_SECRET_KEY
AVALARA_API_KEY

// Access in edge functions
const plaidSecret = Deno.env.get('PLAID_SECRET');

9. Compliance Requirements

9.1 SOC 2 Type II Considerations

Access Control:

Segregation of duties (creator ≠ approver)
Dual approval for high-value transactions (configurable threshold)
Immutable audit logs with 7-year retention

Data Protection:

Encryption at rest (AES-256) for PII/PHI
Encryption in transit (TLS 1.3)
Regular key rotation (annually)

Monitoring:

Failed login attempts logged
Unusual transaction patterns flagged
Period close/reopen events alerted

9.2 HIPAA Considerations (If Applicable)

BAA Required: If FA module processes Protected Health Information (PHI):

Supabase must have signed Business Associate Agreement (BAA)
All PHI fields must be encrypted at rest
Access logs must be retained for 6 years
Breach notification procedures must be documented

PHI in FA:

Patient billing records in fa_customers (if customers = patients)
Grant compliance reports mentioning patient counts (de-identified only)

9.3 PCI DSS (If Storing Cards)

DO NOT STORE:

Full credit card numbers (use tokenization via Stripe/Authorize.Net)
CVV/CVC codes (NEVER store these)

IF STORING CARD DATA:

Requires PCI DSS Level 1 compliance
Quarterly vulnerability scans
Annual penetration testing
Recommend: Use Stripe Payment Intents instead

10. Security Checklist

Pre-Implementation Checklist

Post-Implementation Checklist

11. Incident Response

Security Breach Procedure

1. Immediate Actions (0-1 hour):

Disable affected user accounts
Revoke leaked API keys/tokens
Enable additional logging/monitoring
Notify security team and CFO

2. Investigation (1-24 hours):

Review audit logs for unauthorized access
Identify scope of data exposure
Determine attack vector and timeline
Preserve evidence for forensics

3. Remediation (24-72 hours):

Patch vulnerability
Rotate all encryption keys
Reset affected user passwords
Deploy security updates

4. Notification (72 hours):

Notify affected customers (if PII exposed)
File breach reports (if legally required)
Update security documentation
Conduct post-mortem review

12. Contact & Resources

Security Team:

Email: security@encoreos.io
Slack: #security-incidents
On-Call: PagerDuty rotation

Documentation:

Compliance:

SOC 2 Report: Available on request
HIPAA BAA: Contact legal@encoreos.io
PCI DSS: Not certified (use tokenization)

Last Security Audit: Pending
Next Audit Due: Before production release
Approved By: [CFO/CTO Name] - [Date]

Overview

Clinical

Practice Management

Recovery Housing

Finance & Revenue

Workforce & HR

Community Engagement

Facilities & Inventory

Forms & Workflow

Governance & Compliance

IT Service Management

Leadership

Platform

Documentation Index

​1. Overview

​2. Data Classification

​2.1 Highly Sensitive Data (Encryption Required)

​2.2 Sensitive Data (Access Control Required)

​2.3 Restricted Logging

​3. Row-Level Security (RLS) Patterns

​3.1 Standard Finance Admin Access

​3.2 Department Manager Limited Access

​3.3 Recursive RLS Anti-Pattern (AVOID)

​4. Audit Logging

​4.1 Automatic Audit Triggers

​4.2 High-Risk Actions (Additional Logging)

​5. Input Validation & SQL Injection Prevention

​5.1 Parameterized Queries

​5.2 Amount Validation

​5.3 Date Validation

​6. Multi-Tenancy Security

​6.1 Organization Isolation

​6.2 Site-Level Security

​7. API Security

​7.1 Edge Function Authentication

​7.2 Rate Limiting

​8. Secrets Management

​8.1 Encryption Keys

​8.2 Third-Party API Keys

​9. Compliance Requirements

​9.1 SOC 2 Type II Considerations

​9.2 HIPAA Considerations (If Applicable)

​9.3 PCI DSS (If Storing Cards)

​10. Security Checklist

​Pre-Implementation Checklist

​Post-Implementation Checklist

​11. Incident Response

​Security Breach Procedure

​12. Contact & Resources