Reader-friendly documentation

Open AP per vendor bill, aged into buckets.

One row per open vendor bill as of the snapshot day, with the vendor, remaining functional-USD balance, days overdue from due_date, and aging bucket (current / 1_30 / 31_60 / 61_90 / 90_plus). Total open AP ties to the AP control account (subledger_ties_to_gl).

Open AP totals by aging bucket and vendor segment.

De-identified rollup of open AP by (as_of_day × aging_bucket × vendor_segment). vendor_segment is the vendor's payment_terms (a coarse, non-identifying class), not the vendor itself. `open_amount` is summed functional USD; `vendors_count` is a distinct vendor count.

The approval-workflow rules that gate proposals — what triggers a sign-off, who signs, and the SLA.

One row per approval rule (grain `rule_id`), sourced from `stg_gcs__rfx_approval_rules`. Each row describes one gate in the proposal-approval chain: `trigger_type` is what it fires on (`discount_pct` / `deal_value` / `margin_floor` / `legal_terms`), `threshold_value` + `comparator` (`gt` / `gte` / `lt` / `lte`) form the condition, `approver_role` is who must approve when it fires, `sequence` orders the rule in the chain, `escalation_role` + `sla_hours` define the escalation if the SLA is breached, and `active` flags whether the rule is currently in force. Reference / governance data — no personal data. Use it to answer "what discount triggers exec approval" or "what is the approval chain for a large deal".

Open AR per customer invoice, aged into buckets.

One row per open customer invoice as of the snapshot day, with the customer, remaining functional-USD balance, days overdue from due_date, and aging bucket. Total open AR ties to the AR control account (subledger_ties_to_gl).

Open AR totals by aging bucket and customer segment.

De-identified rollup of open AR by (as_of_day × aging_bucket × customer_segment). customer_segment is the coarse ecommerce segment, not a customer identifier. `open_amount` is summed functional USD; `customers_count` is a distinct count.

Budget vs posted actual, by account, cost center, and period.

One row per (period × cost_center × gl_account) with the approved `budget_amount`, the posted `actual_amount` (sum(debit) - sum(credit) from the trial balance), and `variance_amount` (actual - budget). FULL JOIN so budget-only and actual-only cells both appear.

Daily cash in/out and running balance per bank account.

One row per (as_of_day × bank_account) from bank transactions: credit increases cash, debit decreases it. `net_flow` is the day's credit - debit; `running_balance` is the cumulative balance through the day per account (the latest row per account is the current balance). A cash snapshot, not a full cash-flow-statement categorization.

Per-client×contract SLA scorecard — OTIF and pick-accuracy actual vs target, volume, and billing.

One row per client×contract engagement (grain (client_id, contract_id)), sourced from int_whse__contract_sla joined to fulfillment actuals. Carries each SLA target next to its achieved actual (otif_target_pct/otif_actual_pct, pick_accuracy_target_pct/ pick_accuracy_actual_pct, dock_to_stock_target_hrs), the derived otif_gap_pct (negative = breach), order_volume, billing_model (∈ {open_book, fixed_fee, cost_plus, activity_based}), and client segment/tier. client_name is a company, not PII. Client-scoped via the client_account_manager row policy on client_id — the canonical isolation-demo surface: two managers open this and see disjoint scorecards.

Where each customer stands on each purpose, right now.

Point-in-time snapshot of the consent ledger — the latest event per (customer × purpose) wins. The `is_active` flag is the gate analytics queries should filter on before processing personal data for that purpose. Snapshot only; for trend (withdrawals over time) read the source log `stg_postgres__consent_events`.

Who are our customers right now, and which ones are at risk.

Point-in-time view of every customer with a derived health flag. Joins lifetime revenue (from mart_customers) with support footprint (tickets, resolution, CSAT). `at_risk` fires on any of: CSAT ≤ 2.5, ≥3 currently-open tickets, or ≥5 lifetime high-priority tickets. Snapshot — not a time series.

Every customer with their lifetime order rollup.

One row per customer carrying lifetime order count, lifetime revenue, and first/last order timestamps. The customer-keyed input to `mart_customer_support_health` and to per-customer queries. PII (email) is masked out for AI personas via column grants — row-level access is gated by row policies (e.g. `region_us_analyst` sees only `country='US'`).

Embedded chunks — the semantic-search surface.

One row per chunk with an `embedding Array(Float32)` column and a `vector_similarity('hnsw', 'cosineDistance', N)` index. AI reaches this mart only via `search_documents` — never via raw SQL. The `embedding` column is initially `[]`; the embedding worker fills it idempotently.

Catalog over our unstructured corpora.

One row per document we've extracted. Use this to look up what we have, what the extractor did to it, and how much PII it likely contains (per the Presidio entity-count summary). The searchable surface is `mart_document_chunks`; this mart is the index of what's been indexed.

Email, chat, and notes unified into one searchable comms row.

One row per finance communication (email message, chat message, or finance note) carrying its subject/title and inline body_text, the GCS source_uri / document_id, and the best-effort linked finance entity (from communication_links). The body text + subject are free-text PII; AI reaches the body only via search_documents, never through this raw mart (which is controller/brain-only). This mart is deliberately NOT exposed as a metric — free text never enters the metric layer.

Forecasted amounts by scenario, version, period, and cost center.

One row per (period × cost_center × scenario × version × gl_account) with the forecasted amount and the forecast header (scenario, version, status). Versions are kept side by side so accuracy can be measured against a fixed baseline. The forecast side of forecast-accuracy.

Daily fulfillment rollup — OTIF, on-time, in-full, and pick-accuracy counts by segment, region, temp class, and carrier.

The pre-aggregated operational-analytics rollup for the 3PL fulfillment domain. One row per (ship_month × client_segment × site_region × temp_class × carrier) carrying raw count columns (never pre-divided rates): order_count, units_shipped, otif_count, on_time_count, in_full_count, accurate_lines, total_lines. Only shipped orders are counted (ship_date is not null), so the counts are correct denominators for on-time/OTIF rates. ship_month is toStartOfMonth(ship_date), so this is a trend surface. It feeds two metrics — fulfillment (OTIF/on-time/in-full rates) and pick_accuracy (accurate_lines/total_lines) — which recompute rates with nullif formula measures so multi-bucket roll-ups stay window-correct. Nullable dims are coalesced to unknown so they stay MergeTree-safe and visible.

Posted GL balances by account, period, and cost center.

One row per (period × gl_account × cost_center) summarising posted journal lines. `net_amount` is the balance computed as sum(debit) - sum(credit); the sign follows the account's normal side. Built only from posted entries (drafts/reversed excluded). The starting point for the trial balance and financial statements.

How much stock sits where, by category.

Per-day inventory rollup by (warehouse, category). Use `unique_skus_below_reorder` for cross-warehouse summaries — averaging `under_reorder_pct` unweighted misleads. Stockouts (`on_hand_qty=0`) are tracked as a count, not a percentage.

Per-lot stock health — quantity, days of cover, turnover, and near-expiry, per client.

One row per inventory lot (grain lot_id), sourced from int_whse__inventory_valued. Carries qty_on_hand, derived days_of_cover and turnover_ratio, a near_expiry_flag (computed against the fixed logistics_as_of horizon), storage zone/bin/status, temp_class/ hazard_class, and the client_id / sku / site_id FKs. status ∈ {available, allocated, quarantine, damaged, expired}. This is the per-lot table the inventory_turnover metric aggregates over; read rows to name which lots are slow-moving or near expiry. Client-scoped via the client_account_manager row policy on client_id.

Every posted/draft GL line with its entry and account.

One row per journal LINE with the entry header (number, date, source, status, creating employee) and the account it posts to. `net_amount` per line is debit - credit. The drill-down behind the GL balances; the creating employee is an actor reference, not a maskable identity column.

Sends, opens, clicks, unsubs — the funnel.

Daily engagement funnel rollup. Sends are gated upstream on the active marketing-consent state at send-time; mid-window consent withdrawals are NOT reflected here. For real compliance reporting, pair with the `consent_state` metric (built on `mart_consent_current`) rather than reading this surface alone.

The rules that route an MDT proposal through review, escalation, and issuance.

One row per routing/approval rule (grain rule_id), grouped by rule_type: escalation_trigger (e.g. high_value at the £250k LOA threshold, new_scope_of_work, unusual_or_high_risk_technical, capex_new_equipment, manual_override), review_path (standard / escalated), issuance_route (uk_front_desk / us_bdm_direct / live_presentation_then_reissue), and requote_reason. sequence_order gives the escalation-ladder / step order.

Which past jobs are comparable to an opportunity — the grounding for ROM benchmarking.

One row per curated similarity edge (grain match_id) between an opportunity and a precedent job reference, with similarity_score (0–100), a prose relevance_note explaining why the precedent matters (method reuse, same client, overlapping test scope), and referenced_in_rfq marking edges the client's own RFQ cited. matched_proposal_id joins the precedent to its proposal so line-item pricing can be re-aggregated.

Where MDT proposals sit by entity, region, study type, and status over time.

The proposal pipeline rollup for the medical-device testing-services domain. Each row is one (issued_month × entity × client_region × study_type × proposal_status) bucket carrying the proposal count, the summed base ROM (ex-VAT ex-contingency), and the average request→issued cycle time. issued_month is the month the proposal was issued, so this is a trend surface. Proposals without an issued_date (drafts / ROM-only quotes) drop out of the issued-month grain.

Cost line items behind each proposal's ROM (BI-only).

One row per pricing line item (grain line_item_id): device_nickname, category, related_test, quantity/rate/amount, and the proposal currency. The amounts sum to the proposal's total_rom. Confidential-commercial — exposed only to BI + commercial personas.

Win/loss/status and profile of every MDT proposal.

One row per proposal (grain proposal_id) from int_mdt__proposal_economics. Carries entity, study_type, client_region, proposal_status, currency, the base ROM (total_rom — confidential-commercial), is_won / is_decided flags, review_path, and the request→issued cycle_time_days. Prefer the aggregate metrics for grouped rates; read rows here to inspect individual proposals.

Per-test rate card (GBP pence) for costing RFQ test requests.

One row per (related_test, category). related_test is the exact-matchable test name — a canonical test family or one of the raw mdt_test_methods.test_name variants (is_canonical=0 alias). canonical_test is the normalized family. unit_rate_minor is the per-unit rate in GBP integer pence; category splits labour / setup / materials. Synthetic reference data (build_rate_card.py); a few families are anchored to observed proposal pricing, the rest are seed-derived within a plausible band. BI + commercial personas only.

The incoming RFP intake queue — what's been requested and where it sits.

One row per incoming Request for Proposal (grain rfp_id) in the intake pipeline (MVD Category 02). Real RFPs (grounded in the package RFQ artifacts, linked to their quoted opportunity) plus synthetic net-new prospects. Carries entity/region/channel, requested device/test scope, target dates, and the intake status (received → qualifying → quoted, or declined / on_hold). Use it to reason about the pre-proposal funnel; for issued-proposal outcomes use query_records('mdt_proposal').

Devices under test with their test-method rollup.

One row per device (grain device_id): device name/type/manufacturer/ active ingredient/variant count, plus test_count, replicate_total, and the distinct standards cited across its tests, joined to the parent opportunity's entity / study_type / region.

The close checklist, one row per period and step.

One row per (period × close_step) exploding the close rollup into the controller's checklist: post_all_entries, trial_balance_ties, reconcile_bank, lock_period. `step_status` is done / pending / blocked, backed by the entry, trial-balance, and reconciliation counts.

Win/loss of the SLA promise for every order — OTIF, cycle time, line accuracy, and value, per client.

One row per order (grain order_id), sourced from int_whse__order_enriched. Carries the SLA outcome trio (is_otif / is_on_time / is_in_full), cycle_time_hrs (order→ship), line_accuracy_pct, order_value, and the client_id / site_id / consignee_id FKs. De-identified — consignee_id is a surrogate, never the recipient's name/address/email. This is the per-order table the fulfillment metric aggregates over; read rows here (via query_records('fulfillment_order')) to name which orders breached SLA. Client-scoped: the client_account_manager row policy restricts rows to the caller's assigned client_id.