Spaces:
Running
Running
| # 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. | |