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:

```text
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.