docs/projects-backend-foundation.md

MediaRouter Project Backend Foundation P1

Architecture

Project is the authoritative, durable workspace-owned container for future creative work. P1 introduces only the parent domain. It does not attach assets, generation jobs, templates, publishing records, timelines, analytics, or automations.

The implementation follows the existing backend boundaries:

FastAPI router
  -> API-key authentication and method-specific scope policy
  -> ProjectService (validation, lifecycle, audit coordination)
  -> ProjectRepository (tenant-scoped SQLAlchemy access)
  -> SecurityDatabase.tenant_session (PostgreSQL RLS context)
  -> projects

No client request controls id, workspace_id, created_by, status on creation, or any timestamp. workspace_id and user_id come only from the authenticated API-key principal and its active workspace membership.

Database

Migration: app/projects/migrations/0001_projects_foundation.sql

The migration is additive and must run after the authoritative tenancy migration. MediaRouter's existing tenancy, user, and asset primary keys are text columns containing UUID values. Projects use that same representation so their foreign keys remain type-compatible; IDs are generated by gen_random_uuid()::text in PostgreSQL and uuid4() in local SQLAlchemy-managed SQLite.

projects contains:

Column	Contract
id	Server-generated UUID value and primary key
workspace_id	Required FK to workspaces, immutable
created_by	Required FK to users, immutable
name	Normalized non-empty text, maximum 200 characters
description	Nullable text, maximum 4,000 characters
status	active or archived only
thumbnail_asset_id	Nullable FK to authoritative media_assets
metadata	JSON object, limited to 16 KiB by the service
timestamps	Server-controlled creation, update, and archive timestamps

Indexes cover workspace lookup, workspace/status, workspace/update ordering, and creator. There is no name uniqueness constraint: duplicate project names are valid. No child-domain foreign keys or cascading project deletion is introduced.

The migration also adds generic audit_events storage behind the existing AuditService. This extends the shared audit boundary instead of introducing a project-only audit subsystem.

RLS and tenant isolation

projects and audit_events both enable and force PostgreSQL row-level security. Project policies are command-specific:

• SELECT requires app.workspace_id, app.user_id, and an active membership.
• INSERT additionally requires created_by = app.user_id.
• UPDATE requires the same active workspace membership; a trigger makes ownership fields immutable.
• DELETE can only target the active RLS workspace. The public API never issues a hard delete.

Every repository query also includes an explicit workspace_id predicate, so local SQLite tests and privileged backend roles retain the same application-level isolation. Missing and foreign IDs both return PROJECT_NOT_FOUND, preventing IDOR probes from distinguishing another workspace's records.

The project ownership trigger rejects a thumbnail whose canonical asset belongs to another workspace, including writes made by a privileged service role.

Permissions and API keys

The shared scope registry and middleware policy define:

• projects:read — list and retrieve
• projects:create — create
• projects:update — patch
• projects:delete — archive through DELETE

Developer and operator roles receive all four. Viewer receives read only. Administrators continue to resolve through the existing admin scope behavior.

Frontend Architecture v2 already uses projects:write for aggregate capability rendering. The scope remains a compatibility aggregate only: it expands to create/update/delete, while every HTTP method is enforced against its specific scope. An API key may be granted only projects:create without gaining update or delete access.

REST API

All endpoints are authenticated, rate-limited, request-audited, and represented in OpenAPI.

GET /v1/projects

Query parameters:

• status: active (default) or archived
• search: case-insensitive name/description search over the loaded workspace
• limit: 1–100, default 50
• cursor: opaque continuation token

Results are ordered by (updated_at DESC, id DESC). The response contains items, limit, and next_cursor. The cursor encodes the last ordering pair but is opaque to callers; malformed cursors return PROJECT_INVALID_CURSOR.

POST /v1/projects

Accepts only name, description, thumbnail_asset_id, and metadata. Names have internal whitespace collapsed and are never truncated. Unknown/system fields are rejected.

GET /v1/projects/{project_id}

Returns the safe project API schema. workspace_id is included because the authenticated caller already receives its authoritative workspace in /v1/auth/context; it is never accepted as input.

PATCH /v1/projects/{project_id}

Accepts only name, description, thumbnail_asset_id, metadata, and status. Setting status to archived records archived_at. Archived projects are immutable in P1 and return PROJECT_ALREADY_ARCHIVED on subsequent mutations. Restore is not implemented.

DELETE /v1/projects/{project_id}

Returns 204 and archives the project. It never hard-deletes or cascades. A repeated delete returns PROJECT_ALREADY_ARCHIVED.

Thumbnail handling

P1 does not invent a new generic asset collection. A thumbnail is supported only when the ID resolves to the existing authoritative media_assets table and belongs to the authenticated workspace. The API stores the stable asset ID, not a filesystem path or arbitrary URL. Invalid, missing, and foreign references return PROJECT_THUMBNAIL_INVALID.

Asset browsing and project-to-asset relationships remain outside P1.

Validation and errors

Request schemas forbid unknown fields. Name, description, metadata size, enum values, UUID path parameters, limits, and cursor size are bounded before repository access. Database exceptions are handled by the global safe error envelope and are never returned directly.

Project-specific codes are:

• PROJECT_NOT_FOUND
• PROJECT_INVALID_NAME
• PROJECT_INVALID_STATUS
• PROJECT_THUMBNAIL_INVALID
• PROJECT_ALREADY_ARCHIVED
• PROJECT_INVALID_CURSOR

Normal schema failures use the existing VALIDATION_ERROR envelope.

Audit and logging

The shared AuditService records:

• project.created
• project.updated
• project.archived (PATCH status transition)
• project.deleted (DELETE requested; disposition is archived)

Audit metadata contains only bounded field names and lifecycle disposition. Full project metadata, credentials, tokens, authorization headers, and secrets are not recorded. Structured operational logs contain the project ID, workspace ID, operation, duration, and result.

The existing request audit and rate limiter continue to wrap every project route.

Frontend activation

OpenAPI advertises:

• GET and POST on /v1/projects
• GET, PATCH, and DELETE on /v1/projects/{project_id}
• ProjectCreate, ProjectUpdate, ProjectResponse, ProjectListResponse, and ProjectStatus

This satisfies the existing frontend's route-based project capability discovery. The frontend does not need a hardcoded project availability flag.

Migration operations and rollback

Production migrations remain externally applied; application startup never mutates PostgreSQL. Schema readiness now fails closed if projects, audit_events, or required project indexes are missing.

The repository has no destructive down-migration convention. If rollback is required before any dependent domain exists, operators should first disable project traffic, preserve/export project and audit data, then remove policies, triggers, indexes, and tables in dependency order through the normal reviewed database process. Never run that procedure against production without a verified backup.

Current limitations and future relationships

P1 intentionally has no project sharing, comments, versions, editor state, favorite/pin state, templates execution, or child resource relationships. The projects.id key is stable and the workspace ownership constraint is suitable for future composite/trigger-validated references. Each future child domain must reuse its authoritative table, retain its own workspace ID where the existing tenancy architecture requires it, and enforce that the child and project workspaces match. No second asset, job, template, or membership system should be created.