SysEngineering

Google Workspace Admin Assistant

A read-only, multi-tenant Slack chatbot for Google Workspace administration. Ask it anything in plain English (“what are the 2SV settings for Hillspire’s workspace?”, “is jdoe@scsp.ai suspended?”, “who are the super admins across all workspaces?”) and it routes the question to the right Google Admin Console(s) on the backend, runs read-only Admin SDK / Cloud Identity / Chrome Management lookups, and writes the answer itself.

The LLM backbone is Claude Opus 5. OpenAI and Vertex paths remain in agent.py as fallbacks, selectable with LLM_PROVIDER without a code change.

Architecture

Slack (Events API)
  -> Cloud Run: FastAPI (main.py)
       -> agent.py: Claude Opus 5 tool-calling loop (no hardcoded intents)
            -> workspace.py: ~20 read-only tools, each taking a `workspace`
               parameter, executed with per-tenant delegated credentials
                 -> Google Admin SDK / Reports / Cloud Identity / Chrome APIs

• main.py — Slack signature verification, event filtering, allowlist, placeholder/edit message flow, per-thread conversation memory (in-process).
• slack_images.py — downloads and prepares screenshots attached to a message.
• agent.py — the agent loop. The model receives the workspace routing table in its system prompt plus tool schemas, decides which tools to call against which tenants (parallel calls supported), then composes the Slack reply.
• workspace.py — tenant routes, per-route service-account credentials with domain-wide delegation, and the read-only tool registry.

Read-only guarantee

Three enforcement layers:

1. Every OAuth scope requested ends in .readonly (test_assistant.py::test_every_scope_is_readonly enforces this).
2. Only list/get/report API calls exist in workspace.py. There is no code path that mutates tenant data.
3. Domain-wide delegation in each tenant only needs the read-only scope list (dwd_readonly_scopes.txt) for this assistant’s lookups.

Multi-tenant routing

Routes come from JSON via one of (first match wins):

• GW_ROUTES_JSON — inline JSON.
• GW_ROUTES_FILE — path to a JSON file.
• GW_ROUTES_SECRET — Secret Manager resource (full projects/.../secrets/... path, or short name + GW_ROUTES_SECRET_PROJECT).

The production config reuses the helpdesk module’s routes secret (projects/okta-workflows-offboard/secrets/slack-helpdesk-routes-sandbox), so tenants are maintained in exactly one place. Route shape (same as the helpdesk module):

[
  {
    "key": "hillspire-prod",
    "label": "Hillspire",
    "domains": ["hillspire.com", "ericschmidt.com"],
    "adminEmail": "admin@hillspire.com",
    "serviceAccountSecret": "gw-sa-hillspire"
  }
]

GW_ROUTE_SA_SECRET_OVERRIDES (JSON object, route key -> secret resource) overrides a route’s service-account secret. Used so the sandbox route keeps using this project’s own already-authorized service account.

Routes are cached for the life of the instance; deploy a new revision (or restart) after changing them.

Environment variables

Variable	Purpose
SLACK_BOT_TOKEN / SLACK_SIGNING_SECRET	Slack app credentials (Secret Manager).
LLM_PROVIDER	anthropic (default in production), openai, or vertex.
ANTHROPIC_API_KEY	Claude key (Secret Manager: gw-assistant-anthropic-api-key).
ANTHROPIC_MODEL	Default claude-opus-5.
ANTHROPIC_EFFORT	Default low, for latency. Raise for harder questions.
OPENAI_API_KEY	OpenAI key (Secret Manager) — fallback provider.
OPENAI_MODEL	Default gpt-5.4-mini.
OPENAI_MAX_OUTPUT_TOKENS	Default 1500 (clamped 300-4000).
OPENAI_REASONING_EFFORT	Optional reasoning effort.
GW_ROUTES_SECRET / GW_ROUTES_JSON / GW_ROUTES_FILE	Workspace routes.
GW_ROUTE_SA_SECRET_OVERRIDES	Per-route SA secret overrides (JSON).
GW_DEFAULT_WORKSPACE	Optional route key assumed when the user says “our” without naming a workspace. Unset = the bot asks.
GOOGLE_CLOUD_PROJECT	Fallback project for short serviceAccountSecret names.
SLACK_ALLOWED_USER_IDS	Optional comma-separated Slack user allowlist.
ALLOW_UNVERIFIED_SLACK_REQUESTS	Local dev only.
LOG_LEVEL	Default INFO.

Onboarding a tenant

1. The tenant must be in the routes secret (it already is if the helpdesk module covers it).
2. In that tenant’s Admin Console, append the scopes from dwd_readonly_scopes.txt to the existing domain-wide delegation entry for its gw-sa-* service account. Append — do not replace.
3. Ask the bot something about that workspace. A 403 answer means step 2 is not done yet; everything else keeps working.

Tests

cd integrations\google_workspace_admin_assistant
.\.venv\Scripts\python.exe -m unittest test_assistant -v

CSV group membership exports

Ask Greg in natural language, for example:

Export allstaff@schmidtentities.com to CSV, including every nested group.

Greg attaches two UTF-8 CSV files in the requesting DM or thread:

• Members: one row per user membership path. top_level_group identifies the immediate branch under the root (or the root for a direct member), source_group is the group that directly holds the user, and group_path preserves the full nesting. Also includes workspace, user email, source-group role, membership status, direct/nested classification, and depth. A user in several groups appears under each path; different email addresses are not assumed to be the same person. Names and account suspension are not enriched.
• Groups: one row per group path, including empty groups. Counts direct users and nested groups observed, exported descendant membership rows, and status/detail. For a partial group, observed counts can be lower bounds.

Exports requested in a channel are shared with that channel’s audience, like Greg’s text replies. Use a DM when the report should be private to you.

The backend reads every membership page and resolves nested groups through their owning workspace’s delegated credentials. Every root or nested group domain, including aliases, must be present in an owning route. Unknown or excluded nested domains are reported without querying them through another workspace; an unconfigured root is rejected before a scan. Shared groups are fetched once and represented on every path. Circular nesting, unreadable groups, unsupported member types and limits are recorded in the summary. Any gap marks the export partial, including its filenames. Membership status is the Google Group member status, not a user account’s suspension status.

Rows are generated directly from API records, outside the model’s result size and response limits. Spreadsheet formula characters are escaped and CSV quoting is handled by Python’s CSV library. Files exist in memory until upload and are shared only in the conversation selected by the signed Slack event. The model has no destination/channel parameter. Existing user/team allowlists still apply; Google Workspace permissions stay read-only.

Slack setup: add files:write to Greg’s production app’s Bot Token Scopes under OAuth & Permissions, then reinstall the app to Hillspire. Updating this repo’s manifest alone does not update an installed app. If reinstallation changes the token, update gw-assistant-prod-slack-bot-token in Secret Manager and roll a new revision. Missing scope fails before the group scan and produces setup instructions.

V1 limits: one root per request and one active export per Cloud Run instance; 180 seconds of traversal (plus bounded in-flight API calls), 500 group lookups, 2,000 group paths, depth 25, 100,000 records per group/export, 1,000 pages per group, and a conservative 20 MB membership CSV budget. Hitting a limit returns a partial report. The worker uses the existing Cloud Run background-task model, with CPU always on; an instance restart can interrupt an export. A durable job queue is a later improvement.

Tests: python -m unittest test_assistant test_group_export -v.

Workspace reports (CSV)

Anything that covers many accounts, drives or folders is a report: Greg queues it (workspace_report), and after his reply the report is built in code, its CSVs are attached in the same DM or thread, and a summary written from the data is posted under his reply. The model never sees the rows, so it cannot miscount or invent them. One request can queue up to four, for example InfoSec’s audit question:

I need a focus report on SCSP: accounts that sent no email in 90 days, accounts inactive for 60 days, top 10 users by My Drive size and file count, the My Drive vs shared drive split, top shared drives by size with who has access, and the top 10 folders shared inside and outside the org.

Report	Answers	CSV	Needs Drive scopes
user_activity	no sign-in for N days (default 60); no email sent in N days (default 90, max 180); storage per account	one row per account	no
top_drive_users	top N by My Drive storage (default 10, max 25), with owned file and folder counts	one row per ranked account	file counts only
shared_drives	every shared drive by size, item count and members, internal vs external	drives, and one row per member	yes
folder_sharing	My Drive folders shared beyond their owner: top N outside the organization, top N only within it	shared folders, and accounts read	yes
user_list	accounts picked out by conditions on what Google records about them (address, domain, name, title, department, employee type, company, manager, employee ID, building, org unit, status, admin role, 2-Step, guest, last sign-in, created…); or every account. One workspace or every workspace	one row per account with its workspace, and (every workspace) which workspaces were read	no

The My Drive vs shared drive split stays a quick usage_report answer.

Account lists (user_list, 2026-11-07). “Every contractor across all our workspaces: their addresses have .consultant in them.” Google’s Directory search matches only from the start of a word, so email:*consultant* finds nothing and an empty search proves nothing. user_list reads every account (only the fields it can test or show) and matches in code. With workspace: "all" every workspace is read in parallel with its own credentials into one CSV with a workspace column, plus a coverage CSV (read / partial / not connected / not read, accounts read, matches). A workspace that can’t be read is named in the summary and makes the report partial: it is never a silent zero. Only user_list accepts all.

Conditions (user_query.py, 2026-11-07). “Every active account with .consultant, .contractor or .advisor in the address, across every workspace, in one CSV”: with one piece of text per report, Greg ran three reports and told Adam to merge them. Now a request is a list of conditions, so any combination of what Google keeps on an account is one report:

• Every condition must hold. A condition is {field, op, values} and holds when any value matches (for not_contains / not_equals / not_under, when none does), so alternatives for one field are one condition: [{field: email, op: contains, values: [.consultant, .contractor, .advisor]}, {field: status, op: equals, values: [active]}]. {any_of: [...]} is OR across fields (an address with .consultant or “contractor” in the title).
• Fields: text (email, domain, alias, first_name, last_name, full_name, title, department, employee_type, company, cost_center, manager, employee_id, building, suspension_reason: contains, equals, starts_with, ends_with, their not_ forms, is_empty, is_not_empty); org_unit (under = the unit and below); status and admin (equals); yes/no flags (two_step_enrolled, two_step_enforced, guest, mailbox_setup, must_change_password, in_directory); dates (last_sign_in, created: before, on or after, within / not within N days, never, ever; never signed in counts as before any date and as not within, so “not since June 1” includes it).
• email is the primary address only, never aliases. In Hillspire, 16 of the 18 accounts with .consultant only in an alias were consultants who became employees and kept the old address. Aliases are their own field, used only when asked.
• Never read or offered: recovery email and phone, phone numbers, home addresses, gender. A test pins it.
• Structured on purpose. The model picks a field and an op from fixed lists that the tool schema and the code share (a test pins that too); nothing is parsed from free text and nothing reaches Google as a query. A bad condition is refused with the reason, so the model can fix it.
• The CSV has name, status, suspension reason, org unit, title, department, employee type, company, manager, employee ID, building, admin role, 2-Step, guest, created, last sign-in and matched_on (which alternative hit, e.g. address contains ".advisor"); the summary counts each alternative (“By match”).
• Not filterable here: group membership (export_group_members), licenses, and email or Drive activity (user_activity, top_drive_users). Greg says so.
• The #268 parameters email_contains, title_contains, org_unit and status are still accepted (one value or a list) and become conditions.

Live check, 2026-11-07 (read-only, counts only): the three-marker active list read all 30 workspaces (3,003 accounts) and found 55: 40 .consultant, 15 .advisor, no active .contractor.

How the numbers are made. Last sign-in is the latest of the Directory’s lastLoginTime and the usage report’s last login and last SSO times. Emails sent are summed from Google’s daily per-user Gmail report over the window, which ends at the most recent day Google has published (2–4 days behind); a day it can’t read makes the report partial and is named. An account with no Gmail data at all is unknown, never “sent nothing”. Shared drive sizes are added up file by file, as the drive’s highest-role active member, including items in trash; a drive with only group members has no size. Folder sharing reads each active account’s own folders; a subfolder whose sharing is only its parent’s is counted under the parent, and folders inside shared drives are left to the shared drive report. “Top” means link/public access first, then most outside people or groups (external), and organization-wide first, then most people or groups (internal).

Drive scopes are opt-in per workspace (GW_DRIVE_REPORT_WORKSPACES, SCSP only). Asking Google for a scope a tenant has not authorized fails the whole token, so Drive credentials are separate, carry one scope each, and are minted only for listed workspaces. A refused Drive token marks the report partial and never marks the workspace as not onboarded. drive.metadata.readonly does the reading; drive.readonly is used for one call, listing the domain’s shared drives, and a test fails if anything else uses it. No call downloads, exports or changes a file.

Summaries contain tenant-written names (drives, folders), so they are escaped for Slack, skip the Markdown sanitizer, and are kept out of the conversation history the model sees next turn; history records only which report was delivered.

Limits: one report at a time per instance, 7 minutes per report, 6 parallel reads (for user_list across every workspace: 6 workspaces at a time, one 7-minute budget for all of them). Hitting a limit gives a partial report with the gap stated, and counts become minimums.

Tests: python -m unittest test_workspace_reports test_user_query -v.

Mail delivery log (2026-10-28)

“A coworker emailed three groups and nothing came back. Why?” mail_delivery_log reads Gmail’s delivery log, the data behind the Admin console’s Email Log Search, through the Reports API (applicationName=gmail). No new scope: it uses admin.reports.audit.readonly, which every onboarded workspace already grants.

• Delivery stages only: sent, received, bounced (with the receiving server’s reason), quarantined, released, spam, rate limited, send started, other delivery stage. The same log records what recipients then do (opened, read, replied, link clicked, forwarded); those are never requested. Each stage is fetched with its own server-side filter, anything else that comes back is dropped, and gmail stays out of audit_events. Tests pin all three. Decided by Adam on 2026-10-28.
• Whose log. Every event belongs to one account (mailbox_email): a sender’s log shows what they sent and any bounces; a recipient’s shows what arrived; a group’s address works too, showing each message arriving and being passed on to members. A received event does not name its sender (Google leaves that field empty through this API), so one message is followed between mailboxes by subject or Message-ID.
• Shown: time, stage, mailbox, recipients (first 5), subject, Message-ID, and a bounce’s reason. Not shown: IP addresses, attachments, spam scoring internals.
• Never message content. Greg holds no Gmail scope, so no body or attachment can be read; this log is metadata. The subject is the one sensitive field: it reaches the model and the Slack reply, as it would an admin in Email Log Search. Adam chose to include subjects on 2026-10-28: following one message between mailboxes needs them.
• Limits: a 30-day window at most (Google’s rule), default the last 24 hours; up to 30 events, newest first, trimmed to fit one tool result, and marked incomplete when more match. Gmail logs depend on the workspace’s edition; one that keeps none returns an empty log, which Greg reports as such.
• Not readable: a group’s posting permissions and moderation queue. The Groups Settings API has no read-only scope, and Greg never takes a write scope, so Greg names the Admin console path instead.

Verified against Schmidt Entities on 2026-10-28 (structure and counts only). Tests: python -m unittest test_mail_delivery_log -v.

Images and screenshots (2026-10-28)

Attach screenshots to a message and Greg reads them with the question: a group’s member list, a bounced email, an Admin console error. Up to 10 per message; a message with only images works too. slack_images.py does the reading.

• Slack scope files:read downloads them. Slack sends a message with attachments as subtype file_share; before this, Greg dropped that subtype with every other one, so a question sent with screenshots got no reply at all.
• Only after the allowlist. A person who can’t use Greg never has a file fetched. The bot token goes only to files.slack.com, redirects are not followed, and an event from Greg’s own bot user (his CSV uploads) is never treated as a person’s.
• Decoding is the type check. PNG, JPEG, GIF and WebP; Slack’s label proves nothing, and a token without files:read gets an HTML sign-in page back, not an error. An image within the API’s limits is sent untouched; one over 3.75 MB (5 MB as base64, the strictest per-image limit on any Claude platform) or over 8000 px is shrunk to 2576 px on the long edge, which is all the model reads anyway. iPhone photos (JPEGs with an MPF segment, which Pillow calls MPO) are always re-encoded as a plain JPEG of the first frame, turned upright from their EXIF. A message’s images share 15 MB (20 MB encoded) between them, so ten large ones still fit one request on the strictest platform Greg can use (30 MB, Vertex); each gets an equal share and is shrunk further only if it’s over it.
• Everything else is named, not read. A PDF or document becomes a service note, so Greg says he can’t open it and asks for a screenshot or the text.
• Images are evidence, not data. The prompt has Greg confirm what matters with lookups and say which facts came from the image; text inside an image is never instructions.
• Seen once. Images go to the model in their own turn only. History keeps a note naming them, so a follow-up that needs detail Greg didn’t write down gets “send it again” instead of a guess.

Tests: python -m unittest test_slack_images -v.

Weekly digest

digest_job.py posts one weekly Slack message covering every connected organization: security items that need attention now, a standing licenses section, changes since last week, and dormancy trends. It reuses this service’s read-only credential layer, so it needs no extra scopes and no Admin Console work. Deployed as a separate Cloud Run Job; see DIGEST.md.

Daily license alerts

license_alert_job.py checks every organization’s Google Workspace seats each weekday and posts to the digest channel only when one drops to 3 or fewer free (@-mentioning Anthony Xu and Debashis Saha) or recovers. Same read as the digest’s licenses section, judged per SKU, no new scopes. Google’s numbers run 2-4 days behind, so the threshold is the buffer. A separate Cloud Run Job; see LICENSE_ALERTS.md.

Scheduled group inventory

group_inventory_job.py reuses this service’s multi-tenant read-only credential layer to inventory actual Google Group membership and reconcile it against the HR-backed extraction workbook. It is deployed as a separate Cloud Run Job; see GROUP_INVENTORY.md.

Deploy

See HANDOFF.md for the full no-traffic-revision deploy flow. Short version:

gcloud run deploy google-workspace-admin-assistant `
  --project gw-admin-assistant --region us-central1 `
  --source . --no-traffic --tag <tag> --revision-suffix <suffix>