DB Schema & Project Management API

[tkowalczyk]

Parent PRD

#1

What to build

Set up the database schema and API endpoints for project management. This is the foundation everything else builds on.

Schema: Create tables for Project (id, name, unique_slug, status, github_repo, created_at), Message (id, project_id, role, content, phase, created_at), Spec (id, project_id, content_markdown, version, created_at), and Task (id, project_id, title, description, status, github_issue_number, github_issue_url, sort_order).

API: CRUD endpoints for projects — create project (generates unique slug), list projects, get project by slug. Project creation returns the shareable client link.

Uses Drizzle ORM + Neon Postgres as per the saas-on-cf template.

Acceptance criteria

• Database tables created for Project, Message, Spec, and Task entities
• Drizzle migrations generated and applied
• API endpoint to create a new project (returns unique slug)
• API endpoint to list all projects with status
• API endpoint to get a project by slug (public, no auth required)
• Unique slug generation is collision-resistant

Blocked by

None - can start immediately

User stories addressed

• User story 16