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 |
| Value |
Description |
image |
Photograph or image |
video |
Video |
audio |
Audio clip |
embed |
Embedded content |
document |
Document (PDF, etc.) |
infographic |
Infographic |
| 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 |
| Value |
Description |
extracted |
Extracted from source |
uploaded |
Manually uploaded |
generated |
AI-generated |
licensed |
Licensed from provider |
| 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#
| 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 |
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 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).