Data Model (form)

Compact reference for every field on a Founder File record, derived directly from the production model (`core/models/founder-file.model.ts`). Three groups: file-wide metadata (one row in `FounderFile`), per-language content (1–3 rows in `FounderFileLang`), and the related junction / lookup tables. For the full schema with SQL types, validation rules, and form rendering, see the Create spec.

File-wide fields (one row in `FounderFile`)

id

System primary key (UUID). Never user-editable.

fileNumber

Editorial sequence number (INT IDENTITY). Renders as "File #04" on covers + cards. Locked after first save.

slug

Auto-generated from EN title (kebab-case, ASCII-safe). Unique. Locked after Status leaves `draft`.

status

Enum (6 states): draft · ready-for-review · under-review · published · needs-updates · archived. See status-workflow spec.

category

Single-select enum (required). 9 taxonomy slugs: `business-fundamentals` · `funding-investment` · `startup-storytelling` · `growth-gtm` · `financial-literacy` · `legal-compliance` · `product-operations` · `leadership-teams` · `mena-market-insights`. SHARED with Articles taxonomy.

topics[]

Many-to-many to `Topic` (string slugs). Min 1, max 8. Card shows first 3, details page shows all. SHARED with Articles.

availableLanguages[]

Array of language codes (`en` / `ar` / `fr`). Derived from which `FounderFileLang` rows exist + complete. Drives the EN/AR/FR chips on cards + the public language switcher.

coverImageUrl

Azure Blob URL · 3:4 portrait · min 600×800 · max 5 MB · JPG/PNG/WebP. Fallback to per-file SVG composite when NULL/404.

ogImageUrl?

Optional 1.91:1 social-share image. Falls back to `coverImageUrl` when NULL.

sponsorId?

Optional FK to `FounderFileSponsor`. ON DELETE SET NULL. If set, sponsor contract must be active on save.

isFeatured

BIT (default 0). Drives the "FEATURED" badge + the landing spotlight section. Only one Featured file per category at a time.

isGated

BIT (default 1). When 1, Download click opens the consent modal (lead capture). When 0, direct PDF stream.

isDownloadable

BIT (default 1). Master kill-switch. When 0, the Download CTA is hidden entirely on the public page.

readingTimeMinutes

TINYINT, range 1–120. Manual editorial estimate. Renders as "{n} min read" chip on cards + details meta row.

publishedAt

DATETIME2 NULL. Auto-stamped UTC on the first transition to `published`. Re-publish does NOT overwrite.

lastUpdatedAt

DATETIME2 NOT NULL. Bumps to GETUTCDATE() on every successful save (any field).

archivedAt? / archivedBy?

Soft-delete stamp + actor. NULL while live. Filled atomically on Archive. Restorable for 90 days.

Per-language content (1–3 rows in `FounderFileLang`)

Composite PK = (`FounderFileId`, `Lang`). One file owns 1–3 rows (EN / AR / FR). The trilingual publish gate requires all 3 rows complete before status can leave `draft`.

title

NVARCHAR(80) NOT NULL · 10–80 chars · h1 on details page + listing card title + breadcrumb tail + browser tab.

subtitle

NVARCHAR(160) NOT NULL · 30–160 chars · the tagline rendered DIRECTLY UNDER the h1 on the details page hero (and on the featured spotlight section of the listing landing).

shortDescription

NVARCHAR(300) NOT NULL · 60–300 chars · the 1–2 sentence summary rendered as the body paragraph on LISTING CARDS + used as meta description fallback when `seoDescription` is empty. NOTE: distinct from `subtitle` — `subtitle` is the inline tagline, `shortDescription` is the card-level summary.

fullDescription

NVARCHAR(MAX) NOT NULL · 100–600 chars · plain text only (HTML stripped on save). Rendered as the "Why this file matters" paragraph on the details page.

bestForTagline?

NVARCHAR(120) NULL · 15–120 chars · short editorial tagline rendered in the "Best for" block on LISTING CARDS (e.g. "Pre-raise self-audit"). Was previously hard-coded per file id in the i18n dictionary; now a proper per-language CP field. Card falls back to the legacy `ff.bestFor.<file.id>` i18n key when the field is empty.

pdfUrl

NVARCHAR(500) NOT NULL · Azure Blob path `founder-files/{slug}/{lang}/v{N}.pdf` · max 50 MB · `application/pdf` only · virus-scanned. Last 5 versions retained per language. NOTE: model currently has a single `pdfUrl` on the file; migration target is per-language. See pdf-storage spec.

fileSizeBytes

BIGINT NOT NULL · auto-computed from the uploaded PDF (server-side). Shown as "{X} MB" chip in the CP after upload + (future) public download button.

pageCount

INT NOT NULL · auto-computed from the PDF parse step. Display only.

seoTitle? / seoDescription?

NVARCHAR(120) NULL / NVARCHAR(300) NULL · optional per-locale SEO overrides. When NULL, page uses `title` + `subtitle` for `<title>` + meta description.

ogTitle? / ogDescription? / ogImageUrl? / canonicalUrl?

Per-locale Open Graph overrides. All optional. Fall back to the equivalent file-wide / `seoTitle` values.

Related tables

FounderFileTopic (N:M junction)

Junction between `FounderFile` and the SHARED `Topic` table. Composite PK = (FounderFileId, TopicId). Card renders the first 3 topics; details renders all (max 8 per file).

FounderFileLearningPoint (1:N)

One row per "What you'll learn" bullet on the DETAILS PAGE. 3–5 rows per file. Each row has `TextEn` + `TextAr` + `TextFr` (each NVARCHAR(100), all required). Rendered in full on details; first 5 are duplicated on the featured spotlight on the landing.

FounderFileSponsor (reference)

Independent record. Fields: id, name, name_ar, tier (`sponsored-by` / `presented-by` / `in-partnership-with` / `powered-by`), websiteUrl, logoUrl, shortDescription, shortDescription_ar, contractStart, contractEnd, isActive. Linked from `FounderFile.sponsorId`.

DownloadLead (1:N · legal record)

One row per consent-gated download. Stores founderFileId (FK ON DELETE NO ACTION), fullName, email, country, role (8-value enum), primaryInterest (6-value enum), companyName?, linkedinUrl?, startupStage?, industry?, websiteUrl?, language (CHAR(2)), consentNewsletter (BIT), consentSponsor (BIT), source (`listing`/`details`/`reader`/`direct`), utmSource?/utmMedium?/utmCampaign?, downloadedAt (DATETIME2). 7-year retention; never hard-deleted via CP.

FounderFileAuditLog (1:N)

One row per status transition or editable-field change. Fields: id, founderFileId, occurredAt, actorId (FK→AdminUser), eventType (`status-transition` / `field-change` / `review-picked-up` / `restored`), fromValue, toValue, reason?. Used by the Edit screen → History tab.

FounderFileAnalyticsDay (1:N)

Aggregated daily counters per (file, day, language): views, reads, downloads, shares + 4 CTA click counters. Powers the "Most downloaded" / "Most read" public sort options + admin lead funnel reports.