Create

Complete spec for every field on the Add-New-Founder-File CP page. Written for the dev who will build the screen + the API + the database, AND for the QC team who will verify the implementation against this spec. For each field you will find: (1) what it does, (2) where exactly it lives in the CP UI, (3) backend type & relationships, (4) validation rules QC can test, (5) what it produces on the public page.

Table: FounderFile (core record)

These fields live on the **Metadata** tab of the Add-New-Founder-File CP page (with the exception of the system-stamped fields at the bottom which are read-only in the UI).

Id (system)

• Purpose — Internal primary key used by every related table (Lang rows, Topics, Learning Points, Leads, Audit log). Never seen by humans.
• DB type — UNIQUEIDENTIFIER NOT NULL · PRIMARY KEY (clustered) · DEFAULT NEWID().
• Validation — QC test: every record returned by the API has a non-empty UUID `id`.

FileNumber (system)

• Purpose — Editorial sequence number ("File #04"). Used as the file's human-friendly identity in the eyebrow on cards + details page + the SVG fallback cover.
• DB type — INT NOT NULL · IDENTITY(1,1) · UNIQUE NONCLUSTERED.
• Validation — QC test: numbers are sequential, no gaps under normal flow. Locked after first save (no edit affordance in the UI).

Slug

• Purpose — The URL-safe identifier under `/founder-files/{slug}`. SEO-critical — once a file is published with a slug, search engines index that URL.
• DB type — NVARCHAR(120) NOT NULL · UNIQUE NONCLUSTERED.
• Validation — Regex `^[a-z0-9]+(-[a-z0-9]+)*$` · 3–120 chars. On duplicate at save, server appends `-2`, `-3`. QC tests: try saving two files with the same EN title and confirm second gets `-2`; try editing slug after publish and confirm the field is disabled.

Status (system + admin actions)

• Purpose — Lifecycle state. Controls public visibility (only `published` shows on the live site) and which actions are available in the CP.
• DB type — NVARCHAR(20) NOT NULL · CHECK Status IN ('draft','ready-for-review','under-review','published','needs-updates','archived') · DEFAULT 'draft'.
• Validation — QC tests: editing any field on a `published` file should auto-flip status to `needs-updates`; Archive button should be disabled when there are pending lead deliveries.
• Workflow — Full state machine in cp-specs:founder-files:status-workflow.

CategoryId

• Purpose — Single-select category (Funding · Growth · Operations · Legal · Hiring). Drives the meta-row label on the public page + auto-listing under `/founder-files/categories/{slug}`. SHARED with Articles — picking `funding` here uses the same Category row that articles tag with `funding`.
• DB type — UNIQUEIDENTIFIER NOT NULL · FK → `Category(Id)` · ON DELETE NO ACTION · N:1 relationship.
• Validation — Required. QC tests: try submit-for-review with empty Category → should fail with "Category required"; try deleting a Category that owns files → should fail with FK constraint.

CoverImageUrl (file upload)

• Purpose — The 3:4 portrait cover image shown on the listing cards + at the top of the details page. When missing, the page falls back to an editorial SVG made of FileNumber + EN Title.
• DB type — NVARCHAR(500) NULL — stores the Azure Blob URL returned by the upload service.
• Storage — Azure Blob path: `founder-files/{slug}/cover.{jpg|png|webp}` · private container · public access via short-lived SAS URL.
• Validation — Aspect 3:4 (±2%) · min 600×800 px · max 5 MB · MIME ∈ {image/jpeg, image/png, image/webp}. QC tests: upload 4:3 → reject with "Wrong aspect ratio"; upload 5.5 MB → reject; upload .pdf disguised as .jpg → server MIME sniff rejects.

OgImageUrl (file upload, optional)

• Purpose — Override image for social-media share previews (Facebook, Twitter, LinkedIn). Different aspect ratio than the 3:4 cover — these platforms want 1.91:1.
• DB type — NVARCHAR(500) NULL.
• Storage — Azure Blob path: `founder-files/{slug}/og.{ext}` · same private container as cover.
• Validation — Aspect 1.91:1 (±5%, recommended 1200×630) · min 1200×630 · max 5 MB · same MIME rules as cover.

SponsorId

• Purpose — Optional link to a sponsor record. When set, drives the sponsor chip in the hero, the dedicated Sponsor block on the page, and the Sponsored badge on the listing card. Lead routing follows the sponsor.
• DB type — UNIQUEIDENTIFIER NULL · FK → `FounderFileSponsor(Id)` · ON DELETE SET NULL · N:1 relationship.
• Validation — If not NULL: `Sponsor.ContractEnd >= GETUTCDATE()` at save time, else save fails with `SPONSOR_CONTRACT_EXPIRED`. QC test: assign a sponsor with a past ContractEnd → save should fail with that exact error code.

IsFeatured

• Purpose — Marks the file as the promoted one in its category. Drives the "FEATURED" badge on the cover + a hero banner slot on the listing landing.
• DB type — BIT NOT NULL · DEFAULT 0.
• Validation — Filtered unique index `(CategoryId) WHERE IsFeatured = 1`. QC test: flip Featured ON for a second file in a category that already has one → save should fail with `FEATURED_ALREADY_EXISTS` and name the conflicting file.

IsGated

• Purpose — Controls whether the Download click captures a lead. Default is gated (=1). Turn off only for files where lead capture would violate a contract (rare).
• DB type — BIT NOT NULL · DEFAULT 1.
• Validation — QC test: with `IsGated=1`, Download click opens consent modal; with `IsGated=0`, Download click goes directly to PDF stream (verify a DownloadLead row is NOT created).

IsDownloadable

• Purpose — Master kill-switch for the Download CTA. Set to 0 to hide the download entirely (e.g. sponsor revoked rights, file awaiting archive).
• DB type — BIT NOT NULL · DEFAULT 1.
• Validation — QC test: with `IsDownloadable=0`, the public details page should NOT render the Download CTA at all (not "disabled" — entirely absent from DOM).

ReadingTimeMinutes

• Purpose — Manual editorial estimate of how long the PDF takes to read. Helps the visitor decide whether to download before committing.
• DB type — TINYINT NOT NULL · CHECK (ReadingTimeMinutes BETWEEN 1 AND 120).
• Validation — Integer 1–120. QC test: enter 0 or 121 → input rejects + server CHECK rejects.

PublishedAt (system)

• Purpose — The date the file first went live. Used in the meta row + Article JSON-LD. Does NOT change on re-publish after needs-updates.
• DB type — DATETIME2 NULL · auto-stamped UTC at first transition to `published`.
• Validation — QC test: archive a published file then restore → PublishedAt should remain unchanged (not bumped).

LastUpdatedAt (system)

• Purpose — Timestamp of the most recent edit on ANY field. Drives the "Updated on" meta line and `dateModified` in JSON-LD.
• DB type — DATETIME2 NOT NULL · auto-stamped UTC on every successful save.
• Validation — QC test: edit any single field on a file, save → LastUpdatedAt should bump to current UTC; PublishedAt stays unchanged.

CreatedBy / LastUpdatedBy (system)

• Purpose — Audit trail — who created the record and who last touched it. Used by the History tab + activity reports.
• DB type — UNIQUEIDENTIFIER NOT NULL · FK → `AdminUser(Id)` · ON DELETE NO ACTION (audit history must survive Admin removal).
• Validation — QC test: deactivate an AdminUser account → existing CreatedBy refs must keep resolving (not become NULL).

ArchivedAt / ArchivedBy (system)

• Purpose — Stamped when the file is soft-deleted. Drives the 30-day HTTP 410 window + 90-day restore eligibility.
• DB type — DATETIME2 NULL · UNIQUEIDENTIFIER NULL FK → AdminUser.
• Validation — QC test: archive then restore within 90 days → ArchivedAt/By cleared back to NULL; archive then wait 91 days → Restore button must disappear.

Table: FounderFileLang (1:N — one row per language)

These fields live on the **EN**, **AR**, and **FR** tabs — same form, three times. Composite PK = (`FounderFileId`, `Lang`). The CP form tabs only let the Admin reach `under-review` when 3 rows exist with all required fields populated (the trilingual publish gate).

Lang (system, per row)

• Purpose — Marks which language the row belongs to. Drives the `availableLanguages` chips on the public page + which CP tab the row belongs to.
• DB type — CHAR(2) NOT NULL · CHECK Lang IN ('en', 'ar', 'fr') · part of composite PK.
• Validation — QC test: a file cannot have two Lang rows with the same value (composite PK enforces).

Title (per language)

• Purpose — The headline. Used as h1 on the public details page, the card title, the browser tab, the breadcrumb tail, the SVG fallback cover, and the SEO title (when SeoTitle is NULL).
• DB type — NVARCHAR(80) NOT NULL.
• Validation — 10–80 chars · no emojis · no straight double-quotes (smart quotes allowed). QC test: try 9-char title → reject; try 81-char title → reject; try emoji → reject; verify character counter live-updates.

Subtitle (per language)

• Purpose — One-sentence tagline that appears immediately under the h1. Sets the value promise. Also doubles as the fallback meta description for SEO when SeoDescription is NULL.
• DB type — NVARCHAR(160) NOT NULL.
• Validation — 30–160 chars · plain text only. QC test: enter 29 chars → reject; enter 161 chars → reject.

FullDescription (per language)

• Purpose — The "Why this file matters" paragraph. Sells the file to the visitor before they download. NOT the file body — the PDF is the body.
• DB type — NVARCHAR(MAX) NOT NULL.
• Validation — 100–600 chars · plain text only (HTML tags auto-stripped on save). QC test: paste a `<script>` tag → server should strip it; paste 99 chars → reject; paste 601 chars → reject.

PdfUrl (per language — file upload)

• Purpose — The actual PDF asset the visitor downloads after consenting. One per language — visitor downloads the PDF that matches the locale they're browsing in.
• DB type — NVARCHAR(500) NOT NULL — stores the Azure Blob URL.
• Storage — Azure Blob path: `founder-files/{slug}/{lang}/v{N}.pdf`. Last 5 versions retained per language. Private container — public access only via short-lived (1h) SAS URL generated at download time. Virus-scanned by Azure Defender.
• Validation — Max 50 MB · MIME `application/pdf` only (server-side sniff, not just extension). QC tests: upload 51 MB → reject pre-upload; upload a .exe renamed to .pdf → server MIME sniff rejects; upload a real PDF → response includes auto-derived `FileSizeBytes` + `PageCount`; replace existing PDF → previous version becomes `v{N-1}.pdf` in storage.

FileSizeBytes / PageCount (per language — system, auto)

• Purpose — Auto-derived metadata about the uploaded PDF. Used for the post-upload preview chip in the CP + (future) display next to the public Download button.
• DB type — BIGINT NOT NULL · INT NOT NULL.
• Validation — QC test: upload a known 3.7 MB / 24-page PDF → CP preview shows "3.7 MB · 24 pages" with values matching the source.

SeoTitle / SeoDescription (per language, optional)

• Purpose — Optional SEO override fields. When set, take precedence over Title/Subtitle for the `<title>` tag + meta description. Use when the editorial title is great for humans but not optimal for search.
• DB type — NVARCHAR(120) NULL · NVARCHAR(300) NULL.
• Validation — When non-NULL: 30–120 chars (title), 70–300 chars (description). QC test: set SeoTitle, view page source → `<title>` should match SeoTitle, not the headline Title.

Table: FounderFileTopic (N:M junction)

Junction table linking files to topics. Composite PK = (`FounderFileId`, `TopicId`). Topics live in a `Topic` table SHARED with Articles — the topic slug `early-stage` resolves to the same row whether tagged on an article or a file.

• TopicId — UNIQUEIDENTIFIER NOT NULL · FK → `Topic(Id)` · ON DELETE CASCADE on the junction row only (the Topic survives).
• Validation — Minimum 1 topic before publish. Maximum 8 topics per file (soft enforced in CP).

Table: FounderFileLearningPoint (1:N)

One row per bullet on the "What you will learn" list. Trilingual columns. 3–5 rows per file (enforced at submit-for-review).

• Id — UNIQUEIDENTIFIER NOT NULL · PK · NEWID() default.
• FounderFileId — UNIQUEIDENTIFIER NOT NULL · FK → FounderFile · ON DELETE CASCADE.
• OrderIndex — TINYINT NOT NULL · 1–5 · controls display order.
• TextEn — NVARCHAR(100) NOT NULL · 10–100 chars.
• TextAr — NVARCHAR(100) NOT NULL · 10–100 chars.
• TextFr — NVARCHAR(100) NOT NULL · 10–100 chars.

Table: FounderFileSponsor (reference table)

Sponsor records exist independently of files; a sponsor can back 0..N files. CP for sponsors is a separate module — fields listed here for relationship reference only.

• Id — UNIQUEIDENTIFIER PK.
• Name — NVARCHAR(80) NOT NULL.
• Name_Ar — NVARCHAR(80) NOT NULL.
• Tier — NVARCHAR(20) NOT NULL CHECK Tier IN ('platinum','gold','silver','bronze').
• WebsiteUrl — NVARCHAR(500) NOT NULL.
• LogoUrl — NVARCHAR(500) NULL.
• ShortDescription / ShortDescription_Ar — NVARCHAR(300) NOT NULL each.
• ContractStart / ContractEnd — DATETIME2 NOT NULL. Sponsor record cannot back files outside this window.

Table: DownloadLead (legal record, 1:N from FounderFile)

Every consent-gated Download click creates a row here. 7-year retention by policy. Never hard-deleted via CP (only via Super Admin DB script for GDPR erasure).

• Id — UNIQUEIDENTIFIER PK.
• FounderFileId — UNIQUEIDENTIFIER NOT NULL FK → FounderFile · ON DELETE NO ACTION (legal).
• PdfVersionDelivered — TINYINT NOT NULL — which `vN.pdf` the lead received (for audit / re-send).
• LangDelivered — CHAR(2) NOT NULL — which language PDF was sent.
• LeadEmail — NVARCHAR(254) NOT NULL · RFC 5321 max.
• LeadRole — NVARCHAR(30) NOT NULL CHECK LeadRole IN ('founder','team-member','investor','accelerator','corporate','student','consultant','other').
• LeadCountry — CHAR(2) NOT NULL — ISO-3166-1 alpha-2.
• ConsentMarketing / ConsentPartners — BIT NOT NULL each. Drives CRM routing (own list vs sponsor handoff).
• CreatedAt / DeliveredAt — DATETIME2 NOT NULL · NULL. Delivered = when email with the PDF was sent.

Cross-module taxonomy reuse summary

Submit-for-review gate (server-side validation)

When the Admin POSTs `submit-for-review`, the server validates ALL of:

1. 3 rows exist in `FounderFileLang` for this FileId (en + ar + fr), each with Title + Subtitle + FullDescription + PdfUrl populated.
2. Between 3 and 5 rows in `FounderFileLearningPoint`, each with TextEn + TextAr + TextFr non-empty.
3. `CoverImageUrl` is non-NULL and points to a Blob that exists (HEAD check).
4. `CategoryId` is non-NULL and FK resolves.
5. At least 1 row in `FounderFileTopic` for this FileId.
6. If `SponsorId` is set: `Sponsor.ContractEnd >= GETUTCDATE()`.

On success: `Status` UPDATE to `under-review`, write audit-log row, emit `founder-file.submitted-for-review` event. On failure: return per-field error array (HTTP 422 with `errors: [{ field, code, message_en, message_ar, message_fr }]`).

Form preview — actual fields the Admin sees

Read-only preview of every input as it will appear in the live CP. Each tab is rendered with real form controls (disabled for preview). QC can use this as a reference when verifying the implementation.

Tab 1 · Metadata

Tabs 2 · 3 · 4 — EN / AR / FR (identical structure)

Showing the EN tab below as the canonical example. The AR tab uses identical fields but switches the editor to RTL. The FR tab is identical to EN.

Tab 5 · Sponsor