Primary filter columns and several foreign keys have no index; views nest three to four levels #14

New Issue

2026-10-01T22:50:43Z

KofTwentyTwo commented

2026-10-01 22:50:43 +00:00

(Migrated from github.com)

Summary

Migrations V1 to V60 create 82 tables and 65 views but only 13 indexes. Unindexed columns that the dashboards and reports filter on:

carl_transaction.effective_date (dashboard range filter in DashboardFacts.java:63; only UNIQUE(account_id, source_id) exists)
carl_bill.due_date, carl_bill.vendor_id
carl_plan_step.plan_id, carl_plan_step.assignee_id
carl_grant.member_id
carl_artifact_source.source_id (hit by the staleness EXISTS in carl_artifact_view, V1__carl_domain.sql:197)
carl_correction.record_id, carl_client_workflow.requester_id, carl_expense_settlement.expense_id, carl_balance_source.import_id, carl_record.created_at

Permission views are three to four levels deep with correlated EXISTS checks, and imports scan with LIMIT 100001. The foundation contract asks for indexes on actual filters. The data volume already tested (10,920 transactions, 22,365 balances) is small enough to hide this.

Suggested resolution

Add a migration with the indexes above, then measure the dashboard and report queries with EXPLAIN ANALYZE on the public-household fixture at ten times its current size.

Priority rationale

P2: correctness is fine; performance will degrade with real multi-year data.

Filed from the 2026-10-01 real-versus-fake audit of Carl (branch feature/cicd-liquibase-gitops, HEAD 5414d7a, main 1b334d1). Four read-only reviews covered domain and services, QQQ metadata and data model, tests/CI/evidence, and docs/integrations. A local offline build of a Carl copy against the installed foundation 0.5.0-SNAPSHOT is in progress; its result will be added as a comment. Existing issues #3, #4 and #5 (real Monarch accounts, Authentik with real data, CI/CD with Liquibase and ArgoCD) are referenced rather than duplicated.

## Summary Migrations V1 to V60 create 82 tables and 65 views but only 13 indexes. Unindexed columns that the dashboards and reports filter on: - `carl_transaction.effective_date` (dashboard range filter in `DashboardFacts.java:63`; only `UNIQUE(account_id, source_id)` exists) - `carl_bill.due_date`, `carl_bill.vendor_id` - `carl_plan_step.plan_id`, `carl_plan_step.assignee_id` - `carl_grant.member_id` - `carl_artifact_source.source_id` (hit by the staleness EXISTS in `carl_artifact_view`, `V1__carl_domain.sql:197`) - `carl_correction.record_id`, `carl_client_workflow.requester_id`, `carl_expense_settlement.expense_id`, `carl_balance_source.import_id`, `carl_record.created_at` Permission views are three to four levels deep with correlated EXISTS checks, and imports scan with `LIMIT 100001`. The foundation contract asks for indexes on actual filters. The data volume already tested (10,920 transactions, 22,365 balances) is small enough to hide this. ## Suggested resolution Add a migration with the indexes above, then measure the dashboard and report queries with `EXPLAIN ANALYZE` on the public-household fixture at ten times its current size. ## Priority rationale P2: correctness is fine; performance will degrade with real multi-year data. --- Filed from the 2026-10-01 real-versus-fake audit of Carl (branch `feature/cicd-liquibase-gitops`, HEAD `5414d7a`, main `1b334d1`). Four read-only reviews covered domain and services, QQQ metadata and data model, tests/CI/evidence, and docs/integrations. A local offline build of a Carl copy against the installed foundation 0.5.0-SNAPSHOT is in progress; its result will be added as a comment. Existing issues #3, #4 and #5 (real Monarch accounts, Authentik with real data, CI/CD with Liquibase and ArgoCD) are referenced rather than duplicated.

This repo is archived. You cannot comment on issues.

1 Participants

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: KofTwentyTwo/carl#14