# Yaa Database Schema Documentation

This document provides a complete inventory of all database schemas, tables,
types, and relationships used in the Yaa codebase.

---

## Table of Contents

1. [Overview](#overview)
2. [Schema Organization](#schema-organization)
3. [Custom Types (ENUMs)](#custom-types-enums)
4. [Core Tables](#core-tables)
5. [Authentication & Users](#authentication--users)
6. [Content Taxonomy](#content-taxonomy)
7. [Media Assets](#media-assets)
8. [Fact-Checking & Claims](#fact-checking--claims)
9. [Multimedia Production](#multimedia-production)
10. [Social Media Distribution](#social-media-distribution)
11. [User Engagement](#user-engagement)
12. [Notifications & Outbox](#notifications--outbox)
13. [Search Infrastructure](#search-infrastructure)
14. [Misinformation Detection](#misinformation-detection)
15. [TypeScript Type Mappings](#typescript-type-mappings)
16. [Database Functions & Triggers](#database-functions--triggers)
17. [Integration Considerations](#integration-considerations)

---

## Overview

The Yaa database uses PostgreSQL with the following characteristics:

| Attribute             | Value                |
| --------------------- | -------------------- |
| **Database**          | PostgreSQL 15+       |
| **Extensions**        | `pgcrypto`, `citext` |
| **Migration Files**   | 63 files (001-063)   |
| **Total Tables**      | 50+                  |
| **Custom ENUM Types** | 15+                  |
| **Client Library**    | `pg` (node-postgres) |

### Design Principles

1. **Timestamped Records** - All tables include `created_at` and `updated_at`
2. **JSONB Metadata** - Extensible metadata columns on major tables
3. **Case-Insensitive Text** - `CITEXT` for slugs, emails, domains
4. **Outbox Pattern** - Reliable event-driven operations
5. **Soft Deletes** - Status enums instead of physical deletion
6. **GIN Indexes** - Optimized JSONB and full-text search

---

## Schema Organization

**Migration Location:** `infrastructure/database/postgres/migrations/`

### Migration Structure

```
migrations/
├── 001_source_registry.sql      # Sources and feeds
├── 002_source_metadata.sql      # Source additional info
├── 003-010_ingestion/           # Feed item processing
├── 011_source_attributions.sql  # Attribution tracking
├── 012_articles.sql             # Core article schema
├── 013_taxonomy.sql             # Categories, topics, tags
├── 014_entities.sql             # Named entity recognition
├── 015_media_assets.sql         # Media storage
├── 016_story_clusters.sql       # Story clustering
├── 017_fact_checking.sql        # Claims and verification
├── 018_user_tips.sql            # User-submitted tips
├── 019_bias_analysis.sql        # Bias scoring
├── 020-023_auth/                # Authentication
├── 024-028_multimedia/          # Audio/video production
├── 029-036_social/              # Social accounts
├── 037-045_engagement/          # User interactions
├── 046-048_search/              # Search indexing
├── 049-053_variants/            # Content variants
├── 054-060_distribution/        # Social media publishing
└── 061-063_misinformation/      # Misinformation tracking
```

---

## Custom Types (ENUMs)

### Source & Feed Types

```sql
-- Source classification
CREATE TYPE source_kind AS ENUM (
  'publisher', 'government', 'ngo', 'blog', 'social', 'other'
);

-- Source operational status
CREATE TYPE source_status AS ENUM (
  'active', 'paused', 'retired'
);

-- Feed format types
CREATE TYPE feed_type AS ENUM (
  'rss', 'atom', 'sitemap', 'api'
);

-- Source ownership classification
CREATE TYPE ownership_type AS ENUM (
  'private', 'state', 'public', 'political', 'religious',
  'ngo', 'community', 'unknown', 'other'
);
```

### Article Types

```sql
-- Article publication status
CREATE TYPE article_status AS ENUM (
  'draft', 'published', 'scheduled', 'archived'
);

-- Translation quality indicator
CREATE TYPE translation_quality AS ENUM (
  'machine', 'human'
);

-- Article perspective stance
CREATE TYPE perspective_stance AS ENUM (
  'supporting', 'critical', 'neutral', 'analysis'
);
```

### Entity & Taxonomy Types

```sql
-- Named entity classification
CREATE TYPE entity_type AS ENUM (
  'person', 'organization', 'place', 'event', 'product', 'work', 'other'
);
```

### Media Types

```sql
-- Media asset classification
CREATE TYPE media_kind AS ENUM (
  'image', 'video', 'audio', 'embed', 'document'
);

-- Media usage context
CREATE TYPE media_role AS ENUM (
  'featured', 'inline', 'thumbnail', 'gallery', 'social', 'other'
);

-- Media origin
CREATE TYPE media_source AS ENUM (
  'extracted', 'uploaded', 'generated'
);

-- Storage provider
CREATE TYPE media_storage_provider AS ENUM (
  'external', 's3', 'gcs', 'r2', 'minio', 'local'
);
```

### Fact-Checking Types

```sql
-- Fact-check verdict
CREATE TYPE fact_check_overall_status AS ENUM (
  'verified', 'likely_true', 'disputed', 'false', 'unverified'
);
```

### Search Types

```sql
-- Search index operation
CREATE TYPE search_index_op AS ENUM (
  'upsert', 'delete'
);
```

---

## Core Tables

### Sources

Primary table for content sources (news publishers, blogs, etc.):

```sql
CREATE TABLE sources (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name              TEXT NOT NULL,
  kind              source_kind NOT NULL,
  status            source_status NOT NULL DEFAULT 'active',
  canonical_domain  CITEXT UNIQUE,
  home_url          TEXT,
  country_code      TEXT CHECK (char_length(country_code) = 2) DEFAULT 'GH',
  region            TEXT,
  languages         TEXT[] DEFAULT ARRAY['en'],
  metadata          JSONB DEFAULT '{}',
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_sources_status ON sources(status);
CREATE INDEX idx_sources_kind ON sources(kind);
CREATE INDEX idx_sources_metadata ON sources USING GIN (metadata);
```

### Source Feeds

RSS/Atom/Sitemap feed configurations:

```sql
CREATE TABLE source_feeds (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source_id               UUID NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
  feed_type               feed_type NOT NULL,
  url                     TEXT NOT NULL,
  is_active               BOOLEAN NOT NULL DEFAULT true,
  fetch_interval_seconds  INTEGER CHECK (fetch_interval_seconds > 0),
  priority                INTEGER DEFAULT 0,
  last_fetched_at         TIMESTAMPTZ,
  last_http_status        INTEGER,
  etag                    TEXT,
  last_modified           TEXT,
  consecutive_failures    INTEGER DEFAULT 0,
  backoff_until           TIMESTAMPTZ,
  metadata                JSONB DEFAULT '{}',
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (source_id, url)
);

CREATE INDEX idx_source_feeds_active ON source_feeds(is_active);
CREATE INDEX idx_source_feeds_source ON source_feeds(source_id);
CREATE INDEX idx_source_feeds_backoff ON source_feeds(backoff_until);
```

### Articles

Core content storage:

```sql
CREATE TABLE articles (
  id                              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  canonical_normalized_feed_item_id UUID UNIQUE,
  slug                            CITEXT UNIQUE NOT NULL,
  status                          article_status NOT NULL DEFAULT 'draft',
  headline                        TEXT NOT NULL,
  summary                         TEXT,
  body                            TEXT,
  body_html                       TEXT,
  language_tag                    TEXT DEFAULT 'en',
  source_id                       UUID REFERENCES sources(id) ON DELETE SET NULL,
  source_url                      TEXT,
  source_published_at             TIMESTAMPTZ,
  featured_media_asset_id         UUID REFERENCES media_assets(id) ON DELETE SET NULL,
  audio_version_url               TEXT,
  video_version_url               TEXT,
  is_original                     BOOLEAN DEFAULT false,
  is_ai_generated                 BOOLEAN DEFAULT false,
  human_reviewed                  BOOLEAN DEFAULT false,
  reviewed_by_user_id             UUID REFERENCES auth_users(id) ON DELETE SET NULL,
  published_at                    TIMESTAMPTZ,
  views                           INTEGER DEFAULT 0 CHECK (views >= 0),
  shares                          INTEGER DEFAULT 0 CHECK (shares >= 0),
  read_time_seconds               INTEGER CHECK (read_time_seconds >= 0),
  metadata                        JSONB DEFAULT '{}',
  created_at                      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_articles_status ON articles(status);
CREATE INDEX idx_articles_published ON articles(published_at DESC);
CREATE INDEX idx_articles_language ON articles(language_tag);
CREATE INDEX idx_articles_source ON articles(source_id);
CREATE INDEX idx_articles_metadata ON articles USING GIN (metadata);
```

### Authors

Article author profiles:

```sql
CREATE TABLE authors (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug        CITEXT UNIQUE NOT NULL,
  name        TEXT NOT NULL,
  bio         TEXT,
  avatar_url  TEXT,
  source_id   UUID REFERENCES sources(id) ON DELETE SET NULL,
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE article_authors (
  article_id  UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  author_id   UUID NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
  position    INTEGER DEFAULT 0 CHECK (position >= 0),
  role        TEXT,

  PRIMARY KEY (article_id, author_id)
);
```

### Article Attributions

Track source attributions for aggregated content:

```sql
CREATE TABLE article_attributions (
  article_id    UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  source_id     UUID NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
  url           TEXT NOT NULL,
  published_at  TIMESTAMPTZ,
  title         TEXT,
  language_tag  TEXT,
  metadata      JSONB DEFAULT '{}',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (article_id, url)
);
```

### Article Translations

Multi-language support:

```sql
CREATE TABLE article_translations (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id    UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  language_tag  TEXT NOT NULL,
  headline      TEXT NOT NULL,
  summary       TEXT,
  body          TEXT,
  body_html     TEXT,
  quality       translation_quality DEFAULT 'machine',
  metadata      JSONB DEFAULT '{}',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (article_id, language_tag)
);
```

### Article Perspectives

Multiple viewpoints on the same story:

```sql
CREATE TABLE article_perspectives (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id  UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  source_id   UUID REFERENCES sources(id) ON DELETE SET NULL,
  url         TEXT NOT NULL,
  stance      perspective_stance NOT NULL,
  summary     TEXT NOT NULL,
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## Authentication & Users

### Auth Users

Core user accounts:

```sql
CREATE TABLE auth_users (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email          CITEXT UNIQUE NOT NULL,
  password_hash  TEXT NOT NULL,
  name           TEXT,
  metadata       JSONB DEFAULT '{}',
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_auth_users_created ON auth_users(created_at);
CREATE INDEX idx_auth_users_metadata ON auth_users USING GIN (metadata);
```

### Auth Identities

OAuth provider integrations:

```sql
CREATE TABLE auth_identities (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id              UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  provider             TEXT NOT NULL,  -- 'google', 'facebook', 'apple'
  provider_account_id  TEXT NOT NULL,
  email                CITEXT,
  metadata             JSONB DEFAULT '{}',
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (provider, provider_account_id)
);
```

### Phone OTP Verification

```sql
CREATE TABLE auth_phone_otps (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  phone       TEXT NOT NULL,
  otp_hash    TEXT NOT NULL,
  expires_at  TIMESTAMPTZ NOT NULL,
  verified    BOOLEAN DEFAULT false,
  attempts    INTEGER DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### Password Reset Tokens

```sql
CREATE TABLE password_reset_tokens (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  token_hash  TEXT NOT NULL,
  expires_at  TIMESTAMPTZ NOT NULL,
  used_at     TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## Content Taxonomy

### Categories

Hierarchical content categories:

```sql
CREATE TABLE categories (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug        CITEXT UNIQUE NOT NULL,
  name        TEXT NOT NULL,
  description TEXT,
  parent_id   UUID REFERENCES categories(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE article_categories (
  article_id   UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  category_id  UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (article_id, category_id)
);
```

### Topics

Subject-based classification:

```sql
CREATE TABLE topics (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug        CITEXT UNIQUE NOT NULL,
  name        TEXT NOT NULL,
  description TEXT,
  category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE article_topics (
  article_id  UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  topic_id    UUID NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (article_id, topic_id)
);
```

### Tags

Free-form keywords:

```sql
CREATE TABLE tags (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug        CITEXT UNIQUE NOT NULL,
  label       TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE article_tags (
  article_id  UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  tag_id      UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (article_id, tag_id)
);
```

---

## Media Assets

### Media Assets Table

```sql
CREATE TABLE media_assets (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  kind              media_kind NOT NULL,
  role              media_role NOT NULL DEFAULT 'other',
  source            media_source NOT NULL DEFAULT 'extracted',
  original_url      TEXT NOT NULL,
  cdn_url           TEXT,
  mime_type         TEXT,
  width             INTEGER CHECK (width >= 0),
  height            INTEGER CHECK (height >= 0),
  duration_seconds  INTEGER CHECK (duration_seconds >= 0),
  size_bytes        BIGINT CHECK (size_bytes >= 0),
  checksum          TEXT,
  alt_text          TEXT,
  caption           TEXT,
  credit            TEXT,
  storage_provider  media_storage_provider DEFAULT 'external',
  storage_bucket    TEXT,
  storage_key       TEXT,
  metadata          JSONB DEFAULT '{}',
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (kind, role, original_url),

  -- Ensure storage consistency
  CHECK (
    (storage_provider = 'external') OR
    (storage_bucket IS NOT NULL AND storage_key IS NOT NULL)
  )
);

CREATE INDEX idx_media_assets_kind ON media_assets(kind);
CREATE INDEX idx_media_assets_role ON media_assets(role);
CREATE INDEX idx_media_assets_source ON media_assets(source);
CREATE INDEX idx_media_assets_created ON media_assets(created_at);
CREATE INDEX idx_media_assets_storage ON media_assets(storage_provider);
CREATE INDEX idx_media_assets_metadata ON media_assets USING GIN (metadata);
```

### Article Media Junction

```sql
CREATE TABLE article_media (
  article_id      UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  media_asset_id  UUID NOT NULL REFERENCES media_assets(id) ON DELETE CASCADE,
  position        INTEGER DEFAULT 0 CHECK (position >= 0),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (article_id, media_asset_id)
);
```

---

## Fact-Checking & Claims

### Claims

Extracted claims for verification:

```sql
CREATE TABLE claims (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  text            TEXT NOT NULL,
  normalized_text TEXT NOT NULL UNIQUE,
  metadata        JSONB DEFAULT '{}',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE article_claims (
  article_id  UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  claim_id    UUID NOT NULL REFERENCES claims(id) ON DELETE CASCADE,
  position    INTEGER DEFAULT 0 CHECK (position >= 0),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (article_id, claim_id)
);
```

### Article Fact-Check Status

Overall fact-check status per article:

```sql
CREATE TABLE article_fact_checks (
  article_id       UUID PRIMARY KEY REFERENCES articles(id) ON DELETE CASCADE,
  overall_status   fact_check_overall_status NOT NULL DEFAULT 'unverified',
  last_checked_at  TIMESTAMPTZ,
  metadata         JSONB DEFAULT '{}',
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### Claim Verifications

Individual claim verification results:

```sql
CREATE TABLE claim_verifications (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  claim_id    UUID NOT NULL REFERENCES claims(id) ON DELETE CASCADE,
  status      fact_check_overall_status NOT NULL,
  confidence  REAL CHECK (confidence >= 0 AND confidence <= 1),
  notes       TEXT[],
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### Claim Evidence

Supporting evidence for verifications:

```sql
CREATE TABLE claim_evidence (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  claim_verification_id UUID NOT NULL REFERENCES claim_verifications(id) ON DELETE CASCADE,
  url                   TEXT NOT NULL,
  source_id             UUID REFERENCES sources(id) ON DELETE SET NULL,
  quote                 TEXT,
  retrieved_at          TIMESTAMPTZ,
  metadata              JSONB DEFAULT '{}',
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## Multimedia Production

### Voice Profiles

TTS voice configurations:

```sql
CREATE TABLE voice_profiles (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          CITEXT UNIQUE NOT NULL,
  name          TEXT NOT NULL,
  provider      TEXT NOT NULL,  -- 'elevenlabs', 'ghana_nlp'
  provider_id   TEXT,
  language_tag  TEXT NOT NULL DEFAULT 'en',
  is_active     BOOLEAN DEFAULT true,
  settings      JSONB DEFAULT '{}',
  metadata      JSONB DEFAULT '{}',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### Article Audio Renders

Audio production records:

```sql
CREATE TABLE article_audio_renders (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id      UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  voice_profile_id UUID NOT NULL REFERENCES voice_profiles(id),
  status          TEXT NOT NULL DEFAULT 'pending',  -- 'pending', 'processing', 'completed', 'failed'
  url             TEXT,
  duration_seconds INTEGER,
  size_bytes      BIGINT,
  error_message   TEXT,
  metadata        JSONB DEFAULT '{}',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (article_id, voice_profile_id)
);
```

### Avatar Profiles

AI anchor configurations:

```sql
CREATE TABLE avatar_profiles (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          CITEXT UNIQUE NOT NULL,
  name          TEXT NOT NULL,
  provider      TEXT NOT NULL DEFAULT 'heygen',
  provider_id   TEXT,
  is_active     BOOLEAN DEFAULT true,
  is_fallback   BOOLEAN DEFAULT false,
  settings      JSONB DEFAULT '{}',
  metadata      JSONB DEFAULT '{}',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### Article Video Renders

Video production records:

```sql
CREATE TABLE article_video_renders (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id       UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  avatar_profile_id UUID NOT NULL REFERENCES avatar_profiles(id),
  status           TEXT NOT NULL DEFAULT 'pending',
  video_url        TEXT,
  thumbnail_url    TEXT,
  hls_url          TEXT,
  duration_seconds INTEGER,
  resolution       TEXT,
  size_bytes       BIGINT,
  error_message    TEXT,
  metadata         JSONB DEFAULT '{}',
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (article_id, avatar_profile_id)
);
```

---

## Social Media Distribution

### YouTube Shorts Schedule

```sql
CREATE TABLE youtube_shorts_schedule_runs (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id      UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  video_render_id UUID REFERENCES article_video_renders(id),
  status          TEXT NOT NULL DEFAULT 'pending',
  scheduled_at    TIMESTAMPTZ,
  published_at    TIMESTAMPTZ,
  youtube_id      TEXT,
  url             TEXT,
  error_message   TEXT,
  metadata        JSONB DEFAULT '{}',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### WhatsApp Integration

```sql
CREATE TABLE whatsapp_channel_subscribers (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  phone        TEXT NOT NULL UNIQUE,
  name         TEXT,
  subscribed   BOOLEAN DEFAULT true,
  preferences  JSONB DEFAULT '{}',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE whatsapp_channel_posts (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id  UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  message     TEXT NOT NULL,
  media_url   TEXT,
  status      TEXT NOT NULL DEFAULT 'pending',
  sent_at     TIMESTAMPTZ,
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE whatsapp_channel_messages (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id     UUID NOT NULL REFERENCES whatsapp_channel_posts(id) ON DELETE CASCADE,
  subscriber_id UUID NOT NULL REFERENCES whatsapp_channel_subscribers(id),
  status      TEXT NOT NULL DEFAULT 'pending',
  sent_at     TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ,
  read_at     TIMESTAMPTZ,
  error       TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### TikTok Integration

```sql
CREATE TABLE tiktok_schedule_runs (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id      UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  video_render_id UUID REFERENCES article_video_renders(id),
  status          TEXT NOT NULL DEFAULT 'pending',
  scheduled_at    TIMESTAMPTZ,
  published_at    TIMESTAMPTZ,
  tiktok_id       TEXT,
  url             TEXT,
  error_message   TEXT,
  metadata        JSONB DEFAULT '{}',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tiktok_analytics (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tiktok_video_id TEXT NOT NULL,
  schedule_run_id UUID REFERENCES tiktok_schedule_runs(id),
  views           INTEGER DEFAULT 0,
  likes           INTEGER DEFAULT 0,
  comments        INTEGER DEFAULT 0,
  shares          INTEGER DEFAULT 0,
  fetched_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  metadata        JSONB DEFAULT '{}',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tiktok_sound_trends (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sound_id     TEXT NOT NULL UNIQUE,
  name         TEXT,
  author       TEXT,
  usage_count  INTEGER DEFAULT 0,
  trending_at  TIMESTAMPTZ,
  metadata     JSONB DEFAULT '{}',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## User Engagement

### User Article Reads

```sql
CREATE TABLE user_article_reads (
  user_id       UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  article_id    UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  first_read_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_read_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  read_count    INTEGER DEFAULT 1 CHECK (read_count >= 0),
  metadata      JSONB DEFAULT '{}',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (user_id, article_id)
);
```

### User Follows

```sql
CREATE TABLE user_topic_follows (
  user_id     UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  topic_id    UUID NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (user_id, topic_id)
);

CREATE TABLE user_source_preferences (
  user_id     UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  source_id   UUID NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
  preference  TEXT NOT NULL,  -- 'follow', 'mute', 'block'
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (user_id, source_id)
);

CREATE TABLE user_author_follows (
  user_id     UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  author_id   UUID NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (user_id, author_id)
);

CREATE TABLE user_saved_articles (
  user_id     UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  article_id  UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (user_id, article_id)
);
```

---

## Notifications & Outbox

### Push Notification Infrastructure

```sql
CREATE TABLE user_push_tokens (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  token       TEXT NOT NULL,
  provider    TEXT NOT NULL DEFAULT 'fcm',  -- 'fcm', 'apns'
  device_type TEXT,  -- 'ios', 'android', 'web'
  is_active   BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (user_id, token)
);

CREATE TABLE push_notification_events (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  kind                 TEXT NOT NULL,
  provider             TEXT DEFAULT 'fcm',
  article_id           UUID REFERENCES articles(id) ON DELETE SET NULL,
  status               TEXT NOT NULL DEFAULT 'pending',  -- 'pending', 'sent', 'failed'
  attempts             INTEGER DEFAULT 0 CHECK (attempts >= 0),
  success_count        INTEGER DEFAULT 0 CHECK (success_count >= 0),
  failure_count        INTEGER DEFAULT 0 CHECK (failure_count >= 0),
  invalid_token_count  INTEGER DEFAULT 0 CHECK (invalid_token_count >= 0),
  payload              JSONB,
  metadata             JSONB DEFAULT '{}',
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (kind, provider, article_id)
);

CREATE TABLE push_notification_outbox (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id         UUID NOT NULL REFERENCES push_notification_events(id) ON DELETE CASCADE,
  user_push_token_id UUID NOT NULL REFERENCES user_push_tokens(id) ON DELETE CASCADE,
  status           TEXT NOT NULL DEFAULT 'pending',
  attempts         INTEGER DEFAULT 0,
  last_error       TEXT,
  sent_at          TIMESTAMPTZ,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE push_notification_clicks (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id    UUID NOT NULL REFERENCES push_notification_events(id) ON DELETE CASCADE,
  user_id     UUID REFERENCES auth_users(id) ON DELETE SET NULL,
  clicked_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  metadata    JSONB DEFAULT '{}'
);
```

---

## Search Infrastructure

### Search Index Outbox

Implements outbox pattern for Elasticsearch synchronization:

```sql
CREATE TABLE search_index_outbox (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id  UUID NOT NULL UNIQUE,
  op          search_index_op NOT NULL,
  status      TEXT NOT NULL DEFAULT 'queued',  -- 'queued', 'processing', 'completed', 'failed'
  not_before  TIMESTAMPTZ DEFAULT now(),
  attempts    INTEGER DEFAULT 0 CHECK (attempts >= 0),
  last_error  TEXT,
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_search_outbox_status ON search_index_outbox(status, not_before);
CREATE INDEX idx_search_outbox_article ON search_index_outbox(article_id);
```

### Search Query Events

User search analytics:

```sql
CREATE TABLE search_query_events (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID REFERENCES auth_users(id) ON DELETE SET NULL,
  query       TEXT NOT NULL,
  filters     JSONB DEFAULT '{}',
  result_count INTEGER,
  clicked_article_id UUID REFERENCES articles(id) ON DELETE SET NULL,
  session_id  TEXT,
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_search_events_user ON search_query_events(user_id);
CREATE INDEX idx_search_events_created ON search_query_events(created_at DESC);
```

---

## Misinformation Detection

### External Fact-Checks

Aggregated fact-checks from external sources:

```sql
CREATE TABLE external_fact_checks (
  id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  external_id              VARCHAR(255),
  source                   VARCHAR(50) NOT NULL CHECK (
    source IN ('claimbuster', 'google', 'fullfact', 'snopes',
               'politifact', 'afp', 'other')
  ),
  claim_text               TEXT NOT NULL,
  claim_text_normalized    TEXT NOT NULL,
  claim_text_tsv           TSVECTOR GENERATED ALWAYS AS (
    to_tsvector('english', claim_text_normalized)
  ) STORED,
  rating                   VARCHAR(50) CHECK (
    rating IN ('true', 'mostly_true', 'half_true', 'mostly_false',
               'false', 'pants_on_fire', 'unverifiable',
               'misleading', 'satire', 'unknown')
  ),
  rating_score             DECIMAL(3,2) CHECK (rating_score >= 0 AND rating_score <= 1),
  fact_check_url           TEXT NOT NULL,
  publisher_name           VARCHAR(255),
  publisher_url            TEXT,
  publisher_ifcn_certified BOOLEAN,
  publisher_country        VARCHAR(10),
  claim_date               TIMESTAMPTZ,
  review_date              TIMESTAMPTZ NOT NULL,
  claimant                 VARCHAR(255),
  review_text              TEXT,
  language                 VARCHAR(10) DEFAULT 'en',
  raw_data                 JSONB,
  imported_at              TIMESTAMPTZ DEFAULT now(),
  last_synced_at           TIMESTAMPTZ,
  is_active                BOOLEAN DEFAULT true,
  created_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at               TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (source, external_id)
);

CREATE INDEX idx_external_fc_source ON external_fact_checks(source);
CREATE INDEX idx_external_fc_rating ON external_fact_checks(rating);
CREATE INDEX idx_external_fc_score ON external_fact_checks(rating_score);
CREATE INDEX idx_external_fc_review ON external_fact_checks(review_date DESC);
CREATE INDEX idx_external_fc_tsv ON external_fact_checks USING GIN (claim_text_tsv);
CREATE INDEX idx_external_fc_publisher ON external_fact_checks(publisher_name);
CREATE INDEX idx_external_fc_language ON external_fact_checks(language);
CREATE INDEX idx_external_fc_normalized ON external_fact_checks(claim_text_normalized);
```

### Tracked Claims

Cross-source claim tracking:

```sql
CREATE TABLE tracked_claims (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  normalized_claim      TEXT NOT NULL UNIQUE,
  normalized_claim_tsv  TSVECTOR GENERATED ALWAYS AS (
    to_tsvector('english', normalized_claim)
  ) STORED,
  claim_variations      JSONB[] DEFAULT ARRAY[]::JSONB[],
  first_seen            TIMESTAMPTZ DEFAULT now(),
  last_updated          TIMESTAMPTZ DEFAULT now(),
  consensus_rating      VARCHAR(50),
  consensus_score       DECIMAL(3,2),
  external_checks_count INTEGER DEFAULT 0,
  internal_checks_count INTEGER DEFAULT 0,
  article_mentions      INTEGER DEFAULT 0,
  metadata              JSONB DEFAULT '{}',
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_tracked_claims_tsv ON tracked_claims USING GIN (normalized_claim_tsv);
CREATE INDEX idx_tracked_claims_rating ON tracked_claims(consensus_rating);
CREATE INDEX idx_tracked_claims_updated ON tracked_claims(last_updated DESC);
```

### Misinformation Signals

Detected misinformation patterns:

```sql
CREATE TABLE misinformation_signals (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type                VARCHAR(50) NOT NULL CHECK (
    type IN ('viral_false_claim', 'coordinated_spread', 'emerging_narrative',
             'recirculating_debunked', 'bot_amplification', 'political_manipulation',
             'health_misinformation', 'election_misinformation',
             'financial_scam', 'celebrity_hoax')
  ),
  severity            VARCHAR(20) NOT NULL CHECK (
    severity IN ('low', 'medium', 'high', 'critical')
  ),
  title               VARCHAR(500) NOT NULL,
  description         TEXT,
  details             JSONB DEFAULT '{}',
  related_claims      JSONB[] DEFAULT ARRAY[]::JSONB[],
  primary_claim_id    UUID REFERENCES tracked_claims(id),
  detected_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  detection_method    VARCHAR(100),
  confidence_score    DECIMAL(3,2) CHECK (confidence_score >= 0 AND confidence_score <= 1),
  metrics             JSONB DEFAULT '{}',
  affected_regions    JSONB[] DEFAULT ARRAY[]::JSONB[],
  primary_region      VARCHAR(100),
  status              VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (
    status IN ('active', 'monitoring', 'contained', 'resolved', 'false_positive')
  ),
  resolution_notes    TEXT,
  resolved_at         TIMESTAMPTZ,
  resolved_by         UUID REFERENCES auth_users(id),
  recommended_action  TEXT,
  action_taken        TEXT,
  last_activity_at    TIMESTAMPTZ DEFAULT now(),
  escalation_count    INTEGER DEFAULT 0,
  metadata            JSONB DEFAULT '{}',
  tags                JSONB[] DEFAULT ARRAY[]::JSONB[],
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_signals_type ON misinformation_signals(type);
CREATE INDEX idx_signals_severity ON misinformation_signals(severity);
CREATE INDEX idx_signals_status ON misinformation_signals(status);
CREATE INDEX idx_signals_detected ON misinformation_signals(detected_at DESC);
CREATE INDEX idx_signals_claim ON misinformation_signals(primary_claim_id);
CREATE INDEX idx_signals_region ON misinformation_signals(primary_region);
CREATE INDEX idx_signals_tags ON misinformation_signals USING GIN (tags);
```

### Misinformation Alerts

Editorial notifications:

```sql
CREATE TABLE misinformation_alerts (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  signal_id   UUID NOT NULL REFERENCES misinformation_signals(id) ON DELETE CASCADE,
  user_id     UUID REFERENCES auth_users(id) ON DELETE SET NULL,
  channel     TEXT NOT NULL,  -- 'email', 'slack', 'push', 'sms'
  status      TEXT NOT NULL DEFAULT 'pending',
  sent_at     TIMESTAMPTZ,
  read_at     TIMESTAMPTZ,
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## TypeScript Type Mappings

### Location

Type definitions are in `packages/models/src/`

### Core Types

```typescript
// packages/models/src/article.ts
type ArticleStatus = 'draft' | 'published' | 'scheduled' | 'archived';

interface Article extends Timestamped {
  id: Uuid;
  slug: string;
  status: ArticleStatus;
  headline: string;
  summary: string;
  body: string;
  bodyHtml: string;
  featuredImage: MediaAsset | null;
  media: MediaAsset[];
  audioVersion: UrlString | null;
  videoVersion: UrlString | null;
  categories: Category[];
  tags: Tag[];
  entities: Entity[];
  topics: Topic[];
  source: Source;
  sourceUrl: UrlString;
  sourcePublishedAt: Date;
  authors: Author[];
  attributions: ArticleAttribution[];
  isOriginal: boolean;
  isAIGenerated: boolean;
  humanReviewed: boolean;
  reviewedBy: UserRef | null;
  factCheckStatus: FactCheckStatus;
  claims: Claim[];
  biasScore: BiasScore;
  perspectives: Perspective[];
  storyCluster: StoryCluster | null;
  relatedArticles: Uuid[];
  languageTag: LanguageTag;
  translations: Translation[];
  publishedAt: Date;
  views: number;
  shares: number;
  readTimeSeconds: number;
  metadata: Record<string, unknown>;
}
```

```typescript
// packages/models/src/source.ts
type SourceKind =
  | 'publisher'
  | 'government'
  | 'ngo'
  | 'blog'
  | 'social'
  | 'other';
type SourceStatus = 'active' | 'paused' | 'retired';
type FeedType = 'rss' | 'atom' | 'sitemap' | 'api';

interface Source extends Timestamped {
  id: Uuid;
  name: string;
  kind: SourceKind;
  status: SourceStatus;
  canonicalDomain: string | null;
  homeUrl: UrlString | null;
  countryCode: CountryCode;
  region: string | null;
  languages: LanguageTag[];
  notes: string | null;
  ownership: SourceOwnership;
  bias: SourceBiasMetadata;
  metadata: Record<string, unknown>;
}
```

```typescript
// packages/models/src/entity.ts
type EntityType =
  | 'person'
  | 'organization'
  | 'place'
  | 'event'
  | 'product'
  | 'work'
  | 'other';

interface Entity extends Timestamped {
  id: Uuid;
  type: EntityType;
  name: string;
  normalizedName: string;
  summary: string | null;
  externalIds: Record<string, string>;
  metadata: Record<string, unknown>;
}
```

```typescript
// packages/models/src/media.ts
type MediaKind = 'image' | 'video' | 'audio' | 'embed' | 'document';
type MediaRole =
  | 'featured'
  | 'inline'
  | 'thumbnail'
  | 'gallery'
  | 'social'
  | 'other';
type MediaStorageProvider =
  | 'external'
  | 's3'
  | 'gcs'
  | 'r2'
  | 'minio'
  | 'local';

interface MediaAsset extends Timestamped {
  id: Uuid;
  kind: MediaKind;
  role: MediaRole;
  source: MediaSource;
  originalUrl: UrlString;
  cdnUrl: UrlString | null;
  mimeType: string | null;
  width: number | null;
  height: number | null;
  durationSeconds: number | null;
  sizeBytes: number | null;
  checksum: string | null;
  altText: string | null;
  caption: string | null;
  credit: string | null;
  storage: MediaStorage;
  metadata: Record<string, unknown>;
}
```

```typescript
// packages/models/src/fact-check.ts
type FactCheckOverallStatus =
  | 'verified'
  | 'likely_true'
  | 'disputed'
  | 'false'
  | 'unverified';

interface Claim extends Timestamped {
  id: Uuid;
  text: string;
  normalizedText: string;
  metadata: Record<string, unknown>;
}

interface FactCheckStatus {
  overall: FactCheckOverallStatus;
  claims: ClaimVerification[];
  lastCheckedAt: Date;
}
```

---

## Database Functions & Triggers

### Timestamp Trigger

Automatically updates `updated_at` on all tables:

```sql
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Applied to all tables with updated_at column
CREATE TRIGGER tr_sources_updated_at
  BEFORE UPDATE ON sources
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();
```

### Search Index Enqueueing

Functions for search index synchronization:

```sql
CREATE OR REPLACE FUNCTION enqueue_article_search_index(
  p_article_id UUID,
  p_op search_index_op,
  p_metadata JSONB DEFAULT '{}'
) RETURNS UUID AS $$
DECLARE
  v_id UUID;
BEGIN
  INSERT INTO search_index_outbox (article_id, op, metadata)
  VALUES (p_article_id, p_op, p_metadata)
  ON CONFLICT (article_id) DO UPDATE
  SET op = p_op,
      status = 'queued',
      not_before = now(),
      attempts = 0,
      last_error = NULL,
      metadata = p_metadata,
      updated_at = now()
  RETURNING id INTO v_id;

  RETURN v_id;
END;
$$ LANGUAGE plpgsql;

-- Trigger functions for automatic enqueueing
CREATE OR REPLACE FUNCTION enqueue_article_search_index_upsert()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM enqueue_article_search_index(NEW.id, 'upsert');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION enqueue_article_search_index_delete()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM enqueue_article_search_index(OLD.id, 'delete');
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- Triggers on articles table
CREATE TRIGGER tr_articles_search_upsert
  AFTER INSERT OR UPDATE ON articles
  FOR EACH ROW
  EXECUTE FUNCTION enqueue_article_search_index_upsert();

CREATE TRIGGER tr_articles_search_delete
  AFTER DELETE ON articles
  FOR EACH ROW
  EXECUTE FUNCTION enqueue_article_search_index_delete();

-- Triggers on junction tables for search index updates
CREATE TRIGGER tr_article_categories_search
  AFTER INSERT OR DELETE ON article_categories
  FOR EACH ROW
  EXECUTE FUNCTION enqueue_article_search_index_touch_article_id();

CREATE TRIGGER tr_article_topics_search
  AFTER INSERT OR DELETE ON article_topics
  FOR EACH ROW
  EXECUTE FUNCTION enqueue_article_search_index_touch_article_id();

CREATE TRIGGER tr_article_tags_search
  AFTER INSERT OR DELETE ON article_tags
  FOR EACH ROW
  EXECUTE FUNCTION enqueue_article_search_index_touch_article_id();
```

---

## Integration Considerations

### Oshun Migration Notes

1. **Schema Compatibility**
   - Yaa uses `CITEXT` extension for case-insensitive slugs
   - All UUIDs are generated with `gen_random_uuid()` (pgcrypto)
   - JSONB metadata columns allow flexible extension

2. **Type System Alignment**
   - ENUM types should be mapped to Oshun conventions
   - TypeScript interfaces in `packages/models/` provide type safety
   - Consider Prisma migration for enhanced type generation

3. **Outbox Pattern Integration**
   - `search_index_outbox` demonstrates reliable event delivery
   - Pattern can be extended for cross-domain events
   - Consider using shared outbox infrastructure

4. **Performance Considerations**
   - GIN indexes on JSONB columns are essential
   - TSVector indexes for full-text search
   - Foreign key indexes for JOIN performance

5. **Multi-Tenancy**
   - Current schema is single-tenant
   - Consider tenant_id column for multi-org support
   - Row-level security policies may be needed

---

_Last Updated: January 2026_

_Part of the Veritas domain integration documentation_
