{"record":{"id":"a621152cbf1589e3","repo":"clockworklabs/SpacetimeDB","slug":"subscriptions-require-indexes-on-join-columns","errorCode":null,"errorMessage":"Subscriptions require indexes on join columns","messagePattern":"Subscriptions require indexes on join columns","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/subscription/src/lib.rs","lineNumber":649,"sourceCode":"                    alias,\n                ) => {\n                    table_aliases.push(*alias);\n                    table_ids.push(schema.table_id);\n                }\n                _ => {}\n            });\n            (table_ids, table_aliases)\n        }\n\n        let mut subscriptions = vec![];\n        let mut physical_plans = vec![];\n        let params = ExecutionParams::from_auth(auth);\n\n        for plan in plans {\n            let plan_opt = plan.clone().optimize()?;\n\n            if has_non_index_join(&plan_opt) {\n                bail!(\"Subscriptions require indexes on join columns\")\n            }\n\n            if plan_opt.reads_from_event_table() {\n                bail!(\"Event tables cannot be used as the lookup table in subscription joins\")\n            }\n\n            let (table_ids, table_aliases) = table_ids_for_plan(&plan);\n\n            let fragments = Fragments::compile_from_plan(&plan, &table_aliases)?;\n            let is_join = fragments.insert_plans.len() > 1 && fragments.delete_plans.len() > 1;\n\n            let mut view_ids = HashSet::new();\n            plan_opt.collect_views(&mut view_ids);\n\n            let metadata = SubscriptionMetadata {\n                table_ids,\n                return_schema: plan_opt.return_table(),\n                view_ids: view_ids.into_iter().collect(),","sourceCodeStart":631,"sourceCodeEnd":667,"githubUrl":"https://github.com/clockworklabs/SpacetimeDB/blob/6dee26c6efc2856793e12b148a59742964f5d783/crates/subscription/src/lib.rs#L631-L667","documentation":"Subscription evaluation replays row deltas through join plans incrementally, which requires index lookups on the join columns. After optimizing the plan, the compiler rejects it via has_non_index_join when any join column lacks an index, because indexless joins cannot be evaluated per-delta at acceptable cost.","triggerScenarios":"Subscribing to a query like SELECT a.* FROM a JOIN b ON a.user_id = b.id where the join column(s) on the probing side (a.user_id) have no index, unique constraint, or primary-key coverage.","commonSituations":"Adding join subscriptions over newly added foreign-key-like columns without declaring #[index]; joining on plain data columns; schema evolved to add the column but the index attribute was forgotten.","solutions":["Declare an index on the join column in the table definition and re-publish: #[spacetimedb::index] on the field, or #[unique]/primary key where uniqueness holds.","Join on the side that is already indexed (e.g. the primary key of the other table) and adjust the query shape accordingly."],"exampleFix":"// before\n#[spacetimedb::table(name = player)]\npub struct Player {\n    #[primary_key]\n    pub id: u64,\n    pub guild_id: u64, // no index -> join subscription fails\n}\n\n// after\n#[spacetimedb::table(name = player)]\npub struct Player {\n    #[primary_key]\n    pub id: u64,\n    #[spacetimedb::index]\n    pub guild_id: u64,\n}","handlingStrategy":"validation","validationCode":"-- verify join columns are indexed before subscribing:\nspacetime describe my-db\n-- every column used in a subscription JOIN ... ON must appear as an index,\n-- unique constraint, or primary key in the table schema","typeGuard":null,"tryCatchPattern":"try {\n  await db.subscription.build([\"SELECT a.* FROM a JOIN b ON a.b_id = b.id\"]).subscribe();\n} catch (e: any) {\n  if (String(e.message).includes(\"require indexes on join columns\")) {\n    // add #[spacetimedb::index] on a.b_id, republish, resubscribe\n  }\n}","preventionTips":["Declare #[spacetimedb::index] on every column you plan to join on.","Review join subscriptions against spacetime describe output before shipping.","Treat foreign-key-like columns as indexed by convention in table definitions."],"tags":["subscription","join","indexes","query-planning"],"backgroundTag":"missing-database-index","analyzedSha":"6dee26c6efc2856793e12b148a59742964f5d783","analyzedAt":"2026-08-20T06:08:37.179Z","contentChangedAt":"2026-08-20T06:08:37.179Z","schemaVersion":2},"datasetVersion":"2026-09-14T05:17:10.506Z"}