# RLS Unification Design

## Status: DRAFT
## Date: 2026-04-04

---

## 1. Problem

Three independent RLS paths enforce different subsets of the access model:

| Path | Location | Covers | Missing |
|------|----------|--------|---------|
| A: `buildRLSFilter` | `query_service.go:746` | SuperUser, OWD, ViewAll, Owner, Hierarchy | ModifyAll, Group/Queue, Sharing Rules, Manual Share, Team |
| B: `applyRLSCondition` | `security_validator.go:181` | SuperUser, OWD, ViewAll, Owner, Hierarchy | ModifyAll, Group/Queue, Sharing Rules, Manual Share, Team |
| C: `CheckRecordAccess` | `record_access.go:15` | **All 7 layers** | — (authoritative) |

Additionally, 2 query paths have **zero RLS**:
- `executeSubQueries` (`query_service.go:329`) — calls `repo.Find` directly
- `RunAnalytics` (`query_service.go:564`) — only checks object permission

## 2. Target Architecture

```
                         ┌─────────────────────────────┐
                         │  PermissionService           │
                         │  .BuildRLSFilterExpr()       │  ← Single source of truth
                         │  Returns: (filterExpr string,│
                         │            skipRLS bool)     │
                         └──────────┬──────────────────┘
                                    │
                    ┌───────────────┼───────────────┐
                    │               │               │
              ┌─────▼─────┐  ┌─────▼─────┐  ┌─────▼─────┐
              │ Query()    │  │ Search()  │  │ Raw SQL   │
              │ Subquery() │  │           │  │ (AST path)│
              │ Analytics()│  │           │  │           │
              └────────────┘  └───────────┘  └───────────┘
              String filter    String filter   Parse filter
              → FilterExpr     → FilterExpr    → AST WHERE
```

### Key Design Decisions

1. **Single function, string output.** `BuildRLSFilterExpr` returns a SQL WHERE fragment as a string. This is consumable by both the query builder (append to FilterExpr) and the AST path (parse string into AST node).

2. **All 7 layers from CheckRecordAccess.** The filter must be the set-equivalent of the procedural CheckRecordAccess waterfall.

3. **Parameterized values.** All user-supplied IDs are escaped via `fmt.Sprintf("'%s'", strings.ReplaceAll(id, "'", "''"))` — defense-in-depth even though IDs are system-generated.

4. **CheckRecordAccess stays as-is.** The single-record path is already correct and complete. We don't touch it.

## 3. BuildRLSFilterExpr Specification

```go
// BuildRLSFilterExpr returns a SQL WHERE fragment that restricts query results
// to records the user can see. Returns ("", true) if no filtering needed.
// This is the SINGLE source of truth for query-level RLS — all query paths
// must call this function.
//
// Implements the same 7 access layers as CheckRecordAccess:
//   0. SuperUser bypass
//   1. OWD (PublicReadWrite / PublicRead)
//   2. ViewAllRecords / ModifyAllRecords
//   3. Ownership (self)
//   4. Group/Queue ownership
//   5. Role hierarchy (subordinates, if EnableHierarchySharing)
//   6. Sharing rules (criteria-less: all records; criteria-based: deferred)
//   7. Manual share + Team member (via _System_RecordShare + _System_TeamMember)
func (ps *PermissionService) BuildRLSFilterExpr(
    ctx context.Context,
    schema *models.ObjectMetadata,
    objectName string,
    user *models.UserSession,
) (filterExpr string, skipRLS bool)
```

### Output format

When `skipRLS = false`, `filterExpr` is a valid SQL WHERE fragment like:

```sql
(
  OwnerId IN ('user-id-1', 'user-id-2', 'user-id-3')
  OR OwnerId IN (SELECT gm.GroupId FROM GroupMember gm WHERE gm.UserOrGroupId = 'user-id-1')
  OR Id IN (SELECT rs.RecordId FROM _System_RecordShare rs WHERE rs.SobjectType = 'Account' AND rs.UserOrGroupId IN ('user-id-1', 'group-id-1'))
  OR Id IN (SELECT tm.RecordId FROM _System_TeamMember tm WHERE tm.SobjectType = 'Account' AND tm.UserId = 'user-id-1')
)
```

Sharing rules with empty criteria (share ALL records with a role) → the function returns `skipRLS = true` for that rule's target users (they see everything).

Criteria-based sharing rules are deferred to Phase 2 (requires formula-to-SQL translation). Documented as known gap.

## 4. Call Sites — What Changes

| Call Site | Current | After |
|-----------|---------|-------|
| `Query()` :67 | `buildRLSFilter()` | `ps.BuildRLSFilterExpr()` |
| `SearchSingleObject()` :492 | `buildRLSFilter()` | `ps.BuildRLSFilterExpr()` |
| `executeSubQueries()` :329 | **None** | `ps.BuildRLSFilterExpr()` |
| `RunAnalytics()` :564 | **None** | `ps.BuildRLSFilterExpr()` |
| `applyRLS()` in security_validator | `applyRLSCondition()` inline | Call `ps.BuildRLSFilterExpr()`, parse result string into AST |

## 5. What Gets Deleted

- `query_service.go:746-791` — entire `buildRLSFilter` method
- `security_validator.go:181-265` — entire `applyRLSCondition` method (replaced by calling BuildRLSFilterExpr + parsing)

## 6. Implementation

### File: `backend/internal/features/permissions/rls_filter.go` (NEW)

Single new file containing `BuildRLSFilterExpr`.

### File: `backend/internal/features/analytics/query_service.go`

- Delete `buildRLSFilter` (lines 746-791)
- Replace all 4 call sites with `qs.permissions.BuildRLSFilterExpr(ctx, schema, objectName, user)`
- Add RLS to `executeSubQueries` and `RunAnalytics`

### File: `backend/internal/features/analytics/security_validator.go`

- Delete `applyRLSCondition` (lines 181-265)
- In `applyRLS`, call `BuildRLSFilterExpr`, parse the result string as a SQL WHERE expression using TiDB parser, append to `stmt.Where`

## 7. Known Gaps (Deferred)

- **Criteria-based sharing rules**: Rules with `Criteria != ""` cannot be translated to SQL without a formula→SQL compiler. These rules work in `CheckRecordAccess` (per-record evaluation) but NOT in query-level RLS. Document as known limitation.
- **EnableHierarchySharing=false**: Objects with hierarchy sharing disabled should not include subordinate IDs. `BuildRLSFilterExpr` must check this flag.