Instruction file imported from dfarre888/SPECTRAL (.cursor/rules/03-database-schema.mdc). Copyright stays with the author.

Database Schema Reference

Supabase Client Tiers — Always Pick the Right One

// Browser (client components only) — anon key, RLS enforced
import { createBrowserClient } from '@/lib/supabase/client'

// Server (Server Components + API routes) — anon + cookie session, RLS enforced
import { createClient } from '@/lib/supabase/server'
const supabase = await createClient()

// Service role server (admin ops only, explicit call) — bypasses RLS
import { createServiceClient } from '@/lib/supabase/server'

// Node.js runtime service role (PCM WorldStateEngine + scripts only) — WebSocket transport
import { createServiceRoleNodeClient } from '@/lib/supabase/service-role-node'

Rules:

• createServiceClient() and createServiceRoleNodeClient() NEVER appear in client components or page components
• service-role-node.ts is for lib/pcm/worldStateEngine.ts and standalone Node scripts ONLY
• Demo mode auto-elevates createClient() to service role — never set NEXT_PUBLIC_DEMO_MODE=true in production

Migration Files

supabase/migrations/ — 30+ timestamped migration files. Apply in order. Apply with: supabase db push OR Supabase MCP apply_migration. Never modify existing migration files. Always create new ones: YYYYMMDDHHMMSS_description.sql

Pending (must apply in order):

1. 20260624120000_iran_missile_platforms.sql
2. 20260624130000_us_allied_cruise_missiles.sql — has guidance_type constraint dependency on #1

Tables (Supabase PostgreSQL)

platforms — Military UAS

gnss_jammers — EW Jamming Systems

gnss_constellations — Satellite Navigation Systems

anti_drone_systems — C-UAS

conflict_incidents — Case Studies

nav_countermeasures — GPS-Denied Navigation Methods

scenario_templates + scenario_injects

• scenario_templates — Red/Blue scenario configs with terrain, ORBAT, phase timeline
• scenario_injects — 15 pre-seeded injects (GNSS Degraded, ROE Change, Swarm Detected, Spoof Alert, Fiber Optic FPV, etc.)

Defeat / Accredited Tables

Critical isolation rule: Accredited table data NEVER flows to the learner model (spectral_competency_records), client components, or any Claude call. Access via lib/operations/accredited-supplements.ts only.

PCM (Persistent Combat Model) Tables

MOAT (Learner Model) Tables

NEVER insert directly into MOAT tables. All writes must go through processMoatAfterTurn().

WOPR Tables

Operations Tables

TypeScript Interfaces

All types live in lib/types/index.ts:

Platform, GnssConstellation, GnssJammer, JammerTier,
AntiDroneSystem, ConflictIncident, NavCountermeasure,
ScenarioConfig, ScenarioInject

Always import from @/lib/types — never redefine inline.

Supabase Query Patterns

Server component (correct)

import { createClient } from '@/lib/supabase/server'

export default async function PlatformsPage() {
  const supabase = await createClient()
  const { data, error } = await supabase
    .from('platforms')
    .select('*')
    .eq('status', 'Operational')
    .order('name')
  // ...
}

Client component with realtime (correct)

'use client'
import { createClient } from '@/lib/supabase/client'
// use inside useEffect or React Query

NEVER do these

• supabaseAdmin / service role key in any client component or page component
• supabase.from('platforms').select('*') without .order() on list pages — always paginate or order
• Raw SQL via supabase.rpc() unless there's a specific reason — prefer the query builder
• Bypassing RLS with { count: 'exact', head: true } queries in user-facing paths

RLS Policies

• Read access is open (anon key is fine for all SELECT)
• Write access requires authentication
• Service role key is server-side only (never expose to browser)

Migration Files

supabase/migrations/001_initial_schema.sql — contains the full schema + 15 scenario injects pre-seeded. Never modify this file directly. Create new migration files sequentially: 002_..., 003_...