[Epic] ClickUp ingestion: AI4UI active projects + Operational Excellence queue + daily sync

[semicolint]

Goal

Ingest the AI4UI ClickUp workspace into the dashboard as the source of truth for two distinct pipelines, while merging with — not replacing — the existing manually-curated projects.

1. Active projects — every project list under the IIDS-AI4UI space (excluding [TEST] * lists and the queue list itself)
2. Operational Excellence Queue — every task under the "AI4UI New Project Requests" list
3. Daily status sync via GitHub Actions cron, one-way (ClickUp → dashboard)

The existing 15 manually-curated rows in applications are preserved (source='manual'); only rows from source IN ('clickup-active','clickup-queue') are touched by the sync.

ClickUp source mapping

Pipeline #1: Active projects (lists in space 90171739280 / IIDS-AI4UI)

Skip lists matching [TEST] * and skip the "AI4UI New Project Requests" list. List content is a structured markdown block parsed as:

applications column	ClickUp source	Parser
name	list.name	direct
description	Scope: block in list.content	regex
owner_name	Project Lead: line	regex
owner_email	assignee email lookup for the named lead	ClickUp users API
department	Customer: line	regex
github_repo	first GitHub URL in Repo Link(s): line	regex (often empty)
status	derived from the originating queue task (see below)	name-match join
clickup_list_id (new column)	list.id	direct, upsert key
source (new column)	'clickup-active' constant

Status derivation (option A): Each Pipeline #1 list is matched to a Pipeline #2 queue task by case-insensitive name similarity. The queue task's status drives the dashboard status:

Queue task status	Dashboard status
to be reviewed	idea
active	building
complete	maintained
rejected	archived

Fallback for lists with no queue-task counterpart (e.g., MindRouter 2.0, Nexus, OpenERA, predating the queue): default to building and flag in sync log for manual triage.

URL field: intentionally not populated by sync. The "Audience" line in list.content is not a URL field. See follow-up issue to add a real URL custom field in ClickUp.

Pipeline #2: Queue tasks (list 901706737951 / AI4UI New Project Requests)

applications column	ClickUp source	Parser
name	task.name	direct
description	task.description	direct
owner_name	first task.assignees[].username	direct
owner_email	first task.assignees[].email	direct (full task fetch)
status	mapped from task.status	same table as above
clickup_task_id (new column)	task.id	direct, upsert key
source (new column)	'clickup-queue' constant

Queue ↔ Active relationship

Many queue tasks have list-counterparts (e.g., "Idaho Water Law Repository" ↔ "Water Law Database"). On the dashboard, queue tasks that have a matching active project are still surfaced separately on the queue page (different audience), but their status drives the active project's status. We rely on name-match for matching today; a clickup_promoted_to_list_id field can be added later for explicit linking once ClickUp data hygiene improves.

Data hygiene note for ClickUp side: every active project list should have a corresponding queue task (and vice versa where applicable) for status derivation to work cleanly. May require a one-time cleanup pass in ClickUp.

Schema changes (migration 010)

ALTER TABLE applications
  ADD COLUMN clickup_list_id   TEXT UNIQUE,
  ADD COLUMN clickup_task_id   TEXT UNIQUE,
  ADD COLUMN source            TEXT NOT NULL DEFAULT 'manual',
  ADD COLUMN last_synced_at    TIMESTAMPTZ;
CREATE INDEX idx_applications_source ON applications(source);

Existing rows get source='manual' and stay untouched by the sync.

UI presentation

• /portfolio — unchanged for stakeholders; shows source IN ('manual','clickup-active')
• /portfolio/queue (new page) — shows source = 'clickup-queue'; tighter triage-oriented view for IIDS staff (status, owner, last-synced); links each queue row to its matched active project if one exists

The two pages have different audiences (stakeholders vs. IIDS triage), which is why they're separated rather than filter-chips on a single page.

Sync semantics

• Upsert by clickup_list_id / clickup_task_id. If a row exists, update only ClickUp-authoritative fields (name, description, owner, status). Dashboard-edited fields (blockers, notes) are left alone.
• Disappearance: if a ClickUp source is deleted/archived, set status='archived' (soft-delete) — never hard-delete dashboard rows.
• Conflict resolution: ClickUp wins for the fields it owns; the dashboard owns blockers/notes/edits.
• Existing manual rows: untouched. The sync filters on source != 'manual' for any deletions/updates.

Implementation plan (sequential PRs)

• PR 1 — Migration 010 (schema only, no behavior change)
• PR 2 — lib/clickup-parse.ts (the list-content regex parser, with unit tests against MindRouter 2.0 / Nexus fixtures)
• PR 3 — scripts/sync-clickup.ts + npm run sync:clickup; manual run only for now; structured JSON sync report
• PR 4 — /portfolio/queue page; lib/work.ts helpers to scope queries by source
• PR 5 — GitHub Actions cron (1×/day) running npm run sync:clickup against prod DB; secrets via repo settings
• PR 6 — Retire lib/portfolio.ts projects[] array + seed-portfolio.ts (types and constants stay)

Out of scope

• Two-way sync (dashboard → ClickUp). One-way only.
• Real-time webhooks. Daily cron is sufficient.
• The tier column for ingested rows — defaults to 1, not exposed publicly.

Decisions made

• ✅ Existing manual rows preserved — source='manual' is the merge layer
• ✅ Pipeline Secure enterprise agreement with Anthropic for institutional AI tool use #1 status derivation: Option A (inherit from originating queue task)
• ✅ UI: separate /portfolio/queue page (Option B)
• ✅ URL: not populated by sync; ClickUp custom field tracked in follow-up issue