Analytics & Tips Integration

Restaurant analytics in Danvas are Neon-native: warehouse fact tables land in public, app-owned marts live in analytics, and Intelligence → Analytics pages read those tables through server actions and TanStack Query. There is no external Analytics API roundtrip for tips or team-performance charts.

Related: ADR-0076 (tip calculation model), Analytics user guide, analytics upstream sync contract.

ArchitectureCopy Anchor Link

graph TB
    subgraph Upstream["Toast sync"]
        TOAST[Toast POS + labor]
        FACTS[canonical facts<br/>f_checks, f_orders, f_time_entries]
    end

    subgraph Writers["Dagster"]
        D1[daily_certified_metrics_job]
        WEEKLY[completed_week_metrics_job]
        REPAIR[approval-bound repair]
        UPGRADES[Upgrade daily + weekly assets]
    end

    subgraph Marts["analytics schema"]
        TMD[tip_metrics_daily]
        TMH[tip_metrics_hourly]
        TMW[tip_metrics_weekly]
        EUS[employee_upgrade_scores_v2]
    end

    subgraph Readers["apps/app"]
        TIPS_UI[/analytics/tips]
        PERF[/analytics/sales-by-employee …]
        UP_UI[/analytics/upgrades]
        DASH[Dashboard tip enrichment]
    end

    TOAST --> FACTS --> D1 --> TMD
    D1 --> TMH
    TMD --> WEEKLY --> TMW
    REPAIR --> D1
    REPAIR --> WEEKLY
    UPGRADES --> EUS
    TMD --> TIPS_UI
    TMH --> PERF
    EUS --> UP_UI
    TMD --> DASH

Layer rolesCopy Anchor Link

Staging Deprecation & Canonical Fact Tables (Issue #908)Copy Anchor Link

The legacy staging tables (analytics.stg_orders, analytics.stg_checks, analytics.stg_selections) were permanently dropped in migration 0155_mixed_paladin.sql to eliminate dual-writing overhead and raw payload parsing drift. They have been replaced with canonical fact models (analytics.f_orders, analytics.f_checks, etc.) as the source-of-truth warehouse layer.

Creation and Refresh:

• Pipeline Ingest: Dagster's single-flight live_ingestion_job reads Toast source payloads and populates the canonical f_orders, f_checks, and f_time_entries tables.
• Mart Refresh: The partitioned daily_certified_metrics_job rebuilds tip_metrics_daily and tip_metrics_hourly from canonical facts. The separate completed_week_metrics_job owns tip_metrics_weekly.
• Degraded-State Handling: Since analytics tables are managed by background syncs and can lag or be temporarily absent (e.g., during database migrations or pipeline backfills), all app query surfaces (e.g., dashboard, lineup pacing, historical metrics) wrap analytics/fact table queries in try-catch blocks using isMissingRelationError(err). If missing, the app degrades gracefully (returning zeroed figures or empty datasets and marking analyticsUnavailable: true or logging a warning) rather than causing HTTP 500 errors.

Join keys use location_slug and employee_key — never Toast GUIDs in app read paths (ADR-0063).

Tip metrics martsCopy Anchor Link

Schema: packages/database/src/schema/analytics.ts.

tip_metrics_dailyCopy Anchor Link

Primary key: (business_date, location_slug, employee_key).

Written by the partitioned Dagster tip_metrics_daily asset from certified facts, Toast time entries, and job mappings. Implements ADR-0076: charged tips, declared cash, estimated cash (18–30% band), combined tips, Bar Bot redistribution, and tip variance.

Key columns: actual_tips, declared_cash_tips, estimated_cash_tips, total_combined_tips, tip_pct, tip_variance (manager-only), gross_sales, hours_worked, sph, effective_hourly_pay, upgrade_score.

tip_metrics_hourlyCopy Anchor Link

Primary key: (business_date, hour_start, location_slug, employee_key).

Built by the partitioned Dagster D-1 asset from accepted analytics.f_time_entries plus certified order/check facts. Carries labor, sales splits, transactions, and guest counts at UTC hour grain with restaurant-local display fields for Team Performance and dashboard enrichment.

tip_metrics_weeklyCopy Anchor Link

Primary key: (week_start, location_slug, employee_key).

Aggregated from all seven finalized tip_metrics_daily partitions by the Dagster tip_metrics_weekly asset (weeks start Saturday, America/Chicago). tip_pct is a ratio of additive charged-tip and tipped-payment sums.

Writers and schedulesCopy Anchor Link

Historical backfillCopy Anchor Link

The app-side date-range writer is retired. Historical replay uses exact Dagster partitions or an approval-bound #989 repair manifest. Production repair requires an exact branch, scope, plan digest, checks, and rollback identity.

Coverage expectations after 1-year backfill (hartalliance, 2026-06-17):

• Hourly — populated for all days with neon_fct_employee_hourly rows (~250–1,300 rows/day).
• Daily — sparse before comp mart rollout (~May 2026); requires staging data for rebuild-comp. Older dates may show dailyRows: 0 while hourly still backfills.
• Weekly — accumulates from daily; expect rows once daily mart has coverage for that week.

Historical rows created before the additive tipped-payment columns remain null until an approved repair replaces their partitions. Weekly materialization fails closed rather than treating those nulls as measured zeroes.

Read surfacesCopy Anchor Link

Location scoping uses getAccessibleLocations(); all queries include authorized location_slug filters.

Upgrade scoresCopy Anchor Link

analytics.employee_upgrade_scores_v2 and analytics.employee_upgrade_score_weeks store the approved daily and weekly Upgrade components. They are owned by the same explicit D-1 and completed-week Dagster jobs. See CONTEXT.md (Upgrade Score, Protein/Side Revenue).

Operational checksCopy Anchor Link

-- Tip mart freshness
SELECT 'daily', COUNT(*), MIN(business_date), MAX(business_date)
FROM analytics.tip_metrics_daily
UNION ALL
SELECT 'hourly', COUNT(*), MIN(business_date), MAX(business_date)
FROM analytics.tip_metrics_hourly
UNION ALL
SELECT 'weekly', COUNT(*), MIN(week_start), MAX(week_start)
FROM analytics.tip_metrics_weekly;

Dagster materialization and blocking-check records are the automation evidence. The retired /cron/rebuild-comp route returns HTTP 410 and is not a health source or fallback writer.

RelatedCopy Anchor Link