Files / Config

MISSION_CONTROL_DB.md

# Mission Control Database Foundation

SQLite is the live operating state for OpenClaw/Fakker Mission Control. Markdown remains the human-readable summary and runbook layer; the database is the controlled source for live projects, agents, tasks, runs, artifacts, decisions, approvals, workflows, workflow steps, and events.

The live database path is `data/mission-control.sqlite3` unless `MISSION_CONTROL_DB` is set.

## Architecture

- SQLite database: default path `data/mission-control.sqlite3`.
- SQL migrations/seeds: `mission_control/sql/001_init.sql`, `mission_control/sql/002_seed.sql`, `mission_control/sql/003_live_state.sql`, and `mission_control/sql/004_task_lifecycle.sql`.
- Migration ledger: `schema_migrations` records applied SQL files, checksums, kind, apply time, and execution time.
- Controlled service layer: `mission_control/service.py`.
- Fakker bridge: `mission_control/bridge.py` is the local integration facade for conversational commands and automations.
- API: `mission_control/api.py` with FastAPI endpoints for controlled actions and a minimal local HTML dashboard.
- CLI utilities: `mission_control/cli.py` for initialization, schema inspection, backup/export, dashboard inspection, and controlled state changes.

Agents should not write raw SQL during normal operation. Use `mission_control.bridge`, `MissionControlService`, the CLI, or the FastAPI endpoints so validation and event logging stay consistent.

## Live State Rule

- `data/mission-control.sqlite3` is canonical for current Mission Control state.
- `MISSION_CONTROL.md`, `TASKS.md`, `PROJECTS.md`, and `AGENT_ROSTER.md` are human-readable summaries and boundaries.
- `state/*.example.*` files are fixtures/examples only, not live state.
- Every meaningful service/API/CLI mutation appends an event.
- A task cannot be marked `done` through the service unless verification notes exist.
- Initialization is idempotent. Re-running `python3 -m mission_control.cli init` applies only missing migrations and does not duplicate seed/live-state rows.
- Live DB files and backup DB files are local runtime state and must stay ignored by git.

## Tables

- `projects`: tracked project boundaries and source-of-truth locations.
- `agents`: Fakker and specialized agent roster state.
- `tasks`: lifecycle, owner, priority, expected output, blocker, and verification fields.
- `events`: append-focused audit stream for meaningful actions.
- `runs`: execution attempts tied to tasks and agents.
- `artifacts`: files or outputs produced by runs/tasks.
- `decisions`: durable architecture or operating decisions.
- `approvals`: human approval requests and review state.
- `workflows`: grouped work with current step and dependencies.
- `workflow_steps`: ordered workflow steps, optional task links, and verification notes.
- `schema_migrations`: migration ledger for applied SQL files.

`tasks.status = 'done'` is blocked by the service unless `verification_notes` are present.

## Seed Data

The seed scripts create the live Fakker project, protected Paperclip boundary, current operating tasks, initial workflows, accepted operating decisions, and these agents:

- Fakker
- Backend Agent
- Frontend Agent
- Infra Agent
- Backoffice Agent
- QA Agent
- Codex Agent

They also create initial Mission Control workflows and seed/live-state events.

## Actions

The service/API currently supports:

- `create_project`
- `create_agent`
- `create_task`
- `update_task_status`
- `assign_task`
- `append_event`
- `update_agent_status`
- `start_run`
- `finish_run`
- `get_dashboard_state`

Every mutating service action appends an event in the same transaction. `append_event` is the direct audit insertion action and does not create a second recursive event.

## Fakker Bridge

Fakker and OpenClaw conversational command handlers should use the bridge as the first local integration layer for task operations:

```python
from mission_control import bridge

task = bridge.create_task(
    title="Example task",
    expected_output="Concrete deliverable.",
    domain="backoffice",
)
summary = bridge.summarize_board()
```

Bridge functions:

- `create_task`
- `create_task_from_intake`
- `list_tasks`
- `get_task`
- `move_task`
- `assign_task`
- `get_task_history`
- `summarize_board`

The bridge initializes the local database idempotently, then delegates to `MissionControlService`. It does not write raw SQL or edit markdown task files.

Local bridge CLI examples:

```bash
python3 -m mission_control.cli bridge create-task --title "Example" --expected-output "Concrete deliverable."
python3 -m mission_control.cli bridge intake-task --text "Title: Follow up\nPriority: high\nSource: telegram"
python3 -m mission_control.cli bridge list-tasks --status planned
python3 -m mission_control.cli bridge show-task task_id --json
python3 -m mission_control.cli bridge move-task task_id in_progress --from-status assigned
python3 -m mission_control.cli bridge assign-task task_id --agent "Codex Agent"
python3 -m mission_control.cli bridge task-history task_id --json
python3 -m mission_control.cli bridge board-summary --json
```

## Initialize And Inspect

```bash
python3 -m mission_control.cli init
python3 -m mission_control.cli dashboard
python3 -m mission_control.cli schema
```

To use a different database path:

```bash
MISSION_CONTROL_DB=/path/to/mission-control.sqlite3 python3 -m mission_control.cli init
```

## Backup And Restore

Create a safe backup/export of the live DB:

```bash
python3 -m mission_control.cli backup
```

By default this writes a timestamped SQLite backup under `data/backups/`, for example:

```text
data/backups/mission-control-20260519T120000Z.sqlite3
```

Backups never overwrite an existing file. To choose a directory or filename:

```bash
python3 -m mission_control.cli backup --output-dir data/backups --name mission-control-before-change.sqlite3
```

Restore procedure:

1. Stop any local API/dashboard process using the DB.
2. Back up the current live DB first:

```bash
python3 -m mission_control.cli backup --name mission-control-before-restore.sqlite3
```

3. Replace `data/mission-control.sqlite3` with the selected backup file:

```bash
cp -i data/backups/<backup-file>.sqlite3 data/mission-control.sqlite3
```

4. Re-run:

```bash
python3 -m mission_control.cli init
python3 -m mission_control.cli dashboard
```

Do not commit `data/mission-control.sqlite3` or anything under `data/backups/`.

## Controlled CLI Actions

Agents can mutate the live state without raw SQL:

```bash
python3 -m mission_control.cli bridge create-task --title "Example" --expected-output "Tracked deliverable."
python3 -m mission_control.cli bridge board-summary
python3 -m mission_control.cli create-task --title "Example" --domain backoffice --expected-output "Tracked deliverable."
python3 -m mission_control.cli assign-task task_id --owner-agent-id agent_codex
python3 -m mission_control.cli update-task-status task_id --status active
python3 -m mission_control.cli update-agent-status agent_codex --status active --health healthy
python3 -m mission_control.cli start-run --task-id task_id --agent-id agent_codex --summary "Starting work"
python3 -m mission_control.cli finish-run run_id --result ok --verification-notes "Checks passed"
python3 -m mission_control.cli append-event --type note.added --entity-type task --entity-id task_id --message "Note"
```

To mark a task done:

```bash
python3 -m mission_control.cli update-task-status task_id --status done --test-status Passed --verification-notes "Specific verification evidence."
```

## Run The API

Install the API dependencies first:

```bash
python3 -m pip install -r requirements.txt
```

Then run:

```bash
python3 -m uvicorn mission_control.api:app --host 127.0.0.1 --port 8000
```

Useful endpoints:

- `GET /dashboard`
- `GET /dashboard.html`
- `GET /`
- `POST /projects`
- `POST /agents`
- `POST /tasks`
- `PATCH /tasks/{task_id}/status`
- `PATCH /tasks/{task_id}/assignment`
- `POST /events`
- `PATCH /agents/{agent_id}/status`
- `POST /runs`
- `PATCH /runs/{run_id}/finish`

Keep the host bound to `127.0.0.1`; do not expose the API publicly. Auth and Telegram integration are intentionally not included in Milestone 3.