# ai-lab-content-projects-table-concept

awesome — two tables will work great:

# Airtable Schema: **Content Projects** + **Content**

## Table 1 — **Content Projects**

Represents the whole initiative spawned by an ask (often many pages + media).

**Fields**

* **Project Name** (Primary, Single line)
* **Project Status** (Single select)

* New Ask → Evaluate → Plan/Map → In Execution → In Review → Ready to Publish → Published → Archived
* **Priority** (Single select): P0 / P1 / P2
* **Requestor / Stakeholders** (Linked to **People** or text for now)
* **RACI**

* Responsible (Link to People)
* Accountable (Link)
* Consulted (Link)
* Informed (Link)
* **Go-Live Window** (Date range)
* **Content Items** (Link to **Content**, allow multiple; show lookup of each item’s Status/Type)
* **% Complete (rolled up)** (Rollup of **Content.% Complete**, `AVERAGE(values)`)
* **Blocked Items (count)** (Rollup of **Content** where **Status = Blocked**, `COUNTALL(values)`)
* **Risks / Decisions / Notes** (Long text)
* **Architecture / Plan Doc** (URL to HackMD/GDoc/Figma/Miro)
* **Publishing Checklist (Project)** (Checkboxes or multi-select; optional)

**Useful Views**

* **Kanban: Project Status**
* **Dashboard: Health** (filter where `Blocked Items > 0` OR `% Complete < 100 AND Go-Live < today+7`)
* **Timeline: Go-Live**

---

## Table 2 — **Content**

Represents a single piece of content (page, image set, video, PDF, post, etc.) with its own lifecycle.

**Fields**

* **Title** (Primary)
* **Project** (Link to **Content Projects**)
* **Type** (Single select): Page / Section Index / News Post / PDF Download / Image Set / Single Image / Video / Audio / Social Asset / Data Viz / Other
* **Status** (Single select — combined “Stage + RACI” lifecycle)

* New / Intake
* Evaluate (content inventory for this item)
* Plan (outline/placement/metadata decided)
* Drafting / Production
* **Needs Input / Blocked**
* Internal Review
* Leadership Review
* Ready to Publish
* Published
* Archived
* **Responsible (Author/Producer)** (Link to People)
* **Accountable (Editor/Owner)** (Link to People)
* **Consulted / Informed** (Links)
* **Source / Draft URL** (URL to HackMD, GDoc, Figma, Frame.io, etc.)
* **Target URL / Slug** (Short text or URL)
* **Asset Location** (Multi-URL: CDN bucket, Airtable attachment, Drive path)
* **Due Date** (Date), **Review By** (Date), **Publish By** (Date)
* **Effort (pts)** (Number) — t-shirt size or story points
* **SEO / Metadata** (Group of fields; show/hide when Type = Page/Post)

* Slug, Title Tag, Meta Description, Canonical, OG Title/Desc, OG Image
* **Accessibility / Compliance** (Group; vary by Type)

* Pages: Headings valid, internal links checked
* Images: Alt text, dimensions, optimized
* Video: Captions, transcript, loudness, aspect, thumb
* PDF: Tagged PDF, fonts embedded, title, language set
* **QA** (Checkbox): Passed
* **Change Log** (Long text)
* **% Complete** (Formula; simple heuristic by Status)

  IF(Status="Published",100,
  IF(Status="Ready to Publish",90,
  IF(Status="Leadership Review",80,
  IF(Status="Internal Review",70,
  IF(Status="Drafting / Production",45,
  IF(Status="Plan",25,
  IF(Status="Evaluate",15,0)))))))
  )

* **Blocked Reason** (Long text; only when Status = Blocked)

**Useful Views**

* **Kanban: Status** (day-to-day)
* **Blocked** (Status = Needs Input / Blocked)
* **Ready to Publish** (Status = Ready to Publish AND QA checked)
* **By Type** (grouped)
* **By Project** (grouped)
* **Due Soon** (Due/Publish within 7 days)

---

## Short-Label Variants (nice for Kanban chips)

* **Projects**: Ask → Evaluate → Plan → Execute → Review → Ready → Published → Archived
* **Content**: Intake → Evaluate → Plan → Draft → Blocked → Int. Review → Stakeholder → Ready → Published → Archived

---

## Conditional Checklists (by Type)

Use a **single “Checklist” long text** with templates via automation (or multiple checkboxes with conditional visibility):

**Page / Post**

* [ ] Outline approved
* [ ] Links updated & validated
* [ ] SEO fields set (title/meta/canonical/OG)
* [ ] Accessibility headings pass
* [ ] Images compressed & alt text present
* [ ] Final copy edit

**Image / Image Set**

* [ ] Alt text written
* [ ] Dimensions / crop spec
* [ ] WebP/AVIF exported
* [ ] Thumbnail/social image
* [ ] License/credit stored

**Video**

* [ ] Captions (human-checked)
* [ ] Transcript stored
* [ ] Loudness normalized
* [ ] Thumbnail selected
* [ ] Player embed tested
* [ ] License/credit stored

**PDF / Download**

* [ ] Tagged PDF accessibility
* [ ] Fonts embedded
* [ ] Title/Subject/Language set
* [ ] File size optimized
* [ ] Version/date in footer

---

## Automations (lightweight, high-leverage)

* **Content → Status = Ready to Publish**

* Notify **Accountable** in Slack/Email, include Target URL + checklist
* If **QA** not checked, post reminder to run QA
* **Any Content → Status = Blocked**

* Ping **Responsible + Accountable**, post **Blocked Reason**
* **All linked Content = Published**

* Set **Project Status → Published**
* **New Content created**

* If **Type** = Page/Post, create empty SEO fields + checklist template
* If **Type** = Video, create caption/transcript tasks
* **Due/Publish reminders**

* 48h before **Publish By**, if not Ready/Published, notify **Accountable**

---

## Why this works for mixed media

* **One Content table** handles pages **and** assets; **Type** governs which fields/checklists matter.
* Project metrics stay clean via rollups; you avoid mixing project-level planning fields into every asset.

---

## Optional Extras

* **People** table (centralize contacts; enables RACI links)
* **Taxonomies** (multi-select): Audience, Topic, Campaign, Site Section
* **Interfaces**:

* **Publishing Queue** (Ready to Publish + QA)
* **Project Health** (progress bar, blockers, dates)
* **My Work** (Assigned to me, next 7 days)

---

If you’d like, I can give you:

1. a **CSV starter** for both tables (import-ready), or
2. an **Airtable JSON schema** you can paste into a base-builder script.