FGL Data Model Overview
The FGL data model is built around five core areas — Chart of Accounts, Periods and Calendars, Ledgers, Journals, and Balances — with supporting entities for value sets, segments, code combinations, and period statuses. Understanding how these relate is the key to understanding how Raytio records and reports on financial activity. Conversion rates are used when journal entries are recorded in a currency different from the ledger's functional currency. For details on rate types, daily rates, inverse lookup, and triangulation, see Currency and Conversion Rates.
The big picture
Chart of accounts
The chart of accounts defines the account structure used to classify financial transactions.
Value sets
A Value Set defines the domain of valid values for a single segment of the chart of accounts. Each value set has a code, a validation type (default INDEPENDENT), a data type (default VARCHAR), and a maximum size (default 30 characters).
Values
A Value is an individual entry within a value set. Values carry a code, an enabled flag, and an optional account type (ASSET, LIABILITY, EQUITY, REVENUE, or EXPENSE). Values can form hierarchies through a self-referencing parent link, which supports roll-up reporting.
COA structures
A COA Structure defines the segment layout for a chart of accounts. Each structure has a delimiter (default -), an enabled flag, and a dynamic_insertion_allowed flag that controls whether new values and combinations can be auto-created during validation.
COA segments
A COA Segment maps a numbered position (1–10) within a structure to a value set. Each segment has a column name (segment1 through segment10), a display size, and two exclusive flags:
- Balancing segment — exactly one per structure; identifies the segment used for inter-entity balancing
- Natural account — exactly one per structure; identifies the segment that carries the account type
Code combinations
A Code Combination is a concrete set of segment values within a structure. A trigger-maintained concatenated_segments column joins the segment values with the structure's delimiter for display. Code combinations carry an enabled flag and an allow_posting flag. Only combinations where both are true can receive journal postings.
Periods and calendars
Period types
A Period Type is a reference record that defines how many periods occur per year (e.g. 12 for monthly, 4 for quarterly).
Calendars
A Calendar links a name to a period type and acts as a container for individual periods.
Periods
A Period represents a single accounting period within a calendar. Each period has a year, a number, a start date, an end date, and an adjustment flag. Non-adjustment periods within a calendar cannot overlap (enforced by an exclusion constraint on the date range).
Ledgers
A Ledger ties together a COA structure, a calendar, and a functional currency. Key properties:
| Property | Purpose |
|---|---|
| COA structure | Defines which chart of accounts is used |
| Calendar | Determines the accounting periods |
| Functional currency | The reporting currency for this ledger |
| Suspense enabled | Whether the posting process auto-balances unbalanced journals via a suspense code combination |
| Suspense CCID | The code combination used for suspense entries when enabled |
| Retained earnings CCID | Reserved for year-end close (future phase) |
Period statuses
A Period Status tracks whether a given period is open or closed for a specific application within a ledger. The application codes are GL, AR, and AP. The status lifecycle is:
NEVER_OPENED → FUTURE_ENTRY → OPEN → CLOSED → PERMANENTLY_CLOSED
Period management functions enforce transition rules:
fgl_open_period— transitions toOPENfromNEVER_OPENED,FUTURE_ENTRY, orCLOSEDfgl_close_period— transitions toCLOSEDfromOPENorFUTURE_ENTRY; for GL, requires AR and AP to be closed and no unposted journal headers to remainfgl_permanently_close_period— transitions toPERMANENTLY_CLOSEDfromCLOSEDonly
Journals
Journals follow a batch → header → line hierarchy. For full detail on the journal lifecycle, posting, and reversal, see Journals and Posting.
Journal batches
A Journal Batch groups one or more journal headers for posting as a unit. Batches belong to a ledger and carry a status (UNPOSTED, POSTED, or ERROR).
Journal headers
A Journal Header represents a single journal entry within a batch. Each header records:
- The ledger and accounting period
- A journal name, date, source, and category
- The entry currency
- Status (
UNPOSTED,POSTED, orERROR) - Trigger-maintained running totals for debits and credits
- Reversal tracking fields (
reversal_flag,reversed_header_id)
Journal lines
A Journal Line is a single debit or credit entry on a header. Each line references a code combination, records entered and accounted amounts, and optionally carries a conversion rate for foreign-currency entries. A line is always exactly one of a debit or a credit — never both, never zero.
Balances
A Balance row stores period-level financial totals for a specific code combination, currency, ledger, and period. Balance rows are maintained exclusively by the fgl_post_batch function and are read-only to application users.
Each balance row holds:
| Column | Purpose |
|---|---|
| begin_balance_dr / begin_balance_cr | Opening position, rolled forward from the prior period |
| period_net_dr / period_net_cr | Net activity posted during this period |
End balances are computed as begin_balance + period_net and are exposed through the trial balance view.
Reporting views
Two views provide reporting access to posted data:
- Trial Balance (
fgl_trial_balance) — balances by code combination and period, with computed end balances. See Journals and Posting — Trial balance. - Account Activity (
fgl_account_activity) — posted journal lines by code combination and period. See Journals and Posting — Account activity.
Entity summary
| Entity | Purpose |
|---|---|
| Value Set | Domain of valid values for a COA segment |
| Value | Individual value within a value set, with optional account type and hierarchy |
| COA Structure | Segment layout and delimiter for a chart of accounts |
| COA Segment | Maps a segment position to a value set within a structure |
| Code Combination | Concrete segment-value tuple used as a posting target |
| Period Type | Reference record defining periods per year |
| Calendar | Named container linking a period type to accounting periods |
| Period | Single accounting period with date range |
| Ledger | Central configuration linking COA structure, calendar, and functional currency |
| Period Status | Open/close state of a period for a specific application within a ledger |
| Journal Batch | Groups journal headers for batch posting |
| Journal Header | Single journal entry with period, currency, source, and category |
| Journal Line | Individual debit or credit line referencing a code combination |
| Balance | Period-level stored totals per code combination, currency, and ledger |
Multi-tenancy
The entire FGL model is tenant-scoped. Every entity belongs to a tenant, and row-level security ensures that users only see data belonging to their own tenant. Within a tenant, authorisation is managed through the platform's permission system, with FGL-specific roles described in Roles.