Finance & Accounting Database Tables

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

​

Overview

​

Table Categories

​

1. Chart of Accounts (3 tables)

• asset, liability, equity, revenue, expense

• Assets: current_asset, fixed_asset, other_asset
• Liabilities: current_liability, long_term_liability
• Equity: net_assets, retained_earnings
• Revenue: program_revenue, contribution, grant, other_revenue
• Expense: program_expense, admin_expense, fundraising_expense

fa_accounts
├── parent_account_id (self-reference)
├── level (1-5)
├── is_control_account
└── allows_posting

​

2. General Ledger (3 tables)

• draft → pending_approval → posted
• reversed (after reversal)

type EntrySourceType = 
  | 'manual'      // Manual journal entry
  | 'invoice'     // AR invoice posting
  | 'payment'     // AP payment posting
  | 'receipt'     // AR receipt posting
  | 'adjustment'  // Period adjustment
  | 'closing'     // Period close entry
  | 'recurring'   // Recurring entry
  | 'import';     // Imported entry

​

3. Fiscal Management (2 tables)

• future → open → closed → locked

• Only one is_current fiscal year per org
• Periods must be sequential with no gaps
• Closed periods cannot accept new postings

​

4. Fund Accounting (2 tables)

• unrestricted - General operating
• temporarily_restricted - Time/purpose restrictions
• permanently_restricted - Endowment funds
• board_designated - Board-restricted funds

interface FundRestrictions {
  donor_restriction?: string;
  purpose_restriction?: string;
  time_restriction?: string;  // Date when restriction releases
}

​

5. Budgeting (4 tables)

• draft → submitted → approved → active
• rejected (back to draft)

• original - Initial approved budget
• revised - Mid-year revision
• forecast - Rolling forecast
• scenario - What-if analysis

​

6. Accounts Receivable (8 tables)

• draft → posted → partially_paid → paid
• void (cancelled)
• written_off (bad debt)

fa_invoices (posted)
    ↓
fa_customer_payments
    ↓
fa_ar_payment_applications (links payment → invoice)
    ↓
fa_invoices.balance_due updated

​

7. Accounts Payable (6 tables)

• check, ach, wire, credit_card, cash

fa_purchase_orders → fa_po_receipts → fa_vendor_bills
        ↓                    ↓              ↓
    (ordered)           (received)      (invoiced)

​

8. Purchasing (6 tables)

draft → pending_approval → approved → partially_received → received → closed
                ↓
            rejected

​

9. Banking (6 tables)

// Reconciliation summary
interface ReconciliationSummary {
  bank_ending_balance: number;
  gl_ending_balance: number;
  outstanding_checks_total: number;
  deposits_in_transit_total: number;
  adjustments_total: number;
  difference: number;  // Should be 0 when balanced
  is_balanced: boolean;
}

​

10. Reporting (4 tables)

• Statement of Financial Position (Balance Sheet)
• Statement of Activities (Income Statement)
• Statement of Functional Expenses
• Statement of Cash Flows
• Budget vs Actual
• Trial Balance
• General Ledger Detail

​

11. Module Settings (1 table)

​

Common Query Patterns

​

Get Account Balance

const { data } = await supabase
  .from('fa_account_balances')
  .select(`
    *,
    account:fa_accounts(account_number, name)
  `)
  .eq('period_id', periodId)
  .eq('account_id', accountId)
  .single();

​

Get Trial Balance

const { data } = await supabase
  .from('fa_account_balances')
  .select(`
    account:fa_accounts(
      account_number,
      name,
      account_type,
      normal_balance
    ),
    ending_balance
  `)
  .eq('period_id', periodId)
  .eq('organization_id', orgId);

​

Get AR Aging

const { data } = await supabase
  .from('fa_invoices')
  .select(`
    *,
    customer:fa_customers(customer_name)
  `)
  .eq('organization_id', orgId)
  .gt('balance_due', 0)
  .in('status', ['posted', 'partially_paid']);

​

Get Budget Variance

const { data } = await supabase
  .from('fa_budget_alerts')
  .select(`
    *,
    account:fa_accounts(account_number, name),
    budget:fa_budgets(version_name)
  `)
  .eq('organization_id', orgId)
  .eq('alert_status', 'active')
  .order('variance_percent', { ascending: false });

​

RLS Policies

• All tables filtered by organization_id
• Posted entries cannot be modified (application-level)
• Sensitive data (vendor banking) restricted to FA admins

• fa_user_has_access() - General access check
• fa_can_post_to_period() - Period status check
• fa_validate_journal_entry() - Balance validation

​

Triggers & Functions

​

Auto-Calculations

• fa_update_account_balance() - Updates balances on posting
• fa_update_invoice_balance() - Updates AR balance on payment
• fa_update_bill_balance() - Updates AP balance on payment

​

Validations

• fa_validate_balanced_entry() - Ensures debits = credits
• fa_validate_period_open() - Checks period status
• fa_validate_fund_restriction() - Fund usage rules

​

See Also

• FA Module Specs
• FA Module Specs - See fund accounting specifications
• Database Schema Overview