Analytics

Overview

Reporting via Metabase backed by Postgres analytics views. Views live in the analytics schema, managed through Prisma migrations. Three dashboards (Owner, Community Manager, Bookkeeping) serve different audiences with shared location and time filters.

Infrastructure

Metabase: http://homelab:3100 — Docker on homelab (/opt/homelab/metabase/).

Two database connections, both to production Supabase Postgres:

Connection	Role	Schema	Purpose
Production DB	`metabase_readonly`	`public`	App data (resources, reservations, transactions, etc.)
Production DB	`metabase_readonly`	`analytics`	Analytics views and materialized views
Stripe	`postgres`	`stripe`	Local Stripe tables, synced from the Stripe API (cron)

The stripe connection uses postgres because metabase_readonly is not granted on the stripe schema (RLS-enabled local tables populated by the sync job). The data is local — no live Stripe API calls — but for dashboards prefer the analytics materialized views (e.g. bookkeeping_stripe_ledger), which pre-join Stripe data for the fast metabase_readonly connection.

The metabase_readonly role has BYPASSRLS — it sees all rows regardless of RLS policies.

Role setup: apps/api/scripts/setup-metabase-role.ts.

Dashboards

Dashboard	Audience	Tabs
Owner	Aaron / leadership	Monthly Studios, Scorecard, Tours, Hourly Studios, Insurance
Community Mgr	Location staff	Occupancy, utilization, tour pipeline, alerts
Bookkeeping	Finance / Aaron / CB Solutions (main accounting firm, all locations)	Revenue, Money Movements, Transactions, Receivables (dashboard id 18) — CB Solutions works in this dashboard directly
Stripe Bookkeeping (COR Accounting)	COR Accounting (smaller firm, MG4/5/6/8/9 subset)	Location-scoped parity variant of Bookkeeping (dashboard id 34): Revenue, Balances, Transactions; locked to MG4/5/6/8/9; routine card-query changes sync in place via `sync-bookkeeping-clone.py`, id only churns on a full structural re-clone

All share global controls: location multi-select and time range. Dashboard 34 bakes the location filter into every card so it can't be bypassed via the dropdown. Dashboard creation automated via Python scripts using the Metabase API (analytics/scripts/).

Two firms, two dashboards. CB Solutions (main firm, all locations) works in dashboard 18 directly; COR Accounting (smaller firm, subset of buildings) uses dashboard 34, whose MG4/5/6/8/9 lock implements that subset. See [[features/bookkeeping/dashboard]] for full detail.

Location filter format (gotcha). Analytics views expose location_name as the prefixed form mg_id || ' ' || name (e.g. MG10 Cherry City), and every card maps the location_param filter to its view's location_name. The filter's value list must therefore also be the prefixed form, or string-equality matches nothing and every filtered card goes blank. Source the Owner dashboard's location_param values from the connected fields (the mapped location_name columns), not from public.locations.name (plain Cherry City). This bit the whole dashboard once (2026-06-25): the filter was sourced from plain locations.name while the views had moved to the prefixed form, so selecting any single location returned zero rows on every tab.

Analytics Views

Views in the analytics schema, created via Prisma migrations.

Owner/CM Dashboard Views (from app data)

View	Type	Source Tables	Purpose
`subscription_months`	VIEW	reservations, resources, locations	MRR trend — one row per active sub per month
`subscription_events`	VIEW	reservations, resources, locations	MRR waterfall — new/reactivation/cancelled
`mrr_monthly`	VIEW	subscription_events	Monthly MRR aggregation
`churn_monthly`	VIEW	reservations, locations	Subscriber and revenue churn rates by month
`occupancy_monthly`	VIEW	resources, reservations, waitlists, locations	Occupied/total/waitlist/insured counts monthly
`active_subscriptions`	VIEW	reservations, resources, users, locations	Current snapshot — tenure, insurance status
`upcoming_cancellations`	VIEW	reservations, resources, locations	Subscriptions with pending cancellation
`tour_events`	VIEW	reservations, users, locations	One row per tour — completion, conversion, no-show
`tour_pipeline`	VIEW	reservations, resources, locations	Weekly tour goals and pacing by location
`insurance_monthly`	VIEW	reservations, resources, locations	Insurance revenue and penetration monthly
`insurance_by_tier`	VIEW	reservations, resources, locations	Insurance breakdown by price tier
`insurance_coverage_activity`	VIEW	reservations, resources, locations	Insured-unit roster by month (carrier filing)
`hourly_bookings`	VIEW	reservations, credit_transfers, locations	Hourly revenue trend and booking counts
`hourly_revenue_by_source`	VIEW	reservations, credit_transfers, transactions	Revenue by type (credit sub, onetime, direct)
`utilization_daily`	VIEW	reservations, resource_availability_cache	Daily utilization % for hourly studios
`booking_heatmap`	VIEW	reservations	Hour × day-of-week grid (zero-filled 24×7)
`booking_hours`	VIEW	reservations	Booking volume by day of week

Bookkeeping Views (from Stripe + app data)

View	Type	Source	Purpose
`bookkeeping_stripe_ledger`	MAT	stripe.invoices, stripe.charges	Raw Stripe transaction ledger: `posted_date` (from the balance transaction's `available_on`) and invoice-line `service_period_start`/`service_period_end` (from the invoice line's `period`) since ADR-027 phase 1 (L1)
`bookkeeping_attribution_adjudications`	TABLE	staff-written, keyed on `charge_id`	Frozen human/ruling attribution overlay for charges evidence alone can't classify; L2 consults it before falling back to `unattributed`. ADR-027 phase 1
`bookkeeping_attributed_ledger`	VIEW	stripe_ledger + reservations/users + adjudications	Ledger attributed to locations/resources; includes `transferred_cents`/`platform_fee_cents` and, since ADR-027 phase 1, `posted_date`/`service_period_start`/`service_period_end`/`service_month`/`report_category` (L2)
`bookkeeping_revenue_summary`	VIEW	attributed_ledger	Revenue by type, daily and monthly grain; location rows show cash-basis (transfers in); Metrognome row shows charge-basis (L3)
`bookkeeping_transfers_by_location`	VIEW	attributed_ledger	Revenue transfers grouped by location; Part C synthesizes rows from transfers with no Payment FK (L3)
`bookkeeping_transactions`	VIEW	attributed_ledger	Row-level detail of every charge/refund/transfer/payout, excludes `is_synthesized` rows to avoid double counting against `bookkeeping_transfers_by_location` (L3); gains `failure_code`/`failure_message`/`expected_debit_date` in migration `20260717180000` (PR #899), `posted_date`/`service_month`/`report_category` in ADR-027 phase 1; backs the Revenue tab's All Transactions table
`bookkeeping_transfers`	VIEW	attributed_ledger	Row-level transfer/`transfer_refund` detail — destination location, `purchase_type` (from transfer metadata), `transfer_source_charge_id`; `amount_cents` stays platform-signed (negative = cash leaving MG0) (L3); backs the Balances tab's All Transfers table
`bookkeeping_mg0_balance`	VIEW	stripe_ledger	Platform Stripe account cash position — `SUM(net_cents)` split available/pending, `latest_activity_date` (L3)
`bookkeeping_connect_balances`	VIEW	connect_balance_snapshots + locations	Latest daily Stripe Connect balance snapshot per location (available/pending/balance); PR #900. Values run near zero because Connect accounts auto-pay-out daily, so this shows in-transit money, not a standing balance
`bookkeeping_liabilities`	VIEW	waitlists, user_credit_balances, attributed_ledger, transfer_retries	Obligation-side lens — "what does Metrognome owe right now?" across 6 liability categories. `pending_waitlist_deposit` and `unredeemed_credits` gained age/origin scoping in ADR-027 phase 1, see below
`bookkeeping_recognition_events`	VIEW	waitlists, user_credit_balances, transactions	Dated accrual events for deferred revenue types (deposit redemption, deposit breakage, purchased-credit redemption/reversal/adjustment, credit breakage); derived, never stored (ADR-025). ADR-027 phase 1
`bookkeeping_settlements`	VIEW	attributed_ledger	Execution-dated money-movement rows named by backing record (payouts, transfers, reversals, fees, surcharge withholds); "what moved this month." ADR-027 phase 1
`bookkeeping_receivables`	VIEW	stripe_ledger, attributed_ledger, stripe.invoices/charges, writeoffs	One row per unpaid member obligation (ADR-029): bounced charges without a successful retry plus never-succeeded invoices, `kind` bounced\|never_succeeded, `status` open\|written_off, aging. Cleared rows simply stop appearing
`bookkeeping_receivable_writeoffs`	TABLE	staff tooling	Case-by-case bad-debt rulings (`bookkeeping.ts writeoff-receivable`); overlay of judgment like the attribution adjudications, keyed on `charge_id`
`bookkeeping_ar_rollforward`	VIEW	stripe.invoices/charges, stripe_ledger, writeoffs	Monthly A/R movement (arisen/collected/written-off, cumulative closing gate-tied to open receivables); universe = every invoice that ever had a failed attempt, incl. recovered
`bookkeeping_deferral_rollforward`	VIEW	waitlists, transactions, recognition_events, stripe.refunds	Per month and deferral type: opening + new - released - breakage = closing, at face value; backs the Payable tab's rollforward card (replaced the bridge). Closings tie to outstanding obligations, gate-enforced
`bookkeeping_receivable_invoice_lines`	VIEW	stripe.invoices/charges, payments, reservations	Never-succeeded invoice lines (service date from line period, Monthly/Insurance folding) so the Metabase model can synthesize their revenue - `metabase_readonly` cannot read the raw `stripe` schema, the view runs with owner rights
`bookkeeping_credit_activity`	VIEW	credit_purchases, balances, transfers	Credit purchase/spend/expiry activity
`bookkeeping_credit_balances`	VIEW	user_credit_balances, users	Current credit balance snapshot
`bookkeeping_waitlist_balance`	VIEW	waitlists, transactions	Waitlist deposit status and transfers
`bookkeeping_refunds`	VIEW	stripe_ledger	Refund transactions
`bookkeeping_failed_transactions`	VIEW	stripe_ledger	Failed charge details
`bookkeeping_migration_remaining`	VIEW	migration_invitations/submissions	Outstanding migration progress

Bookkeeping attribution chain (bookkeeping_attributed_ledger):

The L2 view is the core attribution layer. It has three parts:

Part A — charges with a Payment FK: attributes via the Payment→Reservation→Resource→Location path. Transfer-typed rows (source charge has a Payment FK) pass through Part A and count in transfers_by_location only.
Part B — charges without a Payment FK but with a direct reservation link: attributes via invoice.reservation_id.
Part C — transfers whose source charge has no Payment FK: synthesizes a charge-typed row with gross_cents/fee_cents/net_cents derived from the source charge totals, falling back to the transfer amount for unsourced transfers. Covers legacy waitlist deposits and pre-migration member transfers that landed in Connect accounts before the app existed. Each transfer appears in exactly one of {revenue_summary, transfers_by_location} — no double-counting.

The L2 view exposes an explicit is_synthesized column: transaction_type = 'charge' AND transfer_destination IS NOT NULL — i.e. a Part C synthesized row (charge-shaped revenue derived from a transfer, charge_id NULL), representing money already sent to a location. Consumers that reason about untransferred obligations must exclude these. The stripe-reconcile-balance obligations query filters transaction_type IN ('charge','payment') and now adds AND NOT bal.is_synthesized; without it, already-paid transfers were counted as outstanding location obligations (a phantom $5,816 / 83 charges). The column was appended via CREATE OR REPLACE; dependent L3 views are unaffected.

waitlist_redemption revenue type. A member who joined the waitlist and paid a deposit gets that deposit credited against their first month's invoice. Stripe represents this as two lines on the subscription_create invoice: a full-price monthly line and a negative credit line for the deposit. Part A used to label both lines monthly, netting the invoice down to the discounted cash amount actually collected. It now classifies the negative line as waitlist_redemption when it's on a subscription_create invoice and abs(gross_cents) exactly matches the subscription metadata's waitlistDepositApplied — so the monthly line reports at full non-discounted price and the redemption shows as its own negative line. Cash totals (net_cents, transferred_cents) are unchanged; only the revenue-type split changes. Historical fulfillments applied via Stripe customer balance (pre-dating per-invoice deposit application) have no invoice line to reclassify and keep their original monthly/waitlist_deposit treatment.

Two-perspective cash model:

bookkeeping_revenue_summary presents two perspectives in one table, distinguished by location_name:

Metrognome (platform perspective) — aggregates charges and refunds. Shows gross customer charges, Stripe fees, net to platform balance.
Per-location rows (Connect account perspective) — aggregate transfers in (transfer rows) and refunds out (transfer_refund rows), sign-flipped so inflows are positive. Stripe Fees and Platform Fee are 0/NULL for location rows (those deductions happen on the platform side). Gross/Net/Transferred collapse to the same number for location rows.

A $300 charge that generates a $290 transfer contributes $300 to the Metrognome row or $290 to the location row, not both.

transferred_cents and platform_fee_cents:

Both columns appear on bookkeeping_attributed_ledger and bookkeeping_revenue_summary. transferred_cents is the cash that landed in the location's Connect account — the number bookkeepers reconcile against. platform_fee_cents is derived as net_cents − transferred_cents per row (Metrognome billing surcharge). For credit transfers, credit_transfers.platform_fee_cents supplies the value directly via COALESCE precedence.

Daily grain and accountant detail views. bookkeeping_revenue_summary groups by event_date in addition to month_start — the monthly cards keep working off month_start, and a daily card/filter can group off event_date instead. bookkeeping_transactions and bookkeeping_transfers are row-level pass-throughs of bookkeeping_attributed_ledger built for the accountant's "show me every line" requests rather than for dashboard aggregation: bookkeeping_transactions is every non-synthesized charge/refund/transfer/payout row, scoped on the dashboard to customer-facing types (charge/payment/refund/payment_refund/payment_failure_refund); bookkeeping_transfers is just the transfer/transfer_refund rows with purchase_type pulled from transfer_metadata. bookkeeping_mg0_balance sits outside the attribution chain entirely — it sums net_cents straight off bookkeeping_stripe_ledger (available/pending split, latest_activity_date) to answer "what's the platform Stripe account's cash position," so it's only as fresh as the last materialized view refresh (see the daily refresh cron below).

Bookkeeping Dashboard tab restructure (2026-07-17). The dashboard's tabs don't map 1:1 to the views above. It went through three rounds the same day: 8 tabs, then 6 (Revenue, Memberships, Transactions, Transfers, Credits, MG0 Balance), then a final consolidation to 3 tabs: Revenue, Memberships, Balances. create-bookkeeping-dashboard.py builds the Revenue tab: six plain scalars over the filtered period (Gross Revenue, Waitlist Discounts, Stripe Transaction Fees, Net Revenue, Stripe Platform Fees, Transferred to Location, an additive chain that reconciles penny-exact with the pivot), the Revenue by Location pivot, and the All Transactions detail table (bookkeeping_transactions), all querying bookkeeping_attributed_ledger directly rather than bookkeeping_revenue_summary. add-bookkeeping-memberships-tab.py builds Memberships: one row per succeeded subscription invoice, no dedicated view, querying bookkeeping_attributed_ledger joined to users, filtered to succeeded subscription-invoice charges (excluding credit-purchase invoices), with billing_reason mapped to a signup/renewal/change/rebill Type column. add-bookkeeping-balance-tab.py builds Balances (renamed from MG0 Balance): platform cash position, waitlist deposit holdings, holder-level credit balances and the Metrognome credit-fee scalar, bookkeeping_connect_balances (PR #900), and the transfer count/amount scalars plus the All Transfers detail table (bookkeeping_transfers); everything that isn't customer-facing revenue lands here. add-bookkeeping-{transactions,transfers,credits}-tab.py were deleted in the final consolidation (their content folded into Revenue/Balances); add-bookkeeping-{waitlist,refunds,failed}-tab.py were deleted in the earlier 8-to-6-tab restructure. Insurance section cards, Migrations Remaining cards (a stale 5-row Yardi count; the migration program completed June 2026), and the units-denominated Credits activity view (sold/redeemed counts, flow chart: dollar flows already cover the same ground via Transactions/Transfers/Balances) were dropped outright.

The Revenue tab's six scalars and pivot widened their transaction_type filter from charge/payment to also include payment_failure_refund, refund, payment_refund, so a bounced ACH charge now nets against its own reversal instead of reading as uncorrected revenue. The pivot gained a Grain (temporal-unit, default month) parameter, retargeting its date breakout from month_start to event_date so a day-grain view has something to bin against, and split its former Platform Fee column into Stripe Platform Fee (renamed) and a new MG0 Credit Fee column (the credit_redemption_transfer platform-fee cut, previously excluded from the pivot entirely). Balances — now labeled Money Movements on the staff dashboard, still Balances on the clone, since tab titles aren't part of the clone sync's scope — gained a shared MG0 Balance Breakdown card (native SQL, id 1415): the MG0 Stripe balance partitioned into liability-type obligations (waitlist deposits, unredeemed credits at payout basis, parked deposits, untransferred charges) and an unrestricted remainder, with the credits line sourced from analytics.platform_events so it flips to purchased-origin-only automatically once PR #917 (ADR-026) ships.

A further round of live edits renamed and repacked the Revenue tab. Net Revenue is now Net Available and Transferred to Location is now Net Transferred (both the scalar cards — staff 967/969, clone 1394/1396 — and the matching pivot columns), and a new MG0 Credit Fees scalar (staff card 1416, clone 1417, mapped in sync-bookkeeping-clone.py's CARD_MAP) sits on the Revenue tab: the same 10% credit-redemption cut as the pivot's MG0 Credit Fee column and the Money Movements tab's Metrognome Credit Fees card, aggregation-coalesced to 0 so dashboard 34 reads $0.00 instead of blank. Net Transferred now includes credit-redemption payouts: transferred_cents is NULL on credit_redemption_transfer rows, so both the scalar and the pivot column sum abs(gross_cents) for those rows instead, leaving every other row unchanged — the Net Available − Stripe Platform Fees − MG0 Credit Fees = Net Transferred chain only holds for periods with no credit redemptions. Both dashboards are now set to full width, and every scalar card on both was resized from height 4 to height 3 with rows repacked. See [[features/bookkeeping/dashboard]] for the full column table and row layout.

Tab reshuffle: Memberships out, Transactions its own tab (through 2026-07-23). Dashboard 18 is back to 3 tabs but with different contents: Revenue, Money Movements, Transactions (a fourth tab, Receivables, joined at the 2026-07-24 ADR-029 deploy - see [[features/bookkeeping/dashboard]]). The Memberships tab (built by add-bookkeeping-memberships-tab.py) is gone from both dashboards — it's retired, not run anywhere live, though the script is still in the repo. The All Transactions table moved out of the Revenue tab into its own new Transactions tab, which carries the same Location/Category/Date filter mappings. A new Category filter (revenue_type_param, multi-select, static value/label list) is wired to the pivot, all seven Revenue scalars, and All Transactions on both dashboards; the pivot's Report Type case (and the new category charts below) fold waitlist_redemption and — newly, Aaron-approved — prorated rows into a Monthly display category, but the raw revenue_type values are untouched and the Category filter can still isolate either one individually. The Location filter's trailing Metrognome entry is now labeled MG0 Platform and moved to the top of the list (a live Metabase edit — the script's natural_location_values() sort still puts it last; re-running the script loses the reorder).

Below the Revenue pivot, two new native-SQL cards: Revenue by Category Grand Total (pie, staff 1418 / clone 1420) and Revenue by Category Over Time (stacked bar, month grain, staff 1419 / clone 1421). Both use a CASE {{measure}} template variable driven by a single-select "Measure" filter (default Gross, inline-anchored next to a "Revenue by Category" heading rather than in the top filter bar — Units was dropped from the Measure list because the shared value column can only carry one Metabase number format and every other amount on the tab is USD currency). They respect Location/Category/Date but not Grain. Refunds and payment reversals net into the original charge's category via a LATERAL lookup (refund_original_charge_id, or source_id for payment_failure_refund; 100% linkage), so the pie's total reconciles to the Gross Revenue scalar. Dashboard 34's copies have the MG4-9 lock baked into their own SQL and can't be auto-synced (native SQL isn't sync-bookkeeping-clone.py's pMBQL-stages shape) — they print as unmapped and need a manual copy.

Routine clone maintenance no longer re-clones: analytics/scripts/sync-bookkeeping-clone.py syncs card dataset_query from Bookkeeping (dash 18) to dashboard 34 in place via an explicit CARD_MAP, preserving dashboard 34's location-lock filter clauses (structural comparison stripping volatile lib/uuid keys) and never touching visualization_settings. Cards whose dataset_query isn't the pMBQL-stages shape are skipped with an explicit message instead of being overwritten wholesale. create-bookkeeping-accountant-dashboard.py (archive-and-recreate, dashboard id churns) remains only for structural changes — a new tab, a new card, or a source-view column change.

See [[features/bookkeeping/dashboard]] for the full tab-by-tab spec, including dashboard 34 — COR Accounting's location-scoped variant (MG4/5/6/8/9). CB Solutions, the main firm, works in dashboard 18 directly.

Fee sign convention is presentation-level, not stored. fee_cents and platform_fee_cents are stored as positive magnitudes on every bookkeeping view; no view encodes a display sign. Individual Metabase cards choose whether to negate them. The Revenue tab's scalar cards and the Revenue by Location pivot's Stripe Fees / Stripe Platform Fee / MG0 Credit Fee columns all multiply by -1 so both surfaces read as an additive chain (Gross + Waitlist Discount + (−Fees) = Net). The raw Transactions and Transfers detail tables keep Stripe's native positive-fee convention because they are the audit trail.

Obligation-side view (bookkeeping_liabilities):

The balance-math chain (bookkeeping_stripe_ledger → bookkeeping_attributed_ledger → bookkeeping_revenue_summary) decomposes the Stripe balance by revenue type, but it nets receipts against fulfillments and so can't answer "held for whom, and how much do we owe." bookkeeping_liabilities is a separate, independent lens for that question — one row per obligation, UNION ALL across six liability_type values:

`liability_type`	What it is	Amount basis
`pending_waitlist_deposit`	Deposit still on-platform, entry open (`ACTIVE`/`INVITED`), no transfer fired	Net deposit
`location_held_waitlist_deposit`	Deposit already transferred to the location while the entry is still open (pre-PR-401 optimistic transfers) — contingent: cash is off-platform, but a cancel still refunds from the platform balance	Net deposit
`parked_fulfilled_deposit`	Entry `FULFILLED` but the deposit transfer never fired (the MG-API-54 class; self-heals at renewal per #837)	Net deposit
`unredeemed_credits`	Unexpired `user_credit_balances` rows (`balance > 0`, not yet expired)	Face is 1 credit = $1; `amount_cents` is the payout after the `credit_transfer` platform fee
`untransferred_lockout_charge`	Succeeded lockout/prorated charges, post the 2026-02-01 per-charge cutoff, with no `source_transaction`-linked transfer in `bookkeeping_stripe_ledger`	Whole-charge net (all line items of the charge, not just monthly/prorated ones) minus the `stripe_billing` surcharge the transfer path withholds
`untransferred_dedicated_room_charge`	Same qualifying condition, but the invoice traces to an organization-dedicated-room `Payment` (`payments.organization_id` set)	Whole-charge net, no surcharge deducted — the `stripe_billing` surcharge is lockout-gated in `StripeWebhookService`, so dedicated-room transfers move full net

Two columns carry the dollar figures on every row: amount_cents is the cash obligation (net/payout basis — what the platform actually owes out), face_value_cents is the customer-facing value (gross charge, or credit face). The two untransferred_*_charge types additionally surface retry_status from the transfer_retries queue where a retry row exists for that charge. Both types share one query (charge-level aggregation over bookkeeping_attributed_ledger; both classify as monthly in L2) and split only on whether the charge's invoice traces to an org-owned Payment — getting that branch wrong understates an org charge's liability by a surcharge it was never actually charged.

Scope is deliberately narrower than "everything owed": refund-path liabilities and location-side AR are out per the 2026-04-21 decision, and hourly direct charges are excluded entirely — their transfers are immediate (transfer_data on the PaymentIntent, see [[stripe/transfers]]), and failures land in transfer_retries, not a standing liability bucket.

Both untransferred_*_charge types aggregate at the charge level, not the ledger-line level: bookkeeping_attributed_ledger expands one invoice charge into multiple line items, but the transfer path moves the whole charge's net in one shot, so the view sums every line of a charge and qualifies the charge if any line is monthly/prorated. It also excludes Part C synthesized rows (bal.is_synthesized) — see the is_synthesized note above — since those represent transfers already paid out, not outstanding obligations.

Refund handling differs by bucket. For the untransferred_*_charge types, a partial refund reduces amount_cents by the refunded amount (floored at zero; the surcharge still applies to the original gross, matching what the transfer path withholds) and only fully-refunded charges drop out; face_value_cents stays the original gross. Waitlist deposit buckets exclude on any succeeded refund by design: deposit refunds are all-or-nothing (gross minus the kept fee), so a refunded deposit carries no residual obligation.

This view depends on the L1/L2 ledger views. A future migration that rebuilds bookkeeping_stripe_ledger with DROP ... CASCADE must recreate bookkeeping_liabilities too (same contract as the other L3 views).

apps/api/scripts/stripe-reconcile-balance.ts's obligations section reads this view directly (per-liability_type totals with aging via days_aged) instead of the ad hoc per-category queries it used before. It warns in two tiers: on-platform liabilities (total minus the off-platform location_held_waitlist_deposit subtotal) exceeding the Stripe balance including pending is a real funding gap; exceeding only the available balance is a liquidity risk that resolves once pending funds settle.

Fixed alongside: bookkeeping_waitlist_balance and bookkeeping-verify-attributed-ledger.ts's ledger cross-check both filtered waitlists.status = 'PENDING' — dead since migration 20260611085014 backfilled legacy PENDING rows into ACTIVE/INVITED. Both now filter status IN ('ACTIVE', 'INVITED').

Service-period ledger migration (ADR-027 phase 1, 2026-07-23). Four migrations (20260723200000 through 20260723230000, plus a same-day dedupe fix at 20260723240000) add the columns and views a service-period revenue lens needs, on top of the existing cash-basis chain. Deployed via PR #919 (plus #920 for the dedupe fix), verified on prod with the checks below.

L1 dates. bookkeeping_stripe_ledger gains posted_date (the balance transaction's available_on, converted from its Unix timestamp) on both UNION branches, and invoice-line service_period_start/service_period_end (from Stripe's line.period.start/end) on the invoice branch only; branch 1 (bare charges, no invoice) carries them NULL. A new bookkeeping_stripe_ledger_posted_date index backs it.
L2 service_month and report_category. bookkeeping_attributed_ledger passes the three dates through and derives service_month (UTC, matching the event_date convention): the invoice line's period-start month for invoice-backed rows, the reservation's slot month for hourly-direct rows (via the existing reservation join), else the event month. report_category is the single display folding shared by every consuming surface: monthly, waitlist_redemption, and prorated all read as Monthly; insurance maps to Insurance; hourly_direct maps to Hourly; credit_purchase maps to Credit Purchase; waitlist_deposit maps to Waitlist Deposit; payment_failure_reversal maps to Payment Reversal; refund maps to Refund; unattributed maps to Unattributed; anything else passes through verbatim (a single test_charge-labeled adjudication row, for example, stays test_charge). This mirrors, and is meant to eventually replace, the Report Type CASE expression hand-maintained in the Metabase pivot; see [[features/bookkeeping/dashboard]]. Not yet fully in sync: the pivot's live Report Type case picked up a test_charge to "Test Charge" label on 2026-07-23 that report_category doesn't have yet; see the phase-2 rider in [[features/service-period-recognition/plan]].
Attribution overlay. analytics.bookkeeping_attribution_adjudications is a plain table (RLS-free, same posture as analytics.timeline_adjudications) keyed on charge_id: revenue_type, location_name, source (evidence | ruling), note, adjudicated_by, adjudicated_at. It's an overlay of judgment, never a mutation of the base charge, written only by staff tooling. L2's revenue-type CASE now falls through to COALESCE(adjudications.revenue_type, 'unattributed') instead of landing on unattributed directly, and the adjudication's location_name sits at the tail of the location COALESCE too (deliberate: a human ruling can supply location evidence couldn't). apps/api/scripts/bookkeeping.ts seed-attribution-adjudications idempotently upserts from apps/api/scripts/seed-data/legacy-attribution-adjudications.json (81 frozen legacy charge ids, --production gated, dry-run first); running it on prod took the unattributed bucket to $0.
Topup classification. transaction_type = 'topup' now classifies as revenue_type = 'topup' instead of falling to unattributed.
ACH-retry double-counting fix (PR #920). The L2 payments joins (inv_p, pi_p) were bare joins that fanned out when an invoice had both a FAILED and a later PAID Payment row (an ACH retry): six balance transactions (all May 2026 ACH-retry invoices) doubled in the ledger. Both joins are now LEFT JOIN LATERAL ... ORDER BY (status = 'PAID') DESC, created_at DESC LIMIT 1, preferring the paid row then the latest, matching the pre-existing sub_p pattern. Pre-existing defect (dates to the original L2 build), surfaced by the PR #919 verification gate, not introduced by it.
bookkeeping_recognition_events. Dated accrual events for revenue the cash ledger can't carry a date for on its own; derived, never stored (ADR-025's cash/accrual boundary). One row per: deposit_redemption (movements-side marker when a waitlist deposit applies to the first invoice; the revenue itself is still the invoice's full-price line); income_forfeiture / waitlist_deposit (deposit breakage at the 12-month anniversary of payment, per Paul's 2026-07-22 ruling; refunded deposits and transferred deposits never forfeit, since the location already holds transferred cash); credit_redemption / credit_redemption_reversal / credit_adjustment (purchased-credit-origin only; redemption dates at the booking's slot date, gross is the redeemed value, the 10% MG0 cut lands in platform_fee_cents); income_forfeiture / credit_purchase (purchased-credit remainders at expires_at, 18-month policy). Promo-origin credits and comps produce no rows at all, per the final 2026-07-23 no-money-movement ruling (ADR-028): no cash event, no row to date. Vocabulary (income_forfeiture, etc.) is shared with analytics.platform_events, one vocabulary, never two.
bookkeeping_settlements. Execution-dated movement rows, one lens for "what moved this month," named by backing record: credit_redemption_payout (transfer matched to credit_transfers; purchased-origin going forward, grandfathered promo-origin payouts already executed stay listed, since the view records what actually moved), deposit_payout (transfer matched to a waitlist entry), charge_transfer (transfer carrying transfer_source_charge_id), standalone_transfer (destination-only, no source charge; manual-era batches, remediation), transfer_reversal/stripe_fee/topup/bank_payout (their balance-transaction types), and surcharge_withhold (per source charge, net minus transferred when positive; the 0.7% billing surcharge retained on lockout settlements, dated at the charge's first transfer, feeds the withheld-vs-billed recoupment check). amount_cents is the raw balance-transaction gross (money out of MG0 is negative) except surcharge_withhold, which is the positive amount retained. Known gap: transfer_reversal rows carry no location or source charge; Stripe's reversal object id (trr_...) doesn't resolve back to the originating tr_... through any mirror table, so the join structurally misses it (inherited, not new).
Liabilities scope change. bookkeeping_liabilities's pending_waitlist_deposit bucket now excludes deposits past their 12-month anniversary, since those are recognized breakage (see bookkeeping_recognition_events above), not a standing obligation. unredeemed_credits now filters to purchased-origin balances only (credit_package_id IS NOT NULL); promo credits carry no cash obligation under the final 2026-07-23 ruling. Same 6 liability_type categories, same columns; CREATE OR REPLACE.
Verification gate. apps/api/scripts/bookkeeping.ts verify-service-period runs read-only checks (25 pass/fail as of the phase-3 close: deferral + A/R rollforward ties, cash-bucket coverage, MG0 balance tie): L1 drift bounded by per-line rounding, L1 covers every balance transaction, L2 preserves L1 cash totals (slot-rounding bounded), service_month/posted_date populated on every charge row, unattributed bucket empty, purchased-credit unit-ledger balances reconcile, every unrefunded deposit is explained, no post-expiry activity on expired purchased balances, an invoice-line >35-day canary (expected ~1 on prod, a real long line, not a bug), and the ADR-029 receivables checks: bounce completeness (every bounce is recovered, open, or written off - never none, never two), failed-only invoice coverage, recovered-bounce takeover stability (full-amount contras, retries cover the same service months), and the restatement disclosure (lifetime-earned delta equals kept contras plus synthetic failed-invoice revenue minus writeoffs; open A/R printed). All checks passed on prod after the overlay seed and the #920 dedupe fix.
What's still cash-basis. The Metabase dashboards (Revenue/Money Movements/Transactions tabs) are not yet wired to service_month/report_category/the new views; that's phase 2-3 (Revenue tab re-base, a settlements tab, a bridge card) and hasn't shipped. analytics/scripts/configure-metabase-metadata.py's EXPECTED_TABLES list and [[features/bookkeeping/dashboard]] still reflect the pre-migration surface; don't assume the live dashboard reads these columns yet.

Location name normalization:

All analytics views use COALESCE(l.mg_id || ' ' || l.name, l.name, 'Metrognome') as the location_name expression. The three-level COALESCE prefers the MG-prefixed form (e.g., "MG10 Cherry City"), falls back to bare name if mg_id is missing, then "Metrognome" when no location row exists. Aggregating views also include l.mg_id in their GROUP BY. This gives the Metabase Location filter a single canonical entry per location.

bookkeeping_stripe_ledger is the only MATERIALIZED VIEW — requires refresh after Stripe data changes. Refreshed daily by /cron/sync-stripe-and-refresh-views. Manual refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.bookkeeping_stripe_ledger. The matview exists specifically to avoid repeatedly re-joining the Stripe tables across the view chain at query time.

The same cron also snapshots Connect account balances (snapshotConnectBalances in apps/api/src/lib/connect-balance-snapshots.ts, PR #900): after the Stripe sync and matview refresh steps, it calls stripe.balance.retrieve per location's Connect account and writes one row per location to connect_balance_snapshots. analytics.bookkeeping_connect_balances exposes the latest snapshot per location. Per-location fetch failures are caught and captureError'd, then skipped, so one location's Stripe error doesn't block the snapshot for the rest.

The stripe ledger includes failure_code, failure_message, expected_debit_date (for pending ACH), and payment_method_display (e.g. "Visa ····4242") — these flow through the full view chain and are exposed on bookkeeping_failed_transactions.

Prospecting Views

prospecting schema: business_summary, market_scores, band_density, metro_band_summary — location market analysis for expansion planning.

Ad-Attribution Pipeline

The ad-attribution pipeline records which ad campaign, UTM parameters, or click identifier drove each conversion. This is separate from the bookkeeping attribution chain above — the bookkeeping chain attributes Stripe charges to locations/resources; the ad-attribution pipeline attributes conversions to ad campaigns.

Data model

conversion_attributions table (in public schema). One row per conversion event. Each row links a sourceType + sourceId pair to whatever ad signal was present at conversion time.

Column	Source
`sourceType`	INQUIRY / TOUR / HOURLY / MONTHLY / WAITLIST / PROMO_REDEMPTION / CREDIT_PURCHASE
`sourceId`	PK of the source record (inquiry.id, reservation.id, etc.)
`utm_*`	UTM parameters from the URL at ad click
`fbclid`	Facebook click ID from URL or `_fbc` cookie
`gclid`	Google click ID from URL
`fbp`	Meta Pixel browser ID (cookie-only; not a campaign signal)
`fbc`	Meta click ID cookie (derived from `_fbc`)

fbp and fbc are stored but do not count as real ad attribution. The hasRealAdAttribution predicate (in AttributionService.ts) requires at least one of: UTM source/medium/campaign/term/content, fbclid, or gclid. Rows with only fbp/fbc are not written — fbp is set by the Meta Pixel on any pageview and carries no campaign signal.

MMS event codes (e.g. MMS-TENT-2026) ride on utm_content rather than a dedicated sourceCode column. The sourceCode field was designed but never applied to prod and was dropped before reaching production.

Pipeline stages

Stage 1 — Frontend capture. UTMCapture in app/layout.tsx reads URL params on mount and writes them to sessionStorage (24-hour TTL). If fbclid is in the URL it also writes the _fbc cookie (90-day TTL). getStoredAttribution() in apps/web/src/lib/utm-storage.ts merges sessionStorage + cookies and falls back to parsing fbclid from the _fbc cookie when sessionStorage has expired.

Stage 2 — Checkout initialization. Checkout routes (initialize-lockout, initialize-hourly, initialize-credits, initialize-waitlist) call buildStripeAttributionMetadata(attribution) and spread the result into Stripe PaymentIntent / SetupIntent / Subscription metadata. This travels with the payment object through the Stripe event lifecycle.

Stage 3 — Webhook fulfillment. FulfillmentService and PaymentIntentHandler call writeOrCarryForwardAttribution(tx, key, sourceType, sourceId, stripeMetadata) after payment succeeds.

Stage 4 — Direct writers. Some conversion events bypass Stripe fulfillment and write directly:

/api/contact, /api/leads/email-capture → INQUIRY
/api/promo/redeem → PROMO_REDEMPTION
/api/comp/reservations → varies
ReservationCreationService.createTourReservation → TOUR

Write logic (`AttributionService.ts`)

writeOrCarryForwardAttribution implements a three-step resolution:

Migration user check. If userId has a migration_submissions row, skip the write entirely and emit an info-level Sentry signal (reason: migration_user). Migration-origin users are not ad-attributed.
Direct write. If Stripe metadata contains real ad attribution (hasRealAdAttribution — UTMs or a click ID), write the row directly.
Carry-forward. If metadata has only fbp/fbc or nothing, fall through to carryForwardAttribution. This queries conversion_attributions for the most recent row tied to any of the user's INQUIRY / TOUR / HOURLY / MONTHLY / WAITLIST / PROMO_REDEMPTION / CREDIT_PURCHASE sources that has real ad attribution (findPriorAttributionForUser). If found, copy it to the new source. If not, fall back to findMetaLeadAttributionForUser — off-site Meta Instant Form leads never create an Inquiry, so the prior-attribution query misses them; this matches the converting user to a persisted meta_leads row by email/phone (normalized: normalizeEmail, normalizePhoneToE164 plus 10-digit / +1 / formatted variants) and synthesizes a Meta attribution (utmSource: fb, utmMedium: paid, utmContent: instant-form:<formName>, source tag meta-instant-form-lead). If still nothing but user.fbclidFirstTouch is set, write a minimal row from that; same for user.gclidFirstTouch (fbclid rescue wins when both are set). If nothing — emit a warning-level Sentry signal (reason: no_prior_attribution) and return. Real on-site prior attribution always wins over a synthesized lead attribution; migration users are still skipped. Precedence: prior attribution → meta-lead match → fbclid rescue → gclid rescue → CM-reported source.

First-touch click IDs are persisted on the User row (fbclidFirstTouch/fbclidFirstTouchAt, gclidFirstTouch/gclidFirstTouchAt) via POST /api/user/attribution at signup/login (first-touch-only: each column writes only while null) and via the tour quick-signup stub-user path.

The carry-forward has no lookback age limit; it uses the most recent real attribution regardless of age.

All four Sentry signal points share tags: { service: 'attribution', operation, reason, sourceType }. The no_prior_attribution warning includes stripeRef in its extras so a future agent can cross-reference raw Stripe metadata.

AdConversionAlert emails

fireAdConversionAlert in AttributionService.ts calls EmailService.sendAdConversionAlert after every successful write. The email includes:

Attribution data (UTMs / click IDs)
attributionSource tag (AdAttributionSource): direct | prior-attribution-carry-forward | meta-instant-form-lead | user-fbclid-rescue | user-gclid-rescue | cm-reported-source
sourceContext populated by fetchSourceContext: contact name, email (clickable), phone, location, and scheduled time (TOUR only). Other source types get attribution data only.

Subject line preference order: contact email > contact name > utm_content > utm_campaign > utm_source > "tracked".

PostHog event tracking

Custom event tracking in apps/web/src/lib/tracking.ts wraps posthog.capture. The posthog.__loaded guard was removed — it prevented events from being queued when the SDK loads async on cold ad traffic. PostHog's SDK already queues events that arrive before init and replays them on load, so the guard was actively dropping events on mobile. Mid-funnel engagement events (market-landing-viewed, scroll-depth-75, tour-form-started, etc.) now reach PostHog regardless of SDK load order.

Google Ads UTM tracking

Google Ads campaigns are configured with account-level final_url_suffix via apps/api/scripts/google-ads-set-utm-tracking.ts. The suffix injects utm_source=google&utm_medium=paid&utm_campaign={campaignid}&utm_term={adgroupid}&utm_content={creative}. Campaign and ad-group values are numeric IDs (ValueTrack has no name params) — join against the Google Ads API when reporting campaign names.

When a click lands without UTMs (e.g., a campaign-level override suppressed the suffix), gclid alone is captured. apps/api/scripts/backfill-gclid-attribution.ts can look up historical gclid rows in Google Ads click_view (90-day retention, single-date queries) and back-fill the UTM fields. Already applied once for 3 rows from Salem's first-practice campaign.

Google Ads server-side conversion upload (Data Manager API)

Mirror of Meta CAPI for Google. apps/api/src/lib/google-ads/ — data-manager-client.ts (env-gated, never-throws, captureError on failure, 5s timeout, GOOGLE_DM_VALIDATE_ONLY test analog), hash-identity.ts (Google normalization: gmail/googlemail dot + plus-suffix removal, E.164 phone, SHA-256 hex), track-purchase.ts / track-lead.ts. The legacy Google Ads API UploadClickConversions was blocked for new integrations on 2026-06-15; uploads go to datamanager.googleapis.com/v1/events:ingest (plain OAuth datamanager scope on the existing service account, no developer token). Background: [[features/google-capi/investigation]].

Wire sites mirror the Meta CAPI table: purchase events fire beside every trackPurchaseEventToMeta call (FulfillmentService, PaymentIntentHandler, LockoutFinalizeService — the lockout one is ledger-idempotent via LOCKOUT_EFFECT_KEYS.googlePurchaseEvent, deliberately not in the required-keys list so the sweep cron doesn't retro-fire old reservations); lead events fire beside every trackLeadEventToMeta call (tours, contact ×2, email-capture, green-room, comp).
Dedup: transactionId = the same Stripe object ID used as Meta event_id; the browser gtag Purchase conversion now sends the same value as transaction_id, so tag + upload dedup within the shared conversion action.
Conversion actions (account 6392867894, the conversion-tracking owner): Purchase = 7510162937 (the existing gtag WEBPAGE action — same action so dedup works), Lead = 7677760314 (Lead (server upload), UPLOAD_CLICKS, SUBMIT_LEAD_FORM, secondary). Managed via scripts/marketing/google-ads-conversion-setup.ts (audit / create-lead-action / validate-dm subcommands).
Consent: every send gates on ConsentService.isOptedOutOfAdvertising exactly like the Meta track-* helpers; contact-route sends additionally ride runAdTrackingAfter (anonymous GPC/opt-out gate). Events we do send carry consent: CONSENT_GRANTED.
Env: GOOGLE_ADS_CONVERSION_CUSTOMER_ID, GOOGLE_ADS_CONVERSION_ACTION_ID_PURCHASE, GOOGLE_ADS_CONVERSION_ACTION_ID_LEAD (+ the existing service-account key vars). Missing env → silent skip.

Meta Instant Form lead capture

Naming note: Inquiry is the generic on-site lead record (contact form, email capture, tour request); MetaLead is specifically the off-platform Meta Instant Form feed synced from the Graph API — it is not a general lead table. (Google has no equivalent: we run no Google Lead Form assets, verified 2026-07-08 via google-ads-conversion-setup.ts audit.)

Meta Instant Form leads are submitted inside Facebook/Instagram and never touch metrognome.com, so they leave no on-site UTM trail and produce no Inquiry — the carry-forward query above can't see them. They're also only retained by Meta's Graph API for ~90 days. The meta_leads table persists them so the lead→tenant conversion stays measurable beyond that window and can feed the attribution synthesis.

Table: meta_leads (public schema) — metaLeadId (unique), formId, formName, email, phone, name, rawFields (JSONB), leadCreatedAt, syncedAt. Indexed on email, phone, and leadCreatedAt. Soft-deletable.
Fetch: src/lib/meta/leads.ts — fetchInstantFormLeads(token, { since }) walks /me/accounts → page forms → leads via the Graph API (v21.0), paginating each level (a hardcoded limit=25 previously dropped leads past the first page). parseLeadFields normalizes the raw field_data into email/phone/name, guarding against fields like phone_number_verified.
Sync: MetaLeadSyncService.syncMetaLeads(token) upserts by metaLeadId (idempotent). Incremental — getIncrementalCutoff reads the newest stored leadCreatedAt and re-pulls with a 2-day overlap (OVERLAP_DAYS) so late-arriving leads aren't missed.
Cron: /api/cron/sync-meta-leads runs daily (07:00 UTC, maxDuration 120s); see [[../architecture/background-jobs]]. Per-form fetch errors are collected and captureError'd rather than failing the whole run. Auth: META_MARKETING_ACCESS_TOKEN.

Marketing scripts (`apps/api/scripts/marketing/`)

Script	Purpose
`meta-ads-insights.ts`	Live Meta Marketing API pull: per-ad spend, impressions, CTR, CPC, CPM, results, rankings. Flags: `--days`, `--start/--end`, `--daily`, `--active`, `--json`. Auth: `META_MARKETING_ACCESS_TOKEN`.
`meta-inspect-lead-campaigns.ts`	Detailed lead-campaign breakdown from Meta
`meta-leads-trace.ts`	Traces persisted `meta_leads` to tenancy — matches lead email/phone/name to a user → active (or ever) monthly reservation; fill-rate/field-key diagnostics. Sync populates the table; trace matches from it.
`attribution-counts.ts`	Read-only: `conversion_attributions` counts per source type split Meta/Google/Other; all-time MONTHLY (LTV-bearing); INQUIRY/TOUR/MONTHLY by `utm_campaign`.
`attribution-cohort.ts`	Traces attributed inquiries → monthly tenancy (inquiry→user→reservation) to close the MONTHLY-attribution gap; 4-tier match (userId/email/phone/name); reports cost-per-tenant vs LTV per channel, excludes `cancelAtPeriodEnd` from active.
`google-ads-insights.ts`	Campaign-level spend, impressions, clicks, CTR, CPC, conversions across MCC for a window. Flags: `--days`, `--start/--end`.
`google-ads-set-utm-tracking.ts`	Audits/sets account-level `final_url_suffix` across MCC. `--apply` to write, `--clear-overrides` to strip campaign-level masks.
`google-ads-check-access.ts`	Validates OAuth credentials + MCC access
`google-ads-pmax-audit.ts`	PMax campaign audit
`google-ads-search-audit.ts`	Search campaign keyword + ad audit
`google-ads-salem-status.ts`	Ad approval state, policy issues, budget status for Salem campaign
`google-ads-salem-cleanup.ts`	Salem campaign cleanup operations
`google-ads-keyword-research.ts`	Keyword research helper
`export-google-customer-match.ts`	Exports user list for Google Customer Match upload
`backfill-gclid-attribution.ts`	Backfills UTMs on gclid-only attribution rows via Google Ads click_view. Dry-run by default, `--apply` to execute.

The /mg-meta-ads-review skill uses meta-ads-insights.ts as its default data source. CSV export from Ads Manager is a fallback for Events Manager Pixel-vs-CAPI diagnostics (no live-API equivalent for that split).

Key Calculations

Metric	Formula
MRR	Sum of subscription prices for active MONTHLY reservations at point in time
ARR	Latest month MRR × 12
Churn Rate	Cancelled in period ÷ active at period start × 100 (Stripe's formula)
Occupancy	Resources with isAvailable=false ÷ total ACTIVE STUDIO_MONTHLY resources × 100
Utilization	Booked hours ÷ available hours × 100 (from resource_availability_cache)
Tour Conversion	New MONTHLY reservations within 30d of completed tour ÷ completed tours × 100
Tour Goal	ceil(available_studios / 4 / 0.8) per location per week
Credit Discount	10% off card price
Platform Fee (credit)	10% of credit value at redemption (surfaced as the bookkeeping pivot's MG0 Credit Fee column and the Revenue/Balances tabs' MG0/Metrognome Credit Fees scalars)
Platform Fee (charge)	`net_cents − transferred_cents` per attributed ledger row (billing surcharge on lockout subscriptions and other charges)
Net Transferred	`transferred_cents` — sum of outbound Stripe transfers from platform to Connect account for the source charge, allocated proportionally to net across line items; for `credit_redemption_transfer` rows (where `transferred_cents` is NULL) uses `abs(gross_cents)` instead

Code Map

apps/api/prisma/migrations/        View definitions (SQL in migration files)
analytics/
  README.md                        Metabase setup, directory structure, conventions
  DASHBOARDS.md                    Dashboard specifications — layouts, chart types, data sources
  REPORTS.md                       In-app staff reports reference (pre-Metabase)
  scripts/                         Python scripts for Metabase API automation
  queries/
    capacity/                      Occupancy, utilization, waitlist
    customers/                     Churn, tenure, tour funnel
    finance/                       MRR, revenue, failed payments
    legacy/                        Superseded queries (reference only — do not use for new dashboards)
apps/api/scripts/
  setup-metabase-role.ts           Create metabase_readonly role
  validate-metabase-queries.ts     Validate all SQL queries against the database
  audit-metabase-queries.ts        Audit dashboard queries with row counts and sample data
  index-docs.ts                    Reindex pgvector embeddings (doc Q&A)
  backfill-gclid-attribution.ts    Backfill UTMs on gclid-only attribution rows via Google Ads click_view
  marketing/
    meta-ads-insights.ts           Live Meta Marketing API: ad-level spend/results/rankings
    meta-inspect-lead-campaigns.ts Meta lead-campaign breakdown
    google-ads-insights.ts         Google Ads campaign metrics for a window
apps/api/src/services/shared/
  AttributionService.ts            Conversion attribution write/carry-forward/alert pipeline
apps/web/src/lib/
  utm-storage.ts                   Frontend UTM + click-ID capture and retrieval
  tracking.ts                      PostHog custom event wrapper (trackEvent)

Dashboard Scripts (`analytics/scripts/`)

All require METABASE_API_KEY env var and default to METABASE_URL=http://homelab:3100.

Script	Purpose
`create-bookkeeping-dashboard.py`	Creates Bookkeeping Dashboard (id 18): Revenue tab (seven reconciliation scalars, Revenue by Location pivot, Category/Grain params) and Transactions tab (All Transactions); also (re)builds the MG-ordered static Location filter list. Header documents what's live-managed only (Revenue by Category charts, Measure filter, hand-tuned layout) — re-running creates new cards rather than updating existing ones
`create-bookkeeping-accountant-dashboard.py`	Full structural (re)clone of Bookkeeping Dashboard into `Stripe Bookkeeping (COR Accounting)` collection — id churns, run only for a new tab/card or a source-view column change — drops Memberships (`DROP_TABS`, a no-op now that dash 18 lacks it too), locks every location-bearing card to MG4/5/6/8/9
`sync-bookkeeping-clone.py`	Routine parity maintenance: syncs card `dataset_query` from Bookkeeping (dash 18) to dashboard 34 in place via `CARD_MAP`, preserving clone-only filter clauses and never touching `visualization_settings`; dashboard id stays stable. Native-SQL cards (e.g. the Revenue by Category charts) print as unmapped and need manual copying
`create-prospecting-dashboard.py`	Creates Prospecting Dashboard
`add-monthly-tab.py`	Adds Monthly Studios tab to Owner Dashboard
`add-tours-tab.py`	Adds Tours tab to Owner Dashboard
`add-hourly-tab.py`	Adds Hourly Studios tab to Owner Dashboard
`add-insurance-tab.py`	Adds Insurance tab to Owner Dashboard
`add-bookkeeping-memberships-tab.py`	Retired — built the Memberships tab (one row per succeeded subscription invoice, signup/renewal/change/rebill Type column, excludes credit-purchase invoices); not run against either live dashboard, both of which have dropped Memberships
`add-bookkeeping-balance-tab.py`	Adds the Money Movements tab to Bookkeeping Dashboard (script still calls it "Balances" internally): MG0 cash position, waitlist deposits, holder-level credit balances + credit-fee scalar, Connect account balances (PR #900), transfer count/amount + All Transfers detail table
`update-bookkeeping-daily-filter.py`	Converts the Bookkeeping dashboard's month-year filter to a daily `date/all-options` filter renamed "Date"; retargets `month_start` parameter mappings to `event_date` where the source view has one
`configure-metabase-metadata.py`	Configures field semantic types after view migration
`add-bands-tab.py`	Adds Bands tab to Prospecting Dashboard
`add-listings-card.py`	Adds listings card to Prospecting Dashboard
`add-prospecting-scalars.py`	Adds scalar cards to Prospecting Dashboard
`restore-prospecting-tabs.py`	Restores Prospecting tabs if accidentally deleted

Patterns

Adding a new analytics view:

Create a Prisma migration: CREATE OR REPLACE VIEW analytics.your_view AS ...
Grant access: GRANT SELECT ON analytics.your_view TO metabase_readonly (in the migration)
Run configure-metabase-metadata.py after deploying to set column semantic types
Add the query to analytics/queries/ for version control
Update DASHBOARDS.md if adding to a dashboard

Modifying an existing view:

Create a new migration that drops and recreates the view (DROP VIEW IF EXISTS then CREATE VIEW)
For materialized views: DROP MATERIALIZED VIEW IF EXISTS ... CASCADE then recreate
Run validate-metabase-queries.ts to verify no queries break
If column names changed, update configure-metabase-metadata.py field mappings

Adding a new dashboard tab:

Write a Python script following the pattern in analytics/scripts/add-tours-tab.py
Use discover_analytics_schema() to find table/field IDs dynamically
Run with METABASE_API_KEY=x python3 analytics/scripts/your-script.py

Dashboard script conventions:

DATABASE_ID = 2 is the production DB connection in Metabase
Scripts discover table/field IDs dynamically via the Metabase API — don't hardcode IDs
Currency fields from cents: {"number_style": "currency", "currency": "USD", "scale": 0.01}
Scripts are idempotent for creation but tabs/cards are additive — running twice creates duplicates

Validating queries:

validate-metabase-queries.ts — runs all SQL queries against the DB, shows row counts
audit-metabase-queries.ts — deeper audit with sample data
Both support --production flag (requires .env.production.vercel)

Gotchas

bookkeeping_stripe_ledger is a MATERIALIZED VIEW — stale until refreshed. All other views are regular views (always current).
Views use CREATE OR REPLACE where possible, but adding/removing columns requires DROP + CREATE (Postgres limitation).
Don't sum bookkeeping_liabilities.amount_cents as "cash Metrognome must have on hand." location_held_waitlist_deposit rows are contingent and off-platform (cash already sent to the location) — stripe-reconcile-balance reports that subtotal separately rather than folding it into the on-platform funding-gap check.
configure-metabase-metadata.py must run after migrations that add new views — Metabase won't apply semantic types (category dropdowns, currency formatting) without it.
analytics/queries/legacy/ contains superseded queries kept for reference — don't use for new dashboards.
The analytics schema is separate from public — views can read from public tables but live in analytics.
Bookkeeping attribution coverage note: charges lacking a Payment FK entirely (no matching invoice, payment intent, or reservation) stay in the Metrognome / unattributed bucket. A separate backfill script is needed to recover those rows. Part C covers transfers with no Payment FK, not bare charges. Don't confuse the two.
Two-perspective counting: never filter bookkeeping_revenue_summary with both location_name = 'Metrognome' and a location name in the same query — the platform and Connect views are independent; adding both double-counts revenue.
Ad attribution: migration users are intentionally excluded. writeOrCarryForwardAttribution skips any user with a migration_submissions row. ~89% of MONTHLY volume since 2026-04 is migration-linked, not ad-driven — segmenting those out is required before interpreting low coverage rates.
Ad attribution: fbp-only rows are not written. hasRealAdAttribution requires a UTM param, fbclid, or gclid. Rows with only fbp/fbc (Meta Pixel cookie) are dropped; CAPI reads fbp directly from Stripe metadata and does not need it in this table.
UTM sessionStorage TTL is 24 hours. The typical lockout funnel spans days. fbclid survives 90 days via the _fbc cookie; gclid has no equivalent persistent cookie. Mitigation: when the user signs up (or a tour stub is created) within the 24h window, the gclid is persisted to User.gclidFirstTouch and rescued at conversion time by the carry-forward chain. A Google click that never reaches signup within 24h is still lost. iOS click IDs (gbraid/wbraid) are captured and threaded like gclid (real ad attribution, Stripe metadata, conversion_attributions, DM upload adIdentifiers) but have no first-touch User column — they only survive within the 24h window.
Google Ads UTM campaign/term are numeric IDs. ValueTrack {campaignid} and {adgroupid} emit integers. Join against the Google Ads API (google-ads-insights.ts) to resolve names when reporting.
PostHog SDK loads async. Do not add guards on posthog.__loaded before calling trackEvent — the SDK queues events that arrive before init and replays them. Adding the guard re-introduces the silent-drop bug (see #720).