# Agencio Predict - Database Schema (PostgreSQL)

## Entity Relationship Diagram

```
prediction_orgs
    │ 1:M
    └──► prediction_org_members ──► auth.users

auth.users (managed by Agencio Auth Service)
    │ 1:1
    ├──► prediction_subscriptions
    │ 1:M
    ├──► prediction_api_keys
    │ 1:M
    ├──► prediction_usage_metrics
    │ 1:M
    ├──► prediction_campaigns
    │ 1:M
    ├──► prediction_rules
    │ 1:M
    ├──► prediction_action_connections
    │ 1:M
    └──► prediction_data_feeds

prediction_categories
    │ 1:M
    ▼
prediction_events
    │ 1:M
    ├──► prediction_signals
    │ 1:M
    ├──► prediction_snapshots
    │ 1:M
    ├──► prediction_explanations
    │ 1:M
    ├──► prediction_trust_scores
    │ 1:M
    ├──► prediction_trust_flags
    │ 1:1
    ├──► prediction_resolutions
    │        │ 1:M
    │        └──► prediction_resolution_disputes
    │ M:M (via linking table)
    └──► prediction_campaign_events ──► prediction_campaigns

prediction_campaigns (marketing)
    │ 1:M
    └──► prediction_campaign_variants
              │ 1:M
              └──► prediction_campaign_results

prediction_calibration_scores (global / per-category)

prediction_rules (triggers & actions)
    │ 1:M
    └──► prediction_rule_executions

prediction_action_connections (external platform auth)

prediction_data_sources (data feed pipeline)
    │ 1:M
    └──► prediction_data_feeds
              │ 1:M
              ├──► prediction_feed_runs
              │ 1:M
              └──► prediction_unmatched_signals

prediction_audit_log (immutable, append-only)
```

---

## Auth & Identity Tables

> User records are managed by the Agencio Authentication Service microservice.
> The schema below references `auth.users(id)` — this maps to the user ID
> issued by the auth service. In PostgreSQL, create a stub table or foreign
> data wrapper pointing to the auth service's user store.

### auth.users (stub / reference)

```sql
-- Stub table for FK references. The real user data lives in the
-- Agencio Authentication Service. This table is synced via webhook
-- or CDC on user create/update/delete events from the auth service.

CREATE SCHEMA IF NOT EXISTS auth;

CREATE TABLE auth.users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  avatar_url TEXT,
  is_active BOOLEAN DEFAULT true,
  synced_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_auth_users_email ON auth.users(email);
```

### prediction_orgs

Multi-tenancy — organisations that group users.

```sql
CREATE TABLE prediction_orgs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  owner_id UUID NOT NULL REFERENCES auth.users(id),
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_orgs_owner ON prediction_orgs(owner_id);
```

### prediction_org_members

```sql
CREATE TABLE prediction_org_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES prediction_orgs(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member'
    CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  invited_by UUID REFERENCES auth.users(id),
  accepted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE UNIQUE INDEX idx_org_members_unique ON prediction_org_members(org_id, user_id);
CREATE INDEX idx_org_members_user ON prediction_org_members(user_id);
```

### prediction_subscriptions

Billing tier and feature limits per user.

```sql
CREATE TABLE prediction_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) UNIQUE,
  org_id UUID REFERENCES prediction_orgs(id),

  -- Plan
  tier TEXT NOT NULL DEFAULT 'free'
    CHECK (tier IN ('free', 'pro', 'team', 'enterprise',
                    'api_developer', 'api_startup', 'api_growth', 'api_enterprise')),

  -- Payment provider
  stripe_customer_id TEXT UNIQUE,
  stripe_subscription_id TEXT UNIQUE,

  -- Status
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'past_due', 'cancelled', 'trialing', 'paused')),

  -- Feature limits (NULL = unlimited)
  max_events INTEGER,
  max_rules INTEGER,
  max_campaigns_per_month INTEGER,
  max_feeds INTEGER,
  max_team_members INTEGER,
  api_rate_limit_rpm INTEGER,
  api_rate_limit_rpd INTEGER,
  realtime_enabled BOOLEAN DEFAULT false,

  -- Period
  current_period_start TIMESTAMPTZ,
  current_period_end TIMESTAMPTZ,
  trial_end TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ,

  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_subscriptions_tier ON prediction_subscriptions(tier);
CREATE INDEX idx_subscriptions_stripe ON prediction_subscriptions(stripe_customer_id);
```

### prediction_api_keys

External API access keys (hashed, never stored in plaintext).

```sql
CREATE TABLE prediction_api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  key_prefix TEXT NOT NULL,              -- first 12 chars for display: "pk_live_abc1"
  key_hash TEXT NOT NULL UNIQUE,         -- SHA-256 of full key
  scopes TEXT[] DEFAULT '{read}',        -- ['read', 'signals', 'trust', 'marketing', 'write']
  rate_limit_rpm INTEGER NOT NULL DEFAULT 10,
  rate_limit_rpd INTEGER NOT NULL DEFAULT 100,
  last_used_at TIMESTAMPTZ,
  last_used_ip INET,
  is_active BOOLEAN DEFAULT true,
  expires_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_api_keys_hash ON prediction_api_keys(key_hash) WHERE is_active = true;
CREATE INDEX idx_api_keys_user ON prediction_api_keys(user_id);
```

### prediction_usage_metrics

Per-user usage tracking per billing period.

```sql
CREATE TABLE prediction_usage_metrics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  period_start TIMESTAMPTZ NOT NULL,
  period_end TIMESTAMPTZ NOT NULL,

  api_calls INTEGER DEFAULT 0,
  events_tracked INTEGER DEFAULT 0,
  rules_active INTEGER DEFAULT 0,
  campaigns_created INTEGER DEFAULT 0,
  explanations_generated INTEGER DEFAULT 0,
  feeds_active INTEGER DEFAULT 0,
  signals_ingested BIGINT DEFAULT 0,
  storage_bytes_used BIGINT DEFAULT 0,
  llm_tokens_used BIGINT DEFAULT 0,
  llm_cost_cents INTEGER DEFAULT 0,

  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE UNIQUE INDEX idx_usage_user_period ON prediction_usage_metrics(user_id, period_start);
```

---

## Core Prediction Tables

### prediction_categories

```sql
CREATE TABLE prediction_categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE,
  description TEXT,
  icon TEXT,
  sort_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO prediction_categories (name, slug, description, sort_order) VALUES
  ('Geopolitics', 'geopolitics', 'International relations, conflicts, elections', 1),
  ('Technology', 'technology', 'Tech industry, AI, crypto, product launches', 2),
  ('Marketing', 'marketing', 'Campaign outcomes, viral content, brand events', 3),
  ('Finance', 'finance', 'Markets, commodities, economic indicators', 4),
  ('Sports', 'sports', 'Game outcomes, player transfers, league events', 5),
  ('Entertainment', 'entertainment', 'Box office, awards, streaming, cultural events', 6);
```

### prediction_events

The core entity — a trackable prediction question.

```sql
CREATE TABLE prediction_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category_id UUID REFERENCES prediction_categories(id),

  -- Event definition
  title TEXT NOT NULL,
  description TEXT,
  slug TEXT NOT NULL UNIQUE,
  image_url TEXT,

  -- Current state (denormalised for fast reads — canonical source is latest snapshot)
  current_probability DECIMAL(5,4) CHECK (current_probability >= 0 AND current_probability <= 1),
  sentiment_score DECIMAL(5,4) CHECK (sentiment_score >= -1 AND sentiment_score <= 1),
  confidence TEXT CHECK (confidence IN ('LOW', 'MEDIUM', 'HIGH', 'VERY_HIGH')),
  divergence TEXT CHECK (divergence IN ('NONE', 'LOW', 'MEDIUM', 'HIGH', 'EXTREME')),
  trust_score DECIMAL(5,4) CHECK (trust_score >= 0 AND trust_score <= 1),
  volatility DECIMAL(5,4) CHECK (volatility >= 0 AND volatility <= 1),

  -- Previous state (for trigger "crosses" evaluation — updated atomically)
  previous_probability DECIMAL(5,4),
  previous_sentiment DECIMAL(5,4),
  previous_trust_score DECIMAL(5,4),
  previous_divergence TEXT,

  -- Resolution
  status TEXT NOT NULL DEFAULT 'ACTIVE'
    CHECK (status IN ('ACTIVE', 'PAUSED', 'RESOLVED_YES', 'RESOLVED_NO', 'RESOLVED_AMBIGUOUS', 'CANCELLED')),
  resolution_criteria TEXT,
  resolution_date TIMESTAMPTZ,
  resolved_at TIMESTAMPTZ,

  -- Metadata
  source_urls TEXT[],
  tags TEXT[],
  is_public BOOLEAN DEFAULT true,       -- false = org-private event
  org_id UUID REFERENCES prediction_orgs(id),
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_events_category ON prediction_events(category_id);
CREATE INDEX idx_events_status ON prediction_events(status);
CREATE INDEX idx_events_probability ON prediction_events(current_probability);
CREATE INDEX idx_events_created ON prediction_events(created_at DESC);
CREATE INDEX idx_events_tags ON prediction_events USING GIN(tags);
CREATE INDEX idx_events_slug ON prediction_events(slug);
CREATE INDEX idx_events_org ON prediction_events(org_id) WHERE org_id IS NOT NULL;
CREATE INDEX idx_events_updated ON prediction_events(updated_at DESC);
```

### prediction_signals

Raw signal data from external sources.

```sql
CREATE TABLE prediction_signals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES prediction_events(id) ON DELETE CASCADE,
  feed_id UUID REFERENCES prediction_data_feeds(id),  -- which feed produced this signal

  -- Source
  source TEXT NOT NULL,
  source_url TEXT,
  source_event_id TEXT,

  -- Signal data
  signal_type TEXT NOT NULL
    CHECK (signal_type IN ('MARKET_PRICE', 'SENTIMENT', 'VOLUME', 'NEWS', 'SOCIAL', 'EXPERT', 'MANUAL')),
  value DECIMAL(10,4),
  raw_data JSONB,
  narrative TEXT,

  -- Quality
  weight DECIMAL(3,2) DEFAULT 1.0 CHECK (weight >= 0 AND weight <= 5),
  reliability DECIMAL(3,2) CHECK (reliability >= 0 AND reliability <= 1),

  fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_signals_event ON prediction_signals(event_id);
CREATE INDEX idx_signals_source ON prediction_signals(source);
CREATE INDEX idx_signals_type ON prediction_signals(signal_type);
CREATE INDEX idx_signals_fetched ON prediction_signals(fetched_at DESC);
CREATE INDEX idx_signals_feed ON prediction_signals(feed_id) WHERE feed_id IS NOT NULL;

-- Deduplication: same source + external ID + type within 5-min window
CREATE UNIQUE INDEX idx_signals_dedup ON prediction_signals (
  source, source_event_id, signal_type,
  (date_trunc('hour', fetched_at) + (EXTRACT(minute FROM fetched_at)::int / 5) * interval '5 min')
) WHERE source_event_id IS NOT NULL;
```

### prediction_snapshots

Time-series snapshots of aggregated event state (for charting).

```sql
CREATE TABLE prediction_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES prediction_events(id) ON DELETE CASCADE,

  probability DECIMAL(5,4) CHECK (probability >= 0 AND probability <= 1),
  sentiment_score DECIMAL(5,4) CHECK (sentiment_score >= -1 AND sentiment_score <= 1),
  trust_score DECIMAL(5,4) CHECK (trust_score >= 0 AND trust_score <= 1),
  volatility DECIMAL(5,4) CHECK (volatility >= 0 AND volatility <= 1),
  volume INTEGER DEFAULT 0,
  divergence TEXT CHECK (divergence IN ('NONE', 'LOW', 'MEDIUM', 'HIGH', 'EXTREME')),
  signal_count INTEGER DEFAULT 0,        -- total signals contributing to this snapshot

  snapshot_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_snapshots_event_time ON prediction_snapshots(event_id, snapshot_at DESC);

-- Partition by month for performance (create partitions via scheduled job or pg_partman)
-- CREATE TABLE prediction_snapshots_y2026m03 PARTITION OF prediction_snapshots
--   FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
```

### prediction_explanations

AI-generated explanations for significant movements.

```sql
CREATE TABLE prediction_explanations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES prediction_events(id) ON DELETE CASCADE,

  -- What triggered this explanation
  trigger_type TEXT NOT NULL
    CHECK (trigger_type IN ('PROBABILITY_SHIFT', 'SENTIMENT_SHIFT', 'ANOMALY', 'DIVERGENCE', 'SCHEDULED', 'MANUAL', 'RESOLUTION')),
  trigger_data JSONB,

  -- The explanation
  headline TEXT NOT NULL,
  summary TEXT NOT NULL,
  detailed TEXT,
  contributing_signals UUID[],
  factors JSONB,

  -- AI metadata
  model_used TEXT,
  prompt_version TEXT,                   -- track prompt template version for quality
  prompt_tokens INTEGER,
  completion_tokens INTEGER,
  generation_time_ms INTEGER,

  -- Quality feedback
  thumbs_up INTEGER DEFAULT 0,
  thumbs_down INTEGER DEFAULT 0,

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_explanations_event ON prediction_explanations(event_id);
CREATE INDEX idx_explanations_trigger ON prediction_explanations(trigger_type);
CREATE INDEX idx_explanations_created ON prediction_explanations(created_at DESC);
```

---

## Trust & Compliance Tables

### prediction_trust_scores

```sql
CREATE TABLE prediction_trust_scores (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES prediction_events(id) ON DELETE CASCADE,

  overall_score DECIMAL(5,4) CHECK (overall_score >= 0 AND overall_score <= 1),
  manipulation_risk TEXT CHECK (manipulation_risk IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),

  -- Breakdown (all 0-1 range)
  market_integrity DECIMAL(5,4) CHECK (market_integrity >= 0 AND market_integrity <= 1),
  sentiment_authenticity DECIMAL(5,4) CHECK (sentiment_authenticity >= 0 AND sentiment_authenticity <= 1),
  source_diversity DECIMAL(5,4) CHECK (source_diversity >= 0 AND source_diversity <= 1),
  volume_consistency DECIMAL(5,4) CHECK (volume_consistency >= 0 AND volume_consistency <= 1),
  temporal_consistency DECIMAL(5,4) CHECK (temporal_consistency >= 0 AND temporal_consistency <= 1),

  flagged_activity BOOLEAN DEFAULT false,
  flag_count INTEGER DEFAULT 0,

  computed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_trust_event ON prediction_trust_scores(event_id);
CREATE INDEX idx_trust_event_latest ON prediction_trust_scores(event_id, computed_at DESC);
CREATE INDEX idx_trust_risk ON prediction_trust_scores(manipulation_risk);
```

### prediction_trust_flags

```sql
CREATE TABLE prediction_trust_flags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES prediction_events(id) ON DELETE CASCADE,
  trust_score_id UUID REFERENCES prediction_trust_scores(id),

  flag_type TEXT NOT NULL CHECK (flag_type IN (
    'INSIDER_TIMING',
    'VOLUME_SPIKE',
    'COORDINATED_ACTION',
    'BOT_SENTIMENT',
    'WASH_TRADING',
    'NARRATIVE_PLANT',
    'SOURCE_MANIPULATION',
    'OTHER'
  )),

  severity TEXT NOT NULL CHECK (severity IN ('INFO', 'WARNING', 'ALERT', 'CRITICAL')),
  description TEXT NOT NULL,
  evidence JSONB,
  related_signals UUID[],

  reviewed BOOLEAN DEFAULT false,
  reviewed_by UUID REFERENCES auth.users(id),
  reviewed_at TIMESTAMPTZ,
  review_notes TEXT,

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_flags_event ON prediction_trust_flags(event_id);
CREATE INDEX idx_flags_type ON prediction_trust_flags(flag_type);
CREATE INDEX idx_flags_severity ON prediction_trust_flags(severity);
CREATE INDEX idx_flags_unreviewed ON prediction_trust_flags(created_at DESC) WHERE reviewed = false;
```

---

## Calibration & Resolution Tables

### prediction_resolutions

```sql
CREATE TABLE prediction_resolutions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES prediction_events(id) ON DELETE CASCADE UNIQUE,

  outcome TEXT NOT NULL CHECK (outcome IN ('YES', 'NO', 'AMBIGUOUS', 'CANCELLED')),
  outcome_details TEXT,
  evidence_urls TEXT[],

  -- What we predicted at close
  final_probability DECIMAL(5,4),
  market_final_probability DECIMAL(5,4),

  -- Resolution workflow
  resolved_by UUID REFERENCES auth.users(id),
  resolution_method TEXT NOT NULL DEFAULT 'manual'
    CHECK (resolution_method IN ('manual', 'automated', 'market_settled', 'community')),
  resolved_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Dispute window
  dispute_window_end TIMESTAMPTZ,
  is_disputed BOOLEAN DEFAULT false,
  is_final BOOLEAN DEFAULT false         -- true after dispute window closes with no dispute
);

CREATE INDEX idx_resolutions_event ON prediction_resolutions(event_id);
CREATE INDEX idx_resolutions_outcome ON prediction_resolutions(outcome);
CREATE INDEX idx_resolutions_disputed ON prediction_resolutions(is_disputed) WHERE is_disputed = true;
```

### prediction_resolution_disputes

```sql
CREATE TABLE prediction_resolution_disputes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resolution_id UUID NOT NULL REFERENCES prediction_resolutions(id) ON DELETE CASCADE,
  event_id UUID NOT NULL REFERENCES prediction_events(id),

  disputed_by UUID NOT NULL REFERENCES auth.users(id),
  reason TEXT NOT NULL,
  evidence_urls TEXT[],

  status TEXT NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'under_review', 'upheld', 'overturned', 'dismissed')),
  reviewed_by UUID REFERENCES auth.users(id),
  review_notes TEXT,
  reviewed_at TIMESTAMPTZ,

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_disputes_resolution ON prediction_resolution_disputes(resolution_id);
CREATE INDEX idx_disputes_status ON prediction_resolution_disputes(status);
```

### prediction_calibration_scores

```sql
CREATE TABLE prediction_calibration_scores (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category_id UUID REFERENCES prediction_categories(id), -- NULL = global
  time_window TEXT NOT NULL CHECK (time_window IN ('7D', '30D', '90D', 'ALL_TIME')),

  total_predictions INTEGER NOT NULL DEFAULT 0,
  resolved_predictions INTEGER NOT NULL DEFAULT 0,
  brier_score DECIMAL(6,4),
  log_score DECIMAL(8,4),
  accuracy_at_50 DECIMAL(5,4),
  accuracy_at_70 DECIMAL(5,4),
  accuracy_at_90 DECIMAL(5,4),

  calibration_curve JSONB,

  computed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_calibration_category ON prediction_calibration_scores(category_id);
CREATE INDEX idx_calibration_window ON prediction_calibration_scores(time_window);
CREATE UNIQUE INDEX idx_calibration_unique ON prediction_calibration_scores(
  COALESCE(category_id, '00000000-0000-0000-0000-000000000000'), time_window
);
```

---

## Marketing Prediction Tables

### prediction_campaigns

```sql
CREATE TABLE prediction_campaigns (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id),
  org_id UUID REFERENCES prediction_orgs(id),

  -- Campaign details
  name TEXT NOT NULL,
  description TEXT,
  target_audience TEXT,
  platform TEXT CHECK (platform IN ('tiktok', 'instagram', 'x', 'linkedin', 'youtube', 'google', 'meta', 'multi')),
  campaign_type TEXT CHECK (campaign_type IN ('brand_awareness', 'conversion', 'engagement', 'launch', 'retention')),

  -- Persona link (Agencio integration)
  persona_id UUID,

  -- Prediction output
  overall_success_probability DECIMAL(5,4) CHECK (overall_success_probability >= 0 AND overall_success_probability <= 1),
  recommended_variant_id UUID,
  prediction_explanation TEXT,
  factors JSONB,
  predicted_at TIMESTAMPTZ,              -- when prediction was generated

  -- Resolution
  status TEXT DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'PREDICTED', 'LAUNCHED', 'COMPLETED', 'ARCHIVED')),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_campaigns_user ON prediction_campaigns(user_id);
CREATE INDEX idx_campaigns_org ON prediction_campaigns(org_id) WHERE org_id IS NOT NULL;
CREATE INDEX idx_campaigns_status ON prediction_campaigns(status);
```

### prediction_campaign_events

Links campaigns to prediction events they depend on. When linked events shift,
triggers can auto-repredict or pause campaigns.

```sql
CREATE TABLE prediction_campaign_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID NOT NULL REFERENCES prediction_campaigns(id) ON DELETE CASCADE,
  event_id UUID NOT NULL REFERENCES prediction_events(id) ON DELETE CASCADE,

  relationship TEXT NOT NULL DEFAULT 'monitors'
    CHECK (relationship IN ('monitors', 'depends_on', 'contraindicates')),
  -- monitors:        campaign watches this event for context
  -- depends_on:      campaign success depends on event outcome
  -- contraindicates: event going YES is bad for the campaign

  probability_threshold DECIMAL(5,4),    -- optional: trigger action if event crosses this
  notes TEXT,

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE UNIQUE INDEX idx_campaign_events_unique ON prediction_campaign_events(campaign_id, event_id);
CREATE INDEX idx_campaign_events_event ON prediction_campaign_events(event_id);
```

### prediction_campaign_variants

```sql
CREATE TABLE prediction_campaign_variants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID NOT NULL REFERENCES prediction_campaigns(id) ON DELETE CASCADE,

  label TEXT NOT NULL,
  hook TEXT,
  creative_url TEXT,
  content_description TEXT,

  -- Prediction
  success_probability DECIMAL(5,4) CHECK (success_probability >= 0 AND success_probability <= 1),
  predicted_engagement DECIMAL(5,4) CHECK (predicted_engagement >= 0 AND predicted_engagement <= 1),
  predicted_conversion DECIMAL(5,4) CHECK (predicted_conversion >= 0 AND predicted_conversion <= 1),
  reasoning TEXT,
  scoring_factors JSONB,

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_variants_campaign ON prediction_campaign_variants(campaign_id);
```

### prediction_campaign_results

Actual campaign outcomes — the feedback loop for improving predictions.

```sql
CREATE TABLE prediction_campaign_results (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID NOT NULL REFERENCES prediction_campaigns(id) ON DELETE CASCADE,
  variant_id UUID REFERENCES prediction_campaign_variants(id),

  -- Actual metrics
  impressions INTEGER,
  clicks INTEGER,
  conversions INTEGER,
  spend_cents INTEGER,                   -- total spend in cents
  revenue_cents INTEGER,                 -- total revenue in cents

  -- Computed
  actual_ctr DECIMAL(7,4),               -- clicks / impressions
  actual_conversion_rate DECIMAL(7,4),   -- conversions / clicks
  actual_roas DECIMAL(7,2),             -- revenue / spend
  actual_engagement_rate DECIMAL(7,4),

  -- Comparison to prediction
  prediction_error DECIMAL(5,4),         -- |predicted - actual|
  prediction_direction_correct BOOLEAN,  -- did we get the direction right?

  -- Period
  result_period_start TIMESTAMPTZ,
  result_period_end TIMESTAMPTZ,
  recorded_at TIMESTAMPTZ DEFAULT now(),
  recorded_by UUID REFERENCES auth.users(id)
);

CREATE INDEX idx_results_campaign ON prediction_campaign_results(campaign_id);
CREATE INDEX idx_results_variant ON prediction_campaign_results(variant_id);
CREATE INDEX idx_results_recorded ON prediction_campaign_results(recorded_at DESC);
```

---

## Triggers & Actions Tables

> Full trigger/action specs in [08-triggers-and-actions.md](./08-triggers-and-actions.md)

### prediction_rules

```sql
CREATE TABLE prediction_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id),
  org_id UUID REFERENCES prediction_orgs(id),

  -- Definition
  name TEXT NOT NULL,
  description TEXT,
  enabled BOOLEAN DEFAULT true,

  -- Scope
  scope_type TEXT NOT NULL CHECK (scope_type IN ('event', 'category', 'all', 'campaign', 'tag')),
  scope_id UUID,
  scope_tag TEXT,

  -- Trigger
  trigger_type TEXT NOT NULL,
  trigger_params JSONB NOT NULL DEFAULT '{}',

  -- Actions (ordered array)
  actions JSONB NOT NULL DEFAULT '[]',

  -- Throttling
  cooldown_minutes INTEGER DEFAULT 60,
  max_fires_per_day INTEGER DEFAULT 10,
  active_hours JSONB,

  -- Lifecycle
  is_system BOOLEAN DEFAULT false,
  expires_at TIMESTAMPTZ,
  last_fired_at TIMESTAMPTZ,
  fire_count INTEGER DEFAULT 0,

  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_rules_user ON prediction_rules(user_id);
CREATE INDEX idx_rules_org ON prediction_rules(org_id) WHERE org_id IS NOT NULL;
CREATE INDEX idx_rules_scope ON prediction_rules(scope_type, scope_id);
CREATE INDEX idx_rules_enabled ON prediction_rules(enabled) WHERE enabled = true;
CREATE INDEX idx_rules_trigger ON prediction_rules(trigger_type);
```

### prediction_rule_executions

```sql
CREATE TABLE prediction_rule_executions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rule_id UUID NOT NULL REFERENCES prediction_rules(id) ON DELETE CASCADE,
  event_id UUID REFERENCES prediction_events(id),

  trigger_snapshot JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'PENDING'
    CHECK (status IN ('PENDING', 'EXECUTING', 'COMPLETED', 'PARTIAL_FAILURE', 'FAILED',
                      'SKIPPED_COOLDOWN', 'SKIPPED_HOURS', 'SKIPPED_LIMIT')),

  action_results JSONB DEFAULT '[]',

  started_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ,
  error_message TEXT,
  retry_count INTEGER DEFAULT 0
);

CREATE INDEX idx_executions_rule ON prediction_rule_executions(rule_id);
CREATE INDEX idx_executions_event ON prediction_rule_executions(event_id);
CREATE INDEX idx_executions_status ON prediction_rule_executions(status);
CREATE INDEX idx_executions_started ON prediction_rule_executions(started_at DESC);
```

### prediction_action_connections

Stores encrypted OAuth tokens / API keys for external platforms.

```sql
CREATE TABLE prediction_action_connections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

  platform TEXT NOT NULL,
  credentials JSONB NOT NULL,            -- encrypted at application layer (AES-256-GCM)
  scopes TEXT[],

  status TEXT DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'EXPIRED', 'REVOKED', 'ERROR')),
  last_used_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ,
  error_message TEXT,

  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE UNIQUE INDEX idx_connections_user_platform ON prediction_action_connections(user_id, platform);
```

---

## Data Feed Pipeline Tables

> Full feed pipeline specs in [09-data-feed-pipeline.md](./09-data-feed-pipeline.md)

### prediction_data_sources

Registry of available data source types.

```sql
CREATE TABLE prediction_data_sources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  category TEXT NOT NULL CHECK (category IN (
    'prediction_market', 'news', 'social', 'financial', 'alternative', 'government', 'custom'
  )),
  description TEXT,
  icon_url TEXT,

  adapter_type TEXT NOT NULL CHECK (adapter_type IN ('built_in', 'custom_api', 'webhook', 'upload')),
  config_schema JSONB,
  default_config JSONB,

  supports_pull BOOLEAN DEFAULT true,
  supports_push BOOLEAN DEFAULT false,
  supports_backfill BOOLEAN DEFAULT false,

  rate_limit_rpm INTEGER,
  rate_limit_rpd INTEGER,

  is_system BOOLEAN DEFAULT false,
  status TEXT DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'DEPRECATED', 'DISABLED')),
  created_at TIMESTAMPTZ DEFAULT now()
);
```

### prediction_data_feeds

Instances of feeds configured by users.

```sql
CREATE TABLE prediction_data_feeds (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source_id UUID NOT NULL REFERENCES prediction_data_sources(id),
  user_id UUID NOT NULL REFERENCES auth.users(id),

  name TEXT NOT NULL,
  config JSONB NOT NULL DEFAULT '{}',

  poll_interval_minutes INTEGER DEFAULT 15,
  cron_expression TEXT,
  next_run_at TIMESTAMPTZ,

  event_mapping_mode TEXT DEFAULT 'auto'
    CHECK (event_mapping_mode IN ('auto', 'manual', 'ai')),
  manual_event_mappings JSONB,

  default_signal_type TEXT DEFAULT 'MARKET_PRICE',
  default_weight DECIMAL(3,2) DEFAULT 1.0 CHECK (default_weight >= 0 AND default_weight <= 5),
  transform_config JSONB,

  credentials_encrypted JSONB,           -- encrypted at application layer
  auth_status TEXT DEFAULT 'ACTIVE'
    CHECK (auth_status IN ('ACTIVE', 'EXPIRED', 'INVALID', 'PENDING')),
  auth_expires_at TIMESTAMPTZ,

  enabled BOOLEAN DEFAULT true,
  last_successful_run TIMESTAMPTZ,
  last_error TEXT,
  consecutive_failures INTEGER DEFAULT 0,
  auto_disabled_at TIMESTAMPTZ,

  avg_latency_ms INTEGER,
  signals_ingested_total BIGINT DEFAULT 0,
  signals_ingested_24h INTEGER DEFAULT 0,

  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_feeds_source ON prediction_data_feeds(source_id);
CREATE INDEX idx_feeds_user ON prediction_data_feeds(user_id);
CREATE INDEX idx_feeds_next_run ON prediction_data_feeds(next_run_at) WHERE enabled = true;
```

### prediction_feed_runs

```sql
CREATE TABLE prediction_feed_runs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  feed_id UUID NOT NULL REFERENCES prediction_data_feeds(id) ON DELETE CASCADE,

  started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at TIMESTAMPTZ,
  duration_ms INTEGER,

  status TEXT NOT NULL DEFAULT 'RUNNING'
    CHECK (status IN ('RUNNING', 'SUCCESS', 'PARTIAL', 'FAILED', 'TIMEOUT', 'RATE_LIMITED', 'AUTH_FAILED')),

  signals_fetched INTEGER DEFAULT 0,
  signals_valid INTEGER DEFAULT 0,
  signals_deduplicated INTEGER DEFAULT 0,
  signals_matched INTEGER DEFAULT 0,
  signals_unmatched INTEGER DEFAULT 0,
  signals_written INTEGER DEFAULT 0,
  events_updated INTEGER DEFAULT 0,

  request_count INTEGER DEFAULT 0,
  bytes_received BIGINT DEFAULT 0,
  rate_limit_remaining INTEGER,
  error_message TEXT,
  error_details JSONB,

  triggers_evaluated INTEGER DEFAULT 0,
  triggers_fired INTEGER DEFAULT 0,

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_runs_feed ON prediction_feed_runs(feed_id);
CREATE INDEX idx_runs_status ON prediction_feed_runs(status);
CREATE INDEX idx_runs_started ON prediction_feed_runs(started_at DESC);
```

### prediction_unmatched_signals

```sql
CREATE TABLE prediction_unmatched_signals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  feed_run_id UUID REFERENCES prediction_feed_runs(id),
  feed_id UUID NOT NULL REFERENCES prediction_data_feeds(id),

  source_event_id TEXT,
  signal_type TEXT,
  value DECIMAL(10,4),
  narrative TEXT,
  raw_data JSONB,

  match_attempts JSONB,
  suggested_event_id UUID REFERENCES prediction_events(id),
  suggestion_confidence DECIMAL(3,2) CHECK (suggestion_confidence >= 0 AND suggestion_confidence <= 1),

  status TEXT DEFAULT 'PENDING'
    CHECK (status IN ('PENDING', 'MATCHED', 'NEW_EVENT_CREATED', 'DISCARDED')),
  resolved_event_id UUID REFERENCES prediction_events(id),
  resolved_by UUID REFERENCES auth.users(id),
  resolved_at TIMESTAMPTZ,

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_unmatched_feed ON prediction_unmatched_signals(feed_id);
CREATE INDEX idx_unmatched_status ON prediction_unmatched_signals(status);
CREATE INDEX idx_unmatched_pending ON prediction_unmatched_signals(created_at DESC) WHERE status = 'PENDING';
```

---

## Audit & Compliance Tables

### prediction_audit_log

Immutable, append-only. No UPDATE or DELETE permitted for application role.

```sql
CREATE TABLE prediction_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Who
  actor_id UUID,
  actor_type TEXT NOT NULL CHECK (actor_type IN ('user', 'service', 'system', 'api_key')),
  actor_ip INET,
  actor_user_agent TEXT,

  -- What
  action TEXT NOT NULL,
  resource_type TEXT NOT NULL,
  resource_id UUID,

  -- Details
  details JSONB,
  previous_state JSONB,
  new_state JSONB,

  -- Context
  request_id TEXT,
  service TEXT,

  severity TEXT DEFAULT 'INFO' CHECK (severity IN ('INFO', 'WARNING', 'ALERT', 'CRITICAL')),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_audit_actor ON prediction_audit_log(actor_id);
CREATE INDEX idx_audit_action ON prediction_audit_log(action);
CREATE INDEX idx_audit_resource ON prediction_audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_severity ON prediction_audit_log(severity) WHERE severity IN ('ALERT', 'CRITICAL');
CREATE INDEX idx_audit_created ON prediction_audit_log(created_at DESC);

-- Immutability: revoke destructive operations from application role
REVOKE UPDATE, DELETE ON prediction_audit_log FROM predict_app;
```

---

## Row Level Security (RLS)

```sql
-- Events: public events visible to all, org-private events visible to org members
ALTER TABLE prediction_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public events viewable by everyone"
  ON prediction_events FOR SELECT
  USING (is_public = true);

CREATE POLICY "Org-private events viewable by org members"
  ON prediction_events FOR SELECT
  USING (
    org_id IS NOT NULL AND EXISTS (
      SELECT 1 FROM prediction_org_members
      WHERE org_id = prediction_events.org_id
        AND user_id = auth.uid()
    )
  );

CREATE POLICY "Authenticated users can create events"
  ON prediction_events FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL);

-- Campaigns: private to user / org
ALTER TABLE prediction_campaigns ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own or org campaigns"
  ON prediction_campaigns FOR SELECT
  USING (
    user_id = auth.uid()
    OR (org_id IS NOT NULL AND EXISTS (
      SELECT 1 FROM prediction_org_members
      WHERE org_id = prediction_campaigns.org_id
        AND user_id = auth.uid()
    ))
  );

CREATE POLICY "Users create own campaigns"
  ON prediction_campaigns FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users update own campaigns"
  ON prediction_campaigns FOR UPDATE
  USING (auth.uid() = user_id);

-- Rules: private to user / shared within org
ALTER TABLE prediction_rules ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own or org rules"
  ON prediction_rules FOR SELECT
  USING (
    user_id = auth.uid()
    OR (org_id IS NOT NULL AND EXISTS (
      SELECT 1 FROM prediction_org_members
      WHERE org_id = prediction_rules.org_id
        AND user_id = auth.uid()
    ))
    OR is_system = true
  );

CREATE POLICY "Users manage own rules"
  ON prediction_rules FOR ALL
  USING (user_id = auth.uid());

-- Action connections: ALWAYS private to user
ALTER TABLE prediction_action_connections ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Connections private to user"
  ON prediction_action_connections FOR ALL
  USING (user_id = auth.uid());

-- API keys: ALWAYS private to user
ALTER TABLE prediction_api_keys ENABLE ROW LEVEL SECURITY;

CREATE POLICY "API keys private to user"
  ON prediction_api_keys FOR ALL
  USING (user_id = auth.uid());

-- Feeds: private to user
ALTER TABLE prediction_data_feeds ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Feeds private to user"
  ON prediction_data_feeds FOR ALL
  USING (user_id = auth.uid());

-- Subscriptions: private to user
ALTER TABLE prediction_subscriptions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Subscriptions private to user"
  ON prediction_subscriptions FOR SELECT
  USING (user_id = auth.uid());

-- Usage metrics: private to user
ALTER TABLE prediction_usage_metrics ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Usage private to user"
  ON prediction_usage_metrics FOR SELECT
  USING (user_id = auth.uid());

-- Audit log: admin only
ALTER TABLE prediction_audit_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Audit log admin only"
  ON prediction_audit_log FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM prediction_org_members
      WHERE user_id = auth.uid() AND role IN ('owner', 'admin')
    )
  );
```

---

## Database Roles

```sql
-- Application role (used by services)
CREATE ROLE predict_app LOGIN PASSWORD 'from_secrets_manager';
GRANT USAGE ON SCHEMA public TO predict_app;
GRANT USAGE ON SCHEMA auth TO predict_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO predict_app;
GRANT SELECT ON ALL TABLES IN SCHEMA auth TO predict_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO predict_app;

-- Audit log: revoke destructive operations
REVOKE UPDATE, DELETE ON prediction_audit_log FROM predict_app;

-- Read-only role (for analytics / reporting)
CREATE ROLE predict_readonly LOGIN PASSWORD 'from_secrets_manager';
GRANT USAGE ON SCHEMA public TO predict_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO predict_readonly;
```

---

## Views

```sql
-- Active events with latest trust data
CREATE VIEW v_active_events AS
SELECT
  e.*,
  c.name as category_name,
  c.slug as category_slug,
  ts.overall_score as latest_trust_score,
  ts.manipulation_risk,
  ts.flagged_activity,
  (SELECT COUNT(*) FROM prediction_trust_flags f
   WHERE f.event_id = e.id AND f.severity IN ('ALERT', 'CRITICAL')
   AND f.reviewed = false) as active_alerts
FROM prediction_events e
LEFT JOIN prediction_categories c ON c.id = e.category_id
LEFT JOIN LATERAL (
  SELECT * FROM prediction_trust_scores
  WHERE event_id = e.id
  ORDER BY computed_at DESC LIMIT 1
) ts ON true
WHERE e.status = 'ACTIVE';

-- Calibration dashboard
CREATE VIEW v_calibration_dashboard AS
SELECT
  c.name as category,
  cs.time_window,
  cs.total_predictions,
  cs.resolved_predictions,
  cs.brier_score,
  cs.accuracy_at_70,
  cs.accuracy_at_90,
  cs.computed_at
FROM prediction_calibration_scores cs
LEFT JOIN prediction_categories c ON c.id = cs.category_id
ORDER BY cs.computed_at DESC;

-- Feed health overview
CREATE VIEW v_feed_health AS
SELECT
  f.id,
  f.name,
  ds.source_id,
  ds.name as source_name,
  f.enabled,
  f.poll_interval_minutes,
  f.consecutive_failures,
  f.last_successful_run,
  f.last_error,
  f.auth_status,
  f.signals_ingested_24h,
  f.avg_latency_ms,
  lr.status as last_run_status,
  lr.started_at as last_run_at,
  lr.signals_written as last_run_signals
FROM prediction_data_feeds f
JOIN prediction_data_sources ds ON ds.id = f.source_id
LEFT JOIN LATERAL (
  SELECT * FROM prediction_feed_runs
  WHERE feed_id = f.id
  ORDER BY started_at DESC LIMIT 1
) lr ON true;
```

---

## Data Retention Policies

Implemented via scheduled job (predict-worker, daily at 03:00 UTC).

```sql
-- Feed runs: 90 days
DELETE FROM prediction_feed_runs WHERE created_at < now() - interval '90 days';

-- Rule executions: 90 days
DELETE FROM prediction_rule_executions WHERE started_at < now() - interval '90 days';

-- Unmatched signals (resolved): 30 days
DELETE FROM prediction_unmatched_signals
  WHERE status IN ('DISCARDED', 'MATCHED') AND created_at < now() - interval '30 days';

-- Snapshots: downsample after 90 days (keep 1 per hour), delete after 2 years
-- (implemented in application code, not raw SQL)

-- Audit log: move to S3 cold storage after 90 days, delete from DB after 2 years
-- (implemented in application code)
```

---

## Table Count Summary

| Category | Tables | Purpose |
|----------|--------|---------|
| Auth & Identity | 6 | users, orgs, members, subscriptions, API keys, usage |
| Core Prediction | 4 | categories, events, signals, snapshots |
| Intelligence | 1 | explanations |
| Trust | 2 | trust_scores, trust_flags |
| Calibration | 3 | resolutions, disputes, calibration_scores |
| Marketing | 4 | campaigns, campaign_events, variants, results |
| Triggers | 3 | rules, executions, connections |
| Data Pipeline | 4 | sources, feeds, runs, unmatched_signals |
| Audit | 1 | audit_log |
| **Total** | **28** | |