Eve library

Veritas Database Schema

Guide

Revision 1

On this page

Complete reference for the Veritas PostgreSQL database schema managed by Prisma ORM.

ORM: Prisma Database: PostgreSQL Extensions: pgvector (vector similarity search), citext (case-insensitive text) Schema file: libs/veritas/database/prisma/schema.prisma Connection: VERITAS_DATABASE_URL environment variable


Table of Contents#


Enums#

Source Management Enums#

SourceStatus#

Value Description
active Currently ingesting content
paused Temporarily stopped
retired Permanently deactivated
pending Awaiting activation

SourceKind#

Value Description
newspaper Print newspaper
broadcaster TV or radio broadcaster
news_agency Wire service or news agency
digital_native Online-only news outlet
wire_service Wire service (AFP, Reuters)
publisher Book or magazine publisher
government Government source
ngo Non-governmental organization
blog Blog or independent writer
social Social media source
academic Academic institution
other Uncategorized

OwnershipType#

Value Description
private Privately owned
state State-owned
public Publicly traded
political Owned by political entity
religious Owned by religious body
ngo Non-profit organization
community Community-owned
academic Academic institution
unknown Ownership not determined
other Other ownership type

FeedType#

Value Description
rss RSS feed
atom Atom feed
sitemap XML sitemap
api API endpoint
scrape Web scraping

Article Enums#

ArticleStatus#

Value Description
draft Not yet published
pending_review Awaiting editorial review
published Publicly visible
scheduled Scheduled for future publish
archived Archived / no longer prominent
retracted Retracted due to errors

ArticleContentType#

Value Description
news Straight news report
analysis In-depth analysis
opinion Opinion or editorial
feature Feature story
interview Interview
investigation Investigative report
breaking Breaking news
live_update Live-updating story
explainer Explainer piece
fact_check Fact-check article

TranslationQuality#

Value Description
machine Machine-translated
human Human-translated
verified Machine + human verification

PerspectiveStance#

Value Description
supporting Supports the article's view
critical Critical perspective
neutral Balanced perspective
analysis Analytical perspective

ContentPriority#

Value Description
breaking Breaking news priority
high High priority
normal Normal priority
low Low priority

ContributorRole#

Value Description
author Primary author
editor Editor
reviewer Reviewer
translator Translator
photographer Photographer
illustrator Illustrator
contributor General contributor

Entity Enums#

EntityType#

Value Description
person Individual person
organization Organization or company
place Geographic location
event Event
product Product or service
work Creative work
concept Abstract concept
legislation Law or regulation
other Uncategorized

Media Enums#

MediaKind#

Value Description
image Photograph or image
video Video
audio Audio clip
embed Embedded content
document Document (PDF, etc.)
infographic Infographic

MediaRole#

Value Description
featured Featured/hero media
inline Inline within article body
thumbnail Thumbnail image
gallery Part of a gallery
social Social media share image
hero Hero banner image
background Background image
other Other role

MediaSource#

Value Description
extracted Extracted from source
uploaded Manually uploaded
generated AI-generated
licensed Licensed from provider

MediaStorageProvider#

Value Description
external External URL
s3 Amazon S3
gcs Google Cloud Storage
r2 Cloudflare R2
minio MinIO (local dev)
local Local filesystem
cdn CDN-hosted

Fact-Checking Enums#

FactCheckOverallStatus#

Value Description
verified Verified as accurate
likely_true Likely true
disputed Disputed by sources
misleading Contains misleading content
mostly_false Mostly false
false Determined to be false
unverifiable Cannot be verified
unverified Not yet checked

Note: false is mapped as false_status in the Prisma enum (@map("false")).

ClaimType#

Value Description
factual Factual claim
opinion Opinion
prediction Future prediction
quote Direct quote
statistical Statistical claim
historical Historical claim
scientific Scientific claim

EvidenceStance#

Value Description
supports Evidence supports the claim
refutes Evidence refutes the claim
neutral Evidence is neutral
inconclusive Evidence is inconclusive

Clustering Enums#

ClusteringMethod#

Value Description
semantic_similarity Embedding-based similarity
entity_overlap Shared entity overlap
temporal_proximity Time-based proximity
topic_modeling Topic model clustering
hybrid Combination of methods
manual Manually created cluster

StoryClusterStatus#

Value Description
active Active developing story
developing Still developing
archived No longer active
merged Merged into another cluster

Voice/Audio Enums#

EmbeddingProvider#

Value Description
openai OpenAI embeddings
cohere Cohere embeddings

VoiceProvider#

Value Description
elevenlabs ElevenLabs TTS
ghana_nlp Ghana NLP TTS
openai OpenAI TTS
google Google Cloud TTS

Social/Scheduling Enums#

SocialPlatform#

Value Description
x X (Twitter)
facebook Facebook
instagram Instagram
tiktok TikTok
youtube YouTube

ScheduleRunStatus#

Value Description
claimed Claimed by a worker
running Currently executing
completed Successfully completed
failed Failed with error
skipped Skipped (e.g., no content)

WhatsAppSubscriberStatus#

Value Description
active Active subscriber
unsubscribed Unsubscribed

WhatsAppPostStatus#

Value Description
running Currently sending
completed All messages sent
failed Sending failed

User Enums#

SourcePreferenceType#

Value Description
prefer User prefers this source
avoid User wants to avoid source

AI Processing Enums#

CorrectionKind#

Value Description
correction Factual correction
clarification Clarification
update Updated information
retraction Full retraction

CorrectionSeverity#

Value Description
low Minor correction
medium Moderate correction
high Significant correction

AIReviewKind#

Value Description
managing_editor_qc_v1 Managing editor quality control
editor_in_chief_standards_v1 Editor-in-chief standards review

NMC Compliance Enums#

NmcViolationCategory#

Value Description
indecency Indecent content
inaccuracy Factual inaccuracy
offending_sensibilities Content offending cultural sensibilities
prejudice_stereotyping Prejudice or stereotyping
mixing_facts_opinion Mixing facts with opinion
invasion_of_privacy Invasion of privacy
defamation Defamatory content
incitement Incitement to violence or disorder
contempt_of_court Contempt of court
endangering_national_security Endangering national security
plagiarism Plagiarism
unfair_reporting Unfair or unbalanced reporting
minors_exploitation Exploitation of minors
advertising_deception Deceptive advertising

NmcComplaintStage#

Value Description
filed Complaint filed
under_review Under initial review
settlement_meeting Settlement meeting stage
investigation Under investigation
deliberation NMC deliberation
ruling Ruling issued
compliance Awaiting compliance with ruling
closed Case closed

NmcComplaintSeverity#

Value Description
critical Critical violation
high High severity
medium Medium severity
low Low severity

NmcComplainantType#

Value Description
individual Individual person
organization Organization
regulator Regulatory body
automated Automated detection

NmcRemedy#

Value Description
retraction Full retraction of content
formal_apology Formal public apology
rejoinder Right of rejoinder/reply
correction_with_prominence Correction with equal prominence
content_removal Content removal
written_warning Written warning
public_censure Public censure by NMC

DPA Compliance Enums#

DpaLawfulBasis#

Value Description
consent Explicit consent
contract Necessary for contract performance
legal_obligation Required by law
legitimate_interest Legitimate interest
public_duty Necessary for public duty
vital_interest Protecting vital interests
journalism_exemption Journalism exemption under Act 843 s.62

DpaProcessingPurpose#

Value Description
account_management Managing user accounts
content_delivery Delivering content
personalization Personalizing experience
analytics Analytics and insights
advertising Advertising
push_notifications Push notification delivery
email_communications Email communications
research Research purposes
journalism Journalistic purposes
security Security measures
legal_compliance Legal compliance

DpaRequestType#

Value Description
access Access personal data
correction Correct personal data
erasure Erase personal data
objection Object to processing
consent_withdrawal Withdraw consent
data_export Export personal data
restrict_processing Restrict processing

DpaRequestStatus#

Value Description
submitted Request submitted
acknowledged Request acknowledged
processing Being processed
completed Request fulfilled
refused Request refused
expired Request expired

Election Coverage Enums#

ElectionType#

Value Description
presidential Presidential election
parliamentary Parliamentary election
district_assembly District assembly
referendum National referendum
by_election By-election

ElectionCoverageViolationType#

Value Description
unequal_coverage Unequal coverage of parties/candidates
silence_period_violation Violation of election silence period
unauthorized_results Publishing unauthorized election results
false_election_information Spreading false election information
incitement_to_violence Incitement to election violence
partisan_language Use of partisan language
missing_rejoinder Missing right of rejoinder
candidate_endorsement Unauthorized candidate endorsement
hate_speech Hate speech
voter_intimidation Voter intimidation
campaign_advertising_undisclosed Undisclosed campaign advertising
exit_poll_during_voting Publishing exit polls during voting
accreditation_violation Media accreditation violation

CoverageComplianceLevel#

Value Description
compliant Fully compliant
minor_violation Minor violation
major_violation Major violation
critical_violation Critical violation

Payment Enums#

PaymentTransactionType#

Value Description
collection Collection from user
disbursement Disbursement to user
refund Refund

PaymentTransactionStatus#

Value Description
initiated Transaction initiated
pending Waiting for confirmation
successful Successfully completed
failed Transaction failed
cancelled Cancelled by user/system
expired Transaction expired

SubscriptionStatus#

Value Description
active Active subscription
past_due Payment overdue
cancelled Cancelled by user
expired Subscription expired

Note: expired is mapped as expired_status in the Prisma enum (@map("expired")).


Source Management#

Source (table: sources)#

Central model for news sources tracked by the platform.

Field Type Constraints Description
id UUID PK, auto-generated Source identifier
slug String Unique URL-friendly identifier
name String Required Display name
kind SourceKind Required Type of news source
status SourceStatus Default: active Current status
canonicalDomain String? -- Canonical domain (e.g., graphic.com.gh)
homeUrl String? -- Homepage URL
countryCode VarChar(2) Default: "GH" ISO 3166-1 alpha-2 country code
region String? -- Sub-national region
languages String[] Default: ["en"] Languages published in
notes String? -- Internal editorial notes
ownershipName String? -- Owner/parent company name
ownershipType OwnershipType Default: unknown Ownership classification
ownershipDetails Json? -- Additional ownership metadata
biasPoliticalScore Float? DoublePrecision Political bias score (-3 to +3)
biasConfidence Float? DoublePrecision Confidence in bias score
biasMethodology String? -- Methodology used for scoring
biasLastAssessedAt DateTime? -- When bias was last assessed
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Relations: feeds, articles, authors, articleAttributions, articlePerspectives, claimEvidence, calendarEvents, userPreferences, nmcComplaints, nmcContentChecks

SourceFeed (table: source_feeds)#

RSS/Atom/API feeds associated with a source.

Field Type Constraints Description
id UUID PK, auto-generated Feed identifier
sourceId UUID FK -> Source Parent source
feedType FeedType Required Feed format
url String Required Feed URL
isActive Boolean Default: true Whether feed is being polled
fetchIntervalSeconds Int Default: 900 Polling interval (seconds)
priority Int Default: 0 Fetch priority
lastFetchedAt DateTime? -- Last successful fetch
lastHttpStatus Int? -- HTTP status of last fetch
etag String? -- HTTP ETag for conditional requests
lastModified String? -- Last-Modified header value
consecutiveFailures Int Default: 0 Failure count for backoff
backoffUntil DateTime? -- Backoff deadline
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Relations: source (Source), feedItems (FeedItem[])


Content Ingestion#

FeedItem (table: feed_items)#

Raw items ingested from source feeds.

Field Type Constraints Description
id UUID PK, auto-generated Item identifier
sourceFeedId UUID FK -> SourceFeed Parent feed
dedupeKey String Unique per feed Deduplication key
guid String? -- Feed item GUID
url String Required Item URL
title String? -- Item title
summary String? -- Item summary
contentHtml String? -- HTML content
publishedAt DateTime? -- Publication date
authors String[] Default: [] Author names
categories String[] Default: [] Feed categories
raw Json? -- Raw feed item data
firstSeenAt DateTime Default: now() First ingestion timestamp
lastSeenAt DateTime Default: now() Last seen in feed
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [sourceFeedId, dedupeKey]

NormalizedFeedItem (table: normalized_feed_items)#

Cleaned and normalized version of a feed item, ready for article creation.

Field Type Constraints Description
id UUID PK, auto-generated Identifier
feedItemId UUID Unique, FK -> FeedItem Source feed item
normalizedTitle String Required Cleaned title
normalizedText String Required Cleaned plain text
normalizedHtml String? -- Cleaned HTML
extractedAt DateTime Default: now() Extraction timestamp
metadata Json Default: {} Extraction metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

SocialMonitor (table: social_monitors)#

Social media monitoring queries.

Field Type Constraints Description
id UUID PK, auto-generated Monitor identifier
platform SocialPlatform Required Social platform
query String Required Search query
isActive Boolean Default: true Whether monitoring is active
fetchIntervalSeconds Int Default: 300 Polling interval
sinceId String? -- Last seen post ID for pagination
lastFetchedAt DateTime? -- Last fetch timestamp
lastHttpStatus Int? -- Last HTTP status
consecutiveFailures Int Default: 0 Failure count
backoffUntil DateTime? -- Backoff deadline
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [platform, query]

SocialPost (table: social_posts)#

Social media posts captured by monitors.

Field Type Constraints Description
id UUID PK, auto-generated Post identifier
monitorId UUID? FK -> SocialMonitor Parent monitor (nullable)
platform SocialPlatform Required Source platform
externalId String Required Platform-specific post ID
authorExternalId String? -- Platform author ID
authorUsername String? -- Author username
url String? -- Post URL
content String? -- Post text content
publishedAt DateTime? -- Publication timestamp
raw Json? -- Raw API response
metadata Json Default: {} Extensible metadata
firstSeenAt DateTime Default: now() First seen timestamp
lastSeenAt DateTime Default: now() Last seen timestamp
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [platform, externalId]


Articles#

Article (table: articles)#

The core content model. Articles are created from normalized feed items or original content.

Field Type Constraints Description
id UUID PK, auto-generated Article identifier
canonicalNormalizedFeedItemId UUID? Unique, FK -> NormalizedFeedItem Source normalized feed item
slug String Unique URL-friendly slug
status ArticleStatus Default: draft Publication status
contentType ArticleContentType Default: news Content type classification
priority ContentPriority Default: normal Content priority level
headline String Required Article headline
summary String? -- Article summary
body String Required Plain text body
bodyHtml String? -- HTML body
languageTag String Default: "en" BCP-47 language tag
sourceId UUID? FK -> Source Source reference
sourceUrl String? -- Original source URL
sourcePublishedAt DateTime? -- Source publication date
featuredMediaAssetId UUID? FK -> MediaAsset Featured image/media
audioVersionUrl String? -- Audio version URL
videoVersionUrl String? -- Video version URL
isOriginal Boolean Default: false Whether content is original
isAIGenerated Boolean Default: false Whether AI-generated
humanReviewed Boolean Default: false Whether human-reviewed
reviewedByUserId UUID? FK -> AuthUser Reviewing user
publishedAt DateTime? -- Publication timestamp
scheduledAt DateTime? -- Scheduled publication time
views Int Default: 0 View count
shares Int Default: 0 Share count
readTimeSeconds Int Default: 0 Estimated read time
storyClusterId UUID? FK -> StoryCluster Story cluster assignment
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Indexes: [status, publishedAt], [sourceId], [languageTag]

Relations: authors, attributions, translations, perspectives, categories, topics, tags, entities, media, claims, factCheck, embeddings, corrections, aiReviews, revisions, summaryVariants, headlineVariants, relatedFrom, relatedTo, userReads, userSaves, audioRenders, videoRenders, youtubeRuns, tiktokRuns, whatsappPosts, clusterMembers, nmcComplaints, nmcContentChecks

Author (table: authors)#

Journalists and content authors.

Field Type Constraints Description
id UUID PK, auto-generated Author identifier
slug String Unique URL-friendly slug
name String Required Display name
bio String? -- Biography
avatarUrl String? -- Profile image URL
sourceId UUID? FK -> Source Affiliated source
twitterHandle String? -- X/Twitter handle
linkedinUrl String? -- LinkedIn profile URL
facebookUrl String? -- Facebook profile URL
websiteUrl String? -- Personal website URL
expertise String[] Default: [] Areas of expertise
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

ArticleAuthor (table: article_authors)#

Join table for articles and authors with contributor role and position.

Field Type Constraints Description
articleId UUID Composite PK, FK -> Article Article reference
authorId UUID Composite PK, FK -> Author Author reference
position Int Default: 0 Byline position
role ContributorRole Default: author Contributor role

ArticleAttribution (table: article_attributions)#

Attribution links for syndicated or referenced content.

Field Type Constraints Description
articleId UUID Composite PK, FK -> Article Article reference
url String Composite PK Attribution URL
sourceId UUID? FK -> Source Source reference
publishedAt DateTime? -- Original publication date
title String? -- Attribution title
languageTag String? -- Language of attributed content
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

ArticleTranslation (table: article_translations)#

Translated versions of articles.

Field Type Constraints Description
id UUID PK, auto-generated Translation identifier
articleId UUID FK -> Article Parent article
languageTag String Required Target language
headline String Required Translated headline
summary String? -- Translated summary
body String Required Translated body
bodyHtml String? -- Translated HTML body
quality TranslationQuality Default: machine Translation quality level
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [articleId, languageTag]

ArticlePerspective (table: article_perspectives)#

Alternative perspectives on a story from different sources.

Field Type Constraints Description
id UUID PK, auto-generated Perspective identifier
articleId UUID FK -> Article Parent article
sourceId UUID? FK -> Source Perspective source
url String Required Source URL for perspective
stance PerspectiveStance Required Stance classification
summary String? -- Perspective summary
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

ArticleRelated (table: article_related_articles)#

Directed relationship between related articles.

Field Type Constraints Description
articleId UUID Composite PK, FK -> Article Source article
relatedArticleId UUID Composite PK, FK -> Article Related article

Taxonomy#

Category (table: categories)#

Top-level content categories with optional hierarchy.

Field Type Constraints Description
id UUID PK, auto-generated Category identifier
slug String Unique URL-friendly slug
name String Required Display name
description String? -- Category description
parentId UUID? FK -> Category (self) Parent category for hierarchy
sortOrder Int Default: 0 Display order
isActive Boolean Default: true Whether visible
icon String? -- Icon identifier
color String? -- Brand color hex
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Self-relation: parent / children (CategoryHierarchy)

Topic (table: topics)#

Fine-grained topics belonging to categories.

Field Type Constraints Description
id UUID PK, auto-generated Topic identifier
slug String Unique URL-friendly slug
name String Required Display name
description String? -- Topic description
categoryId UUID? FK -> Category Parent category
isActive Boolean Default: true Whether visible
isTrending Boolean Default: false Whether currently trending
trendScore Float Default: 0 Trending score
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Tag (table: tags)#

Free-form tags for articles.

Field Type Constraints Description
id UUID PK, auto-generated Tag identifier
slug String Unique URL-friendly slug
label String Required Display label
usageCount Int Default: 0 Number of tagged articles
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Join Tables#

  • ArticleCategory (article_categories): PK [articleId, categoryId]
  • ArticleTopic (article_topics): PK [articleId, topicId]
  • ArticleTag (article_tags): PK [articleId, tagId]

Entities#

Entity (table: entities)#

Named entities (people, organizations, places, etc.) extracted from articles.

Field Type Constraints Description
id UUID PK, auto-generated Entity identifier
entityType EntityType Required Entity classification
name String Required Display name
normalizedName String Required Lowercased normalized name
summary String? -- Entity summary/bio
externalIds Json Default: {} External identifiers (Wikidata, etc.)
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [entityType, normalizedName]

ArticleEntity (table: article_entities)#

Link between articles and entities with confidence and salience scores.

Field Type Constraints Description
articleId UUID Composite PK, FK -> Article Article reference
entityId UUID Composite PK, FK -> Entity Entity reference
confidence Float Default: 1.0 Extraction confidence (0-1)
salience Float Default: 0.0 Entity importance in article
metadata Json Default: {} Extensible metadata

EntityRelationship (table: entity_relationships)#

Directed relationships between entities.

Field Type Constraints Description
id UUID PK, auto-generated Relationship identifier
fromEntityId UUID FK -> Entity Source entity
toEntityId UUID FK -> Entity Target entity
relationshipType String Required Relationship type label
confidence Float Default: 1.0 Confidence score
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

Unique constraint: [fromEntityId, toEntityId, relationshipType]


Media Assets#

MediaAsset (table: media_assets)#

Media files (images, video, audio, documents) associated with articles.

Field Type Constraints Description
id UUID PK, auto-generated Asset identifier
kind MediaKind Required Media type
role MediaRole Default: other Usage role
source MediaSource Default: extracted How the media was obtained
originalUrl String? -- Original source URL
cdnUrl String? -- CDN delivery URL
mimeType String? -- MIME type
width Int? -- Width in pixels
height Int? -- Height in pixels
durationSeconds Float? -- Duration (audio/video)
sizeBytes Int? -- File size
checksum String? -- File checksum
altText String? -- Accessibility alt text
caption String? -- Media caption
credit String? -- Attribution credit
storageProvider MediaStorageProvider? -- Storage provider
storageBucket String? -- Storage bucket name
storageKey String? -- Storage object key
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

ArticleMedia (table: article_media)#

Join table linking articles to media assets with position ordering.

Field Type Constraints Description
articleId UUID Composite PK, FK -> Article Article reference
mediaAssetId UUID Composite PK, FK -> MediaAsset Media asset reference
position Int Default: 0 Display order

Story Clusters#

StoryCluster (table: story_clusters)#

Groups of related articles covering the same news event.

Field Type Constraints Description
id UUID PK, auto-generated Cluster identifier
title String Required Cluster title
summary String? -- Cluster summary
status StoryClusterStatus Default: active Cluster status
method ClusteringMethod Default: hybrid Method used to create cluster
score Float? -- Cluster quality score
canonicalArticleId UUID? -- Representative article
lastActivityAt DateTime Default: now() Last article added
archivedAt DateTime? -- When cluster was archived
mergedIntoId UUID? FK -> StoryCluster (self) Target cluster if merged
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Self-relation: mergedInto / mergedFrom (ClusterMerge)

StoryClusterMember (table: story_cluster_members)#

Membership of articles in story clusters.

Field Type Constraints Description
storyClusterId UUID Composite PK, FK -> StoryCluster Cluster reference
articleId UUID Composite PK, FK -> Article Article reference
isCanonical Boolean Default: false Whether canonical member
addedAt DateTime Default: now() When added to cluster

Fact-Checking#

Claim (table: claims)#

Claims extracted from articles for fact-checking.

Field Type Constraints Description
id UUID PK, auto-generated Claim identifier
text String Required Original claim text
normalizedText String Unique Lowercase normalized text
claimType ClaimType Default: factual Claim classification
speaker String? -- Person who made the claim
context String? -- Contextual information
checkworthiness Float Default: 0 Checkworthiness score (0-1)
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

ArticleClaim (table: article_claims)#

Join table linking articles to claims.

Field Type Constraints Description
articleId UUID Composite PK, FK -> Article Article reference
claimId UUID Composite PK, FK -> Claim Claim reference
position Int Default: 0 Position in article
confidence Float Default: 1.0 Extraction confidence

ArticleFactCheck (table: article_fact_checks)#

Overall fact-check status for an article.

Field Type Constraints Description
articleId UUID PK, FK -> Article Article reference
overallStatus FactCheckOverallStatus Required Aggregate fact-check status
lastCheckedAt DateTime? -- Last check timestamp
checkedBy String? -- Checker identifier
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

ClaimVerification (table: claim_verifications)#

Individual verification attempts for a claim.

Field Type Constraints Description
id UUID PK, auto-generated Verification identifier
claimId UUID FK -> Claim Verified claim
status FactCheckOverallStatus Required Verification result
confidence Float Default: 0 Confidence in result
summary String? -- Verification summary
notes String[] Default: [] Reviewer notes
verifiedBy String Required Verifier identifier
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

ClaimEvidence (table: claim_evidence)#

Evidence items supporting or refuting a claim.

Field Type Constraints Description
id UUID PK, auto-generated Evidence identifier
claimVerificationId UUID FK -> ClaimVerification Parent verification
url String Required Evidence source URL
sourceId UUID? FK -> Source Known source reference
quote String? -- Relevant quote
title String? -- Evidence title
summary String? -- Evidence summary
stance EvidenceStance Default: neutral Evidence stance
relevance Float Default: 0 Relevance score (0-1)
credibility Float Default: 0 Source credibility score
retrievedAt DateTime Default: now() Retrieval timestamp
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

Embeddings#

ArticleEmbedding (table: article_embeddings)#

Vector embeddings for articles, used for semantic similarity and clustering.

Field Type Constraints Description
articleId UUID Composite PK, FK -> Article Article reference
provider EmbeddingProvider Composite PK Embedding provider
model String Composite PK Model name/version
dimensions Int Required Vector dimensions
embedding vector pgvector type (Unsupported("vector")) Embedding vector
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

Composite PK: [articleId, provider, model]


Authentication#

AuthUser (table: auth_users)#

User accounts. Email uses citext for case-insensitive matching.

Field Type Constraints Description
id UUID PK, auto-generated User identifier
email String Unique (citext) Email address
passwordHash String? -- bcrypt password hash
name String? -- Display name
avatarUrl String? -- Profile image URL
isActive Boolean Default: true Account active flag
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Registration timestamp
updatedAt DateTime Auto-updated Last update timestamp

Relations: identities, passwordReset, reviewedArticles, articleReads, savedArticles, topicFollows, sourcePreferences, authorFollows, pushTokens, notifications

AuthIdentity (table: auth_identities)#

OAuth/social login identities linked to a user account.

Field Type Constraints Description
id UUID PK, auto-generated Identity identifier
userId UUID FK -> AuthUser Parent user
provider String Required OAuth provider (google, etc.)
providerAccountId String Required Provider-specific user ID
email String? -- Email from provider
accessToken String? -- OAuth access token
refreshToken String? -- OAuth refresh token
expiresAt DateTime? -- Token expiry
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [provider, providerAccountId]

PasswordReset (table: password_resets)#

Password reset tokens.

Field Type Constraints Description
id UUID PK, auto-generated Reset identifier
userId UUID FK -> AuthUser Target user
token String Unique Reset token
expiresAt DateTime Required Token expiry
usedAt DateTime? -- When token was used
createdAt DateTime Default: now() Creation timestamp

User Engagement#

UserArticleRead (table: user_article_reads)#

Tracks user reading activity.

Field Type Constraints Description
userId UUID Composite PK, FK -> AuthUser User reference
articleId UUID Composite PK, FK -> Article Article reference
firstReadAt DateTime Default: now() First read timestamp
lastReadAt DateTime Default: now() Most recent read timestamp
readCount Int Default: 1 Number of reads
readPercent Float Default: 0 Reading completion (0-100)
timeOnPage Int Default: 0 Time on page (seconds)
metadata Json Default: {} Extensible metadata

UserSavedArticle (table: user_saved_articles)#

User bookmarked/saved articles.

Field Type Constraints Description
userId UUID Composite PK, FK -> AuthUser User reference
articleId UUID Composite PK, FK -> Article Article reference
savedAt DateTime Default: now() When saved

UserTopicFollow (table: user_topic_follows)#

Topics followed by a user.

Field Type Constraints Description
userId UUID Composite PK, FK -> AuthUser User reference
topicId UUID Composite PK, FK -> Topic Topic reference
followedAt DateTime Default: now() When followed

UserSourcePreference (table: user_source_preferences)#

User preferences for specific sources.

Field Type Constraints Description
userId UUID Composite PK, FK -> AuthUser User reference
sourceId UUID Composite PK, FK -> Source Source reference
preference SourcePreferenceType Required Prefer or avoid
createdAt DateTime Default: now() Creation timestamp

UserAuthorFollow (table: user_author_follows)#

Authors followed by a user.

Field Type Constraints Description
userId UUID Composite PK, FK -> AuthUser User reference
authorId UUID Composite PK, FK -> Author Author reference
followedAt DateTime Default: now() When followed

Voice and Audio#

VoiceProfile (table: voice_profiles)#

TTS voice configurations.

Field Type Constraints Description
id UUID PK, auto-generated Profile identifier
slug String Unique URL-friendly slug
provider VoiceProvider Default: elevenlabs TTS provider
providerVoiceId String Required Provider voice identifier
displayName String Required Display name
description String? -- Voice description
languageTags String[] Default: ["en"] Supported languages
defaultSettings Json Default: {} Provider-specific settings
defaultFormat String Default: "mp3_44100_128" Audio output format
isActive Boolean Default: true Whether available
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [provider, providerVoiceId]

ArticleAudioRender (table: article_audio_renders)#

Audio renderings of articles using TTS.

Field Type Constraints Description
id UUID PK, auto-generated Render identifier
articleId UUID FK -> Article Source article
voiceProfileId UUID FK -> VoiceProfile Voice used
languageTag String Default: "en" Render language
status ScheduleRunStatus Default: claimed Render status
audioUrl String? -- Output audio URL
durationSeconds Float? -- Audio duration
sizeBytes Int? -- File size
storageProvider MediaStorageProvider? -- Where audio is stored
storageBucket String? -- Storage bucket
storageKey String? -- Storage key
errorMessage String? -- Error if failed
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

AvatarProfile (table: avatar_profiles)#

AI avatar configurations for video rendering.

Field Type Constraints Description
id UUID PK, auto-generated Profile identifier
slug String Unique URL-friendly slug
provider String Required Avatar provider
providerAvatarId String Required Provider avatar identifier
displayName String Required Display name
description String? -- Avatar description
isActive Boolean Default: true Whether available
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

ArticleVideoRender (table: article_video_renders)#

Video renderings of articles using AI avatars.

Field Type Constraints Description
id UUID PK, auto-generated Render identifier
articleId UUID FK -> Article Source article
avatarProfileId UUID FK -> AvatarProfile Avatar used
languageTag String Default: "en" Render language
status ScheduleRunStatus Default: claimed Render status
videoUrl String? -- Output video URL
thumbnailUrl String? -- Video thumbnail
durationSeconds Float? -- Video duration
sizeBytes Int? -- File size
storageProvider MediaStorageProvider? -- Where video is stored
storageBucket String? -- Storage bucket
storageKey String? -- Storage key
errorMessage String? -- Error if failed
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Push Notifications#

UserPushToken (table: user_push_tokens)#

Device push notification tokens.

Field Type Constraints Description
id UUID PK, auto-generated Token identifier
userId UUID FK -> AuthUser Token owner
provider String Default: "fcm" Push provider
token String Required Device token
platform String? -- Device platform (ios, android)
deviceId String? -- Device identifier
isActive Boolean Default: true Whether token is valid
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [provider, token]

PushNotificationOutbox (table: push_notification_outbox)#

Outbox pattern for reliable notification delivery.

Field Type Constraints Description
id UUID PK, auto-generated Notification identifier
provider String Default: "fcm" Push provider
userId UUID FK -> AuthUser Target user
pushTokenId UUID FK -> UserPushToken Target token
kind String Required Notification type
dedupeKey String Required Deduplication key
status String Default: "pending" Delivery status
notBefore DateTime? -- Earliest delivery time
expiresAt DateTime? -- Notification expiry
attempts Int Default: 0 Delivery attempts
lastError String? -- Last error message
payload Json Required Notification payload
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [pushTokenId, kind, dedupeKey]

PushNotificationDelivery (table: push_notification_deliveries)#

Delivery attempt records.

Field Type Constraints Description
id UUID PK, auto-generated Delivery identifier
outboxId UUID FK -> PushNotificationOutbox Parent notification
userId UUID -- Target user (denormalized)
pushTokenId UUID FK -> UserPushToken Target token
kind String Required Notification type
status String Required Delivery result
error String? -- Error message if failed
sentAt DateTime Default: now() Send timestamp
metadata Json Default: {} Extensible metadata

Content Scheduling#

YouTubeShortsScheduleRun (table: youtube_shorts_schedule_runs)#

YouTube Shorts publishing schedule.

Field Type Constraints Description
id UUID PK, auto-generated Run identifier
dateKey String Required Date key (e.g., 2026-02-14)
slotMinutes Int Required Minute of day (0-1439)
timeZone String Default: "Africa/Accra" Time zone
status ScheduleRunStatus Default: claimed Run status
attempts Int Default: 0 Attempt count
claimedAt DateTime? -- When claimed by worker
startedAt DateTime? -- When execution started
finishedAt DateTime? -- When execution finished
articleId UUID? FK -> Article Article published
articleSlug String? -- Article slug (denormalized)
youtubeVideoId String? -- YouTube video ID
youtubeUrl String? -- YouTube URL
errorMessage String? -- Error if failed
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [dateKey, slotMinutes]

TikTokScheduleRun (table: tiktok_schedule_runs)#

TikTok publishing schedule. Same structure as YouTube Shorts with TikTok-specific fields.

Field Type Constraints Description
id UUID PK, auto-generated Run identifier
dateKey String Required Date key
slotMinutes Int Required Minute of day
timeZone String Default: "Africa/Accra" Time zone
status ScheduleRunStatus Default: claimed Run status
attempts Int Default: 0 Attempt count
claimedAt DateTime? -- When claimed
startedAt DateTime? -- When started
finishedAt DateTime? -- When finished
articleId UUID? FK -> Article Article published
articleSlug String? -- Article slug
tiktokPublishId String? -- TikTok publish ID
tiktokVideoId String? -- TikTok video ID
tiktokUrl String? -- TikTok URL
errorMessage String? -- Error if failed
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [dateKey, slotMinutes]


WhatsApp#

WhatsAppChannelSubscriber (table: whatsapp_channel_subscribers)#

WhatsApp channel subscribers.

Field Type Constraints Description
id UUID PK, auto-generated Subscriber identifier
phoneE164 String Unique Phone in E.164 format
status WhatsAppSubscriberStatus Default: active Subscription status
subscribedAt DateTime Default: now() When subscribed
unsubscribedAt DateTime? -- When unsubscribed
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

WhatsAppChannelPost (table: whatsapp_channel_posts)#

Posts distributed via WhatsApp channel.

Field Type Constraints Description
id UUID PK, auto-generated Post identifier
articleId UUID Unique, FK -> Article Source article
articleSlug String Required Article slug (denormalized)
priority ContentPriority Default: normal Distribution priority
status WhatsAppPostStatus Default: running Distribution status
messageText String? -- Sent message text
recipients Int Default: 0 Total recipients
succeeded Int Default: 0 Successful deliveries
failed Int Default: 0 Failed deliveries
errorMessage String? -- Error if failed
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

AI Processing#

ArticleCorrection (table: article_corrections)#

Corrections, clarifications, and retractions for articles.

Field Type Constraints Description
id UUID PK, auto-generated Correction identifier
articleId UUID FK -> Article Corrected article
kind CorrectionKind Required Type of correction
severity CorrectionSeverity Required Severity level
summary String Required Correction summary
publicNote String? -- Public-facing note
publishedAt DateTime? -- When correction was published
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

ArticleAIReview (table: article_ai_reviews)#

AI quality reviews of articles.

Field Type Constraints Description
articleId UUID Composite PK, FK -> Article Reviewed article
kind AIReviewKind Composite PK Review type
reviewedAt DateTime Default: now() Review timestamp
inputTextHash String Required Hash of input for cache
approved Boolean Required Whether approved
score Float? -- Quality score
report Json Default: {} Full review report
triggeredByUserId UUID? -- User who triggered review
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

ArticleRevision (table: article_revisions)#

Version history for article edits.

Field Type Constraints Description
id UUID PK, auto-generated Revision identifier
articleId UUID FK -> Article Article reference
version Int Required Version number
headline String Required Headline at this version
summary String? -- Summary at this version
body String Required Body at this version
bodyHtml String? -- HTML body at this version
changedBy String? -- Who made the change
changeNote String? -- Change description
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

Unique constraint: [articleId, version]

ArticleSummaryVariant (table: article_summary_variants)#

AI-generated summary variations.

Field Type Constraints Description
id UUID PK, auto-generated Variant identifier
articleId UUID FK -> Article Article reference
variant String Required Variant label (e.g., "short")
summary String Required Summary text
score Float? -- Quality score
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

Unique constraint: [articleId, variant]

ArticleHeadlineVariant (table: article_headline_variants)#

AI-generated headline variations.

Field Type Constraints Description
id UUID PK, auto-generated Variant identifier
articleId UUID FK -> Article Article reference
variant String Required Variant label
headline String Required Headline text
score Float? -- Quality score
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp

Unique constraint: [articleId, variant]


Calendar Events#

CalendarEvent (table: calendar_events)#

Editorial calendar events.

Field Type Constraints Description
id UUID PK, auto-generated Event identifier
sourceId UUID? FK -> Source Associated source
title String Required Event title
description String? -- Event description
location String? -- Event location
startTime DateTime Required Start time
endTime DateTime? -- End time
allDay Boolean Default: false Whether all-day event
recurrence String? -- Recurrence rule (iCal format)
status String Default: "scheduled" Event status
externalId String? -- External calendar ID
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

NMC Compliance#

NmcComplaint (table: nmc_complaints)#

Complaints filed under National Media Commission guidelines.

Field Type Constraints Description
id UUID PK, auto-generated Complaint identifier
articleId UUID? FK -> Article Related article
sourceId UUID? FK -> Source Related source
complainantType NmcComplainantType Required Who filed the complaint
category NmcViolationCategory Required Violation category
stage NmcComplaintStage Default: filed Current stage
severity NmcComplaintSeverity Required Severity level
summary String Required Complaint summary
details String Required Full details
evidenceUrls String[] Default: [] Supporting evidence URLs
remedySought NmcRemedy[] Required Remedies requested
remedyOrdered NmcRemedy[] Default: [] Remedies ordered by NMC
rulingNotes String? -- Ruling notes
complianceDeadline DateTime? -- Compliance deadline
isCompliant Boolean? -- Whether compliant
filedAt DateTime Default: now() Filing timestamp
lastUpdatedAt DateTime Auto-updated Last update
closedAt DateTime? -- Closure timestamp

Indexes: [sourceId], [articleId], [stage], [category], [severity], [filedAt]

NmcComplaintTransition (table: nmc_complaint_transitions)#

Stage transition audit log for complaints.

Field Type Constraints Description
id UUID PK, auto-generated Transition identifier
complaintId UUID FK -> NmcComplaint Parent complaint
fromStage NmcComplaintStage Required Previous stage
toStage NmcComplaintStage Required New stage
notes String Required Transition notes
transitionedAt DateTime Default: now() Transition timestamp

Index: [complaintId]

NmcContentCheck (table: nmc_content_checks)#

Automated NMC compliance checks.

Field Type Constraints Description
id UUID PK, auto-generated Check identifier
sourceId UUID? FK -> Source Checked source
articleId UUID? FK -> Article Checked article
overallScore Int Required Compliance score (0-100)
compliant Boolean Required Whether compliant
violationCount Int Default: 0 Number of violations
principleScores Json Default: {} Per-principle scores
methodology String Default: "nmc_compliance_v1" Methodology version
checkedAt DateTime Default: now() Check timestamp

Indexes: [sourceId], [articleId], [checkedAt], [sourceId, checkedAt]

NmcViolationFlag (table: nmc_violation_flags)#

Individual violation flags from content checks.

Field Type Constraints Description
id UUID PK, auto-generated Flag identifier
contentCheckId UUID FK -> NmcContentCheck Parent check
category NmcViolationCategory Required Violation category
severity NmcComplaintSeverity Required Severity level
description String Required Violation description
textExcerpt String? -- Offending text excerpt
recommendedRemedy NmcRemedy Required Suggested remedy
createdAt DateTime Default: now() Creation timestamp

Indexes: [contentCheckId], [category]


Data Protection (DPA)#

DpaConsent (table: dpa_consents)#

User consent records under Ghana's Data Protection Act 2012 (Act 843).

Field Type Constraints Description
id UUID PK, auto-generated Consent identifier
userId UUID Required User reference
purpose DpaProcessingPurpose Required Processing purpose
lawfulBasis DpaLawfulBasis Required Legal basis
consentGiven Boolean Required Whether consent was given
consentText String Default: "" Consent text shown to user
consentVersion String Default: "1.0" Consent version
grantedAt DateTime? -- When granted
withdrawnAt DateTime? -- When withdrawn
expiresAt DateTime? -- Consent expiry
ipAddress String? -- IP at time of consent
userAgent String? -- User agent at consent
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [userId, purpose] Index: [userId]

DpaSubjectRequest (table: dpa_subject_requests)#

Data subject access/modification requests.

Field Type Constraints Description
id UUID PK, auto-generated Request identifier
userId UUID Required Requesting user
requestType DpaRequestType Required Type of request
status DpaRequestStatus Default: submitted Processing status
description String Required Request description
responseNotes String? -- Response notes
refusalReason String? -- Reason if refused
deadlineAt DateTime Required Processing deadline
submittedAt DateTime Default: now() Submission timestamp
acknowledgedAt DateTime? -- Acknowledgment timestamp
completedAt DateTime? -- Completion timestamp
exportFileUrl String? -- URL for data export file
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Indexes: [userId], [status], [deadlineAt], [userId, status]


Election Coverage#

Election (table: elections)#

Elections tracked by the platform.

Field Type Constraints Description
id UUID PK, auto-generated Election identifier
name String Required Election name
electionType ElectionType Required Type of election
electionDate DateTime Required Election date
isActive Boolean Default: true Whether currently active
registeredParties Json Default: [] Registered parties
presidentialCandidates Json Default: [] Presidential candidates
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Indexes: [isActive], [electionDate]

ElectionCoverageViolation (table: election_coverage_violations)#

Violations of election coverage rules.

Field Type Constraints Description
id UUID PK, auto-generated Violation identifier
electionId UUID FK -> Election Related election
violationType ElectionCoverageViolationType Required Type of violation
severity CoverageComplianceLevel Required Severity level
description String Required Violation description
legalBasis String Required Legal basis for violation
affectedParty String? -- Affected political party
articleId UUID? -- Related article
sourceId UUID? -- Related source
recommendation String Required Recommended action
metadata Json Default: {} Extensible metadata
recordedAt DateTime Default: now() When recorded
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Indexes: [electionId], [violationType], [severity], [sourceId], [articleId], [recordedAt], [electionId, violationType], [electionId, sourceId]


Payment Processing#

PaymentTransaction (table: payment_transactions)#

Payment transactions (MTN MoMo, Vodafone Cash, AirtelTigo Money, Stripe).

Field Type Constraints Description
id UUID PK, auto-generated Transaction identifier
userId UUID Required Paying user
transactionType PaymentTransactionType Required Transaction type
provider String Default: "mtn_momo" Payment provider
referenceId String Unique Internal reference ID
externalId String Required Provider transaction ID
financialTransactionId String? -- Financial transaction ID
amount String Required Amount (string for precision)
currency VarChar(3) Default: "GHS" ISO 4217 currency code
phoneNumber String Required Mobile money phone number
status PaymentTransactionStatus Default: initiated Transaction status
providerStatus String? -- Provider-specific status
failureCode String? -- Failure code
failureMessage String? -- Failure message
payerMessage String Required Message to payer
payeeNote String Required Note for payee
callbackReceived Boolean Default: false Whether callback was received
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Indexes: [userId], [externalId], [status], [userId, status], [createdAt]

PaymentSubscription (table: payment_subscriptions)#

Recurring payment subscriptions.

Field Type Constraints Description
id UUID PK, auto-generated Subscription identifier
userId UUID Required Subscriber
planId String Required Plan identifier
provider String Default: "mtn_momo" Payment provider
phoneNumber String Required Payment phone number
status SubscriptionStatus Default: active Subscription status
preApprovalId String? -- MoMo pre-approval ID
currentPeriodStart DateTime Required Current billing period start
currentPeriodEnd DateTime Required Current billing period end
lastPaymentAt DateTime? -- Last successful payment
lastPaymentTransactionId UUID? -- Last payment transaction
nextBillingAt DateTime Required Next billing date
failedAttempts Int Default: 0 Consecutive failed payments
cancelledAt DateTime? -- Cancellation timestamp
metadata Json Default: {} Extensible metadata
createdAt DateTime Default: now() Creation timestamp
updatedAt DateTime Auto-updated Last update timestamp

Unique constraint: [userId, planId] Indexes: [userId], [status], [nextBillingAt]


Entity-Relationship Diagram#

text
┌───────────────────────────────────────────────────────────────────────────────┐
│                            SOURCE MANAGEMENT                                  │
│                                                                               │
│  ┌──────────┐  1:N  ┌────────────┐  1:N  ┌──────────┐  1:1  ┌────────────┐  │
│  │  Source   │──────>│ SourceFeed │──────>│ FeedItem │──────>│Normalized  │  │
│  │          │       │            │       │          │       │FeedItem    │  │
│  └────┬─────┘       └────────────┘       └──────────┘       └─────┬──────┘  │
│       │                                                           │          │
│       │ 1:N                                                 1:0..1│          │
│       v                                                           v          │
│  ┌──────────┐                                              ┌──────────┐      │
│  │  Author  │                                              │ Article  │      │
│  └────┬─────┘                                              └────┬─────┘      │
│       │                                                         │            │
└───────┼─────────────────────────────────────────────────────────┼────────────┘
        │                                                         │
        │ N:M (ArticleAuthor)                                     │
        └────────────────────────>────────────────────────────────┘
                                                                  │
┌─────────────────────────────────────────────────────────────────┼────────────┐
│                              ARTICLE                             │            │
│                                                                  │            │
│    ┌─────────────────────┬──────────────┬──────────────┐         │            │
│    │                     │              │              │         │            │
│    v N:M                 v N:M          v N:M          v N:M    │            │
│  ┌──────────┐     ┌──────────┐   ┌──────────┐   ┌──────────┐   │            │
│  │ Category │     │  Topic   │   │   Tag    │   │  Entity  │   │            │
│  └──────────┘     └──────────┘   └──────────┘   └────┬─────┘   │            │
│                                                       │         │            │
│                                                  N:N  │         │            │
│                                        ┌──────────────┘         │            │
│                                        v                        │            │
│                                  ┌──────────────┐               │            │
│                                  │   Entity     │               │            │
│                                  │ Relationship │               │            │
│                                  └──────────────┘               │            │
│                                                                 │            │
│    ┌───────────────┬────────────────┬───────────────┐           │            │
│    │               │                │               │           │            │
│    v 1:N           v 1:N            v 1:N           v 0..1      │            │
│  ┌──────────┐  ┌──────────────┐  ┌──────────────┐  ┌────────┐  │            │
│  │MediaAsset│  │  Translation │  │  Perspective │  │FactChk │  │            │
│  └──────────┘  └──────────────┘  └──────────────┘  └────────┘  │            │
│                                                                 │            │
│    ┌──────────────┬──────────────┬──────────────┐               │            │
│    │              │              │              │               │            │
│    v 1:N          v 1:N          v 1:N          v N:M           │            │
│  ┌──────────┐  ┌──────────┐  ┌───────────┐  ┌──────────────┐   │            │
│  │Correction│  │AIReview  │  │ Revision  │  │StoryCluster  │   │            │
│  └──────────┘  └──────────┘  └───────────┘  │  Member      │   │            │
│                                              └──────┬───────┘   │            │
└─────────────────────────────────────────────────────┼───────────┘            │
                                                      │                        │
┌─────────────────────────────────────────────────────┼────────────────────────┐
│                        STORY CLUSTERS               │                        │
│                                                      │                        │
│  ┌──────────────┐  1:N  ┌──────────────────┐  N:1   │                        │
│  │ StoryCluster │──────>│StoryClusterMember│<───────┘                        │
│  │              │       └──────────────────┘                                 │
│  │  (self-ref:  │                                                            │
│  │  mergedInto) │                                                            │
│  └──────────────┘                                                            │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│                           FACT-CHECKING                                       │
│                                                                              │
│  ┌──────────┐  N:M     ┌─────────────────┐                                  │
│  │  Article │<────────>│  ArticleClaim   │                                  │
│  └──────────┘          └────────┬────────┘                                  │
│                                 │                                            │
│                                 v N:1                                        │
│                          ┌──────────┐  1:N  ┌───────────────────┐            │
│                          │  Claim   │──────>│ClaimVerification  │            │
│                          └──────────┘       └────────┬──────────┘            │
│                                                      │ 1:N                   │
│                                                      v                       │
│                                               ┌──────────────┐               │
│                                               │ClaimEvidence │               │
│                                               └──────────────┘               │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│                          USER ENGAGEMENT                                     │
│                                                                              │
│  ┌──────────┐                                                                │
│  │ AuthUser │                                                                │
│  └────┬─────┘                                                                │
│       │                                                                      │
│       ├── 1:N ──> AuthIdentity                                               │
│       ├── 1:N ──> PasswordReset                                              │
│       ├── N:M ──> UserArticleRead   (Article)                                │
│       ├── N:M ──> UserSavedArticle  (Article)                                │
│       ├── N:M ──> UserTopicFollow   (Topic)                                  │
│       ├── N:M ──> UserSourcePreference (Source)                              │
│       ├── N:M ──> UserAuthorFollow  (Author)                                 │
│       ├── 1:N ──> UserPushToken                                              │
│       └── 1:N ──> PushNotificationOutbox ──1:N──> PushNotificationDelivery   │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│                          VOICE / VIDEO                                       │
│                                                                              │
│  Article ──1:N──> ArticleAudioRender ──N:1──> VoiceProfile                   │
│  Article ──1:N──> ArticleVideoRender ──N:1──> AvatarProfile                  │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│                        DISTRIBUTION                                          │
│                                                                              │
│  Article ──1:N──> YouTubeShortsScheduleRun                                   │
│  Article ──1:N──> TikTokScheduleRun                                          │
│  Article ──1:1──> WhatsAppChannelPost                                        │
│                                                                              │
│  SocialMonitor ──1:N──> SocialPost                                           │
│  WhatsAppChannelSubscriber (standalone)                                       │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│                         COMPLIANCE                                           │
│                                                                              │
│  NmcComplaint ──1:N──> NmcComplaintTransition                                │
│  NmcContentCheck ──1:N──> NmcViolationFlag                                   │
│  DpaConsent (per user per purpose)                                           │
│  DpaSubjectRequest (per user)                                                │
│  Election ──1:N──> ElectionCoverageViolation                                 │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│                         PAYMENTS                                             │
│                                                                              │
│  PaymentTransaction (per user, per provider)                                 │
│  PaymentSubscription (per user per plan)                                     │
└──────────────────────────────────────────────────────────────────────────────┘

Indexes#

Article Indexes#

Table Columns Purpose
articles [status, published_at] Efficient feed queries by status + date
articles [source_id] Filter articles by source
articles [language_tag] Filter articles by language

NMC Compliance Indexes#

Table Columns Purpose
nmc_complaints [source_id] Complaints by source
nmc_complaints [article_id] Complaints by article
nmc_complaints [stage] Filter by complaint stage
nmc_complaints [category] Filter by violation category
nmc_complaints [severity] Filter by severity
nmc_complaints [filed_at] Chronological listing
nmc_complaint_transitions [complaint_id] Transitions for a complaint
nmc_content_checks [source_id] Checks by source
nmc_content_checks [article_id] Checks by article
nmc_content_checks [checked_at] Chronological listing
nmc_content_checks [source_id, checked_at] Source compliance history
nmc_violation_flags [content_check_id] Flags for a check
nmc_violation_flags [category] Filter by violation category

DPA Compliance Indexes#

Table Columns Purpose
dpa_consents [user_id] Consents by user
dpa_subject_requests [user_id] Requests by user
dpa_subject_requests [status] Filter by status
dpa_subject_requests [deadline_at] Approaching deadlines
dpa_subject_requests [user_id, status] User requests by status

Election Coverage Indexes#

Table Columns Purpose
elections [is_active] Active elections
elections [election_date] Elections by date
election_coverage_violations [election_id] Violations per election
election_coverage_violations [violation_type] Filter by type
election_coverage_violations [severity] Filter by severity
election_coverage_violations [source_id] Violations by source
election_coverage_violations [article_id] Violations by article
election_coverage_violations [recorded_at] Chronological listing
election_coverage_violations [election_id, violation_type] Election + type compound
election_coverage_violations [election_id, source_id] Election + source compound

Payment Indexes#

Table Columns Purpose
payment_transactions [user_id] Transactions by user
payment_transactions [external_id] Lookup by provider ID
payment_transactions [status] Filter by status
payment_transactions [user_id, status] User transactions by status
payment_transactions [created_at] Chronological listing
payment_subscriptions [user_id] Subscriptions by user
payment_subscriptions [status] Filter by status
payment_subscriptions [next_billing_at] Billing schedule queries

Unique Constraints (Also Serve as Indexes)#

Table Columns
sources [slug]
articles [slug]
articles [canonical_normalized_feed_item_id]
authors [slug]
categories [slug]
topics [slug]
tags [slug]
entities [entity_type, normalized_name]
entity_relationships [from_entity_id, to_entity_id, type]
claims [normalized_text]
article_translations [article_id, language_tag]
article_revisions [article_id, version]
article_summary_variants [article_id, variant]
article_headline_variants [article_id, variant]
voice_profiles [slug]
voice_profiles [provider, provider_voice_id]
avatar_profiles [slug]
feed_items [source_feed_id, dedupe_key]
social_monitors [platform, query]
social_posts [platform, external_id]
auth_users [email] (citext)
auth_identities [provider, provider_account_id]
password_resets [token]
user_push_tokens [provider, token]
push_notification_outbox [push_token_id, kind, dedupe_key]
youtube_shorts_schedule_runs [date_key, slot_minutes]
tiktok_schedule_runs [date_key, slot_minutes]
whatsapp_channel_subscribers [phone_e164]
whatsapp_channel_posts [article_id]
payment_transactions [reference_id]
payment_subscriptions [user_id, plan_id]
dpa_consents [user_id, purpose]

Migration Workflow#

Setup#

bash
# Install dependencies
pnpm install

# Set the database URL
export VERITAS_DATABASE_URL="postgresql://user:password@localhost:5432/veritas"

Commands#

bash
# Generate Prisma client from schema
pnpm exec prisma generate --schema=libs/veritas/database/prisma/schema.prisma

# Create a new migration
pnpm exec prisma migrate dev --name <migration_name> --schema=libs/veritas/database/prisma/schema.prisma

# Apply pending migrations (production)
pnpm exec prisma migrate deploy --schema=libs/veritas/database/prisma/schema.prisma

# Reset database (destructive - development only)
pnpm exec prisma migrate reset --schema=libs/veritas/database/prisma/schema.prisma

# View current migration status
pnpm exec prisma migrate status --schema=libs/veritas/database/prisma/schema.prisma

# Open Prisma Studio (visual database editor)
pnpm exec prisma studio --schema=libs/veritas/database/prisma/schema.prisma

# Seed the database
pnpm exec prisma db seed --schema=libs/veritas/database/prisma/schema.prisma

Seed Files#

  • libs/veritas/database/prisma/seed.ts -- Production seed data (categories, topics, sources)
  • libs/veritas/database/prisma/seed-test.ts -- Test seed data with sample articles and claims

PostgreSQL Extensions#

The schema requires two PostgreSQL extensions:

  • pgvector (vector) -- Enables vector similarity search for article embeddings
  • citext -- Enables case-insensitive text for email addresses

These are typically installed via:

sql
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS citext;

Or automatically when using the Docker Compose development environment (see docker/docker-compose.dev.yml).