Partner-wide / system-level Software Library entries (currently strictly per-org)

[bdunncompany]

Partner-wide / system-level Software Library entries (currently strictly per-org)

The pain

software_catalog.org_id is NOT NULL and RLS scopes every read/write to a single org. For an MSP partner with N organizations, every Software Library entry has to be manually created N times — once per org — even though the catalog content (vendor, download URL, silent-install args, SHA-256, file size) is identical across all of them.

The cost grows with the partner. A 5-org MSP curating 50 packages needs 250 catalog rows + their version rows; a 60-org MSP needs 3,000. Worse, every new org added later starts with an empty Software Library and has to have the entire catalog cloned into it before the operator can deploy anything to that org's devices. Every package update, URL refresh, or silent-args tweak becomes an N-place maintenance problem.

This mirrors a gap that already exists in the scripts table — and which Breeze partially solved there with the is_system flag, but with an unfixed-upstream RLS hole that makes is_system=true rows invisible to non-system users (see "Note on the scripts equivalent" below).

Proposal — mirror the scripts pattern, fix the RLS hole at the same time

Schema:

-- 1. Allow null org_id for system-level rows
ALTER TABLE software_catalog ALTER COLUMN org_id DROP NOT NULL;

-- 2. is_system flag, defaulting to current per-org behavior
ALTER TABLE software_catalog
  ADD COLUMN IF NOT EXISTS is_system boolean NOT NULL DEFAULT false;

-- 3. Invariant: a row is either org-scoped or system-scoped, never both / neither
ALTER TABLE software_catalog
  ADD CONSTRAINT software_catalog_org_or_system_chk
  CHECK ((org_id IS NULL) = (is_system = true));

-- 4. RLS: keep org isolation, but make system rows readable to everyone
DROP POLICY IF EXISTS breeze_org_isolation_select ON software_catalog;
CREATE POLICY breeze_org_isolation_select ON software_catalog
  FOR SELECT USING (
    is_system = true OR breeze_has_org_access(org_id)
  );
-- INSERT/UPDATE/DELETE policies unchanged: still require breeze_has_org_access(org_id),
-- so partner/org users still can't write system rows; system-scope handlers bypass RLS.

Routes (apps/api/src/routes/software.ts):

• GET /catalog: optional includeSystem=true query param; when set, the WHERE clause becomes (software_catalog.org_id = :orgId OR software_catalog.is_system = true). Default behavior unchanged for backwards compat.
• POST /catalog: only auth.scope === 'system' may set isSystem: true. When isSystem: true, org_id is forced to NULL.
• PUT /catalog/:id, DELETE /catalog/:id: only system scope can mutate system rows.

Versions follow the catalog. software_versions.catalog_id already FK's to a UUID, no changes needed — a system catalog row's versions are visible to anyone who can see the catalog row.

Inventory matching (software_inventory.catalog_id) keeps working: when a device's installed software matches a system catalog row by name+vendor, the inventory row gets the system catalog id. Inventory's own RLS is by device_id / org_id, so a Tucker device sees its own inventory pointing at a system row that everyone-in-the-partner sees.

Deployments (software_deployments) keep working: a deployment record is org-scoped (software_deployments.org_id), and references a software_version_id whose catalog row may now be either org-scoped or system. FK is still satisfied; the deployer just gets a longer list of versions to choose from.

Note on the scripts equivalent (already partially shipped, with a gap)

scripts.is_system already exists upstream, and apps/api/src/routes/scripts.ts already filters with or(eq(scripts.orgId, query.orgId), eq(scripts.isSystem, true)) when includeSystem=true. But the SELECT RLS policy on scripts is breeze_has_org_access(org_id), which evaluates to false for org_id IS NULL — so partner / org users can never see is_system=true scripts under upstream RLS. The handler-level OR is dead-letter behind that policy.

Self-hosted operators currently have to add a permissive scripts_system_visible_to_all policy themselves. Folding that fix into the same upstream PR (or as a sibling commit) would close that hole at the same time.

Why this over per-org replication

Per-org replication works as a stop-gap, but the maintenance cost is O(orgs × packages) and every newly-onboarded org starts at zero until somebody re-runs the cloning script. A NULL-org system row is one place; new orgs inherit it the moment they're created.

Backwards compat

• Existing rows continue to have org_id NOT NULL semantics (the CHECK constraint enforces it).
• GET /catalog without includeSystem=true returns exactly today's result set.
• POST /catalog without isSystem: true is unchanged.
• No data migration is needed for existing deployments.

Scope of PR

Roughly:

• 1 migration file (~30 lines, idempotent)
• 1 schema change in software.ts (~3 lines)
• ~30 lines of route changes for INSERT/UPDATE/DELETE/GET
• Vitest coverage for: system-scope can create, partner-scope cannot, partner-scope sees system rows when includeSystem, partner-scope cannot mutate system rows
• A sibling commit (or separate PR) closing the scripts RLS hole the same way

I have a complete patch drafted locally; happy to PR if there's interest. Wanted to surface as a Discussion first since this has an opinionated design choice (NULL-org vs. partner-scoped).

Alternative considered: partner-scoped catalog

Add a partner_id column instead of is_system, with a "visible to anyone in this partner" semantic. More fine-grained for SaaS Breeze, less useful for self-hosted single-partner deployments. The is_system mirror is simpler and matches existing precedent. Could be a v2.

[ToddHebebrand]

Real pain, agreed — N-org MSPs hitting "clone every package per org" is exactly the kind of friction we don't want in the Software Library.

Want to call out the RLS shape carefully before you start, since this is the part that's easy to get wrong:

On the proposed SELECT policy. Allowing is_system = true OR breeze_has_org_access(org_id) is the right read shape, but the INSERT/UPDATE/DELETE policies need to be explicit about system-row writes. Today every tenant-scoped table is FORCE RLS against breeze_app, so handlers can't "bypass RLS" the way the proposal phrases it — they have to either run in a context where the policy permits the write, or use withSystemDbAccessContext (which sets a session var that the policies allow). The pattern we use for scripts.is_system writes is withSystemDbAccessContext from a system-scope route handler. Worth mirroring exactly.

On the scripts RLS hole. Good catch — if scripts.is_system = true rows are invisible to non-system users, that's a bug that wants its own fix-forward migration. Probably worth landing that fix first (or in the same PR) so we don't ship a second copy of the same pattern with the same hole. Happy to take that as either:

• A separate PR that lands first, then this one mirrors the now-correct pattern, OR
• One bigger PR that fixes both surfaces at once.

Your call — I'd lean toward the first since it's easier to review.

On (org_id IS NULL) = (is_system = true). Nice invariant. Make sure the migration also drops/recreates the org_id FK as ON DELETE-still-valid-for-NULL (it is by default, just flagging).

On software_versions. Confirmed — no changes needed there, the FK chain works.

Allowlist update. software_catalog is already on the Shape 1 (direct org_id) auto-discovered path. After this change it becomes a hybrid (Shape 1 + system rows), so the rls-coverage.integration.test.ts allowlists may need adjustment. Worth running that test locally before opening the PR.

PR welcome once we've decided how to sequence the scripts fix.

[bdunncompany]

Thanks, Todd. Taking your suggestion — going Path 1: a focused scripts.is_system RLS-fix PR first, then the software_catalog system-rows PR mirrors the now-correct pattern.

Verified the hole on current main:

• SELECT policy on scripts is the baseline shape from apps/api/migrations/0001-baseline.sql:17710:
  breeze_org_isolation_select ... USING (breeze_has_org_access(org_id))
• breeze_has_org_access(NULL) returns FALSE for every non-system scope (function body in 0001-baseline.sql:1663). So is_system=true rows stored with org_id=NULL are invisible to partner-scope and org-scope readers.
• No later migration (2026-04-11 roles-rls-fix, 2026-05-03 permission-scope-hardening, others scanned) has touched the scripts SELECT policy.

Plan for the fix PR, sanity-checking the shape before opening:

1. Failing test added to apps/api/src/__tests__/integration/rls-coverage.integration.test.ts (file confirmed present, 37 KB on main) — partner-scope context reads /scripts, asserts a known is_system=true row is in the result; without the fix, fails.
2. New migration replaces the scripts SELECT policy with USING (is_system = true OR breeze_has_org_access(org_id)). Idempotent shape (DROP POLICY IF EXISTS / CREATE POLICY) per the migrations workflow.
3. INSERT/UPDATE/DELETE policies stay org-scoped; system-row writes continue via withSystemDbAccessContext from system-scope handlers (the pattern you spelled out).
4. Update rls-coverage.integration.test.ts allowlist entries for scripts to reflect the now-hybrid Shape (Shape 1 + system rows).

Quick clarifying question before I write the migration: do you want the same idempotency dance you've used elsewhere (pg_policies exists check before CREATE)? Or is straight DROP IF EXISTS + CREATE fine on scripts since it's a known-named policy?

If reproduction holds and you confirm the migration idempotency preference, I'll open it as a tight stand-alone PR — no scope creep into the software_catalog work.

[ToddHebebrand]

On idempotency: straight DROP POLICY IF EXISTS … ; CREATE POLICY … is fine — no pg_policies existence-check / DO $$ … EXCEPTION dance needed. For your same-name replace that's:

DROP POLICY IF EXISTS breeze_org_isolation_select ON scripts;
CREATE POLICY breeze_org_isolation_select ON scripts
  FOR SELECT USING (is_system = true OR breeze_has_org_access(org_id));

Re-running converges to the same final state, so it's idempotent in the sense the workflow requires. The pg_policies existence-check pattern is reserved for cases where an unconditional drop isn't safe (additive policies you don't want to clobber, conditional column/constraint adds) — a deterministic known-named policy replace isn't one of those.

Precedent for the approach is apps/api/migrations/2026-04-11-roles-rls-fix.sql: it swapped the roles SELECT policy using exactly plain DROP POLICY IF EXISTS … ; CREATE POLICY …, no existence-check guard, and its header is literally annotated "Fully idempotent." One difference to be aware of — that migration renamed the policy (dropped breeze_org_isolation_select, created breeze_role_isolation_select); yours keeps the same name. The idempotency approach is identical either way, so it's a fine reference for the pattern but not a line-for-line template.

One caveat so you don't copy it verbatim: roles-rls-fix.sql wraps its statements in an inner BEGIN;/COMMIT;. That predates the current rule — autoMigrate already wraps each migration file in a transaction, so an inner BEGIN;/COMMIT; just emits NOTICE: there is already a transaction in progress. Omit it (you already referenced the migrations workflow, so you likely know — flagging because the reference file violates it).

Also confirmed your reproduction independently: the scripts SELECT policy is breeze_org_isolation_select at 0001-baseline.sql:17710 (USING (public.breeze_has_org_access(org_id))), and no later migration touches it — the hole is real on current main.

Sequence and plan are right (failing rls-coverage test first, policy-replacement migration, INSERT/UPDATE/DELETE unchanged with system writes via withSystemDbAccessContext, allowlist update for the now-hybrid Shape). Open it as the tight standalone PR.

[bdunncompany]

Draft PR up at #715. Path 1 — tight standalone scripts.is_system SELECT-policy fix per the sequence and idempotency you confirmed:

• Migration: plain DROP POLICY IF EXISTS + CREATE POLICY with the same name. No inner BEGIN/COMMIT (autoMigrate already wraps).
• INSERT/UPDATE/DELETE policies unchanged.
• Integration test in apps/api/src/tests/integration/scripts-system-rls.integration.test.ts mirrors the audit-logs-rls.integration.test.ts shape: partner-scope read of an is_system row (fails on main, passes with migration), org-scope read of the same row, and a partner-scope INSERT attempt that asserts RLS still rejects writes.
• rls-coverage.integration.test.ts allowlist: no edit required — scripts stays Shape 1 (the new policy still references breeze_has_org_access in the OR branch, so the semantic check passes).
• Integration test was not run locally (test postgres container is not up on my build host); relying on CI smoke-test to exercise. tsc clean on apps/api.

The software_catalog system-rows PR will mirror this corrected pattern after this one lands.

[ToddHebebrand]

PR #715 merged. Greenlight on the software_catalog mirror PR using the same pattern — plain DROP/CREATE POLICY in a fresh migration, integration test mirroring scripts-system-rls.integration.test.ts, no rls-coverage.integration.test.ts allowlist edit needed (Shape 1 with system-row OR branch stays valid). Ping me when the draft is up.