Projects layer — Phase 1 spec

Status: draft, awaiting approval Author: Frederike + Claude Date: 2026-05-29 Scope: minimum viable Project/Brand layer between Organization and tenant resources Estimated work: ~6–8 focused hours

Why

Today the codebase has 2 tiers: User → Organization → (channels, sources, content). The Organization layer is doing double-duty as both "the company/account" and "the brand", which forces a bad choice:

• One org per brand = login fragmentation, no cross-brand overview, ugly UX
• All brands in one org = 11 channels visible together with no way to scope publishing to a single brand → high risk of cross-brand mis-publishing, especially under autopilot

We need a 3-tier model:

User
 └── Organization (the company/account, e.g. "Frederike Falke's Workspace")
       └── Project/Brand (TIRIDA, NxtConnect, Amplicast, …)
             └── Channels, Sources, Content

Current production state (as of 2026-05-29)

• 1 user (you), 1 active org (d1bc8de1-…)
• 11 channels across 4+ brands all in that one org
• 24 content_items in that one org (all just backfilled with org_id)
• 1 source: Tirida Character World (b2_manifest)

In scope (Phase 1)

Component	Included
projects table	✅
project_id FK column on platform_accounts, content_sources, content_items, content_packages	✅
Migration + backfill of all existing rows to projects	✅
Sync-worker propagates source.project_id → new items	✅
resolveProject() middleware reading X-Project-ID header	✅
/api/projects CRUD endpoints	✅
useProjectStore (Zustand) + ProjectSwitcher in dashboard header	✅
Scope sources/channels/content tabs by current project	✅
Backfill UI: one-time "assign channels to projects" page	✅

Explicitly deferred to later phases

These are real needs, named here so we don't forget — they are not Phase 1 work.

Phase	Component	Notes
Phase 2	Voice & writing style per project	Extend projects.settings.voice JSONB or new project_voice_profiles table; needed for AI-pipeline brand consistency
Phase 3	Super-admin cross-project dashboard	Aggregates costs / usage / content counts across all orgs + projects; uses existing SUPER_ADMIN_EMAILS env infra
Phase 4	Per-project member roles	New project_members join table with roles (viewer, editor, admin); meaningful when inviting team members
Phase 5	Brand VARCHAR cleanup	content_packages.brand (free-text) becomes redundant; drop column or derive from project_id

Data model

New table: projects

CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name        VARCHAR(200) NOT NULL,
    slug        VARCHAR(100) NOT NULL,
    settings    JSONB NOT NULL DEFAULT '{}',
    created_at  TIMESTAMP DEFAULT now(),
    updated_at  TIMESTAMP DEFAULT now(),
    UNIQUE (org_id, slug)
);
CREATE INDEX idx_projects_org_id ON projects(org_id);

Decisions:

• org_id NOT NULL — a project must belong to an org from day 1
• slug unique per org, not globally — your "tirida" and someone else's "tirida" don't collide
• ON DELETE CASCADE on org — deleting an org cascades to its projects (existing org-deletion behavior expects this)
• settings JSONB — placeholder for future Phase 2 voice/style; empty for now

Modified tables — add project_id

For each of: platform_accounts, content_sources, content_items, content_packages:

ALTER TABLE <table> ADD COLUMN project_id UUID REFERENCES projects(id) ON DELETE RESTRICT;
CREATE INDEX idx_<table>_project_id ON <table>(project_id);

Decisions:

• ON DELETE RESTRICT (not CASCADE) — deleting a project should fail if it still has channels/content; force the user to migrate or delete those first
• Nullable during migration, NOT NULL after backfill verified

Migration order (5 SQL files)

1. 20260530_001_create_projects.sql — create the table
2. 20260530_002_add_project_id_columns.sql — add nullable FK columns to all 4 tables
3. 20260530_003_backfill_default_projects.sql — create starter projects + auto-assign rows
4. (After UI review) 20260530_004_assign_remaining.sql — apply manual assignments from backfill UI
5. (After zero NULLs verified) 20260530_005_enforce_not_null.sql — add NOT NULL constraints

Backfill plan

Starter projects to create in your workspace org

Name	Slug	Notes
Amplicast	amplicast	The SaaS itself; no channels yet
Nxtconnect AI	nxtconnect-ai	B2B CRM brand
TIRIDA	tirida	Character world
Frederike Falke	frederike-falke	Personal handles
Pinpulse	pinpulse	No channels yet; placeholder
Rike York	rike-york	No channels yet; placeholder
Ableger.io	ableger-io	No channels yet; placeholder
Creative AI Lab	creative-ai-lab	Legacy bucket for times8/creativeailab channels; user may rename or delete later

Auto-assignment heuristics for the existing 11 channels

Channel (account_name)	Platform	→ Project
TIRIDA.World	tiktok	TIRIDA
TIRIDA Character World	youtube	TIRIDA
NxtConnect AI	linkedin	Nxtconnect AI
@NxtCrm	twitter	Nxtconnect AI
@frederikefalke	instagram	Frederike Falke
Frederike Falke (inactive)	linkedin	Frederike Falke
Creative AI Lab	facebook	Creative AI Lab
Creative AI Lab HQ	facebook	Creative AI Lab
@creativeailab.ai	instagram	Creative AI Lab
Creative AI Lab	linkedin	Creative AI Lab
@times8-ai.bsky.social	bluesky	Creative AI Lab

For content_sources: derive from config.default_brand if present; the Tirida source already has default_brand: "tirida", so → tirida project.

For content_items: derive from source_id → source.project_id.

For content_packages: derive from source_content_id → content_item.project_id. Fallback: use the existing brand VARCHAR if source_content_id is NULL.

Rows that can't be auto-assigned

Kept with project_id = NULL and surfaced in the backfill UI for manual assignment. The NOT NULL migration runs only after the UI confirms zero NULLs.

API changes

New endpoints

Method	Path	Description
GET	/api/projects	List projects in current org
POST	/api/projects	Create project
GET	/api/projects/:id	Get one project
PATCH	/api/projects/:id	Update name/slug/settings
DELETE	/api/projects/:id	Delete (fails if has channels/content)

New middleware

// internal/middleware/auth.go
// resolveProject reads X-Project-ID header; if missing OR if project
// doesn't belong to the current org, returns 400/403.
func resolveProject(next http.Handler) http.Handler { ... }

Special handling:

• /api/projects itself only filters by org_id (you need to list projects before you can pick one)
• /api/organizations, /api/me, /api/auth/* skip project resolution entirely
• /api/sources, /api/platform-accounts, /api/content/* all require both X-Organization-ID and X-Project-ID

Headers

Header	Required when	Meaning
X-Organization-ID	All authenticated requests except /api/me, /api/auth/*	Which org
X-Project-ID	All content/channel CRUD	Which project within that org

Frontend changes

New store: useProjectStore

// web/src/lib/stores/project-store.ts
{
  currentProjectId: string | null,
  projects: Project[],
  loading: boolean,
  loadProjects: () => Promise<void>,   // called when org changes
  setCurrentProject: (id: string) => void,  // persists to localStorage
}

Components

• ProjectSwitcher — in dashboard header, next to OrgSwitcher. Shows current project; dropdown to switch.
• Projects settings page — under Settings tab, CRUD list.
• Sources / Channels / Content tabs — filter by currentProjectId (every API call sends X-Project-ID header).
• Backfill UI page (/dashboard/setup/assign-projects) — one-time, lists all rows with NULL project_id, lets you assign each.

Header wiring

web/src/lib/api.ts already sends X-Organization-ID from useOrgStore. Add the same pattern for X-Project-ID from useProjectStore.

Rollout plan

Step	Time	Description
1	30m	Write migration 001 (projects table). Apply to droplet.
2	1h	Add Go model Project, ProjectStore, /api/projects CRUD endpoints. Compile + smoke test.
3	30m	Write migration 002 (project_id columns nullable). Apply to droplet.
4	1h	Update ContentItem/ContentSource/PlatformAccount models with ProjectID. Update sync-worker to propagate source.ProjectID → item.ProjectID. Compile + deploy.
5	30m	Add resolveProject() middleware + wire into routes that need it.
6	1h	Build useProjectStore, ProjectSwitcher, wire into header + API calls.
7	1h	Backfill UI page.
8	30m	Write migration 003 (create starter projects + auto-assign). Apply to droplet.
9	30m	Run through backfill UI for any NULL rows.
10	15m	Migration 005 (NOT NULL constraints).
11	30m	Smoke test publishing to one channel from one project end-to-end.

Total: ~7h focused. Realistic span: 1 working day.

Decisions (resolved 2026-05-29)

1. Project naming: 8 starter projects as listed above. TIRIDA DE / TIRIDA-FastBrainer deferred — create later as needed.
2. Inactive channel (linkedin Frederike Falke): assign to Frederike Falke project anyway; stays inactive.
3. Creative AI Lab: treat as legacy holding bucket for the 5 channels named "Creative AI Lab" / @creativeailab.ai / @times8-ai. User will rename or clean up later.
4. Migration filenames: use existing pattern YYYYMMDDHHMMSS_name.sql — 20260530000001_create_projects.sql etc.

Rollback

If the backfill produces wrong assignments:

• Before migration 005 (NOT NULL): just UPDATE projects ... and UPDATE <table> SET project_id = ... manually — no harm done
• After migration 005: same UPDATEs work, NOT NULL doesn't block re-assignment
• Worst case (drop the whole layer):

  ALTER TABLE platform_accounts DROP COLUMN project_id;
  ALTER TABLE content_sources DROP COLUMN project_id;
  ALTER TABLE content_items DROP COLUMN project_id;
  ALTER TABLE content_packages DROP COLUMN project_id;
  DROP TABLE projects;

  No data lost (org_id scoping still works as before).

What this spec does NOT cover

• Voice/style settings (Phase 2)
• Super-admin cross-project view (Phase 3)
• Per-project members & roles (Phase 4)
• Brand VARCHAR cleanup (Phase 5)
• Billing scoping (today: per org; per-project billing is a future question)
• Cross-project content reuse (e.g., reposting same article under two brands)