fix: update build system RLS policies for consistency by WcaleNieWolny · Pull Request #1404 · Cap-go/capgo

WcaleNieWolny · 2026-01-09T14:59:54Z

Summary

Update RLS policies for build_requests, build_logs, and daily_build_time tables to use consistent patterns
Use get_identity_org_appid() when app_id is available (preferred)
Use get_identity_org_allowed() only when app_id is unavailable (fallback)
Add support for both authenticated and anon roles to enable API key authentication

Motivation

The native build system RLS policies were inconsistent with the rest of the codebase. They used direct EXISTS queries with auth.uid() instead of the standard check_min_rights() and get_identity_org_appid() functions used across other tables like apps, channels, and webhooks.

This inconsistency caused issues with:

API key authentication not working for build system endpoints
Missing org/app-level API key restriction validation
Harder maintenance due to non-standard patterns

Business Impact

Revenue Generation: A working native build system is essential for Capgo's growth. By fixing the RLS policies:

Users can now properly access build requests and build logs through both JWT and API key authentication
The build system becomes reliable and consistent, improving user experience
A smooth build experience reduces churn and increases customer satisfaction
Enables Capgo to offer native builds as a premium feature that drives subscriptions

Test Plan

Verify authenticated users can read their org's build requests
Verify API key authentication works for build_requests endpoint
Verify build_logs are accessible with proper org membership
Verify daily_build_time data is accessible for apps the user has access to
Run supabase db reset to apply migration locally

🤖 Generated with Claude Code

Summary by CodeRabbit

Refactor
- Standardized database access control policies to improve consistency and reliability across backend systems.

_{✏️ Tip: You can customize this high-level summary in your review settings.}

Update RLS policies for build_requests, build_logs, and daily_build_time tables to use the consistent pattern used across the codebase: - Use check_min_rights() instead of direct EXISTS queries - Use get_identity_org_appid() when app_id is available (preferred) - Use get_identity_org_allowed() only when app_id is unavailable (fallback) - Support both authenticated and anon roles for API key auth This fixes the native build system by ensuring proper access control that matches the patterns used in apps, channels, and app_versions tables. Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>

coderabbitai · 2026-01-09T15:00:07Z

📝 Walkthrough

Walkthrough

This SQL migration standardizes Row Level Security policies across three tables—build_requests, build_logs, and daily_build_time—by replacing direct EXISTS-based access checks with a unified pattern using check_min_rights and identity helper functions. Policies are recreated to support both authenticated and anon roles.

Changes

Cohort / File(s)	Summary
RLS Policy Standardization `supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql`	Drops and recreates RLS policies for three tables: `build_requests` (using `get_identity_org_appid` with app_id), `build_logs` (using `get_identity_org_allowed` with org_id), and `daily_build_time` (joining with apps table to derive owner_org). All policies apply `check_min_rights` for read-level access control and support both authenticated and anon roles.

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~20 minutes

Possibly related PRs

fix: support hashed API keys in RLS identity functions #1351: Directly consumes the get_identity_org_appid and get_identity_org_allowed identity helper functions that are central to this RLS policy refactoring.

Poem

🐰 Hopping through the database with glee,
Our RLS policies now all agree!
Three tables unified, access control tight,
Identity helpers shining so bright. ✨

🚥 Pre-merge checks | ✅ 3

✅ Passed checks (3 passed)

Check name	Status	Explanation
Title check	✅ Passed	The title 'fix: update build system RLS policies for consistency' accurately summarizes the primary change—standardizing RLS policies across build system tables.
Description check	✅ Passed	The PR description provides a comprehensive summary, clear motivation, business impact, and test plan; however, the test plan items are incomplete (unchecked boxes with no execution details).
Docstring Coverage	✅ Passed	No functions found in the changed files to evaluate docstring coverage. Skipping docstring coverage check.

_{✏️ Tip: You can configure your own custom pre-merge checks in the settings.}

📜 Recent review details

Configuration used: defaults

Review profile: CHILL

Plan: Pro

📥 Commits

Reviewing files that changed from the base of the PR and between a12769f and debfe47.

📒 Files selected for processing (1)

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

🧰 Additional context used

📓 Path-based instructions (1)

supabase/migrations/**/*.sql

📄 CodeRabbit inference engine (.github/copilot-instructions.md)

Database migrations must be created with supabase migration new <feature_slug> and never modify previously committed migrations

supabase/migrations/**/*.sql: When a feature requires schema changes, create a single migration file with the Supabase CLI (supabase migration new <feature_slug>) and keep editing that file until the feature ships; never edit previously committed migrations.
A migration that introduces a new table may include seed inserts for that table, but treat that seeding as part of the current feature and do not modify previously committed migrations.
Do not create new cron jobs; instead update the process_all_cron_tasks function in a new migration file to add your job if needed.

Files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

🧠 Learnings (9)

📓 Common learnings

Learnt from: WcaleNieWolny
Repo: Cap-go/capgo PR: 1300
File: supabase/migrations/20251224103713_2fa_enforcement.sql:409-539
Timestamp: 2025-12-24T14:11:10.256Z
Learning: In supabase/migrations for get_orgs_v6 and get_orgs_v7: The inner functions with user_id parameter (get_orgs_v6(uuid) and get_orgs_v7(uuid)) should NOT be granted to anon/authenticated roles as this allows any user to query other users' organizations; only the no-argument wrapper functions should be public as they perform authentication checks.

📚 Learning: 2025-12-24T14:11:10.256Z

Learnt from: WcaleNieWolny
Repo: Cap-go/capgo PR: 1300
File: supabase/migrations/20251224103713_2fa_enforcement.sql:409-539
Timestamp: 2025-12-24T14:11:10.256Z
Learning: In supabase/migrations for get_orgs_v6 and get_orgs_v7: The inner functions with user_id parameter (get_orgs_v6(uuid) and get_orgs_v7(uuid)) should NOT be granted to anon/authenticated roles as this allows any user to query other users' organizations; only the no-argument wrapper functions should be public as they perform authentication checks.

Applied to files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

📚 Learning: 2026-01-08T00:40:00.524Z

Learnt from: CR
Repo: Cap-go/capgo PR: 0
File: AGENTS.md:0-0
Timestamp: 2026-01-08T00:40:00.524Z
Learning: Applies to supabase/migrations/**/*.sql : A migration that introduces a new table may include seed inserts for that table, but treat that seeding as part of the current feature and do not modify previously committed migrations.

Applied to files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

📚 Learning: 2026-01-08T00:40:00.524Z

Learnt from: CR
Repo: Cap-go/capgo PR: 0
File: AGENTS.md:0-0
Timestamp: 2026-01-08T00:40:00.524Z
Learning: Applies to supabase/seed.sql : Updating `supabase/seed.sql` to back new or evolved tests is expected; keep fixtures focused on current behavior while leaving committed migrations unchanged.

Applied to files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

📚 Learning: 2026-01-08T00:40:00.524Z

Learnt from: CR
Repo: Cap-go/capgo PR: 0
File: AGENTS.md:0-0
Timestamp: 2026-01-08T00:40:00.524Z
Learning: Applies to supabase/migrations/**/*.sql : Do not create new cron jobs; instead update the `process_all_cron_tasks` function in a new migration file to add your job if needed.

Applied to files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

📚 Learning: 2025-12-23T02:53:12.055Z

Learnt from: CR
Repo: Cap-go/capgo PR: 0
File: .github/copilot-instructions.md:0-0
Timestamp: 2025-12-23T02:53:12.055Z
Learning: Applies to supabase/migrations/**/*.sql : Database migrations must be created with `supabase migration new <feature_slug>` and never modify previously committed migrations

Applied to files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

📚 Learning: 2026-01-08T00:40:00.524Z

Learnt from: CR
Repo: Cap-go/capgo PR: 0
File: AGENTS.md:0-0
Timestamp: 2026-01-08T00:40:00.524Z
Learning: Applies to supabase/migrations/**/*.sql : When a feature requires schema changes, create a single migration file with the Supabase CLI (`supabase migration new <feature_slug>`) and keep editing that file until the feature ships; never edit previously committed migrations.

Applied to files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

📚 Learning: 2025-12-23T02:53:12.055Z

Learnt from: CR
Repo: Cap-go/capgo PR: 0
File: .github/copilot-instructions.md:0-0
Timestamp: 2025-12-23T02:53:12.055Z
Learning: Applies to supabase/functions/_backend/**/*.{ts,js} : Use Drizzle ORM query patterns with `schema` from `postgress_schema.ts` for all database operations; use `aliasV2()` for self-joins or multiple table references

Applied to files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

📚 Learning: 2025-12-25T11:22:13.039Z

Learnt from: WcaleNieWolny
Repo: Cap-go/capgo PR: 1300
File: supabase/migrations/20251224103713_2fa_enforcement.sql:85-96
Timestamp: 2025-12-25T11:22:13.039Z
Learning: In SQL migrations under the repository (e.g., supabase/migrations), enforce that when an org has enforcing_2fa=true, all users (including super_admins) must have 2FA enabled before accessing any org functions, including check_org_members_2fa_enabled. Do not grant admin exceptions to 2FA requirements. This ensures consistent security enforcement across all org-related operations; implement this rule within relevant migrations and associated stored procedures/tests.

Applied to files:

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

⏰ Context from checks skipped due to timeout of 90000ms. You can increase the timeout in your CodeRabbit configuration to a maximum of 15 minutes (900000ms). (2)

GitHub Check: Run tests
GitHub Check: Analyze (java-kotlin)

🔇 Additional comments (4)

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql (4)

18-38: LGTM – build_requests policy uses the correct pattern.

The policy correctly uses get_identity_org_appid() since app_id is available in the table, and the check_min_rights() wrapper ensures consistent access control. The DROP POLICY IF EXISTS is safe and prevents migration failures if the old policy was already removed.

44-63: LGTM – build_logs policy correctly falls back to org-level check.

Since build_logs lacks an app_id column, using get_identity_org_allowed() as the fallback function is appropriate. The NULL::CHARACTER VARYING for app_id and NULL::BIGINT for version are correctly typed.

69-95: EXISTS join pattern is valid; apps.app_id indexing is implicit.

The policy correctly joins through apps to obtain owner_org, which is necessary since daily_build_time only has app_id. The EXISTS correlated subquery will perform efficiently because apps.app_id is the primary key of the apps table and therefore implicitly indexed (required to support the foreign key constraint from daily_build_time and other tables).

No changes needed.

1-12: Migration correctly applies consistent RLS policy patterns with proper function usage.

The helper function signatures match all call patterns in the migration:

check_min_rights(min_right, user_id, org_id, app_id, channel_id) is correctly invoked with UUIDs returned from the identity functions

get_identity_org_appid(keymode[], org_id, app_id) is used for build_requests and daily_build_time where app_id is available

get_identity_org_allowed(keymode[], org_id) is used for build_logs where only org_id is available

All three RLS policies correctly support both authenticated and anon roles and follow the established pattern across the codebase.

Warning

There were issues while running some tools. Please review the errors and either fix the tool's configuration or disable the tool if it's a critical failure.

🔧 SQLFluff (3.5.0)

supabase/migrations/20260109000000_fix_build_system_rls_consistency.sql

User Error: No dialect was specified. You must configure a dialect or specify one on the command line using --dialect after the command. Available dialects:
ansi, athena, bigquery, clickhouse, databricks, db2, doris, duckdb, exasol, flink, greenplum, hive, impala, mariadb, materialize, mysql, oracle, postgres, redshift, snowflake, soql, sparksql, sqlite, starrocks, teradata, trino, tsql, vertica

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

sonarqubecloud · 2026-01-09T15:05:48Z

Quality Gate passed

Issues
1 New issue
0 Accepted issues

Measures
0 Security Hotspots
0.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarQube Cloud

riderx merged commit c793c6b into main Jan 9, 2026
10 of 11 checks passed

riderx deleted the fix/build-system-rls-consistency branch January 9, 2026 16:04

This was referenced Jan 12, 2026

Improve usage credit RLS policies with get_identity_org_allowed #1423

Merged

feat: RBAC system v1 #1429

Merged

coderabbitai bot mentioned this pull request Jan 25, 2026

fix(rbac): use single auth.uid() call per policy #1496

Merged

4 tasks

coderabbitai bot mentioned this pull request Feb 6, 2026

fix(build): prevent cross-app job access #1590

Merged

5 tasks

coderabbitai bot mentioned this pull request Mar 3, 2026

fix(security): patch 9 vulnerabilities across SQL functions and edge endpoints #1735

Closed

4 tasks

This was referenced Mar 10, 2026

fix(rbac): allow app insert returning #1774

Closed

fix(db): scope manifest reads to owning org #1816

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

fix: update build system RLS policies for consistency#1404

fix: update build system RLS policies for consistency#1404
riderx merged 1 commit intomainfrom
fix/build-system-rls-consistency

WcaleNieWolny commented Jan 9, 2026 •

edited by coderabbitai bot

Loading

Uh oh!

coderabbitai bot commented Jan 9, 2026 •

edited

Loading

Walkthrough

Changes

Estimated code review effort

Possibly related PRs

Poem

Uh oh!

sonarqubecloud bot commented Jan 9, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

Conversation

WcaleNieWolny commented Jan 9, 2026 • edited by coderabbitai bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Motivation

Business Impact

Test Plan

Summary by CodeRabbit

Uh oh!

coderabbitai bot commented Jan 9, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Walkthrough

Changes

Estimated code review effort

Possibly related PRs

Poem

Uh oh!

sonarqubecloud bot commented Jan 9, 2026

Quality Gate passed

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

WcaleNieWolny commented Jan 9, 2026 •

edited by coderabbitai bot

Loading

coderabbitai bot commented Jan 9, 2026 •

edited

Loading