-- ============================================================================ -- SAG Manager — 0005 indexes + Row Level Security -- ============================================================================ -- Cross-cutting performance indexes and RLS policies. -- -- POLICY MODEL (single-tenant for now): -- The SAG Manager is a single-operator product today (Glenn). Until a -- multi-user model is fleshed out, RLS is defensive — any authenticated -- `auth.uid()` can read + write app-level tables. This is safer than -- "anon all-access" while still letting the Next.js app function with -- the anon key once a user is signed in. The CHECK in each policy is -- intentionally narrow: `auth.uid() is not null`. -- -- When multi-tenant support lands, swap the policy bodies to scope rows -- by an `owner_user_id` column or via an entity_grants join table. -- ============================================================================ -- ───────────────────────────────────────────────────────────────────────── -- High-cardinality lookup indexes — already covered in 0001-0004 for the -- common cases, but extras for the heavy queries are added here so they -- can be tuned independently without touching the table-creation migrations. -- ───────────────────────────────────────────────────────────────────────── -- Bills: (entity_slug, due_date) is the page's main query create index if not exists bills_entity_due_date_idx on public.bills (entity_slug, due_date); create index if not exists bills_due_status_idx on public.bills (due_date, status); -- NCDOR: (entity_slug, period_year, period_month) for the period filter create index if not exists ncdor_filings_period_idx on public.ncdor_filings (entity_slug, period_year desc, period_month desc); -- License expiries — global "expiring soon" query create index if not exists licenses_expires_status_idx on public.licenses (expires_date, status); create index if not exists hemp_licenses_expires_status_idx on public.hemp_licenses (expires_date); create index if not exists abc_permits_expires_status_idx on public.abc_permits (expires_date); create index if not exists domains_expiration_status_idx on public.domains (expiration_date, status); -- Subscriptions: monthly burn rollup by entity create index if not exists subscriptions_entity_amount_idx on public.subscriptions (entity_slug, monthly_amount); -- Owner draws: monthly / yearly totals (date prefix queries) create index if not exists owner_draws_date_entity_idx on public.owner_draws (draw_date desc, from_entity_slug); -- Social posts: scheduled-at window query create index if not exists social_posts_scheduled_status_idx on public.social_posts (scheduled_at, status); -- Gmail threads: classification join create index if not exists gmail_threads_internal_date_message_count_idx on public.gmail_threads (internal_date desc, message_count); -- ───────────────────────────────────────────────────────────────────────── -- Enable RLS on every app table -- ───────────────────────────────────────────────────────────────────────── alter table public.entities enable row level security; alter table public.entity_overrides enable row level security; alter table public.employees enable row level security; alter table public.bills enable row level security; alter table public.plaid_accounts enable row level security; alter table public.plaid_access_tokens enable row level security; alter table public.plaid_txn_overrides enable row level security; alter table public.plaid_txn_splits enable row level security; alter table public.subscriptions enable row level security; alter table public.subscription_ignored enable row level security; alter table public.owner_draws enable row level security; alter table public.toast_imports enable row level security; alter table public.toast_labor enable row level security; alter table public.ncdor_filings enable row level security; alter table public.hemp_licenses enable row level security; alter table public.abc_permits enable row level security; alter table public.ttb_step_status enable row level security; alter table public.nc_sos_filings enable row level security; alter table public.licenses enable row level security; alter table public.domains enable row level security; alter table public.hires enable row level security; alter table public.pto_rules enable row level security; alter table public.pto_requests enable row level security; alter table public.pay_schedules enable row level security; alter table public.offer_letters enable row level security; alter table public.onboarding_tasks enable row level security; alter table public.onboarding_progress enable row level security; alter table public.comp_benchmarks enable row level security; alter table public.documents enable row level security; alter table public.social_accounts enable row level security; alter table public.social_posts enable row level security; alter table public.music_releases enable row level security; alter table public.music_bookings enable row level security; alter table public.music_contacts enable row level security; alter table public.music_streaming enable row level security; alter table public.gmail_threads enable row level security; alter table public.gmail_thread_classifications enable row level security; alter table public.mail_calendar_suggestions enable row level security; alter table public.agent_threads enable row level security; alter table public.research_findings enable row level security; -- ───────────────────────────────────────────────────────────────────────── -- Policies — single-user model. Any signed-in user can read + write. -- Each policy is dropped + re-created so re-running the migration is safe. -- ───────────────────────────────────────────────────────────────────────── do $$ declare t text; tables text[] := array[ 'entities','entity_overrides','employees', 'bills','plaid_accounts','plaid_access_tokens','plaid_txn_overrides','plaid_txn_splits', 'subscriptions','subscription_ignored','owner_draws','toast_imports','toast_labor', 'ncdor_filings','hemp_licenses','abc_permits','ttb_step_status','nc_sos_filings', 'licenses','domains','hires','pto_rules','pto_requests','pay_schedules', 'offer_letters','onboarding_tasks','onboarding_progress','comp_benchmarks', 'documents','social_accounts','social_posts', 'music_releases','music_bookings','music_contacts','music_streaming', 'gmail_threads','gmail_thread_classifications','mail_calendar_suggestions', 'agent_threads','research_findings' ]; begin foreach t in array tables loop execute format( 'drop policy if exists "Authenticated full access" on public.%I;', t ); execute format( 'create policy "Authenticated full access" on public.%I for all to authenticated using (auth.uid() is not null) with check (auth.uid() is not null);', t ); end loop; end $$;