Analytics and BI

Two access methods are available: a direct read-only database connection (recommended for Metabase, Grafana, Power BI, and similar tools) and a REST export API (useful for JSON-pulling ETL tools or simple scripts that only need the analytics_events table).

Direct database connection (recommended)

Create a read-only Postgres user in Supabase and point any JDBC/ODBC-compatible BI tool at it:

-- Run once as the database owner
CREATE ROLE bi_readonly WITH LOGIN PASSWORD 'choose-a-strong-password';
GRANT CONNECT ON DATABASE your_db TO bi_readonly;
GRANT USAGE ON SCHEMA public TO bi_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO bi_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO bi_readonly;

Connection string: postgresql://bi_readonly:<password>@<supabase-host>/<db>?sslmode=require

The Supabase host is under Project Settings → Database → Connection string. Use the read replica endpoint if your project has one — it isolates BI queries from production traffic.

All tables are multi-tenant. Always filter by account_id in queries. The default account UUID is 00000000-0000-0000-0000-000000000001.

REST export API

Useful for tools that pull JSON (Airbyte, custom ETL scripts). Only exposes analytics_events.

Create a token at Admin → Tokens with scope analytics, then paginate with the cursor:

GET /api/export/analytics
  ?since=2026-01-01T00:00:00Z
  &limit=1000
  &eventType=activity_created
Authorization: Bearer <token>

Response:

{
  "data": [{ "id": 1, "account_id": "...", "user_email": "...", "event_type": "...",
             "event_date": "2026-05-01", "occurred_at": "2026-05-01T09:12:33Z", "metadata": {} }],
  "cursor": "2026-05-01T09:12:33Z",
  "hasMore": true
}

Keep fetching with ?since=<cursor> until hasMore is false.

Table reference

analytics_events — user interaction log

30-day retention by default. The primary fact table for usage analysis.

Column	Type	Notes
id	bigint	PK
account_id	uuid	FK → tenant_accounts.id
user_email	text	Lowercase
event_type	text	See event types below
event_date	date	Local date (for daily aggregates)
occurred_at	timestamptz	Exact timestamp
metadata	jsonb	Event-specific payload

Event types:

event_type	When it fires	Key metadata fields
login	User session starts	—
day_viewed	User opens a calendar day	date — deduplicated per user/day
activity_created	Activity or task created	section
activity_edited	Activity or task edited	section
activity_deleted	Activity or task deleted	section

metadata.section values on create/edit/delete events:

section	What it covers
calendar	ERP calendar events
tasks	Tasks (ERP, Outlook, Google)
kanban	Kanban column moves — detected when the only fields updated are ActType/ActState/ActStateCode/SalesStep
booking	Booking page completions

Events recorded before section tracking (pre-2026-05-23) have no section key. Queries use COALESCE(metadata->>'section', 'calendar') to treat them as calendar.

bookings — completed meeting bookings

One row per booking submitted through a booking page.

Column	Type	Notes
id	uuid	PK
account_id	uuid
template_id	uuid	FK → booking_templates
share_link_id	uuid	FK → favorite_share_links
booker_email	text	Person who booked
booked_date	date	Local date of the meeting
booked_utc	timestamptz	Unambiguous UTC timestamp — use this for sorting
duration_minutes	int
status	text	pending, confirmed, cancelled, rescheduled, failed
field_values	jsonb	Custom form field answers keyed by field label
created_erp_ids	jsonb	Array of ERP SernNr values created
created_at	timestamptz

booking_templates — meeting types

Column	Type	Notes
id	uuid	PK
user_email	text	Owner
name	text	Template name shown to bookers
duration_minutes	int
buffer_minutes	int	Gap after each meeting
availability_windows	jsonb	Array of {day, start, end} objects
targets	jsonb	Which calendars/connections receive the booking
custom_fields	jsonb	Array of form field definitions
active	boolean

favorite_share_links — booking/sharing pages

Column	Type	Notes
id	uuid	PK
token	text	URL token (public identifier)
visibility	text	busy, titles, full
booking_enabled	boolean
access_count	int	Total visit count
last_accessed_at	timestamptz

user_favorites — saved calendar views

Column	Type	Notes
id	uuid	PK
user_email	text	Owner
name	text
view	text	day, 3day, 5day, 7day, month, tasks, kanban
person_codes	text[]	ERP person codes included

account_members — user roster

Column	Type	Notes
id	uuid	PK
email	text
role	text	admin, member
active	boolean
last_login	timestamptz

Key queries

Active users (30 days)

SELECT COUNT(DISTINCT user_email) AS active_users
FROM analytics_events
WHERE account_id = '<account_id>'
  AND event_date >= CURRENT_DATE - 30;

Usage by area

SELECT
  COALESCE(metadata->>'section', 'calendar') AS area,
  COUNT(*) FILTER (WHERE event_type = 'activity_created') AS created,
  COUNT(*) FILTER (WHERE event_type = 'activity_edited')  AS edited,
  COUNT(*) FILTER (WHERE event_type = 'activity_deleted') AS deleted
FROM analytics_events
WHERE account_id = '<account_id>'
  AND event_date >= CURRENT_DATE - 30
  AND event_type IN ('activity_created', 'activity_edited', 'activity_deleted')
GROUP BY area
ORDER BY created DESC;

Per-user engagement

SELECT
  user_email,
  COUNT(*) FILTER (WHERE event_type = 'login')            AS logins,
  COUNT(*) FILTER (WHERE event_type = 'day_viewed')       AS days_viewed,
  COUNT(*) FILTER (WHERE event_type = 'activity_created') AS created,
  MAX(event_date)                                          AS last_active
FROM analytics_events
WHERE account_id = '<account_id>'
  AND event_date >= CURRENT_DATE - 30
GROUP BY user_email
ORDER BY last_active DESC;

Bookings by template

SELECT
  bt.name            AS template,
  sl.name            AS share_link,
  COUNT(b.id)        AS total_bookings,
  COUNT(b.id) FILTER (WHERE b.status = 'confirmed') AS confirmed,
  COUNT(b.id) FILTER (WHERE b.status = 'cancelled') AS cancelled
FROM bookings b
JOIN booking_templates bt ON bt.id = b.template_id
JOIN favorite_share_links sl ON sl.id = b.share_link_id
WHERE b.account_id = '<account_id>'
GROUP BY bt.id, bt.name, sl.name
ORDER BY total_bookings DESC;

Booking pages — views vs conversions

SELECT
  sl.name           AS page,
  sl.access_count   AS views,
  COUNT(b.id) FILTER (WHERE b.status = 'confirmed') AS bookings,
  ROUND(
    COUNT(b.id) FILTER (WHERE b.status = 'confirmed')::numeric
    / NULLIF(sl.access_count, 0) * 100, 1
  )                 AS conversion_pct
FROM favorite_share_links sl
LEFT JOIN bookings b ON b.share_link_id = sl.id AND b.account_id = '<account_id>'
WHERE sl.booking_enabled = true
GROUP BY sl.id, sl.name, sl.access_count
ORDER BY views DESC;

Known limitations

Gap	Notes
Booking page views	access_count counts every GET on the share link page, not only the booking form. Treat it as an approximation of page views.
analytics_events retention	Default 30 days. Extend by changing retentionDays in lib/analytics.ts. Raw bookings are not purged.
Kanban heuristic	Column moves are tagged kanban only when the PUT body contains solely the four state fields. Full edits opened from the kanban drawer are tagged calendar.
iOS Reminders	iOS task completions are not tracked in analytics_events — the device syncs directly to the ios_reminders table.