<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/engineering/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=624518) </details> <!--IssueSummary end--> ## Summary An audit of all 106 `gitlab_main_org` tables currently declaring `organization_id` as their sole sharding key found 11 tables (across 9 features) where `organization_id` is the wrong *type* of sharding key — the data is actually owned by a namespace, project, or (in one case) two structurally different ownership types at once. Fixing these requires a different sharding key (or, in a couple of cases, a schema change), not just better code to populate the existing column. This is a companion to a separate issue covering tables where `organization_id` may be the right kind of key in principle, but the current code populates it incorrectly (a data-population bug, not a misclassification). Per [`doc/development/organization/sharding/_index.md`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/doc/development/organization/sharding/_index.md#using-organization_id-as-sharding-key), `organization_id` as a sole sharding key is only valid for genuinely root-level models, and "must not contain data related to groups or projects — instead use `project_id`, or `namespace_id`." ## Wrong `gitlab_schema` vs. wrong `sharding_key` These 11 tables split into two structurally different problems, worth tracking separately since the fix shape differs: **Wrong schema (2, plus a split case)** — the data can't be consistently attributed to a single organization at all under the current model, which is a `gitlab_main_org` prerequisite ("must be able to consistently attribute each row to an organization"). Fixing these needs the underlying association/data model rethought, not just a different column. - `jira_connect_installations` — an installation's subscriptions can span namespaces in different organizations. - `keys` (`DeployKey`) — one DeployKey can attach to projects in different organizations via the join table. - `scim_oauth_access_tokens` *(split case)* — the instance-level half of this table (tokens not tied to any group) doesn't obviously belong to one organization either. **Wrong key, schema is fine (8, plus scim's other half)** — `gitlab_main_org` is the correct schema, but `organization_id` is the wrong column; the real owner is a namespace/project, which is still valid within `gitlab_main_org`, it just needs the more specific key. - `scim_oauth_access_tokens` *(group-scoped tokens)* — belong to a specific group; should key off `group_id`/`namespace_id`. - `ai_catalog_mcp_server_blocks`, ~~`ai_events_counts`~~, `enabled_foundational_flow_check_results`, `granular_scopes` — each has a real `namespace_id` on the row already. - ~~`custom_dashboards`~~, `custom_dashboard_versions`, `custom_dashboard_search_data` — dashboards can be namespace-owned; children need the `namespace_id` column added. - `subscription_seat_assignments` — model requires `namespace`; DB just doesn't enforce it as `NOT NULL` yet. ## Tables and evidence 1. **jira_connect_installations** (`JiraConnectInstallation`) — `has_many :subscriptions`; `JiraConnectSubscription` (`app/models/jira_connect_subscription.rb`) `belongs_to :namespace` with no constraint tying that namespace to the installation's organization. A single installation can legitimately span multiple organizations — there is no single correct `organization_id` to assign, ever, under the current data model. 2. **keys** (`DeployKey` specifically, within the shared `keys` table) — `app/models/deploy_key.rb`'s `ghost_user_project` infers the organization from "the first available project" a DeployKey happens to be attached to via the `deploy_keys_projects` join table, even though one DeployKey can be attached to projects in different organizations. Same structural problem as above — no single-org answer exists. 3. **scim_oauth_access_tokens** — the model (`ee/app/models/scim_oauth_access_token.rb`) serves two different ownership grains in one table: group-scoped SCIM tokens (`belongs_to :group, optional: true`) and instance-level tokens (`validates :organization_id, presence: true, on: :create, if: -> { group_id.blank? }`). The sharding key only accounts for the instance-level case. This needs either a table split or a second (conditional) sharding key, not better derivation logic. 4. **ai_catalog_mcp_server_blocks** — `belongs_to :namespace, optional: false` is the row's real identity (uniqueness is scoped by `namespace_id` + `ai_catalog_mcp_server_id`); the table's own YAML description literally calls these "Group-level block records." `organization_id` is just a denormalized copy from the parent MCP server. 5. ~~**ai_events_counts** — has a real `belongs_to :namespace, optional: true`, and its primary query interface (`for_namespace` scope, `total_occurrences_for`) is namespace-keyed, but the sharding key only declares `organization_id`.~~ 6. **enabled_foundational_flow_check_results** — its own immediate parent table, `Ai::Catalog::EnabledFoundationalFlow`, is already correctly sharded by a `namespace_id`/`project_id` multi-column key (mutually exclusive, enforced by a `belongs_to_namespace_or_project` validation). This child table inexplicably declares `organization_id` instead of inheriting the parent's already-correct key. 7. ~~**granular_scopes** — has a real, usually-non-null `belongs_to :namespace` (nil only for user/instance-standalone scopes) plus an `organization_match` validation. Same shape as `labels`/`notes`/`todos`, which GitLab already handles correctly via a multi-column `{namespace_id, organization_id}` sharding key with a not-null check constraint.~~ 8. ~~**custom_dashboards, custom_dashboard_versions, custom_dashboard_search_data** — `Dashboard` (`ee/app/models/analytics/custom_dashboards/dashboard.rb`) has both `belongs_to :namespace, optional: true` and `belongs_to :organization, optional: false`, with explicit `org_scoped`/`namespace_scoped` query scopes — dashboards can genuinely be namespace-owned. Verified directly: the two child tables (`custom_dashboard_versions`, `custom_dashboard_search_data`) don't even have a `namespace_id` column in `db/structure.sql` today — only `custom_dashboard_id` (FK to the dashboard) and `organization_id`. Fixing this needs a schema change on the children, not just a different YAML declaration.~~ 9. **subscription_seat_assignments** — the model (`ee/app/models/gitlab_subscriptions/seat_assignment.rb`) declares `belongs_to :namespace, optional: false` and the feature is explicitly namespace-scoped ("seat assignment information and activity within a namespace" per its own description), but the database schema only enforces `organization_id NOT NULL`, not `namespace_id NOT NULL` — a schema/model mismatch likely explaining why `organization_id` was picked. ## Suggested next steps - Route each table to its feature-owning team (labels above are a starting point) with Tenant Scale support. - The "wrong key, schema is fine" group (`ai_catalog_mcp_server_blocks`, `ai_events_counts`, `enabled_foundational_flow_check_results`, `granular_scopes`, scim's group-scoped tokens) can likely reuse the existing `{namespace_id, organization_id}` multi-column pattern already proven on `labels`/`notes`/`todos` — lowest-effort of this group. - `custom_dashboards` (+2 children) and `subscription_seat_assignments` need a schema change (new column and/or a `NOT NULL` constraint) before the key can be corrected, but stay within `gitlab_main_org`. - The "wrong schema" group (`jira_connect_installations`, `keys`/DeployKey, and scim's instance-level tokens) is the hardest: each needs the underlying feature/data model reconsidered — whether the cross-org relationship should be allowed at all, or restructured — before any sharding key or schema can be assigned correctly.