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`	Stripe FDW — live API data via foreign tables

The stripe connection requires postgres because the FDW uses Supabase Vault for API key storage. Stripe schema queries make live API calls (~8s latency) — use Metabase caching for Stripe-based dashboards.

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, Tours, Hourly Studios, Insurance
Community Mgr	Location staff	Occupancy, utilization, tour pipeline, alerts
Bookkeeping	Finance / Aaron	Revenue, Credits, Waitlist, Refunds, Failed
Stripe Bookkeeping (COR Accounting)	External accountant (COR Accounting)	Clone of Bookkeeping; credit cards dropped; locked to MG4/5/6/8/9

All share global controls: location multi-select and time range. The COR Accounting dashboard 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/).

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	Insurance coverage lifecycle events
`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 (L1)
`bookkeeping_attributed_ledger`	VIEW	stripe_ledger + reservations/users	Ledger attributed to locations/resources; includes `transferred_cents` and `platform_fee_cents` (L2)
`bookkeeping_revenue_summary`	VIEW	attributed_ledger	Monthly revenue by type; 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_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.

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.

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. Manual refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.bookkeeping_stripe_ledger. The matview exists specifically to avoid repeated FDW hits at query time (~8s per Stripe API call).

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.

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
Platform Fee (charge)	`net_cents − transferred_cents` per attributed ledger row (billing surcharge on lockout subscriptions and other charges)
Transferred to Location	`transferred_cents` — sum of outbound Stripe transfers from platform to Connect account for the source charge, allocated proportionally to net across line items

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)

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 with Revenue + Credits tabs
`create-bookkeeping-accountant-dashboard.py`	Clones Bookkeeping Dashboard into `Stripe Bookkeeping (COR Accounting)` collection, locked to MG4/5/6/8/9
`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-credits-tab.py`	Adds Credits tab to Bookkeeping Dashboard
`add-bookkeeping-waitlist-tab.py`	Adds Waitlist tab to Bookkeeping Dashboard
`add-bookkeeping-refunds-tab.py`	Adds Refunds tab to Bookkeeping Dashboard
`add-bookkeeping-failed-tab.py`	Adds Failed Payments tab to Bookkeeping Dashboard
`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).
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.
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.