This document provides a complete inventory of all database schemas, tables, types, and relationships used in the Yaa codebase.
Table of Contents#
- Overview
- Schema Organization
- Custom Types (ENUMs)
- Core Tables
- Authentication & Users
- Content Taxonomy
- Media Assets
- Fact-Checking & Claims
- Multimedia Production
- Social Media Distribution
- User Engagement
- Notifications & Outbox
- Search Infrastructure
- Misinformation Detection
- TypeScript Type Mappings
- Database Functions & Triggers
- 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#
- Timestamped Records - All tables include
created_atandupdated_at - JSONB Metadata - Extensible metadata columns on major tables
- Case-Insensitive Text -
CITEXTfor slugs, emails, domains - Outbox Pattern - Reliable event-driven operations
- Soft Deletes - Status enums instead of physical deletion
- 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#
-- 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#
-- 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#
-- Named entity classification
CREATE TYPE entity_type AS ENUM (
'person', 'organization', 'place', 'event', 'product', 'work', 'other'
);
Media Types#
-- 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#
-- Fact-check verdict
CREATE TYPE fact_check_overall_status AS ENUM (
'verified', 'likely_true', 'disputed', 'false', 'unverified'
);
Search Types#
-- Search index operation
CREATE TYPE search_index_op AS ENUM (
'upsert', 'delete'
);
Core Tables#
Sources#
Primary table for content sources (news publishers, blogs, etc.):
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:
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:
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:
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:
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:
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:
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:
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:
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#
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#
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:
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:
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:
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#
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#
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:
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:
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:
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:
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:
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:
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:
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:
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#
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#
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#
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#
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#
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#
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:
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:
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:
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:
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:
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:
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#
// 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>;
}
// 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>;
}
// 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>;
}
// 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>;
}
// 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:
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:
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#
-
Schema Compatibility
- Yaa uses
CITEXTextension for case-insensitive slugs - All UUIDs are generated with
gen_random_uuid()(pgcrypto) - JSONB metadata columns allow flexible extension
- Yaa uses
-
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
-
Outbox Pattern Integration
search_index_outboxdemonstrates reliable event delivery- Pattern can be extended for cross-domain events
- Consider using shared outbox infrastructure
-
Performance Considerations
- GIN indexes on JSONB columns are essential
- TSVector indexes for full-text search
- Foreign key indexes for JOIN performance
-
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