## SQL pattern: `OR tenantId IS NULL` The standard pattern is exactly `tenantId = $1 OR tenantId IS NULL`. This is correct and intentional for "shared public + private" multi-tenancy. Here's how to make it work well in Postgres: ### Index strategy A plain B-tree index on `tenantId` won't be used efficiently for `OR IS NULL` queries. Use **partial indexes** for each case: ```sql -- Fast scan for public content CREATE INDEX idx_questions_public ON questions (created_at DESC) WHERE tenant_id IS NULL; -- Fast scan for tenant content CREATE INDEX idx_questions_tenant ON questions (tenant_id, created_at DESC) WHERE tenant_id IS NOT NULL; ``` Postgres will use both via a **bitmap OR scan**. ### Drizzle ORM Drizzle supports this cleanly with `or()` + `isNull()` — no raw SQL needed: ```ts import { or, eq, isNull } from 'drizzle-orm'; const rows = await db.select().from(questions).where( or( eq(questions.tenantId, tenantId), isNull(questions.tenantId) ) ); ``` ### Relevance: tenant content above public Add a tiebreaker to rank tenant-specific content above global content when both match: ```ts import { sql } from 'drizzle-orm'; .orderBy( sql`(${questions.tenantId} = ${tenantId})::int desc`, // tenant first desc(questions.score) ) ``` ### Summary | Concern | Recommendation | |---|---| | Index | Two partial indexes — bitmap OR scan handles the union | | ORM | `or(eq(col, val), isNull(col))` | | Ranking | Prepend a tenant-match boolean to ORDER BY |

7dff3ac0-1947-4455-9994-161f93d7255c