Application Security — Comprehensive Reference

Table of Contents

1. Security Architecture Overview
2. Authentication
3. Authorization (RBAC)
4. Organization Isolation
5. Encryption & Data Protection
6. Middleware Security Stack
7. Input Validation & Request Limits
8. Rate Limiting
9. Security Headers
10. CORS Policy
11. Audit Trail
12. Error Handling & Monitoring
13. Data Retention & Deletion
14. Secrets Management
15. Infrastructure Security
16. Vulnerability Management
17. Incident Response
18. Backup & Disaster Recovery

1. Security Architecture Overview

Prism implements defense-in-depth with security controls at every layer of the stack:

┌─────────────────────────────────────────────────────────┐
│                    Client (Browser)                      │
│  Clerk.js SDK → JWT acquisition, session management      │
└───────────────────────┬─────────────────────────────────┘
                        │ HTTPS (TLS 1.2+)
┌───────────────────────▼─────────────────────────────────┐
│              Cloudflare Edge (TLS termination)           │
│  WAF · DDoS mitigation · HSTS · Bot management          │
│  CF-Connecting-IP header injection                       │
└───────────────────────┬─────────────────────────────────┘
                        │ Internal (Cloudflare Tunnel)
┌───────────────────────▼─────────────────────────────────┐
│               Go Backend (Chi Router)                    │
│                                                          │
│  ┌─ Middleware Stack (13 layers) ───────────────────┐   │
│  │  1. Sentry panic capture                         │   │
│  │  2. Chi Recoverer                                │   │
│  │  3. Gzip compression                             │   │
│  │  4. CORS enforcement                             │   │
│  │  5. Structured logging                           │   │
│  │  6. Prometheus metrics                           │   │
│  │  7. Audit logging (mutation tracking)            │   │
│  │  8. Request body size limits                     │   │
│  │  9. Security headers                             │   │
│  │ 10. Rate limiting (Redis / in-memory)            │   │
│  │ 11. Real IP extraction (CF-Connecting-IP)        │   │
│  │ 12. Request ID injection                         │   │
│  │ 13. Auth (Clerk JWT / API Key) + Scope check     │   │
│  └──────────────────────────────────────────────────┘   │
│                                                          │
│  Handler → Service → Repository → Database               │
└───────────────────────┬─────────────────────────────────┘
                        │ TLS (connection pooling via pgx)
┌───────────────────────▼─────────────────────────────────┐
│            PostgreSQL 15 + PostGIS                       │
│  Row-Level Security (RLS) on all org-scoped tables       │
│  Org consistency triggers on join tables                  │
│  Encrypted at rest (managed platform)                    │
└─────────────────────────────────────────────────────────┘

Security Layers Summary

2. Authentication

Prism supports two authentication methods — Clerk JWT (user sessions) and API Keys (machine-to-machine). Every API request must authenticate via one of these methods.

2.1 Clerk JWT Authentication (User Sessions)

Flow:

1. User authenticates via Clerk.js SDK in the browser (SSO, social login, or email — MFA required for production access)
2. Clerk issues a short-lived JWT containing user identity, org context, and permissions
3. Frontend sends Authorization: Bearer <token> on every API request
4. Backend validates the JWT and extracts auth context

Implementation: go-backend/internal/middleware/auth.go, go-backend/internal/auth/clerk.go

JWT Validation Process

The ClerkValidator performs multi-step validation:

Token received
  │
  ├─ 1. Parse JWT header → extract `kid` (key ID)
  │
  ├─ 2. Fetch signing key from JWKS endpoint
  │     └─ Cached with 1-hour TTL (background refresh)
  │     └─ SSRF protection on JWKS fetch (see §2.3)
  │
  ├─ 3. Verify RSA signature (RS256 / RS384 / RS512 only)
  │     └─ HMAC algorithms explicitly rejected
  │
  ├─ 4. Validate standard claims:
  │     ├─ `exp` — must be present and not expired
  │     ├─ `iss` — must match configured CLERK_ISSUER
  │     └─ `aud` — must match CLERK_AUDIENCE (if configured)
  │
  └─ 5. Extract Prism-specific claims:
        ├─ `sub` → user ID
        ├─ `org_id` → organization ID
        └─ `org_permissions` → permission array

JWT Claims Structure

{
  "sub": "user_abc123",
  "org_id": "org_xyz789",
  "org_permissions": [
    "org:buildings:read",
    "org:buildings:write",
    "org:comps:read",
    "org:tims:read",
    "org:tims:write"
  ],
  "iss": "https://clerk.property-prism.com",
  "aud": "prism-api",
  "exp": 1712000000,
  "iat": 1711996400
}

Scope Normalization

Clerk permissions use org: prefix format. The backend normalizes these to internal scope format:

• org:buildings:read → buildings:read
• org:tims:write → tims:write
• org:api_keys:manage → api_keys:manage

Strict Scope Mode

STRICT_CLERK_SCOPES=true must be set in production.

Wildcard Scope Detection

If a JWT contains a wildcard scope (*), the middleware:

• Logs a structured error with the user ID and org ID
• Does not grant elevated access — wildcard scopes are not honored

2.2 API Key Authentication (Machine-to-Machine)

For scripts, integrations, and BI tools that cannot use browser-based JWT auth.

Flow:

1. Org admin creates an API key via POST /api/v1/api-keys (requires api_keys:manage scope)
2. Backend generates a cryptographically random key, stores only the SHA-256 hash
3. Raw key is returned once in the creation response — never stored or retrievable again
4. Client sends the raw key via X-API-Key header on subsequent requests
5. Backend hashes the provided key with SHA-256, looks up the hash in the database

Implementation: go-backend/internal/middleware/auth.go

API Key Validation Process

X-API-Key header received
  │
  ├─ 1. Length check: reject if > 512 bytes (guard against abuse)
  │
  ├─ 2. SHA-256 hash the raw key
  │
  ├─ 3. Database lookup by hash
  │     └─ Returns: key record with scopes, org_id, expires_at, revoked_at
  │
  ├─ 4. Expiration check: reject if past expires_at
  │
  ├─ 5. Revocation check: reject if revoked_at is set
  │
  ├─ 6. Org active check: verify the org is still active
  │
  ├─ 7. Set auth context (org ID, scopes, auth method = "api_key")
  │
  └─ 8. Async update of last_used_at timestamp (non-blocking)

API Key Security Properties

Scope Delegation Validation

The ValidAPIKeyScopes allowlist explicitly defines which scopes can be assigned to API keys. This prevents privilege escalation — an admin cannot create an API key with api_keys:manage scope, which would allow the key to create more keys.

Excluded from API key delegation:

• api_keys:manage
• operations:write

2.3 SSRF Protection (JWKS Fetch)

The JWKS endpoint fetch includes multiple SSRF defenses:

Configuration:

var defaultJWKSAllowedHostSuffixes = []string{
    ".clerk.accounts.dev", ".clerk.com", "clerk.accounts.dev",
    "clerk.com", "api.clerk.com", "property-prism.com",
}

2.4 JWKS Key Caching

3. Authorization (RBAC)

3.1 Role Definitions

3.2 Complete Scope Inventory

Defined in go-backend/internal/domain/auth.go:

Read Scopes (Viewer+):

Write Scopes (Member+):

Admin Scopes:

3.3 Enforcement Points

Authorization is enforced at four levels:

1. Route-level middleware (RequireScope) — checks the auth context has the required scope before the handler executes. Applied to every protected route group.

2. Admin middleware — additional gate for /admin/* routes that requires admin-level scopes.

3. Frontend UI gates (AuthGate, AdminRouteGate) — hides UI elements the user cannot access. These are convenience-only, not authoritative — the backend enforces the real check.

4. Database RLS — row-level policies enforce org isolation even if application-layer checks were somehow bypassed (defense-in-depth).

4. Organization Isolation

4.1 Multi-Tenant Architecture

Prism is a multi-tenant SaaS application. Each organization's data is fully isolated through a layered enforcement model:

JWT claims (org_id)
  │
  ├─ Backend middleware extracts org_id → sets auth context
  │
  ├─ Service layer passes org_id to all repository calls
  │
  ├─ Repository layer includes org_id in all SQL WHERE clauses
  │
  └─ Database RLS enforces org_id check at the row level

4.2 Data Classification

4.3 Row-Level Security (RLS) Implementation

All tables in public schema have RLS enabled, including public datasets (defense-in-depth).

RLS Helper Functions

Defined in database/PRISM_vNext_FINAL.sql:

RLS Policy Pattern

Standard org-scoped tables follow this pattern:

-- SELECT: any org member can read
CREATE POLICY tenant_select ON public.tenant_lookup
  FOR SELECT USING (public.can_org_read(organization_id));

-- INSERT: active members can create
CREATE POLICY tenant_insert ON public.tenant_lookup
  FOR INSERT WITH CHECK (public.can_org_write(organization_id));

-- UPDATE: active members can modify
CREATE POLICY tenant_update ON public.tenant_lookup
  FOR UPDATE USING (public.can_org_write(organization_id))
  WITH CHECK (public.can_org_write(organization_id));

-- DELETE: active members can remove
CREATE POLICY tenant_delete ON public.tenant_lookup
  FOR DELETE USING (public.can_org_write(organization_id));

Special RLS Policies

4.4 Cross-Org Consistency Triggers

Database triggers enforce referential integrity across org boundaries on join tables:

These triggers prevent an application bug from accidentally associating records across org boundaries.

4.5 BI Export Isolation

The export schema (prism_export) provides org-filtered views for BI tools:

• Each org gets a dedicated BI database role (e.g., prism_bi_org_123)
• The BI role has SELECT only on export views
• Export views are pre-filtered by organization_id
• BI credential lifecycle is managed via one-time token exchange (see §5.5)

5. Encryption & Data Protection

5.1 Transport Encryption (TLS)

HSTS Policy: Strict-Transport-Security: max-age=31536000; includeSubDomains

• 1-year HSTS max-age ensures browsers always use HTTPS after first visit
• includeSubDomains covers all subdomains

5.2 Data at Rest Encryption

Prism does not implement application-level field encryption — encryption at rest is delegated to the managed database and cache platforms, which is appropriate for the data classification (commercial real estate intelligence, no PII beyond contact names/emails).

5.3 JWT Cryptographic Verification

5.4 API Key Hashing

SHA-256 is appropriate here because API keys are high-entropy random values (not user-chosen passwords), so brute-force/dictionary attacks are not feasible.

5.5 BI Credential One-Time Token Exchange

BI credential creation uses a secure token exchange pattern to avoid transmitting database credentials in standard API responses:

Admin creates BI credentials
  │
  ├─ 1. Backend generates credentials (DB role + password)
  ├─ 2. Backend generates a 256-bit cryptographically random token
  ├─ 3. Token stored in Redis with 5-minute TTL
  ├─ 4. API returns the token (not the credentials)
  │
  └─ Client exchanges token for credentials
        ├─ 5. Client calls exchange endpoint with token
        ├─ 6. Backend validates token, checks org ID matches
        ├─ 7. Backend returns credentials
        └─ 8. Token is immediately deleted (one-time use)

Token generation: go-backend/internal/pkg/token/token.go

// 32 bytes (256 bits) of crypto/rand, URL-safe base64 encoded
func GenerateBICredentialToken() (string, error) {
    return GenerateSecureToken(32)
}

Security properties:

• 256-bit cryptographic randomness (crypto/rand)
• 5-minute TTL — auto-expires if not exchanged
• One-time use — deleted immediately after successful exchange
• Org ID validation — token can only be exchanged by the org that created it

5.6 Secret Storage

• No secrets in source code — .env files are gitignored, .env.example has only placeholders
• Infisical is the single source of truth for all production secrets
• No long-lived keys in frontend — only VITE_* (public) variables are in frontend builds
• GitHub push protection blocks commits containing detected secret patterns

See Secrets Management for the full secret inventory and rotation playbooks.

6. Middleware Security Stack

The backend applies a 13-layer middleware stack to every request, in this order:

Implementation: go-backend/cmd/api/main.go

Protected route groups add additional middleware:

• RequireScope(scope) — enforces specific permission scopes per route group
• BulkMaxBody() / UploadMaxBody() — larger body limits for bulk/upload routes

7. Input Validation & Request Limits

7.1 Request Body Size Limits

Implementation: go-backend/internal/middleware/request_limit.go

Requests exceeding the limit receive 413 Request Entity Too Large.

7.2 Domain Model Validation

Every domain entity has a Validate() method that enforces business rules before database operations:

Implementation: go-backend/internal/domain/*.go

Validation is called in the service layer before any repository operation. Invalid input returns 400 Bad Request with structured field-level errors.

7.3 SQL Injection Prevention

All database queries use parameterized queries via pgx:

// All user input is passed as parameters ($1, $2, ...), never interpolated
rows, err := pool.Query(ctx,
    `SELECT * FROM buildings WHERE building_id = $1 AND organization_id = $2`,
    buildingID, orgID,
)

No raw string concatenation is used in SQL queries.

7.4 API Key Length Guard

API keys provided in the X-API-Key header are rejected if longer than 512 bytes, preventing abuse via oversized inputs before hashing.

8. Rate Limiting

8.1 Algorithm

Implementation: go-backend/internal/middleware/rate_limit_redis.go

8.2 Configuration

8.3 Real IP Extraction

Implementation: go-backend/internal/middleware/real_ip.go

When TRUST_CLOUDFLARE_HEADERS=true:

1. Reads CF-Connecting-IP header (single IP, set by Cloudflare)
2. Falls back to X-Real-IP → X-Forwarded-For → RemoteAddr

The CF-Connecting-IP header cannot be spoofed by clients when traffic routes through Cloudflare, as Cloudflare overwrites it. This must only be trusted when Cloudflare is the edge proxy.

9. Security Headers

Implementation: go-backend/internal/middleware/security_headers.go

10. CORS Policy

Implementation: go-backend/cmd/api/main.go

cors.Handler(cors.Options{
    AllowedOrigins:   cfg.AllowedOrigins,  // Explicit per-environment list
    AllowedMethods:   []string{"GET", "POST", "PUT", "PATCH", "DELETE", "OPTIONS"},
    AllowedHeaders:   []string{"Accept", "Authorization", "Content-Type", "X-CSRF-Token", "X-API-Key"},
    ExposedHeaders:   []string{"Link"},
    AllowCredentials: true,
    MaxAge:           300,  // Preflight cache: 5 minutes
})

11. Audit Trail

11.1 Audit Log

Implementation: go-backend/internal/middleware/audit.go

The audit middleware captures all data mutations on sensitive resources:

Audited Resources

Audit Log Fields

Sensitive Field Redaction

For API key operations, the audit middleware uses an allowlist — only these fields are logged from request bodies:

• name
• scopes
• expires_in_days

Key hashes and raw key values are never written to the audit log.

Audit Log Integrity

• Append-only: RLS policy allows INSERT for org members but denies UPDATE and DELETE
• Admin-only read: Only users with audit:read scope can query the log
• Retention: 7 years (SOC 2 compliance) — enforced by pg_cron scheduled job

12. Error Handling & Monitoring

12.1 Error Capture

Implementation: go-backend/internal/middleware/error_capture.go

12.2 Request ID Injection

Implementation: go-backend/internal/middleware/request_id_inject.go

Every JSON error response body (4xx and 5xx) is automatically augmented with a request_id field. This ID is propagated to:

• Structured log entries (zap)
• Error log table entries
• Sentry error reports
• Frontend error display (user can report the ID for support)

12.3 Error Response Format

All API errors follow a standardized format:

{
  "error": "Forbidden",
  "message": "insufficient scope: buildings:write required",
  "request_id": "req_abc123def456"
}

Validation errors include field-level detail:

{
  "error": "Bad Request",
  "message": "validation failed",
  "field_errors": {
    "name": "required",
    "property_type": "must be one of: industrial, flex, office"
  },
  "request_id": "req_abc123def456"
}

Internal details (stack traces, SQL errors) are never exposed to clients — they are captured in Sentry and the error log.

12.4 Monitoring Stack

12.5 Debug Endpoint Protection

/debug/pprof endpoints (goroutine stacks, heap dumps) are only enabled in development mode (ENV=development). They are disabled in staging and production to prevent information leakage.

13. Data Retention & Deletion

13.1 Automated Retention Policies

Implementation: database/migrations/000018_add_data_retention_policies.up.sql

Data retention is enforced via pg_cron scheduled jobs that run daily at staggered times:

Purge SQL

-- Runs daily, staggered to avoid concurrent load
DELETE FROM public.error_log WHERE occurred_at < now() - INTERVAL '90 days';
DELETE FROM public.import_jobs WHERE created_at < now() - INTERVAL '90 days';
DELETE FROM public.contact_activities WHERE created_at < now() - INTERVAL '2 years';
DELETE FROM public.audit_log WHERE occurred_at < now() - INTERVAL '7 years';

13.2 Organization Offboarding (Full Purge)

When an organization cancels their account, all org-scoped data is deleted in FK-safe order:

1. Junction/child tables first: contact_activities, contact_owner_roles, broker assignments, building metrics/addresses/electrical, comp_leases, import_jobs
2. Core entity tables: contacts, buildings, owner_lookup, tenant_lookup, brokerage_firm_lookup
3. Auth/config tables: api_keys, user_org_memberships
4. Audit log: Anonymized (not deleted) — actor_user_id set to 'deleted-org', details redacted
5. Error log: Deleted
6. Organization record: Deleted last

Post-deletion checklist:

• Confirm zero rows for the org across all tables
• Revoke any BI database roles created for the org
• Remove org from Clerk
• Log the deletion with requester, date, and ticket number

13.3 Individual User Removal

When a single user leaves an org that remains active:

1. Delete user_org_memberships row for the user + org
2. Deactivate/remove user in Clerk

Business data stays with the org. Contacts, buildings, comps, activities, and import history belong to the organization, not individual users. The created_by column is retained as an internal audit trail.

13.4 Redis Cache Expiration

Redis is a cache, not durable storage. Total Redis loss causes performance degradation but no data loss.

14. Secrets Management

14.1 Architecture

14.2 Secret Inventory

14.3 Rotation Process

1. Rotate the credential in the upstream service
2. Update the value in Infisical (prod environment)
3. Infisical auto-syncs to deployment platform
4. Redeploy the affected service
5. Verify health (/ready for backend, auth flow for frontend)
6. Infisical version history serves as the rotation log

See Secrets Management for detailed per-secret rotation playbooks.

14.4 Break-Glass Access

Emergency direct database access:

• Must be documented, audited, and the credentials rotated immediately after use
• Uses postgres superuser role — break-glass only
• Normal application access uses prism_app role (no SUPERUSER, no BYPASSRLS)

15. Infrastructure Security

15.1 Network Architecture

Internet → Cloudflare (WAF + TLS) → Cloudflare Tunnel → Render (Backend :8080)
                                                       → Supabase (Postgres)
                                                       → Upstash (Redis)

• Backend port :8080 is not publicly accessible — only reachable via Cloudflare tunnel
• Database is managed by Supabase with network-level access controls
• Redis is managed by Upstash with TLS-only connections

15.2 Database Role Model

Key constraint: prism_app has no SUPERUSER and no BYPASSRLS — all application access goes through RLS policies.

15.3 Platform Access Control

15.4 CI/CD Security

16. Vulnerability Management

16.1 Dependency Scanning

16.2 Response SLAs

See Vulnerability Management for full procedures.

17. Incident Response

17.1 Severity Levels

17.2 Detection Sources

17.3 Response Process

1. Detect — alert triggers or report received
2. Triage — assign severity, notify on-call
3. Contain — isolate affected systems (revoke keys, block IPs, disable features)
4. Investigate — correlate request IDs, audit logs, error logs, Sentry events
5. Remediate — deploy fix, rotate compromised credentials
6. Recover — verify system health, confirm data integrity
7. Postmortem — document timeline, root cause, preventive actions

See Incident Response for full procedures.

18. Backup & Disaster Recovery

18.1 Objectives

18.2 Backup Strategy

Pre-migration: Manual snapshot required before every production database migration.

18.3 Degraded Mode Behavior

18.4 Restore Drills

Restore drills must be performed quarterly. Drill records are stored in docs/audit/restore-drills/.

See Backup & Disaster Recovery for full restore procedures.

Related Documents

• Security Architecture Overview — high-level security summary
• Access Control Policy — roles, permissions, joiner/mover/leaver
• Secrets Management — secret inventory, Infisical setup, rotation playbooks
• Incident Response — severity levels, triage, containment, postmortem
• Vulnerability Management — dependency scanning, CVE response
• Backup & DR — backup strategy, restore procedures
• Data Deletion Policy — org offboarding, user removal procedures
• Security Audit (2026-04-01) — latest security audit

Source Code References