# NexusCRM Permission System -- Definitive Design

> **⚠️ HISTORICAL (archived 2026-04-23).** Pre-refactor design; the
> architecture below (`_System_ProfilePermission` as primary storage,
> 3-layer resolution, Go catalog, bitmap columns) was eliminated by
> PR #1831. Current architecture lives in
> `docs/design/permissions-metadata-refactor-outcome.md`. Kept for
> reasoning-chain context only.

**Status:** Approved Design (HISTORICAL — see banner)
**Date:** 2026-04-03
**Based on:** 8-agent deep research (`detailed-research.md`), SF model analysis (`salesforce-permission-model.md`), gap analysis (`README.md`)
**Scope:** Complete User & Access Management domain for NexusCRM (127 users, single-tenant, TiDB)

---

## 1. Design Principles

### SF's 5 Principles, Adapted for NexusCRM

| # | Principle | SF Implementation | NexusCRM Adaptation |
|---|-----------|-------------------|---------------------|
| 1 | **Default Deny** | OWD=Private baseline, additive-only grants | Same. OWD defaults to Private for new objects. Missing grant = visible error (user reports it). Missing restriction = silent data leak (nobody notices). |
| 2 | **Orthogonal 3 Dimensions** | Object / Field / Record checked independently | Same. All three must pass. Granting field access does not affect record visibility. |
| 3 | **Additive Model** | Permissions only stack upward; Muting is the sole subtraction (scoped to PSG) | Same. Profile UNION PermSets UNION PSG components MINUS Muting. Already implemented in `perm_system.go:67-122`. |
| 4 | **Declarative (Metadata, not Code)** | Permissions stored in metadata tables, configured at runtime | Same. All permission data lives in `_System_*` tables. No if/else permission logic in business code. |
| 5 | **Composition over Inheritance** | PermSet stacking (has-a), not role inheritance (is-a) | Same. PermSet-first model. Profile is the minimal baseline. PSGs bundle PermSets into personas. |

### What We Keep

- Full 4-layer system permission resolution: Profile -> PermSet columns -> PermSet rows -> PSG + Muting
- Object permission MAX() aggregation across Profile + PermSets
- Field permission MAX() aggregation with fallback
- Role hierarchy as a tree (single parent, deterministic ancestor chain)
- In-memory permission cache with TTL + singleflight coalescence
- Real-time record access computation (no materialized Share tables)

### What We Simplify

| SF Feature | Simplification | Rationale |
|------------|---------------|-----------|
| Share table materialization (`<Object>Share`) | Real-time subquery in SQL | 127 users; materialization adds async recalculation complexity for zero benefit |
| 364 system permissions | 60+ permissions | CRM-scoped; enterprise admin/platform perms unnecessary |
| 7+ user types | Standard only | No Community/Portal/Guest users |
| PermissionSet License (PSL) | Not applicable | Single-tenant; no license gating |
| External OWD (separate Portal OWD) | Skip | No external users |

### What We Skip (See Section 10)

Territory Management, Implicit Sharing, Session-based PermSets, SAML SSO, MFA, Delegated Administration, Login Hours/IP Ranges, Permission Dependencies, PermSet assignment expiration.

---

## 2. Data Model

### 2.1 User (Existing -- `User`)

**Status:** Exists. Minor changes needed.

| Field | Type | Notes |
|-------|------|-------|
| Id | VARCHAR(255) PK | |
| Username | VARCHAR(255) UNIQUE | |
| Email | VARCHAR(255) | |
| Password | VARCHAR(255) | bcrypt hash |
| FirstName | VARCHAR(255) | |
| LastName | VARCHAR(255) | |
| Phone | VARCHAR(255) | |
| ProfileId | VARCHAR(255) NOT NULL FK | -> Profile.Id |
| UserRoleId | VARCHAR(255) FK | -> UserRole.Id (nullable) |
| ManagerId | VARCHAR(255) FK | -> User.Id (self-ref, nullable) |
| Title | VARCHAR(255) | |
| Department | VARCHAR(255) | |
| Division | VARCHAR(255) | |
| IsActive | BOOLEAN DEFAULT true | |
| IsDeleted | BOOLEAN DEFAULT false | |
| OwnerId | VARCHAR(255) | |
| CreatedById | VARCHAR(255) | |
| LastModifiedById | VARCHAR(255) | |
| LastLoginDate | DATETIME | |
| CreatedDate | DATETIME | |
| LastModifiedDate | DATETIME | |

**Change needed:** When `IsActive` is set to `false`, all sessions for this user must be revoked (see Section 6).

### 2.2 Profile (Existing -- `Profile`)

**Status:** Exists. Controls 3 of SF's 8 categories (Object/Field/System perms). Sufficient for scope.

| Field | Type | Notes |
|-------|------|-------|
| Id | VARCHAR(255) PK | |
| Name | VARCHAR(255) UNIQUE | |
| Description | TEXT | |
| IsSystem | BOOLEAN | Locked profiles (system_admin, etc.) |
| IsDeleted | BOOLEAN | |

**Bootstrap profiles (5):** system_admin, standard_user, sales_user, deal_user, legal_user.

**What Profile controls in NexusCRM:**
1. Object permissions (via `_System_ProfilePermission` + profile-owned PermissionSet)
2. Field permissions (via profile-owned PermissionSet)
3. System permissions (via `_System_ProfilePermission`)
4. Page Layout assignment (via `_System_LayoutAssignment` -- Role x Profile x RecordType, exceeds SF)

**Not controlled by Profile (deferred):** Record Type access, App visibility defaults, Tab visibility, Login Hours/IP Ranges.

### 2.3 PermissionSet (Existing -- `PermissionSet`)

**Status:** Complete. Dual storage: boolean columns + normalized `_System_PermissionSetPermission` rows.

Key fields: `Id`, `Name`, `Label`, `Description`, `ProfileId` (nullable), `IsOwnedByProfile`, `IsDeleted`, plus 13 `Permissions*` boolean columns (ApiEnabled, ViewAllData, ModifyAllData, ManageUsers, ViewSetup, CustomizeApplication, RunReports, ExportReport, TransferAnyEntity, EmailSingle, EmailMass, ResetPasswords, DataExport).

**Assignment:** `PermissionSetAssignment` (AssigneeID -> User.Id, PermissionSetID -> PermissionSet.Id).

### 2.4 PermissionSetGroup + MutingPermissionSet (Existing Backend)

**Status:** Backend 100% complete. UI not registered in SetupRegistry.

| Table | Purpose | Key Fields |
|-------|---------|------------|
| `PermissionSetGroup` | Bundles PermSets into a persona | Id, MasterLabel, DeveloperName, Description |
| `PermissionSetGroupComponent` | Links PermSets to PSG | GroupID, PermissionSetID |
| `PermissionSetGroupAssignment` | Assigns PSG to User | GroupID, AssigneeID |
| `MutingPermissionSet` | 1-per-PSG subtraction | Id, GroupID (FK to PSG) |
| `_System_MutingPermissionSetPermission` | Muted permission rows | MutingPermissionSetID, PermissionAPIName |

**Muting scope:** Muting only affects permissions within its parent PSG. Cannot mute Profile-granted permissions or permissions from other PSGs.

### 2.5 ObjectPermissions (Existing -- `ObjectPermissions`)

| Field | Type | Notes |
|-------|------|-------|
| Id | VARCHAR(255) PK | |
| ParentId | VARCHAR(255) FK | -> PermissionSet.Id |
| SobjectType | VARCHAR(255) | Object API name |
| PermissionsRead | BOOLEAN | |
| PermissionsCreate | BOOLEAN | |
| PermissionsEdit | BOOLEAN | |
| PermissionsDelete | BOOLEAN | |
| PermissionsViewAllRecords | BOOLEAN | |
| PermissionsModifyAllRecords | BOOLEAN | |

**Resolution:** MAX() aggregation across all PermSets assigned to user (including profile-owned). Already implemented.

### 2.6 FieldPermissions (Existing -- `FieldPermissions`)

| Field | Type | Notes |
|-------|------|-------|
| Id | VARCHAR(255) PK | |
| ParentId | VARCHAR(255) FK | -> PermissionSet.Id |
| SobjectType | VARCHAR(255) | |
| Field | VARCHAR(255) | Field API name |
| PermissionsRead | BOOLEAN | |
| PermissionsEdit | BOOLEAN | |

**Resolution:** MAX() aggregation with fallback (if no explicit FLS entry, field is visible if object Read is granted). Already implemented.

### 2.7 SystemPermissions (Existing -- Dual Storage)

**Storage path 1:** Boolean columns on `PermissionSet` (e.g., `PermissionsAPIEnabled`).
**Storage path 2:** Normalized rows in `_System_ProfilePermission` (ProfileID, PermissionAPIName, IsEnabled) and `_System_PermissionSetPermission` (PermissionSetID, PermissionAPIName, IsEnabled).

Both paths are merged during resolution. The 4-layer resolution reads both.

### 2.8 UserRole + RoleHierarchy (Existing -- `UserRole`)

| Field | Type | Notes |
|-------|------|-------|
| Id | VARCHAR(255) PK | |
| Name | VARCHAR(255) | |
| DeveloperName | VARCHAR(255) | |
| ParentRoleId | VARCHAR(255) FK | -> UserRole.Id (nullable, tree structure) |
| RollupDescription | VARCHAR(255) | |

**Cache:** In-memory `map[roleID]*parentID`, refreshed on role changes. Ancestor traversal via `GetRoleAncestors()`. Located in `role_hierarchy.go`.

**Bootstrap:** 2 roles (sales_rep, sales_manager). Additional roles created by admins or migrated from SF.

### 2.9 OWD -- Organization-Wide Defaults (EXISTING METADATA, ENFORCEMENT NEW)

**No new table needed.** The `_System_ObjectMetadata` table already has:

| Field | Type | Current State |
|-------|------|---------------|
| `SharingModel` | VARCHAR(255) DEFAULT 'Private' | Enum defined in `enums.go:84-91`: `Private`, `PublicRead`, `PublicReadWrite`. Values are SET during bootstrap/discovery. **Not read by access control.** |
| `EnableHierarchySharing` | BOOLEAN | Exists in `ObjectMetadata` struct (`metadata.go:152`). Hardcoded to `false` in `metadata_scan_schema.go:70`. **Not read by access control.** |

**Design: Use existing fields, add enforcement.**

The `SharingModel` field on `_System_ObjectMetadata` becomes the authoritative OWD configuration. No migration needed -- values already exist.

`EnableHierarchySharing` (equivalent to SF's `GrantAccessUsingHierarchies`) controls whether role hierarchy grants R+E+D or is disabled for this object.

**Default values for new objects:**
- `SharingModel` = `'Private'` (secure default, consistent with SF)
- `EnableHierarchySharing` = `true` (consistent with SF standard object behavior)

**Typical production configuration:**

| Object | SharingModel | EnableHierarchySharing |
|--------|-------------|----------------------|
| Account | PublicRead | true |
| Contact | PublicRead | true |
| Opportunity | Private | true |
| Lead | Private | true |
| Case | Private | true |
| Custom objects | Private (default) | true (configurable) |

### 2.10 SharingRule (Existing -- `SharingRule`)

| Field | Type | Notes |
|-------|------|-------|
| Id | VARCHAR(255) PK | |
| ObjectAPIName | VARCHAR(255) | Target object |
| ShareWithRoleID | VARCHAR(255) FK | -> UserRole.Id (nullable) |
| ShareWithGroupID | VARCHAR(255) FK | -> Group.Id (nullable) |
| AccessLevel | VARCHAR(20) | 'Read' or 'Edit' |
| Criteria | TEXT | Formula expression evaluated by formula engine |
| IsActive | BOOLEAN | |

**Current:** Supports role-based + group-based + criteria-based sharing. Owner-based sharing (share records owned by Role A with Role B) is not yet implemented but can be approximated with criteria-based rules at 127-user scale.

### 2.11 RecordShare (Existing -- `_System_RecordShare`)

| Field | Type | Notes |
|-------|------|-------|
| Id | VARCHAR(255) PK | |
| SobjectType | VARCHAR(255) | Object API name |
| RecordId | VARCHAR(255) | Target record |
| UserOrGroupId | VARCHAR(255) | Grantee (User or Group) |
| AccessLevel | VARCHAR(20) | 'Read' or 'Edit' |
| RowCause | VARCHAR(255) | 'Manual', 'Rule', 'Team', etc. |

### 2.12 TeamMember (Existing -- `_System_TeamMember`)

Used for Account Teams, Opportunity Teams, Case Teams. Grants record-level access based on team membership.

### 2.13 GroupMember (Existing -- `GroupMember`)

Links Users to Groups (Public Groups, Queues). Groups can be referenced in sharing rules and manual shares.

### 2.14 LoginHistory -- NEW TABLE

```sql
CREATE TABLE _System_LoginHistory (
    Id              VARCHAR(255) PRIMARY KEY,
    UserId          VARCHAR(255) NOT NULL,
    LoginTime       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SourceIp        VARCHAR(45)  NOT NULL,  -- IPv4 or IPv6
    LoginType       VARCHAR(50)  NOT NULL,  -- 'Application', 'API', 'OAuth', 'Google'
    Status          VARCHAR(20)  NOT NULL,  -- 'Success', 'Failed', 'Locked'
    LoginUrl        VARCHAR(512),
    Browser         VARCHAR(255),
    Platform        VARCHAR(255),
    Application     VARCHAR(255),           -- Connected App name if OAuth
    ClientVersion   VARCHAR(50),
    ApiType         VARCHAR(50),            -- 'REST', 'SOAP', 'Bulk'
    ApiVersion      VARCHAR(10),
    CountryIso      VARCHAR(2),
    FailureMessage  VARCHAR(500),           -- NULL on success; reason on failure

    INDEX idx_login_user   (UserId, LoginTime DESC),
    INDEX idx_login_time   (LoginTime DESC),
    INDEX idx_login_status (Status, LoginTime DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

**Retention:** 180 days. Cleanup via scheduled job (same job that cleans expired sessions).

**SF parity fields omitted:** AuthenticationServiceId, CipherSuite, TlsProtocol (unnecessary at this scale).

### 2.15 SetupAuditTrail -- NEW TABLE

```sql
CREATE TABLE _System_SetupAuditTrail (
    Id              VARCHAR(255) PRIMARY KEY,
    Action          VARCHAR(100) NOT NULL,   -- 'Created', 'Changed', 'Deleted'
    Section         VARCHAR(100) NOT NULL,   -- 'Manage Users', 'Profiles', 'Permission Sets', 'Sharing Rules', 'Roles', 'Security Controls'
    Display         VARCHAR(1000) NOT NULL,  -- Human-readable: "Changed profile assignment for user John Doe from Standard to Admin"
    DelegateUser    VARCHAR(255),            -- If action performed via delegated admin
    CreatedById     VARCHAR(255) NOT NULL,
    CreatedDate     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ResponsibleNamespacePrefix VARCHAR(255), -- NULL for non-package changes

    INDEX idx_audit_date    (CreatedDate DESC),
    INDEX idx_audit_section (Section, CreatedDate DESC),
    INDEX idx_audit_user    (CreatedById, CreatedDate DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

**What gets logged:**
- User create/deactivate/reactivate, profile change, role change
- Permission set assignment/removal
- PSG create/modify/delete, muting changes
- Sharing rule create/modify/delete
- OWD (SharingModel) changes
- Role hierarchy changes
- Object/Field permission changes

**Retention:** 180 days (matches SF).

---

## 3. Permission Resolution

### System Permissions (ALREADY IMPLEMENTED)

**Location:** `perm_system.go:67-122`

```
Effective = {}

1. Profile baseline
   SELECT PermissionAPIName, IsEnabled
   FROM _System_ProfilePermission WHERE ProfileID = @profileId
   -> UNION into Effective (if enabled)

2. PermissionSet boolean columns (profile-owned + assigned + PSG components)
   SELECT Permissions* columns FROM PermissionSet
   WHERE (IsOwnedByProfile=1 AND ProfileID=@profileId)
      OR Id IN (assigned to user)
      OR Id IN (PSG component assigned to user)
   -> UNION into Effective (if true)

3. PermissionSet normalized rows (direct assignments)
   SELECT PermissionAPIName FROM _System_PermissionSetPermission
   JOIN PermissionSetAssignment ON ...
   WHERE AssigneeID = @userId
   -> UNION into Effective (if enabled)

4. PSG component rows + Muting
   SELECT PermissionAPIName FROM _System_PermissionSetPermission
   JOIN PermissionSetGroupComponent ON ...
   JOIN PermissionSetGroupAssignment ON ...
   -> UNION into Effective

   SELECT PermissionAPIName FROM _System_MutingPermissionSetPermission
   JOIN MutingPermissionSet ON ...
   JOIN PermissionSetGroupAssignment ON ...
   -> DELETE from Effective
```

**Result:** `map[string]bool` -- the user's effective system permission set.

### Object Permissions (ALREADY IMPLEMENTED)

```
For each object:
  Effective[perm] = MAX(profile_owned_permset[perm], permset_1[perm], ..., permset_N[perm])

Where MAX(false, true) = true (additive).
ViewAllRecords and ModifyAllRecords are included in this aggregation.
```

### Field Permissions (ALREADY IMPLEMENTED)

```
For each field:
  Effective.Read  = MAX(all PermSets' PermissionsRead for this field)
  Effective.Edit  = MAX(all PermSets' PermissionsEdit for this field)

Fallback: If no FieldPermissions row exists for a field, the field inherits
          visibility from the object-level Read permission.
```

### Complete Resolution Flow (Pseudocode)

```
function canUserAccess(user, object, field, record, operation):

    // Gate 0: Active user check
    if not user.IsActive: return DENY

    // Gate 1: SuperUser bypass
    if IsSuperUser(user.ProfileID): return ALLOW

    // Gate 2: System permission check (route-level)
    // Handled by middleware.RequirePermission() before reaching this point

    // Gate 3: Object permission
    objPerm = getEffectiveObjectPermission(user, object)
    if not objPerm[operation]: return DENY

    // Gate 4: Field permission (if field-level access requested)
    if field != "":
        fieldPerm = getEffectiveFieldPermission(user, object, field)
        if operation == "Read" and not fieldPerm.Read: return DENY
        if operation == "Edit" and not fieldPerm.Edit: return DENY

    // Gate 5: Record-level access (if specific record)
    if record != nil:
        if not checkRecordAccess(user, object, record, operation): return DENY

    return ALLOW
```

---

## 4. Record Access Model

This is the core of the design. Two access paths must produce consistent results.

### Path A: Single Record (CheckRecordAccess)

**Location:** `record_access.go` -- currently 5 layers. Design adds OWD + ViewAll to make 7 layers.

```
function checkRecordAccess(user, objectName, record, operation):

    // 0. SuperUser bypass (EXISTING - record_access.go:21)
    if IsSuperUser(user.ProfileID): return true

    // 1. OWD check (NEW)
    owd = metadata.GetSchema(objectName).SharingModel
    if owd == "PublicReadWrite": return true
    if owd == "PublicRead" and operation == "Read": return true
    // else: continue to record-level checks

    // 2. ViewAllRecords / ModifyAllRecords (NEW)
    objPerm = getEffectiveObjectPermission(user, objectName)
    if objPerm.ViewAllRecords and operation == "Read": return true
    if objPerm.ModifyAllRecords: return true  // ModifyAll implies ViewAll

    // 3. Record Ownership (EXISTING - record_access.go:51-63)
    if record.OwnerId == user.ID: return true
    if isUserInGroup(record.OwnerId, user.ID): return true  // Queue ownership

    // 4. Role Hierarchy (UPGRADE from Read-only to configurable R/E/D)
    //    EXISTING: record_access.go:69 -- only if operation == "Read"
    //    NEW: check EnableHierarchySharing on object metadata
    enableHierarchy = metadata.GetSchema(objectName).EnableHierarchySharing
    if enableHierarchy:
        ownerRoleID = getRecordOwnerRoleID(record.OwnerId)
        if isUserAboveInHierarchy(user.RoleID, ownerRoleID):
            return true  // Grants R+E+D (bounded by user's object permissions)

    // 5. Sharing Rules (EXISTING - record_access.go:79-85)
    rules = metadata.GetSharingRules(objectName)
    for rule in rules:
        if checkSharingRuleAccess(record, rule, user, operation): return true

    // 6. Manual Share (EXISTING - record_access.go:89-93)
    levels = repo.GetManualShareAccessLevels(objectName, record.Id, user.ID)
    for level in levels:
        if accessLevelAllowsOperation(level, operation): return true

    // 7. Team Member (EXISTING - record_access.go:96-99)
    teamLevel = repo.GetTeamMemberAccessLevel(objectName, record.Id, user.ID)
    if teamLevel != nil and accessLevelAllowsOperation(teamLevel, operation):
        return true

    // 8. Default: DENY
    return false
```

### Path B: Bulk Query (SQL RLS)

**Location:** `security_validator.go:163-233` -- currently only `owner_id = ?`. Design upgrades to full equivalence.

**Target WHERE clause (injected by applyRLS):**

```sql
-- Called for each table reference in the query.
-- Skip injection entirely if:
--   (a) IsSuperUser, OR
--   (b) object's SharingModel = 'PublicReadWrite', OR
--   (c) user has ViewAllRecords on this object
--
-- For SharingModel = 'PublicRead', inject only for Edit/Delete operations
-- (but RLS is read-path; SELECT queries always get the full injection
--  since RLS gates visibility, not editability. PublicRead = skip RLS for SELECT.)

WHERE (
    -- 1. Record Ownership (direct)
    t.owner_id = @user_id

    -- 2. Record Ownership (queue/group)
    OR t.owner_id IN (
        SELECT gm.GroupId FROM GroupMember gm
        WHERE gm.UserOrGroupId = @user_id
    )

    -- 3. Role Hierarchy subordinates (if EnableHierarchySharing = true)
    OR t.owner_id IN (
        SELECT u.Id FROM User u
        WHERE u.UserRoleId IN (@subordinate_role_ids)
        AND u.IsActive = 1
    )

    -- 4. Sharing Rules (record share entries with RowCause = 'Rule')
    OR EXISTS (
        SELECT 1 FROM _System_RecordShare rs
        WHERE rs.RecordId = t.Id
        AND rs.SobjectType = @object_name
        AND rs.UserOrGroupId IN (@user_group_ids)
    )

    -- 5. Manual Share (RowCause = 'Manual')
    -- Already covered by the RecordShare subquery above (same table)

    -- 6. Team Member
    OR EXISTS (
        SELECT 1 FROM _System_TeamMember tm
        WHERE tm.RecordId = t.Id
        AND tm.SobjectType = @object_name
        AND tm.UserId = @user_id
    )
)
```

**Pre-computed values (resolved once per request, not per row):**
- `@user_id` -- from session
- `@subordinate_role_ids` -- from role hierarchy cache (`GetRoleDescendants()`, new method)
- `@user_group_ids` -- user's direct ID + all Group IDs the user belongs to

**WHERE injection points:**

| Query Path | Current RLS | Target RLS |
|------------|-------------|------------|
| `ExecuteRawSQL` (analytics) | `owner_id = ?` only | Full WHERE clause |
| `/query` (SOQL endpoint) | **None** | Full WHERE clause |
| List views with filters | **None** | Full WHERE clause |
| Reports | Partial (via ExecuteRawSQL) | Full WHERE clause |
| Record CRUD (`GET /sobjects/:sobject/:id`) | Post-fetch CheckRecordAccess | Keep as-is (single record) |
| Composite/Batch | Delegates to above paths | Inherits from above |

**JOIN queries:** Inject RLS on each table independently. Currently `security_validator.go:173` returns nil for JOINs. Design: walk the `TableRefs` tree, inject WHERE clause for each `TableSource` that has an `owner_id` field.

```
function applyRLS(stmt, user):
    tables = extractAllTableSources(stmt.From)  // Walk Left/Right recursively
    for table in tables:
        schema = metadata.GetSchema(table.Name)
        if schema == nil: continue
        if schema.SharingModel == "PublicReadWrite": continue
        if userHasViewAllRecords(user, table.Name): continue
        if not hasOwnerIdField(schema): continue

        condition = buildRLSCondition(table, user)
        stmt.Where = AND(stmt.Where, condition)
```

### Consistency Guarantee

**Invariant:** For any user U and record R:
```
R is visible in SOQL query results  <=>  CheckRecordAccess(U, R, "Read") == true
```

Both paths consume the same data sources (OWD, ownership, hierarchy cache, sharing rules, record shares, team members). The SQL WHERE clause is the set-equivalent of the procedural CheckRecordAccess waterfall.

---

## 5. Route-Level Security

### Current Guards

| Route Group | Auth | PermLoader | Permission Guard | Status |
|-------------|------|------------|-----------------|--------|
| `/services/oauth2/*` | requireAuth (except /token) | No | None | OK |
| `/services/data/:version/*` | requireAuth | permLoader | None (global) | **Missing PermApiEnabled** |
| Metadata write routes | requireAuth | permLoader | `RequirePermission(PermModifyMetadata)` | OK |
| Flow execution | requireAuth | permLoader | `RequirePermission(PermRunFlow)` | OK |
| Tooling write routes | requireAuth | permLoader | `RequirePermission(PermCustomizeApplication)` | OK |
| Layout mutation | requireAuth | permLoader | `RequirePermission(PermCustomizeApplication)` | OK |
| App admin | requireAuth | permLoader | `RequirePermission(PermCustomizeApplication)` | OK |
| Analytics routes | requireAuth | permLoader | **None** | **Missing RunReports** |
| Bulk API | requireAuth | permLoader | None | **Missing PermApiEnabled** |

### Needed Guards

| Gap | Fix | Location |
|-----|-----|----------|
| **PermApiEnabled not checked** | Add `RequirePermission(PermApiEnabled)` to the `api` group | `routes.go:56` |
| **Analytics missing RunReports** | Add `RequirePermission(PermRunReports)` to `analyticsGroup` | `routes.go:159` |
| **Bulk API missing PermApiEnabled** | Inherited if PermApiEnabled added to `api` group | `routes.go:56` |

### PermApiEnabled Gate Design

```go
// In routes.go, change:
api := router.Group("/services/data/:version", requireAuth, permLoader)

// To:
api := router.Group("/services/data/:version",
    requireAuth,
    permLoader,
    middleware.RequirePermission(constants.PermApiEnabled),
)
```

This gates ALL data API access behind `PermApiEnabled`. SuperUsers bypass automatically (handled by `RequirePermission`). The `/services/oauth2/*` routes remain ungated since they are authentication endpoints, not data access.

---

## 6. Authentication & Session

### Current JWT Design (Keep As-Is)

```
JWT payload = {
    id:           string,    // User ID
    name:         string,    // Display name
    email:        string,    // Email
    profile_id:   string,    // Profile ID (may become stale)
    profile_name: string,    // Profile name
    role_id:      *string,   // Role ID (nullable)
    role_name:    *string,   // Role name (nullable)
    exp:          int64,     // Expiry (24h from issuance)
}
```

**Key design decision:** JWT carries identity only. Permissions are resolved per-request via `PermissionLoader` middleware (cached in-memory, 5min TTL). This means:
- Permission changes take effect within 5 minutes, no re-login required
- Profile reassignment detected by `PermissionLoader` (resolves current profile from DB with 60s cache -- `permission_loader.go:73`)
- No JWT bloat, no stale-permission security window

### Session Lifecycle Improvements

**6.1 Revoke on Deactivation (NEW)**

When `UpdateUser()` sets `IsActive = false`:
1. DELETE FROM `_System_Session` WHERE `UserId` = @userId
2. Call `permCache.InvalidateUser(userId)`
3. Log to `_System_SetupAuditTrail`

Any subsequent request with the old JWT will fail at `RequireAuth` because session lookup returns nothing.

**6.2 Expired Session Cleanup (NEW)**

Scheduled job (runs every hour):
```sql
DELETE FROM _System_Session WHERE ExpiresAt < NOW()
```

Existing `_System_Session` table has: Id, UserId, Token, ExpiresAt, CreatedAt, UpdatedAt, SourceIp, UserAgent.

**6.3 Permission Cache Invalidation on Profile/Role Change (NEW)**

When admin changes a user's ProfileId or UserRoleId:
1. Call `permCache.InvalidateUser(userId)` -- clears all cached permission maps
2. On next request, `PermissionLoader` detects profile mismatch (60s profile cache TTL) and reloads

When admin modifies a Profile's permissions or a PermissionSet's permissions:
1. Call `permCache.Invalidate()` -- full cache flush (affects all users)
2. Acceptable at 127-user scale (re-warm is fast)

---

## 7. Audit Model

### 7.1 _System_AuditLog Improvements

**Current:** Tracks field-level changes on record updates only (`record_access.go` path).
**Missing:** Create tracking, Delete tracking, Setup change tracking.

**Design:**
- Add audit log entries for record creates (log all non-null field values)
- Add audit log entries for record deletes (log record ID + key fields)
- Setup changes go to `_System_SetupAuditTrail` (separate table, see 2.15)

### 7.2 LoginHistory (NEW -- see Section 2.14)

**Write points:**
- Successful login: `auth_service.go` Login/TokenExchange handlers
- Failed login: `auth_service.go` (on password mismatch or inactive user)
- OAuth flows: `oauth_handler.go` Token endpoint

**API:**
- `GET /services/data/:version/query?q=SELECT+...+FROM+LoginHistory` (via standard SOQL)
- Setup page: LoginHistory viewer (filterable by user, date range, status)

### 7.3 SetupAuditTrail (NEW -- see Section 2.15)

**Write points:** All permission/config mutation handlers:
- User management (create, deactivate, profile change, role change)
- Permission set assignment/removal
- PSG management
- Sharing rule CRUD
- OWD changes
- Role hierarchy changes

**API:** Standard SOQL query against `_System_SetupAuditTrail`.

### 7.4 Session Cleanup

Hourly scheduled job:
```sql
-- Clean expired sessions
DELETE FROM _System_Session WHERE ExpiresAt < NOW();

-- Clean old login history (180 days)
DELETE FROM _System_LoginHistory WHERE LoginTime < DATE_SUB(NOW(), INTERVAL 180 DAY);

-- Clean old audit trail (180 days)
DELETE FROM _System_SetupAuditTrail WHERE CreatedDate < DATE_SUB(NOW(), INTERVAL 180 DAY);
```

---

## 8. Setup Administration

### Existing Pages (13)

| Page | Component | Status |
|------|-----------|--------|
| Objects | Schema Builder | Working |
| Users | UserManager.tsx | Working |
| Profiles | ProfileManager.tsx | Working |
| Permission Sets | PermissionSetManager.tsx | Working |
| Groups | GroupManager.tsx | Working |
| Roles | RoleManager.tsx (with tree + search) | Working |
| Sharing Rules | SharingRuleManager.tsx | Working |
| Permission Analyzer | PermissionAnalyzer.tsx (with user comparison) | Working |
| Flows | FlowManager | Working |
| Apps | AppManager | Working |
| Import | ImportWizard | Working |
| Export | ExportManager | Working |
| Schema Builder | SchemaBuilder | Working |

### Pages to Build

| Page | Priority | Effort | Description |
|------|----------|--------|-------------|
| PSG Manager | P1 (Phase 2) | 2d | Backend 100% ready. Register in SetupRegistry. UI: list PSGs, add/remove component PermSets, manage muting, assign to users. |
| OWD / Sharing Settings | P2 (Phase 3) | 2d | Per-object SharingModel dropdown (Private/PublicRead/PublicReadWrite). EnableHierarchySharing toggle. Read from `_System_ObjectMetadata`, PATCH to update. |
| Login History | P2 (Phase 2) | 1d | Table view with filters (user, date range, status). Read-only. |
| Setup Audit Trail | P2 (Phase 2) | 1d | Table view with filters (section, user, date range). Read-only. |
| System Permissions Catalog | P3 (Phase 3) | 1d | List all 60+ system permissions with descriptions and categories. |
| Session Management | P3 (Phase 3) | 1d | List active sessions. Admin can revoke individual sessions. |
| Custom Permissions Manager | P3 (Phase 3) | 1d | Backend ready. List/create/edit custom permissions. |
| Connected Apps Admin | P3 (Phase 3) | 2d | Table exists. CRUD UI for OAuth connected apps. |
| Sharing Debugger | P3 (Phase 3) | 3d | "Why can/can't user X see record Y?" -- walk the access check layers and show which one granted/denied. |

### OWD Configuration Page Design

```
Route: /setup/sharing-settings
Guard: RequirePermission(PermCustomizeApplication)

UI Layout:
┌─────────────────────────────────────────────────────┐
│ Organization-Wide Sharing Defaults                   │
├──────────────┬──────────────────┬───────────────────┤
│ Object       │ Default Access   │ Hierarchy Sharing │
├──────────────┼──────────────────┼───────────────────┤
│ Account      │ [PublicRead ▼]   │ [✓ Enabled]       │
│ Contact      │ [PublicRead ▼]   │ [✓ Enabled]       │
│ Opportunity  │ [Private    ▼]   │ [✓ Enabled]       │
│ Lead         │ [Private    ▼]   │ [✓ Enabled]       │
│ Case         │ [Private    ▼]   │ [✓ Enabled]       │
│ ...          │ ...              │ ...               │
└──────────────┴──────────────────┴───────────────────┘
                                    [Save]

API:
  GET  /services/data/v63.0/metadata/objects  (already exists, returns SharingModel)
  PATCH /services/data/v63.0/metadata/objects/:apiName
        body: { "sharingModel": "Private", "enableHierarchySharing": true }
        (already exists -- metadata_schema.go:165 handles SharingModel updates)
```

---

## 9. Implementation Phases

### Phase 0: Security (This Week, 3-4 Days)

| # | Task | Files | Effort | Impact |
|---|------|-------|--------|--------|
| 0.1 | **OWD enforcement in CheckRecordAccess** -- read `schema.SharingModel`, short-circuit on PublicReadWrite/PublicRead | `record_access.go` | 0.5d | Enables per-object access model |
| 0.2 | **ViewAllRecords check in CheckRecordAccess** -- after OWD, before ownership | `record_access.go` | 0.5d | Users with ViewAll can see all records of that object |
| 0.3 | **ViewAllRecords check in applyRLS** -- skip RLS injection if user has ViewAll | `security_validator.go:132` | 0.5d | Consistent with single-record path |
| 0.4 | **OWD check in applyRLS** -- skip injection if PublicReadWrite | `security_validator.go:163` | 0.5d | Avoids unnecessary WHERE clause |
| 0.5 | **PermApiEnabled guard** on API route group | `routes.go:56` | 0.5d | Gates all data API access |
| 0.6 | **RunReports guard** on analytics routes | `routes.go:159` | 0.5d | Gates report execution |
| 0.7 | **Session revocation on user deactivation** | `auth_user_mgmt.go` | 0.5d | Deactivated users immediately lose access |
| 0.8 | **Fix Profile UserType display** -- `GetAllProfiles` returns `IsSystem` (bool) but frontend expects `UserType` (string='Standard'). Add `IF(IsSystem, 'Standard', 'Custom') AS UserType` to the query, or map in service layer. Currently all profiles display as "Custom" in Setup UI. | `perm_system.go:36-63`, `ProfileManager.tsx:108` | 0.5d | Profiles page shows correct Standard/Custom badge |
| 0.9 | **Fix bootstrap seeder for all profiles** -- `InitializePermissions` (`permissions.go:31-37`) hardcodes 5 profile IDs. SF-migrated profiles with UUID IDs get zero ObjectPermissions seeded. Replace hardcoded list with `GetAllProfiles()` DB query (same pattern as `GrantInitialPermissions` at `service.go:410`). | `bootstrap/permissions.go:25-47` | 0.5d | All profiles get correct ObjectPermissions regardless of ID format |

### Phase 1: RLS Coverage (2-3 Weeks)

| # | Task | Files | Effort |
|---|------|-------|--------|
| 1.1 | **Upgrade applyRLS WHERE clause** -- add hierarchy subordinates, sharing rules, manual share, team member subqueries | `security_validator.go` | 3d |
| 1.2 | **JOIN support in applyRLS** -- walk TableRefs tree, inject per-table | `security_validator.go:172` | 2d |
| 1.3 | **Extend RLS to SOQL /query endpoint** -- call SecurityValidator from query handler | Query handler | 2d |
| 1.4 | **Role Hierarchy upgrade** -- configurable R+E+D via `EnableHierarchySharing` | `record_access.go:69` | 1d |
| 1.5 | **Add GetRoleDescendants()** -- inverse of GetRoleAncestors, needed for RLS subordinate lookup | `role_hierarchy.go` | 0.5d |
| 1.6 | **Permission cache invalidation** -- call InvalidateUser on profile/role change | `auth_user_mgmt.go`, permission handlers | 1d |

### Phase 2: Admin Completeness (2-3 Weeks)

| # | Task | Files | Effort |
|---|------|-------|--------|
| 2.1 | **PSG Manager UI** -- register in SetupRegistry, build React component | Frontend | 2d |
| 2.2 | **LoginHistory table + write points** -- create table, instrument auth handlers | Bootstrap, auth_service.go | 2d |
| 2.3 | **SetupAuditTrail table + write points** -- create table, instrument admin handlers | Bootstrap, various handlers | 3d |
| 2.4 | **Session cleanup scheduled job** -- hourly cleanup of expired sessions + old audit data | New background job | 1d |
| 2.5 | **UserInfo add role_id/role_name** | `oauth_handler.go:291-312` | 0.5d |
| 2.6 | **Frontend ViewAll/ModifyAll** -- create new `GET /services/data/v63.0/objectpermissions` endpoint that calls `ListEffectiveObjectPermissionsForUser()` (already exists at `service.go:457`). Frontend `permissionQueries.ts:35-36` fetches from this endpoint instead of hardcoding `false`. The existing `/sobjects` describe API does NOT return ViewAll/ModifyAll and should not be modified (matches SF describe behavior). | `permissionQueries.ts`, new `permissions_handler.go`, `routes.go` | 1.5d |

### Phase 3: Polish (Optional, 4-6 Weeks)

| # | Task | Effort |
|---|------|--------|
| 3.1 | OWD configuration page (Sharing Settings) | 2d |
| 3.2 | Password reset flow (email-based) | 3d |
| 3.3 | Account lockout after N failed logins | 2d |
| 3.4 | System Permissions catalog page | 1d |
| 3.5 | Custom Permissions Manager page | 1d |
| 3.6 | Session Management admin page | 1d |
| 3.7 | Sharing Debugger ("Why can/can't user X see record Y?") | 3d |
| 3.8 | Sharing Rules cache (5min TTL) | 1d |
| 3.9 | Connected Apps admin UI | 2d |

---

## 10. What We Explicitly Skip

| Feature | Why We Skip |
|---------|-------------|
| **Territory Management** | Enterprise-scale feature for geographic/product-line data segmentation. 127 users do not need a second hierarchy dimension. Use sharing rules + role hierarchy instead. |
| **Implicit Sharing (parent-child)** | Non-configurable in SF. Adds hidden complexity. At 127 users, explicit sharing rules cover the same use cases with better visibility. Revisit if users report access confusion with Account-Contact relationships. |
| **Session-based Permission Sets** | Elevates permissions for a session duration (e.g., "approve as manager"). No current business requirement. Can be added later without schema changes (HasActivationRequired field on PermissionSet). |
| **External OWD** | Separate OWD for Portal/Community users. No external users in NexusCRM. |
| **SAML 2.0 SSO** | Enterprise SSO protocol. Current Google OAuth + username/password covers needs. Add when an IdP integration is required. |
| **MFA / 2FA** | Important security feature but orthogonal to the permission model. Can be added to auth flow without affecting permission design. Recommend as separate workstream. |
| **Delegated Administration** | Allows non-admins to manage subsets of users. 127 users managed by a small admin team. Not needed. |
| **Login Hours / IP Range restrictions** | Profile-level access time/location restrictions. Low ROI at current scale. |
| **Permission Dependencies** | Auto-granting dependent permissions (e.g., ModifyAllData implies ViewAllData). Currently handled by bootstrap data correctness. Formalize if permission assignment errors become a problem. |
| **PermSet Assignment Expiration** | Time-limited permission grants. No current requirement. ExpirationDate field can be added to PermissionSetAssignment later. |
| **Record Type Access per Profile** | Controls which Record Types are visible/default per Profile. Low urgency; all Record Types are currently visible to all profiles. |
| **Tab/App Visibility per Profile** | Controls which tabs and apps are visible. Tables exist (`ProfileTabSetting`, `ProfileAppVisibility`) but no enforcement. Low priority. |
| **Share Table Materialization** | SF pre-computes `<Object>Share` tables for query performance. At 127 users, real-time subquery computation is fast enough and avoids async recalculation complexity. |
| **Apex Managed Sharing** | Programmatic sharing via code. NexusCRM has no Apex equivalent. Manual shares + sharing rules cover the same ground. |

---

## 11. Report & Dashboard Permissions

Report and Dashboard access in Salesforce is governed by a **separate folder-based permission subsystem**, distinct from the standard record-sharing model (OWD + RecordShare + RLS). NexusCRM implements this as a dedicated `FolderAccessService`.

### 11.1 Folder Access Model

**Why folders are separate from standard RLS:**

| Aspect | Standard Record Sharing | Folder Sharing |
|--------|------------------------|----------------|
| Scope | Point-to-point (one grant = one record) | Container-scoped (one grant = folder + all contents) |
| Access levels | Read / Edit / All | **View / Manage** |
| OWD baseline | Private → open up via grants | **No OWD** — pure positive-grant model |
| Role hierarchy | Automatic inheritance (manager sees subordinate records) | **No auto-inheritance** — must explicitly share with RoleAndSubordinates |
| Storage | `_System_RecordShare` (runtime DML) | `FolderShare` (metadata declarations) |

**Folder types:**

| Type | AccessType | Default Access | Auto-Created |
|------|-----------|---------------|-------------|
| Personal Reports | `Hidden` | Owner only (CreatedById) | Yes — lazy-created on first ListFolders |
| Personal Dashboards | `Hidden` | Owner only (CreatedById) | Yes — lazy-created on first ListFolders |
| Public/Shared Folders | `Shared` | Controlled by FolderShare rows | No — admin-created |

**Access levels (2-tier, matching Salesforce Enhanced Folder Sharing):**

| Level | Allows |
|-------|--------|
| **View** | See folder in list, read contents, run reports/dashboards |
| **Manage** | View + edit/delete folder, edit contents, manage sharing |

**FolderShare resolution: additive positive grants (maximum access level wins)**

Access is computed by scanning ALL matching FolderShare rows and taking the
maximum granted access level:

1. SuperUser / ModifyAllRecords → full access (short-circuit)
2. ViewAllRecords → View (short-circuit)
3. CreatedById == currentUser → full access (short-circuit)
4-7. Scan ALL FolderShare rows matching the user (via User, Role,
     RoleAndSubordinates, Group, Organization). For each match, record the
     granted access level. The effective access is the MAXIMUM across all matches.
8. If no match found → deny

This ensures overlapping grants are additive: a View share via Role + a Manage
share via Group = effective Manage access.

### 11.2 SharingModel: FolderSharing

ReportFolder uses a dedicated `SharingModel=FolderSharing` (not Private, not PublicReadWrite). This value causes `BuildRLSResult` in the RLS engine to dispatch to `FolderAccessService.VisibleFolderIDs()` instead of standard sharing logic.

This closes **both** exploit vectors:
- `/folders` REST endpoints → `FolderHandler` calls `CheckAccess()` directly
- `/query` SOQL endpoint → `BuildRLSResult` injects `WHERE Id IN (visible_ids)` via the `AllowedIDs` RLS result field

**SOQL path enforcement**: The `FolderSharing` branch in `BuildRLSResult` checks
BOTH visibility (via FolderAccessService.VisibleFolderIDs) AND the `RunReports`
system permission. A user without `RunReports` gets `DenyAll` on any SOQL query
touching ReportFolder, even if they have FolderShare grants. This ensures the
REST route guard (`RequirePermission(PermRunReports)`) and the SOQL path enforce
the same feature-permission boundary.

```
ReportFolder.SharingModel = "FolderSharing"
        │
        ├─ /folders REST → FolderHandler.CheckAccess(View/Manage)
        │
        └─ /query SOQL → BuildRLSResult → FolderAccessService.VisibleFolderIDs()
                          → RLSResult.AllowedIDs → WHERE Id IN (...)
```

### 11.3 System Permissions for Analytics

| Permission | Status | Controls |
|-----------|--------|---------|
| `RunReports` | **Wired** (route guard on /analytics, /folders read) | Can run reports, view report results |
| `ExportReport` | **Wired** | Can export report results to CSV/Excel |
| `CreateCustomizeReports` | **Wired** (route guard / enforcement) | Create/edit reports |
| `ManageReportsInPubFolders` | **Wired** (route guard / enforcement) | Manage public report folders |
| `ManageDashbdsInPubFolders` | **Wired** (route guard / enforcement) | Manage public dashboard folders |
| `CreateDashboards` | **Wired** (route guard / enforcement) | Create dashboards |
| `EditDashboards` | **Wired** (route guard / enforcement) | Edit dashboards |
| `ViewMyTeamsDashboards` | **Wired** (can be assigned to profiles/permsets; route guard exists) | View team dashboards with running user override. Note: the permission itself is wired and assignable, but the feature it enables (Dashboard Running User RLS context switching) is not yet implemented — see Section 11.4. |

### 11.4 Dashboard Running User (Schema Implemented — RLS Switching Phase 3)

Salesforce dashboards have a `DashboardType` that controls whose data permissions are used:

| DashboardType | Behavior |
|--------------|----------|
| `SpecifiedUser` | All viewers see data as the fixed `RunningUserId` |
| `LoggedInUser` | Each viewer sees data through their own RLS (dynamic dashboard) |
| `MyTeamUser` | Viewers see data as `RunningUserId`; can override if they have `ViewMyTeamsDashboards` |

**Current state:** Schema fields `DashboardType` and `RunningUserId` exist on the Dashboard table and are returned in API responses. The RLS context switching (executing dashboard queries using the running user's permissions) is NOT yet implemented — all dashboards currently run as LoggedInUser regardless of DashboardType.

### 11.5 Report/Dashboard Ownership

- `OwnerId` is the mutable ownership field on `ReportFolder`. Defaults to
  `CreatedById` at creation time (via COALESCE fallback for existing folders).
- `CreatedById` is preserved as an immutable audit attribute — never modified.
- `TransferFolderOwnership` updates `OwnerId`, not `CreatedById`.
- `FolderAccessService.CheckAccess` uses `COALESCE(OwnerId, CreatedById)` for
  backward compatibility with folders created before OwnerId was added.

### 11.6 Implementation Files

| File | Purpose |
|------|---------|
| `backend/internal/features/permissions/folder_access.go` | FolderAccessService: CheckAccess + VisibleFolderIDs |
| `backend/internal/features/permissions/rls_filter.go` | BuildRLSResult: FolderSharing branch dispatches to FolderAccessService |
| `backend/internal/domain/rls/rls.go` | AllowedIDs field on RLSResult + ToSQLFilter handler |
| `backend/internal/interfaces/rest/folder_crud.go` | Folder CRUD with access checks |
| `backend/internal/interfaces/rest/folder_shares.go` | FolderShare CRUD with access checks |
| `backend/internal/bootstrap/folder_permission_migration.sql` | Migration: personal folders + Organization shares + index |
| `backend/internal/bootstrap/folder_shares_bootstrap.go` | Bootstrap: Organization View shares for existing folders |
| `shared/pkg/constants/enums.go` | SharingModelFolderSharing enum value |
| `backend/internal/bootstrap/schema.go` | defaultSharingModel: ReportFolder → FolderSharing |

---

## References

### Implementation Files (verified line numbers)

| File | What It Does |
|------|-------------|
| `backend/internal/features/permissions/record_access.go` | 5-layer record access check (lines 15-104) |
| `backend/internal/features/analytics/security_validator.go` | SQL RLS injection -- applyRLS (lines 163-233), SecurityVisitor (lines 235-316) |
| `backend/internal/infrastructure/persistence/perm_system.go` | 4-layer system permission resolution (lines 67-122) |
| `backend/internal/interfaces/middleware/permission_loader.go` | Permission cache + PermissionLoader middleware (lines 324-398), RequirePermission guard (lines 401-442) |
| `backend/internal/features/permissions/role_hierarchy.go` | Role hierarchy cache + ancestor traversal (lines 10-98) |
| `backend/internal/features/permissions/sharing_rules.go` | Sharing rule evaluation with formula engine (lines 33-119) |
| `backend/cmd/server/routes.go` | Route definitions + guards (lines 31-285) |
| `shared/pkg/constants/enums.go:84-91` | SharingModel enum (Private, PublicRead, PublicReadWrite) |
| `shared/pkg/models/metadata.go:140-161` | ObjectMetadata struct (SharingModel, EnableHierarchySharing fields) |
| `shared/pkg/models/auth.go:4-13` | UserSession struct (models) |
| `backend/pkg/auth/jwt.go:19-27` | UserSession struct (JWT layer) |
| `shared/pkg/constants/z_generated_tables.go` | All system table names |
```

---