Type of Change

• Fix - bug fix or security fix to source code

Description

Fixes #2525.

ncl groups delete always failed with SQLITE_CONSTRAINT_FOREIGNKEY because the generic single-table DELETE handler (src/cli/crud.ts) ran a bare DELETE FROM agent_groups WHERE id = ? while nine other tables held FKs into it. The group could not be deleted via the CLI at all.

Why this shape

• Custom op, not a generic CASCADE migration. Adding ON DELETE CASCADE to nine existing FK columns means rebuilding every dependent table (SQLite cannot ALTER TABLE … ADD CONSTRAINT). That is a much larger blast radius than this bug warrants, and it bakes the cleanup policy into the schema rather than into one CLI op. A handler is the smallest change that fixes the failure.
• customOperations.delete on the groups resource, not an edit to deleteAgentGroup in src/db/agent-groups.ts. That helper is a primitive used by other code paths that may not want a cascade; leaving it alone keeps the blast radius to the CLI verb.
• Single sync db.transaction() wrapping all the deletes — better-sqlite3 rolls the whole thing back if any statement throws. Counts are sourced from each DELETE's .changes so the response reports exactly what the transaction did, not a separate pre-flight snapshot.
• hasTable guards for the two module-installed tables (agent_destinations, pending_approvals) so an install without those modules degrades silently instead of throwing on no such table.
• messaging_groups rows are shared platform identities (keyed on channel_type + platform_id) and are deliberately preserved on agent-group delete; only the agent-specific join rows in messaging_group_agents are removed.

What changed

• src/cli/resources/groups.ts — removed delete: 'approval' from operations and added a customOperations.delete handler that runs the FK-ordered cascade inside a transaction. Returns { deleted, removed: { sessions, pending_questions, pending_approvals, agent_destinations_owned, agent_destinations_pointing, pending_sender_approvals, pending_channel_approvals, messaging_group_agents, agent_group_members, user_roles, container_configs } }.
• src/cli/resources/groups.test.ts — new file. Three tests covering the regression: (1) full fixture exercising every dependent table including container_configs, (2) polymorphic agent_destinations row in agent B pointing at deleted agent A gets cleaned up, (3) unknown id returns handler-error (not the cryptic SQLite error).

Test plan

• pnpm run typecheck clean
• pnpm test clean (331/331)
• New tests pass in isolation (3/3)
• Manual repro on a live install: ncl groups delete --id <gid> for a group with a session, destination, and pending approval — response should contain non-zero removed counts and the group should be gone from agent_groups.

Out of scope (will file follow-up)

• Killing any running container for the group. This PR only touches DB state. A running container will continue to run, write to data/v2-sessions/<group-id>/, and only exit on its own. Worth a separate change to call killContainer for each session before the DELETE.
• On-disk cleanup of groups/<folder>/ and data/v2-sessions/<group-id>/. These are left untouched so an accidental delete is recoverable (re-insert the row, the folders are still there). Worth re-evaluating once we have a soft-delete / restore flow.

Confidence

High on the fix correctness and the regression coverage. Medium on the FK ordering being exhaustive — I cross-checked every REFERENCES agent_groups(id) in src/db/ (5 in initial schema, 1 each in migrations 011, 012, 014 with CASCADE, plus the two module migrations), and the test fixture inserts a row into every one. If a new FK lands later, the test will catch the regression because the bare delete throws SQLITE_CONSTRAINT_FOREIGNKEY.