docs/project-resource-foundation.md

MediaRouter Project Resource Foundation (P2)

Scope

P2 verifies the Project Foundation (P1), integrates its real API with the existing frontend, and adds only the minimum authoritative Project-to-Asset and Project-to-Job relationships needed before a future editor. It does not implement an editor, timeline, AI Studio, publishing UI, analytics, automation, or a new asset/job domain.

P1 verification

The P1 implementation remains layered as API -> ProjectService -> ProjectRepository -> SecurityDatabase. The five core /v1/projects operations, typed request/response schemas, method-specific scopes, audit events, archive lifecycle, tenant predicates, and forced-RLS migration remain intact.

Production targets Python 3.10:

• Dockerfile uses python:3.10-slim.
• pyproject.toml configures Black and Ruff for Python 3.10.
• requirements.txt pins FastAPI 0.115.12, Pydantic 2.11.4, and Pydantic Settings 2.9.1.

The current Termux environment has Python 3.14.6. Its .venv also uses Python 3.14.6 and does not contain Pydantic or pytest. Pydantic's pinned native core cannot be built for this Python version. Docker, Podman, PostgreSQL, and psql are unavailable here. Production pins were not weakened.

Consequently, source compilation and frontend integration are verifiable locally, but runtime FastAPI/OpenAPI and PostgreSQL RLS execution require either:

1. the production Docker image on a host with Docker, or
2. CPython 3.10 with requirements.txt installed, plus disposable PostgreSQL admin and non-BYPASSRLS tenant credentials.

For PostgreSQL verification, set SOCIAL_TEST_ADMIN_DATABASE_URL and SOCIAL_TEST_TENANT_DATABASE_URL to an isolated disposable database and run pytest tests/test_postgres_rls.py. Never point these variables at production.

Authoritative domains

Assets

media_assets is the canonical workspace-owned media domain. Social media assets are publishing-specific wrappers and are unchanged.

P2 adds nullable media_assets.project_id -> projects.id ON DELETE RESTRICT. This model was selected because the target hierarchy gives a canonical asset one optional project parent. Existing workspace-level assets remain valid with project_id = NULL; no migration invents ownership for historical records.

The mediarouter_assert_media_asset_project_workspace trigger enforces:

• canonical asset workspace immutability;
• referenced project existence;
• exact asset/project workspace equality;
• attachments only to active projects.

The indexed access path is (workspace_id, project_id, created_at DESC).

Jobs

MediaRouter does not have one generic job table. Durable generation jobs, social publishing jobs, and operation/worker concepts have different persistence and lifecycle rules.

P2 therefore adds project_generation_jobs, a narrowly named association for the existing durable generation_jobs table. It does not change or merge social jobs, operations, WAN, or FLUX behavior. Each durable generation job may be attached to at most one project.

Fields:

• id
• workspace_id
• project_id
• generation_job_id (unique)
• attached_by
• created_at

All foreign keys use ON DELETE RESTRICT. Projects archive instead of hard-delete, and resource histories cannot be silently cascaded away.

The mediarouter_assert_project_generation_job_workspace trigger enforces:

• immutable association ownership;
• exact project/job/association workspace equality;
• active-project attachment;
• active membership for the attaching user.

RLS and ownership

The existing forced-RLS policy on media_assets continues to isolate canonical assets by app.workspace_id; the new cross-workspace trigger protects the project reference independently of application checks.

project_generation_jobs enables and forces RLS. Separate SELECT, INSERT, and DELETE policies require the transaction-local workspace plus an active existing workspace membership. INSERT additionally requires attached_by = app.user_id.

Repository queries always include workspace_id and use SecurityDatabase.tenant_session, even though RLS remains the database backstop. Foreign project, asset, and job identifiers return tenant-safe not-found errors rather than revealing another workspace's resources.

Permissions

No parallel resource scopes were created.

Operation	Required scope
List/read project resources	projects:read
Attach/detach asset	projects:update
Attach/detach generation job	projects:update

Core project methods continue to require projects:read, projects:create, projects:update, and projects:delete. projects:write remains a frontend compatibility aggregate; backend enforcement is exact and method-specific. API-key authentication follows the same ScopePolicy mapping.

APIs

Core P1 routes are unchanged:

• GET /v1/projects
• POST /v1/projects
• GET /v1/projects/{project_id}
• PATCH /v1/projects/{project_id}
• DELETE /v1/projects/{project_id} (archive, never hard-delete)

P2 adds:

• GET /v1/projects/{project_id}/assets
• POST /v1/projects/{project_id}/assets with { "asset_id": "..." }
• DELETE /v1/projects/{project_id}/assets/{asset_id}
• GET /v1/projects/{project_id}/jobs
• POST /v1/projects/{project_id}/jobs with { "generation_job_id": "..." }
• DELETE /v1/projects/{project_id}/jobs/{job_id}

The job response has the explicit job_type: "generation" discriminator. It does not imply support for social or generic jobs.

Relationship mutation is allowed only for active projects. Re-attaching a resource to the same project is idempotent and does not emit a duplicate audit event. Attachment to another project returns a conflict.

Audit

The shared AuditService records only state-changing relationship events:

• project.asset_attached
• project.asset_detached
• project.job_attached
• project.job_detached

Events use the project as the audited entity and contain only a bounded resource_id. Provider metadata, generation input, credentials, tokens, and asset metadata are not copied into audit records.

Frontend integration

The frontend continues to discover project support from runtime OpenAPI. There is no environment flag or frontend-only availability override.

Discovery now resolves independently:

• collection/detail reads;
• create (POST);
• update (PATCH);
• archive (DELETE);
• nested asset reads;
• nested job reads.

TanStack Query remains authoritative for server state. Project create, update, and archive mutations invalidate the shared project key boundary. Project context continues to use URL state plus queries and does not introduce Zustand project state.

The project detail renders only supported modules:

• Project Header
• Overview
• Assets (only when its nested GET path exists)
• Jobs (only when its nested GET path exists)

The Assets view displays canonical filenames, MIME types, sizes, and timestamps. The Jobs view displays only attached durable generation jobs. Empty states never fabricate records. Create, edit, and archive controls require both their OpenAPI method and exact permission. The command registry registers Open Projects and, when authorized, Create Project.

The frontend facade also distinguishes a direct ProjectResponse.metadata field from a MediaRouter API envelope, preventing direct project detail responses from being incorrectly unwrapped.

Migration and rollback

Migration: app/projects/migrations/0002_project_resources.sql.

It is additive and contains no table drops. A rollback, if operational tooling requires one, must first detach all project asset/job relationships, drop the relationship table and its policies/triggers, then remove the asset trigger/index/FK/column. Rollback must not run while future resources depend on these links. P1 and existing social migrations are not edited destructively.

Verification commands

Production-compatible runtime:

python -m pytest tests/test_projects_foundation.py
SOCIAL_TEST_ADMIN_DATABASE_URL=... SOCIAL_TEST_TENANT_DATABASE_URL=... python -m pytest tests/test_postgres_rls.py

Frontend:

cd frontend
npm run typecheck
npm run lint
npm test
npm run build

Current limitations and future editor requirements

• Runtime OpenAPI generation is not verified in the current Python 3.14 environment.
• PostgreSQL migration/RLS execution is not verified without a disposable PostgreSQL instance and non-bypass tenant role.
• Only generation jobs have a persistent project association. Social jobs and transient operations are deliberately excluded.
• P2 does not automatically attach generation outputs or uploaded assets. Future producing services must explicitly call the relationship service after both resources are authoritative.
• There is no project resource reorder, bulk attachment, editor document, timeline, history, collaboration, or versioning contract.
• The future editor should consume these IDs and services rather than add another project, asset, job, or activity store.

P2 validation status in this workspace

• Python source compilation: PASS.
• Black check for touched backend files: PASS.
• Static API/migration guard inspection: PASS.
• Frontend TypeScript: PASS.
• Project-scoped ESLint: PASS with zero warnings.
• Frontend Vitest: PASS, 41 files and 125 tests.
• Full frontend ESLint: pre-existing unrelated failures remain in global search, theme, developer, playground, social, realtime, and shared virtualization files; no P2 project file is reported.
• Frontend production build: ENVIRONMENT LIMITATION; Turbopack has no Android/arm64 native binding and refuses its WASM-only fallback.
• Backend runtime tests: NOT VERIFIED; Python 3.10 dependencies are unavailable under the local Python 3.14 environment.
• Runtime OpenAPI: NOT VERIFIED.
• PostgreSQL migration/RLS/security integration: NOT VERIFIED.