Skip to content

Invoicing & Accounting Schema

The invoicing schema owns financial operations: vendors, inbound bills, cardholder receipts, expense reconciliation, budgets, and invoices.

Strict Internal Privacy

All invoicing.* tables are private by design:

  • Excluded from Public API: Raw invoicing tables are never exposed to public PostgREST or granted to public application roles (authenticated, platform_api_role).
  • Accessed via Internal API: Human staff interact with invoicing exclusively through views and RPCs in internal_api using internal_api_role.
  • Worker Execution: Asynchronous processors (email parsing, OCR extraction, Plaid/Amex syncs) interact via direct asyncpg pools and private invoicing.*_for_worker functions.

Project Budgets & Cost Codes

Projects (in projects.projects) anchor financial budgets:

  • Project Types: Projects are immutably typed as renovation or maintenance.
  • Cost Codes: Renovation projects track expenses against structured cost codes (invoicing.project_cost_codes). Maintenance bills use freeform operational codes (cost_code_text).
  • Line Item Immutability: Bill line items are tied immutably to their parent bill (bill_id) to preserve audit provenance, source OCR coordinates, and verification event logs.

Bill Review Mutations

Staff review and mutate bills via guarded RPCs: * internal_api.add_bill_line_item(bill_id, description, amount, is_tax) — Appends a validated line item. * internal_api.remove_bill_line_item(line_item_id) — Removes a line item and compacts positions. * Both enforce project manager or administrator permissions, refresh readiness status, and append audit events (invoicing.bill_events).