> ## 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.

# Finance & Accounting Database Tables

> The FA module provides nonprofit fund accounting capabilities including chart of accounts, general ledger, fund/program tracking, budgeting, accounts receivabl…

**Module:** Finance & Accounting\
**Prefix:** `fa_`\
**Table Count:** 45\
**Last Updated:** 2026-01-10

***

## Overview

The FA module provides nonprofit fund accounting capabilities including chart of accounts, general ledger, fund/program tracking, budgeting, accounts receivable, accounts payable, purchasing, and banking/reconciliation.

***

## Table Categories

### 1. Chart of Accounts (3 tables)

| Table                 | Columns | Description             |
| --------------------- | ------- | ----------------------- |
| `fa_accounts`         | 28      | GL account definitions  |
| `fa_account_types`    | 12      | Account type catalog    |
| `fa_account_balances` | 10      | Period account balances |

**Account Types (Enum):**

* `asset`, `liability`, `equity`, `revenue`, `expense`

**Account Subtypes (Enum):**

* 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`

**Hierarchical Structure:**

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

### 2. General Ledger (3 tables)

| Table                    | Columns | Description               |
| ------------------------ | ------- | ------------------------- |
| `fa_journal_entries`     | 24      | Journal entry headers     |
| `fa_journal_entry_lines` | 12      | Debit/credit lines        |
| `fa_recurring_entries`   | 18      | Recurring entry templates |

**Journal Entry Status:**

* `draft` → `pending_approval` → `posted`
* `reversed` (after reversal)

**Source Types (Enum):**

```typescript theme={null}
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)

| Table               | Columns | Description               |
| ------------------- | ------- | ------------------------- |
| `fa_fiscal_years`   | 12      | Fiscal year definitions   |
| `fa_fiscal_periods` | 14      | Monthly/quarterly periods |

**Period Status (Enum):**

* `future` → `open` → `closed` → `locked`

**Key Constraints:**

* 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)

| Table         | Columns | Description         |
| ------------- | ------- | ------------------- |
| `fa_funds`    | 18      | Fund definitions    |
| `fa_programs` | 14      | Program definitions |

**Fund Types (Enum):**

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

**Restriction Tracking:**

```typescript theme={null}
interface FundRestrictions {
  donor_restriction?: string;
  purpose_restriction?: string;
  time_restriction?: string;  // Date when restriction releases
}
```

### 5. Budgeting (4 tables)

| Table                 | Columns | Description       |
| --------------------- | ------- | ----------------- |
| `fa_budgets`          | 22      | Budget versions   |
| `fa_budget_lines`     | 14      | Budget line items |
| `fa_budget_alerts`    | 18      | Variance alerts   |
| `fa_budget_approvals` | 12      | Approval workflow |

**Budget Status (Enum):**

* `draft` → `submitted` → `approved` → `active`
* `rejected` (back to draft)

**Budget Version Types:**

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

### 6. Accounts Receivable (8 tables)

| Table                        | Columns | Description              |
| ---------------------------- | ------- | ------------------------ |
| `fa_customers`               | 26      | Customer master records  |
| `fa_invoices`                | 24      | AR invoices              |
| `fa_invoice_lines`           | 14      | Invoice line items       |
| `fa_customer_payments`       | 22      | Payment receipts         |
| `fa_ar_payment_applications` | 10      | Payment-to-invoice links |
| `fa_credit_memos`            | 24      | Credit memo headers      |
| `fa_credit_memo_lines`       | 14      | Credit memo lines        |
| `fa_credit_applications`     | 10      | Credit-to-invoice links  |

**Invoice Status (Enum):**

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

**AR Workflow:**

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

### 7. Accounts Payable (6 tables)

| Table                     | Columns | Description           |
| ------------------------- | ------- | --------------------- |
| `fa_vendors`              | 28      | Vendor master records |
| `fa_vendor_bills`         | 26      | AP bills/invoices     |
| `fa_vendor_bill_lines`    | 16      | Bill line items       |
| `fa_payments`             | 24      | Payment records       |
| `fa_payment_applications` | 10      | Payment-to-bill links |
| `fa_payment_batches`      | 16      | Batch payment runs    |

**Payment Methods:**

* `check`, `ach`, `wire`, `credit_card`, `cash`

**3-Way Match (if enabled):**

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

### 8. Purchasing (6 tables)

| Table                     | Columns | Description        |
| ------------------------- | ------- | ------------------ |
| `fa_purchase_orders`      | 28      | PO headers         |
| `fa_purchase_order_lines` | 16      | PO line items      |
| `fa_po_amendments`        | 14      | PO change orders   |
| `fa_po_receipts`          | 16      | Receipt headers    |
| `fa_po_receipt_lines`     | 12      | Receipt line items |
| `fa_approval_thresholds`  | 14      | Approval rules     |

**PO Status Flow:**

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

### 9. Banking (6 tables)

| Table                           | Columns | Description             |
| ------------------------------- | ------- | ----------------------- |
| `fa_bank_accounts`              | 18      | Bank account setup      |
| `fa_bank_statements`            | 18      | Imported statements     |
| `fa_bank_statement_lines`       | 16      | Statement transactions  |
| `fa_bank_reconciliations`       | 26      | Reconciliation records  |
| `fa_reconciliation_matches`     | 12      | Statement-to-GL matches |
| `fa_reconciliation_adjustments` | 14      | Reconciling items       |

**Reconciliation Workflow:**

```typescript theme={null}
// 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)

| Table                   | Columns | Description        |
| ----------------------- | ------- | ------------------ |
| `fa_report_definitions` | 20      | Report templates   |
| `fa_report_schedules`   | 14      | Scheduled runs     |
| `fa_report_runs`        | 14      | Execution history  |
| `fa_report_recipients`  | 8       | Distribution lists |

**Standard Reports:**

* 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)

| Table                | Columns | Description      |
| -------------------- | ------- | ---------------- |
| `fa_module_settings` | 29      | FA configuration |

**Key Settings:**

| Setting                                      | Type    | Default |
| -------------------------------------------- | ------- | ------- |
| `default_fiscal_year_start_month`            | integer | 1       |
| `require_po_approval`                        | boolean | true    |
| `po_approval_threshold_amount`               | decimal | 5000    |
| `enable_3_way_match`                         | boolean | false   |
| `auto_generate_invoice_numbers`              | boolean | true    |
| `invoice_number_prefix`                      | text    | 'INV-'  |
| `budget_variance_critical_threshold_percent` | decimal | 10      |

***

## Common Query Patterns

### Get Account Balance

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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

FA tables use strict RLS:

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

**Helper Functions:**

* `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](https://github.com/Encore-OS/encoreos/tree/development/specs/fa)
* [FA Module Specs](https://github.com/Encore-OS/encoreos/tree/development/specs/fa) - See fund accounting specifications
* [Database Schema Overview](/architecture/DATABASE_SCHEMA)