Data Model

Database tables backing the Opportunity Radar module. 16 tables total: 1 core record, 1 i18n companion, 1 organizer, 5 lookup/taxonomy, 5 child detail tables, 2 cross-module helpers, 1 subscription, 1 analytics event log. Every bilingual field uses an *Lang companion table; never store EN + AR in the same row.

Table 1 · Opportunity (core)

Id (PK, uuid, required)

Stable primary key. Never exposed to URLs (use Slug).

Slug (varchar(120), unique, required)

URL identifier. Kebab-case ASCII. Used in route /:lang/opportunity-radar/{slug}.

TypeId (FK → OpportunityType, required)

Single type per opportunity.

OrganizerId (FK → Organizer, required)

One canonical organizer; co-organizers (if any) live in a separate OpportunityCoOrganizer join table.

CountryId (FK → Country, required)

Primary country anchor. Multi-country eligibility tracked separately via OpportunityCountry join.

Status (varchar(20), required, enum)

Values: Open / ClosingSoon / Rolling / Closed. Some statuses are computed (see Status Logic page) but persisted nightly for fast filtering.

IsRolling (bool, required, default false)

When true, DeadlineDate ignored; opportunity accepts applications continuously.

IsSponsored (bool, required, default false)

Commercial promotion flag. Drives "SPONSORED" badge + sponsor disclosure ribbon when set.

IsVerified (bool, required, default false)

Editorial verification gate. Drives the green ✓ badge.

IsActive (bool, required, default true)

Soft-delete flag. IsActive=false hides the row from all public queries but preserves the record + cross-module references.

DeadlineDate (timestamptz, nullable)

ISO 8601 with timezone. Null only when IsRolling=true. Used for status computation.

OfficialUrl (varchar(500), nullable)

Organizer’s canonical apply URL. Required when Status ∈ { Open, ClosingSoon, Rolling }.

ValueSignal (varchar(120), nullable)

Short bilingual phrase summarising what the opportunity offers — "Up to $100K equity-free" / "Mentorship + workspace". Stored on Opportunity itself (not Lang) when locale-agnostic.

TimeRequired (varchar(60), nullable)

Free-text estimate. "15 minutes" / "2 hours" / "1 day". Drives section D.

DisplayOrder (int, nullable)

Editorial ranking. Lower numbers surface higher in unsorted lists.

EditorialPriority (varchar(20), required)

standard / featured / spotlight. Mirrors §9.3 OpportunityRadar editorialPriority. Default: standard.

PublishedAt (timestamptz, nullable)

Set when the record first goes IsActive=true. Drives "Newest" sort + JSON-LD datePublished.

LastVerifiedAt (timestamptz, nullable)

Bumped by editorial during re-verification. Drives the "Last verified N days ago" note.

VerifiedByUserId (FK → User, nullable)

User who performed the last verification.

CreatedAt / UpdatedAt (timestamptz, required)

Standard audit columns.

Table 2 · OpportunityLang (i18n companion)

• Composite PK: (OpportunityId, Lang). Lang ∈ {'en', 'ar'}.
• Fields: Title (varchar(200), required) · ShortDescription (varchar(220), required, ≤200 in practice) · FullDescription (text, required) · ProgramStructure (text, nullable) · WhyItMatters (text, nullable) · FounderProfile (text, nullable) · MinimumRequirements (text, nullable) · ApplicationRequirements (text, nullable) · BestForStages (text, nullable).
• Required: at least the (en) row + (ar) row must both exist with Title, ShortDescription, FullDescription populated. Other fields are individually nullable.

Tables 3-7 · Lookup / taxonomy

OpportunityType

Id, Slug (e.g. 'accelerator'), DisplayOrder. NameLang companion. Seeded values: accelerator · incubator · grant · competition · fellowship · soft-landing · residency · vc-program.

OpportunityStatus

Enum-like reference. Seeded: Open · ClosingSoon · Rolling · Closed. Code allowed to use raw string literal — table is for admin UI lookup.

Country

Shared with calendar / coverage modules. Id, Code (ISO 3166-1 alpha-2), Region (GCC / Levant / North Africa). NameLang companion.

OpportunityStage (join: OpportunityId ↔ StageCode)

Many-to-many. StageCode values: idea, pre-seed, seed, series-a, growth, sme, scaleup.

OpportunitySector (join: OpportunityId ↔ SectorCode)

Many-to-many. SectorCode is free editorial taxonomy: fintech, healthtech, edtech, climatetech, deeptech, agritech, etc.

Table 8 · Organizer

• Id, Name (varchar(200)), Type (Accelerator / Incubator / VC / Corporate / Government / University / Community / Media / Other), CountryCode, LogoUrl, WebsiteUrl, IsVerified.
• OrganizerLang companion: DescriptionLang (text, nullable).

Tables 9-13 · Child detail tables

OpportunityBenefit

Id, OpportunityId, Type (funding / mentorship / investor-access / workspace / corporate-pilots / legal-support / cloud-credits / network-access), IconKey, DisplayOrder. Lang companion: TitleLang + DescriptionLang.

OpportunityEligibilityCriteria

Id, OpportunityId, IsRequired (bool), DisplayOrder. Lang companion: TitleLang + NotesLang.

OpportunityApplicationRequirements

One row per opportunity. RequiredDocs (text[]), OptionalDocs (text[]), PitchDeckRequired (bool), FounderProfileRequired (bool), ProductDemoRequired (bool), FormUrl (varchar(500), nullable).

OpportunityTimeline

Id, OpportunityId, Date (timestamptz), Status (past / current / upcoming — computed at render, not stored), DisplayOrder. Lang companion: LabelLang + DescriptionLang.

OpportunityCountry (join, multi-country eligibility)

OpportunityId ↔ CountryCode. Distinct from Opportunity.CountryId (anchor country); join captures FULL eligibility list when an opportunity spans multiple countries.

Tables 14-16 · Cross-module + admin

OpportunityRelatedOpportunity (manual related links)

OpportunityId ↔ RelatedOpportunityId, DisplayOrder. Editor-curated explicit relationships.

EcosystemEntityLinks (cross-module fabric)

Single shared table across the platform. Fields: Id, EntityType ('opportunity' | 'founder' | 'startup' | 'editorial' | 'event' | 'coverage' | 'founder-file'), EntityId, RelatedEntityType, RelatedEntityId, RelationType, ReasonLang (companion table), DisplayOrder.

OpportunitySubscription

Id, Email, CountryPreference (nullable), StagePreference (nullable), ConsentPlatform (bool, required true), ConsentPartners (bool, default false), Source ('listing' | 'details'), OpportunityIdContext (FK nullable, set when source=details), CreatedAt.