Data Model

14 tables for Editorial. Core (Article + Lang), Taxonomy (Category + Topic with their Lang companions + ArticleTopic join), People (Author + Lang), Media (ArticleImage), Cross-module (ArticleRelated for manual links, EcosystemEntityLinks shared), Operational (Analytics, Seo). Every bilingual field uses an *Lang companion table.

Table 1 · EditorialArticle (core)

Identity

Id (uuid PK), Slug (varchar(120) unique). CanonicalArticleId (FK → self, nullable) for translation-pair linking.

Type + Topic

Type (enum: news|analysis|commentary|interview|profile|guide, required). Topic (varchar(40), required, single value). Sector + Country (varchar, nullable, denormalised from mentions for fast filtering).

Foreign keys

CategoryId (FK → EditorialCategory, required), AuthorId (FK → EditorialAuthor, required), FeaturedImageId (FK → EditorialArticleImage, nullable).

Mentioned entity arrays

MentionedFounderIds (uuid[]), MentionedStartupIds, MentionedEventIds, MentionedOpportunityIds. All optional. Feed the cross-module reverse-lookups + connected-signals rail.

Editorial flags

IsActive (bool, soft-delete gate), IsFeatured (bool, drives spotlight), IsEditorPick (bool, drives "editor-picks" sort), IsMostRead (bool, manual override for editorial choices), IsSponsored (bool, drives sponsor disclosure), IsPractical (bool, suppresses Files cross-module rail), IsTranslated (bool, true when this is a translation of another article).

Lifecycle

Status (enum: draft|in-review|scheduled|published|archived, required). PublishDate, UpdatedDate, ScheduledFor (timestamptz). ViewCount (int, denormalised from EditorialArticleAnalytics).

Reading meta

ReadingTimeMinutes (int, computed from body word count at save time), WordCount (int), OriginalLanguage (varchar(2), 'en' or 'ar').

Audit

CreatedAt, UpdatedAt, CreatedByUserId, LastEditedByUserId.

Tables 2-14

• EditorialArticleLang (composite PK ArticleId + Lang): Title, Subtitle?, Dek, Excerpt, BodyHtml (sanitised), MetaTitle?, MetaDescription?.
• EditorialCategory (Id, Slug, IconKey, DisplayOrder, IsActive). EditorialCategoryLang companion: Name + Description.
• EditorialTopic (Id, Slug, RelatedCategoryId?). EditorialTopicLang companion: Name + Description.
• EditorialArticleTopic (join: ArticleId ↔ TopicId, DisplayOrder).
• EditorialAuthor (Id, Slug, AvatarUrl, LinkedInUrl, TwitterUrl, PersonalSiteUrl, Email, IsActive). EditorialAuthorLang companion: DisplayName + Role + Bio.
• EditorialArticleImage (Id, ArticleId, Url, Alt (bilingual), Caption (bilingual), Credit, AspectRatio, DisplayOrder). DisplayOrder=0 is the featured cover.
• EditorialArticleRelated (manual related: ArticleId ↔ RelatedArticleId, DisplayOrder). Editor-curated.
• EditorialArticleAnalytics (ArticleId PK, ViewCount, AvgReadingTimeMs, ScrollDepthAvgPct, BounceRate, LastViewedAt). Updated incrementally by analytics ingestion.
• EditorialArticleSeo (ArticleId PK, CanonicalUrl, OgImage?, SchemaType: 'Article'|'NewsArticle'|'BlogPosting'). When null, derived from defaults.
• EditorialArticleVersion (snapshot table for edit history, 90-day restore window).
• EcosystemEntityLinks (shared platform-wide, see §13.1.3 — supports entityType='editorial').
• EditorialSubscription (Email, PreferredLang, InterestCategories[], ConsentPlatform, ConsentPartners, Source: 'listing'|'category'|'topic'|'article', ArticleIdContext?, CreatedAt).