Database Reference — SQLite Schema & Query Guide

CRITICAL: This is a SQLite Database

Database engine: SQLite 3
Database file: Set by DB_PATH environment variable
Connection: sqlite3.connect(os.environ['DB_PATH'])

SQLite-Specific Commands (USE THESE)

-- List all tables:
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

-- Get columns for a table:
PRAGMA table_info(table_name);

-- Get all indexes:
SELECT name, tbl_name FROM sqlite_master WHERE type='index';

MySQL Commands That Will FAIL (NEVER USE THESE)

SHOW TABLES;                    -- ❌ FAILS
SHOW DATABASES;                 -- ❌ FAILS
DESCRIBE table_name;            -- ❌ FAILS
information_schema.columns;     -- ❌ FAILS
information_schema.tables;      -- ❌ FAILS

SQLite Syntax Rules — Common Mistakes to Avoid

These are the most frequent SQL errors encountered. Follow these rules exactly:

1. NO FULL OUTER JOIN

SQLite does not support FULL OUTER JOIN. This will return empty results silently.

-- ❌ WRONG — will fail or return nothing:
SELECT ... FROM a FULL OUTER JOIN b ON a.key = b.key

-- ✅ RIGHT — use LEFT JOIN:
SELECT ... FROM a LEFT JOIN b ON a.key = b.key

-- ✅ RIGHT — if you need rows from both sides, use UNION:
SELECT a.key, a.val, b.val FROM a LEFT JOIN b ON a.key = b.key
UNION
SELECT b.key, a.val, b.val FROM b LEFT JOIN a ON b.key = a.key WHERE a.key IS NULL

2. NO RIGHT JOIN

SQLite does not support RIGHT JOIN. Swap table order and use LEFT JOIN.

3. NO IF() function — use CASE WHEN

-- ❌ WRONG: IF(amount > 0, 'positive', 'negative')
-- ✅ RIGHT: CASE WHEN amount > 0 THEN 'positive' ELSE 'negative' END

4. NO ISNULL() — use COALESCE() or IFNULL()

-- ❌ WRONG: ISNULL(column, 0)
-- ✅ RIGHT: COALESCE(column, 0)

5. String concatenation — use || not CONCAT()

-- ❌ WRONG: CONCAT(first_name, ' ', last_name)
-- ✅ RIGHT: first_name || ' ' || last_name

6. Date functions

-- ❌ WRONG: NOW(), CURDATE(), DATEDIFF()
-- ✅ RIGHT: datetime('now'), date('now'), JULIANDAY(date1) - JULIANDAY(date2)

7. The Actuals-vs-Forecast Comparison Pattern

This is the most commonly requested query. Here is the correct SQLite pattern:

-- Compare FY2025 actuals to FY2026 forecast (LEFT JOIN, not FULL OUTER JOIN):
SELECT a.account_code,
       COALESCE(p.description, a.account_code) as description,
       a.actual_2025,
       COALESCE(f.forecast_2026, 0) as forecast_2026,
       ROUND(COALESCE(f.forecast_2026, 0) - a.actual_2025, 0) as variance
FROM (
    SELECT account_code, SUM(amount) as actual_2025
    FROM pl_actuals
    WHERE fiscal_year = 2025 AND scenario = 'actual' AND entity = 'CONSOLIDATED'
    GROUP BY account_code
) a
LEFT JOIN (
    SELECT account_code, SUM(amount) as forecast_2026
    FROM pl_forecasts
    WHERE fiscal_year = 2026 AND entity = 'CONSOLIDATED'
    GROUP BY account_code
) f ON a.account_code = f.account_code
LEFT JOIN pl_line_items p ON a.account_code = p.account_code
ORDER BY ABS(COALESCE(f.forecast_2026, 0)) DESC;

---

Table Directory

This is a curated subset, not an inventory. It documents the tables you will actually query, with the column names people get wrong. Measured against the live tenant schema on 2026-10-05: 362 tables exist and this page names 42.

Do not read a table's absence from this page as evidence it does not exist. That is the most expensive mistake this reference can cause. Ask the database:

SELECT name FROM sqlite_master WHERE type='table'
  AND name NOT LIKE 'sqlite_%' ORDER BY name;

The large families deliberately not detailed below, so you know where to look before concluding something is missing:

Family	Tables not listed here	What lives there
neo_*	75	Opportunity screening, scoring, capture, proposal
pricing_*	36	Rate builds, wrap rates, provisional rate configs
cf_*	18	Cash flow — AR/AP history and open items, payroll model, revolver
ma_*	15	Beyond the eight detailed below
unanet_*	10	ERP mirror and harvest staging
contract_*	18	Beyond the one detailed below
pipeline_* · forecast_* · ai_*	8 each	Pipeline canon and aliases, forecast params, AI cache and notes
report_*	9	Report definitions
debt_*	6	Amortization rules, analysis runs
gl_* · bs_*	4–5 each	GL mapping, balance sheet detail

Those counts drift with every migration. The query above does not.

Core Financial Tables

Table	Purpose	Key Columns
pl_actuals	Historical GL actuals from ERP sync	actual_id, entity, fiscal_year, fiscal_month, account_code, amount, scenario, source
pl_forecasts	All forecasted P&L amounts	forecast_id, entity, fiscal_year, fiscal_month, account_code, amount, source, source_detail
pl_line_items	GL account descriptions & hierarchy	line_id, account_code, description, parent_code, sort_order, section, forecast_source
entity_close_dates	Books close date (actuals/forecast boundary)	entity, accounting_close_date, close_fiscal_year, close_fiscal_month
account_hierarchy	Full chart of accounts with hierarchy	account_key, account_code, description, type (A/L/E), Level_0 through Level_7

Contract Tables

Table	Purpose	Key Columns
contracts	Master contract list from ERP sync	contract_id (INT PK), contract_name, contract_number, division_id (INT → divisions), client_name, is_sca, pop_start_date, pop_end_date, is_active
divisions	Division lookup table	division_id (INT PK), division_name, division_code (TEXT), is_active
forecast_submissions	Contract forecast submission wrapper	submission_id, contract_id (→ contracts.contract_id), fiscal_year, submitted_by, submitted_date, status
forecast_data	Monthly contract forecast dollar amounts	forecast_id, submission_id (→ forecast_submissions), fiscal_year, fiscal_month, revenue, direct_cost, gross_profit, direct_labor_cost, subcontractor_cost, travel_cost, materials_cost, other_cost
contract_actuals	Monthly contract actuals from ERP sync	actual_id, contract_id, fiscal_year, fiscal_month, revenue, direct_labor_cost, subcontractor_cost, travel_cost, materials_cost, other_cost, gross_profit
forecast_periods	Forecast period lock status	period_id, fiscal_year, status (OPEN/LOCKED)

Indirect Cost Tables

Table	Purpose	Key Columns
indirect_forecast_config	Per-account forecast method & settings	config_id, gl_account_code, fiscal_year, forecast_method, elasticity, tier (INT), notes
forecast_parameters	Global engine parameters	param_id, param_key, param_value (REAL), param_text, fiscal_year, entity, description
bonus_budgets	Annual bonus pool budgets	budget_id, fiscal_year, entity, category, gl_account_code, annual_amount, spread_method
indirect_rates	Historical indirect rates from ERP/DCAA	fiscal_year, fringe_rate_nonsca, fringe_rate_sca, overhead_rate, ga_rate, wrap_rate_nonsca, wrap_rate_sca

Balance Sheet & Debt Tables

Table	Purpose	Key Columns
balance_sheet	Monthly projected BS balances	id, entity, fiscal_year, fiscal_month, account_code, account_name (TEXT — built in), amount, source
bs_assumptions	BS driver assumptions (DSO, DPO, etc.)	id, param_name, param_value (REAL), description
cash_flow	Monthly CFS line items	id, entity, fiscal_year, fiscal_month, line_item, section, amount, sort_order
debt_instruments	Debt facilities (loans, revolver)	id, name (TEXT), instrument_type, lender, original_principal (REAL), interest_rate, rate_type, origination_date, maturity_date, facility_limit, is_active
debt_schedule	Monthly debt amortization	id, instrument_id (→ debt_instruments.id), payment_date, fiscal_year, fiscal_month, beginning_balance, principal_payment, interest_payment, ending_balance, is_actual
covenant_config	Covenant thresholds	id, covenant_name, covenant_type, threshold, measurement_frequency, measurement_month, is_active
ebitda_adjustments	EBITDA add-backs	id, category, entity, fiscal_year, fiscal_month, amount, notes
intangible_schedules	Intangible asset D&A	id, asset_name, gl_account_code, original_amount, useful_life_months, monthly_amort, is_active

IMPORTANT — Balance Sheet Notes:

• balance_sheet already contains account_name — no JOIN needed for descriptions
• Account codes are ERP GL codes (e.g., 11.12.13 = Cash, 21.11.11 = Accounts Payable) — NOT simplified codes like CASH or AR
• For section grouping (Assets/Liabilities/Equity), LEFT JOIN account_hierarchy on account_code
• account_hierarchy.type: A = Asset, L = Liability, E = Equity
• There is NO bs_structure table — use account_hierarchy for hierarchical lookups

IMPORTANT — Debt Instrument Column Names:

• The column is name — NOT instrument_name
• The column is original_principal — NOT original_amount
• There is no term_months column

Pipeline & Growth Tables

Table	Purpose	Key Columns
pipeline_opportunities	Pipeline from C2P import (44 cols)	id, opportunity_name, customer_office, division (TEXT), pwin, award_value (REAL), company_value (REAL), annual_revenue, exclude_from_forecast (INT), division_override, duration_years, new_recompete, prime_sub
growth_targets	Annual new business targets by division	id, division (TEXT), fiscal_year, revenue_target (REAL), gp_percent, notes, entity
growth_targets_monthly	Monthly phased new business	id, division, fiscal_year, fiscal_month, revenue, gross_profit
pipeline_division_rules	Auto-classification rules	id, division, match_field, match_pattern, priority, is_active

IMPORTANT — Pipeline Column Names:

• The column is division — NOT division_code
• The column is award_value or company_value — NOT estimated_value
• The column is exclude_from_forecast — NOT is_excluded
• The column is revenue_target — NOT target_revenue
• There is NO weighted_value column — compute as: company_value * pwin
• There is NO stage column — use capture_status or acquisition_status
• The table is pipeline_division_rules — NOT pipeline_classification_rules

Scenario Tables

Table	Purpose	Key Columns
scenarios	Scenario definitions	id, name (TEXT), description, color, is_base (INT), created_at, created_by
scenario_adjustments	Per-scenario adjustments	id, scenario_id, adjustment_type, label, target, fiscal_year, value, metadata
scenario_results	Computed scenario results	scenario_id, fiscal_year, metric, amount

IMPORTANT — Scenario Column Names:

• The column is name — NOT scenario_name
• The column is is_base — NOT is_active
• scenario_results has only amount — NOT base_value, adjusted_value, delta

M&A Tables (8)

Table	Purpose	Key Columns
ma_deals	Deal metadata	deal_id (TEXT PK), deal_name, target_name, close_date, is_active, status, purchase_price, earnout_total, exit_multiple, debt_structure
ma_target_financials	Target company annual financials	deal_id (TEXT), fiscal_year, revenue, revenue_growth, gross_profit, gross_margin, ebitda, adj_ebitda
ma_debt_instruments	Deal-specific debt	instrument_id, deal_id (TEXT), instrument_type, instrument_name, principal (REAL), interest_rate, pik_rate, term_years, is_plug
ma_earnout_schedule	GP-based earnout schedule	deal_id (TEXT), earnout_year, payment_year, base_amount, gp_threshold, gp_target, gp_maximum
ma_sources_uses	Purchase price allocation	deal_id (TEXT), side (TEXT: 'source'/'use'), line_item, amount
ma_goodwill_intangibles	Goodwill & intangible assets	deal_id (TEXT), asset_type, asset_name, gross_amount, useful_life_months, amort_method
ma_scenarios	Deal scenario overlays	deal_id (TEXT), scenario_name, acquirer_revenue_adj, acquirer_margin_adj, target_revenue_adj, target_margin_adj, earnout_pct
ma_covenant_schedule	Deal covenant schedule	deal_id (TEXT), fiscal_year, leverage_max, dscr_min, exit_multiple

IMPORTANT — M&A Column Names:

• ma_deals PK is deal_id (TEXT) — NOT id (INTEGER)
• The column is target_name — NOT target_company
• ma_sources_uses.side — NOT category
• ma_debt_instruments.principal — NOT amount
• ma_goodwill_intangibles has amort_method — NOT monthly_amort

System & Admin Tables

Table	Purpose	Key Columns
system_events	Dirty flag tracking (input changes, computes)	id, event_type, source, user_name, detail, created_at
budget_lock	Budget lock status by year	fiscal_year, is_locked, locked_at, locked_by, note
app_users	User accounts	username, display_name, role, division (TEXT), is_active, email
system_settings	Application settings	key (TEXT PK), value, updated_at, updated_by

---

GL Account Code Structure

Format: XX.YY.ZZ (e.g., 61.11.11)

P&L Account Ranges

Range	Category	Examples
4x.xx.xx	Revenue	41.11.11 = Non-SCA Revenue, 42.11.11 = SCA Revenue
51.xx.xx	Non-SCA Direct Labor	51.11.11 = Non-SCA DL
52.xx.xx	SCA Direct Labor	52.11.11 = SCA DL
53.xx.xx	Subcontractors / Consultants	53.11.11 = Subs
54.xx.xx	Travel	54.11.11 = Travel
55.xx.xx	Materials / Supplies	55.11.11 = Materials
56.xx.xx	Other Direct Costs	56.11.11 = Other DC
61–62.xx	Non-SCA Fringe Benefits	61.11.11 = FICA, 61.21.11 = Health Insurance
63–64.xx	SCA Fringe Benefits	63.11.11 = SCA FICA, 63.21.11 = SCA H&W
66.xx.xx	Facility Costs	66.11.11 = Rent, 66.21.11 = Utilities
71–76.xx	Overhead	71.11.11 = OH Labor, 71.51.11 = OH Awards
81–86.xx	G&A / B&P / BD	81.11.11 = G&A Labor, 82.11.11 = B&P, 83.11.11 = BD
92.xx.xx	Other Income / Unallowables	92.11.11 = Interest Income, 92.31.44 = Interest Expense

Balance Sheet Account Ranges

Range	Category	hierarchy.type
1x	Assets	A
2x	Liabilities	L
3x	Equity	E

Indirect costs (everything below gross profit):

account_code LIKE '6%' OR account_code LIKE '7%' OR account_code LIKE '8%' OR account_code LIKE '9%'

Allocation accounts — ALWAYS EXCLUDE from P&L totals:

account_code NOT IN ('65.99.98','65.99.99','66.99.99','59.99.61','59.99.62','59.99.64',
    '62.41.11','74.99.61','62.41.21','74.99.66','89.99.61','77.99.61','62.41.19','62.41.15','89.99.66')
AND account_code NOT LIKE '89.99.61.%'

---

Critical Query Patterns

Always use these filters:

-- Standard financial query:
WHERE entity = 'CONSOLIDATED'

-- Actuals only (not budgets):
WHERE scenario = 'actual'

-- Forecast by source:
WHERE source = 'contract_forecast'  -- or 'indirect_forecast', 'new_business', 'da_schedule', 'manual_override'

Sign Convention:

• Revenue (4x): POSITIVE numbers
• All costs (5x, 6x, 7x, 8x, 9x): NEGATIVE numbers
• Gross Profit = Revenue + Direct Costs (since costs are negative, this is effectively revenue minus costs)
• Net Income = SUM(all amounts)
• When sorting by size, use ABS(): ORDER BY ABS(SUM(amount)) DESC

Actuals vs Forecast Boundary:

-- Get the close date:
SELECT close_fiscal_year, close_fiscal_month FROM entity_close_dates WHERE entity = 'ALL';

-- Actuals: fiscal_year <= close_fiscal_year
-- Forecast: fiscal_year > close_fiscal_year
-- For the close year itself: months <= close_fiscal_month are actuals, months > are forecast

Joining account descriptions (P&L):

-- Always LEFT JOIN pl_line_items for human-readable P&L account names:
SELECT f.account_code,
       COALESCE(p.description, f.account_code) as description,
       SUM(f.amount) as total
FROM pl_forecasts f
LEFT JOIN pl_line_items p ON f.account_code = p.account_code
WHERE ...
GROUP BY f.account_code;

Balance Sheet queries (account_name is built-in):

-- Simple BS query — no JOIN needed:
SELECT account_code, account_name, amount
FROM balance_sheet
WHERE entity = 'CONSOLIDATED' AND fiscal_year = 2026 AND fiscal_month = 12
ORDER BY account_code;

-- With section grouping:
SELECT bs.account_code, bs.account_name, ah.type as acct_type,
       ah.Level_2 as section, bs.amount
FROM balance_sheet bs
LEFT JOIN account_hierarchy ah ON bs.account_code = ah.account_code
WHERE bs.entity = 'CONSOLIDATED' AND bs.fiscal_year = 2026 AND bs.fiscal_month = 12
ORDER BY ah.type, ah.Level_2, bs.account_name;

-- Search by account name keyword:
SELECT fiscal_month, account_code, account_name, amount
FROM balance_sheet
WHERE entity = 'CONSOLIDATED' AND fiscal_year = 2026
  AND LOWER(account_name) LIKE '%contingent%'
ORDER BY fiscal_month;

Division lookups:

-- Get division code from contracts (requires JOIN):
SELECT c.contract_name, d.division_code, d.division_name
FROM contracts c
JOIN divisions d ON c.division_id = d.division_id;

-- Division codes are configured per tenant (see divisions table)
-- NOTE: contracts uses division_id (INT), pipeline/growth tables use division (TEXT name)

Source values in pl_forecasts:

Valid values: 'contract_forecast', 'indirect_forecast', 'new_business', 'da_schedule', 'manual_override'

---

10 Most Common Queries

1. Total revenue by fiscal year

SELECT fiscal_year, SUM(amount) as revenue
FROM pl_forecasts
WHERE entity = 'CONSOLIDATED' AND account_code LIKE '4%'
GROUP BY fiscal_year ORDER BY fiscal_year;

2. Full P&L summary for a year

SELECT
    CASE
        WHEN account_code LIKE '4%' THEN '1-Revenue'
        WHEN account_code LIKE '5%' THEN '2-Direct Costs'
        WHEN account_code LIKE '6%' THEN '3-Fringe'
        WHEN account_code LIKE '7%' THEN '4-Overhead'
        WHEN account_code LIKE '8%' THEN '5-G&A/B&P/BD'
        WHEN account_code LIKE '9%' THEN '6-Other'
    END as category,
    SUM(amount) as total
FROM pl_forecasts
WHERE entity = 'CONSOLIDATED' AND fiscal_year = 2026
GROUP BY category ORDER BY category;

3. Indirect costs by account (sorted by size)

SELECT f.account_code, COALESCE(p.description, f.account_code) as description,
       SUM(f.amount) as annual_total
FROM pl_forecasts f
LEFT JOIN pl_line_items p ON f.account_code = p.account_code
WHERE f.entity = 'CONSOLIDATED' AND f.fiscal_year = 2026
  AND (f.account_code LIKE '6%' OR f.account_code LIKE '7%'
       OR f.account_code LIKE '8%' OR f.account_code LIKE '9%')
GROUP BY f.account_code
ORDER BY ABS(SUM(f.amount)) DESC;

4. Compare actuals to forecast

SELECT COALESCE(a.account_code, f.account_code) as account_code,
       p.description,
       a.actual_total as fy2025_actual,
       f.forecast_total as fy2026_forecast
FROM (SELECT account_code, SUM(amount) as actual_total
      FROM pl_actuals WHERE fiscal_year=2025 AND scenario='actual' AND entity='CONSOLIDATED'
      GROUP BY account_code) a
LEFT JOIN (SELECT account_code, SUM(amount) as forecast_total
      FROM pl_forecasts WHERE fiscal_year=2026 AND entity='CONSOLIDATED'
      GROUP BY account_code) f ON a.account_code = f.account_code
LEFT JOIN pl_line_items p ON COALESCE(a.account_code, f.account_code) = p.account_code
ORDER BY ABS(COALESCE(f.forecast_total,0)) DESC;

5. Revenue by division (from contract forecasts)

SELECT d.division_code,
       SUM(fd.revenue) as total_revenue,
       SUM(fd.gross_profit) as total_gp
FROM forecast_data fd
JOIN forecast_submissions fs ON fd.submission_id = fs.submission_id
JOIN contracts c ON fs.contract_id = c.contract_id
JOIN divisions d ON c.division_id = d.division_id
WHERE fd.fiscal_year = 2026
GROUP BY d.division_code ORDER BY total_revenue DESC;

6. Monthly revenue trend

SELECT fiscal_year, fiscal_month, SUM(amount) as revenue
FROM pl_forecasts
WHERE entity = 'CONSOLIDATED' AND account_code LIKE '4%'
GROUP BY fiscal_year, fiscal_month
ORDER BY fiscal_year, fiscal_month;

7. Debt balances

SELECT di.name, ds.fiscal_year, ds.fiscal_month, ds.ending_balance
FROM debt_schedule ds
JOIN debt_instruments di ON ds.instrument_id = di.id
ORDER BY di.name, ds.fiscal_year, ds.fiscal_month;

8. Pipeline coverage

SELECT gt.division, gt.revenue_target,
       SUM(po.company_value) as pipeline_value,
       ROUND(SUM(po.company_value) / NULLIF(gt.revenue_target, 0), 1) as coverage
FROM growth_targets gt
LEFT JOIN pipeline_opportunities po
    ON gt.division = po.division AND po.exclude_from_forecast = 0
WHERE gt.fiscal_year = 2026
GROUP BY gt.division;

9. Gross profit and margin

SELECT fiscal_year,
       SUM(CASE WHEN account_code LIKE '4%' THEN amount ELSE 0 END) as revenue,
       SUM(CASE WHEN account_code LIKE '5%' THEN amount ELSE 0 END) as direct_costs,
       SUM(CASE WHEN account_code LIKE '4%' OR account_code LIKE '5%' THEN amount ELSE 0 END) as gross_profit,
       ROUND(SUM(CASE WHEN account_code LIKE '4%' OR account_code LIKE '5%' THEN amount ELSE 0 END) * 100.0 /
             NULLIF(SUM(CASE WHEN account_code LIKE '4%' THEN amount ELSE 0 END), 0), 1) as gp_margin_pct
FROM pl_forecasts
WHERE entity = 'CONSOLIDATED'
GROUP BY fiscal_year ORDER BY fiscal_year;

10. Books close date

SELECT accounting_close_date, close_fiscal_year, close_fiscal_month
FROM entity_close_dates WHERE entity = 'ALL';