Enterprise Architecture

Platform Vision: Two Audiences, One Portal

The OA Portal (server.outsourceaccess.com) serves two fundamentally different audiences through a single unified platform. Every user authenticates via Google OAuth. The system determines their role, resolves their permissions via the RBAC hierarchy, and renders a personalized dashboard with only the data and tools they are authorized to see.

Internal Employees (500+ staff)

Ops Managers (OM) -- Portfolio-level visibility across all their TLs, VAs, and clients
Account Managers (AM) -- Client relationship health, engagement metrics, CSAT scores
Team Leaders (TL) -- VA performance, time tracking, client task completion
VAs -- Personal dashboard: time logged, tasks, training, HR/benefits
Vision Team -- Executive-level analytics, company-wide KPIs, financial summaries
HR/Benefits -- Self-service compensation, leave, health insurance info
Training -- Onboarding modules, skill assessments, certification tracking

External Clients (85+ industries)

Engagement Visibility -- VA hours, task completion, activity breakdown
Client Health Score -- CSAT trends, NPS, engagement trajectory
Strategic Advisory -- Industry intelligence, market dossiers, custom AI dashboards
OA Playbook -- Living document with SOPs, workflows, delegation guides
Market Intelligence -- Competitor monitoring, industry benchmarks, trend analysis
Custom AI Dashboards -- Per-client AI-powered analytics specific to their industry
Communication -- Direct channel to AM, support tickets, meeting scheduler

System Architecture Flow

Google OAuth

RBAC Middleware

Role + Permissions

D1 Database

Users + Config + Cache

Dashboard Render

Personalized View

Current State vs. Target State

Dimension	Current State (Sep 2026)	Target State (Phase 2)
Authentication	Google OAuth on admin portal; employee/client dashboards have no auth gate	Every page behind Google OAuth with session cookie + RBAC middleware
Data Source	Static HTML with hardcoded data; manually updated per deploy	D1-backed with Cloudflare Worker sync jobs pulling live data from HubSpot, Time Doctor, Zoho, GWS
Employee Dashboards	15 static HTML pages deployed (3 OM, 4 AM, 8 TL)	Single dynamic template rendering personalized data per user from D1
Client Dashboards	19 static HTML pages, no login required, URL-guessable	Auth-gated client portal with per-client data isolation from D1
RBAC	Basic: admin vs non-admin check on admin portal only	Full 7-tier hierarchy with granular permissions, data scoping, audit trail
URL Structure	Flat: /dashboard/oa-{name}/ per person	Semantic: /dashboard/{role}/{slug}/ with role-based routing

Phased Roadmap Summary

Phase	Focus	Outcome	Status
Phase 1A	Auth-gate every page	Zero unauthenticated access to any dashboard	In Progress
Phase 1B	URL migration + RBAC	Semantic URLs, full role hierarchy, permission matrix	Planned
Phase 1C	Data pipeline Workers	Live data sync from HubSpot, Time Doctor, Zoho Recruit	Not Started
Phase 1D	Dynamic dashboards	Replace static HTML with D1-backed rendering engine	Not Started
Phase 2	Client portal	Authenticated client dashboards with engagement visibility	Planned
Phase 3	Market intelligence + AI	Per-client AI dashboards, industry intelligence, competitor monitoring	Planned
Phase 4	External tool replacement	Move Time Doctor, Zoho, HubSpot functions into native OA platform	Future

URL Taxonomy: server.outsourceaccess.com

Complete path taxonomy for the OA portal. Green dots indicate paths that exist today. Yellow dots are partially implemented. Red dots are not yet built. Blue dots are planned for a future phase.

Root Paths

/ -- Homepage / redirect to login

/login/ -- Google OAuth login page

/login/callback -- OAuth callback handler

/logout -- Session termination

Admin Paths -- /admin/

/admin/ -- Admin portal (main index.html with sidebar + tabs)

/admin/dashboards/ -- Admin dashboard embeds directory

/admin/dashboards/talent-matching-audit.html -- TA matching audit

/admin/dashboards/ta-matching-audit.html -- TA audit (alternate)

/admin/dashboards/data-mapping.html -- Data mapping reference

/admin/dashboards/database-design-2.html -- This page (enterprise architecture)

/admin/dashboards/agenda.html -- Meeting agenda tool

/admin/dashboards/claude-ai-training.html -- AI training reference

/admin/dashboards/client-expansion-pipeline.html -- Client expansion tracking

/admin/dashboards/oa-melchor.html -- OM: Melchor Moralina dashboard

/admin/dashboards/oa-treve.html -- OM: Treve Gonzaga dashboard

/admin/dashboards/oa-jerome.html -- OM: Jerome Banal dashboard

Employee Dashboard Paths -- /dashboard/ (Current)

Operations Managers (3)

/dashboard/oa-melchor-moralina/ -- OM dashboard

/dashboard/oa-treve-gonzaga/ -- OM dashboard

/dashboard/oa-jerome-banal/ -- OM dashboard

Account Managers (4)

/dashboard/oa-maria-paola-apacible/ -- AM dashboard

/dashboard/oa-beia-ramanauskas/ -- AM dashboard

/dashboard/oa-emily-gerebise/ -- AM dashboard

/dashboard/oa-mia-isabella-gerebise/ -- AM dashboard

Team Leaders (8)

/dashboard/oa-dan-ryan-cabalse/ -- TL dashboard

/dashboard/oa-rose-an-canete/ -- TL dashboard

/dashboard/oa-michael-dansalan/ -- TL dashboard

/dashboard/oa-dawn-may-lumactod/ -- TL dashboard

/dashboard/oa-ma-cristine-magallon/ -- TL dashboard

/dashboard/oa-lovely-palacios/ -- TL dashboard

/dashboard/oa-charlene-solatorio/ -- TL dashboard

/dashboard/oa-maria-hazel-bongoc/ -- TL dashboard

Vision Team (5) -- No dashboards yet

/dashboard/oa-mary-anne-castro/ -- Not built

/dashboard/oa-cleofe-niez/ -- Not built

/dashboard/oa-leonard-aballe/ -- Not built

/dashboard/oa-alva-bayawa/ -- Not built

/dashboard/oa-stephen-bill-de-veyra/ -- Not built

Proposed URL Migration -- /dashboard/{role}/{slug}/

Semantic Pattern (Target)

/dashboard/om/melchor-moralina/ -- OM prefix for operations managers

/dashboard/am/maria-paola-apacible/ -- AM prefix for account managers

/dashboard/tl/dan-ryan-cabalse/ -- TL prefix for team leaders

/dashboard/va/{name}/ -- VA prefix (future)

/dashboard/exec/{name}/ -- Vision Team / executive

Migration strategy: serve both old and new URLs during transition via Cloudflare redirect rules. Old URLs return 301 to new canonical path. No broken bookmarks.

Client Paths -- /client/ (Phase 2)

/client/{company-slug}/ -- Client dashboard root (engagement, health, advisory)

/client/{company-slug}/engagement/ -- VA hours, activity, task completion

/client/{company-slug}/intelligence/ -- Market intel, competitor monitoring

/client/{company-slug}/playbook/ -- SOPs, workflows, delegation guides

/client/{company-slug}/ai/ -- Custom AI dashboards

API Paths -- /api/

/api/auth/session -- Session validation endpoint

/api/auth/login -- OAuth initiation

/api/auth/callback -- OAuth callback

/api/data/users -- User CRUD (partial)

/api/data/org-tree -- Org hierarchy data (partial)

/api/data/dashboards -- Dashboard config CRUD

/api/data/permissions -- Permission management

/api/sync/hubspot -- HubSpot sync trigger

/api/sync/timedoctor -- Time Doctor sync trigger

/api/sync/zoho -- Zoho Recruit sync trigger

Role Hierarchy

The RBAC system uses a 7-tier hierarchy with adjacency-list storage and recursive CTE resolution. Each role inherits the view permissions of all roles below it. Data scoping is additive going up the chain: a TL sees their own VAs' data, an OM sees all TLs' data under them, and so on.

Owner (Brad)

Admin (Sterling, System)

Vision Team (Mary, Cleo, Leonard, Alva, Stephen Bill)

Ops Manager (Melchor, Treve, Jerome)

Account Manager (Paola, Beia, Emily, Mia)

Team Leader (8 TLs)

VA (500+ staff)

Client (External)

Permission Matrix

Permission Category	VA	TL	OM	AM	Vision	Admin	Owner	Client
Own profile / HR info
Own time tracking data
Direct reports' time data
Performance reviews (write)
Client engagement data
Compensation data
Company-wide financials
Admin portal access
User management (CRUD)
Audit log access

Full access Scoped / partial No access

Data Scope Rules

Role	Data Scope	Resolution Method
VA	Own records only (time, HR, benefits, tasks)	WHERE user_id = :current_user
Team Leader	Own records + all VAs in their team	WHERE user_id IN (SELECT employee_user_id FROM employee_reporting WHERE manager_user_id = :current_user)
Ops Manager	Own records + all TLs and VAs in their reporting chain	Recursive CTE traversing employee_reporting from OM down through TLs to VAs
Account Manager	All clients assigned to them + engagement data for those clients	WHERE client_id IN (SELECT client_id FROM client_assignments WHERE user_id = :current_user)
Vision Team	Company-wide data (all employees, all clients, financials)	No scope filter (full SELECT)
Client	Own engagement data only (their VAs, their hours, their advisory docs)	WHERE client_id = :current_client_id (isolated tenant)

Sensitive Data Classification

Category	Examples	Min. Role Required	Classification
Compensation	Salary, bonuses, payroll data, Airwallex wire amounts	exec-only	Restricted
HR Records	Benefits, leave history, disciplinary notes, health insurance	self + manager	Confidential
Client Financials	Billing history, MRR, contract terms, pricing	manager + client	Confidential
Performance Reviews	Evaluation scores, manager notes, improvement plans	manager-only	Confidential
Engagement Metrics	VA hours, activity rates, task completion	employee + client	Internal
Company Directory	Names, roles, team assignments, photos	all employees	Internal

RBAC Database Schema

Core Tables (click to expand SQL)

-- Core user identity CREATE TABLE users ( id TEXT PRIMARY KEY, email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, role TEXT NOT NULL CHECK(role IN ('owner','admin','ops_manager','account_manager','team_leader','va','client')), department TEXT, title TEXT, status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','inactive','suspended')), auth_provider TEXT NOT NULL DEFAULT 'google', avatar_url TEXT, last_login_at TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now')), updated_at TEXT NOT NULL DEFAULT (datetime('now')) ); -- Org hierarchy (adjacency list) CREATE TABLE employee_reporting ( id TEXT PRIMARY KEY, employee_user_id TEXT NOT NULL REFERENCES users(id), manager_user_id TEXT NOT NULL REFERENCES users(id), relationship_type TEXT NOT NULL DEFAULT 'direct' CHECK(relationship_type IN ('direct','dotted','temporary')), started_at TEXT NOT NULL DEFAULT (datetime('now')), ended_at TEXT, UNIQUE(employee_user_id, manager_user_id, relationship_type) ); -- Client-to-employee assignment CREATE TABLE client_assignments ( id TEXT PRIMARY KEY, client_id TEXT NOT NULL, user_id TEXT NOT NULL REFERENCES users(id), assignment_role TEXT NOT NULL CHECK(assignment_role IN ('account_manager','team_leader','va')), is_primary INTEGER NOT NULL DEFAULT 1, started_at TEXT NOT NULL DEFAULT (datetime('now')), ended_at TEXT ); -- Granular permissions CREATE TABLE permissions ( id TEXT PRIMARY KEY, name TEXT NOT NULL, description TEXT, category TEXT NOT NULL, is_sensitive INTEGER NOT NULL DEFAULT 0 ); CREATE TABLE role_permissions ( role TEXT NOT NULL, permission_id TEXT NOT NULL REFERENCES permissions(id), PRIMARY KEY (role, permission_id) ); -- Dashboard access control CREATE TABLE dashboards ( id TEXT PRIMARY KEY, name TEXT NOT NULL, type TEXT NOT NULL CHECK(type IN ('standard','template','custom')), owner_user_id TEXT REFERENCES users(id), config_json TEXT, is_active INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL DEFAULT (datetime('now')) ); CREATE TABLE dashboard_role_access ( dashboard_id TEXT NOT NULL REFERENCES dashboards(id), role TEXT NOT NULL, can_view INTEGER NOT NULL DEFAULT 1, can_edit INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (dashboard_id, role) ); CREATE TABLE dashboard_user_access ( dashboard_id TEXT NOT NULL REFERENCES dashboards(id), user_id TEXT NOT NULL REFERENCES users(id), can_view INTEGER NOT NULL DEFAULT 1, can_edit INTEGER NOT NULL DEFAULT 0, granted_by TEXT REFERENCES users(id), granted_at TEXT NOT NULL DEFAULT (datetime('now')), PRIMARY KEY (dashboard_id, user_id) ); -- Audit trail CREATE TABLE audit_log ( id TEXT PRIMARY KEY, user_id TEXT REFERENCES users(id), action TEXT NOT NULL, resource_type TEXT NOT NULL, resource_id TEXT, details_json TEXT, ip_address TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now')) );

Recursive CTE: Hierarchy Resolution (click to expand)

-- Resolve full reporting chain below a given manager -- Used to determine data scope for OMs and Vision Team WITH RECURSIVE report_chain AS ( -- Base case: direct reports of the target manager SELECT er.employee_user_id, er.manager_user_id, u.full_name, u.role, 1 AS depth FROM employee_reporting er JOIN users u ON u.id = er.employee_user_id WHERE er.manager_user_id = :target_manager_id AND er.ended_at IS NULL AND er.relationship_type = 'direct' UNION ALL -- Recursive case: reports of reports SELECT er.employee_user_id, er.manager_user_id, u.full_name, u.role, rc.depth + 1 FROM employee_reporting er JOIN users u ON u.id = er.employee_user_id JOIN report_chain rc ON rc.employee_user_id = er.manager_user_id WHERE er.ended_at IS NULL AND er.relationship_type = 'direct' AND rc.depth < 10 -- safety limit ) SELECT * FROM report_chain ORDER BY depth, full_name;

Data Pipeline Architecture

The target architecture uses Cloudflare Workers as scheduled sync jobs that pull data from external APIs and write it to D1 (SQLite). The dashboard frontend reads from D1 via Cloudflare Functions API endpoints. Every API response includes data freshness metadata so the frontend can display staleness indicators when data is older than the expected sync interval.

EXTERNAL APIs

HubSpot CRM

Contacts, Deals, CSAT

Time Doctor

Hours, Activity, Screenshots

Zoho Recruit

Candidates, Job Openings

Google Workspace

Drive, Calendar, Sheets

REST / OAuth

CLOUDFLARE WORKERS

hubspot-sync

Every 30 min

timedoctor-sync

Every 60 min

zoho-sync

Every 2 hours

gdrive-sync

Every 4 hours

D1 Binding

D1 + FRONTEND

D1 Database

Cached data store

Functions API

/api/data/* endpoints

Dashboard UI

Personalized render

Sync Schedule

Worker	External Source	Data Synced	Interval	D1 Tables Written	Status
hubspot-sync	HubSpot CRM API	Contacts, companies, deals, tickets, CSAT surveys, engagements	30 min	hs_contacts, hs_companies, hs_deals, hs_tickets, hs_csat	Not Started
timedoctor-sync	Time Doctor API v2	Work logs, activity rates, screenshots, projects, tasks	60 min	td_worklogs, td_activity, td_projects	Not Started
zoho-sync	Zoho Recruit API	Candidates, job openings, interviews, applications	2 hours	zr_candidates, zr_openings, zr_applications	Not Started
gdrive-sync	Google Drive API	Playbook file links, file counts per client folder, last modified dates	4 hours	gd_files, gd_playbooks	Not Started

Data Freshness Metadata

Every API response from the Functions layer includes a freshness envelope so the frontend can show staleness warnings. If data is older than 2x the expected sync interval, a yellow "stale" indicator appears. If older than 4x, a red "very stale" indicator appears.

{ "data": [ ... ], "_meta": { "source": "hubspot", "last_sync_at": "2026-09-02T14:30:00Z", "sync_interval_minutes": 30, "staleness": "fresh", // "fresh" | "stale" | "very_stale" | "offline" "records_in_cache": 1247, "next_sync_expected": "2026-09-02T15:00:00Z" } }

Data Source Schema

data_sources + sync_log tables (click to expand)

CREATE TABLE data_sources ( id TEXT PRIMARY KEY, name TEXT NOT NULL, api_base_url TEXT, sync_interval_minutes INTEGER NOT NULL DEFAULT 60, last_sync_at TEXT, last_sync_status TEXT CHECK(last_sync_status IN ('success','partial','failed')), last_sync_error TEXT, records_synced INTEGER, is_active INTEGER NOT NULL DEFAULT 1 ); CREATE TABLE sync_log ( id TEXT PRIMARY KEY, data_source_id TEXT NOT NULL REFERENCES data_sources(id), started_at TEXT NOT NULL, completed_at TEXT, status TEXT NOT NULL CHECK(status IN ('running','success','partial','failed')), records_created INTEGER DEFAULT 0, records_updated INTEGER DEFAULT 0, error_message TEXT );

Authentication Flow

The portal uses Google OAuth 2.0 (GCP project on brad@outsourceaccess.com) to authenticate all users. After successful OAuth, a session is created in D1 and a HttpOnly cookie is set. Every subsequent request passes through _middleware.js which validates the session cookie against D1 before allowing access.

User

Visits /login/

Google OAuth

Consent screen

Callback

Exchange code

D1 Session

Create + cookie

Dashboard

Personalized

Session Management

Property	Value	Rationale
Cookie Name	oa_session	Prefixed with oa_ to avoid collisions
HttpOnly	Yes	Prevents JavaScript access (XSS protection)
Secure	Yes	Only transmitted over HTTPS
SameSite	Lax	Prevents CSRF while allowing top-level navigations
Expiration	24 hours (sliding window)	Active users stay logged in; inactive sessions expire automatically
Storage	D1 sessions table	Server-side validation on every request; sessions can be revoked instantly
Domain	server.outsourceaccess.com	Scoped to portal domain only

Middleware Architecture

The _middleware.js file runs on every request to Cloudflare Functions. It intercepts the request, validates the session cookie against D1, resolves the user's role and permissions, and either allows the request to proceed or redirects to /login/.

Middleware Decision Flow

Step	Check	Pass	Fail
1	Is path in public allowlist? (/login/, /login/callback, /api/auth/*, static assets)	Allow request to proceed	Continue to step 2
2	Does request have oa_session cookie?	Continue to step 3	302 redirect to /login/?redirect={original_path}
3	Is session token valid in D1 sessions table?	Continue to step 4	Clear cookie, 302 to /login/
4	Is session expired? (> 24h since last activity)	Extend session, continue to step 5	Delete session from D1, clear cookie, 302 to /login/
5	Does user's role have permission for requested path?	Set user context on request, allow through	403 Forbidden page

_routes.json Configuration

The _routes.json file controls which paths are routed through Cloudflare Functions (and therefore through middleware). Static assets are excluded to avoid unnecessary function invocations.

{ "version": 1, "include": [ "/api/*", "/admin/*", "/dashboard/*", "/client/*", "/login/*", "/logout" ], "exclude": [ "/*.css", "/*.js", "/*.png", "/*.jpg", "/*.svg", "/*.ico", "/*.woff2" ] }

Current Auth Gaps

Path / Area	Current Auth State	Risk Level	Fix Phase
/admin/*	Protected -- Google OAuth + admin role check	Low	Done
/dashboard/oa-*/ (15 employee dashboards)	Unprotected -- Public HTML, URL-guessable	High	Phase 1A
/client/*/ (19 client dashboards)	Unprotected -- Public HTML, URL-guessable	High	Phase 1A
/api/data/* endpoints	Partial -- Some auth checks, inconsistent	Medium	Phase 1A
Static assets (CSS, JS, images)	Excluded -- Intentionally public (no sensitive data)	Low	N/A

Summary: 34 dashboard pages (15 employee + 19 client) are currently accessible without authentication. This is the highest-priority security gap and is addressed in Phase 1A.

Platform Component Inventory

Component	Status	Count	Auth State	Data Source	Priority	Notes
Admin Portal	Live	1	Protected	D1 + Static	Maintenance	Google OAuth, admin role gate, sidebar tabs
OM Dashboards	Live	3	None	Static HTML	Critical	Melchor, Treve, Jerome. Hardcoded data, no auth gate.
AM Dashboards	Live	4	None	Static HTML	Critical	Paola, Beia, Emily, Mia. Hardcoded data, no auth gate.
TL Dashboards	Live	8	None	Static HTML	Critical	8 TLs. Hardcoded data, no auth gate.
Vision Team Dashboards	Not Started	0 / 5	--	--	High	Mary, Cleo, Leonard, Alva, Stephen Bill. Need executive-level views.
Client Dashboards	Live	19	None	Static HTML	Critical	URL-guessable. Contains client engagement data.
D1 Database (users, sessions, org)	Partial	1	Protected	D1	High	Users + sessions tables exist. Org tree, permissions, dashboard config tables need creation.
Data Pipeline Workers	Not Started	0 / 4	--	--	High	HubSpot, Time Doctor, Zoho, GDrive sync workers not built.
RBAC System	Basic	--	Partial	D1	High	Currently only admin vs. non-admin. Full 7-tier hierarchy needed.
Login Page	Live	1	--	OAuth	Maintenance	Google OAuth working. Redirect-after-login implemented.
_middleware.js	Partial	1	--	--	Critical	Runs on /admin/. Needs to extend to /dashboard/ and /client/*.
_routes.json	Partial	1	--	--	High	Needs /dashboard/* and /client/* added to include list.
Dynamic Dashboard Engine	Not Started	0	--	--	Planned	Template-based rendering from D1 config. Phase 1D.
Client Portal	Not Started	0	--	--	Planned	Auth-gated client login with isolated data views. Phase 2.
Audit Logging	Not Started	0	--	--	High	Table defined in schema. Middleware integration needed.

Security Gap Summary

Unprotected Pages

15 employee + 19 client dashboards with no auth gate

Missing Dashboards

Vision Team members have no dashboards built

0/4

Sync Workers Built

All dashboard data is static / manually updated

Area Protected

Admin portal (/admin/*) fully behind OAuth + role check

Target-State D1 Schema: 6 Table Groups, 22 Tables

The OA Portal target schema is organized into six logical groups. Every table lives in a single Cloudflare D1 database. Foreign keys enforce referential integrity. The schema supports the full RBAC hierarchy, dynamic dashboard rendering, cached data pipeline, and a complete audit trail.

Auth & Identity (5 tables) Org Structure (3 tables) Client Management (3 tables) Dashboard System (5 tables) Data Pipeline (4 tables) Audit & Security (2 tables)

Entity Relationship Overview

users

Central identity table

sessions

employee_reporting

employee_profiles

client_assignments

dashboards

dashboard_user_access

audit_log

clients

client_contacts

widgets

dashboard_widgets

dashboard_role_access

data_sources

sync_log

cached_data

permissions

role_permissions

departments

api_credentials

security_events

Group 1: Auth & Identity (5 tables)

Core identity, authentication, and permission resolution. The users table is the hub that every other group references.

users PRIMARY HUB

id TEXT PK UUID
email TEXT NOT NULL UNIQUE
full_name TEXT NOT NULL
role TEXT NOT NULL CHECK(owner|admin|ops_manager|account_manager|team_leader|va|client)
department TEXT
title TEXT
status TEXT DEFAULT 'active' CHECK(active|inactive|suspended)
auth_provider TEXT DEFAULT 'google'
avatar_url TEXT
last_login_at TEXT
created_at TEXT DEFAULT now()
updated_at TEXT DEFAULT now()

sessions

id TEXT PK session token
user_id TEXT FK --> users.id
ip_address TEXT
user_agent TEXT
created_at TEXT NOT NULL
expires_at TEXT NOT NULL sliding 24h
last_active_at TEXT

permissions

id TEXT PK
name TEXT NOT NULL e.g. 'view_compensation'
description TEXT
category TEXT NOT NULL e.g. 'hr', 'finance', 'client'
is_sensitive INTEGER DEFAULT 0

role_permissions JUNCTION

role TEXT PK composite
permission_id TEXT FK --> permissions.id

Maps each role to its set of granted permissions. Used by RBAC middleware to check access.

user_permission_overrides NEW

id TEXT PK
user_id TEXT FK --> users.id
permission_id TEXT FK --> permissions.id
action TEXT CHECK(grant|revoke)
granted_by TEXT FK --> users.id
granted_at TEXT DEFAULT now()

Per-user overrides on top of role defaults. Allows granting extra permissions or revoking inherited ones for specific users.

Permission Resolution Order

1. user.role

2. role_permissions

3. + user grants

4. - user revokes

= Final set

Group 2: Org Structure (3 tables)

Models the OA management hierarchy as an adjacency list so any employee's full chain of command can be resolved with a recursive CTE. Supports direct, dotted-line, and temporary reporting relationships.

employee_reporting ADJACENCY LIST

id TEXT PK
employee_user_id TEXT FK --> users.id
manager_user_id TEXT FK --> users.id
relationship_type TEXT CHECK(direct|dotted|temporary)
started_at TEXT DEFAULT now()
ended_at TEXT NULL = active

UNIQUE(employee_user_id, manager_user_id, relationship_type). Use recursive CTE to resolve full chain.

employee_profiles

user_id TEXT PK FK --> users.id
hire_date TEXT
position_title TEXT
department_id TEXT FK --> departments.id
location TEXT e.g. 'CDO', 'ATL'
timezone TEXT
time_doctor_id TEXT external ref
hubspot_contact_id TEXT external ref
zoho_candidate_id TEXT external ref
compensation_tier TEXT SENSITIVE
skills_json TEXT

Extended profile data. Stores cross-references to external system IDs for sync. compensation_tier is exec-only access.

departments

id TEXT PK
name TEXT NOT NULL
head_user_id TEXT FK --> users.id
parent_dept_id TEXT FK --> departments.id
created_at TEXT DEFAULT now()

Self-referential FK allows nested departments (e.g., Operations > Client Services > Team A).

Reporting Hierarchy (resolved via recursive CTE)

Owner (Brad)

Vision Team (Admin)

Ops Managers

Account Managers

Team Leaders

Direct TLs

Group 3: Client Management (3 tables)

Tracks client companies, their designated OA team members (AM, TL, VAs), and client-side contacts who can log in to the portal.

clients

id TEXT PK
company_name TEXT NOT NULL
slug TEXT UNIQUE used in /client/{slug}/
industry TEXT
hubspot_company_id TEXT external ref
status TEXT CHECK(active|churned|onboarding|paused)
onboarded_at TEXT
monthly_rate REAL SENSITIVE
va_count INTEGER DEFAULT 1
created_at TEXT DEFAULT now()

client_assignments JUNCTION

id TEXT PK
client_id TEXT FK --> clients.id
user_id TEXT FK --> users.id
assignment_role TEXT CHECK(account_manager|team_leader|va)
is_primary INTEGER DEFAULT 1
started_at TEXT DEFAULT now()
ended_at TEXT NULL = active

Maps which OA employees are assigned to which clients. A client may have 1 AM, 1 TL, and multiple VAs.

client_contacts

id TEXT PK
client_id TEXT FK --> clients.id
user_id TEXT FK --> users.id
is_primary_contact INTEGER DEFAULT 0
can_view_billing INTEGER DEFAULT 0
added_at TEXT DEFAULT now()

Client-side people who can log into the portal. user_id links to users table (role='client'). Permissions narrowed by can_view_billing, is_primary_contact flags.

Client Data Isolation Model

Client logs in

Resolve client_id

via client_contacts

Scope ALL queries

WHERE client_id = ?

Client sees ONLY their data

Group 4: Dashboard System (5 tables)

Drives the entire dynamic dashboard rendering engine. Dashboards are containers. Widgets are reusable data-display components. Access is controlled at both role and individual user level.

dashboards

id TEXT PK
name TEXT NOT NULL
slug TEXT UNIQUE URL-safe identifier
type TEXT CHECK(standard|template|custom)
owner_user_id TEXT FK --> users.id
description TEXT
config_json TEXT layout, theme, sidebar config
is_active INTEGER DEFAULT 1
created_at TEXT DEFAULT now()

widgets OBJECT LIBRARY

id TEXT PK
name TEXT NOT NULL e.g. 'VA Performance Metrics'
widget_type TEXT CHECK(chart|table|kpi_card|list|iframe|custom)
data_source_id TEXT FK --> data_sources.id
query_template TEXT parameterized SQL or API call
display_config TEXT JSON: chart type, columns, colors
permission_level TEXT CHECK(all-access|client-safe|employee-only|manager-only|exec-only)
category TEXT e.g. 'Billing', 'Performance'
parameters_schema TEXT JSON: which context vars to inject
is_active INTEGER DEFAULT 1

dashboard_widgets JUNCTION

id TEXT PK
dashboard_id TEXT FK --> dashboards.id
widget_id TEXT FK --> widgets.id
position_row INTEGER
position_col INTEGER
width INTEGER DEFAULT 1 grid columns
height INTEGER DEFAULT 1
config_override TEXT per-instance overrides

dashboard_role_access

dashboard_id TEXT FK PK composite
role TEXT PK composite
can_view INTEGER DEFAULT 1
can_edit INTEGER DEFAULT 0

Role-level defaults. "All TLs can view this dashboard." Overridden by dashboard_user_access for individuals.

dashboard_user_access

dashboard_id TEXT FK PK composite
user_id TEXT FK --> users.id
can_view INTEGER DEFAULT 1
can_edit INTEGER DEFAULT 0
granted_by TEXT FK --> users.id
granted_at TEXT DEFAULT now()

Individual overrides. Grant a specific client or specific VA access to a dashboard beyond their role default.

Dashboard Access Resolution (runtime)

Query dashboard_role_access for user's role. Get base set of dashboards user can see.

Query dashboard_user_access for this specific user. Merge with role-level grants (union, not replace).

For each dashboard, load its widget list from dashboard_widgets. Filter widgets by widget.permission_level vs user.role.

Render sidebar nav from accessible dashboards. Render main content from authorized widgets. Inject user context (user_id, client_id, team_id) into widget query_template.

Group 5: Data Pipeline (4 tables)

Manages external API connections, sync scheduling, cached results, and API credentials. The hybrid model caches 90% of data and proxies 10% in real-time for high-freshness needs.

data_sources

id TEXT PK
name TEXT NOT NULL e.g. 'HubSpot', 'Time Doctor'
api_base_url TEXT
sync_interval_minutes INTEGER DEFAULT 60
last_sync_at TEXT
last_sync_status TEXT CHECK(success|partial|failed)
last_sync_error TEXT
records_synced INTEGER
is_active INTEGER DEFAULT 1

sync_log

id TEXT PK
data_source_id TEXT FK --> data_sources.id
started_at TEXT NOT NULL
completed_at TEXT
status TEXT CHECK(running|success|partial|failed)
records_created INTEGER DEFAULT 0
records_updated INTEGER DEFAULT 0
error_message TEXT

cached_data NEW

id TEXT PK
data_source_id TEXT FK --> data_sources.id
entity_type TEXT e.g. 'contact', 'company', 'worksession'
entity_id TEXT external system ID
data_json TEXT full cached payload
scope_client_id TEXT for data isolation
scope_user_id TEXT for data isolation
synced_at TEXT NOT NULL
stale_after TEXT computed from sync_interval

Indexed on (data_source_id, entity_type, entity_id) and (scope_client_id). scope_* fields enable the RBAC middleware to filter results without needing to know each external system's data model.

api_credentials ENCRYPTED

id TEXT PK
data_source_id TEXT FK --> data_sources.id
credential_type TEXT CHECK(api_key|oauth_token|basic_auth)
encrypted_value TEXT AES-256-GCM
expires_at TEXT
created_at TEXT DEFAULT now()
rotated_at TEXT

Never stored in code or env vars. Encrypted at rest. Only decrypted by sync Workers at runtime. Rotation tracked via rotated_at.

Sync Schedule by Source

Data Source	Sync Interval	Data Types	Est. Records	Staleness Display
HubSpot	30 min	Contacts, Companies, Deals, Tickets, Engagements	~5,000	Green <30m, Yellow 30-60m, Red >60m
Time Doctor	60 min	Work sessions, Projects, Tasks, Screenshots	~10,000/week	Green <1h, Yellow 1-2h, Red >2h
Zoho Recruit	2 hr	Candidates, Job Openings, Applications	~2,000	Green <2h, Yellow 2-4h, Red >4h
Google Drive	4 hr	Playbooks, SOPs, Advisory Docs metadata	~500	Green <4h, Yellow 4-8h, Red >8h
Google Chat	Real-time proxy	Space messages (on-demand, not cached)	N/A	Live indicator

Data Pipeline Flow

External APIs

HubSpot, TD, Zoho, GDrive

Sync Workers

Cron-triggered

D1: cached_data

Scoped by client/user

Dashboard API

RBAC-filtered

Frontend

Widget renders

Group 6: Audit & Security (2 tables)

Immutable append-only logs for every sensitive data access, authentication event, and configuration change. Required for compliance and forensic analysis.

audit_log APPEND-ONLY

id TEXT PK
user_id TEXT FK --> users.id
action TEXT NOT NULL e.g. 'login', 'view_compensation', 'export_data'
resource_type TEXT NOT NULL e.g. 'dashboard', 'employee_profile', 'client'
resource_id TEXT
details_json TEXT request context, before/after
ip_address TEXT
user_agent TEXT
created_at TEXT NOT NULL DEFAULT now()

NO UPDATE or DELETE triggers. Rows are write-once. Auto-logged by middleware on every sensitive action. Indexed on (user_id, created_at) and (resource_type, resource_id).

security_events NEW

id TEXT PK
event_type TEXT NOT NULL CHECK(failed_login|unauthorized_access|session_hijack|rate_limit|data_export)
severity TEXT CHECK(low|medium|high|critical)
user_id TEXT NULL for anonymous
ip_address TEXT
attempted_resource TEXT
details_json TEXT
resolved INTEGER DEFAULT 0
created_at TEXT NOT NULL DEFAULT now()

Separate from audit_log. Tracks suspicious and blocked activity. High/critical events trigger admin notification via Cloudflare Worker.

Auto-Logged Actions

Action	Resource Type	Trigger	Sensitive?
login	session	Every successful OAuth callback	No
view_compensation	employee_profile	Any access to compensation_tier field	Yes
view_client_billing	client	Access to monthly_rate or billing widgets	Yes
export_data	varies	Any CSV/Excel download from dashboard	Yes
modify_permissions	role_permissions	Any change to RBAC configuration	Yes
impersonate_user	session	Admin "View As..." feature	Yes
dashboard_access	dashboard	Every dashboard page load	No

Complete Table Inventory (22 tables)

#	Table	Group	Primary Key	Key Foreign Keys	Status
1	users	Auth	id (UUID)	--	Exists
2	sessions	Auth	id (token)	user_id --> users	Planned
3	permissions	Auth	id	--	Planned
4	role_permissions	Auth	(role, permission_id)	permission_id --> permissions	Planned
5	user_permission_overrides	Auth	id	user_id, permission_id	New
6	employee_reporting	Org	id	employee_user_id, manager_user_id --> users	Planned
7	employee_profiles	Org	user_id	user_id --> users, department_id --> departments	New
8	departments	Org	id	head_user_id --> users, parent_dept_id --> self	New
9	clients	Client	id	--	Planned
10	client_assignments	Client	id	client_id --> clients, user_id --> users	Planned
11	client_contacts	Client	id	client_id --> clients, user_id --> users	New
12	dashboards	Dashboard	id	owner_user_id --> users	Planned
13	widgets	Dashboard	id	data_source_id --> data_sources	New
14	dashboard_widgets	Dashboard	id	dashboard_id, widget_id	New
15	dashboard_role_access	Dashboard	(dashboard_id, role)	dashboard_id --> dashboards	Planned
16	dashboard_user_access	Dashboard	(dashboard_id, user_id)	dashboard_id, user_id --> users	Planned
17	data_sources	Pipeline	id	--	Planned
18	sync_log	Pipeline	id	data_source_id --> data_sources	Planned
19	cached_data	Pipeline	id	data_source_id --> data_sources	New
20	api_credentials	Pipeline	id	data_source_id --> data_sources	New
21	audit_log	Audit	id	user_id --> users	Planned
22	security_events	Audit	id	user_id --> users (nullable)	New

Security Architecture: Zero-Trust, Defense in Depth

Every page, API endpoint, and data request is authenticated and authorized. There are zero public pages (except /login/). Security is enforced at the Cloudflare Worker middleware layer before any request reaches D1 or external APIs.

Defense-in-Depth Layers (outside to inside)

Layer 1 Cloudflare Edge (WAF + DDoS + Bot Management)

Requests hit Cloudflare's edge network first. WAF rules block malicious payloads, DDoS mitigation absorbs volumetric attacks, bot detection filters automated probes. All traffic is HTTPS-only with automatic HTTP-to-HTTPS redirect.

Layer 2 Auth Middleware (Session + Google OAuth)

Every request (except /login/ and /api/auth/*) is intercepted by the auth middleware Worker. It validates the session cookie, checks expiration (24h sliding window), and loads the user object. Invalid or missing sessions redirect to /login/.

Layer 3 RBAC Authorization (Permission Resolution)

After authentication, the RBAC layer resolves the user's effective permissions (role defaults + user overrides) and checks them against the requested resource. Unauthorized requests return 403 and log a security_event.

Layer 4 Data Scoping (Row-Level Isolation)

Even after RBAC passes, data queries are scoped to the user's domain. Clients see only their data (WHERE client_id = ?). Managers see only their reports' data (resolved via recursive CTE). No user can ever access another scope's data, even with a valid session.

D1 Database + External APIs

Protected data reached only after passing all 4 layers

Required Security Headers

Header	Value	Purpose
Strict-Transport-Security	max-age=31536000; includeSubDomains	Force HTTPS for 1 year
X-Content-Type-Options	nosniff	Prevent MIME-type sniffing
X-Frame-Options	SAMEORIGIN	Block clickjacking (allow admin iframes)
Content-Security-Policy	default-src 'self'; script-src 'self' 'unsafe-inline'; style-src 'self' 'unsafe-inline' fonts.googleapis.com	Block XSS, restrict resource origins
Referrer-Policy	strict-origin-when-cross-origin	Limit referrer leakage
Permissions-Policy	camera=(), microphone=(), geolocation=()	Disable unused browser APIs
X-Request-ID	auto-generated UUID	Trace requests through audit log

Sensitive Data Classification & Access Rules

Certain data fields require elevated access and trigger audit logging on every read. These categories are enforced at the RBAC layer and cannot be bypassed by dashboard configuration.

Data Category	Examples	Minimum Role	Audit Logged?	Encryption
Compensation	Salary tiers, rates, bonuses, pay history	Owner/Admin only	Every access	At rest (D1)
HR/Personal	Performance reviews, disciplinary records, PIPs	Manager + Admin	Every access	At rest (D1)
Client Financials	Monthly rates, billing history, contract terms	Owner/Admin + AM (own clients)	Every access	At rest (D1)
API Credentials	HubSpot keys, Time Doctor tokens, OAuth secrets	Owner only	Every access	AES-256-GCM
Export Payloads	CSV/Excel downloads of any dataset	Manager + Admin	Every export	N/A (transit only)
VA Performance	Hours, task completion, screenshots	TL (own team) + Manager + Admin	Standard	At rest (D1)
Client Health	CSAT scores, NPS, engagement metrics	AM (own clients) + Manager + Admin + Client	Standard	At rest (D1)

Authentication Flow (step-by-step)

User hits any URL on server.outsourceaccess.com

Cloudflare Worker middleware intercepts the request before it reaches Pages.

Middleware checks for session cookie (oa_session)

Reads cookie value, queries sessions table in D1. Checks: exists? not expired? user.status = 'active'?

No valid session: Redirect to /login/

Store the original URL in a query param (?redirect=/dashboard/om/mary) so user lands back after login.

Valid session: Attach user object to request context

User's role, permissions, client_id (if client), and reporting chain are available to all downstream handlers. Slide session expiry forward by 24 hours.

Google OAuth callback (/api/auth/callback)

Validates the OAuth state token. Verifies the user's email exists in the users table and status is 'active'. Creates a new session row in D1. Sets HttpOnly, Secure, SameSite=Lax cookie. Logs the login in audit_log.

RBAC route check

Middleware checks if the user's resolved permissions include access to the requested URL pattern. Admin override: owner and admin roles can access any page (including "View As..." impersonation).

Serve the page or return 403

If authorized, serve the page with user context injected. If not, return a styled 403 page explaining the user doesn't have access and suggesting they contact their admin.

Rate Limiting & Session Security

Rate Limiting

Auth endpoints: 5 requests/minute per IP
API endpoints: 60 requests/minute per session
Data export: 10 exports/hour per user
Admin write ops: 30/minute per user
Exceeded limits return 429 + log security_event

Session Cookie

Name: oa_session
HttpOnly: true (JS cannot read it)
Secure: true (HTTPS only)
SameSite: Lax (CSRF protection)
Expiry: 24h sliding window
Value: Cryptographic random token (256-bit)

CORS Policy

Allowed origin: server.outsourceaccess.com only
No wildcard (*) ever
Credentials: include (cookies)
Methods: GET, POST, PUT, DELETE
Preflight cached 1 hour

Error Handling

Never leak stack traces or DB errors
401: "Authentication required"
403: "You don't have access to this resource"
500: "Something went wrong" + unique request ID
Full error details in audit_log only (admin-visible)

Implementation Phases

Each phase builds on the previous one. Dependencies are called out explicitly. Estimated timelines assume Sterling execution speed (not human dev timelines).

Phase 1A: Auth Everything

In Progress -- Est. 2-3 hours Sterling time -- No external dependencies

Extend _middleware.js to intercept /dashboard/* and /client/* paths
Update _routes.json to include /dashboard/* and /client/* in function routing
Add redirect-after-login support: store original URL in session, redirect after OAuth callback
Create sessions table if not exists; implement sliding window expiration (24h)
Add user lookup: on first OAuth login, check if email exists in users table; if not, create with role='va' (default) and flag for admin review
Deploy and verify: every dashboard page redirects to /login/ for unauthenticated requests

Phase 1B: URL Migration + Full RBAC

Planned -- Est. 4-6 hours Sterling time -- Depends on: Phase 1A complete

Create D1 tables: employee_reporting, client_assignments, permissions, role_permissions, dashboards, dashboard_role_access, dashboard_user_access, audit_log
Seed users table with all 20 employees (15 existing + 5 Vision Team) and their roles
Seed employee_reporting with actual org hierarchy (who reports to whom)
Build /dashboard/{role}/{slug}/ URL pattern with Cloudflare redirect rules for old URLs (301)
Implement permission-checking in middleware: after session validation, check if user's role has access to requested path
Build recursive CTE for data scope resolution (OM sees their full reporting chain)
Add audit_log entries for all login/logout/access events

Phase 1C: Data Pipeline Workers

Not Started -- Est. 6-8 hours Sterling time -- Depends on: Phase 1B (D1 tables) + API credentials configured

Build hubspot-sync Worker: scheduled every 30 min via cron trigger; syncs contacts, companies, deals, tickets, CSAT to D1 tables
Build timedoctor-sync Worker: scheduled every 60 min; syncs work logs, activity rates, projects
Build zoho-sync Worker: scheduled every 2 hours; syncs candidates, job openings, applications
Build gdrive-sync Worker: scheduled every 4 hours; syncs playbook file links and metadata
Create data_sources and sync_log tables for monitoring
Implement staleness metadata on all API responses
Build admin monitoring panel: sync status, last run times, error counts, manual re-trigger button

Phase 1D: Dynamic Dashboard Rendering

Not Started -- Est. 8-12 hours Sterling time -- Depends on: Phase 1C (live data in D1)

Build dashboard template engine: single HTML template that reads widget configuration from D1
Replace 15 static employee dashboards with dynamic renders from D1-backed widget configs
Implement widget library: ~150 reusable components, each with a data source binding and permission level
Build admin Dashboard Builder UI: drag-and-drop widget assignment per user/role/group
Data scoping at widget level: each widget query filtered by user's scope (via recursive CTE)
Staleness indicators on each widget: green/yellow/red dot showing data freshness

Phase 2: Client Portal

Planned -- Est. 6-10 hours Sterling time -- Depends on: Phase 1A (auth) + Phase 1C (data pipeline)

Build client login flow: Google OAuth with client email whitelist in D1
Create /client/{company-slug}/ URL structure with per-client data isolation
Replace 19 static client dashboards with dynamic, auth-gated, D1-backed views
Client-visible widgets: engagement metrics, VA hours, task completion, CSAT scores, playbook access
Client-hidden widgets: internal performance reviews, compensation data, inter-client comparisons
OA Playbook integration: link client's Google Drive playbook folder with embedded viewer
Client onboarding flow: AM invites client via email, client sets up Google OAuth, first-login walkthrough

Phase 3: Market Intelligence + AI Dashboards

Planned -- Est. 12-20 hours Sterling time -- Depends on: Phase 2 (client portal)

Per-client industry intelligence dashboards: competitor monitoring, market trends, benchmark data
AI-powered analytics: natural language queries against client's engagement data
Automated insights: weekly AI-generated summaries of client health trends
Custom AI dashboards: per-client widgets built by Sterling based on industry and client needs
Integration with Gemini Enterprise Agent Builder for conversational analytics
Strategic advisory document hub: industry dossiers, best practices, expert knowledge linked per client

Phase 4: In-Platform Tool Replacement

Future -- Est. 30-60 hours Sterling time -- Depends on: Phase 3 complete + Brad directive

Replace Time Doctor dependency: build native time tracking within OA portal
Replace Zoho Recruit dependency: build native ATS/recruiting pipeline within OA portal
Reduce HubSpot dependency: mirror critical CRM functions natively for faster access
Native ticketing system: replace HubSpot tickets with portal-native support system
Evaluate: build vs. integrate decision for each tool based on ROI and maintenance burden
Goal: single pane of glass for all OA operations, reducing context switching and vendor lock-in

Dependency Chain

1A: Auth

1B: RBAC

1C: Pipeline

1D: Dynamic

2: Client

3: AI/Intel

4: Replace

Enterprise Architecture: OA Portal Platform