Skip to content

Latest commit

 

History

History
90 lines (66 loc) · 8.06 KB

README.md

File metadata and controls

90 lines (66 loc) · 8.06 KB

Pulse — Supabase

Schema reference only — the single source of truth is schema.sql. No application code lives here.

Apply schema

Run schema.sql via the Supabase dashboard SQL editor or the apply_migration MCP tool. Safe to re-apply: tables use CREATE TABLE IF NOT EXISTS, RPCs use CREATE OR REPLACE, policies are DROP ... IF EXISTS before recreate.


Tables

devices — one row per app install. UUID generated by the app on first launch; stable across FCM token rotations, resets on reinstall. All client writes go through SECURITY DEFINER RPCs (see below); the table carries no open RLS policy.

Column Type Notes
id UUID PK App-generated
fcm_token TEXT NOT NULL UNIQUE (devices_fcm_token_key). Updated via onTokenRefresh
notify_at TIME Nullable — null = notify at cron time (MVP default); set = preferred UTC time
user_id UUID FK → auth.users Stamped after login; nullable. ON DELETE SET NULL
updated_at TIMESTAMPTZ

user_preferences — one row per authenticated user.

Column Type Notes
user_id UUID PK → auth.users RLS: auth.uid() = user_id. ON DELETE CASCADE
preferences JSONB Full UserPreferences object. Default '{}'
updated_at TIMESTAMPTZ

digests — one row per region per date, shared across all users. Cron upserts on (region, date) — safe to re-run. Rows older than 7 days are evicted before each daily write. PK (region, date) covers the main app query (WHERE region = ANY($1) AND date = $2); max ~56 rows (8 regions × 7 days), no extra index.

Column Type Notes
region TEXT e.g. "Hungary"
date DATE e.g. 2026-05-16
payload JSONB { headlines: Headline[] }
created_at TIMESTAMPTZ

notify_state — single-row store of the last window processed by jobs/notify.ts. The job notifies devices whose notify_at fell in (last_run_at, now], so no device is dropped when the GitHub Actions schedule fires irregularly. Seeded with now() so the first run after deploy sees a tiny window (no mass-notify). Cron service-role only.

Column Type Notes
id BOOLEAN PK DEFAULT TRUE CHECK (id) — forces exactly one row
last_run_at TIMESTAMPTZ DEFAULT now()

pipeline_runs — one row per daily-digest run; durable observability/cost record written by jobs/daily-digest.ts (see cron/src/runReport.ts, RunReport pulse.run.v1). Promoted scalar columns make cost-over-time queries cheap; the full structured report lives in report. Cron service-role only (no client read path).

Column Type Notes
id BIGINT PK GENERATED ALWAYS AS IDENTITY
run_at TIMESTAMPTZ NOT NULL Indexed DESC (pipeline_runs_run_at_idx)
status TEXT NOT NULL
total_cost_usd NUMERIC(10,6) NOT NULL
total_tokens INTEGER NOT NULL
regions_succeeded SMALLINT NOT NULL
regions_failed SMALLINT NOT NULL
duration_ms INTEGER Nullable
report JSONB NOT NULL Full per-region quality / cost / notify report
created_at TIMESTAMPTZ DEFAULT now()

RLS

Two-key model: publishable key in the app, secret (service-role) key server-side only. The service role bypasses RLS, so cron-only tables define no policy and stay private.

Table Rule
digests SELECT open to all (public read, publishable key). Writes via secret key (bypasses RLS).
devices No open policy. RLS denies every direct anon/authenticated SELECT/INSERT/UPDATE. All client access via the RPCs below.
user_preferences SELECT/INSERT/UPDATE restricted to auth.uid() = user_id (app publishable key).
notify_state No policy — cron service-role only.
pipeline_runs No policy — cron service-role only.

RPCs (SECURITY DEFINER)

All client writes to devices go through these instead of direct table access, which lets the table stay fully RLS-locked.

Function Grant Purpose
register_device(id, token, user_id?) anon, authenticated, service_role Canonical registration write (app + cron). Upserts by stable id; evicts any other row holding the same token to collapse reinstall ghosts.
update_notify_time(id, notify_at) anon, authenticated, service_role Only path for the app to change a device's notify_at. Returns FOUND.
link_device_to_user(id) authenticated, service_role Stamps auth.uid() (not a parameter) onto a device row — caller can only link to self.
peek_due_notifications() service_role Read-only "is any device due since last_run_at?". Used by the notify workflow guard.
claim_due_notifications() service_role Returns due FCM tokens AND advances last_run_at atomically (FOR UPDATE, at-most-once).

Gotcha — anon auto-grant. Supabase's ALTER DEFAULT PRIVILEGES auto-grants EXECUTE on every new public function directly to anon (and authenticated/service_role), so REVOKE ... FROM PUBLIC alone leaves anon able to call it. Service-only functions (peek_due_notifications, claim_due_notifications) and link_device_to_user therefore revoke from anon explicitly: REVOKE EXECUTE ... FROM PUBLIC, anon[, authenticated].