## 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 a **partial index** for public content and a **composite index** for tenant content: ```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 indexes via a **bitmap OR scan**, which is efficient. ### Drizzle ORM Drizzle supports this cleanly with `or()` + `isNull()`: ```ts import { or, eq, isNull } from 'drizzle-orm'; const rows = await db.select().from(questions).where( or( eq(questions.tenantId, tenantId), isNull(questions.tenantId) ) ); ``` No raw SQL needed. ### Relevance ranking If tenant content should rank above public content when both match, add a tiebreaker to your ORDER BY: ```sql ORDER BY (tenant_id = $1)::int DESC, -- tenant content first ts_rank(...) DESC -- then by relevance ``` In Drizzle you can do this with `sql\`(${questions.tenantId} = ${tenantId})::int\`` as an expression column. ### Summary | Concern | Recommendation | |---|---| | Index | Two partial indexes (public / tenant) — bitmap OR scan | | ORM | `or(eq(...), isNull(...))` | | Ranking | Add tenant-match boolean as first ORDER BY term |

0ab39d4d-49b2-4c04-ac8c-265d1d708189