# Process database requirements for admin.apper

Create and apply the schema changes in **admin.apper only**, before enabling the process feature against the shared database. No migration files are provided or run in planner.apper. Automated tests create only isolated SQLite test tables.

## New table: task_processes

| Column                 | Type                                        | Nullable / default          |
| ---------------------- | ------------------------------------------- | --------------------------- |
| id                     | unsigned BIGINT, auto-increment primary key | required                    |
| name                   | VARCHAR(255)                                | required                    |
| description            | TEXT                                        | nullable                    |
| color                  | VARCHAR(20)                                 | nullable                    |
| status                 | VARCHAR(20)                                 | required, default `open`    |
| assigned_to            | unsigned BIGINT                             | nullable                    |
| start_date             | DATE                                        | nullable                    |
| end_date               | DATE                                        | nullable                    |
| completed_at           | TIMESTAMP                                   | nullable                    |
| user_id                | unsigned BIGINT                             | required                    |
| current_team_id        | unsigned BIGINT                             | required                    |
| created_at, updated_at | TIMESTAMP                                   | standard Laravel timestamps |

Foreign keys:

- `assigned_to` → `users.id`, ON DELETE SET NULL.
- `user_id` → `users.id`, ON DELETE RESTRICT.
- `current_team_id` → `teams.id`, ON DELETE RESTRICT.

Indexes:

- `(current_team_id, status)`
- `(current_team_id, start_date)`
- `(current_team_id, end_date)`
- `(current_team_id, assigned_to)`
- Indexes required for the foreign keys.

Use key types, storage engine, and connection compatible with the existing tasks/users/teams tables.

## Change to tasks

- Add `process_id`: nullable unsigned BIGINT, default NULL.
- Index `process_id` and `(current_team_id, process_id)`.
- Foreign key `process_id` → `task_processes.id`, ON DELETE SET NULL. Never cascade-delete tasks.
- Existing tasks remain unlinked; no backfill or task duplication is required.
- Keep existing date, due_date, completed_at, and step_id columns unchanged. The existing nullable tasks.date continues to represent deferred tasks.

## Application invariants

- Both process dates are NULL, or both are present with start_date <= end_date. Endpoints are inclusive.
- Process statuses: `open`, `completed`. Completion sets completed_at; reopening clears it.
- Colors: `rose`, `teal`, `amber`, `sky`, `lime`, `violet`, `slate`, or NULL.
- Assignees and attached tasks must belong to the process's team.
- Every task belongs to at most one process.
- Process date/status/assignee changes never implicitly change linked tasks.
- Processes and linked tasks are team-shared; standalone tasks retain creator/assignee privacy. Detaching restores that privacy.
- No process deletion UI, soft-delete column, mode column, task-day table, pivot table, or stored progress counters are needed.

## Activation

Application code expects the table and column above to exist. Apply the main-project schema before activating this code against a shared database. No live/shared database changes are performed by planner.apper's test suite for this feature.