[Feature]: Add the project planning domain model and authorization foundation

[roomote-community]

Problem

Roomote has durable tasks, artifacts, repositories, environments, and automation work items, but no project planning model for milestones, dependencies, acceptance, or project-scoped permissions.

Proposed behavior

Add the additive database and typed service foundation for projects, project repositories, immutable plan versions, milestones, plan items, dependencies, task/evidence links, members, and configurable role grants.

Reuse the existing Drizzle schema conventions, server-only database boundary, repository/environment/task relations, authorization patterns, and N-1 rollback guarantee. Do not repurpose tasks or automation work_items.

Acceptance criteria

• Project, milestone, item, dependency, membership, and role schemas are typed and covered by constraints.
• Dependency cycles are rejected with a useful cycle path.
• Unauthorized users cannot read or mutate project entities.
• Additive migrations preserve one-release application rollback.
• Archive/delete behavior preserves linked task and artifact history safely.

Validation

Package-scoped database tests, schema constraints, service authorization tests, and N-1 rollback coverage.

[roomote-community]

I've analyzed this issue and here's my proposed implementation plan:

1. Define the shared project-planning vocabulary in a focused packages/types/src/project-planning.ts module and export it through the package index. Include typed project/archive state, milestone and item state, acceptance state, member roles, and grantable actions as as const vocabularies so database checks and service inputs share one source of truth.
2. Extend packages/db/src/schema.ts with additive tables and Drizzle relations for projects, project-repository mappings, immutable plan versions, version-scoped milestones and plan items, item dependencies, project-task links, project-evidence links, project members, configurable project roles, and role grants. Use foreign keys, composite uniqueness, ordering constraints, same-project/version consistency, and vocabulary CHECKs; keep tasks and work_items unchanged.
3. Model authorization as project-scoped RBAC: create each project, its owner membership, default roles, and grants atomically; resolve effective grants from the active member's assigned role; allow deployment admins to administer projects; and require the resulting permission predicate in every project read and mutation rather than filtering only at the route layer. Default roles should be seeded data backed by the same configurable role/grant tables, not hard-coded authorization branches.
4. Add a server-only project-planning service under packages/db/src/lib/project-planning.ts, export it only from packages/db/src/server.ts, and expose transaction-safe operations for project lifecycle, membership/role management, repository links, plan publication, milestones/items, dependencies, task/evidence links, and acceptance. Keep immutable plan versions append-only: edits produce a new version and published versions are never updated in place.
5. Reject dependency cycles inside the dependency mutation transaction. Serialize graph mutations by locking the project/version, use a recursive query or deterministic graph traversal to test the proposed edge, and return a domain error containing the complete item-title/ID cycle path (for example A -> B -> C -> A). Also reject self-dependencies, cross-project/version edges, and duplicate edges at the schema/service boundaries.
6. Preserve history through lifecycle semantics rather than destructive cascades. Archive projects with archivedAt/archivedByUserId; reserve hard deletion for projects with no durable history. Keep task and evidence link rows when their targets disappear by using nullable ON DELETE SET NULL references plus immutable captured metadata. This is necessary because the current task deletion path soft-deletes the task but physically removes task_artifacts; evidence links must therefore retain the artifact path/version/type (and any acceptance note) instead of depending on task_artifacts as their only record.
7. Add inferred row/create types for all new tables in packages/db/src/types.ts, new factories under packages/db/src/fixtures/factories/, schema exports/relations in packages/db/src/server.ts, and generate the next Drizzle migration and snapshot from schema.ts. The migration should only create nullable/defaulted columns, tables, constraints, and indexes so the previous application release remains bootable against the upgraded database.
8. Add real-Postgres coverage in packages/db/src/__tests__/schema-constraints.test.ts (or a focused project-planning constraint suite) for FK/composite uniqueness, vocabulary checks, immutable version behavior, and deletion semantics. Add packages/db/src/lib/project-planning.test.ts coverage for authorized and unauthorized reads/mutations, admin override, configurable grants, atomic owner/bootstrap creation, cycle paths, concurrent dependency insertion, archive behavior, and retained task/evidence history.
9. Validate with the package-scoped DB test/type/lint commands, migration journal consistency, and the existing deploy/ci/upgrade-compatibility.sh lane, which snapshots the previous schema contract, applies candidate migrations, and cold-restarts the previous release against the new schema.

This keeps the foundation inside the existing @roomote/db client-safe/server-only boundary, uses the repository's generated Drizzle migration and real-database testing conventions, and makes authorization and history preservation properties of the shared service rather than assumptions each future UI/API caller must reproduce.

Please tag @roomote if you'd like me to implement this, or reply with feedback on the plan.