Encore OS — Documentation

Generated by npm run db:docs:generate — DO NOT EDIT BY HAND. Change the schema via a migration, then regenerate.

Core: fa_ · Tables: 129 · Source generated: 2026-06-10T02:55:32.169Z

Entity relationships

Tables

`fa_1099_forms`

FA-10: 1099 tax form records for vendor payments RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year_id`	uuid	no
`vendor_id`	uuid	yes
`vendor_name`	text	no
`vendor_tin_encrypted`	text	yes
`form_type`	text	no	’1099-NEC’::text
`box_amounts`	jsonb	no	’{}‘::jsonb
`total_amount`	numeric(15,2)	no	0
`status`	text	no	’draft’::text
`is_correction`	boolean	no	false
`corrects_form_id`	uuid	yes
`filed_at`	timestamp with time zone	yes
`filed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`efile_data`	jsonb	yes		FA-10 AC-2: IRS FIRE Payee “B” record payload for this form (TIN masked at rest; decrypted at transmission).

Foreign keys:

corrects_form_id → fa_1099_forms(id)
created_by → users(id)
filed_by → users(id)
organization_id → pf_organizations(id)
tax_year_id → fa_tax_years(id)
updated_by → users(id)

Policies:

fa_1099_forms_insert (INSERT)
fa_1099_forms_select (SELECT)
fa_1099_forms_update (UPDATE)

`fa_account_balances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`account_id`	uuid	no
`period_id`	uuid	no
`fund_id`	uuid	yes
`beginning_balance`	numeric(15,2)	no	0.00
`period_debits`	numeric(15,2)	no	0.00
`period_credits`	numeric(15,2)	no	0.00
`ending_balance`	numeric(15,2)	no	0.00
`updated_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	yes	now()	Timestamp when the record was created

Foreign keys:

account_id → fa_accounts(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
period_id → fa_fiscal_periods(id)

Policies:

fa_account_balances_delete (DELETE)
fa_account_balances_insert (INSERT)
fa_account_balances_select (SELECT)
fa_account_balances_update (UPDATE)

`fa_account_mappings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`source_system`	character varying(50)	no	’d365_bc’::character varying
`source_account_code`	character varying(50)	no
`fa_account_id`	uuid	no
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`fund_id`	uuid	yes		Fund association for GL migration mapping (D365 BC → Encore OS). Added by FA-02 Phase 2/3 expansion.

Foreign keys:

created_by → pf_profiles(id)
fa_account_id → fa_accounts(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_account_mappings_delete (DELETE)
fa_account_mappings_insert (INSERT)
fa_account_mappings_select (SELECT)
fa_account_mappings_update (UPDATE)

`fa_account_types`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`account_type`	account_type	no
`account_subtype`	account_subtype	no
`name`	text	no
`description`	text	yes
`normal_balance`	text	no
`is_system`	boolean	no	false
`display_order`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_account_types_delete (DELETE)
fa_account_types_insert (INSERT)
fa_account_types_org_read (SELECT)
fa_account_types_update (UPDATE)

`fa_accounts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`account_number`	text	no
`name`	text	no
`description`	text	yes
`account_type`	account_type	no
`account_subtype`	account_subtype	no
`parent_account_id`	uuid	yes
`level`	integer	no	1
`status`	account_status	no	’active’::account_status
`is_system_account`	boolean	no	false
`is_control_account`	boolean	no	false
`allows_posting`	boolean	no	true
`requires_fund`	boolean	no	false
`requires_department`	boolean	no	false
`normal_balance`	text	no
`current_balance`	numeric(15,2)	no	0.00
`default_fund_id`	uuid	yes
`tax_line_mapping`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`functional_classification`	character varying(50)	yes		Functional expense classification: program, management, fundraising (FASB ASC 958)
`source_template_id`	text	yes		Template id (CoATemplate.id) that created this account via Apply Template, if any. NULL for manually-created or imported accounts.
`template_applied_at`	timestamp with time zone	yes		Timestamp when the source template was applied. NULL for manually-created or imported accounts.
`is_intercompany`	boolean	no	false	FA-09: account is inter-company (e.g. “Due To/From <entity>”); lines posted to it require related_entity_id.

Foreign keys:

created_by → pf_profiles(id)
default_fund_id → fa_funds(id)
organization_id → pf_organizations(id)
parent_account_id → fa_accounts(id)
updated_by → pf_profiles(id)

Policies:

fa_accounts_delete (DELETE)
fa_accounts_insert (INSERT)
fa_accounts_org_read (SELECT)
fa_accounts_update (UPDATE)

`fa_alert_thresholds`

FA-ENH: Configurable financial alert thresholds that trigger notifications when metrics cross boundaries RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`metric_type`	text	no
`comparison_operator`	text	no
`threshold_value`	numeric(12,2)	no
`severity`	text	no	’warning’::text
`is_active`	boolean	no	true
`notification_roles`	text[]	yes	’{}‘::text[]
`last_triggered_at`	timestamp with time zone	yes
`last_checked_at`	timestamp with time zone	yes
`current_value`	numeric(12,2)	yes
`is_triggered`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_alert_thresholds_delete (DELETE)
fa_alert_thresholds_insert (INSERT)
fa_alert_thresholds_select (SELECT)
fa_alert_thresholds_update (UPDATE)

`fa_allocation_bases`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`pool_id`	uuid	no
`fiscal_year`	integer	no
`base_type`	text	no	’mtdc’::text
`base_total`	numeric	no	0
`exclusion_config`	jsonb	no	’{}‘::jsonb
`computed_at`	timestamp with time zone	no	now()
`computed_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

computed_by → pf_profiles(id)
organization_id → pf_organizations(id)
pool_id → fa_indirect_cost_pools(id)

Policies:

fa_allocation_bases_delete (DELETE)
fa_allocation_bases_insert (INSERT)
fa_allocation_bases_select (SELECT)
fa_allocation_bases_update (UPDATE)

`fa_approval_thresholds`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`module`	character varying(50)	no	’ap_bills’::character varying
`threshold_amount`	numeric(15,2)	no
`approver_role`	app_role	yes
`approver_user_id`	uuid	yes
`description`	text	yes
`is_active`	boolean	no	true
`display_order`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

approver_user_id → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_approval_thresholds_finance_admin_delete (DELETE)
fa_approval_thresholds_finance_admin_insert (INSERT)
fa_approval_thresholds_finance_admin_update (UPDATE)
fa_approval_thresholds_org_select (SELECT)

`fa_ar_payment_applications`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payment_id`	uuid	no
`invoice_id`	uuid	no
`applied_amount`	numeric(15,2)	no
`application_date`	date	no
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
invoice_id → fa_invoices(id)
organization_id → pf_organizations(id)
payment_id → fa_customer_payments(id)
updated_by → users(id)

Policies:

fa_ar_payment_applications_finance_admin_delete (DELETE)
fa_ar_payment_applications_finance_admin_insert (INSERT)
fa_ar_payment_applications_org_select (SELECT)
fa_ar_payment_applications_update (UPDATE)

`fa_asset_depreciation`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_id`	uuid	no
`depreciation_month`	date	no
`depreciation_amount`	numeric(15,2)	no	0
`beginning_book_value`	numeric(15,2)	no	0
`ending_book_value`	numeric(15,2)	no	0
`fiscal_period_id`	uuid	yes
`journal_entry_id`	uuid	yes
`status`	text	no	’gl_posting_pending’::text
`gl_posting_error`	text	yes
`retry_count`	integer	no	0
`last_retry_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

asset_id → fa_fixed_assets(id)
created_by → pf_profiles(id)
fiscal_period_id → fa_fiscal_periods(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)

Policies:

fa_asset_depreciation_delete (DELETE)
fa_asset_depreciation_insert (INSERT)
fa_asset_depreciation_select (SELECT)
fa_asset_depreciation_update (UPDATE)

`fa_asset_disposals`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_id`	uuid	no
`disposal_date`	date	no
`disposal_method`	text	no
`disposal_reason`	text	yes
`book_value_at_disposal`	numeric(15,2)	no	0
`disposal_proceeds`	numeric(15,2)	no	0
`disposal_costs`	numeric(15,2)	no	0
`gain_loss`	numeric(15,2)	no	0
`fiscal_period_id`	uuid	yes
`journal_entry_id`	uuid	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

approved_by → pf_profiles(id)
asset_id → fa_fixed_assets(id)
created_by → pf_profiles(id)
fiscal_period_id → fa_fiscal_periods(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_asset_disposals_delete (DELETE)
fa_asset_disposals_insert (INSERT)
fa_asset_disposals_select (SELECT)
fa_asset_disposals_update (UPDATE)

`fa_asset_transfers`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_id`	uuid	no
`transfer_date`	date	no
`from_site_id`	uuid	yes
`from_building`	text	yes
`from_room`	text	yes
`from_location_description`	text	yes
`to_site_id`	uuid	yes
`to_building`	text	yes
`to_room`	text	yes
`to_location_description`	text	yes
`transfer_reason`	text	yes
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata following FA-11 custom field definitions

Foreign keys:

asset_id → fa_fixed_assets(id)
created_by → pf_profiles(id)
from_site_id → pf_sites(id)
organization_id → pf_organizations(id)
to_site_id → pf_sites(id)

Policies:

fa_asset_transfers_insert (INSERT)
fa_asset_transfers_select (SELECT)

`fa_bad_debt_reserves`

Tracks bad debt reserve calculations and their GL journal entries RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`reserve_date`	date	no
`reserve_amount`	numeric(15,2)	no
`reserve_method`	text	no		Calculation method: percentage_of_sales, aging_analysis, specific_identification
`calculation_details`	jsonb	yes	’{}‘::jsonb	JSON containing method-specific calculation breakdown
`journal_entry_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_bad_debt_reserves_delete (DELETE)
fa_bad_debt_reserves_insert (INSERT)
fa_bad_debt_reserves_select (SELECT)
fa_bad_debt_reserves_update (UPDATE)

`fa_bank_accounts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`account_name`	character varying(255)	no
`account_number`	character varying(50)	no
`bank_name`	character varying(255)	no
`account_type`	character varying(20)	no
`gl_account_id`	uuid	no
`routing_number`	character varying(20)	yes
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`balance`	numeric(15,2)	yes	0	Current account balance. Updated by reconciliation (FA-06) or manual entry. Used by FA-14 cash position calculations.
`fund_id`	uuid	yes		Optional fund association for fund accounting. Enables cash position reporting by fund. Nullable for orgs not using fund accounting.
`plaid_account_id`	text	yes		Plaid account ID from API response (FA-20)
`plaid_item_id`	text	yes		Plaid Item ID linking multiple accounts from same enrollment
`plaid_access_token`	text	yes		Plaid access token for API calls (encrypted at rest via Supabase)
`plaid_institution_id`	text	yes		Plaid institution ID for the connected bank
`plaid_sync_enabled`	boolean	yes	false	Whether automatic transaction sync is enabled for this account
`plaid_last_synced_at`	timestamp with time zone	yes		Timestamp of last successful transaction sync
`plaid_cursor`	text	yes		Cursor for Plaid Sync API incremental pagination
`plaid_connection_status`	text	yes	’disconnected’::text	Current Plaid connection status: connected, disconnected, error, reauth_required
`bank_provider`	text	yes	’manual’::text	Bank connection provider: manual, teller, plaid

Foreign keys:

created_by → pf_profiles(id)
fund_id → fa_funds(id)
gl_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_bank_accounts_delete (DELETE)
fa_bank_accounts_insert (INSERT)
fa_bank_accounts_select (SELECT)
fa_bank_accounts_update (UPDATE)

`fa_bank_reconciliations`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`bank_account_id`	uuid	no
`statement_id`	uuid	no
`reconciliation_date`	date	no
`status`	character varying(20)	no	’draft’::character varying
`gl_beginning_balance`	numeric(15,2)	no	0.00
`gl_ending_balance`	numeric(15,2)	no	0.00
`bank_ending_balance`	numeric(15,2)	no	0.00
`cleared_balance`	numeric(15,2)	no	0.00
`outstanding_checks_total`	numeric(15,2)	no	0.00
`deposits_in_transit_total`	numeric(15,2)	no	0.00
`adjustments_total`	numeric(15,2)	no	0.00
`difference`	numeric(15,2)	yes	(bank_ending_balance - (((gl_ending_balance + deposits_in_transit_total) - outstanding_checks_total) + adjustments_total))
`is_balanced`	boolean	yes	(abs((bank_ending_balance - (((gl_ending_balance + deposits_in_transit_total) - outstanding_checks_total) + adjustments_total))) < 0.01)
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

bank_account_id → fa_bank_accounts(id)
completed_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
statement_id → fa_bank_statements(id)
updated_by → pf_profiles(id)

Policies:

fa_bank_reconciliations_finance_admin_delete (DELETE)
fa_bank_reconciliations_finance_admin_insert (INSERT)
fa_bank_reconciliations_finance_admin_update (UPDATE)
fa_bank_reconciliations_org_read (SELECT)

`fa_bank_statement_lines`

RLS: ✅ enabled (8 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`statement_id`	uuid	no
`line_number`	integer	no
`transaction_date`	date	no
`post_date`	date	yes
`description`	text	no
`amount`	numeric(15,2)	no
`transaction_type`	text	no
`check_number`	character varying(20)	yes
`reference_number`	character varying(50)	yes
`is_matched`	boolean	no	false
`matched_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()	Timestamp when the record was created
`organization_id`	uuid	yes
`plaid_transaction_id`	text	yes		Plaid transaction ID for deduplication (FA-20)
`status`	text	no	’posted’::text	Transaction status: pending (not yet settled), posted (cleared), removed (reversed by bank/provider)
`updated_at`	timestamp with time zone	yes	now()
`rule_id`	uuid	yes
`auto_journal_entry_id`	uuid	yes
`ai_suggested_debit_account_id`	uuid	yes		FA-28: AI-suggested debit account for this transaction
`ai_suggested_credit_account_id`	uuid	yes		FA-28: AI-suggested credit account for this transaction
`ai_categorization_confidence`	numeric(5,2)	yes		FA-28: AI confidence score (0-1) for the suggested categorization
`ai_categorization_reasoning`	text	yes		FA-28: AI explanation for the suggested categorization
`anomaly_flags`	jsonb	yes	’[]‘::jsonb	FA-28: Array of anomaly flags (e.g., [{“type”:“duplicate”,“details”:”…”}])
`anomaly_score`	numeric(5,2)	yes	0	FA-28: Anomaly severity score (0-100)

Foreign keys:

ai_suggested_credit_account_id → fa_accounts(id)
ai_suggested_debit_account_id → fa_accounts(id)
auto_journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
rule_id → fa_transaction_rules(id)
statement_id → fa_bank_statements(id)

Policies:

fa_bank_statement_lines_delete (DELETE)
fa_bank_statement_lines_finance_admin_delete (DELETE)
fa_bank_statement_lines_finance_admin_insert (INSERT)
fa_bank_statement_lines_finance_admin_update (UPDATE)
fa_bank_statement_lines_insert (INSERT)
fa_bank_statement_lines_org_read (SELECT)
fa_bank_statement_lines_select (SELECT)
fa_bank_statement_lines_update (UPDATE)

`fa_bank_statements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`bank_account_id`	uuid	no
`statement_date`	date	no
`period_start_date`	date	no
`period_end_date`	date	no
`beginning_balance`	numeric(15,2)	no
`ending_balance`	numeric(15,2)	no
`total_debits`	numeric(15,2)	no	0.00
`total_credits`	numeric(15,2)	no	0.00
`import_filename`	character varying(255)	yes
`import_format`	character varying(10)	yes
`imported_at`	timestamp with time zone	yes
`imported_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

bank_account_id → fa_bank_accounts(id)
imported_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

fa_bank_statements_finance_admin_delete (DELETE)
fa_bank_statements_finance_admin_insert (INSERT)
fa_bank_statements_finance_admin_update (UPDATE)
fa_bank_statements_org_read (SELECT)

`fa_budget_alerts`

Budget variance alerts exceeding threshold RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`budget_id`	uuid	no
`account_id`	uuid	no
`fund_id`	uuid	yes
`department_id`	uuid	yes
`period_id`	uuid	yes
`budget_amount`	numeric(15,2)	no
`actual_amount`	numeric(15,2)	no
`variance_amount`	numeric(15,2)	no
`variance_percent`	numeric(7,2)	no
`variance_status`	character varying(20)	no
`alert_threshold_percent`	numeric(5,2)	no	10.0
`alert_status`	character varying(20)	no	’active’::character varying
`acknowledged_by`	uuid	yes
`acknowledged_at`	timestamp with time zone	yes
`notification_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for budget alerts
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

account_id → fa_accounts(id)
acknowledged_by → pf_profiles(id)
budget_id → fa_budgets(id)
fund_id → fa_funds(id)
notification_id → pf_notifications(id)
organization_id → pf_organizations(id)
period_id → fa_fiscal_periods(id)

Policies:

fa_budget_alerts_delete (DELETE)
fa_budget_alerts_insert (INSERT)
fa_budget_alerts_select (SELECT)
fa_budget_alerts_update (UPDATE)

`fa_budget_approvals`

Audit trail for budget approval workflow RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`budget_id`	uuid	no
`organization_id`	uuid	no
`action`	character varying(20)	no
`action_by`	uuid	no
`action_at`	timestamp with time zone	no	now()
`comments`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for budget approvals
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

action_by → pf_profiles(id)
budget_id → fa_budgets(id)
organization_id → pf_organizations(id)

Policies:

fa_budget_approvals_delete (DELETE)
fa_budget_approvals_insert (INSERT)
fa_budget_approvals_select (SELECT)
fa_budget_approvals_update (UPDATE)

`fa_budget_lines`

FA-08: Individual budget line items by account and period RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`budget_id`	uuid	no
`account_id`	uuid	no
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`site_id`	uuid	yes
`period_start`	date	no
`period_end`	date	no
`budget_amount`	numeric(15,2)	no	0.00
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()
`organization_id`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
budget_id → fa_budgets(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
program_id → fa_programs(id)
site_id → pf_sites(id)

Policies:

fa_budget_lines_delete (DELETE)
fa_budget_lines_insert (INSERT)
fa_budget_lines_select (SELECT)
fa_budget_lines_update (UPDATE)

`fa_budget_scenarios`

Budget scenarios for what-if analysis (best/worst/base case) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`base_budget_id`	uuid	yes		Optional reference to the approved budget this scenario is based on
`scenario_name`	text	no
`scenario_type`	text	no		Type of scenario: best_case, worst_case, base_case, or custom
`scenario_description`	text	yes
`assumptions`	jsonb	yes	’{}‘::jsonb	Key assumptions and drivers for this scenario as JSON
`status`	text	no	’draft’::text
`custom_fields`	jsonb	yes	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

base_budget_id → fa_budgets(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_budget_scenarios_delete (DELETE)
fa_budget_scenarios_insert (INSERT)
fa_budget_scenarios_select (SELECT)
fa_budget_scenarios_update (UPDATE)

`fa_budget_template_shares`

Explicit sharing grants for budget templates RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_id`	uuid	no
`shared_with_role`	text	yes
`shared_with_user_id`	uuid	yes
`permission`	text	no		view: read-only, apply: can use template, edit: can modify template
`created_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
shared_with_user_id → pf_profiles(id)
template_id → fa_budget_templates(id)

Policies:

fa_budget_template_shares_delete (DELETE)
fa_budget_template_shares_insert (INSERT)
fa_budget_template_shares_select (SELECT)
fa_budget_template_shares_update (UPDATE)

`fa_budget_templates`

Reusable budget templates that can be shared and applied to create new budgets RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_name`	text	no
`template_description`	text	yes
`template_category`	text	yes
`template_structure`	jsonb	no	’{“lines”: [], “metadata”: {}}‘::jsonb	JSONB containing budget line blueprints with account_id, fund_id, amounts, etc.
`sharing_level`	text	yes	’private’::text	private: creator only, organization: all org members, public: editable by org members
`usage_count`	integer	yes	0
`last_used_at`	timestamp with time zone	yes
`last_used_by`	uuid	yes
`usage_log`	jsonb	yes	’[]‘::jsonb	Array of {used_at, used_by, budget_id} entries tracking template usage
`is_active`	boolean	yes	true
`source_budget_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
last_used_by → pf_profiles(id)
organization_id → pf_organizations(id)
source_budget_id → fa_budgets(id)
updated_by → pf_profiles(id)

Policies:

fa_budget_templates_delete (DELETE)
fa_budget_templates_insert (INSERT)
fa_budget_templates_select (SELECT)
fa_budget_templates_update (UPDATE)

`fa_budgets`

FA-08: Budget headers with version control and approval workflow RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`fiscal_year_id`	uuid	no
`version_name`	character varying(255)	no
`version_type`	fa_budget_version_type	no	’baseline’::fa_budget_version_type
`description`	text	yes
`status`	fa_budget_status	no	’draft’::fa_budget_status
`is_active`	boolean	no	false
`is_locked`	boolean	no	false
`submitted_at`	timestamp with time zone	yes
`submitted_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`rejected_at`	timestamp with time zone	yes
`rejected_by`	uuid	yes
`rejection_reason`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
fiscal_year_id → fa_fiscal_years(id)
organization_id → pf_organizations(id)
rejected_by → pf_profiles(id)
submitted_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

fa_budgets_delete (DELETE)
fa_budgets_insert (INSERT)
fa_budgets_select (SELECT)
fa_budgets_update (UPDATE)

`fa_card_transactions`

FA-30: Synced Ramp card transactions for GL coding and reconciliation. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`ramp_connection_id`	uuid	no
`ramp_transaction_id`	text	no
`amount`	numeric	no
`currency`	text	no	’USD’::text
`transaction_date`	date	no
`merchant`	text	yes
`card_program_id`	text	yes
`status`	text	no	’pending’::text
`gl_account_id`	uuid	yes
`vendor_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“memo”: ”…”, “receipt_url”: ”…”})
`created_by`	uuid	yes
`updated_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

created_by → users(id)
gl_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
ramp_connection_id → fa_ramp_connections(id)
updated_by → users(id)
vendor_id → fa_vendors(id)

Policies:

fa_card_transactions_delete (DELETE)
fa_card_transactions_insert (INSERT)
fa_card_transactions_select (SELECT)
fa_card_transactions_update (UPDATE)

`fa_cash_forecasts`

Cash flow forecast data by period. Used for liquidity planning. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`forecast_date`	date	no
`forecast_period`	text	no
`opening_balance`	numeric(15,2)	no
`cash_inflows`	numeric(15,2)	yes	0
`cash_outflows`	numeric(15,2)	yes	0
`net_cash_flow`	numeric(15,2)	yes	(cash_inflows - cash_outflows)
`closing_balance`	numeric(15,2)	yes	((opening_balance + cash_inflows) - cash_outflows)
`forecast_source`	text	yes		Source of forecast: historical analysis, budget data, manual entry, or combined
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_cash_forecasts_delete (DELETE)
fa_cash_forecasts_insert (INSERT)
fa_cash_forecasts_select (SELECT)
fa_cash_forecasts_update (UPDATE)

`fa_cash_positions`

Aggregated cash positions by organization and date. Populated by fa_calculate_cash_position() function. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_date`	date	no	CURRENT_DATE
`operating_cash`	numeric(15,2)	yes	0	Cash in checking accounts (mapped from account_type=checking)
`payroll_cash`	numeric(15,2)	yes	0
`reserve_cash`	numeric(15,2)	yes	0	Cash in savings and reserve accounts (combined)
`grant_cash`	numeric(15,2)	yes	0
`other_cash`	numeric(15,2)	yes	0
`total_cash`	numeric(15,2)	yes	((((operating_cash + payroll_cash) + reserve_cash) + grant_cash) + other_cash)
`cash_by_fund`	jsonb	yes	’{}‘::jsonb	Cash breakdown by fund ID: {“fund_uuid”: amount}
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_cash_positions_delete (DELETE)
fa_cash_positions_insert (INSERT)
fa_cash_positions_select (SELECT)
fa_cash_positions_update (UPDATE)

`fa_category_mappings`

FA-21: Maps transaction categories between source systems and GL accounts for automated categorization RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`source_category`	text	no
`target_category`	text	no
`mapping_rule`	jsonb	no	’{}‘::jsonb	Business logic rules for category mapping (e.g., {“match_type”: “exact”, “conditions”: {…}})
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for category mappings
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted
`plaid_category_primary`	text	yes		FA-21: Plaid personal_finance_category.primary
`plaid_category_detailed`	text	yes		FA-21: Plaid personal_finance_category.detailed (optional)
`debit_account_id`	uuid	yes		FA-21: GL account to debit for this category
`credit_account_id`	uuid	yes		FA-21: GL account to credit for this category
`fund_id`	uuid	yes		FA-21: Optional fund for the mapping
`is_default`	boolean	no	false	FA-21: Whether this is the default mapping for the category

Foreign keys:

created_by → pf_profiles(id)
credit_account_id → fa_accounts(id)
debit_account_id → fa_accounts(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fa_category_mappings_delete (DELETE)
fa_category_mappings_insert (INSERT)
fa_category_mappings_select (SELECT)
fa_category_mappings_update (UPDATE)

`fa_close_checklists`

Close checklists - can be templates or period-specific instances RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`close_period_id`	uuid	yes
`source_template_id`	uuid	yes
`checklist_name`	text	no
`checklist_description`	text	yes
`is_template`	boolean	no	false
`display_order`	integer	no	0
`status`	text	no	’pending’::text
`started_at`	timestamp with time zone	yes
`started_by`	uuid	yes
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

close_period_id → fa_close_periods(id)
completed_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
source_template_id → fa_close_checklists(id)
started_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

fa_close_checklists_delete (DELETE)
fa_close_checklists_insert (INSERT)
fa_close_checklists_select (SELECT)
fa_close_checklists_update (UPDATE)

`fa_close_documentation`

Supporting documentation for close periods RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`close_period_id`	uuid	no
`task_id`	uuid	yes
`document_name`	text	no
`document_type`	text	no	’other’::text
`document_url`	text	yes
`storage_path`	text	yes
`file_size_bytes`	bigint	yes
`mime_type`	text	yes
`description`	text	yes
`uploaded_at`	timestamp with time zone	no	now()
`uploaded_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

close_period_id → fa_close_periods(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
task_id → fa_close_tasks(id)
updated_by → pf_profiles(id)
uploaded_by → pf_profiles(id)

Policies:

fa_close_documentation_delete (DELETE)
fa_close_documentation_insert (INSERT)
fa_close_documentation_select (SELECT)
fa_close_documentation_update (UPDATE)

`fa_close_periods`

Tracks financial close periods (monthly, quarterly, yearly) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`fiscal_period_id`	uuid	yes
`period_type`	text	no
`period_start_date`	date	no
`period_end_date`	date	no
`period_name`	text	no
`status`	text	no	’open’::text
`close_started_at`	timestamp with time zone	yes
`close_started_by`	uuid	yes
`close_completed_at`	timestamp with time zone	yes
`close_completed_by`	uuid	yes
`close_approved_at`	timestamp with time zone	yes
`close_approved_by`	uuid	yes
`close_notes`	text	yes
`close_document_url`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

close_approved_by → pf_profiles(id)
close_completed_by → pf_profiles(id)
close_started_by → pf_profiles(id)
created_by → pf_profiles(id)
fiscal_period_id → fa_fiscal_periods(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_close_periods_delete (DELETE)
fa_close_periods_insert (INSERT)
fa_close_periods_select (SELECT)
fa_close_periods_update (UPDATE)

`fa_close_tasks`

Individual tasks within close checklists RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`checklist_id`	uuid	no
`depends_on_task_id`	uuid	yes
`task_name`	text	no
`task_description`	text	yes
`task_type`	text	no	’manual’::text
`display_order`	integer	no	0
`status`	text	no	’pending’::text
`priority`	text	no	’medium’::text
`assigned_to`	uuid	yes
`due_date`	date	yes
`started_at`	timestamp with time zone	yes
`started_by`	uuid	yes
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`verified_at`	timestamp with time zone	yes
`verified_by`	uuid	yes
`verification_notes`	text	yes
`automation_config`	jsonb	yes
`automation_result`	jsonb	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

assigned_to → pf_profiles(id)
checklist_id → fa_close_checklists(id)
completed_by → pf_profiles(id)
created_by → pf_profiles(id)
depends_on_task_id → fa_close_tasks(id)
organization_id → pf_organizations(id)
started_by → pf_profiles(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

fa_close_tasks_delete (DELETE)
fa_close_tasks_insert (INSERT)
fa_close_tasks_select (SELECT)
fa_close_tasks_update (UPDATE)

`fa_coa_templates`

Org-specific custom CoA templates that finance admins save from their existing chart of accounts and apply later. Built-in industry templates remain code/seed-based in src/cores/fa/data/coaTemplates.ts. Plan: specs/fa/plans/FA-01-coa-templates-completion-PLAN.md (Phase 4). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`source`	text	no	’Custom’::text	Free-text source attribution. Defaults to “Custom” for org-saved templates. Built-in templates have curated sources (SAMHSA, HRSA, NARR, etc.) and remain in code.
`definition`	jsonb	no	’{}‘::jsonb	Full template definition as JSONB. Top-level keys: accounts (required, array of CoATemplateAccount), funds (optional, array of CoATemplateFund), programs (optional, array of CoATemplateProgram). Shape mirrors the code-based CoATemplate type. Validated client-side using the same Zod-equivalent enums (account_type, account_subtype, normal_balance, fund_type).
`is_active`	boolean	no	true	Soft-disable flag. Inactive templates are hidden from the Apply Template list but remain in the table for audit and re-activation.
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_coa_templates_delete (DELETE)
fa_coa_templates_insert (INSERT)
fa_coa_templates_org_read (SELECT)
fa_coa_templates_update (UPDATE)

`fa_collection_activities`

Tracks all collection actions taken on past-due invoices RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`invoice_id`	uuid	no
`activity_type`	text	no		Type of collection action: notice_sent, call_logged, promise_to_pay, payment_plan_created, escalated, written_off, payment_received, other
`activity_date`	timestamp with time zone	no	now()
`notes`	text	yes
`performed_by`	uuid	yes
`next_action_date`	date	yes		Scheduled follow-up date for this invoice
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes		User who created the activity record
`updated_by`	uuid	yes		User who last updated the activity record

Foreign keys:

created_by → pf_profiles(id)
invoice_id → fa_invoices(id)
organization_id → pf_organizations(id)
performed_by → users(id)
updated_by → pf_profiles(id)

Policies:

fa_collection_activities_delete (DELETE)
fa_collection_activities_insert (INSERT)
fa_collection_activities_select (SELECT)
fa_collection_activities_update (UPDATE)

`fa_collection_queue`

FA-24: Tracks overdue invoices in the collection workflow with status, priority, and assignment RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`invoice_id`	uuid	no
`status`	text	no	’open’::text	Workflow status: open, contacted, promised, closed
`priority`	text	no	’medium’::text	Priority level: low, medium, high, critical
`assigned_to`	uuid	yes
`next_action_date`	date	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

assigned_to → pf_profiles(id)
created_by → pf_profiles(id)
invoice_id → fa_invoices(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_collection_queue_delete (DELETE)
fa_collection_queue_insert (INSERT)
fa_collection_queue_select (SELECT)
fa_collection_queue_update (UPDATE)

`fa_compliance_account_mapping`

FA-34: Per-organization mapping from GR compliance event categories to FA expense accounts. Used by fa-gr-compliance-cost-consumer when posting GL entries. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`compliance_type`	text	no		GR compliance category. CHECK constraint enforces allowed values; extend via migration.
`expense_account_id`	uuid	no
`description`	text	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
expense_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_compliance_mapping_delete (DELETE)
fa_compliance_mapping_insert (INSERT)
fa_compliance_mapping_select (SELECT)
fa_compliance_mapping_update (UPDATE)

`fa_consolidation_adjustments`

FA-09: Manual consolidation-only adjustments (US-08, Slice 3 UI). consolidation_run_id FK added with fa_consolidation_runs in Slice 2. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`consolidation_run_id`	uuid	no
`account_id`	uuid	no
`adjustment_type`	character varying(30)	no
`debit_amount`	numeric(15,2)	no	0
`credit_amount`	numeric(15,2)	no	0
`reason`	text	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
organization_id → pf_organizations(id)

Policies:

fa_cons_adj_insert (INSERT)
fa_cons_adj_select (SELECT)
fa_cons_adj_update (UPDATE)

`fa_cost_allocations`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`pool_id`	uuid	no
`project_id`	uuid	no
`fiscal_year`	integer	no
`allocation_base_amount`	numeric	no	0
`idc_rate`	numeric	no	0
`allocated_amount`	numeric	no	0
`status`	text	no	’draft’::text
`allocated_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
pool_id → fa_indirect_cost_pools(id)
project_id → fa_projects(id)
updated_by → pf_profiles(id)

Policies:

fa_cost_allocations_delete (DELETE)
fa_cost_allocations_insert (INSERT)
fa_cost_allocations_select (SELECT)
fa_cost_allocations_update (UPDATE)

`fa_credit_applications`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`credit_memo_id`	uuid	no
`invoice_id`	uuid	no
`applied_amount`	numeric(15,2)	no
`application_date`	date	no
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes		User who last updated this record

Foreign keys:

created_by → pf_profiles(id)
credit_memo_id → fa_credit_memos(id)
invoice_id → fa_invoices(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

credit_applications_finance_delete (DELETE)
credit_applications_finance_insert (INSERT)
credit_applications_org_access (SELECT)
fa_credit_applications_update (UPDATE)

`fa_credit_lines`

Line of credit management for treasury operations. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`bank_account_id`	uuid	yes
`credit_line_number`	text	yes	”::text	Auto-generated: CREDIT-YYYY-NNN format
`credit_line_name`	text	no
`lender_name`	text	no
`credit_limit`	numeric(15,2)	no
`interest_rate`	numeric(10,6)	no
`interest_rate_type`	text	yes
`start_date`	date	no
`end_date`	date	yes
`current_balance`	numeric(15,2)	yes	0
`available_credit`	numeric(15,2)	yes	(credit_limit - current_balance)
`utilization_percentage`	numeric(5,2)	yes	CASE WHEN (credit_limit > (0)::numeric) THEN round(((current_balance / credit_limit) * (100)::numeric), 2) ELSE (0)::numeric END	Current balance / credit limit * 100, rounded to 2 decimals
`interest_expense_account_id`	uuid	yes
`status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

bank_account_id → fa_bank_accounts(id)
created_by → pf_profiles(id)
interest_expense_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_credit_lines_delete (DELETE)
fa_credit_lines_insert (INSERT)
fa_credit_lines_select (SELECT)
fa_credit_lines_update (UPDATE)

`fa_credit_memo_lines`

RLS: ✅ enabled (8 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`credit_memo_id`	uuid	no
`line_number`	integer	no
`description`	text	no
`account_id`	uuid	no
`department_id`	uuid	yes
`fund_id`	uuid	yes
`program_id`	uuid	yes
`quantity`	numeric(10,2)	no	1
`unit_price`	numeric(15,2)	no
`line_amount`	numeric(15,2)	yes	(quantity * unit_price)
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()	Timestamp when the record was created
`organization_id`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
credit_memo_id → fa_credit_memos(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
program_id → fa_programs(id)

Policies:

credit_memo_lines_delete (DELETE)
credit_memo_lines_insert (INSERT)
credit_memo_lines_select (SELECT)
credit_memo_lines_update (UPDATE)
fa_credit_memo_lines_delete (DELETE)
fa_credit_memo_lines_insert (INSERT)
fa_credit_memo_lines_select (SELECT)
fa_credit_memo_lines_update (UPDATE)

`fa_credit_memos`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`credit_memo_number`	text	no
`customer_id`	uuid	no
`credit_memo_date`	date	no
`credit_type`	text	no
`original_invoice_id`	uuid	yes
`subtotal`	numeric(15,2)	no	0
`total_amount`	numeric(15,2)	no	0
`applied_amount`	numeric(15,2)	no	0
`unapplied_amount`	numeric(15,2)	yes	(total_amount - applied_amount)
`status`	text	no	’draft’::text
`reason`	text	yes
`posted_at`	timestamp with time zone	yes
`journal_entry_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
customer_id → fa_customers(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
original_invoice_id → fa_invoices(id)
updated_by → pf_profiles(id)

Policies:

credit_memos_finance_delete (DELETE)
credit_memos_finance_insert (INSERT)
credit_memos_finance_update (UPDATE)
credit_memos_org_access (SELECT)

`fa_customer_payments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payment_number`	text	no
`customer_id`	uuid	no
`payment_date`	date	no
`payment_amount`	numeric(15,2)	no
`payment_method`	text	no
`check_number`	text	yes
`reference_number`	text	yes
`applied_amount`	numeric(15,2)	no	0
`unapplied_amount`	numeric(15,2)	yes	(payment_amount - applied_amount)
`status`	fa_customer_payment_status	no	’unapplied’::fa_customer_payment_status
`posted_at`	timestamp with time zone	yes
`journal_entry_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
customer_id → fa_customers(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_customer_payments_finance_admin_delete (DELETE)
fa_customer_payments_finance_admin_insert (INSERT)
fa_customer_payments_finance_admin_update (UPDATE)
fa_customer_payments_org_select (SELECT)

`fa_customers`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`customer_number`	text	no
`customer_name`	text	no
`customer_type`	fa_customer_type	no	’other’::fa_customer_type
`payment_terms_days`	integer	yes	30
`default_revenue_account_id`	uuid	yes
`billing_address_line1`	text	yes
`billing_address_line2`	text	yes
`billing_city`	text	yes
`billing_state`	text	yes
`billing_postal_code`	text	yes
`billing_country`	text	yes	’US’::text
`contact_name`	text	yes
`contact_email`	text	yes
`contact_phone`	text	yes
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
default_revenue_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_customers_finance_admin_delete (DELETE)
fa_customers_finance_admin_insert (INSERT)
fa_customers_finance_admin_update (UPDATE)
fa_customers_org_select (SELECT)

`fa_dashboard_definitions`

FA-07 Phase 2: Per-organization financial dashboard layout definitions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no	’Financial Dashboard’::text
`layout`	jsonb	no	’[]‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_dashboard_definitions_delete (DELETE)
fa_dashboard_definitions_insert (INSERT)
fa_dashboard_definitions_select (SELECT)
fa_dashboard_definitions_update (UPDATE)

`fa_dashboard_widgets`

FA-16: User-configurable dashboard widget layout and preferences RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`widget_name`	text	no
`widget_type`	text	no
`position_x`	integer	no	0
`position_y`	integer	no	0
`width`	integer	no	1
`height`	integer	no	1
`is_visible`	boolean	no	true
`config`	jsonb	no	’{}‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_dashboard_widgets_delete (DELETE)
fa_dashboard_widgets_insert (INSERT)
fa_dashboard_widgets_select (SELECT)
fa_dashboard_widgets_update (UPDATE)

`fa_deferred_revenue`

FA-18: Deferred revenue (liability). Optionally linked to a recognition schedule. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`invoice_id`	uuid	yes
`revenue_schedule_id`	uuid	yes
`deferred_amount`	numeric(15,2)	no
`recognized_amount`	numeric(15,2)	no	0
`remaining_deferred`	numeric(15,2)	yes	(deferred_amount - recognized_amount)
`recognition_start_date`	date	yes
`recognition_end_date`	date	yes
`status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
invoice_id → fa_invoices(id)
organization_id → pf_organizations(id)
revenue_schedule_id → fa_revenue_schedules(id)
updated_by → pf_profiles(id)

Policies:

fa_deferred_revenue_delete (DELETE)
fa_deferred_revenue_insert (INSERT)
fa_deferred_revenue_select (SELECT)
fa_deferred_revenue_update (UPDATE)

`fa_entities`

FA-09: Legal entity master for multi-entity consolidation. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`created_by`	uuid	yes
`updated_by`	uuid	yes
`legal_name`	character varying(255)	no
`entity_code`	character varying(20)	no
`entity_type`	character varying(50)	no
`tax_id`	character varying(50)	yes
`incorporation_date`	date	yes
`incorporation_state`	character varying(2)	yes
`functional_currency`	character(3)	no	’USD’::bpchar	V1: must equal org reporting currency. FA-09-EN-01 (currency) will relax.
`status`	character varying(20)	no	’active’::character varying
`consolidation_method`	character varying(30)	no	’full’::character varying
`is_reporting_entity`	boolean	no	true
`is_default_primary`	boolean	no	false	Exactly one per org via partial unique index. Auto-created PRIMARY entity for backward compat.
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`deleted_at`	timestamp with time zone	yes

Foreign keys:

organization_id → pf_organizations(id)

Policies:

fa_entities_insert (INSERT)
fa_entities_select (SELECT)
fa_entities_update (UPDATE)

`fa_entity_relationships`

FA-09: Parent/child entity relationships with ownership tracking. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`parent_entity_id`	uuid	no
`child_entity_id`	uuid	no
`ownership_percent`	numeric(5,2)	no	100.00
`effective_date`	date	no	CURRENT_DATE
`end_date`	date	yes
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

child_entity_id → fa_entities(id)
organization_id → pf_organizations(id)
parent_entity_id → fa_entities(id)

Policies:

fa_entity_rel_delete (DELETE)
fa_entity_rel_insert (INSERT)
fa_entity_rel_select (SELECT)
fa_entity_rel_update (UPDATE)

`fa_expense_approvals`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`expense_report_id`	uuid	no
`approver_id`	uuid	no
`approval_order`	integer	no	1
`approval_status`	text	no	’pending’::text
`action_at`	timestamp with time zone	yes
`comments`	text	yes
`delegated_to`	uuid	yes
`delegated_at`	timestamp with time zone	yes
`delegation_reason`	text	yes
`threshold_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

approver_id → pf_profiles(id)
created_by → pf_profiles(id)
delegated_to → pf_profiles(id)
expense_report_id → fa_expense_reports(id)
organization_id → pf_organizations(id)
threshold_id → fa_approval_thresholds(id)
updated_by → pf_profiles(id)

Policies:

fa_expense_approvals_insert (INSERT)
fa_expense_approvals_select (SELECT)
fa_expense_approvals_update (UPDATE)

`fa_expense_category_mappings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`expense_category`	text	no
`gl_account_id`	uuid	no
`default_department_id`	uuid	yes
`default_fund_id`	uuid	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
default_department_id → pf_departments(id)
default_fund_id → fa_funds(id)
gl_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_expense_category_mappings_delete (DELETE)
fa_expense_category_mappings_insert (INSERT)
fa_expense_category_mappings_select (SELECT)
fa_expense_category_mappings_update (UPDATE)

`fa_expense_lines`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`expense_report_id`	uuid	no
`line_number`	integer	no
`expense_date`	date	no
`expense_category`	text	no
`description`	text	no
`vendor_name`	text	yes
`amount`	numeric(15,2)	no
`currency_code`	text	no	’USD’::text
`quantity`	numeric(10,4)	yes	1
`unit_rate`	numeric(15,4)	yes
`miles`	numeric(10,2)	yes
`mileage_rate`	numeric(10,4)	yes
`per_diem_rate`	numeric(15,2)	yes
`per_diem_days`	numeric(5,2)	yes
`gl_account_id`	uuid	yes
`department_id`	uuid	yes
`fund_id`	uuid	yes
`program_id`	uuid	yes
`receipt_attached`	boolean	yes	false
`receipt_storage_path`	text	yes
`receipt_verified`	boolean	yes	false
`receipt_verified_at`	timestamp with time zone	yes
`receipt_verified_by`	uuid	yes
`phi_scan_status`	text	yes	’not_scanned’::text
`phi_scan_result_encrypted`	bytea	yes
`policy_id`	uuid	yes
`policy_exception_approved`	boolean	yes	false
`policy_exception_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`has_policy_violation`	boolean	no	false

Foreign keys:

created_by → pf_profiles(id)
department_id → pf_departments(id)
expense_report_id → fa_expense_reports(id)
fund_id → fa_funds(id)
gl_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
program_id → fa_programs(id)
receipt_verified_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

fa_expense_lines_delete (DELETE)
fa_expense_lines_insert (INSERT)
fa_expense_lines_select (SELECT)
fa_expense_lines_update (UPDATE)

`fa_expense_policies`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`policy_name`	text	no
`policy_code`	text	no
`policy_type`	text	no
`description`	text	yes
`expense_category`	text	yes
`amount_limit`	numeric(15,2)	yes
`daily_limit`	numeric(15,2)	yes
`requires_receipt`	boolean	yes	true
`receipt_threshold`	numeric(15,2)	yes	25.00
`per_diem_rate`	numeric(15,2)	yes
`mileage_rate`	numeric(10,4)	yes
`enforcement_level`	text	no	’warn’::text
`is_active`	boolean	no	true
`effective_start_date`	date	no	CURRENT_DATE
`effective_end_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_expense_policies_delete (DELETE)
fa_expense_policies_insert (INSERT)
fa_expense_policies_select (SELECT)
fa_expense_policies_update (UPDATE)

`fa_expense_reports`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`employee_id`	uuid	no
`report_number`	text	no
`report_name`	text	no
`description`	text	yes
`start_date`	date	no
`end_date`	date	no
`total_amount`	numeric(15,2)	no	0
`currency_code`	text	no	’USD’::text
`status`	text	no	’draft’::text
`submitted_at`	timestamp with time zone	yes
`submitted_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`rejected_at`	timestamp with time zone	yes
`rejected_by`	uuid	yes
`rejection_reason`	text	yes
`default_gl_account_id`	uuid	yes
`department_id`	uuid	yes
`fund_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`journal_entry_id`	uuid	yes
`has_policy_violation`	boolean	no	false

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
default_gl_account_id → fa_accounts(id)
department_id → pf_departments(id)
employee_id → hr_employees(id)
fund_id → fa_funds(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
rejected_by → pf_profiles(id)
site_id → pf_sites(id)
submitted_by → pf_profiles(id)
updated_by → pf_profiles(id)
journal_entry_id → fa_journal_entries(id)

Policies:

fa_expense_reports_delete (DELETE)
fa_expense_reports_insert (INSERT)
fa_expense_reports_select (SELECT)
fa_expense_reports_update (UPDATE)

`fa_expense_rule_conditions`

FA-27: optional scope for a rule (single condition row in Phase 1; OR semantics when multiple rows exist in future). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`rule_id`	uuid	no
`condition_type`	text	no
`value`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
rule_id → fa_expense_rules(id)
updated_by → pf_profiles(id)

Policies:

fa_expense_rule_conditions_delete (DELETE)
fa_expense_rule_conditions_insert (INSERT)
fa_expense_rule_conditions_select (SELECT)
fa_expense_rule_conditions_update (UPDATE)

`fa_expense_rules`

FA-27: declarative expense policy rules (limits, receipt thresholds, per-diem/mileage hooks). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`rule_type`	text	no
`applies_to_category`	text	yes		Optional FA-12 expense category code; scope for category-based rules.
`amount_limit`	numeric(18,2)	yes
`receipt_required_above`	numeric(18,2)	yes
`is_active`	boolean	no	true
`priority`	integer	no	0
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_expense_rules_delete (DELETE)
fa_expense_rules_insert (INSERT)
fa_expense_rules_select (SELECT)
fa_expense_rules_update (UPDATE)

`fa_expense_violations`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`expense_report_id`	uuid	no
`expense_line_id`	uuid	no
`rule_id`	uuid	yes
`violation_type`	text	no
`message`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
expense_line_id → fa_expense_lines(id)
expense_report_id → fa_expense_reports(id)
organization_id → pf_organizations(id)
rule_id → fa_expense_policies(id)

Policies:

fa_expense_violations_delete (DELETE)
fa_expense_violations_insert (INSERT)
fa_expense_violations_select (SELECT)
fa_expense_violations_update (UPDATE)

`fa_fiscal_periods`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`fiscal_year_id`	uuid	no
`period_number`	integer	no
`period_name`	character varying(50)	no
`start_date`	date	no
`end_date`	date	no
`status`	fa_period_status	no	’closed’::fa_period_status
`closed_at`	timestamp with time zone	yes
`closed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

closed_by → pf_profiles(id)
fiscal_year_id → fa_fiscal_years(id)
organization_id → pf_organizations(id)

Policies:

fa_fiscal_periods_delete (DELETE)
fa_fiscal_periods_insert (INSERT)
fa_fiscal_periods_org_read (SELECT)
fa_fiscal_periods_update (UPDATE)

`fa_fiscal_years`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`year_code`	character varying(20)	no
`start_date`	date	no
`end_date`	date	no
`is_current`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_fiscal_years_delete (DELETE)
fa_fiscal_years_insert (INSERT)
fa_fiscal_years_org_read (SELECT)
fa_fiscal_years_update (UPDATE)

`fa_fixed_assets`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`asset_tag`	text	no
`asset_name`	text	no
`description`	text	yes
`asset_category`	text	no	’other’::text
`asset_type`	text	yes
`model`	text	yes
`serial_number`	text	yes
`manufacturer`	text	yes
`purchase_date`	date	yes
`purchase_cost`	numeric(15,2)	no	0
`purchase_order_id`	uuid	yes
`vendor_id`	uuid	yes
`invoice_number`	text	yes
`depreciation_method`	text	no	’straight_line’::text
`useful_life_years`	integer	yes
`useful_life_months`	integer	yes
`salvage_value`	numeric(15,2)	no	0
`depreciation_rate`	numeric(5,4)	yes
`accumulated_depreciation`	numeric(15,2)	no	0
`book_value`	numeric(15,2)	no	0
`last_depreciation_date`	date	yes
`current_site_id`	uuid	yes
`current_building`	text	yes
`current_room`	text	yes
`current_location_description`	text	yes
`status`	text	no	’active’::text
`in_service_date`	date	yes
`disposal_date`	date	yes
`disposal_method`	text	yes
`disposal_proceeds`	numeric(15,2)	yes
`disposal_gain_loss`	numeric(15,2)	yes
`disposal_notes`	text	yes
`asset_account_id`	uuid	yes
`accumulated_depreciation_account_id`	uuid	yes
`depreciation_expense_account_id`	uuid	yes
`gain_loss_account_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

accumulated_depreciation_account_id → fa_accounts(id)
asset_account_id → fa_accounts(id)
created_by → pf_profiles(id)
current_site_id → pf_sites(id)
depreciation_expense_account_id → fa_accounts(id)
gain_loss_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
purchase_order_id → fa_purchase_orders(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
vendor_id → fa_vendors(id)

Policies:

fa_fixed_assets_insert (INSERT)
fa_fixed_assets_select (SELECT)
fa_fixed_assets_update (UPDATE)

`fa_forecast_lines`

Individual line items for rolling forecasts by account and period RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`forecast_id`	uuid	no
`organization_id`	uuid	no
`account_id`	uuid	no
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`site_id`	uuid	yes
`period_start`	date	no
`period_end`	date	no
`forecast_amount`	numeric(15,2)	no	0
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
created_by → pf_profiles(id)
department_id → pf_departments(id)
forecast_id → fa_rolling_forecasts(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
program_id → fa_programs(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fa_forecast_lines_delete (DELETE)
fa_forecast_lines_insert (INSERT)
fa_forecast_lines_select (SELECT)
fa_forecast_lines_update (UPDATE)

`fa_forecast_versions`

Version history for rolling forecasts with complete line snapshots RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`forecast_id`	uuid	no
`organization_id`	uuid	no
`version_number`	integer	no
`version_name`	text	yes
`version_description`	text	yes
`snapshot_date`	date	no
`line_snapshot`	jsonb	no	’[]‘::jsonb	Complete snapshot of forecast lines at version creation time for point-in-time reconstruction
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
forecast_id → fa_rolling_forecasts(id)
organization_id → pf_organizations(id)

Policies:

fa_forecast_versions_delete (DELETE)
fa_forecast_versions_insert (INSERT)
fa_forecast_versions_select (SELECT)
fa_forecast_versions_update (UPDATE)

`fa_funds`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`fund_number`	text	no
`name`	text	no
`description`	text	yes
`fund_type`	fund_type	no	’unrestricted’::fund_type
`is_active`	boolean	no	true
`balance`	numeric(15,2)	no	0.00
`purpose_restriction`	text	yes
`donor_restriction`	text	yes
`time_restriction`	date	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_funds_delete (DELETE)
fa_funds_insert (INSERT)
fa_funds_org_read (SELECT)
fa_funds_update (UPDATE)

`fa_gr_event_log`

FA-34: Idempotent inbox + audit log of GR compliance cost events processed by fa-gr-compliance-cost-consumer Edge Function. Append-only (no DELETE policy). RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`reference_id`	text	no		Idempotency key (typically the GR source event id). Combined with (organization_id, event_type) enforces single processing.
`event_type`	text	no
`payload`	jsonb	no	’{}‘::jsonb
`status`	text	no	’pending’::text
`error_reason`	text	yes
`gl_journal_entry_id`	uuid	yes
`processed_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
gl_journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_gr_event_log_insert (INSERT)
fa_gr_event_log_select (SELECT)
fa_gr_event_log_update (UPDATE)

`fa_grant_drawdowns`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`project_id`	uuid	no
`drawdown_number`	text	no
`drawdown_date`	date	no
`drawdown_amount`	numeric(15,2)	no
`drawdown_type`	text	no	’request’::text
`request_date`	date	yes
`request_status`	text	yes
`request_notes`	text	yes
`receipt_date`	date	yes
`receipt_method`	text	yes
`receipt_reference`	text	yes
`receipt_document_url`	text	yes
`journal_entry_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
project_id → fa_projects(id)
updated_by → pf_profiles(id)

Policies:

fa_grant_drawdowns_delete (DELETE)
fa_grant_drawdowns_insert (INSERT)
fa_grant_drawdowns_select (SELECT)
fa_grant_drawdowns_update (UPDATE)

`fa_import_jobs`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`file_name`	character varying(255)	yes
`status`	character varying(20)	no	’pending’::character varying
`row_count`	integer	yes
`error_count`	integer	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_import_jobs_delete (DELETE)
fa_import_jobs_insert (INSERT)
fa_import_jobs_select (SELECT)
fa_import_jobs_update (UPDATE)

`fa_indirect_cost_pools`

Indirect cost pool definitions for IDC rate calculation (FA-15). Referenced by FA-13 for project cost allocation. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`pool_name`	text	no
`pool_description`	text	yes
`pool_type`	text	no
`expense_account_ids`	uuid[]	no	’{}‘::uuid[]
`total_indirect_costs`	numeric(15,2)	yes	0
`last_calculated_date`	date	yes
`is_active`	boolean	yes	true
`effective_start_date`	date	yes
`effective_end_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_indirect_cost_pools_delete (DELETE)
fa_indirect_cost_pools_insert (INSERT)
fa_indirect_cost_pools_select (SELECT)
fa_indirect_cost_pools_update (UPDATE)

`fa_indirect_cost_rates`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`pool_id`	uuid	no
`rate_type`	text	no
`fiscal_year`	integer	no
`total_indirect_costs`	numeric(15,2)	no
`allocation_base_total`	numeric(15,2)	no
`indirect_cost_rate`	numeric(10,6)	yes	CASE WHEN (allocation_base_total > (0)::numeric) THEN (total_indirect_costs / allocation_base_total) ELSE (0)::numeric END
`allocation_base_type`	text	no
`status`	text	no	’draft’::text
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
pool_id → fa_indirect_cost_pools(id)
updated_by → pf_profiles(id)

Policies:

fa_indirect_cost_rates_delete (DELETE)
fa_indirect_cost_rates_insert (INSERT)
fa_indirect_cost_rates_select (SELECT)
fa_indirect_cost_rates_update (UPDATE)

`fa_investments`

Short-term investment tracking for treasury management. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`bank_account_id`	uuid	yes
`investment_number`	text	yes	”::text	Auto-generated: INV-YYYY-NNN format
`investment_name`	text	no
`investment_type`	text	no
`principal_amount`	numeric(15,2)	no
`interest_rate`	numeric(10,6)	no		Annual interest rate as decimal (e.g., 0.045 = 4.5%)
`purchase_date`	date	no
`maturity_date`	date	no
`interest_earned`	numeric(15,2)	yes	0
`current_value`	numeric(15,2)	yes
`status`	text	no	’active’::text
`investment_account_id`	uuid	yes
`interest_income_account_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

bank_account_id → fa_bank_accounts(id)
created_by → pf_profiles(id)
interest_income_account_id → fa_accounts(id)
investment_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_investments_delete (DELETE)
fa_investments_insert (INSERT)
fa_investments_select (SELECT)
fa_investments_update (UPDATE)

`fa_invoice_lines`

RLS: ✅ enabled (8 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`invoice_id`	uuid	no
`line_number`	integer	no
`description`	text	no
`account_id`	uuid	no
`quantity`	numeric(10,2)	no	1
`unit_price`	numeric(15,2)	no
`line_amount`	numeric(15,2)	no	0
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()	Timestamp when the record was created
`organization_id`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
fund_id → fa_funds(id)
invoice_id → fa_invoices(id)
organization_id → pf_organizations(id)
program_id → fa_programs(id)

Policies:

fa_invoice_lines_delete (DELETE)
fa_invoice_lines_finance_admin_delete (DELETE)
fa_invoice_lines_finance_admin_insert (INSERT)
fa_invoice_lines_finance_admin_update (UPDATE)
fa_invoice_lines_insert (INSERT)
fa_invoice_lines_org_select (SELECT)
fa_invoice_lines_select (SELECT)
fa_invoice_lines_update (UPDATE)

`fa_invoices`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`invoice_number`	text	no
`customer_id`	uuid	no
`invoice_date`	date	no
`due_date`	date	no
`status`	fa_invoice_status	no	’draft’::fa_invoice_status
`subtotal`	numeric(15,2)	no	0
`tax_amount`	numeric(15,2)	no	0
`total_amount`	numeric(15,2)	no	0
`balance_due`	numeric(15,2)	no	0
`posted_at`	timestamp with time zone	yes
`journal_entry_id`	uuid	yes
`source_charge_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`in_collection`	boolean	yes	false
`collection_started_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
customer_id → fa_customers(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fa_invoices_finance_admin_delete (DELETE)
fa_invoices_finance_admin_insert (INSERT)
fa_invoices_finance_admin_update (UPDATE)
fa_invoices_org_select (SELECT)

`fa_journal_entries`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`period_id`	uuid	no
`entry_number`	character varying(50)	yes
`transaction_date`	date	no
`posting_date`	date	no
`description`	text	no
`notes`	text	yes
`status`	fa_journal_entry_status	no	’draft’::fa_journal_entry_status
`source_type`	fa_entry_source_type	no	’MANUAL’::fa_entry_source_type
`source_id`	uuid	yes
`source_reference`	character varying(100)	yes
`posted_at`	timestamp with time zone	yes
`posted_by`	uuid	yes
`reversal_of_entry_id`	uuid	yes
`reversed_by_entry_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
period_id → fa_fiscal_periods(id)
posted_by → pf_profiles(id)
reversal_of_entry_id → fa_journal_entries(id)
reversed_by_entry_id → fa_journal_entries(id)
updated_by → pf_profiles(id)

Policies:

fa_journal_entries_delete (DELETE)
fa_journal_entries_insert (INSERT)
fa_journal_entries_select (SELECT)
fa_journal_entries_update (UPDATE)

`fa_journal_entry_lines`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`journal_entry_id`	uuid	no
`line_number`	integer	no
`account_id`	uuid	no
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`description`	text	yes
`debit_amount`	numeric(15,2)	no	0.00
`credit_amount`	numeric(15,2)	no	0.00
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()	Timestamp when the record was created
`organization_id`	uuid	yes
`related_entity_id`	uuid	yes		FA-09 intercompany counterparty entity for a line posted to an IC-designated account.

Foreign keys:

account_id → fa_accounts(id)
fund_id → fa_funds(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
program_id → fa_programs(id)
related_entity_id → fa_entities(id)

Policies:

fa_journal_entry_lines_delete (DELETE)
fa_journal_entry_lines_insert (INSERT)
fa_journal_entry_lines_select (SELECT)
fa_journal_entry_lines_update (UPDATE)

`fa_kpi_definitions`

FA-16: KPI definitions with thresholds and formulas RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`kpi_code`	text	no
`kpi_name`	text	no
`category`	text	no
`formula`	text	no
`is_active`	boolean	no	true
`target_value`	numeric(12,2)	yes
`unit`	text	no	’ratio’::text
`threshold_warning`	numeric(12,2)	yes
`threshold_critical`	numeric(12,2)	yes
`threshold_direction`	text	no	’higher_is_better’::text
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_kpi_definitions_delete (DELETE)
fa_kpi_definitions_insert (INSERT)
fa_kpi_definitions_select (SELECT)
fa_kpi_definitions_update (UPDATE)

`fa_kpi_history`

FA-16: Append-only KPI calculation history for trend analysis RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`kpi_definition_id`	uuid	no
`calculated_value`	numeric(12,4)	no
`previous_value`	numeric(12,4)	yes
`change_percent`	numeric(8,4)	yes
`status`	text	no	’normal’::text
`calculated_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → users(id)
kpi_definition_id → fa_kpi_definitions(id)
organization_id → pf_organizations(id)

Policies:

fa_kpi_history_insert (INSERT)
fa_kpi_history_no_delete (DELETE)
fa_kpi_history_no_update (UPDATE)
fa_kpi_history_select (SELECT)

`fa_module_settings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`default_fiscal_year_start_month`	integer	yes	1
`default_budget_version_type`	text	yes	’baseline’::text
`require_budget_approval`	boolean	yes	true
`budget_approval_threshold_amount`	numeric(15,2)	yes	10000
`budget_alert_percentages`	integer[]	yes	ARRAY[75, 90, 100]
`invoice_number_prefix`	text	yes	’INV’::text
`invoice_due_days`	integer	yes	30
`auto_generate_invoice_numbers`	boolean	yes	true
`default_invoice_terms`	text	yes	’Net 30’::text
`po_number_prefix`	text	yes	’PO’::text
`require_po_approval`	boolean	yes	true
`po_approval_threshold_amount`	numeric(15,2)	yes	5000
`enable_3_way_match`	boolean	yes	true
`default_payment_method`	text	yes	’check’::text
`payment_batch_approval_required`	boolean	yes	true
`vendor_1099_threshold`	numeric(15,2)	yes	600
`default_date_format`	text	yes	’MM/DD/YYYY’::text
`default_currency`	text	yes	’USD’::text
`show_account_numbers`	boolean	yes	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb
`ai_enabled`	boolean	yes	true	Enable AI features for Finance module
`use_configurable_wizards`	boolean	yes	false	PF-41: Use configurable wizard templates instead of hardcoded wizards
`budget_variance_critical_threshold_percent`	numeric(5,2)	yes	20.00	Percentage threshold for marking budget variances as critical (highlighted in red). Range: 5.00-50.00%.
`invoice_number_sequence`	integer	yes	1	Next sequence number for invoice generation
`po_number_sequence`	integer	yes	1	Next sequence number for purchase order generation
`cash_position_update_interval_minutes`	integer	yes	5	How often to recalculate cash positions (1-1440 minutes)
`investment_maturity_alert_days`	integer	yes	30	Days before maturity to alert (1-365)
`credit_limit_alert_threshold_percent`	numeric(5,2)	yes	80.00	Credit utilization threshold for alerts (50-99%)
`cash_dashboard_refresh_interval_seconds`	integer	yes	300	Dashboard auto-refresh interval (30-3600 seconds)
`investment_number_sequence`	integer	yes	0	Current sequence for investment numbers
`credit_line_number_sequence`	integer	yes	0	Current sequence for credit line numbers
`recurring_generation_time`	time without time zone	yes	’06:00:00’::time without time zone	Time of day (UTC) when recurring invoices are generated by the scheduled edge function
`collection_threshold_days`	integer	yes	30	Number of days past due before an invoice enters the collection workflow (1-365)
`payment_plan_approval_threshold`	numeric(15,2)	yes	1000.00	Payment plans with total amount above this threshold require manager approval
`capitalization_threshold`	numeric(15,2)	yes	5000.00
`depreciation_gl_retry_max_attempts`	integer	yes	5
`plaid_integration_enabled`	boolean	yes	false	Enable Plaid bank feed integration (FA-20)
`drawdown_alert_threshold_percent`	integer	yes	90	Percentage threshold for grant drawdown alerts (FA-13). Default 90%.
`project_number_prefix`	text	yes	’PRJ’::text	Prefix for auto-generated project numbers (FA-13). Default PRJ.
`project_number_include_year`	boolean	yes	true	Include year in project number format (FA-13). Default true for PRJ-YYYY-####.
`project_number_sequence`	integer	yes	1	Current sequence number for project auto-numbering (FA-13).
`idc_allocation_frequency`	text	yes	’monthly’::text	How often IDC is allocated to projects (FA-15). Options: monthly, quarterly, on_demand.
`rule_engine_enabled`	boolean	yes	false	FA-21: Master toggle for transaction rule engine. Default false.
`auto_post_threshold_amount`	numeric(12,2)	yes	5000	FA-21: Auto-post rules only for amounts below this threshold. Default 5000.
`require_review_above_amount`	numeric(12,2)	yes	10000	FA-21: Force draft JE and review queue for amounts above this. Default 10000.
`auto_match_on_sync`	boolean	yes	true	FA-21: Run auto-match after each Plaid sync. Default true.
`auto_match_amount_tolerance`	numeric(8,2)	yes	0.50	FA-21: Amount tolerance for fuzzy matching (+/-). Default 0.50.
`auto_match_date_range_days`	integer	yes	3	FA-21: Date range for matching (+/- days). Default 3. Range 1-30.
`auto_match_description_threshold`	integer	yes	70	FA-21: Fuzzy description match threshold (%). Default 70. Range 0-100.
`collection_queue_days_no_activity`	integer	yes	14	Days of inactivity before a collection queue item is flagged for follow-up (1-365, default 14)
`variance_threshold_percent`	integer	yes	10	FA-16: Percentage threshold for flagging significant budget variances (default 10%)
`ai_categorization_enabled`	boolean	yes	false	FA-28: Enable AI-powered transaction categorization
`ai_categorization_auto_threshold`	numeric(5,2)	yes	0.85	FA-28: Confidence threshold (0-1) above which AI categorizations are auto-applied
`ai_matching_enabled`	boolean	yes	false	FA-28: Enable AI-powered transaction matching
`ai_matching_auto_threshold`	numeric(5,2)	yes	0.90	FA-28: Confidence threshold (0-1) above which AI matches are auto-applied
`ai_anomaly_detection_enabled`	boolean	yes	false	FA-28: Enable AI-powered anomaly detection on bank transactions
`plaid_origination_account_id`	text	yes		FA-20 Transfer UI: Plaid origination (funding) account ID from Dashboard; overrides PLAID_ORIGINATION_ACCOUNT_ID when set
`plaid_link_customization_name`	text	yes		FA-20 Transfer UI: Plaid Link customization name (Account Select: one account) from Dashboard
`email_sender_address`	text	yes		Optional per-module email sender address (e.g. accounting@domain.com). Falls back to org default if null.
`ramp_integration_enabled`	boolean	no	false	FA-30: Enable Ramp corporate cards integration (Connect Ramp, Sync Now, card transactions).
`revenue_recognition_sum_tolerance`	numeric(12,4)	no	0.01	FA-18: rounding tolerance (currency units) for revenue recognition sum checks.
`default_revenue_account_id`	uuid	yes		FA-18 P2: Default revenue (CR) account used by fa_process_revenue_recognition when neither schedule nor contract specifies one.
`default_deferred_revenue_account_id`	uuid	yes		FA-18 P2: Default deferred revenue (DR offset) account used by fa_process_revenue_recognition when neither schedule nor contract specifies one.
`auto_post_revenue_recognition`	boolean	no	false	FA-18 P2: When true, fa_process_revenue_recognition auto-posts the generated journal entry via fa_post_journal_entry. When false (default), the JE is left in draft for manual review.
`auto_create_revenue_schedules`	boolean	no	false	WS-B: When true, fa-consume-revenue-source-events auto-creates revenue schedules from fa_invoice_finalized and fa_revenue_contract_signed domain events.
`intercompany_variance_threshold_cents`	integer	no	100	FA-09: IC variance (cents) at or below which the consolidation validator treats an IC pair as in-balance. Default 100 = $1.00.
`consolidation_max_entities`	integer	no	20	FA-09: Max entities allowed in a single consolidation run. Protects p95 SLA per SC-001.
`consolidation_retention_months`	integer	no	36	FA-09: Months to retain consolidation_runs and consolidated_balances rows.
`consolidation_allow_period_rerun`	boolean	no	true	FA-09: When false, blocks new consolidation_runs for a period that already has a completed run.
`compliance_default_expense_account_id`	uuid	yes		FA-34: Fallback GL expense account when no fa_compliance_account_mapping exists for a compliance_type.
`compliance_default_ap_account_id`	uuid	yes		FA-34: AP accrual account credited on compliance cost GL entries.
`default_cash_account_id`	uuid	yes		FA-05: default Cash (debit) account for AR payment GL posting; falls back to account_number 1000 if null.
`default_ar_account_id`	uuid	yes		FA-05: default Accounts Receivable (credit) account for AR payment GL posting; falls back to account_number 1200 if null.
`auto_approve_expenses_no_violations`	boolean	no	false
`asset_tag_prefix`	text	yes		FA-11: prefix for auto-generated fixed-asset tags (fa_generate_asset_tag); defaults to AST.
`mtdc_base_cost_types`	text[]	no	ARRAY[‘direct’::text]	FA-15 AC-2: fa_project_expenses.cost_type values INCLUDED in the Modified Total Direct Costs base (default direct; indirect excluded).

Foreign keys:

compliance_default_ap_account_id → fa_accounts(id)
compliance_default_expense_account_id → fa_accounts(id)
created_by → pf_profiles(id)
default_ar_account_id → fa_accounts(id)
default_cash_account_id → fa_accounts(id)
default_deferred_revenue_account_id → fa_accounts(id)
default_revenue_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_module_settings_delete (DELETE)
fa_module_settings_insert (INSERT)
fa_module_settings_select (SELECT)
fa_module_settings_update (UPDATE)

`fa_payment_applications`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`payment_id`	uuid	no
`bill_id`	uuid	no
`bill_line_id`	uuid	yes
`amount_applied`	numeric(15,2)	no
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

bill_id → fa_vendor_bills(id)
bill_line_id → fa_vendor_bill_lines(id)
payment_id → fa_payments(id)

Policies:

fa_payment_applications_finance_admin_delete (DELETE)
fa_payment_applications_finance_admin_insert (INSERT)
fa_payment_applications_org_select (SELECT)
fa_payment_applications_update (UPDATE)

`fa_payment_batches`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`batch_number`	character varying(50)	yes
`payment_date`	date	no
`bank_account_id`	uuid	yes
`payment_method`	character varying(20)	no
`total_amount`	numeric(15,2)	yes	0.00
`payment_count`	integer	yes	0
`status`	character varying(20)	yes	’draft’::character varying
`journal_entry_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_payment_batches_finance_admin_delete (DELETE)
fa_payment_batches_finance_admin_insert (INSERT)
fa_payment_batches_finance_admin_update (UPDATE)
fa_payment_batches_org_select (SELECT)

`fa_payment_plan_audit_log`

Immutable audit log for payment plan changes RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`plan_id`	uuid	yes
`installment_id`	uuid	yes
`changed_by`	uuid	yes
`change_type`	text	no
`old_values`	jsonb	yes
`new_values`	jsonb	yes
`reason`	text	yes
`occurred_at`	timestamp with time zone	no	now()
`version_number`	integer	no	1
`created_at`	timestamp with time zone	no	now()

Foreign keys:

changed_by → pf_profiles(id)
installment_id → fa_payment_plan_installments(id)
organization_id → pf_organizations(id)
plan_id → fa_payment_plans(id)

Policies:

fa_payment_plan_audit_log_insert (INSERT)
fa_payment_plan_audit_log_select (SELECT)

`fa_payment_plan_installments`

Individual installments within a payment plan RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payment_plan_id`	uuid	no
`installment_number`	integer	no
`due_date`	date	no
`installment_amount`	numeric(15,2)	no
`status`	text	no	’pending’::text
`paid_amount`	numeric(15,2)	no	0
`paid_date`	date	yes
`payment_id`	uuid	yes
`skipped_at`	timestamp with time zone	yes
`skipped_by`	uuid	yes
`skip_reason`	text	yes
`version_number`	integer	no	1
`audit_timestamp`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
payment_id → fa_customer_payments(id)
payment_plan_id → fa_payment_plans(id)
skipped_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

fa_payment_plan_installments_delete (DELETE)
fa_payment_plan_installments_insert (INSERT)
fa_payment_plan_installments_select (SELECT)
fa_payment_plan_installments_update (UPDATE)

`fa_payment_plans`

Payment plans for customers with approval workflow and audit trail RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`customer_id`	uuid	no
`invoice_id`	uuid	yes
`plan_number`	text	no
`plan_name`	text	yes
`description`	text	yes
`total_amount`	numeric(15,2)	no
`number_of_installments`	integer	no
`payment_frequency`	text	no
`start_date`	date	no
`end_date`	date	yes
`status`	text	no	’draft’::text
`total_paid`	numeric(15,2)	no	0
`remaining_balance`	numeric(15,2)	yes	(total_amount - total_paid)
`submitted_at`	timestamp with time zone	yes
`submitted_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`approval_reason`	text	yes
`approval_chain`	jsonb	yes	’[]‘::jsonb
`rejected_at`	timestamp with time zone	yes
`rejected_by`	uuid	yes
`rejection_reason`	text	yes
`cancelled_at`	timestamp with time zone	yes
`cancelled_by`	uuid	yes
`cancellation_reason`	text	yes
`version_number`	integer	no	1	Incremented on each update for optimistic locking
`audit_timestamp`	timestamp with time zone	no	now()
`reconciliation_hash`	text	yes		SHA-256 hash of financial fields for integrity verification
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

approved_by → pf_profiles(id)
cancelled_by → pf_profiles(id)
created_by → pf_profiles(id)
customer_id → fa_customers(id)
invoice_id → fa_invoices(id)
organization_id → pf_organizations(id)
rejected_by → pf_profiles(id)
submitted_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

fa_payment_plans_delete (DELETE)
fa_payment_plans_insert (INSERT)
fa_payment_plans_select (SELECT)
fa_payment_plans_update (UPDATE)

`fa_payments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`batch_id`	uuid	yes
`vendor_id`	uuid	no
`payment_number`	character varying(50)	yes
`check_number`	character varying(50)	yes
`payment_date`	date	no
`payment_amount`	numeric(15,2)	no
`payment_method`	character varying(20)	no
`memo`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

batch_id → fa_payment_batches(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
vendor_id → fa_vendors(id)

Policies:

fa_payments_finance_admin_delete (DELETE)
fa_payments_finance_admin_insert (INSERT)
fa_payments_finance_admin_update (UPDATE)
fa_payments_org_select (SELECT)

`fa_period_close_audit`

FA-02: Immutable audit log for period close/reopen events. Append-only via SECURITY DEFINER function. RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`period_id`	uuid	no
`action`	text	no
`performed_at`	timestamp with time zone	no	now()
`performed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for close/reopen events.

Foreign keys:

organization_id → pf_organizations(id)
performed_by → pf_profiles(id)
period_id → fa_fiscal_periods(id)

Policies:

fa_period_close_audit_select (SELECT)

`fa_plaid_sync_log`

FA-20: Plaid sync history with error tracking and retry queue for recovery RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`bank_account_id`	uuid	no
`sync_type`	text	no	’manual’::text
`status`	text	no	’pending’::text
`transactions_added`	integer	yes	0
`transactions_modified`	integer	yes	0
`transactions_removed`	integer	yes	0
`new_balance`	numeric(15,2)	yes
`error_code`	text	yes
`error_message`	text	yes
`retry_count`	integer	no	0
`max_retries`	integer	no	3
`next_retry_at`	timestamp with time zone	yes
`started_at`	timestamp with time zone	yes	now()
`completed_at`	timestamp with time zone	yes
`duration_ms`	integer	yes
`triggered_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

bank_account_id → fa_bank_accounts(id)
organization_id → pf_organizations(id)
triggered_by → users(id)
updated_by → users(id)

Policies:

fa_plaid_sync_log_insert (INSERT)
fa_plaid_sync_log_select (SELECT)
fa_plaid_sync_log_update (UPDATE)

`fa_plaid_transfer_intents`

FA-20: Maps Plaid transfer_intent_id to organization for ownership validation RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`plaid_transfer_intent_id`	text	no		Plaid transfer intent ID from /transfer/intent/create
`user_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)
user_id → pf_profiles(id)

Policies:

fa_plaid_transfer_intents_insert (INSERT)
fa_plaid_transfer_intents_select (SELECT)

`fa_pm_event_log`

EN-01: Idempotency log for PM payment_posted and write_off_approved events consumed by FA for GL posting RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`event_id`	uuid	no		fw_domain_events.id - unique per event for idempotency
`event_type`	text	no
`journal_entry_id`	uuid	yes		Created fa_journal_entries.id when status=processed
`processed_at`	timestamp with time zone	no	now()
`status`	text	no	’processed’::text
`error_message`	text	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)

Policies:

fa_pm_event_log_insert (INSERT)
fa_pm_event_log_select (SELECT)
fa_pm_event_log_update (UPDATE)

`fa_po_amendments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`po_id`	uuid	no
`organization_id`	uuid	no
`amendment_number`	integer	no
`amendment_type`	character varying(50)	no
`original_total`	numeric(15,2)	no
`new_total`	numeric(15,2)	no
`reason`	text	no
`requires_reapproval`	boolean	yes	false
`created_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
po_id → fa_purchase_orders(id)

Policies:

fa_po_amendments_delete (DELETE)
fa_po_amendments_insert (INSERT)
fa_po_amendments_select (SELECT)
fa_po_amendments_update (UPDATE)

`fa_po_receipt_lines`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`receipt_id`	uuid	no
`po_line_id`	uuid	no
`quantity_received`	numeric	no
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()	Timestamp when the record was created
`organization_id`	uuid	yes
`created_by`	uuid	yes
`updated_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
po_line_id → fa_purchase_order_lines(id)
receipt_id → fa_po_receipts(id)
updated_by → pf_profiles(id)

Policies:

fa_po_receipt_lines_finance_admin_delete (DELETE)
fa_po_receipt_lines_finance_admin_insert (INSERT)
fa_po_receipt_lines_finance_admin_update (UPDATE)
fa_po_receipt_lines_org_select (SELECT)

`fa_po_receipts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`po_id`	uuid	no
`receipt_number`	character varying	no
`receipt_date`	date	no
`received_by`	uuid	no
`notes`	text	yes
`status`	character varying	no	’completed’::character varying
`voided_at`	timestamp with time zone	yes
`voided_by`	uuid	yes
`void_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
po_id → fa_purchase_orders(id)
received_by → pf_profiles(id)
updated_by → pf_profiles(id)
voided_by → pf_profiles(id)

Policies:

fa_po_receipts_finance_admin_delete (DELETE)
fa_po_receipts_finance_admin_insert (INSERT)
fa_po_receipts_finance_admin_update (UPDATE)
fa_po_receipts_org_select (SELECT)

`fa_programs`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`code`	character varying(20)	no
`name`	character varying(255)	no
`description`	text	yes
`department_id`	uuid	yes
`is_revenue_generating`	boolean	no	true
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_programs_delete (DELETE)
fa_programs_insert (INSERT)
fa_programs_org_read (SELECT)
fa_programs_update (UPDATE)

`fa_project_budgets`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`project_id`	uuid	no
`budget_id`	uuid	yes
`account_id`	uuid	no
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`budget_amount`	numeric(15,2)	no
`period_start_date`	date	yes
`period_end_date`	date	yes
`budget_version`	text	yes	’original’::text
`revision_number`	integer	yes	1
`approved`	boolean	yes	false
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
approved_by → pf_profiles(id)
budget_id → fa_budgets(id)
created_by → pf_profiles(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
project_id → fa_projects(id)
updated_by → pf_profiles(id)

Policies:

fa_project_budgets_delete (DELETE)
fa_project_budgets_insert (INSERT)
fa_project_budgets_select (SELECT)
fa_project_budgets_update (UPDATE)

`fa_project_expenses`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`project_id`	uuid	no
`journal_entry_line_id`	uuid	yes
`vendor_bill_line_id`	uuid	yes
`payroll_expense_id`	uuid	yes
`expense_date`	date	no
`expense_amount`	numeric(15,2)	no
`account_id`	uuid	no
`description`	text	yes
`cost_type`	text	no	’direct’::text
`allocation_percentage`	numeric(5,2)	yes	100.00
`allocated_amount`	numeric(15,2)	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
project_id → fa_projects(id)
updated_by → pf_profiles(id)

Policies:

fa_project_expenses_delete (DELETE)
fa_project_expenses_insert (INSERT)
fa_project_expenses_select (SELECT)
fa_project_expenses_update (UPDATE)

`fa_project_revenue`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`project_id`	uuid	no
`invoice_id`	uuid	yes
`journal_entry_line_id`	uuid	yes
`revenue_date`	date	no
`revenue_amount`	numeric(15,2)	no
`account_id`	uuid	no
`description`	text	yes
`is_drawdown`	boolean	yes	false
`drawdown_request_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
project_id → fa_projects(id)
updated_by → pf_profiles(id)

Policies:

fa_project_revenue_delete (DELETE)
fa_project_revenue_insert (INSERT)
fa_project_revenue_select (SELECT)
fa_project_revenue_update (UPDATE)

`fa_projects`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`fund_id`	uuid	yes
`project_number`	text	no
`project_name`	text	no
`project_description`	text	yes
`project_type`	text	no
`grant_contract_number`	text	yes
`start_date`	date	no
`end_date`	date	yes
`reporting_period_start`	date	yes
`reporting_period_end`	date	yes
`reporting_frequency`	text	yes
`total_award_amount`	numeric(15,2)	yes
`funding_source`	text	yes
`funding_source_type`	text	yes
`status`	text	no	’planning’::text
`status_date`	date	yes
`status_notes`	text	yes
`project_manager_id`	uuid	yes
`program_director_id`	uuid	yes
`total_budget`	numeric(15,2)	yes	0
`total_revenue`	numeric(15,2)	yes	0
`total_expenses`	numeric(15,2)	yes	0
`total_indirect_costs`	numeric(15,2)	yes	0
`net_profitability`	numeric(15,2)	yes	((total_revenue - total_expenses) - total_indirect_costs)
`total_drawdowns`	numeric(15,2)	yes	0
`drawdown_balance`	numeric(15,2)	yes	(COALESCE(total_award_amount, (0)::numeric) - total_drawdowns)
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
program_director_id → pf_profiles(id)
project_manager_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

fa_projects_delete (DELETE)
fa_projects_insert (INSERT)
fa_projects_select (SELECT)
fa_projects_update (UPDATE)

`fa_purchase_order_lines`

FA-04: Line items for purchase orders with GL distribution RLS: ✅ enabled (8 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`po_id`	uuid	no
`line_number`	integer	no
`account_id`	uuid	no
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`description`	text	yes
`quantity`	numeric(12,4)	no
`unit_price`	numeric(12,2)	no
`extended_amount`	numeric(12,2)	yes	(quantity * unit_price)
`unit_of_measure`	character varying(20)	yes
`received_quantity`	numeric(12,4)	no	0.00
`billed_quantity`	numeric(12,4)	no	0.00
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()	Timestamp when the record was created
`organization_id`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
po_id → fa_purchase_orders(id)
program_id → fa_programs(id)

Policies:

fa_po_lines_finance_admin_delete (DELETE)
fa_po_lines_finance_admin_insert (INSERT)
fa_po_lines_finance_admin_update (UPDATE)
fa_po_lines_org_select (SELECT)
fa_purchase_order_lines_delete (DELETE)
fa_purchase_order_lines_insert (INSERT)
fa_purchase_order_lines_select (SELECT)
fa_purchase_order_lines_update (UPDATE)

`fa_purchase_orders`

FA-04: Purchase orders for procurement management RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`po_number`	character varying	no
`vendor_id`	uuid	no
`po_date`	date	no
`delivery_date`	date	yes
`ship_to_site_id`	uuid	yes
`ship_to_address`	text	yes
`po_total`	numeric(12,2)	no	0.00
`received_total`	numeric(12,2)	no	0.00
`billed_total`	numeric(12,2)	no	0.00
`status`	fa_po_status	no	’draft’::fa_po_status
`description`	text	yes
`notes`	text	yes
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`closed_at`	timestamp with time zone	yes
`cancelled_at`	timestamp with time zone	yes
`cancelled_by`	uuid	yes
`cancellation_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

approved_by → pf_profiles(id)
cancelled_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
ship_to_site_id → pf_sites(id)
updated_by → pf_profiles(id)
vendor_id → fa_vendors(id)

Policies:

fa_pos_finance_admin_delete (DELETE)
fa_pos_finance_admin_insert (INSERT)
fa_pos_finance_admin_update (UPDATE)
fa_pos_org_select (SELECT)

`fa_ramp_connections`

FA-30: Ramp OAuth connection per organization. One row per org. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`ramp_entity_id`	text	yes
`access_token`	text	yes		FA-30: OAuth access token. TODO: Migrate to Vault; store vault secret ID only. See REGULATORY_COMPLIANCE_TRACKER.md.
`refresh_token`	text	yes		FA-30: OAuth refresh token. TODO: Migrate to Vault; store vault secret ID only. See REGULATORY_COMPLIANCE_TRACKER.md.
`token_expires_at`	timestamp with time zone	yes
`connection_status`	text	no	’disconnected’::text
`last_synced_at`	timestamp with time zone	yes
`created_by`	uuid	yes
`updated_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`sync_error`	text	yes
`scopes`	text[]	yes	’{}‘::text[]
`access_token_vault_id`	uuid	yes		PF-75: Reference to encrypted credential in pf_credential_vault
`refresh_token_vault_id`	uuid	yes		PF-75: Reference to encrypted credential in pf_credential_vault

Foreign keys:

access_token_vault_id → pf_credential_vault(id)
created_by → users(id)
organization_id → pf_organizations(id)
refresh_token_vault_id → pf_credential_vault(id)
updated_by → users(id)

Policies:

fa_ramp_connections_delete (DELETE)
fa_ramp_connections_insert (INSERT)
fa_ramp_connections_select (SELECT)
fa_ramp_connections_update (UPDATE)

`fa_reconciliation_adjustments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`reconciliation_id`	uuid	no
`adjustment_type`	character varying(50)	no
`description`	text	no
`amount`	numeric(15,2)	no
`gl_account_id`	uuid	no
`journal_entry_id`	uuid	yes
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
gl_account_id → fa_accounts(id)
journal_entry_id → fa_journal_entries(id)
reconciliation_id → fa_bank_reconciliations(id)

Policies:

adjustments_finance_admin_delete (DELETE)
adjustments_finance_admin_insert (INSERT)
adjustments_org_read (SELECT)
fa_reconciliation_adjustments_update (UPDATE)

`fa_reconciliation_matches`

RLS: ✅ enabled (6 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`reconciliation_id`	uuid	no
`bank_line_id`	uuid	yes
`gl_line_id`	uuid	yes
`match_type`	character varying(20)	no	’manual’::character varying
`match_confidence`	integer	yes
`notes`	text	yes
`matched_at`	timestamp with time zone	no	now()
`matched_by`	uuid	no
`created_at`	timestamp with time zone	yes	now()	Timestamp when the record was created
`is_partial`	boolean	yes	false	True when this match is part of a many-to-many group match
`matched_amount`	numeric(15,2)	yes		The specific amount matched in this line (for partial/split matches)
`match_group_id`	uuid	yes		Groups related partial matches together; null for simple 1:1 matches
`organization_id`	uuid	no

Foreign keys:

bank_line_id → fa_bank_statement_lines(id)
gl_line_id → fa_journal_entry_lines(id)
matched_by → pf_profiles(id)
organization_id → pf_organizations(id)
reconciliation_id → fa_bank_reconciliations(id)

Policies:

fa_reconciliation_matches_delete (DELETE)
fa_reconciliation_matches_finance_admin_delete (DELETE)
fa_reconciliation_matches_finance_admin_insert (INSERT)
fa_reconciliation_matches_insert (INSERT)
fa_reconciliation_matches_select (SELECT)
fa_reconciliation_matches_update (UPDATE)

`fa_recurring_entries`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_name`	character varying(255)	no
`description`	text	yes
`frequency`	fa_recurrence_frequency	no
`start_date`	date	no
`end_date`	date	yes
`last_generated_date`	date	yes
`next_generation_date`	date	yes
`is_active`	boolean	no	true
`template_lines`	jsonb	no	’[]‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_recurring_entries_delete (DELETE)
fa_recurring_entries_insert (INSERT)
fa_recurring_entries_select (SELECT)
fa_recurring_entries_update (UPDATE)

`fa_recurring_invoice_lines`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`recurring_invoice_id`	uuid	no
`line_number`	integer	no	1
`description`	text	no
`account_id`	uuid	yes
`quantity`	numeric(10,2)	no	1
`unit_price`	numeric(15,2)	no
`line_amount`	numeric(15,2)	no	0
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
created_by → pf_profiles(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
recurring_invoice_id → fa_recurring_invoices(id)
updated_by → pf_profiles(id)

Policies:

fa_recurring_invoice_lines_delete (DELETE)
fa_recurring_invoice_lines_insert (INSERT)
fa_recurring_invoice_lines_select (SELECT)
fa_recurring_invoice_lines_update (UPDATE)

`fa_recurring_invoices`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`customer_id`	uuid	no
`template_name`	text	no
`template_description`	text	yes
`frequency`	text	no
`custom_interval_days`	integer	yes
`start_date`	date	no
`end_date`	date	yes
`next_generation_date`	date	yes
`generation_day`	integer	yes
`status`	text	no	’active’::text
`auto_send`	boolean	yes	true
`generate_days_before`	integer	yes	0
`last_generated_at`	timestamp with time zone	yes
`last_generated_invoice_id`	uuid	yes
`generation_count`	integer	yes	0
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
customer_id → fa_customers(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_recurring_invoices_delete (DELETE)
fa_recurring_invoices_insert (INSERT)
fa_recurring_invoices_select (SELECT)
fa_recurring_invoices_update (UPDATE)

`fa_recurring_transaction_templates`

FA-21: Templates for recurring journal entries, adjustments, and other automated transactions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`name`	text	no
`description`	text	yes
`template_type`	text	no		Type of recurring transaction: journal_entry, adjustment, depreciation, etc.
`frequency`	text	no		Recurrence frequency: daily, weekly, monthly, quarterly, annually, custom
`next_run_date`	date	no
`last_run_date`	date	yes
`is_active`	boolean	no	true
`template_data`	jsonb	no		Template configuration and transaction details (e.g., {“accounts”: […], “amounts”: […], “description”: ”…”})
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for recurring templates
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fa_recurring_transaction_templates_delete (DELETE)
fa_recurring_transaction_templates_insert (INSERT)
fa_recurring_transaction_templates_select (SELECT)
fa_recurring_transaction_templates_update (UPDATE)

`fa_reimbursement_payments`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`expense_report_id`	uuid	no
`employee_id`	uuid	no
`payment_number`	text	no
`payment_date`	date	no
`payment_amount`	numeric(15,2)	no
`currency_code`	text	no	’USD’::text
`payment_method`	text	no
`status`	text	no	’pending’::text
`bank_account_id`	uuid	yes
`reference_number`	text	yes
`check_number`	text	yes
`journal_entry_id`	uuid	yes
`reversal_payment_id`	uuid	yes
`reversal_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

bank_account_id → fa_bank_accounts(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
expense_report_id → fa_expense_reports(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
reversal_payment_id → fa_reimbursement_payments(id)

Policies:

fa_reimbursement_payments_insert (INSERT)
fa_reimbursement_payments_select (SELECT)

`fa_report_annotations`

FA-07: Inline annotations on financial report lines with PHI awareness RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`report_run_id`	uuid	no
`line_key`	jsonb	no	’{}‘::jsonb	Identifies the report line: {“account_id”: ”…”, “period_key”: ”…”}
`annotation_text`	text	no
`include_in_export`	boolean	no	false	User opt-in to include annotation in PDF/Excel export (default off for PHI safety)
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no	auth.uid()
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
report_run_id → fa_report_runs(id)
updated_by → users(id)

Policies:

fa_report_annotations_delete (DELETE)
fa_report_annotations_insert (INSERT)
fa_report_annotations_select (SELECT)
fa_report_annotations_update (UPDATE)

`fa_report_definitions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`report_type`	character varying	no
`report_name`	character varying	no
`description`	text	yes
`filters`	jsonb	yes	’{}‘::jsonb
`column_config`	jsonb	yes	’{}‘::jsonb
`created_by`	uuid	no
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`is_default`	boolean	yes	false

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

fa_report_definitions_delete (DELETE)
fa_report_definitions_insert (INSERT)
fa_report_definitions_org_read (SELECT)
fa_report_definitions_update (UPDATE)

`fa_report_favorites`

User report favorites. Insert/delete only; audit tracks creation only (created_by). RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`user_id`	uuid	no
`report_key`	text	no
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
user_id → users(id)

Policies:

fa_report_favorites_delete (DELETE)
fa_report_favorites_insert (INSERT)
fa_report_favorites_select (SELECT)

`fa_report_recipients`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`schedule_id`	uuid	no
`recipient_type`	character varying(20)	no
`recipient_value`	text	no
`is_active`	boolean	yes	true
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

schedule_id → fa_report_schedules(id)

Policies:

fa_report_recipients_delete (DELETE)
fa_report_recipients_insert (INSERT)
fa_report_recipients_select (SELECT)
fa_report_recipients_update (UPDATE)

`fa_report_runs`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`schedule_id`	uuid	yes
`organization_id`	uuid	no
`report_type`	character varying(50)	no
`status`	character varying(20)	no	’pending’::character varying
`filters`	jsonb	yes
`export_format`	character varying(20)	yes
`row_count`	integer	yes
`execution_time_ms`	integer	yes
`file_url`	text	yes
`file_size_bytes`	integer	yes
`error_message`	text	yes
`recipients_count`	integer	yes	0
`delivered_count`	integer	yes	0
`failed_count`	integer	yes	0
`started_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	yes	now()
`triggered_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

organization_id → pf_organizations(id)
schedule_id → fa_report_schedules(id)
triggered_by → pf_profiles(id)

Policies:

fa_report_runs_delete (DELETE)
fa_report_runs_org_insert (INSERT)
fa_report_runs_org_read (SELECT)
fa_report_runs_org_update (UPDATE)

`fa_report_schedules`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`report_type`	character varying(50)	no
`schedule_name`	character varying(255)	no
`description`	text	yes
`filters`	jsonb	yes	’{}‘::jsonb
`export_format`	character varying(20)	yes	’pdf’::character varying
`cron_expression`	character varying(100)	no
`timezone`	character varying(100)	yes	’America/New_York’::character varying
`is_enabled`	boolean	yes	true
`created_by`	uuid	no
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes
`last_run_at`	timestamp with time zone	yes
`next_run_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_report_schedules_finance_admin_delete (DELETE)
fa_report_schedules_finance_admin_insert (INSERT)
fa_report_schedules_finance_admin_update (UPDATE)
fa_report_schedules_org_read (SELECT)

`fa_report_versions`

FA-07 Phase 2: Stores versioned snapshots of report configurations for the custom report builder RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`report_definition_id`	uuid	no
`version_number`	integer	no	1
`filters`	jsonb	no	’{}‘::jsonb
`options`	jsonb	no	’{}‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_report_versions_delete (DELETE)
fa_report_versions_insert (INSERT)
fa_report_versions_select (SELECT)
fa_report_versions_update (UPDATE)

`fa_revenue_contracts`

FA-18: Master revenue contracts. Source of recognition schedules for multi-period revenue. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`project_id`	uuid	yes
`contract_number`	text	no
`customer_name`	text	yes
`contract_amount`	numeric(15,2)	no
`contract_start_date`	date	no
`contract_end_date`	date	no
`recognition_method`	text	no	’straight_line’::text
`status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`revenue_account_id`	uuid	yes		FA-18 P2: Revenue (CR) account for recognition postings under this contract. Falls back to fa_module_settings.default_revenue_account_id when NULL.
`deferred_revenue_account_id`	uuid	yes		FA-18 P2: Deferred revenue (DR offset) account for recognition postings under this contract. Falls back to fa_module_settings.default_deferred_revenue_account_id when NULL.

Foreign keys:

created_by → pf_profiles(id)
deferred_revenue_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
project_id → fa_projects(id)
revenue_account_id → fa_accounts(id)
updated_by → pf_profiles(id)

Policies:

fa_revenue_contracts_delete (DELETE)
fa_revenue_contracts_insert (INSERT)
fa_revenue_contracts_select (SELECT)
fa_revenue_contracts_update (UPDATE)

`fa_revenue_milestones`

FA-18 WS-D: Performance-based revenue recognition milestones. Each achievement creates a recognition entry. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`revenue_schedule_id`	uuid	no
`milestone_name`	text	no
`description`	text	yes
`target_date`	date	no
`recognition_amount`	numeric(15,2)	no
`sequence_order`	integer	no	0
`status`	text	no	’pending’::text
`achieved_date`	date	yes
`achieved_by`	uuid	yes
`recognition_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

achieved_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
recognition_id → fa_revenue_recognitions(id)
revenue_schedule_id → fa_revenue_schedules(id)
updated_by → pf_profiles(id)

Policies:

fa_revenue_milestones_delete (DELETE)
fa_revenue_milestones_insert (INSERT)
fa_revenue_milestones_select (SELECT)
fa_revenue_milestones_update (UPDATE)

`fa_revenue_recognitions`

FA-18: Append-only recognition entries. UNIQUE(schedule, period) prevents duplicates. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`revenue_schedule_id`	uuid	no
`recognition_date`	date	no
`recognition_period_start`	date	no
`recognition_period_end`	date	no
`recognition_amount`	numeric(15,2)	no
`fiscal_period_id`	uuid	yes
`journal_entry_id`	uuid	yes
`journal_entry_line_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
fiscal_period_id → fa_fiscal_periods(id)
journal_entry_id → fa_journal_entries(id)
journal_entry_line_id → fa_journal_entry_lines(id)
organization_id → pf_organizations(id)
revenue_schedule_id → fa_revenue_schedules(id)

Policies:

fa_revenue_recognitions_insert (INSERT)
fa_revenue_recognitions_select (SELECT)

`fa_revenue_schedule_modifications`

FA-18 WS-A: Append-only ledger of revenue schedule modifications and recognition reversals. Never updated or deleted. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`revenue_schedule_id`	uuid	no
`modification_type`	text	no
`recognition_id`	uuid	yes
`reversal_recognition_id`	uuid	yes
`before_snapshot`	jsonb	no	’{}‘::jsonb
`after_snapshot`	jsonb	no	’{}‘::jsonb
`amount_delta`	numeric(14,2)	yes
`reason`	text	no
`effective_date`	date	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
recognition_id → fa_revenue_recognitions(id)
revenue_schedule_id → fa_revenue_schedules(id)
reversal_recognition_id → fa_revenue_recognitions(id)

Policies:

fa_rev_sched_mods_insert (INSERT)
fa_rev_sched_mods_select (SELECT)

`fa_revenue_schedule_periods`

FA-18 / FA-UX-15: Per-fiscal-period allocations for a revenue schedule. Persisted atomically by fa_create_revenue_schedule(). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`revenue_schedule_id`	uuid	no
`fiscal_period_id`	uuid	no
`allocated_amount`	numeric(14,2)	no		Amount allocated to this fiscal period at schedule-creation time. Sum across periods must equal fa_revenue_schedules.total_revenue_amount within fa_module_settings.revenue_recognition_sum_tolerance.
`recognized_amount`	numeric(14,2)	no	0
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
fiscal_period_id → fa_fiscal_periods(id)
organization_id → pf_organizations(id)
revenue_schedule_id → fa_revenue_schedules(id)
updated_by → pf_profiles(id)

Policies:

fa_revenue_schedule_periods_delete (DELETE)
fa_revenue_schedule_periods_insert (INSERT)
fa_revenue_schedule_periods_select (SELECT)
fa_revenue_schedule_periods_update (UPDATE)

`fa_revenue_schedules`

FA-18: Revenue recognition schedule. Sourced from EITHER a contract OR an invoice. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`revenue_contract_id`	uuid	yes
`invoice_id`	uuid	yes
`schedule_name`	text	no
`total_revenue_amount`	numeric(15,2)	no
`recognition_start_date`	date	no
`recognition_end_date`	date	no
`total_recognized`	numeric(15,2)	no	0
`remaining_to_recognize`	numeric(15,2)	yes	(total_revenue_amount - total_recognized)
`status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`revenue_account_id`	uuid	yes		FA-18 P2: Revenue (CR) account override for this schedule. Highest precedence; falls back to contract, then module defaults.
`deferred_revenue_account_id`	uuid	yes		FA-18 P2: Deferred revenue (DR offset) account override for this schedule. Highest precedence; falls back to contract, then module defaults.
`recognition_method`	text	no	’straight_line’::text	FA-18 WS-D: straight_line drives period allocation; milestone_based requires fa_revenue_milestones rows.

Foreign keys:

created_by → pf_profiles(id)
deferred_revenue_account_id → fa_accounts(id)
invoice_id → fa_invoices(id)
organization_id → pf_organizations(id)
revenue_account_id → fa_accounts(id)
revenue_contract_id → fa_revenue_contracts(id)
updated_by → pf_profiles(id)

Policies:

fa_revenue_schedules_delete (DELETE)
fa_revenue_schedules_insert (INSERT)
fa_revenue_schedules_select (SELECT)
fa_revenue_schedules_update (UPDATE)

`fa_revenue_source_event_log`

WS-B: Append-only idempotency log for fa-consume-revenue-source-events. One row per (event_id, event_name). RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`event_id`	uuid	no
`event_name`	text	no
`source_type`	text	no
`source_id`	uuid	yes
`revenue_schedule_id`	uuid	yes
`status`	text	no
`error_message`	text	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)
revenue_schedule_id → fa_revenue_schedules(id)

Policies:

fa_revenue_source_event_log_select (SELECT)

`fa_rolling_forecasts`

Rolling forecasts for financial planning with configurable horizons RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`forecast_name`	text	no
`forecast_description`	text	yes
`forecast_start_date`	date	no
`forecast_horizon_months`	integer	yes	12
`source_budget_id`	uuid	yes
`status`	text	no	’draft’::text
`last_updated_date`	date	yes
`update_frequency`	text	yes	’monthly’::text
`sharing_level`	text	yes	’org’::text
`sharing_details`	jsonb	yes	’{}‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
source_budget_id → fa_budgets(id)
updated_by → pf_profiles(id)

Policies:

fa_rolling_forecasts_delete (DELETE)
fa_rolling_forecasts_insert (INSERT)
fa_rolling_forecasts_select (SELECT)
fa_rolling_forecasts_update (UPDATE)

`fa_scenario_budget_lines`

Line items for budget scenarios with account/fund/department allocations RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`scenario_id`	uuid	no
`organization_id`	uuid	no
`account_id`	uuid	no
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`site_id`	uuid	yes
`period_start`	date	no
`period_end`	date	no
`budget_amount`	numeric(15,2)	no	0	Projected budget amount for this line item
`notes`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
created_by → pf_profiles(id)
department_id → pf_departments(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
program_id → fa_programs(id)
scenario_id → fa_budget_scenarios(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fa_scenario_budget_lines_delete (DELETE)
fa_scenario_budget_lines_insert (INSERT)
fa_scenario_budget_lines_select (SELECT)
fa_scenario_budget_lines_update (UPDATE)

`fa_tax_distributions`

FA-10: Tax document distribution tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year_id`	uuid	no
`document_type`	text	no
`document_id`	uuid	yes
`recipient_name`	text	no
`recipient_email`	text	yes
`recipient_address`	text	yes
`distribution_method`	text	no	’electronic’::text
`status`	text	no	’pending’::text
`sent_at`	timestamp with time zone	yes
`delivered_at`	timestamp with time zone	yes
`failure_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
tax_year_id → fa_tax_years(id)
updated_by → users(id)

Policies:

fa_tax_distributions_delete (DELETE)
fa_tax_distributions_insert (INSERT)
fa_tax_distributions_select (SELECT)
fa_tax_distributions_update (UPDATE)

`fa_tax_reports`

FA-10: Quarterly/annual tax reports (Form 941, 940, state reports) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year_id`	uuid	no
`report_type`	text	no
`quarter`	integer	yes
`period_start`	date	yes
`period_end`	date	yes
`report_data`	jsonb	no	’{}‘::jsonb
`total_liability`	numeric(15,2)	no	0
`total_deposits`	numeric(15,2)	no	0
`balance_due`	numeric(15,2)	no	0
`status`	text	no	’draft’::text
`filed_at`	timestamp with time zone	yes
`filed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
filed_by → users(id)
organization_id → pf_organizations(id)
tax_year_id → fa_tax_years(id)
updated_by → users(id)

Policies:

fa_tax_reports_delete (DELETE)
fa_tax_reports_insert (INSERT)
fa_tax_reports_select (SELECT)
fa_tax_reports_update (UPDATE)

`fa_tax_years`

FA-10: Tax year configuration and lifecycle tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year`	integer	no
`status`	text	no	’open’::text
`filing_deadline`	date	yes
`extension_deadline`	date	yes
`notes`	text	yes
`closed_at`	timestamp with time zone	yes
`closed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`ss_wage_base`	numeric(12,2)	yes	168600	FA-10: Social Security wage base for this tax year. Default $168,600 (2024). Updated annually by IRS.

Foreign keys:

closed_by → users(id)
created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

fa_tax_years_delete (DELETE)
fa_tax_years_insert (INSERT)
fa_tax_years_select (SELECT)
fa_tax_years_update (UPDATE)

`fa_template_applications`

Append-only audit of CoA template applies. One row per Apply Template event. Implements FA-01 Phase 3 US-3.1 acceptance criterion “Template metadata stored (e.g., template_id, applied_at) for audit”. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_id`	text	no		CoATemplate.id (kebab-case identifier from src/cores/fa/data/coaTemplates.ts). Stored as text rather than FK because templates are code/seed-based, not DB-backed.
`template_name`	text	no
`source`	text	no
`account_count_created`	integer	no	0
`account_count_skipped`	integer	no	0	Accounts in the template that were not created because an account with the same account_number already existed in the organization.
`fund_count_created`	integer	no	0
`fund_count_skipped`	integer	no	0
`program_count_created`	integer	no	0
`program_count_skipped`	integer	no	0
`created_by`	uuid	no
`created_at`	timestamp with time zone	no	now()

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)

Policies:

fa_template_applications_insert (INSERT)
fa_template_applications_org_read (SELECT)

`fa_transaction_rules`

FA-21: User-defined rules to auto-create GL journal entries from bank transactions. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`priority`	integer	no	100	Lower number = higher priority. First-match-wins evaluation order.
`is_active`	boolean	no	true
`match_description_contains`	text	yes
`match_description_regex`	text	yes
`match_merchant_name`	text	yes
`match_plaid_category`	text	yes
`match_amount_min`	numeric(12,2)	yes
`match_amount_max`	numeric(12,2)	yes
`match_transaction_type`	text	yes
`match_bank_account_id`	uuid	yes
`debit_account_id`	uuid	no
`credit_account_id`	uuid	no
`fund_id`	uuid	yes
`journal_description_template`	text	yes		Template string with {{merchant_name}}, {{date}}, {{amount}}, {{description}}, {{plaid_category_primary}} placeholders.
`auto_post`	boolean	no	false	If true, auto-created JEs are posted (subject to threshold). If false, created as draft.
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active. Preserves rule_id FK from fa_bank_statement_lines.

Foreign keys:

created_by → pf_profiles(id)
credit_account_id → fa_accounts(id)
debit_account_id → fa_accounts(id)
fund_id → fa_funds(id)
match_bank_account_id → fa_bank_accounts(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fa_transaction_rules_delete (DELETE)
fa_transaction_rules_insert (INSERT)
fa_transaction_rules_select (SELECT)
fa_transaction_rules_update (UPDATE)

`fa_vendor_bill_lines`

Line-item distribution of vendor bills to GL accounts RLS: ✅ enabled (8 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`bill_id`	uuid	no
`line_number`	integer	no		Display order within bill
`account_id`	uuid	no
`fund_id`	uuid	yes
`department_id`	uuid	yes
`program_id`	uuid	yes
`description`	text	yes
`amount`	numeric(15,2)	no
`amount_paid`	numeric(15,2)	no	0.00
`custom_fields`	jsonb	no	’{}‘::jsonb
`purchase_order_line_id`	uuid	yes
`quantity_billed`	numeric	yes
`unit_price_billed`	numeric	yes
`organization_id`	uuid	yes

Foreign keys:

account_id → fa_accounts(id)
bill_id → fa_vendor_bills(id)
fund_id → fa_funds(id)
organization_id → pf_organizations(id)
program_id → fa_programs(id)
purchase_order_line_id → fa_purchase_order_lines(id)

Policies:

fa_vendor_bill_lines_delete (DELETE)
fa_vendor_bill_lines_finance_admin_delete (DELETE)
fa_vendor_bill_lines_finance_admin_insert (INSERT)
fa_vendor_bill_lines_finance_admin_update (UPDATE)
fa_vendor_bill_lines_insert (INSERT)
fa_vendor_bill_lines_org_select (SELECT)
fa_vendor_bill_lines_select (SELECT)
fa_vendor_bill_lines_update (UPDATE)

`fa_vendor_bills`

Vendor bills/invoices awaiting approval and payment RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vendor_id`	uuid	no
`bill_number`	character varying(50)	yes
`invoice_number`	character varying(100)	no
`invoice_date`	date	no
`due_date`	date	no
`bill_total`	numeric(15,2)	no	0.00
`amount_paid`	numeric(15,2)	no	0.00
`balance_due`	numeric(15,2)	yes	(bill_total - amount_paid)	Computed: bill_total - amount_paid
`status`	fa_bill_status	no	’draft’::fa_bill_status
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`paid_at`	timestamp with time zone	yes
`journal_entry_id`	uuid	yes
`attachment_document_id`	uuid	yes
`description`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

approved_by → pf_profiles(id)
attachment_document_id → pf_documents(id)
created_by → pf_profiles(id)
journal_entry_id → fa_journal_entries(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
vendor_id → fa_vendors(id)

Policies:

fa_vendor_bills_finance_admin_delete (DELETE)
fa_vendor_bills_finance_admin_insert (INSERT)
fa_vendor_bills_finance_admin_update (UPDATE)
fa_vendor_bills_org_select (SELECT)

`fa_vendors`

Vendor master data with tax and banking information RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vendor_number`	character varying(50)	no
`name`	character varying(255)	no
`doing_business_as`	character varying(255)	yes
`address_line1`	character varying(255)	yes
`address_line2`	character varying(255)	yes
`city`	character varying(100)	yes
`state`	character varying(50)	yes
`postal_code`	character varying(20)	yes
`country`	character varying(2)	yes	’US’::character varying
`phone`	character varying(20)	yes
`email`	character varying(255)	yes
`website`	character varying(255)	yes
`tax_id_encrypted`	text	yes		Encrypted TIN/SSN for 1099 reporting
`requires_1099`	boolean	no	false	Whether this vendor requires 1099 reporting
`form_1099_type`	character varying(20)	yes
`bank_account_name`	character varying(255)	yes
`bank_routing_number_encrypted`	text	yes
`bank_account_number_encrypted`	text	yes
`payment_terms`	integer	yes	30
`default_account_id`	uuid	yes
`is_active`	boolean	no	true
`w9_document_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
default_account_id → fa_accounts(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
w9_document_id → pf_documents(id)

Policies:

fa_vendors_finance_admin_delete (DELETE)
fa_vendors_finance_admin_insert (INSERT)
fa_vendors_finance_admin_update (UPDATE)
fa_vendors_finance_select (SELECT)

`fa_w2_forms`

FA-10: W-2 tax form records for employee wages. employee_id references hr_employees conceptually (no FK to maintain core boundary) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year_id`	uuid	no
`employee_id`	uuid	no
`employee_name`	text	no
`employee_ssn_encrypted`	text	yes
`wages_tips_compensation`	numeric(15,2)	no	0
`federal_tax_withheld`	numeric(15,2)	no	0
`social_security_wages`	numeric(15,2)	no	0
`social_security_tax`	numeric(15,2)	no	0
`medicare_wages`	numeric(15,2)	no	0
`medicare_tax`	numeric(15,2)	no	0
`state_wages`	numeric(15,2)	no	0
`state_tax_withheld`	numeric(15,2)	no	0
`local_wages`	numeric(15,2)	no	0
`local_tax_withheld`	numeric(15,2)	no	0
`box_amounts`	jsonb	no	’{}‘::jsonb
`status`	text	no	’draft’::text
`is_correction`	boolean	no	false
`corrects_form_id`	uuid	yes
`filed_at`	timestamp with time zone	yes
`filed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

corrects_form_id → fa_w2_forms(id)
created_by → users(id)
filed_by → users(id)
organization_id → pf_organizations(id)
tax_year_id → fa_tax_years(id)
updated_by → users(id)

Policies:

fa_w2_forms_delete (DELETE)
fa_w2_forms_insert (INSERT)
fa_w2_forms_select (SELECT)
fa_w2_forms_update (UPDATE)

Clinical Schema Reference Facilities Management Schema Reference

​Entity relationships

​Tables

​fa_1099_forms

​fa_account_balances

​fa_account_mappings

​fa_account_types

​fa_accounts

​fa_alert_thresholds

​fa_allocation_bases

​fa_approval_thresholds

​fa_ar_payment_applications

​fa_asset_depreciation

​fa_asset_disposals

​fa_asset_transfers

​fa_bad_debt_reserves

​fa_bank_accounts

​fa_bank_reconciliations

​fa_bank_statement_lines

​fa_bank_statements

​fa_budget_alerts

​fa_budget_approvals

​fa_budget_lines

​fa_budget_scenarios

​fa_budget_template_shares

​fa_budget_templates

​fa_budgets

​fa_card_transactions

​fa_cash_forecasts

​fa_cash_positions

​fa_category_mappings

​fa_close_checklists

​fa_close_documentation

​fa_close_periods

​fa_close_tasks

​fa_coa_templates

​fa_collection_activities

​fa_collection_queue

​fa_compliance_account_mapping

​fa_consolidation_adjustments

​fa_cost_allocations

​fa_credit_applications

​fa_credit_lines

​fa_credit_memo_lines

​fa_credit_memos

​fa_customer_payments

​fa_customers

​fa_dashboard_definitions

​fa_dashboard_widgets

​fa_deferred_revenue

​fa_entities

​fa_entity_relationships

​fa_expense_approvals

​fa_expense_category_mappings

​fa_expense_lines

​fa_expense_policies

​fa_expense_reports

​fa_expense_rule_conditions

​fa_expense_rules

​fa_expense_violations

​fa_fiscal_periods

​fa_fiscal_years

​fa_fixed_assets

​fa_forecast_lines

​fa_forecast_versions

​fa_funds

​fa_gr_event_log

​fa_grant_drawdowns

​fa_import_jobs

​fa_indirect_cost_pools

​fa_indirect_cost_rates

​fa_investments

​fa_invoice_lines

​fa_invoices

​fa_journal_entries

​fa_journal_entry_lines

​fa_kpi_definitions

​fa_kpi_history

​fa_module_settings

​fa_payment_applications

​fa_payment_batches

Entity relationships

Tables

`fa_1099_forms`

`fa_account_balances`

`fa_account_mappings`

`fa_account_types`

`fa_accounts`

`fa_alert_thresholds`

`fa_allocation_bases`

`fa_approval_thresholds`

`fa_ar_payment_applications`

`fa_asset_depreciation`

`fa_asset_disposals`

`fa_asset_transfers`

`fa_bad_debt_reserves`

`fa_bank_accounts`

`fa_bank_reconciliations`

`fa_bank_statement_lines`

`fa_bank_statements`

`fa_budget_alerts`

`fa_budget_approvals`

`fa_budget_lines`

`fa_budget_scenarios`

`fa_budget_template_shares`

`fa_budget_templates`

`fa_budgets`

`fa_card_transactions`

`fa_cash_forecasts`

`fa_cash_positions`

`fa_category_mappings`

`fa_close_checklists`

`fa_close_documentation`

`fa_close_periods`

`fa_close_tasks`

`fa_coa_templates`

`fa_collection_activities`

`fa_collection_queue`

`fa_compliance_account_mapping`

`fa_consolidation_adjustments`

`fa_cost_allocations`

`fa_credit_applications`

`fa_credit_lines`

`fa_credit_memo_lines`

`fa_credit_memos`

`fa_customer_payments`

`fa_customers`

`fa_dashboard_definitions`

`fa_dashboard_widgets`

`fa_deferred_revenue`

`fa_entities`

`fa_entity_relationships`

`fa_expense_approvals`

`fa_expense_category_mappings`

`fa_expense_lines`

`fa_expense_policies`

`fa_expense_reports`

`fa_expense_rule_conditions`

`fa_expense_rules`

`fa_expense_violations`

`fa_fiscal_periods`

`fa_fiscal_years`

`fa_fixed_assets`

`fa_forecast_lines`

`fa_forecast_versions`

`fa_funds`

`fa_gr_event_log`

`fa_grant_drawdowns`

`fa_import_jobs`

`fa_indirect_cost_pools`

`fa_indirect_cost_rates`

`fa_investments`

`fa_invoice_lines`

`fa_invoices`

`fa_journal_entries`

`fa_journal_entry_lines`

`fa_kpi_definitions`

`fa_kpi_history`

`fa_module_settings`

`fa_payment_applications`

`fa_payment_batches`