Auditing or_tasks reachability

Count how many or_tasks rows are genuinely reachable from no product surface — without condemning the customer task library in the process.

Owner: platformWhen to use: an anti-join says or_tasks is leaking rows

Canonical source. derive_task_shape in app/api/task/task_shape.py is the only definition of what class of thing an ORTask row is. task_shape_sql() in the same module is its SQL twin, pinned to it over the full input space by app/api/task/tests/test_task_shape_sql.py. Paste that CASE; never hand-derive the column combinatorics — this page exists because doing so has produced a false data-leak report twice.

TL;DR

🅰 Five shapes, one table

or_tasks serves five products. project_id, playbook_id, module_id, is_library_task and is_template are the only discriminators, and four of the five shapes have a legitimately NULL project_id.

🅱 A missing mapping is normal

or_task_mappings is not a universal parent link. Library tasks never get one, and rows predating the module_id column reach their module only through one. Neither direction of that anti-join means "detached".

🅲 ORPHAN ≠ unreachable

The resolver has no LINKED shape. A task in a linked playbook module resolves ORPHAN and is still fully reachable via or_module_mappings. Confirm reachability before calling a row garbage.

The audit

Run it on the prod read replica — load the prod-db-investigation skill first; the standby guard is mandatory.

Start with a census, not a predicate. The distribution is what tells you whether you have a leak at all:

SELECT CASE
         WHEN t.deleted_at IS NOT NULL AND t.deleted_by IS NOT NULL THEN 'DELETED'
         WHEN t.is_template THEN 'TEMPLATE'
         WHEN t.project_id IS NOT NULL THEN 'PROJECT'
         WHEN t.playbook_id IS NOT NULL THEN 'PLAYBOOK'
         WHEN t.module_id IS NOT NULL THEN
           CASE WHEN m.is_library_module THEN 'LIBRARY_MODULE' ELSE 'ORPHAN' END
         WHEN t.is_library_task THEN 'LIBRARY_TASK'
         ELSE 'ORPHAN'
       END AS shape,
       COUNT(*) AS n,
       COUNT(DISTINCT t.vendor) AS vendors,
       MAX(t.created_dts)::date AS newest
FROM or_tasks t LEFT JOIN or_modules m ON m.id = t.module_id
GROUP BY 1 ORDER BY 2 DESC;

newest is the single most valuable column and the one an all-time COUNT(*) throws away. A shape whose newest row is months old is a closed regression: the fix already shipped, and what remains is inert residue, not an active leak. Only put a chip on the board for a bucket that is still growing.

Then split the ORPHAN bucket by why, because the causes have nothing in common:

SELECT CASE WHEN t.module_id IS NULL THEN 'no_module_at_all'
            WHEN m.id IS NULL THEN 'module_row_missing'
            ELSE 'module_not_library' END AS why,
       COUNT(*) AS n,
       COUNT(*) FILTER (WHERE EXISTS (
         SELECT 1 FROM or_task_mappings tm WHERE tm.or_task = t.id)) AS mapping_reachable,
       COUNT(*) FILTER (WHERE t.created_dts >= now() - interval '90 days') AS last_90d,
       MAX(t.created_dts)::date AS newest
FROM or_tasks t LEFT JOIN or_modules m ON m.id = t.module_id
WHERE NOT (t.deleted_at IS NOT NULL AND t.deleted_by IS NOT NULL)
  AND NOT t.is_template AND t.project_id IS NULL AND t.playbook_id IS NULL
  AND (t.module_id IS NULL OR m.is_library_module IS NOT TRUE)
  AND NOT (t.module_id IS NULL AND t.is_library_task)
GROUP BY 1 ORDER BY 2 DESC;

Finally, date the survivors. A single-day cluster with no created_by and no created_at is platform-import residue, not a code path — on prod that is 2022-12-14, the day ~60,000 tasks were loaded and the date or_modules.created_dts still defaults to. A multi-day cluster that starts and stops is a shipped-and-fixed regression; map its boundaries to release tags with git tag --contains <sha> before writing any code.

Verify

Your predicate agrees with the application if task_shape_sql() and derive_task_shape agree — which the test suite already proves:

uv run pytest app/api/task/tests/test_task_shape_sql.py -q --tb=short --no-header

If you wrote the SQL by hand instead, the census above is your check: a shape count of zero for LIBRARY_TASK or LIBRARY_MODULE on a tenant that demonstrably uses the library means your predicate is wrong, not the data.

Troubleshooting

🚫 "Thousands of tasks are detached"

The anti-join almost certainly omits is_library_task. A standalone library task has project_id, playbook_id and module_id all NULL and never gets an or_task_mappings row — that is the documented LIBRARY_TASK shape, reachable at Library → Tasks, and it is the bulk of everything the naive query returns.

⚠ `deleted_at IS NULL` is not the delete test

ORStandardModel.is_deleted is deleted_at IS NOT NULL AND deleted_by IS NOT NULL. A half-stamped row is live to the application, so the shorter predicate hides rows that are still served. Render both columns.

⚠ `module_id IS NULL` predates the column

Module linkage originally lived only in or_task_mappings. Old rows therefore carry a NULL module_id and a real mapping, and are reachable through it. Always report mapping_reachable alongside any module_id IS NULL count.

⚠ `ORPHAN` over-reports linked modules

The resolver identifies LIBRARY_MODULE positively and has no shape for a linked playbook module, so a task reaching its playbook only via or_module_mappings lands in ORPHAN. See .claude/rules/playbook-link-semantics.md; ORTask.playbook_id is a cached projection and is legitimately NULL for those rows.