Orbit Audience Engine runs on two migrations that create the full schema, enforce row-level security, and schedule maintenance. This page describes the tables, policies, grants, and indexes that the engineering team maintains.
Migrations
Migration 20260829153215_add_orbit_audience_engine.sql creates the core tables:
- Consent and history
- Minimized events
- Category, score, and segment tables
- Advertiser organization, member, and balance tables
- Campaign, targeting, and creative tables
- Delivery and conversion tables
- Token, transaction, and audit tables
Migration 20260829165710_harden_orbit_audience_engine.sql adds:
- Moderation support
- Configurable frequency and retention policy tables
- Segment rules
- Transactional billing RPCs
- Minimized event ingestion helpers
- Privacy deletion routines
- Stricter column-level advertiser privileges
Row-level security
Every exposed table has RLS enabled. Explicit grants opt only necessary tables into the current Supabase Data API. Do not expose tables that do not need direct client access.
Advertiser policies
Advertiser policies always join membership to auth.uid(). This ensures an advertiser can only see rows belonging to their organization. Identifiable interest and event tables have no advertiser policy, so advertisers cannot query user-level data.
Admin authorization
Admin checks reuse user_roles. The system never trusts user-editable JWT metadata for role decisions. Always query the user_roles table server-side.
Indexes
High-volume indexes cover:
- Event type and time
- User and time
- Foreign keys
- Campaign delivery and reporting
- Transaction ledgers
- Segment arrays
- Frequency-cap lookups
Maintenance routine
audience_engine_maintenance performs two tasks:
- Deletes expired raw events according to the retention policy.
- Applies a 30-day interest half-life to decaying scores.
Schedule this routine daily using a service_role server context. Browser roles cannot execute it.
Do not expose audience_engine_maintenance to the Supabase Data API or any client role. It must run only from a trusted server context with service_role.
