OA Portal Architecture - Security-First Design

Authentication vs. Authorization (The Core Distinction)
Separate Data Stores (Client vs. Employee)
Client-Portal Isolation (Per-Client D1 vs. Shared with Tenant Filtering)
Authentication Provider (Managed vs. Hand-Built)
Cross-Domain VA Reference (Employees on Client Dashboards)
Concrete Test Scenarios
Reusable Security Protocol Checklist
Full Architecture Diagram
Endpoint-by-Endpoint Authorization Map

Section 1

Authentication vs. Authorization

The Wrong Assumption to Correct

Separate login pages do NOT mean data is isolated. Authentication answers "who is this person?" Authorization answers "what can this person access?" A system can authenticate someone perfectly and still hand them data they should never see if authorization is not enforced on every single request, server-side, without trusting anything the client sends.

Authentication = verifying identity. "This session belongs to user #472, who is a client_owner at ABC Landscaping." This happens once at login. Clerk (or whatever auth provider) handles this and issues a signed JWT.

Authorization = enforcing access rules on every request. "User #472 is requesting the performance dashboard for client #38. Is user #472 authorized to see client #38's data?" This happens on every single API call, server-side, by deriving the user's identity from the signed JWT and checking it against the database.

How Authorization Is Enforced: Endpoint by Endpoint

The server NEVER trusts a client_id, tenant_id, or user_id sent from the browser. Identity is always derived from the JWT, and permissions are checked against the database before any data is returned.

      Middleware: Authorization Enforcement (runs on EVERY request)
// This middleware runs before any endpoint handler.
// It extracts identity from the signed JWT (not from query params,
// not from request body, not from anything the browser controls).

async function authorizationMiddleware(request, env) {
  // Step 1: Extract the JWT from the cookie or Authorization header
  const token = request.headers.get('Authorization')?.replace('Bearer ', '');
  if (!token) return new Response('Unauthorized', { status: 401 });

  // Step 2: Verify the JWT signature (Clerk's public key)
  // If the JWT is forged or expired, this fails. Game over for the attacker.
  const claims = await verifyClerkJWT(token, env.CLERK_PUBLIC_KEY);

  // Step 3: From the verified JWT, extract the user ID
  // This ID comes from Clerk's signed token, NOT from the request.
  const userId = claims.sub; // e.g., "user_2xK9mN..."

  // Step 4: Look up this user in OUR database to get their role + scope
  const user = await env.CLIENT_DB.prepare(
    'SELECT id, client_id, role FROM client_users WHERE clerk_id = ?'
  ).bind(userId).first();

  if (!user) return new Response('Forbidden', { status: 403 });

  // Step 5: Attach server-derived identity to the request context
  // Downstream handlers use THIS, never anything from the request body
  request.auth = {
    userId: user.id,
    clientId: user.client_id,  // server-derived, not client-sent
    role: user.role             // "client_owner" | "client_manager" | "client_viewer"
  };

  return null; // proceed to handler
}
    

      Endpoint Example: GET /api/client/performance
// The client_id is NEVER taken from the URL or request body.
// It comes from request.auth.clientId, which was derived server-side.

async function getClientPerformance(request, env) {
  const { clientId, role } = request.auth;

  // Even if an attacker sends ?client_id=OTHER_CLIENT in the URL,
  // we ignore it. We use the server-derived clientId.

  const data = await env.CLIENT_DB.prepare(
    'SELECT * FROM va_performance WHERE client_id = ?'
  ).bind(clientId).all(); // clientId from JWT, not from request

  return Response.json(data.results);
}
    

      Endpoint Example: GET /api/client/feedback (owner-only data)
async function getStaffFeedback(request, env) {
  const { clientId, role } = request.auth;

  // Authorization check: only client_owner can see staff feedback
  if (role !== 'client_owner') {
    return new Response('Forbidden', { status: 403 });
  }

  const data = await env.CLIENT_DB.prepare(
    'SELECT * FROM staff_feedback WHERE client_id = ?'
  ).bind(clientId).all();

  return Response.json(data.results);
}
    

Key Principle: Server-Side Identity Derivation

The browser sends a JWT (set by Clerk at login). That is the only piece of client-sent data we trust, because it is cryptographically signed and verified against Clerk's public key.
From the verified JWT, we extract the user ID. From the user ID, we look up the client_id and role in our database.
The client_id and role are NEVER sent by the browser. They are derived server-side, every time, on every request.
If an attacker steals a session token, they can only access data for the client that token is associated with. They cannot pivot to another client's data because the client_id is bound to the session server-side.
"We tag content by client" is NOT the answer. The answer is: we derive the client identity from a cryptographically verified JWT and use that derived identity as the sole filter on every database query.

Section 2

Separate Data Stores: Client vs. Employee

Two completely separate D1 databases. Client data and employee data never share a store.

Employee HR data (payroll, health insurance, pay stubs) is a higher sensitivity class than client business performance data. A breach of the client portal must be structurally unable to expose employee payroll. Separate databases enforce this at the infrastructure level.

D1 Database: oa-employee-db

~400 OA employees
Payroll records + pay stubs
Health insurance enrollment
Department/division KPIs for leaders
HR announcements, holiday calendars
Strategic initiatives (admin-pushed)
Sensitivity: HIGH (PII, compensation, health)

D1 Database: oa-client-db

~150 client companies, each with 1+ users
VA performance dashboards (HubSpot)
Time tracking (Time Doctor)
Industry research, resources
Satisfaction surveys, staff feedback
Owner vs. employee permission tiers
Sensitivity: MEDIUM (business data)

Why Separate Databases (Not Just Separate Tables)

Blast-Radius Containment

Infrastructure-level isolation: The client portal Worker binds to oa-client-db. It physically cannot query oa-employee-db because it has no binding to it. A full remote code execution exploit in the client portal Worker still cannot reach employee payroll because the connection does not exist at the Cloudflare runtime level.
Different sensitivity classes: Employee compensation and health data may trigger compliance requirements (SOC 2, data residency, retention policies) that client business data does not. Separate stores let you apply different policies cleanly.
Independent failure domains: A bad migration on the employee schema cannot lock or corrupt the client database. A D1 storage quota event on one cannot affect the other.
Zero data overlap: No table in the employee database has a foreign key relationship to any table in the client database. They share no schema. Combining them into one database would be two databases sharing a container for no reason.

Admin Panel: One Worker, Two Bindings

A single Cloudflare Worker can bind to multiple D1 databases simultaneously. The admin panel Worker at admin.outsourceaccess.com binds to BOTH oa-employee-db and oa-client-db. OA staff log in once and can push content to either portal, manage users on either side, and view analytics from both. The admin panel is the bridge, but the underlying data stays in separate stores.

      wrangler.toml for admin Worker (two D1 bindings)
# The admin Worker binds to both databases.
# The client Worker and employee Worker each bind to only their own.

[[d1_databases]]
binding = "EMPLOYEE_DB"
database_name = "oa-employee-db"
database_id = "<employee-db-id>"

[[d1_databases]]
binding = "CLIENT_DB"
database_name = "oa-client-db"
database_id = "<client-db-id>"
    

      wrangler.toml for client portal Worker (single D1 binding)
# The client Worker has NO binding to the employee database.
# Even if an attacker achieves code execution in this Worker,
# env.EMPLOYEE_DB does not exist. The variable is undefined.

[[d1_databases]]
binding = "CLIENT_DB"
database_name = "oa-client-db"
database_id = "<client-db-id>"
    

Section 3

Client-Portal Isolation: Per-Client D1 vs. Shared with Tenant Filtering

Within the client database, there are ~150 client companies. Each must see only their own data. Two architectural options exist. Here is an honest comparison on security grounds, with cost explicitly off the table.

Option A: Separate D1 Database Per Client (~150 databases)

Isolation mechanism: Architectural. Each client's Worker route binds to only that client's D1 database. A bug in one client's query path physically cannot leak another client's data because the binding does not exist.
Breach blast radius: One client only. If client #38's database is compromised, clients #1-37 and #39-150 are untouched.
Query safety: No WHERE clause needed. Every query in the Worker returns data for only that client, by definition. A forgotten filter cannot cause a cross-tenant leak because there is no cross-tenant data to leak.
Cost: $0. D1 free tier is per-database. 150 databases each get 5GB and 5M reads/day independently.
Operational cost: Slightly more complex deployment. Schema migrations run 150 times. Client onboarding creates a new database + Worker binding. INFERRED This can be fully automated with a provisioning script.
Cross-client analytics: Harder. Admin panel would need to query 150 databases for aggregate reporting (e.g., "average satisfaction score across all clients"). INFERRED Solvable with a periodic aggregation job that writes summary stats to an analytics D1.

Option B: One Shared Client Database with Tenant Filtering

Isolation mechanism: Discipline. Every query must include WHERE client_id = ? with a server-derived client_id. The filtering middleware shown in Section 1 enforces this centrally.
Breach blast radius: All clients. If the database is compromised (credential leak, SQL injection that bypasses parameterization), all 150 clients' data is exposed.
Query safety: Depends on every query being written correctly. The middleware pattern below makes this enforceable, but it is still software discipline, not structural impossibility.
Cost: $0. One database, same free tier.
Operational cost: Simpler deployment. One schema, one migration, one database. Standard multi-tenant pattern used by most SaaS companies.
Cross-client analytics: Trivial. One query across all data with GROUP BY client_id.

Recommendation: Option A (Per-Client D1 Databases)

Since cost is not a factor, per-client databases provide stronger security by making cross-client data leaks structurally impossible. The operational complexity (automated provisioning, multi-database migrations) is a one-time engineering cost that Sterling can build. The security benefit is permanent: no future developer can accidentally write a query that leaks Client A's data to Client B, because Client A's Worker binding physically does not include Client B's database.

If You Choose Option B: How to Make Tenant Filtering Airtight

Option B is viable IF the tenant filter is enforced centrally so no individual query can bypass it. Here is the pattern. INFERRED This is the standard approach used by enterprise SaaS. It is well-proven but depends on discipline.

      Tenant-Scoped Query Builder (enforces client_id on every query)
// Instead of letting handlers write raw SQL, they use this builder.
// It ALWAYS injects the client_id filter. Cannot be bypassed.

class TenantScopedDB {
  constructor(db, clientId) {
    this.db = db;
    this.clientId = clientId;
  }

  // Every SELECT automatically includes WHERE client_id = ?
  async select(table, columns = '*', extraWhere = '', extraParams = []) {
    const where = extraWhere
      ? `WHERE client_id = ? AND (${extraWhere})`
      : 'WHERE client_id = ?';
    return this.db.prepare(
      `SELECT ${columns} FROM ${table} ${where}`
    ).bind(this.clientId, ...extraParams).all();
  }

  // INSERT automatically includes client_id column
  async insert(table, data) {
    data.client_id = this.clientId; // forced, regardless of input
    const cols = Object.keys(data).join(', ');
    const placeholders = Object.keys(data).map(() => '?').join(', ');
    return this.db.prepare(
      `INSERT INTO ${table} (${cols}) VALUES (${placeholders})`
    ).bind(...Object.values(data)).run();
  }

  // UPDATE scoped to client_id (cannot update another client's rows)
  async update(table, data, where, params = []) {
    const sets = Object.keys(data).map(k => `${k} = ?`).join(', ');
    return this.db.prepare(
      `UPDATE ${table} SET ${sets} WHERE client_id = ? AND (${where})`
    ).bind(...Object.values(data), this.clientId, ...params).run();
  }

  // DELETE scoped to client_id
  async delete(table, where, params = []) {
    return this.db.prepare(
      `DELETE FROM ${table} WHERE client_id = ? AND (${where})`
    ).bind(this.clientId, ...params).run();
  }
}

// Usage in middleware: handlers receive a scoped DB, not the raw DB
request.scopedDb = new TenantScopedDB(env.CLIENT_DB, request.auth.clientId);
    

      With the scoped builder, handlers cannot bypass the filter
// Handler ONLY has access to request.scopedDb, never env.CLIENT_DB directly.
// Every query automatically includes WHERE client_id = [server-derived value].

async function getPerformance(request) {
  const data = await request.scopedDb.select('va_performance');
  return Response.json(data.results);
}

// Even if someone writes a bad query, scopedDb enforces the filter.
// The raw env.CLIENT_DB binding is only available in the middleware,
// never passed to individual route handlers.
    

Option A vs. B: Bottom Line

Option A (per-client databases) makes cross-client leaks structurally impossible. Option B (shared database + TenantScopedDB) makes cross-client leaks practically very difficult but relies on the middleware always being in the request chain and the raw DB binding never leaking to handlers. With ~150 clients and cost off the table, Option A is the stronger security posture.

Section 4

Authentication: Managed Provider vs. Hand-Built

Brad's directive: cost is off the table. Evaluate on security alone.

Recommendation: Clerk (managed auth provider)

Authentication is the one component where getting it 95% right is worse than getting it 100% right. The 5% you miss (token rotation edge cases, session fixation, cookie SameSite behavior across browsers, brute-force protection, credential stuffing mitigation) are exactly the attack vectors that sophisticated attackers exploit. Clerk's team works on nothing but these problems. Sterling can build auth in 8 minutes, but Clerk's team has prevented auth bugs Sterling would never anticipate because they see millions of auth requests across thousands of apps.

Managed Auth (Clerk)

Security surface area Sterling does NOT manage: Password hashing, salt generation, timing-safe comparison, bcrypt cost factor tuning, credential stuffing detection, brute-force rate limiting, CAPTCHA integration, session token rotation, refresh token revocation, cookie security flags (HttpOnly, Secure, SameSite), CSRF protection, OAuth flow security, JWT signing key rotation
Compliance: SOC 2 Type II certified. They maintain the audit trail, not us.
Multi-factor auth: Built in. TOTP, SMS, email magic links. We turn it on with a flag.
Session management: Automatic token rotation, device tracking, session revocation from dashboard.
SSO/SAML: Available if enterprise clients want to use their own identity provider. INFERRED Not needed today but eliminates a future rebuild.
Free for the first 10,000 monthly active users. OA has ~900 total users. $0 for the foreseeable future.

Hand-Built Auth

Security surface area Sterling DOES manage: All of the items listed to the left. Every one is a potential vulnerability if implemented imperfectly.
Sterling builds fast, but auth bugs are subtle: A timing side-channel in password comparison leaks information. A missing SameSite=Lax flag enables CSRF. A refresh token that does not get rotated enables persistent session hijacking. These are not bugs you find in testing. They are bugs attackers find in production.
Ongoing maintenance: When Chrome ships a new cookie policy (they do this regularly), when Apple changes WebKit session storage behavior, when a new class of session fixation attack is published, Sterling has to update. Clerk does this automatically.
The "warranty" argument: Clerk is a specialist whose entire business depends on auth being correct. Hand-built auth has no warranty. Sterling IS the warranty, and every hour spent debugging an auth edge case is an hour not spent on revenue-moving work.

How Clerk Fits the Architecture

Clerk handles identity (login, session tokens, MFA). OA's D1 databases handle authorization (which client does this user belong to, what role do they have, what data can they see). Clerk's JWT tells us WHO. Our database tells us WHAT THEY CAN DO. The two are separate concerns, as they should be.

      Clerk integration in the Worker
import { verifyToken } from '@clerk/backend';

async function authenticateRequest(request, env) {
  const token = request.headers.get('Authorization')?.replace('Bearer ', '');

  // Clerk verifies the JWT signature, checks expiry, validates issuer
  const payload = await verifyToken(token, {
    secretKey: env.CLERK_SECRET_KEY,
  });

  // Clerk gives us a verified user ID. Now we look up authorization.
  const user = await env.CLIENT_DB.prepare(
    'SELECT id, client_id, role FROM client_users WHERE clerk_id = ?'
  ).bind(payload.sub).first();

  if (!user) throw new Error('User not found in authorization database');

  return user; // { id, client_id, role }
}
    

Section 5

Cross-Domain Link: VAs on Client Dashboards

VAs are OA employees. Their names and performance metrics appear on client dashboards. This creates a cross-domain reference between the employee database and the client database. The solution: narrow reference, not database merging.

Approach: VA Summary Table in the Client Database

The client database contains a va_assignments table with the minimal information a client needs to see: VA display name, profile photo URL, start date, and role. This is NOT a copy of the employee record. It is a purpose-built, minimal reference that contains only what the client portal needs to render the dashboard.

What the client DB stores: va_display_name, va_photo_url, va_start_date, va_role_title, employee_ref_id (opaque reference, not the employee's internal DB primary key).
What the client DB does NOT store: Payroll, health insurance, personal email, phone number, department assignments, KPI scores, HR records. None of that exists in the client database.
Sync mechanism: A scheduled Worker job (Cron Trigger) reads minimal VA assignment info from the employee DB and writes the summary fields to the client DB. This runs on admin infrastructure (admin Worker, which has both bindings). INFERRED
The employee_ref_id is opaque: It is a UUID or hash, not the employee's D1 primary key. Even if a client somehow obtained this ID, they could not use it to query the employee database because the client portal Worker has no binding to that database.

graph LR
    subgraph EmpDB["oa-employee-db"]
        E["employees table
Full record: name, email,
payroll, health, department"]
    end

    subgraph Sync["Admin Worker (Cron)"]
        S["Copies ONLY:
display_name, photo_url,
start_date, role_title"]
    end

    subgraph ClientDB["oa-client-db"]
        V["va_assignments table
Minimal: display_name,
photo_url, start_date,
role_title, employee_ref_id"]
    end

    E -->|"Minimal fields only"| S
    S -->|"Write summary"| V

    style EmpDB fill:#1a1a2e,stroke:#dc2626,color:#fff
    style ClientDB fill:#1a1a2e,stroke:#059669,color:#fff
    style Sync fill:#48A7DB,stroke:#48A7DB,color:#fff

The employee database keeps the full record. The client database gets a purpose-built summary with only what the client dashboard needs. No database merging. No shared tables. No foreign key relationships across databases.

Section 6

Concrete Test Scenarios

Every isolation claim ships with a test. These are specific, runnable scenarios that prove the authorization model works.

Cross-Client Isolation Tests

Test 1: Client A attempts to fetch Client B's performance data by manipulating the URL

GIVEN User "Jane" is authenticated as client_owner for "ABC Landscaping" (client_id = 38)

WHEN Jane sends GET /api/client/performance?client_id=42 (where 42 = "XYZ Plumbing")

THEN The server ignores the query parameter client_id=42. The middleware derives client_id=38 from Jane's JWT. The response contains ONLY ABC Landscaping's data. REQUEST SCOPED TO OWN DATA

      Why this works mechanically
// The handler never reads request.query.client_id.
// It uses request.auth.clientId, which was derived from the JWT.

async function getPerformance(request, env) {
  // request.auth.clientId = 38 (from JWT, server-derived)
  // The ?client_id=42 in the URL is completely ignored.
  const data = await env.CLIENT_DB.prepare(
    'SELECT * FROM va_performance WHERE client_id = ?'
  ).bind(request.auth.clientId).all(); // Always 38, never 42
  return Response.json(data.results);
}

// With Option A (per-client DBs), this is even stronger:
// Jane's Worker binding points to oa-client-db-38.
// Client 42's data is in oa-client-db-42.
// The binding literally does not exist. No code path can reach it.
    

Test 2: Client A attempts to fetch Client B's record by guessing the record ID

GIVEN User "Jane" is authenticated as client_owner for client_id = 38

WHEN Jane sends GET /api/client/surveys/survey_id_999 (a survey belonging to client_id = 42)

THEN Query: SELECT * FROM satisfaction_surveys WHERE id = 999 AND client_id = 38. Returns 0 rows because survey 999 belongs to client 42, not 38. Response: 404 Not Found. DENIED - 404

Test 3: Client session attempts to reach employee data

GIVEN User "Jane" has a valid client portal session (JWT from clients.outsourceaccess.com)

WHEN Jane sends GET https://server.outsourceaccess.com/api/employee/payroll (employee portal endpoint)

THEN The employee portal Worker validates Jane's JWT against the employee users table. Jane does not exist in the employee database. Response: 403 Forbidden. DENIED - 403

      Why cross-portal access fails at multiple layers
// Layer 1: Different Clerk applications
// The client portal and employee portal use SEPARATE Clerk apps.
// Jane's client JWT was signed by the client Clerk app.
// The employee Worker verifies against the employee Clerk app's key.
// Signature verification fails. Request rejected at JWT validation.

// Layer 2: Even if using one Clerk app (simpler setup),
// the employee Worker looks up the user in oa-employee-db.
// Jane is not in that database. 403 Forbidden.

// Layer 3: Even if an attacker somehow bypasses auth,
// the client portal Worker has NO D1 binding to oa-employee-db.
// env.EMPLOYEE_DB is undefined. The database does not exist
// in this Worker's execution context.
    

Test 4: Client employee (non-owner) attempts to access owner-only feedback

GIVEN User "Mike" is authenticated as client_manager for client_id = 38

WHEN Mike sends GET /api/client/feedback

THEN Middleware checks role. Mike's role is "client_manager", not "client_owner". Response: 403 Forbidden. DENIED - ROLE CHECK

Test 5: Unauthenticated request to any API endpoint

GIVEN No JWT token in request headers

WHEN Anonymous user sends GET /api/client/performance

THEN Authorization middleware finds no token. Response: 401 Unauthorized. No database query is ever executed. DENIED - 401

Test 6: Forged JWT with a different client_id claim

GIVEN Attacker crafts a JWT with sub = "fake_user" and client_id = 42

WHEN Attacker sends GET /api/client/performance with this forged JWT

THEN Clerk's verifyToken() checks the JWT signature against Clerk's public key. The forged token's signature does not match. Verification fails. Response: 401 Unauthorized. The client_id claim in the JWT body is never even read (we derive it from our DB, not from the JWT body). DENIED - INVALID SIGNATURE

Admin Panel Tests

Test 7: Non-admin user attempts to access admin panel

GIVEN User "Jane" has a valid client session (role = client_owner)

WHEN Jane sends GET https://admin.outsourceaccess.com/api/admin/users

THEN Admin Worker validates JWT and looks up user in admin_users table. Jane is not an admin. Response: 403 Forbidden. DENIED - NOT ADMIN

Section 7

Reusable Security Protocol Checklist

This checklist applies to every portal, every endpoint, and every new feature added to the OA platform. It is the standing security contract.

Security Checklist (Every Endpoint, Every Feature)

Authentication is not authorization. Verifying WHO someone is (authentication) does not determine WHAT they can access (authorization). Both must be checked on every request. Authentication happens via Clerk JWT verification. Authorization happens via server-side role and scope lookup in OA's D1 database. One without the other is incomplete.

Never trust IDs from the client/browser. User IDs, client IDs, tenant IDs, and record IDs sent in query parameters, URL paths, or request bodies are user-controlled input. They can be manipulated. Always derive identity server-side from the cryptographically verified JWT, then look up permissions in the database. Treat all client-sent IDs as untrusted hints at best, ignored at default.

Prefer isolation by architecture over isolation by filtering discipline. A separate database per tenant (or per data class) makes cross-contamination structurally impossible. A shared database with WHERE clauses makes it practically difficult but relies on every query being correct. When the cost is the same (D1 free tier), architecture wins. When architecture is impractical, enforce filtering centrally via middleware (TenantScopedDB pattern) so individual handlers cannot bypass it.

Least privilege by default. Every user starts with zero access. Permissions are explicitly granted, never implied. The client_viewer role sees less than client_manager, which sees less than client_owner. Each role's allowed endpoints are explicitly listed. If a new endpoint is added, it is denied by default until a role is explicitly granted access.

Defense in depth. No single layer is the whole defense. The architecture stacks multiple layers: (1) Cloudflare WAF blocks common attacks at the edge, (2) Clerk verifies identity via signed JWTs, (3) Middleware derives server-side identity and checks authorization, (4) Database bindings enforce infrastructure-level isolation, (5) Query-level filtering scopes data to the authorized user. An attacker must defeat ALL layers, not just one.

Every isolation claim ships with a test. "Client A cannot see Client B's data" is not a design document assertion. It is a testable scenario with GIVEN/WHEN/THEN steps, expected HTTP status codes, and a mechanistic explanation of why it works. Before deploying any new isolation boundary, write the test scenario first. If you cannot write the test, you do not understand the boundary.

How to Use This Checklist

Before any new endpoint or feature goes live, walk through all 6 items. For items 1-2, confirm the endpoint uses the authorization middleware and never reads IDs from the request. For item 3, confirm which isolation mechanism applies. For item 4, confirm the new endpoint is deny-by-default. For item 5, count how many layers protect this data. For item 6, write the test scenario.

Section 8

Full Architecture Diagram

graph TB
    subgraph DNS["DNS Layer"]
        A["server.outsourceaccess.com"]
        B["clients.outsourceaccess.com"]
        C["admin.outsourceaccess.com"]
    end

    subgraph Auth["Clerk (Managed Auth)"]
        CK1["Client Clerk App
JWT signing + MFA"]
        CK2["Employee Clerk App
JWT signing + MFA"]
        CK3["Admin Clerk App
JWT + MFA + IP whitelist"]
    end

    subgraph Workers["Cloudflare Workers - API + Authorization"]
        W1["Employee Worker
1. Verify Employee JWT
2. Derive employee_id from DB
3. Check role permissions
4. Return scoped data"]
        W2["Client Worker
1. Verify Client JWT
2. Derive client_id from DB
3. Check role (owner/mgr/viewer)
4. Return scoped data"]
        W3["Admin Worker
1. Verify Admin JWT
2. Check admin role
3. Route to correct DB
4. Audit log all writes"]
    end

    subgraph EmpDB["D1: oa-employee-db (HIGH sensitivity)"]
        E1[("employees
payroll_records
pay_stubs")]
        E2[("health_insurance
kpi_metrics
announcements")]
    end

    subgraph ClientDB["D1: oa-client-db (or per-client DBs)"]
        C1[("clients
client_users
va_assignments")]
        C2[("va_performance
time_tracking
satisfaction_surveys")]
        C3[("staff_feedback
industry_content
resources")]
    end

    subgraph Sources["External Data Sources"]
        HS["HubSpot API"]
        TD["Time Doctor API"]
        GD["Google Drive"]
    end

    A --> CK2
    B --> CK1
    C --> CK3

    CK2 -->|"Verified JWT"| W1
    CK1 -->|"Verified JWT"| W2
    CK3 -->|"Verified JWT"| W3

    W1 -->|"ONLY binding"| E1
    W1 --> E2

    W2 -->|"ONLY binding"| C1
    W2 --> C2
    W2 --> C3

    W3 -->|"Binding 1"| E1
    W3 -->|"Binding 2"| C1

    HS --> C2
    TD --> C2
    GD --> C3

    style DNS fill:#362456,stroke:#362456,color:#fff
    style Auth fill:#7c3aed,stroke:#7c3aed,color:#fff
    style Workers fill:#48A7DB,stroke:#48A7DB,color:#fff
    style EmpDB fill:#1a1a2e,stroke:#dc2626,color:#fff
    style ClientDB fill:#1a1a2e,stroke:#059669,color:#fff
    style Sources fill:#f0f0f0,stroke:#ccc,color:#333

Red border = Employee DB (payroll, health, HR). Green border = Client DB (performance, content, feedback). Each Worker binds ONLY to its own database. The Admin Worker is the sole bridge.

Section 9

Endpoint-by-Endpoint Authorization Map

Every API endpoint, what database it touches, how identity is derived, and what authorization check runs.

Client Portal Endpoints (clients.outsourceaccess.com)

Endpoint	DB	Identity Source	Authorization Check	Roles Allowed
`GET /api/client/performance`	oa-client-db	JWT → clerk_id → client_users.client_id	Filter by server-derived client_id	owner, manager, viewer
`GET /api/client/time-tracking`	oa-client-db	JWT → clerk_id → client_users.client_id	Filter by server-derived client_id	owner, manager, viewer
`GET /api/client/surveys`	oa-client-db	JWT → clerk_id → client_users.client_id	Filter by server-derived client_id	owner, manager
`GET /api/client/feedback`	oa-client-db	JWT → clerk_id → client_users.client_id	Filter by client_id + role = owner	owner ONLY
`GET /api/client/resources`	oa-client-db	JWT → clerk_id → client_users.client_id	Filter by client industry tag	owner, manager, viewer
`POST /api/client/users/invite`	oa-client-db	JWT → clerk_id → client_users.client_id	role = owner + same client_id	owner ONLY

Employee Portal Endpoints (server.outsourceaccess.com)

Endpoint	DB	Identity Source	Authorization Check	Roles Allowed
`GET /api/employee/payroll`	oa-employee-db	JWT → clerk_id → employees.id	Filter by server-derived employee_id (own records only)	All employees (own data)
`GET /api/employee/health-insurance`	oa-employee-db	JWT → clerk_id → employees.id	Filter by server-derived employee_id	All employees (own data)
`GET /api/employee/kpis`	oa-employee-db	JWT → clerk_id → employees.department_id	Filter by department_id + role >= team_leader	team_leader, ops_manager, admin, owner
`GET /api/employee/announcements`	oa-employee-db	JWT → clerk_id → employees.id	Public to all authenticated employees	All employees

Admin Panel Endpoints (admin.outsourceaccess.com)

Endpoint	DB	Identity Source	Authorization Check	Roles Allowed
`POST /api/admin/content/push`	Both (routed)	JWT → admin_users table	Admin role + target portal validation	OA admin staff only
`GET /api/admin/clients/list`	oa-client-db	JWT → admin_users table	Admin role required	OA admin staff only
`POST /api/admin/employees/create`	oa-employee-db	JWT → admin_users table	Admin role + HR permission flag	OA HR admin only
`GET /api/admin/audit-log`	Both	JWT → admin_users table	Admin owner role	Brad + senior admin only

Key Pattern Across All Endpoints

Every endpoint follows the same pattern: (1) JWT verified by Clerk, (2) user ID extracted from verified JWT, (3) user looked up in the appropriate database to get role + scope, (4) authorization check runs against the server-derived role + scope, (5) database query filtered by server-derived identity. No endpoint trusts client-sent IDs. No endpoint skips the authorization middleware.

Summary

Decision Summary

Architecture Decisions

1. Auth vs. Authz: Clerk handles authentication (identity). OA's Workers enforce authorization (access control) on every request, server-side, deriving identity from signed JWTs and never trusting client-sent IDs.

2. Data stores: Two separate D1 databases (oa-employee-db, oa-client-db). Different sensitivity classes, different blast radii. Admin Worker bridges both.

3. Per-client isolation: Recommended: per-client D1 databases (~150 DBs, $0 cost). Isolation enforced by architecture, not discipline. If shared model chosen, enforce via TenantScopedDB middleware pattern.

4. Auth provider: Clerk (managed). Auth is the one domain where specialist > generalist. Free for the first 10K MAU. SOC 2 compliant. Handles the subtle edge cases (token rotation, brute-force, session fixation) that hand-built auth misses.

5. VA cross-reference: Minimal va_assignments table in client DB with display-only fields. No database merging. Opaque employee reference ID.

6. Every isolation claim has a test: 7 concrete test scenarios with GIVEN/WHEN/THEN + mechanistic code explanations.

7. Security checklist: 6-point reusable protocol. Apply to every new endpoint and feature.