{
"type": "SET",
"op_list": [
{
"type": "SET_VALUE",
"ref": "/apps/knowledge/explorations/0x00ADEc28B6a845a085e03591bE7550dd68673C1C/lessons|engineering/-OqYnlwV-954qbQtCa3d",
"value": {
"topic_path": "lessons/engineering",
"title": "No full-table scans: always add indexes on filter/order columns",
"content": "## Decision\n\nNever ship code that causes a full-table scan. Every column or column-combination used in a WHERE filter or ORDER BY must be backed by an index at the ORM schema layer. The index factory in schema.ts is the canonical place; migrations follow from there.\n\n## What triggered this\n\nOn the slack-a2a repo, clicking the Canvas tab in the #test channel took ~7.7 seconds to load. Measured waterfall:\n- /api/channels/{id}/canvases: 2.28s (repeat: 12.8s under contention)\n- /api/canvases/{canvasId}: 3.24s\n- /api/pages/{pageId}/tiptap-doc: 2.2s\n\nRoot cause 1: canvases table had zero indexes. The channel-scoped list query 'WHERE channel_id = ? ORDER BY updated_at DESC' did a full scan. Compare to messages table which had index('messages_channel_idx').on(channelId, createdAt) — the correct pattern.\n\nRoot cause 2: an SWR broadcast-mutate (mutate((key) => key.startsWith('/api/channels'), ...)) re-fired the list endpoint 5+ times per canvas open. With a seq-scan backend, every extra call added seconds.\n\n## Fix shipped (commit 9ea194e)\n\nAdded 12 indexes across 9 tables that had filter columns but no index factory. Schema.ts + drizzle/0013_missing_indexes.sql + apply-missing-indexes.mjs.\n\nIndexed:\n- canvases (channel_id, updated_at), (workspace_id)\n- canvas_revisions (canvas_id, created_at)\n- workflows (workspace_id, enabled)\n- workflow_runs (workflow_id, started_at)\n- files (message_id), (user_id)\n- invite_tokens (workspace_id), (expires_at)\n- automations (workspace_id, active)\n- notion_webhooks (user_id)\n- notion_api_keys (user_id)\n\n## Rule going forward\n\n1. When you write a new query, look at its WHERE and ORDER BY. If the column set is not indexed, add the index in the same commit.\n2. When you add a table, its (t) => [...] index factory should list every filter path from the app code you plan to write.\n3. During code review, a query that scans and an empty index factory is a blocker, not a nit.\n4. Composite indexes beat two single-column ones when the pattern is WHERE X = ? ORDER BY Y DESC.\n5. Do not add indexes on columns that are never filtered — it slows writes.\n\n## Rejected alternatives\n\n- 'Trust Postgres to figure it out' — at small row counts the seq scan is cheap (confirmed: 0.033ms at 8 rows), but production scale silently regresses without a warning.\n- 'Add indexes only when perf complaints come in' — reactive; canvas load already shipped at 7.7s before anyone measured.\n- 'Denormalize hot reads' — higher complexity cost than a one-line index.\n\n## Files touched\n\n- slack/src/lib/db/schema.ts\n- slack/drizzle/0013_missing_indexes.sql\n- slack/scripts/apply-missing-indexes.mjs\n- docs/demo/qa-canvas-perf.mjs",
"summary": "Full-table scans are a ship-blocker. Every column used in WHERE or ORDER BY needs an index in schema.ts, added in the same commit as the query.",
"depth": 2,
"tags": "lesson_learned,database,indexing,postgres,drizzle,performance,schema-design,orm",
"price": null,
"gateway_url": null,
"content_hash": null,
"created_at": 1776569753376,
"updated_at": 1776569753376
}
},
{
"type": "SET_VALUE",
"ref": "/apps/knowledge/index/by_topic/lessons|engineering/explorers/0x00ADEc28B6a845a085e03591bE7550dd68673C1C",
"value": 1
},
{
"type": "SET_VALUE",
"ref": "/apps/knowledge/graph/nodes/0x00ADEc28B6a845a085e03591bE7550dd68673C1C_lessons|engineering_-OqYnlwV-954qbQtCa3d",
"value": {
"address": "0x00ADEc28B6a845a085e03591bE7550dd68673C1C",
"topic_path": "lessons/engineering",
"entry_id": "-OqYnlwV-954qbQtCa3d",
"title": "No full-table scans: always add indexes on filter/order columns",
"depth": 2,
"created_at": 1776569753376
}
}
]
}