Admin
Production Setup
Protected setup console for checking whether the live map table, growth tables, source correction audit queue, email briefing send ledger, subscriber compatibility columns, explicit email consent evidence, admin roles, forum tables, forum reputation, account watchlists, persistent evidence claims, guarded news review, PC build profiles, Pro workspace snapshots, marker overlays, forum reports, compliance request ledger, retention audit log, publishing queue, community challenges, map collections, authority-page registry, source-watch runs, social jobs, forum subscriptions, evidence graph, launch command events, durable app limits, account timelines, saved searches, route plans, forum notifications, distribution calendar, public-write lockdown, submissions, and Pro entitlement tables are ready.
Admin session
Checking signed-in admin session...
Critical path
Run the migration once in Supabase. It is written to be safe if re-run.
After it passes, the live map can use discoveries, signups write segmented interests into growth_signups, compatibility subscribers carry explicit consent evidence, source challenges get an audit queue, weekly briefings get a send ledger, admin roles can replace shared tokens, the forum can moderate threads, replies, reactions, reports, reputation, and subscriptions, account watchlists can sync, route plans and saved searches can persist, evidence claims can promote marker overlays, PC builds can be saved to accounts, publishing tasks and daily work items can persist, social jobs and distribution calendar rows can be reviewed, source-watch runs have history, evidence graph links can be browsed, launch command items get memory, compliance requests get a formal ledger, retention runs get an audit trail, direct anonymous writes are locked behind server APIs, and Growth Lab can query first-party analytics.
Until then, the public map falls back to static evidence and email capture keeps using the legacy subscriber table.
Migration SQL
005 map + 006 growth + 007 sources + 008 briefings + 009 analytics + 010 admin roles + 011 forum + 012 accounts + 013 compliance + 014 lockdown + 015 email consent + 016 reputation + 017 product loop + 018 enforcement + 019 operating loop + 020 mission control + 021 platform OS
Supabase SQL editor
-- Leonida Intel: live map resilience
-- Safe to run after any previous migration state. This restores the discoveries table and policies
-- needed by the public live-map API, even when only later growth migrations were applied.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS discoveries (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT NOT NULL,
category TEXT NOT NULL CHECK (category IN (
'collectible', 'vehicle', 'property',
'easter-egg', 'stunt', 'utility'
)),
lat DOUBLE PRECISION NOT NULL,
lng DOUBLE PRECISION NOT NULL,
description TEXT,
video_url TEXT,
contributor TEXT,
source_url TEXT,
is_verified BOOLEAN DEFAULT false,
is_premium BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_discoveries_coords ON discoveries (lat, lng);
CREATE INDEX IF NOT EXISTS idx_discoveries_verified ON discoveries (is_verified) WHERE is_verified = true;
CREATE INDEX IF NOT EXISTS idx_discoveries_category ON discoveries (category);
CREATE INDEX IF NOT EXISTS idx_discoveries_free_verified ON discoveries (category, created_at DESC)
WHERE is_verified = true AND is_premium = false;
ALTER TABLE discoveries ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read verified discoveries" ON discoveries;
CREATE POLICY "Anyone can read verified discoveries"
ON discoveries FOR SELECT
USING (is_verified = true);
DROP POLICY IF EXISTS "Service role can manage all discoveries" ON discoveries;
CREATE POLICY "Service role can manage all discoveries"
ON discoveries FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS discoveries_updated_at ON discoveries;
CREATE TRIGGER discoveries_updated_at
BEFORE UPDATE ON discoveries
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
-- Leonida Intel: segmented growth signups and subscriber compatibility.
-- This migration is safe to run more than once in the Supabase SQL editor.
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
ALTER TABLE public.email_subscribers
ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'active',
ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT now();
UPDATE public.email_subscribers
SET created_at = COALESCE(created_at, subscribed_at, now()),
updated_at = COALESCE(updated_at, subscribed_at, now())
WHERE created_at IS NULL OR updated_at IS NULL;
ALTER TABLE public.email_subscribers
ALTER COLUMN created_at SET DEFAULT now(),
ALTER COLUMN created_at SET NOT NULL;
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_constraint
WHERE conname = 'email_subscribers_status_check'
AND conrelid = 'public.email_subscribers'::regclass
) THEN
ALTER TABLE public.email_subscribers
ADD CONSTRAINT email_subscribers_status_check
CHECK (status IN ('active', 'unsubscribed', 'bounced', 'complained'));
END IF;
END $$;
DROP TRIGGER IF EXISTS email_subscribers_updated_at ON public.email_subscribers;
CREATE TRIGGER email_subscribers_updated_at
BEFORE UPDATE ON public.email_subscribers
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.growth_signups (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL,
interest TEXT NOT NULL CHECK (interest IN (
'early_access',
'weekly_briefing',
'map_updates',
'preorder_alerts',
'ps5_preorder',
'xbox_preorder',
'pc_lab',
'pro_founder',
'launch_alerts'
)),
source_page TEXT,
source_component TEXT,
platform TEXT,
region TEXT,
locale TEXT,
timezone TEXT,
consent BOOLEAN NOT NULL DEFAULT true,
marketing_consent_at TIMESTAMPTZ NOT NULL DEFAULT now(),
referrer TEXT,
user_agent TEXT,
utm_source TEXT,
utm_medium TEXT,
utm_campaign TEXT,
utm_content TEXT,
utm_term TEXT,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
provider_synced_at TIMESTAMPTZ,
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'unsubscribed', 'bounced', 'complained')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_growth_signups_email_interest
ON public.growth_signups (email, interest);
CREATE INDEX IF NOT EXISTS idx_growth_signups_created_at
ON public.growth_signups (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_growth_signups_interest_created_at
ON public.growth_signups (interest, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_growth_signups_region_created_at
ON public.growth_signups (region, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_growth_signups_source_page
ON public.growth_signups (source_page);
ALTER TABLE public.growth_signups ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can join growth lists" ON public.growth_signups;
CREATE POLICY "Anyone can join growth lists"
ON public.growth_signups FOR INSERT
TO anon
WITH CHECK (consent = true);
DROP POLICY IF EXISTS "Service role can manage growth signups" ON public.growth_signups;
CREATE POLICY "Service role can manage growth signups"
ON public.growth_signups FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS growth_signups_updated_at ON public.growth_signups;
CREATE TRIGGER growth_signups_updated_at
BEFORE UPDATE ON public.growth_signups
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
-- Leonida Intel: source correction audit queue.
-- Safe to run more than once in the Supabase SQL editor.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS public.source_corrections (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
submission_id UUID REFERENCES public.intel_submissions(id) ON DELETE SET NULL,
marker_id TEXT,
source_id TEXT,
reason TEXT NOT NULL DEFAULT 'other' CHECK (reason IN (
'coordinates',
'status',
'source',
'timestamp',
'details',
'duplicate',
'other'
)),
title TEXT NOT NULL,
evidence_text TEXT NOT NULL,
source_url TEXT,
media_url TEXT,
return_to TEXT,
page_url TEXT,
display_name TEXT,
email TEXT,
state TEXT NOT NULL DEFAULT 'queued' CHECK (state IN (
'queued',
'needs_review',
'accepted',
'rejected',
'merged'
)),
reviewer_notes TEXT,
user_agent TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_source_corrections_state_created_at
ON public.source_corrections (state, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_source_corrections_marker_id
ON public.source_corrections (marker_id)
WHERE marker_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_source_corrections_source_id
ON public.source_corrections (source_id)
WHERE source_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_source_corrections_submission_id
ON public.source_corrections (submission_id)
WHERE submission_id IS NOT NULL;
ALTER TABLE public.source_corrections ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can submit source corrections" ON public.source_corrections;
CREATE POLICY "Anyone can submit source corrections"
ON public.source_corrections FOR INSERT
TO anon
WITH CHECK (state = 'queued');
DROP POLICY IF EXISTS "Service role can manage source corrections" ON public.source_corrections;
CREATE POLICY "Service role can manage source corrections"
ON public.source_corrections FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS source_corrections_updated_at ON public.source_corrections;
CREATE TRIGGER source_corrections_updated_at
BEFORE UPDATE ON public.source_corrections
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
-- Leonida Intel: weekly email briefing send audit and idempotency ledger.
-- Safe to run more than once in the Supabase SQL editor.
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS public.email_briefing_sends (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
issue_key TEXT NOT NULL,
email TEXT NOT NULL,
interest TEXT NOT NULL DEFAULT 'weekly_briefing',
provider TEXT NOT NULL DEFAULT 'resend',
provider_message_id TEXT,
status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'sent', 'failed', 'skipped', 'unsubscribed', 'suppressed')),
error TEXT,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
queued_at TIMESTAMPTZ NOT NULL DEFAULT now(),
sent_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_email_briefing_sends_issue_email_interest
ON public.email_briefing_sends (issue_key, email, interest);
CREATE INDEX IF NOT EXISTS idx_email_briefing_sends_issue_status
ON public.email_briefing_sends (issue_key, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_briefing_sends_email_created_at
ON public.email_briefing_sends (email, created_at DESC);
ALTER TABLE public.email_briefing_sends ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage email briefing sends" ON public.email_briefing_sends;
CREATE POLICY "Service role can manage email briefing sends"
ON public.email_briefing_sends FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS email_briefing_sends_updated_at ON public.email_briefing_sends;
CREATE TRIGGER email_briefing_sends_updated_at
BEFORE UPDATE ON public.email_briefing_sends
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
-- Leonida Intel: first-party analytics and optimization signals.
-- Stores privacy-light product events so the private admin dashboard can
-- report traffic, funnels, blockers, and experiment signals without relying on
-- Cloudflare page-view estimates.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS public.site_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
event_name TEXT NOT NULL,
session_id TEXT NOT NULL,
visitor_id TEXT,
occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
path TEXT,
page_title TEXT,
referrer TEXT,
landing_path TEXT,
device_type TEXT,
locale TEXT,
timezone TEXT,
viewport_width INTEGER,
viewport_height INTEGER,
user_agent TEXT,
ip_hash TEXT,
utm_source TEXT,
utm_medium TEXT,
utm_campaign TEXT,
utm_content TEXT,
utm_term TEXT,
experiment_key TEXT,
variant_key TEXT,
properties JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_site_events_occurred_at
ON public.site_events (occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_site_events_event_time
ON public.site_events (event_name, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_site_events_path_time
ON public.site_events (path, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_site_events_session_time
ON public.site_events (session_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_site_events_visitor_time
ON public.site_events (visitor_id, occurred_at DESC)
WHERE visitor_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_site_events_experiment_time
ON public.site_events (experiment_key, variant_key, occurred_at DESC)
WHERE experiment_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_site_events_properties_gin
ON public.site_events USING GIN (properties);
ALTER TABLE public.site_events ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage site events" ON public.site_events;
CREATE POLICY "Service role can manage site events"
ON public.site_events FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP POLICY IF EXISTS "No public read access to site events" ON public.site_events;
CREATE POLICY "No public read access to site events"
ON public.site_events FOR SELECT
TO anon, authenticated
USING (false);
CREATE TABLE IF NOT EXISTS public.optimization_snapshots (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
window_days INTEGER NOT NULL DEFAULT 30,
generated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
page_views INTEGER NOT NULL DEFAULT 0,
sessions INTEGER NOT NULL DEFAULT 0,
visitors INTEGER NOT NULL DEFAULT 0,
affiliate_clicks INTEGER NOT NULL DEFAULT 0,
signup_events INTEGER NOT NULL DEFAULT 0,
pro_clicks INTEGER NOT NULL DEFAULT 0,
map_views INTEGER NOT NULL DEFAULT 0,
pc_lab_views INTEGER NOT NULL DEFAULT 0,
recommendations JSONB NOT NULL DEFAULT '[]'::jsonb,
experiment_results JSONB NOT NULL DEFAULT '[]'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_optimization_snapshots_generated_at
ON public.optimization_snapshots (generated_at DESC);
ALTER TABLE public.optimization_snapshots ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage optimization snapshots" ON public.optimization_snapshots;
CREATE POLICY "Service role can manage optimization snapshots"
ON public.optimization_snapshots FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
-- Admin users and roles for protected Leonida Intel admin consoles.
-- Safe to re-run.
create table if not exists public.admin_users (
id uuid primary key default gen_random_uuid(),
user_id uuid unique references auth.users(id) on delete cascade,
email text unique not null,
role text not null default 'viewer' check (role in ('owner', 'admin', 'editor', 'viewer')),
permissions text[] not null default '{}',
active boolean not null default true,
notes text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
last_seen_at timestamptz
);
do $$
begin
if to_regclass('public.admin_users_email_idx') is not null
and to_regclass('public.admin_users_email_lower_idx') is null then
alter index public.admin_users_email_idx rename to admin_users_email_lower_idx;
end if;
end;
$$;
create index if not exists admin_users_email_lower_idx on public.admin_users (lower(email));
create index if not exists admin_users_active_role_idx on public.admin_users (active, role);
alter table public.admin_users enable row level security;
-- No public policies are created. Server-side service-role code reads this table.
comment on table public.admin_users is 'Leonida admin role assignments. Use scripts/grant-admin-user.mjs to grant or update operators.';
do $$
begin
if not exists (
select 1
from pg_constraint
where conname = 'admin_users_permissions_allowed'
and conrelid = 'public.admin_users'::regclass
) then
alter table public.admin_users
add constraint admin_users_permissions_allowed
check (
permissions <@ array[
'read',
'view_analytics',
'manage_setup',
'manage_submissions',
'manage_sources',
'manage_growth',
'manage_briefings',
'manage_discord'
]::text[]
);
end if;
end;
$$;
create or replace function public.set_admin_users_updated_at()
returns trigger
language plpgsql
as $$
begin
new.updated_at = now();
return new;
end;
$$;
drop trigger if exists admin_users_set_updated_at on public.admin_users;
create trigger admin_users_set_updated_at
before update on public.admin_users
for each row
execute function public.set_admin_users_updated_at();
-- Leonida Intel: moderated forum system.
-- Public reads are limited to approved/featured threads. Public writes queue
-- for review; service role manages moderation.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS public.forum_categories (
slug TEXT PRIMARY KEY,
title TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
accent TEXT NOT NULL DEFAULT 'cyan',
sort_order INTEGER NOT NULL DEFAULT 100,
is_public BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS public.forum_threads (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
category_slug TEXT NOT NULL REFERENCES public.forum_categories(slug) ON DELETE RESTRICT,
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 6 AND 180),
body TEXT NOT NULL CHECK (char_length(body) BETWEEN 20 AND 8000),
display_name TEXT NOT NULL DEFAULT 'Community member',
email TEXT,
source_url TEXT,
marker_id TEXT,
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'featured', 'rejected', 'archived')),
labels TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
reviewer_notes TEXT,
ip_hash TEXT,
user_agent TEXT,
reply_count INTEGER NOT NULL DEFAULT 0,
reaction_count INTEGER NOT NULL DEFAULT 0,
last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS public.forum_replies (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
thread_id UUID NOT NULL REFERENCES public.forum_threads(id) ON DELETE CASCADE,
body TEXT NOT NULL CHECK (char_length(body) BETWEEN 10 AND 5000),
display_name TEXT NOT NULL DEFAULT 'Community member',
email TEXT,
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
reviewer_notes TEXT,
ip_hash TEXT,
user_agent TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS public.forum_reactions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
thread_id UUID NOT NULL REFERENCES public.forum_threads(id) ON DELETE CASCADE,
reaction TEXT NOT NULL DEFAULT 'useful' CHECK (reaction IN ('useful', 'source_needed', 'map_this')),
session_hash TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (thread_id, reaction, session_hash)
);
INSERT INTO public.forum_categories (slug, title, description, accent, sort_order)
VALUES
('map-evidence', 'Map Evidence Desk', 'Trailer frames, official screenshots, source notes, marker disputes, and coordinate theories.', 'pink', 10),
('news-verification', 'News Verification', 'Discuss current reports, separate official material from weak claims, and challenge bad headlines.', 'cyan', 20),
('pc-lab', 'PC Lab', 'Hardware forecasts, monitor choices, expected settings, and upgrade planning while PC remains unannounced.', 'green', 30),
('pre-order-watch', 'Pre-order Watch', 'Wishlist links, retailer pages, edition rumours, and launch-prep deals without pretending pre-orders are live.', 'gold', 40),
('launch-week', 'Launch Week Command', 'Mission guide triage, spoiler-safe plans, checklist requests, and what needs mapping first at release.', 'violet', 50)
ON CONFLICT (slug) DO UPDATE SET
title = EXCLUDED.title,
description = EXCLUDED.description,
accent = EXCLUDED.accent,
sort_order = EXCLUDED.sort_order,
updated_at = now();
CREATE INDEX IF NOT EXISTS idx_forum_threads_public_activity
ON public.forum_threads (status, last_activity_at DESC);
CREATE INDEX IF NOT EXISTS idx_forum_threads_category_activity
ON public.forum_threads (category_slug, status, last_activity_at DESC);
CREATE INDEX IF NOT EXISTS idx_forum_threads_marker
ON public.forum_threads (marker_id)
WHERE marker_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forum_replies_thread_public
ON public.forum_replies (thread_id, status, created_at);
CREATE INDEX IF NOT EXISTS idx_forum_replies_status_created
ON public.forum_replies (status, created_at DESC);
ALTER TABLE public.forum_categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.forum_threads ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.forum_replies ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.forum_reactions ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read public forum categories" ON public.forum_categories;
CREATE POLICY "Anyone can read public forum categories"
ON public.forum_categories FOR SELECT
TO anon, authenticated
USING (is_public = true);
DROP POLICY IF EXISTS "Service role can manage forum categories" ON public.forum_categories;
CREATE POLICY "Service role can manage forum categories"
ON public.forum_categories FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP POLICY IF EXISTS "Anyone can read approved forum threads" ON public.forum_threads;
CREATE POLICY "Anyone can read approved forum threads"
ON public.forum_threads FOR SELECT
TO anon, authenticated
USING (status IN ('approved', 'featured'));
DROP POLICY IF EXISTS "Anyone can queue forum threads" ON public.forum_threads;
CREATE POLICY "Anyone can queue forum threads"
ON public.forum_threads FOR INSERT
TO anon, authenticated
WITH CHECK (status = 'pending');
DROP POLICY IF EXISTS "Service role can manage forum threads" ON public.forum_threads;
CREATE POLICY "Service role can manage forum threads"
ON public.forum_threads FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP POLICY IF EXISTS "Anyone can read approved forum replies" ON public.forum_replies;
CREATE POLICY "Anyone can read approved forum replies"
ON public.forum_replies FOR SELECT
TO anon, authenticated
USING (status = 'approved');
DROP POLICY IF EXISTS "Anyone can queue forum replies" ON public.forum_replies;
CREATE POLICY "Anyone can queue forum replies"
ON public.forum_replies FOR INSERT
TO anon, authenticated
WITH CHECK (status = 'pending');
DROP POLICY IF EXISTS "Service role can manage forum replies" ON public.forum_replies;
CREATE POLICY "Service role can manage forum replies"
ON public.forum_replies FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP POLICY IF EXISTS "Anyone can add forum reactions" ON public.forum_reactions;
CREATE POLICY "Anyone can add forum reactions"
ON public.forum_reactions FOR INSERT
TO anon, authenticated
WITH CHECK (true);
DROP POLICY IF EXISTS "Service role can manage forum reactions" ON public.forum_reactions;
CREATE POLICY "Service role can manage forum reactions"
ON public.forum_reactions FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS forum_categories_updated_at ON public.forum_categories;
CREATE TRIGGER forum_categories_updated_at
BEFORE UPDATE ON public.forum_categories
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
DROP TRIGGER IF EXISTS forum_threads_updated_at ON public.forum_threads;
CREATE TRIGGER forum_threads_updated_at
BEFORE UPDATE ON public.forum_threads
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
DROP TRIGGER IF EXISTS forum_replies_updated_at ON public.forum_replies;
CREATE TRIGGER forum_replies_updated_at
BEFORE UPDATE ON public.forum_replies
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
-- Leonida Intel: public community accounts, account-backed forum posts,
-- and account-synced atlas progress.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS public.profiles (
id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
subscription_tier TEXT DEFAULT 'free' CHECK (subscription_tier IN ('free', 'pro')),
display_name TEXT,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.profiles (id)
VALUES (NEW.id)
ON CONFLICT (id) DO NOTHING;
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_user();
ALTER TABLE public.profiles
ADD COLUMN IF NOT EXISTS username TEXT,
ADD COLUMN IF NOT EXISTS bio TEXT,
ADD COLUMN IF NOT EXISTS avatar_url TEXT,
ADD COLUMN IF NOT EXISTS forum_reputation INTEGER NOT NULL DEFAULT 0,
ADD COLUMN IF NOT EXISTS forum_threads_count INTEGER NOT NULL DEFAULT 0,
ADD COLUMN IF NOT EXISTS forum_replies_count INTEGER NOT NULL DEFAULT 0,
ADD COLUMN IF NOT EXISTS atlas_found_count INTEGER NOT NULL DEFAULT 0,
ADD COLUMN IF NOT EXISTS last_seen_at TIMESTAMPTZ;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can view own profile" ON public.profiles;
CREATE POLICY "Users can view own profile"
ON public.profiles FOR SELECT TO authenticated
USING ((select auth.uid()) = id);
DROP POLICY IF EXISTS "Users can update own profile" ON public.profiles;
CREATE POLICY "Users can update own profile"
ON public.profiles FOR UPDATE TO authenticated
USING ((select auth.uid()) = id)
WITH CHECK ((select auth.uid()) = id);
DROP POLICY IF EXISTS "Service role can manage profiles" ON public.profiles;
CREATE POLICY "Service role can manage profiles"
ON public.profiles FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS profiles_updated_at ON public.profiles;
CREATE TRIGGER profiles_updated_at
BEFORE UPDATE ON public.profiles
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE UNIQUE INDEX IF NOT EXISTS idx_profiles_username_unique
ON public.profiles (lower(username))
WHERE username IS NOT NULL AND username <> '';
ALTER TABLE public.forum_threads
ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS is_account_post BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE public.forum_replies
ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS is_account_post BOOLEAN NOT NULL DEFAULT false;
CREATE INDEX IF NOT EXISTS idx_forum_threads_user_activity
ON public.forum_threads (user_id, last_activity_at DESC)
WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forum_replies_user_created
ON public.forum_replies (user_id, created_at DESC)
WHERE user_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS public.atlas_user_progress (
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
marker_id TEXT NOT NULL CHECK (char_length(marker_id) BETWEEN 2 AND 120),
marked_found BOOLEAN NOT NULL DEFAULT true,
saved BOOLEAN NOT NULL DEFAULT false,
note TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, marker_id)
);
CREATE INDEX IF NOT EXISTS idx_atlas_user_progress_user_updated
ON public.atlas_user_progress (user_id, updated_at DESC);
ALTER TABLE public.atlas_user_progress ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can manage own atlas progress" ON public.atlas_user_progress;
CREATE POLICY "Users can manage own atlas progress"
ON public.atlas_user_progress FOR ALL
TO authenticated
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Service role can manage atlas progress" ON public.atlas_user_progress;
CREATE POLICY "Service role can manage atlas progress"
ON public.atlas_user_progress FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS atlas_user_progress_updated_at ON public.atlas_user_progress;
CREATE TRIGGER atlas_user_progress_updated_at
BEFORE UPDATE ON public.atlas_user_progress
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
-- Leonida Intel: compliance operations, privacy request ledger, and retention runs.
-- Safe to re-run.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DO $$
BEGIN
IF EXISTS (
SELECT 1
FROM pg_constraint
WHERE conname = 'admin_users_permissions_allowed'
AND conrelid = 'public.admin_users'::regclass
) THEN
ALTER TABLE public.admin_users DROP CONSTRAINT admin_users_permissions_allowed;
END IF;
ALTER TABLE public.admin_users
ADD CONSTRAINT admin_users_permissions_allowed
CHECK (
permissions <@ ARRAY[
'read',
'view_analytics',
'manage_setup',
'manage_submissions',
'manage_sources',
'manage_growth',
'manage_briefings',
'manage_discord',
'manage_privacy'
]::text[]
);
EXCEPTION
WHEN undefined_table THEN
NULL;
END;
$$;
CREATE TABLE IF NOT EXISTS public.compliance_requests (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
request_type TEXT NOT NULL DEFAULT 'privacy_other' CHECK (
request_type IN (
'privacy_access',
'privacy_delete',
'privacy_correction',
'privacy_export',
'account_delete',
'unsubscribe',
'copyright_takedown',
'copyright_counter_notice',
'accessibility',
'refund',
'community_report',
'legal_notice',
'privacy_other'
)
),
status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'in_review', 'waiting_user', 'resolved', 'rejected', 'spam')),
priority TEXT NOT NULL DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
email TEXT,
name TEXT,
subject TEXT NOT NULL DEFAULT '',
details TEXT NOT NULL DEFAULT '',
related_url TEXT,
user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
source TEXT NOT NULL DEFAULT 'website',
reviewer_notes TEXT,
ip_hash TEXT,
user_agent TEXT,
resolved_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_compliance_requests_status_created
ON public.compliance_requests (status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_compliance_requests_type_created
ON public.compliance_requests (request_type, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_compliance_requests_email_created
ON public.compliance_requests (lower(email), created_at DESC)
WHERE email IS NOT NULL;
ALTER TABLE public.compliance_requests ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage compliance requests" ON public.compliance_requests;
CREATE POLICY "Service role can manage compliance requests"
ON public.compliance_requests FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS compliance_requests_updated_at ON public.compliance_requests;
CREATE TRIGGER compliance_requests_updated_at
BEFORE UPDATE ON public.compliance_requests
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE TABLE IF NOT EXISTS public.retention_runs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
run_type TEXT NOT NULL DEFAULT 'scheduled_cleanup',
dry_run BOOLEAN NOT NULL DEFAULT true,
status TEXT NOT NULL DEFAULT 'started' CHECK (status IN ('started', 'completed', 'failed')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
completed_at TIMESTAMPTZ,
retention_policy JSONB NOT NULL DEFAULT '{}'::jsonb,
deleted_counts JSONB NOT NULL DEFAULT '{}'::jsonb,
error TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_retention_runs_started
ON public.retention_runs (started_at DESC);
ALTER TABLE public.retention_runs ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage retention runs" ON public.retention_runs;
CREATE POLICY "Service role can manage retention runs"
ON public.retention_runs FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
COMMENT ON TABLE public.compliance_requests IS 'Operator ledger for privacy, copyright, accessibility, refund, account deletion, unsubscribe, community report, and legal requests.';
COMMENT ON TABLE public.retention_runs IS 'Audit log for scheduled or manual retention cleanup passes.';
-- Lock public write tables behind the server-side API layer.
-- Public forms post to hardened Astro endpoints using service-role writes,
-- so anonymous clients should not be able to insert directly with the anon key.
ALTER TABLE public.email_subscribers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.growth_signups ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.forum_threads ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.forum_replies ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can subscribe" ON public.email_subscribers;
DROP POLICY IF EXISTS "Public can subscribe" ON public.email_subscribers;
DROP POLICY IF EXISTS "Anyone can join growth lists" ON public.growth_signups;
DROP POLICY IF EXISTS "Public can join growth lists" ON public.growth_signups;
DROP POLICY IF EXISTS "Anyone can queue forum threads" ON public.forum_threads;
DROP POLICY IF EXISTS "Public can queue forum threads" ON public.forum_threads;
DROP POLICY IF EXISTS "Authenticated users can queue forum threads" ON public.forum_threads;
DROP POLICY IF EXISTS "Anyone can queue forum replies" ON public.forum_replies;
DROP POLICY IF EXISTS "Public can queue forum replies" ON public.forum_replies;
DROP POLICY IF EXISTS "Authenticated users can queue forum replies" ON public.forum_replies;
DROP POLICY IF EXISTS "Service role can manage subscribers" ON public.email_subscribers;
CREATE POLICY "Service role can manage subscribers"
ON public.email_subscribers FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP POLICY IF EXISTS "Service role can manage growth signups" ON public.growth_signups;
CREATE POLICY "Service role can manage growth signups"
ON public.growth_signups FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP POLICY IF EXISTS "Service role can manage forum threads" ON public.forum_threads;
CREATE POLICY "Service role can manage forum threads"
ON public.forum_threads FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP POLICY IF EXISTS "Service role can manage forum replies" ON public.forum_replies;
CREATE POLICY "Service role can manage forum replies"
ON public.forum_replies FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
-- Leonida Intel: explicit email consent evidence on compatibility subscribers.
-- New signup APIs write to growth_signups first, but email_subscribers remains
-- a compatibility table for legacy dashboards and provider sync checks.
ALTER TABLE public.email_subscribers
ADD COLUMN IF NOT EXISTS consent BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN IF NOT EXISTS marketing_consent_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS consent_source TEXT,
ADD COLUMN IF NOT EXISTS consent_evidence JSONB NOT NULL DEFAULT '{}'::jsonb;
UPDATE public.email_subscribers
SET consent = true,
marketing_consent_at = COALESCE(marketing_consent_at, updated_at, subscribed_at, now()),
consent_source = COALESCE(consent_source, source, 'explicit-consent-migration')
WHERE consent = false
AND (
source ILIKE '%explicit-consent%'
OR source ILIKE '%weekly_briefing:%'
OR source ILIKE '%preorder_alerts:%'
OR source ILIKE '%pc_lab:%'
OR source ILIKE '%launch_alerts:%'
);
CREATE INDEX IF NOT EXISTS idx_email_subscribers_status_consent
ON public.email_subscribers (status, consent, subscribed_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_subscribers_marketing_consent_at
ON public.email_subscribers (marketing_consent_at DESC)
WHERE consent = true;
ALTER TABLE public.email_subscribers ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage subscribers" ON public.email_subscribers;
CREATE POLICY "Service role can manage subscribers"
ON public.email_subscribers FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
-- Leonida Intel: account-backed forum reputation, public profile fields,
-- reaction counters, and idempotent moderation bookkeeping.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
ALTER TABLE public.profiles
ADD COLUMN IF NOT EXISTS is_public BOOLEAN NOT NULL DEFAULT true,
ADD COLUMN IF NOT EXISTS trust_state TEXT NOT NULL DEFAULT 'new' CHECK (trust_state IN ('new', 'trusted', 'source_scout', 'moderator')),
ADD COLUMN IF NOT EXISTS moderation_state TEXT NOT NULL DEFAULT 'active' CHECK (moderation_state IN ('active', 'limited', 'suspended', 'banned')),
ADD COLUMN IF NOT EXISTS public_badges TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[];
CREATE TABLE IF NOT EXISTS public.forum_reputation_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
source_type TEXT NOT NULL CHECK (source_type IN ('thread', 'reply', 'reaction', 'moderation')),
source_id TEXT NOT NULL CHECK (char_length(source_id) BETWEEN 2 AND 160),
points INTEGER NOT NULL CHECK (points BETWEEN -500 AND 500),
reason TEXT NOT NULL DEFAULT 'forum contribution',
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (user_id, source_type, source_id)
);
CREATE INDEX IF NOT EXISTS idx_forum_reputation_events_user_created
ON public.forum_reputation_events (user_id, created_at DESC);
ALTER TABLE public.forum_reputation_events ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage forum reputation events" ON public.forum_reputation_events;
CREATE POLICY "Service role can manage forum reputation events"
ON public.forum_reputation_events FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
ALTER TABLE public.forum_reactions
ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_forum_threads_user_status
ON public.forum_threads (user_id, status)
WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forum_replies_user_status
ON public.forum_replies (user_id, status)
WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forum_reactions_thread
ON public.forum_reactions (thread_id, reaction);
CREATE OR REPLACE FUNCTION public.recalculate_forum_profile(target_user_id UUID)
RETURNS VOID AS $$
DECLARE
thread_total INTEGER;
reply_total INTEGER;
rep_total INTEGER;
next_trust TEXT;
next_badges TEXT[];
BEGIN
IF target_user_id IS NULL THEN
RETURN;
END IF;
SELECT count(*) INTO thread_total
FROM public.forum_threads
WHERE user_id = target_user_id
AND status IN ('approved', 'featured');
SELECT count(*) INTO reply_total
FROM public.forum_replies
WHERE user_id = target_user_id
AND status = 'approved';
SELECT COALESCE(sum(points), 0)::INTEGER INTO rep_total
FROM public.forum_reputation_events
WHERE user_id = target_user_id;
IF rep_total >= 250 OR (thread_total + reply_total) >= 60 THEN
next_trust := 'source_scout';
ELSIF rep_total >= 75 OR (thread_total + reply_total) >= 20 THEN
next_trust := 'trusted';
ELSE
next_trust := 'new';
END IF;
next_badges := ARRAY[]::TEXT[];
IF thread_total > 0 THEN
next_badges := array_append(next_badges, 'Thread author');
END IF;
IF reply_total >= 5 THEN
next_badges := array_append(next_badges, 'Active reviewer');
END IF;
IF rep_total >= 75 THEN
next_badges := array_append(next_badges, 'Source scout');
END IF;
UPDATE public.profiles
SET forum_threads_count = thread_total,
forum_replies_count = reply_total,
forum_reputation = rep_total,
trust_state = next_trust,
public_badges = next_badges,
updated_at = now()
WHERE id = target_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
CREATE OR REPLACE FUNCTION public.award_forum_reputation(
target_user_id UUID,
source_type_value TEXT,
source_id_value TEXT,
points_value INTEGER,
reason_value TEXT DEFAULT 'forum contribution',
actor_user_id UUID DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
IF target_user_id IS NULL THEN
RETURN;
END IF;
INSERT INTO public.forum_reputation_events (user_id, source_type, source_id, points, reason, created_by)
VALUES (target_user_id, source_type_value, source_id_value, points_value, COALESCE(reason_value, 'forum contribution'), actor_user_id)
ON CONFLICT (user_id, source_type, source_id) DO NOTHING;
PERFORM public.recalculate_forum_profile(target_user_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
CREATE OR REPLACE FUNCTION public.moderate_forum_item(
item_type TEXT,
item_id_value TEXT,
next_status TEXT,
reviewer_note TEXT DEFAULT NULL,
actor_user_id UUID DEFAULT NULL
)
RETURNS JSONB AS $$
DECLARE
target_user_id UUID;
parent_thread_id UUID;
old_status TEXT;
visible_statuses TEXT[] := ARRAY['approved', 'featured'];
BEGIN
IF item_type = 'thread' THEN
SELECT user_id, status INTO target_user_id, old_status
FROM public.forum_threads
WHERE id::TEXT = item_id_value;
IF NOT FOUND THEN
RAISE EXCEPTION 'thread not found';
END IF;
UPDATE public.forum_threads
SET status = next_status,
reviewer_notes = COALESCE(NULLIF(reviewer_note, ''), 'Marked ' || next_status || ' from forum moderation.'),
last_activity_at = now(),
updated_at = now()
WHERE id::TEXT = item_id_value;
IF target_user_id IS NOT NULL AND next_status = ANY(visible_statuses) AND NOT old_status = ANY(visible_statuses) THEN
PERFORM public.award_forum_reputation(target_user_id, 'thread', item_id_value, CASE WHEN next_status = 'featured' THEN 16 ELSE 10 END, 'approved forum thread', actor_user_id);
ELSIF target_user_id IS NOT NULL AND NOT next_status = ANY(visible_statuses) THEN
DELETE FROM public.forum_reputation_events
WHERE user_id = target_user_id AND source_type = 'thread' AND source_id = item_id_value;
PERFORM public.recalculate_forum_profile(target_user_id);
END IF;
RETURN jsonb_build_object('id', item_id_value, 'type', item_type, 'status', next_status);
END IF;
IF item_type = 'reply' THEN
SELECT user_id, status, thread_id INTO target_user_id, old_status, parent_thread_id
FROM public.forum_replies
WHERE id::TEXT = item_id_value;
IF NOT FOUND THEN
RAISE EXCEPTION 'reply not found';
END IF;
UPDATE public.forum_replies
SET status = next_status,
reviewer_notes = COALESCE(NULLIF(reviewer_note, ''), 'Marked ' || next_status || ' from forum moderation.'),
updated_at = now()
WHERE id::TEXT = item_id_value;
IF target_user_id IS NOT NULL AND next_status = 'approved' AND old_status <> 'approved' THEN
PERFORM public.award_forum_reputation(target_user_id, 'reply', item_id_value, 4, 'approved forum reply', actor_user_id);
ELSIF target_user_id IS NOT NULL AND next_status <> 'approved' THEN
DELETE FROM public.forum_reputation_events
WHERE user_id = target_user_id AND source_type = 'reply' AND source_id = item_id_value;
PERFORM public.recalculate_forum_profile(target_user_id);
END IF;
IF parent_thread_id IS NOT NULL THEN
UPDATE public.forum_threads
SET reply_count = (
SELECT count(*)
FROM public.forum_replies
WHERE thread_id = parent_thread_id
AND status = 'approved'
),
last_activity_at = now(),
updated_at = now()
WHERE id = parent_thread_id;
END IF;
RETURN jsonb_build_object('id', item_id_value, 'type', item_type, 'status', next_status, 'thread_id', parent_thread_id);
END IF;
RAISE EXCEPTION 'unsupported item type';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
CREATE OR REPLACE FUNCTION public.apply_forum_reaction(
thread_id_value TEXT,
reaction_value TEXT,
user_id_value UUID DEFAULT NULL,
session_hash_value TEXT DEFAULT NULL
)
RETURNS JSONB AS $$
DECLARE
clean_reaction TEXT;
clean_session TEXT;
count_total INTEGER;
BEGIN
clean_reaction := CASE
WHEN reaction_value IN ('useful', 'source_needed', 'map_this') THEN reaction_value
ELSE 'useful'
END;
clean_session := COALESCE(NULLIF(session_hash_value, ''), COALESCE(user_id_value::TEXT, gen_random_uuid()::TEXT));
INSERT INTO public.forum_reactions (thread_id, reaction, user_id, session_hash)
VALUES (thread_id_value::UUID, clean_reaction, user_id_value, clean_session)
ON CONFLICT (thread_id, reaction, session_hash) DO NOTHING;
SELECT count(*) INTO count_total
FROM public.forum_reactions
WHERE thread_id = thread_id_value::UUID;
UPDATE public.forum_threads
SET reaction_count = count_total,
updated_at = now()
WHERE id = thread_id_value::UUID;
RETURN jsonb_build_object('thread_id', thread_id_value, 'reaction_count', count_total, 'reaction', clean_reaction);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
-- Leonida Intel: persistent Evidence OS product loop.
-- Adds account-synced marker watchlists, evidence claim queues, guarded news
-- review rows, saved PC build profiles, and Pro workspace snapshots.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
ALTER TABLE public.atlas_user_progress
ADD COLUMN IF NOT EXISTS alert_enabled BOOLEAN NOT NULL DEFAULT true,
ADD COLUMN IF NOT EXISTS watch_reason TEXT,
ADD COLUMN IF NOT EXISTS last_alerted_at TIMESTAMPTZ;
CREATE INDEX IF NOT EXISTS idx_atlas_user_progress_saved_alerts
ON public.atlas_user_progress (user_id, saved, alert_enabled, updated_at DESC)
WHERE saved = true;
CREATE TABLE IF NOT EXISTS public.evidence_claims (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
marker_id TEXT NOT NULL CHECK (char_length(marker_id) BETWEEN 2 AND 120),
evidence_item_id TEXT,
source_url TEXT,
source_type TEXT NOT NULL DEFAULT 'community_mapping',
claim_text TEXT NOT NULL CHECK (char_length(claim_text) BETWEEN 20 AND 8000),
proposed_status TEXT,
proposed_confidence INTEGER CHECK (proposed_confidence IS NULL OR proposed_confidence BETWEEN 0 AND 100),
confidence_delta INTEGER CHECK (confidence_delta IS NULL OR confidence_delta BETWEEN -100 AND 100),
review_state TEXT NOT NULL DEFAULT 'pending' CHECK (review_state IN ('pending', 'needs_review', 'accepted', 'rejected', 'retracted', 'duplicate')),
review_priority TEXT NOT NULL DEFAULT 'normal' CHECK (review_priority IN ('low', 'normal', 'high', 'urgent')),
display_name TEXT,
email TEXT,
submitted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
reviewer_notes TEXT,
reviewed_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
reviewed_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_evidence_claims_marker_state
ON public.evidence_claims (marker_id, review_state, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_evidence_claims_review_queue
ON public.evidence_claims (review_state, review_priority, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_evidence_claims_submitter
ON public.evidence_claims (submitted_by, created_at DESC)
WHERE submitted_by IS NOT NULL;
ALTER TABLE public.evidence_claims ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read accepted evidence claims" ON public.evidence_claims;
CREATE POLICY "Anyone can read accepted evidence claims"
ON public.evidence_claims FOR SELECT
TO anon, authenticated
USING (review_state = 'accepted');
DROP POLICY IF EXISTS "Users can read own evidence claims" ON public.evidence_claims;
CREATE POLICY "Users can read own evidence claims"
ON public.evidence_claims FOR SELECT
TO authenticated
USING ((select auth.uid()) = submitted_by);
DROP POLICY IF EXISTS "Users can queue own evidence claims" ON public.evidence_claims;
CREATE POLICY "Users can queue own evidence claims"
ON public.evidence_claims FOR INSERT
TO authenticated
WITH CHECK ((select auth.uid()) = submitted_by AND review_state IN ('pending', 'needs_review'));
DROP POLICY IF EXISTS "Service role can manage evidence claims" ON public.evidence_claims;
CREATE POLICY "Service role can manage evidence claims"
ON public.evidence_claims FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS evidence_claims_updated_at ON public.evidence_claims;
CREATE TRIGGER evidence_claims_updated_at
BEFORE UPDATE ON public.evidence_claims
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE TABLE IF NOT EXISTS public.marker_change_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
marker_id TEXT NOT NULL CHECK (char_length(marker_id) BETWEEN 2 AND 120),
event_type TEXT NOT NULL CHECK (event_type IN ('source_added', 'status_changed', 'confidence_changed', 'correction_accepted', 'claim_rejected', 'note')),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 4 AND 220),
body TEXT NOT NULL DEFAULT '',
status_after TEXT,
confidence_after INTEGER CHECK (confidence_after IS NULL OR confidence_after BETWEEN 0 AND 100),
source_claim_id UUID REFERENCES public.evidence_claims(id) ON DELETE SET NULL,
is_public BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_marker_change_events_marker_created
ON public.marker_change_events (marker_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_marker_change_events_public_created
ON public.marker_change_events (is_public, created_at DESC);
ALTER TABLE public.marker_change_events ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read public marker change events" ON public.marker_change_events;
CREATE POLICY "Anyone can read public marker change events"
ON public.marker_change_events FOR SELECT
TO anon, authenticated
USING (is_public = true);
DROP POLICY IF EXISTS "Service role can manage marker change events" ON public.marker_change_events;
CREATE POLICY "Service role can manage marker change events"
ON public.marker_change_events FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
CREATE TABLE IF NOT EXISTS public.news_review_queue (
id TEXT PRIMARY KEY CHECK (char_length(id) BETWEEN 8 AND 180),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 8 AND 260),
source TEXT NOT NULL DEFAULT 'unknown',
source_url TEXT,
risk_score INTEGER NOT NULL DEFAULT 50 CHECK (risk_score BETWEEN 0 AND 100),
state TEXT NOT NULL DEFAULT 'queued' CHECK (state IN ('queued', 'approved', 'rejected', 'needs_rewrite', 'duplicate')),
reasons TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
raw_payload JSONB NOT NULL DEFAULT '{}'::JSONB,
reviewed_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
reviewed_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_news_review_queue_state_created
ON public.news_review_queue (state, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_news_review_queue_risk
ON public.news_review_queue (risk_score DESC, created_at DESC);
ALTER TABLE public.news_review_queue ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage news review queue" ON public.news_review_queue;
CREATE POLICY "Service role can manage news review queue"
ON public.news_review_queue FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS news_review_queue_updated_at ON public.news_review_queue;
CREATE TRIGGER news_review_queue_updated_at
BEFORE UPDATE ON public.news_review_queue
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE TABLE IF NOT EXISTS public.pc_build_profiles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
label TEXT NOT NULL DEFAULT 'Main build',
cpu TEXT,
gpu TEXT,
ram_gb INTEGER CHECK (ram_gb IS NULL OR ram_gb BETWEEN 4 AND 512),
resolution TEXT,
monitor_refresh_hz INTEGER CHECK (monitor_refresh_hz IS NULL OR monitor_refresh_hz BETWEEN 30 AND 1000),
target_quality TEXT,
ray_tracing BOOLEAN NOT NULL DEFAULT false,
path_tracing BOOLEAN NOT NULL DEFAULT false,
forecast_json JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_pc_build_profiles_user_updated
ON public.pc_build_profiles (user_id, updated_at DESC);
ALTER TABLE public.pc_build_profiles ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can manage own PC build profiles" ON public.pc_build_profiles;
CREATE POLICY "Users can manage own PC build profiles"
ON public.pc_build_profiles FOR ALL
TO authenticated
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Service role can manage PC build profiles" ON public.pc_build_profiles;
CREATE POLICY "Service role can manage PC build profiles"
ON public.pc_build_profiles FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS pc_build_profiles_updated_at ON public.pc_build_profiles;
CREATE TRIGGER pc_build_profiles_updated_at
BEFORE UPDATE ON public.pc_build_profiles
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE TABLE IF NOT EXISTS public.pro_workspace_snapshots (
user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
snapshot JSONB NOT NULL DEFAULT '{}'::JSONB,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.pro_workspace_snapshots ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can manage own Pro workspace snapshot" ON public.pro_workspace_snapshots;
CREATE POLICY "Users can manage own Pro workspace snapshot"
ON public.pro_workspace_snapshots FOR ALL
TO authenticated
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Service role can manage Pro workspace snapshots" ON public.pro_workspace_snapshots;
CREATE POLICY "Service role can manage Pro workspace snapshots"
ON public.pro_workspace_snapshots FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
CREATE TABLE IF NOT EXISTS public.user_alert_preferences (
user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
marker_alerts BOOLEAN NOT NULL DEFAULT true,
briefing_alerts BOOLEAN NOT NULL DEFAULT true,
preorder_alerts BOOLEAN NOT NULL DEFAULT false,
pc_alerts BOOLEAN NOT NULL DEFAULT false,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.user_alert_preferences ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can manage own alert preferences" ON public.user_alert_preferences;
CREATE POLICY "Users can manage own alert preferences"
ON public.user_alert_preferences FOR ALL
TO authenticated
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Service role can manage alert preferences" ON public.user_alert_preferences;
CREATE POLICY "Service role can manage alert preferences"
ON public.user_alert_preferences FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS user_alert_preferences_updated_at ON public.user_alert_preferences;
CREATE TRIGGER user_alert_preferences_updated_at
BEFORE UPDATE ON public.user_alert_preferences
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
-- Leonida Intel: entitlement, moderation and public-evidence promotion hardening.
-- Safe to run more than once in the Supabase SQL editor.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
ALTER TABLE public.pro_entitlements
ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS source TEXT NOT NULL DEFAULT 'stripe',
ADD COLUMN IF NOT EXISTS last_verified_at TIMESTAMPTZ DEFAULT now();
CREATE INDEX IF NOT EXISTS idx_pro_entitlements_user_status
ON public.pro_entitlements (user_id, status, updated_at DESC)
WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_pro_entitlements_email_status
ON public.pro_entitlements (lower(email), status, updated_at DESC);
DROP POLICY IF EXISTS "Anyone can read accepted evidence claims" ON public.evidence_claims;
COMMENT ON TABLE public.evidence_claims IS
'Private review table. Public accepted claim reads must go through the server API so submitter emails and reviewer notes are scrubbed.';
CREATE TABLE IF NOT EXISTS public.marker_public_overlays (
marker_id TEXT PRIMARY KEY CHECK (char_length(marker_id) BETWEEN 2 AND 120),
status TEXT CHECK (status IN ('confirmed', 'reported', 'community', 'rumour', 'prediction', 'debunked', 'speculative')),
confidence INTEGER CHECK (confidence BETWEEN 0 AND 100),
summary TEXT,
source_claim_ids UUID[] NOT NULL DEFAULT ARRAY[]::UUID[],
updated_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_marker_public_overlays_updated
ON public.marker_public_overlays (updated_at DESC);
ALTER TABLE public.marker_public_overlays ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read public marker overlays" ON public.marker_public_overlays;
CREATE POLICY "Anyone can read public marker overlays"
ON public.marker_public_overlays FOR SELECT
TO anon, authenticated
USING (true);
DROP POLICY IF EXISTS "Service role can manage marker overlays" ON public.marker_public_overlays;
CREATE POLICY "Service role can manage marker overlays"
ON public.marker_public_overlays FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS marker_public_overlays_updated_at ON public.marker_public_overlays;
CREATE TRIGGER marker_public_overlays_updated_at
BEFORE UPDATE ON public.marker_public_overlays
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.forum_reports (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
thread_id UUID REFERENCES public.forum_threads(id) ON DELETE CASCADE,
reply_id UUID REFERENCES public.forum_replies(id) ON DELETE CASCADE,
reporter_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
reason TEXT NOT NULL DEFAULT 'other' CHECK (reason IN ('spam', 'abuse', 'ip', 'misinformation', 'spoiler', 'other')),
details TEXT,
status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'reviewed', 'dismissed', 'actioned')),
reviewer_notes TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CHECK (thread_id IS NOT NULL OR reply_id IS NOT NULL)
);
CREATE INDEX IF NOT EXISTS idx_forum_reports_status_created
ON public.forum_reports (status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_forum_reports_thread
ON public.forum_reports (thread_id)
WHERE thread_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forum_reports_reply
ON public.forum_reports (reply_id)
WHERE reply_id IS NOT NULL;
ALTER TABLE public.forum_reports ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Authenticated users can create forum reports" ON public.forum_reports;
CREATE POLICY "Authenticated users can create forum reports"
ON public.forum_reports FOR INSERT
TO authenticated
WITH CHECK ((select auth.uid()) = reporter_user_id);
DROP POLICY IF EXISTS "Service role can manage forum reports" ON public.forum_reports;
CREATE POLICY "Service role can manage forum reports"
ON public.forum_reports FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS forum_reports_updated_at ON public.forum_reports;
CREATE TRIGGER forum_reports_updated_at
BEFORE UPDATE ON public.forum_reports
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
-- Leonida Intel: persistent growth, content and community operating loop.
-- Safe to run more than once in the Supabase SQL editor.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS public.content_pipeline_assets (
id TEXT PRIMARY KEY CHECK (char_length(id) BETWEEN 4 AND 180),
candidate_id TEXT NOT NULL CHECK (char_length(candidate_id) BETWEEN 4 AND 180),
channel TEXT NOT NULL CHECK (channel IN ('shorts', 'youtube', 'news', 'briefing', 'discord', 'x', 'threads', 'seo', 'site')),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 4 AND 260),
canonical_path TEXT NOT NULL DEFAULT '/',
status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'drafted', 'reviewing', 'published', 'skipped', 'archived')),
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('urgent', 'high', 'medium', 'low')),
owner TEXT,
published_url TEXT,
last_copied_at TIMESTAMPTZ,
exported_at TIMESTAMPTZ,
feedback_summary TEXT,
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_content_pipeline_assets_status_priority
ON public.content_pipeline_assets (status, priority, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_content_pipeline_assets_candidate
ON public.content_pipeline_assets (candidate_id, channel);
ALTER TABLE public.content_pipeline_assets ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage content pipeline assets" ON public.content_pipeline_assets;
CREATE POLICY "Service role can manage content pipeline assets"
ON public.content_pipeline_assets FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS content_pipeline_assets_updated_at ON public.content_pipeline_assets;
CREATE TRIGGER content_pipeline_assets_updated_at
BEFORE UPDATE ON public.content_pipeline_assets
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.growth_action_items (
id TEXT PRIMARY KEY CHECK (char_length(id) BETWEEN 4 AND 180),
area TEXT NOT NULL DEFAULT 'Growth',
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('high', 'medium', 'low')),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 4 AND 260),
why TEXT NOT NULL DEFAULT '',
task TEXT NOT NULL DEFAULT '',
target_url TEXT NOT NULL DEFAULT '/',
metric TEXT NOT NULL DEFAULT '',
impact TEXT NOT NULL DEFAULT 'medium' CHECK (impact IN ('high', 'medium', 'low')),
effort TEXT NOT NULL DEFAULT 'medium' CHECK (effort IN ('low', 'medium', 'high')),
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'assigned', 'done', 'snoozed', 'archived')),
owner TEXT,
source_snapshot_at TIMESTAMPTZ,
due_at TIMESTAMPTZ,
completed_at TIMESTAMPTZ,
snoozed_until TIMESTAMPTZ,
created_from TEXT NOT NULL DEFAULT 'analytics',
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_growth_action_items_status_priority
ON public.growth_action_items (status, priority, due_at NULLS LAST, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_growth_action_items_area_status
ON public.growth_action_items (area, status, updated_at DESC);
ALTER TABLE public.growth_action_items ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage growth action items" ON public.growth_action_items;
CREATE POLICY "Service role can manage growth action items"
ON public.growth_action_items FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS growth_action_items_updated_at ON public.growth_action_items;
CREATE TRIGGER growth_action_items_updated_at
BEFORE UPDATE ON public.growth_action_items
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.community_challenges (
id TEXT PRIMARY KEY CHECK (char_length(id) BETWEEN 4 AND 180),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 4 AND 260),
category_slug TEXT NOT NULL,
marker_id TEXT,
source_url TEXT,
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('urgent', 'high', 'medium', 'low')),
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'watching', 'reviewing', 'closed', 'archived')),
reward_label TEXT,
public_summary TEXT NOT NULL DEFAULT '',
due_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_community_challenges_status_priority
ON public.community_challenges (status, priority, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_community_challenges_marker
ON public.community_challenges (marker_id)
WHERE marker_id IS NOT NULL;
ALTER TABLE public.community_challenges ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read open community challenges" ON public.community_challenges;
CREATE POLICY "Anyone can read open community challenges"
ON public.community_challenges FOR SELECT
TO anon, authenticated
USING (status IN ('open', 'watching', 'reviewing'));
DROP POLICY IF EXISTS "Service role can manage community challenges" ON public.community_challenges;
CREATE POLICY "Service role can manage community challenges"
ON public.community_challenges FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS community_challenges_updated_at ON public.community_challenges;
CREATE TRIGGER community_challenges_updated_at
BEFORE UPDATE ON public.community_challenges
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.map_collections (
id TEXT PRIMARY KEY CHECK (char_length(id) BETWEEN 4 AND 180),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 4 AND 260),
description TEXT NOT NULL DEFAULT '',
marker_ids TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
route_ids TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
status TEXT NOT NULL DEFAULT 'public' CHECK (status IN ('public', 'draft', 'archived')),
confidence_floor INTEGER NOT NULL DEFAULT 0 CHECK (confidence_floor BETWEEN 0 AND 100),
source_policy TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_map_collections_status_updated
ON public.map_collections (status, updated_at DESC);
ALTER TABLE public.map_collections ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read public map collections" ON public.map_collections;
CREATE POLICY "Anyone can read public map collections"
ON public.map_collections FOR SELECT
TO anon, authenticated
USING (status = 'public');
DROP POLICY IF EXISTS "Service role can manage map collections" ON public.map_collections;
CREATE POLICY "Service role can manage map collections"
ON public.map_collections FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS map_collections_updated_at ON public.map_collections;
CREATE TRIGGER map_collections_updated_at
BEFORE UPDATE ON public.map_collections
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.seo_authority_pages (
path TEXT PRIMARY KEY CHECK (path ~ '^/'),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 4 AND 260),
focus_keyword TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'live' CHECK (status IN ('live', 'needs_review', 'draft', 'archived')),
intent TEXT NOT NULL DEFAULT '',
source_policy TEXT NOT NULL DEFAULT '',
last_reviewed_at TIMESTAMPTZ,
next_review_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_seo_authority_pages_status_review
ON public.seo_authority_pages (status, next_review_at NULLS FIRST, updated_at DESC);
ALTER TABLE public.seo_authority_pages ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read live authority pages" ON public.seo_authority_pages;
CREATE POLICY "Anyone can read live authority pages"
ON public.seo_authority_pages FOR SELECT
TO anon, authenticated
USING (status = 'live');
DROP POLICY IF EXISTS "Service role can manage authority pages" ON public.seo_authority_pages;
CREATE POLICY "Service role can manage authority pages"
ON public.seo_authority_pages FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS seo_authority_pages_updated_at ON public.seo_authority_pages;
CREATE TRIGGER seo_authority_pages_updated_at
BEFORE UPDATE ON public.seo_authority_pages
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
INSERT INTO public.community_challenges (id, title, category_slug, marker_id, priority, status, reward_label, public_summary)
VALUES
('challenge-vice-city-grid', 'Vice City grid source check', 'map-evidence', 'vice-city-core', 'urgent', 'open', 'Source-scout reputation', 'Attach timestamps, official screenshots, or source notes before stronger Vice City map claims are promoted.'),
('challenge-port-gellhorn-strip', 'Port Gellhorn motel strip route pass', 'map-evidence', 'gellhorn-motel-strip', 'high', 'open', 'Map reviewer credit', 'Separate visible motel/coast evidence from route guesses for the Port Gellhorn layer.'),
('challenge-retailer-watch', 'Retailer watch wording check', 'pre-order-watch', NULL, 'high', 'watching', 'Retail watcher credit', 'Track wishlist and retailer pages without treating prices or editions as official.')
ON CONFLICT (id) DO UPDATE SET
title = EXCLUDED.title,
category_slug = EXCLUDED.category_slug,
marker_id = EXCLUDED.marker_id,
priority = EXCLUDED.priority,
status = EXCLUDED.status,
reward_label = EXCLUDED.reward_label,
public_summary = EXCLUDED.public_summary,
updated_at = now();
INSERT INTO public.content_pipeline_assets (id, candidate_id, channel, title, canonical_path, status, priority, metadata)
VALUES
('pipeline-official-anchor-short', 'official-anchor-run', 'shorts', 'Official anchor run short', '/gta-6-interactive-map/', 'queued', 'high', '{"brief":"Turn the official anchor run into a short script with source labels visible."}'::JSONB),
('pipeline-vice-city-grid-article', 'vice-city-grid-check', 'news', 'Vice City grid source check', '/gta-6-vice-city-locations/', 'queued', 'high', '{"brief":"Explain what is visible, what is inferred, and where the community can challenge the map layer."}'::JSONB),
('pipeline-retailer-watch-briefing', 'retailer-watch', 'briefing', 'Pre-order watch briefing item', '/pre-order/', 'queued', 'medium', '{"brief":"Keep wishlist and affiliate links useful without implying official pre-orders are live."}'::JSONB),
('pipeline-pc-lab-evergreen', 'pc-lab-forecast', 'seo', 'PC Lab forecast refresh', '/pc-checker/', 'queued', 'medium', '{"brief":"Refresh forecast wording, upgrade suggestions, and PC-not-announced guardrails."}'::JSONB)
ON CONFLICT (id) DO UPDATE SET
candidate_id = EXCLUDED.candidate_id,
channel = EXCLUDED.channel,
title = EXCLUDED.title,
canonical_path = EXCLUDED.canonical_path,
priority = EXCLUDED.priority,
metadata = EXCLUDED.metadata,
updated_at = now();
INSERT INTO public.growth_action_items (id, area, priority, title, why, task, target_url, metric, impact, effort, created_from, metadata)
VALUES
('growth-map-to-briefing', 'Map', 'high', 'Turn map sessions into briefing signups', 'Map visitors show high intent, but they need a clearer next step after checking markers.', 'Add or test a briefing capture block near source-backed marker panels and curated runs.', '/map/', 'briefing_signup_rate', 'high', 'medium', 'operating-loop-seed', '{"cadence":"weekly review"}'::JSONB),
('growth-forum-source-loop', 'Community', 'high', 'Move forum threads into source review', 'A forum only compounds if good posts become map evidence, briefs, and source notes.', 'Promote the best challenge threads into the source archive and weekly briefing workflow.', '/forum/', 'forum_to_source_promotions', 'high', 'medium', 'operating-loop-seed', '{"cadence":"weekly review"}'::JSONB),
('growth-preorder-affiliate-path', 'Affiliate', 'medium', 'Reduce friction on retailer watch clicks', 'Retailer-watch traffic needs clear platform and region paths before purchase links exist.', 'Test region-aware platform cards and make disclosure visible without weakening the CTA.', '/pre-order/', 'affiliate_click_rate', 'medium', 'low', 'operating-loop-seed', '{"cadence":"twice monthly"}'::JSONB),
('growth-authority-refresh', 'SEO', 'medium', 'Refresh authority pages from source changes', 'High-intent pages need to stay current as Rockstar or credible reports change the baseline.', 'Review authority pages after every source archive update and link back to marker/evidence trails.', '/gta-6-interactive-map/', 'organic_landing_page_engagement', 'high', 'medium', 'operating-loop-seed', '{"cadence":"after source updates"}'::JSONB)
ON CONFLICT (id) DO UPDATE SET
area = EXCLUDED.area,
priority = EXCLUDED.priority,
title = EXCLUDED.title,
why = EXCLUDED.why,
task = EXCLUDED.task,
target_url = EXCLUDED.target_url,
metric = EXCLUDED.metric,
impact = EXCLUDED.impact,
effort = EXCLUDED.effort,
created_from = EXCLUDED.created_from,
metadata = EXCLUDED.metadata,
updated_at = now();
INSERT INTO public.map_collections (id, title, description, marker_ids, route_ids, status, confidence_floor, source_policy)
VALUES
('collection-official-anchors', 'Official anchor run', 'The cleanest first-pass route through confirmed and trailer-seen map anchors.', ARRAY['vice-city-core','vice-beach-strip','port-gellhorn','grassrivers-wetlands','leonida-keys-causeway'], ARRAY['route-state-spine','route-keys-causeway'], 'public', 70, 'Confirmed or trailer-seen only, with source notes visible.'),
('collection-community-reconstruction', 'Community reconstruction run', 'High-value fan mapping that stays useful while remaining clearly labelled.', ARRAY['intel-mapping-vice-city-grid','intel-keys-causeway','intel-state-spine','intel-hamlet'], ARRAY['route-state-spine','route-ambrosia-kalaga'], 'public', 40, 'Community-mapped and speculative labels stay visible.'),
('collection-launch-week', 'Launch-week spoiler-safe run', 'A planned route for launch-week map triage, spoiler-safe labels, and correction paths.', ARRAY['vice-city-core','port-gellhorn','grassrivers-wetlands','leonida-keys-causeway'], ARRAY['route-state-spine','route-keys-causeway'], 'public', 50, 'No mission spoiler in public labels without a warning.')
ON CONFLICT (id) DO UPDATE SET
title = EXCLUDED.title,
description = EXCLUDED.description,
marker_ids = EXCLUDED.marker_ids,
route_ids = EXCLUDED.route_ids,
status = EXCLUDED.status,
confidence_floor = EXCLUDED.confidence_floor,
source_policy = EXCLUDED.source_policy,
updated_at = now();
INSERT INTO public.seo_authority_pages (path, title, focus_keyword, status, intent, source_policy, last_reviewed_at)
VALUES
('/gta-6-interactive-map/', 'GTA 6 Interactive Map', 'gta 6 interactive map', 'live', 'MapGenie-style pre-release map search intent.', 'Fan atlas until Rockstar publishes final coordinates.', now()),
('/gta-6-leonida-map/', 'GTA 6 Leonida Map', 'gta 6 leonida map', 'live', 'Leonida geography and region search intent.', 'Confirmed regions separate from exact road and POI estimates.', now()),
('/gta-6-vice-city-locations/', 'GTA 6 Vice City Locations', 'gta 6 vice city locations', 'live', 'Vice City district and landmark search intent.', 'Visible landmarks and district guesses stay separated.', now()),
('/gta-6-trailer-2-map-analysis/', 'GTA 6 Trailer 2 Map Analysis', 'gta 6 trailer 2 map analysis', 'live', 'Trailer timestamp and frame-analysis search intent.', 'A frame can support a clue without proving final coordinates.', now()),
('/gta-6-all-characters/', 'GTA 6 Characters', 'gta 6 characters', 'live', 'Character database search intent.', 'Confirmed profiles, reports, rumours, and predictions are labelled.', now()),
('/gta-6-vehicles-list/', 'GTA 6 Vehicles List', 'gta 6 vehicles list', 'live', 'Vehicle database and trailer-seen vehicle search intent.', 'Trailer-seen and indexed vehicles are not final roster claims.', now())
ON CONFLICT (path) DO UPDATE SET
title = EXCLUDED.title,
focus_keyword = EXCLUDED.focus_keyword,
status = EXCLUDED.status,
intent = EXCLUDED.intent,
source_policy = EXCLUDED.source_policy,
last_reviewed_at = EXCLUDED.last_reviewed_at,
updated_at = now();
-- Leonida Intel: mission-control persistence for newsroom, evidence graph,
-- forum subscriptions, social jobs, launch ops, and app-side durable limits.
-- Safe to run more than once.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS public.news_source_runs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
run_type TEXT NOT NULL DEFAULT 'cron' CHECK (run_type IN ('cron', 'admin', 'manual', 'dry_run')),
mode TEXT NOT NULL DEFAULT 'scan' CHECK (mode IN ('scan', 'dry_run', 'publish')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
finished_at TIMESTAMPTZ,
source_count INTEGER NOT NULL DEFAULT 0 CHECK (source_count >= 0),
sources_online INTEGER NOT NULL DEFAULT 0 CHECK (sources_online >= 0),
fetched_count INTEGER NOT NULL DEFAULT 0 CHECK (fetched_count >= 0),
accepted_count INTEGER NOT NULL DEFAULT 0 CHECK (accepted_count >= 0),
queued_count INTEGER NOT NULL DEFAULT 0 CHECK (queued_count >= 0),
published_count INTEGER NOT NULL DEFAULT 0 CHECK (published_count >= 0),
failed_count INTEGER NOT NULL DEFAULT 0 CHECK (failed_count >= 0),
indexnow_status TEXT,
community_status TEXT,
payload JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_news_source_runs_started
ON public.news_source_runs (started_at DESC);
CREATE INDEX IF NOT EXISTS idx_news_source_runs_mode
ON public.news_source_runs (mode, started_at DESC);
ALTER TABLE public.news_source_runs ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage news source runs" ON public.news_source_runs;
CREATE POLICY "Service role can manage news source runs"
ON public.news_source_runs FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS news_source_runs_updated_at ON public.news_source_runs;
CREATE TRIGGER news_source_runs_updated_at
BEFORE UPDATE ON public.news_source_runs
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.social_distribution_jobs (
id TEXT PRIMARY KEY CHECK (char_length(id) BETWEEN 4 AND 180),
candidate_id TEXT NOT NULL CHECK (char_length(candidate_id) BETWEEN 4 AND 180),
channel TEXT NOT NULL CHECK (channel IN ('shorts', 'youtube', 'x', 'threads', 'tiktok', 'discord', 'briefing', 'site')),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 4 AND 260),
canonical_path TEXT NOT NULL DEFAULT '/',
status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'drafted', 'reviewing', 'scheduled', 'published', 'skipped', 'archived')),
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('urgent', 'high', 'medium', 'low')),
copy_text TEXT NOT NULL DEFAULT '',
hook TEXT NOT NULL DEFAULT '',
asset_brief TEXT NOT NULL DEFAULT '',
scheduled_for TIMESTAMPTZ,
published_url TEXT,
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_social_distribution_jobs_status_priority
ON public.social_distribution_jobs (status, priority, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_social_distribution_jobs_candidate
ON public.social_distribution_jobs (candidate_id, channel);
ALTER TABLE public.social_distribution_jobs ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage social distribution jobs" ON public.social_distribution_jobs;
CREATE POLICY "Service role can manage social distribution jobs"
ON public.social_distribution_jobs FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS social_distribution_jobs_updated_at ON public.social_distribution_jobs;
CREATE TRIGGER social_distribution_jobs_updated_at
BEFORE UPDATE ON public.social_distribution_jobs
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.forum_thread_subscriptions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
thread_id TEXT NOT NULL CHECK (char_length(thread_id) BETWEEN 3 AND 180),
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
email TEXT,
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'unsubscribed')),
notification_level TEXT NOT NULL DEFAULT 'replies' CHECK (notification_level IN ('replies', 'source_changes', 'digest')),
source TEXT NOT NULL DEFAULT 'forum',
last_notified_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CHECK (user_id IS NOT NULL OR email IS NOT NULL)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_forum_thread_subscriptions_user
ON public.forum_thread_subscriptions (thread_id, user_id)
WHERE user_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_forum_thread_subscriptions_email
ON public.forum_thread_subscriptions (thread_id, lower(email))
WHERE email IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_forum_thread_subscriptions_status
ON public.forum_thread_subscriptions (status, updated_at DESC);
ALTER TABLE public.forum_thread_subscriptions ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can read own forum subscriptions" ON public.forum_thread_subscriptions;
CREATE POLICY "Users can read own forum subscriptions"
ON public.forum_thread_subscriptions FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users can manage own forum subscriptions" ON public.forum_thread_subscriptions;
CREATE POLICY "Users can manage own forum subscriptions"
ON public.forum_thread_subscriptions FOR ALL
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Service role can manage forum subscriptions" ON public.forum_thread_subscriptions;
CREATE POLICY "Service role can manage forum subscriptions"
ON public.forum_thread_subscriptions FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS forum_thread_subscriptions_updated_at ON public.forum_thread_subscriptions;
CREATE TRIGGER forum_thread_subscriptions_updated_at
BEFORE UPDATE ON public.forum_thread_subscriptions
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.evidence_graph_nodes (
id TEXT PRIMARY KEY CHECK (char_length(id) BETWEEN 3 AND 180),
node_type TEXT NOT NULL CHECK (node_type IN ('marker', 'source', 'frame', 'article', 'character', 'vehicle', 'region', 'claim', 'collection')),
label TEXT NOT NULL CHECK (char_length(label) BETWEEN 2 AND 260),
canonical_path TEXT,
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'watching', 'needs_review', 'archived')),
confidence INTEGER NOT NULL DEFAULT 0 CHECK (confidence BETWEEN 0 AND 100),
source_policy TEXT NOT NULL DEFAULT '',
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_evidence_graph_nodes_type_status
ON public.evidence_graph_nodes (node_type, status, updated_at DESC);
ALTER TABLE public.evidence_graph_nodes ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read active evidence graph nodes" ON public.evidence_graph_nodes;
CREATE POLICY "Anyone can read active evidence graph nodes"
ON public.evidence_graph_nodes FOR SELECT
TO anon, authenticated
USING (status IN ('active', 'watching', 'needs_review'));
DROP POLICY IF EXISTS "Service role can manage evidence graph nodes" ON public.evidence_graph_nodes;
CREATE POLICY "Service role can manage evidence graph nodes"
ON public.evidence_graph_nodes FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS evidence_graph_nodes_updated_at ON public.evidence_graph_nodes;
CREATE TRIGGER evidence_graph_nodes_updated_at
BEFORE UPDATE ON public.evidence_graph_nodes
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.evidence_graph_edges (
id TEXT PRIMARY KEY CHECK (char_length(id) BETWEEN 4 AND 220),
from_node TEXT NOT NULL REFERENCES public.evidence_graph_nodes(id) ON DELETE CASCADE,
to_node TEXT NOT NULL REFERENCES public.evidence_graph_nodes(id) ON DELETE CASCADE,
relation TEXT NOT NULL CHECK (char_length(relation) BETWEEN 2 AND 80),
weight INTEGER NOT NULL DEFAULT 50 CHECK (weight BETWEEN 0 AND 100),
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'watching', 'needs_review', 'archived')),
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_evidence_graph_edges_from
ON public.evidence_graph_edges (from_node, status);
CREATE INDEX IF NOT EXISTS idx_evidence_graph_edges_to
ON public.evidence_graph_edges (to_node, status);
ALTER TABLE public.evidence_graph_edges ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read active evidence graph edges" ON public.evidence_graph_edges;
CREATE POLICY "Anyone can read active evidence graph edges"
ON public.evidence_graph_edges FOR SELECT
TO anon, authenticated
USING (status IN ('active', 'watching', 'needs_review'));
DROP POLICY IF EXISTS "Service role can manage evidence graph edges" ON public.evidence_graph_edges;
CREATE POLICY "Service role can manage evidence graph edges"
ON public.evidence_graph_edges FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS evidence_graph_edges_updated_at ON public.evidence_graph_edges;
CREATE TRIGGER evidence_graph_edges_updated_at
BEFORE UPDATE ON public.evidence_graph_edges
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.launch_command_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
event_type TEXT NOT NULL CHECK (event_type IN ('news', 'map', 'forum', 'source', 'social', 'pro', 'affiliate', 'ops')),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 3 AND 260),
priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('urgent', 'high', 'medium', 'low')),
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'watching', 'done', 'archived')),
target_url TEXT NOT NULL DEFAULT '/',
source_table TEXT,
source_id TEXT,
metric TEXT,
notes TEXT NOT NULL DEFAULT '',
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_launch_command_events_status_priority
ON public.launch_command_events (status, priority, updated_at DESC);
ALTER TABLE public.launch_command_events ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage launch command events" ON public.launch_command_events;
CREATE POLICY "Service role can manage launch command events"
ON public.launch_command_events FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS launch_command_events_updated_at ON public.launch_command_events;
CREATE TRIGGER launch_command_events_updated_at
BEFORE UPDATE ON public.launch_command_events
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at();
CREATE TABLE IF NOT EXISTS public.app_rate_limits (
bucket_key TEXT NOT NULL,
window_start TIMESTAMPTZ NOT NULL,
window_seconds INTEGER NOT NULL CHECK (window_seconds BETWEEN 1 AND 86400),
count INTEGER NOT NULL DEFAULT 0 CHECK (count >= 0),
reset_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (bucket_key, window_start)
);
CREATE INDEX IF NOT EXISTS idx_app_rate_limits_reset
ON public.app_rate_limits (reset_at);
ALTER TABLE public.app_rate_limits ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage app rate limits" ON public.app_rate_limits;
CREATE POLICY "Service role can manage app rate limits"
ON public.app_rate_limits FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
CREATE OR REPLACE FUNCTION public.consume_app_rate_limit(
bucket_key_value TEXT,
limit_value INTEGER,
window_seconds_value INTEGER
)
RETURNS TABLE(allowed BOOLEAN, used_count INTEGER, reset_at_value TIMESTAMPTZ)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
now_value TIMESTAMPTZ := now();
window_start_value TIMESTAMPTZ;
next_count INTEGER;
next_reset TIMESTAMPTZ;
BEGIN
window_start_value := to_timestamp(floor(extract(epoch from now_value) / window_seconds_value) * window_seconds_value);
next_reset := window_start_value + make_interval(secs => window_seconds_value);
INSERT INTO public.app_rate_limits (bucket_key, window_start, window_seconds, count, reset_at)
VALUES (bucket_key_value, window_start_value, window_seconds_value, 1, next_reset)
ON CONFLICT (bucket_key, window_start)
DO UPDATE SET
count = public.app_rate_limits.count + 1,
reset_at = EXCLUDED.reset_at,
updated_at = now()
RETURNING count, reset_at INTO next_count, next_reset;
DELETE FROM public.app_rate_limits
WHERE reset_at < now() - interval '1 hour';
allowed := next_count <= limit_value;
used_count := next_count;
reset_at_value := next_reset;
RETURN NEXT;
END;
$$;
REVOKE ALL ON FUNCTION public.consume_app_rate_limit(TEXT, INTEGER, INTEGER) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.consume_app_rate_limit(TEXT, INTEGER, INTEGER) TO service_role;
INSERT INTO public.evidence_graph_nodes (id, node_type, label, canonical_path, status, confidence, source_policy, metadata)
VALUES
('region-vice-city', 'region', 'Vice City', '/gta-6-vice-city-locations/', 'active', 95, 'Official region, exact district boundaries still need final source proof.', '{"surface":"authority"}'::JSONB),
('marker-vice-city-core', 'marker', 'Vice City Core', '/map/vice-city-core/', 'active', 95, 'Source-backed marker with final coordinates still treated as pre-release reconstruction.', '{"surface":"atlas"}'::JSONB),
('source-rockstar-vi', 'source', 'Rockstar GTA VI page', 'https://www.rockstargames.com/VI', 'active', 100, 'Official source baseline.', '{"source_type":"official"}'::JSONB),
('frame-trailer-2-vice-city-skyline', 'frame', 'Trailer 2 Vice City Skyline', '/trailers/trailer-2/vice-city-skyline/', 'active', 90, 'Trailer frame supports visible skyline evidence, not final road-grid certainty.', '{"trailer":"trailer-2"}'::JSONB),
('collection-official-anchors', 'collection', 'Official anchor run', '/map/?collection=official-anchors', 'active', 82, 'Curated collection keeps official/trailer-seen anchors separate from community reconstruction.', '{"surface":"atlas"}'::JSONB)
ON CONFLICT (id) DO UPDATE SET
node_type = EXCLUDED.node_type,
label = EXCLUDED.label,
canonical_path = EXCLUDED.canonical_path,
status = EXCLUDED.status,
confidence = EXCLUDED.confidence,
source_policy = EXCLUDED.source_policy,
metadata = EXCLUDED.metadata,
updated_at = now();
INSERT INTO public.evidence_graph_edges (id, from_node, to_node, relation, weight, status, metadata)
VALUES
('edge-rockstar-to-vice-city', 'source-rockstar-vi', 'region-vice-city', 'confirms_region', 100, 'active', '{}'::JSONB),
('edge-vice-city-to-core-marker', 'region-vice-city', 'marker-vice-city-core', 'contains_marker', 88, 'active', '{}'::JSONB),
('edge-trailer2-to-core-marker', 'frame-trailer-2-vice-city-skyline', 'marker-vice-city-core', 'supports_marker', 84, 'active', '{}'::JSONB),
('edge-collection-to-core-marker', 'collection-official-anchors', 'marker-vice-city-core', 'includes_marker', 80, 'active', '{}'::JSONB)
ON CONFLICT (id) DO UPDATE SET
from_node = EXCLUDED.from_node,
to_node = EXCLUDED.to_node,
relation = EXCLUDED.relation,
weight = EXCLUDED.weight,
status = EXCLUDED.status,
metadata = EXCLUDED.metadata,
updated_at = now();
INSERT INTO public.launch_command_events (event_type, title, priority, status, target_url, source_table, source_id, metric, notes, metadata)
VALUES
('news', 'Watch official and trusted GTA VI feeds', 'high', 'open', '/admin/ops/', 'news_source_runs', 'source-watch', 'fresh_public_items', 'Source watch should run frequently and queue risky claims before publication.', '{"cadence":"hourly where plan allows"}'::JSONB),
('map', 'Promote useful forum evidence into atlas markers', 'high', 'open', '/forum/map-evidence/', 'community_challenges', 'challenge-vice-city-grid', 'forum_to_marker_promotions', 'Good source work should end in a marker note, challenge closure, or rejected claim.', '{"cadence":"weekly"}'::JSONB),
('social', 'Turn top evidence changes into share packs', 'medium', 'open', '/admin/social-studio/', 'social_distribution_jobs', 'social-studio', 'published_social_jobs', 'Each high-quality source update should produce one site update and at least one social-ready output.', '{"cadence":"daily"}'::JSONB),
('ops', 'Verify backups, PITR, logs, and firewall after deploys', 'high', 'open', '/admin/ops/', 'retention_runs', 'enterprise-readiness', 'ops_readiness', 'Use dashboard-verified PITR and log values for operational claims.', '{"cadence":"after deploy"}'::JSONB)
ON CONFLICT DO NOTHING;
-- Leonida Intel: account-backed platform OS.
-- Adds durable account timelines, saved searches, atlas route plans,
-- forum notifications, and social calendar rows used by the platform layer.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS public.account_event_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
event_type TEXT NOT NULL CHECK (char_length(event_type) BETWEEN 2 AND 80),
source TEXT NOT NULL DEFAULT 'account',
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 2 AND 220),
body TEXT NOT NULL DEFAULT '',
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_account_event_log_user_created
ON public.account_event_log (user_id, created_at DESC);
ALTER TABLE public.account_event_log ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can read own account events" ON public.account_event_log;
CREATE POLICY "Users can read own account events"
ON public.account_event_log FOR SELECT
TO authenticated
USING ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Service role can manage account events" ON public.account_event_log;
CREATE POLICY "Service role can manage account events"
ON public.account_event_log FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
CREATE TABLE IF NOT EXISTS public.user_saved_searches (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
surface TEXT NOT NULL DEFAULT 'atlas' CHECK (surface IN ('atlas', 'sources', 'news', 'forum', 'pc_lab')),
label TEXT NOT NULL CHECK (char_length(label) BETWEEN 2 AND 120),
query TEXT NOT NULL DEFAULT '',
filters JSONB NOT NULL DEFAULT '{}'::JSONB,
notify BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_user_saved_searches_user_surface
ON public.user_saved_searches (user_id, surface, updated_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_saved_searches_unique_label
ON public.user_saved_searches (user_id, surface, lower(label));
ALTER TABLE public.user_saved_searches ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can manage own saved searches" ON public.user_saved_searches;
CREATE POLICY "Users can manage own saved searches"
ON public.user_saved_searches FOR ALL
TO authenticated
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Service role can manage saved searches" ON public.user_saved_searches;
CREATE POLICY "Service role can manage saved searches"
ON public.user_saved_searches FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS user_saved_searches_updated_at ON public.user_saved_searches;
CREATE TRIGGER user_saved_searches_updated_at
BEFORE UPDATE ON public.user_saved_searches
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE TABLE IF NOT EXISTS public.atlas_route_plans (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 2 AND 160),
mode TEXT NOT NULL DEFAULT 'proof_sweep' CHECK (mode IN ('proof_sweep', 'completion_sprint', 'source_review', 'custom')),
source_marker_id TEXT,
marker_ids TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
filters JSONB NOT NULL DEFAULT '{}'::JSONB,
route_json JSONB NOT NULL DEFAULT '{}'::JSONB,
is_pinned BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_atlas_route_plans_user_updated
ON public.atlas_route_plans (user_id, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_atlas_route_plans_marker_ids
ON public.atlas_route_plans USING GIN (marker_ids);
ALTER TABLE public.atlas_route_plans ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can manage own route plans" ON public.atlas_route_plans;
CREATE POLICY "Users can manage own route plans"
ON public.atlas_route_plans FOR ALL
TO authenticated
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Service role can manage route plans" ON public.atlas_route_plans;
CREATE POLICY "Service role can manage route plans"
ON public.atlas_route_plans FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS atlas_route_plans_updated_at ON public.atlas_route_plans;
CREATE TRIGGER atlas_route_plans_updated_at
BEFORE UPDATE ON public.atlas_route_plans
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE TABLE IF NOT EXISTS public.forum_notifications (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
thread_id TEXT,
notification_type TEXT NOT NULL DEFAULT 'thread_update',
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 2 AND 180),
body TEXT NOT NULL DEFAULT '',
href TEXT,
read_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_forum_notifications_user_unread
ON public.forum_notifications (user_id, read_at, created_at DESC);
ALTER TABLE public.forum_notifications ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can read own forum notifications" ON public.forum_notifications;
CREATE POLICY "Users can read own forum notifications"
ON public.forum_notifications FOR SELECT
TO authenticated
USING ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Users can mark own forum notifications read" ON public.forum_notifications;
CREATE POLICY "Users can mark own forum notifications read"
ON public.forum_notifications FOR UPDATE
TO authenticated
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);
DROP POLICY IF EXISTS "Service role can manage forum notifications" ON public.forum_notifications;
CREATE POLICY "Service role can manage forum notifications"
ON public.forum_notifications FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
CREATE TABLE IF NOT EXISTS public.content_distribution_calendar (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
asset_id TEXT,
channel TEXT NOT NULL CHECK (channel IN ('youtube_shorts', 'tiktok', 'x', 'discord', 'newsletter', 'site')),
title TEXT NOT NULL CHECK (char_length(title) BETWEEN 2 AND 220),
source_path TEXT,
scheduled_for TIMESTAMPTZ,
status TEXT NOT NULL DEFAULT 'idea' CHECK (status IN ('idea', 'drafted', 'scheduled', 'published', 'skipped')),
payload JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_content_distribution_calendar_status_schedule
ON public.content_distribution_calendar (status, scheduled_for NULLS FIRST, updated_at DESC);
ALTER TABLE public.content_distribution_calendar ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Service role can manage distribution calendar" ON public.content_distribution_calendar;
CREATE POLICY "Service role can manage distribution calendar"
ON public.content_distribution_calendar FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
DROP TRIGGER IF EXISTS content_distribution_calendar_updated_at ON public.content_distribution_calendar;
CREATE TRIGGER content_distribution_calendar_updated_at
BEFORE UPDATE ON public.content_distribution_calendar
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
CREATE OR REPLACE FUNCTION public.record_account_event(
target_user_id UUID,
event_type_value TEXT,
title_value TEXT,
source_value TEXT DEFAULT 'account',
body_value TEXT DEFAULT '',
metadata_value JSONB DEFAULT '{}'::JSONB
)
RETURNS UUID AS $$
DECLARE
event_id UUID;
BEGIN
IF target_user_id IS NULL THEN
RETURN NULL;
END IF;
INSERT INTO public.account_event_log (user_id, event_type, source, title, body, metadata)
VALUES (
target_user_id,
left(COALESCE(NULLIF(event_type_value, ''), 'account'), 80),
left(COALESCE(NULLIF(source_value, ''), 'account'), 80),
left(COALESCE(NULLIF(title_value, ''), 'Account activity'), 220),
COALESCE(body_value, ''),
COALESCE(metadata_value, '{}'::JSONB)
)
RETURNING id INTO event_id;
RETURN event_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
INSERT INTO public.content_distribution_calendar (asset_id, channel, title, source_path, scheduled_for, status, payload)
VALUES
('launch-countdown-weekly', 'youtube_shorts', 'Weekly GTA VI countdown facts from source archive', '/launch-week/', now() + interval '1 day', 'idea', '{"format":"short","source":"launch-week"}'::JSONB),
('map-evidence-loop', 'discord', 'Community map evidence challenge', '/forum/map-evidence/', now() + interval '2 days', 'idea', '{"format":"prompt","source":"forum"}'::JSONB),
('pc-lab-build-check', 'tiktok', 'Can your PC handle the expected GTA VI target?', '/pc-checker/', now() + interval '3 days', 'idea', '{"format":"short","source":"pc-lab"}'::JSONB)
ON CONFLICT DO NOTHING;