PR 5 — Permission Catalog (DB-Backed)

This PR moves the portalPermissions map from internal/app/permissions.go into a database catalog. Roles stay enum-in-code (rare changes, security-sensitive). Permissions become data — auditable, queryable, seedable into custom roles in PR6.

This is the inflection point between the in-code RBAC of PRs 1–4 and the delegated Spaces + Groups model of PRs 6–7. Without a DB-backed catalog, custom roles in PR6 either duplicate the map or grow uncontrolled.

Depends on PR 1. Independent of PRs 2, 3, 4.

---

1. Goal

• New tables: permissions, roles, role_permissions.
• Seed migration replicates the current portalPermissions map exactly — no behavior change at cutover.
• CanPerform(role, action) reads DB, cached in-process for the request lifetime (or short TTL).
• New endpoints to query the catalog (read-only): GET /api/v1/permissions, GET /api/v1/roles/:role/permissions.
• Single test asserts seeded catalog matches the legacy in-code map at cutover.

Non-goals

• Customer-defined roles (PR6).
• UI to edit the catalog (catalog is platform-defined; edits are migrations).
• Per-customer permission overrides.

---

2. Schema

New migration: internal/db/migrations/007_permission_catalog.sql

CREATE TABLE permissions (
  action      TEXT PRIMARY KEY,
  description TEXT
);

CREATE TABLE roles (
  name  TEXT PRIMARY KEY,
  scope TEXT NOT NULL CHECK (scope IN ('system', 'customer'))
              -- 'customer' reserved for PR6 custom roles
);

CREATE TABLE role_permissions (
  role_name         TEXT NOT NULL REFERENCES roles(name) ON DELETE CASCADE,
  permission_action TEXT NOT NULL REFERENCES permissions(action) ON DELETE CASCADE,
  PRIMARY KEY (role_name, permission_action)
);

CREATE INDEX idx_role_permissions_role ON role_permissions(role_name);

Seed inserts:

• Every action from portalPermissions map → permissions.
• Every role currently in the role priority list → roles with scope = 'system'.
• Every (role, action) pair from the legacy map → role_permissions.

Generated by a one-off Go program (cmd/seed_permissions/main.go) that reads the map and writes SQL — committed in the migration. Document that future permission catalog changes are migrations, not map edits.

---

3. Reader

Replace internal/app/permissions.go CanPerform:

type Catalog struct {
    db *sql.DB
    mu sync.RWMutex
    cache map[string]map[string]struct{} // role → action set
    expiry time.Time
}

func (c *Catalog) CanPerform(role, action string) bool {
    c.refreshIfStale()
    return c.cache[role][action] == struct{}{}
}

func (c *Catalog) refreshIfStale() {
    // 60s TTL is enough; catalog changes are migrations, very rare.
}

Bootstrapped once at app start; injected into request context. Migrations that change the catalog → bounce the API or wait one TTL.

Cutover safety

The first deploy must seed the catalog before the new reader runs. Order:

1. Migration runs (creates tables + seeds rows).
2. App starts; Catalog loads from DB.
3. CanPerform returns identical results to the legacy map.

The map stays in code as a fallback for one release: if the DB read returns zero rows for a role, fall back to map and emit a warning metric. Remove the fallback in the next release.

---

4. Endpoints

Read-only; gated to internal users.

Method	Path	Purpose
GET	/api/v1/permissions	List all permission actions
GET	/api/v1/roles	List all roles + scope
GET	/api/v1/roles/:role/permissions	Permissions granted to a role
GET	/api/v1/permissions/:action/roles	Roles that have a permission (audit query)

Useful for the admin assignment UI ("what does this role do?") and for the upcoming PR6 group-binding picker.

No write endpoints in this PR — catalog edits go through migrations.

---

5. Frontend

Permission map

web/src/auth/permissions.ts:

• Keep the static map as a fallback for offline rendering (sidebar filtering before /me returns).
• After login, override with the effectivePermissions array from the /me payload. Frontend trusts the server.

type Permissions = {
  effectiveActions: string[]   // from /me — server-resolved
  // legacy map kept for boot/fallback
}

export function canPerform(action: string) {
  return useAuth.getState().effectiveActions?.includes(action) ?? false
}

Catalog viewer (optional UI)

/internal-users/:id/permissions — read-only matrix showing which actions the user can take based on their roles. Built on top of the new endpoints. Useful for support / audit; not a P0 for cutover.

---

6. Tests

Layer	Test
Migration	Seeds match in-code map exactly (table-driven test reading both)
Reader	CanPerform returns identical bool for every (role, action) pair vs map
Reader	Cache reload picks up DB changes after TTL
API	GET /roles/:role/permissions returns expected set
Cutover	With Catalog empty (simulated), reader falls back to map + emits warning
Frontend	/me payload populates effectiveActions; sidebar uses it

---

7. Implementation order

Step	Layer	Description
1	Tooling	cmd/seed_permissions/main.go generates migration SQL from current map
2	DB	Migration 007_permission_catalog.sql (uses generated SQL)
3	Backend	internal/app/catalog.go — Catalog type with cache + TTL
4	Backend	CanPerform reads from Catalog, fallback to map + warning
5	Backend	Catalog read endpoints
6	Backend	/me payload includes effectiveActions
7	Tests	Parity test (DB seed == in-code map)
8	Frontend	/me populates effectiveActions; permissions.ts wired
9	Frontend	Optional catalog viewer page
10	Release	After 1 release of dual-mode, remove map fallback

---

8. Risk register

Risk	Mitigation
Migration seed drifts from in-code map at cutover	Generated by one tool; parity test in CI before merge
DB unreachable at boot → no permissions resolve → all 403s	Fallback to in-code map for one release; alert on fallback hits
Cache TTL holds stale catalog after migration	TTL is 60s; restart on catalog migration
Frontend caches a permission and user role changes	/me re-queried on auth state change; effectiveActions refreshed
New permission action added in code but not migrated	Catalog read returns false → forces deploy ordering: migration first
PR6 lands custom roles before fallback removed	Custom roles never match in-code map → fallback miss is correct path