GCP service-account refactor — post-state SA→workload map + service@ retirement + billing cost-table write gap
Numbered T-148 (2026-07-02). T-147's decision log and EOP Local Assistance (fork)'s handoff referred to this as "T-130", but T-130 was already taken by the unrelated Production Firestore getAll crash task (done 2026-06-28). Owner assigned T-148 (real next-free): "T-148 and proceed." T-147's pointer was repointed T-130 → T-148 to match.
Why (owner + cross-agent, 2026-07-02)¶
Lands the previously "pre-task-log" GCP service-account refactor onto the board, and records the fix for a
write-path gap it caused. Discovered by EOP Local Assistance (fork): the cost-table ingest writes a custom
table (gcp_billing_cost_table_backfill) that used to live inside the Google-managed gcp_billing_export
dataset, and on Vercel prod runs as the least-privilege bq-billing-reader@ — so bigquery.tables.create
was denied. Collateral from moving billing off owner-scoped service@: reads carried over, the one write
path did not. Full symptom + decision log: T-147. Decision record: docs/decisions/2026-07-02-billing-derived-dataset-split.md.
Owner approval — option 1 / best end-state (2026-07-02, verbatim):
"I have picked option 1 (Best End-State)."
Derived/computed tables move to their own gcp_billing_derived dataset; raw gcp_billing_export stays
read-only for every app identity.
Post-refactor SA → workload map¶
Retire legacy shared service@aote-pms.iam.gserviceaccount.com (had roles/owner + 15) in favour of
per-workload least-privilege SAs. Pre-state snapshot: docs/playbooks/sa-audit-context.md (2026-05-09);
this task is the post-state record.
| SA | Workload | Intended IAM | Writes? |
|---|---|---|---|
service@ (retiring; strip DEFERRED) |
legacy shared | still roles/owner + 15 as of 2026-07-02 |
— |
drive-receipts@ |
Telegram receipt-bot Drive uploads | Drive-level Content Manager (receipts folder); 0 project IAM | Drive |
drive-accounting@ |
WOPC signing + accounting Drive writes | Drive-level Content Manager (ERL shared drive); 0 project IAM | Drive |
gmail-invoice-fetcher@ |
Gmail Cloud Function (scaffold; fn never deployed) | DWD gmail.readonly; 0 project IAM |
no |
bq-billing-reader@ |
GCP Billing BQ reads + derived cost-table writes | project bigquery.dataViewer + bigquery.jobUser; dataset WRITER on gcp_billing_derived |
derived dataset only |
workspace-admin@ |
Workspace Admin SDK | DWD, 5 read-only scopes; 0 project IAM | no |
firebase-rules-deployer@ |
CI auto-deploy of firestore.rules |
roles/firebaserules.admin |
rules |
firebase-adminsdk-fbsvc@ (NOT part of the refactor) |
Next.js server-side Firestore | datastore.owner + firebase.sdkAdminServiceAgent |
Firestore |
Cost-table write gap — fix (option 1)¶
Done (this task):
- Created dataset aote-pms:gcp_billing_derived (location US, matching the export dataset).
- Copied the existing 711-row gcp_billing_cost_table_backfill into it (partitioning/clustering preserved) — history carried over.
- Granted bq-billing-reader@ dataset WRITER on gcp_billing_derived only (BigQuery dataset ACL; add-iam-policy-binding needs project allowlisting that isn't enabled). gcp_billing_export ACL untouched → raw export not app-writable.
- Reused bq-billing-reader@ as the single billing identity (it already has project dataViewer for export reads + jobUser; the WRITER grant covers the derived write) and pinned it explicitly in Vercel prod + preview: GCP_BILLING_CLIENT_EMAIL / GCP_BILLING_PRIVATE_KEY = bq-billing-reader@. This resolves the cleared-override drift (see below) and makes the billing identity deterministic instead of depending on the GOOGLE_* fallback.
- Set GCP_BILLING_DERIVED_DATASET_ID=gcp_billing_derived in Vercel prod + preview.
- Code (upstream, another agent): 4f5e334c routes the write to GCP_BILLING_DERIVED_DATASET_ID, falling back to GCP_BILLING_DATASET_ID until provisioned. tsc clean.
Status — write gap CLOSED + verified (2026-07-02):
- ✅ Prod redeployed; code 4f5e334c live. Real ingest verified by EOP Local Assistance (fork): POST /api/gcp-billing/cost-table-ingest → HTTP 200, 94 rows → gcp_billing_derived.gcp_billing_cost_table_backfill now 805 (was 711). Raw gcp_billing_export stays read-only for every app identity. Closure on the board: T-147 (commit 83a803d6).
- ✅ Old gcp_billing_export.gcp_billing_cost_table_backfill dropped (Service Account (Local), 2026-07-02; the 711 rows were already copied to gcp_billing_derived, verified intact — no data loss). Only gcp_billing_csv_backfill + Google-managed export tables remain in that dataset.
- ✅ Board mirrored to the orphan taskboard branch (iCloud vault retired).
Identity-choice deviation (from §5 of the cross-agent reply): the deployed write identity is bq-billing-reader@ itself (granted dataset WRITER on gcp_billing_derived + pinned via GCP_BILLING_CLIENT_EMAIL/_PRIVATE_KEY), not a separate bq-billing-writer@. So that SA is now read on gcp_billing_export + write on gcp_billing_derived — no longer strictly read-only. The least-privilege goal still holds (raw export is unwritable by any app identity). A tighter reader/writer split (dedicated bq-billing-writer@ + a separate ingest client config) remains a clean optional follow-up.
Remaining:
- service@ role-strip — UNBLOCKED but DEFERRED. The last BigQuery write path is now off service@, so the technical blocker is cleared (EOP Local Assistance (fork) confirms "clear to strip"). But the owner's standing instruction (2026-07-02) is "continue to defer service@" — so the strip is NOT proceeding without the owner's explicit go. When authorized: re-audit service@'s current usage first (main has advanced ~130 commits since the audit), then strip the 16 bindings → 30-day soak → delete.
- Consolidation candidate: scripts/ingest-gcp-csv-backfill.ts (manual, human-run) writes a separate gcp_billing_csv_backfill into gcp_billing_export; ideally also moves to gcp_billing_derived.
Env drift reconciled (2026-07-02)¶
vercel env pull --environment=production confirmed:
- GCP_BILLING_CLIENT_EMAIL = bq-billing-reader@ (now pinned by this task; was blank, causing the fallback).
- GOOGLE_CLIENT_EMAIL = herald@aote-pms.iam.gserviceaccount.com — NOT bq-billing-reader@ as previously assumed. So the pre-fix ingest would have resolved to herald@ via the fallback (whatever perms/validity that key has), reinforcing why pinning GCP_BILLING_CLIENT_EMAIL explicitly was correct. Open thread: who/when repointed GOOGLE_CLIENT_EMAIL to herald@, and whether GOOGLE_PRIVATE_KEY is herald@'s rotated or historically-broken key — affects other GOOGLE_*-dependent fallback paths (Drive, etc.), tangential to billing. Flag for the SA audit follow-up ([[project_sa_audit_aote_pms]] equivalent).
Decision log¶
2026-07-02 — opened; fix landed (attestation: read AGENTS.md + scanned board by scope — no existing task
covers the SA map; T-130 collision resolved → T-148). Proposed: Service Account (Local). Approved: owner
(quoted above). Rationale + rejected alternatives (dedicated writer SA / dataEditor on export / table-level
IAM): see the ADR. Verified: gcp_billing_derived shows 711 rows in US; WRITER present on derived, absent on
export; GCP_BILLING_* pinned to bq-billing-reader@ (env pull confirmed); code 4f5e334c tsc-clean on main.
Blast radius: GCP billing BigQuery + lib/gcpBilling/costTableIngest.server.ts + Vercel env — no overlap with
the parallel Firebase-Auth/client-SDK/Rules effort (firebase-adminsdk-fbsvc@ + custom claims, untouched).
Source: Service Account (Local) · local session ca159e39-42a3-4569-b1cd-6e006f6639c3
2026-07-02 (later) — write gap verified closed; service@ strip HELD. Redeploy + live ingest confirmed
by EOP Local Assistance (fork): POST /api/gcp-billing/cost-table-ingest → HTTP 200; gcp_billing_derived
711 → 805 rows; raw export unwritable. Old gcp_billing_export cost table dropped (data preserved in derived).
Board mirrored to taskboard. Holding the service@ role-strip: the fork agent (peer, task AIC) confirmed
the technical blocker is clear ("clear to strip"), but the owner's standing instruction (2026-07-02) is
"continue to defer service@". A peer agent's clearance does not override the owner's deferral, and stripping
roles/owner + 15 bindings is significant + hard to reverse — so no strip without the owner's explicit go;
when authorized, re-audit service@'s current usage first (main has advanced ~130 commits).
Source: Service Account (Local) · local session ca159e39-42a3-4569-b1cd-6e006f6639c3
2026-07-02 (re-audit) — service@ still in use; strip NOT safe yet. Owner: "re-audit service@'s current usage first." Audited via Cloud Audit Logs + IAM keys + Cloud Run/Functions/Vercel. Findings:
- All service@ usage funnels through ONE user-managed key 0385e16081… (created 2024-12-17, no expiry, enabled). No other vector — its 30d footprint == service@'s entire footprint: 959 BigQuery ops + 10 Secret Manager writes + 1 SetIamPolicy.
- BigQuery-as-service@ has STOPPED — last op 2026-06-14 (billing migrated to bq-billing-reader@/herald@); 0 in the last 7d. ✅
- Secret Manager via the key is STILL LIVE — 2026-07-02 02:36 UTC it created the 5 AIRWALLEX_DEMO_* secrets, from a GCE-internal Node process. ❌ (this is why the strip is unsafe today)
- Cleared: no Cloud Run/Functions run as service@; no Vercel prod env references it (GOOGLE_CLIENT_EMAIL=herald@); no GCE VMs; Cloud Build uses compute@.
- Likely holder of 0385e160: the cloud-agent-injected GOOGLE_* credential (would explain both the historical BQ-billing usage and today's Airwallex secret-load from a GCE-internal node). Unconfirmed — needs the owner to check what the cloud sandbox injects as GOOGLE_PRIVATE_KEY.
- Retirement path (clean, since it's a single key): identify/rotate whatever holds key 0385e160 off service@ → disable key 0385e160 (one reversible kill-switch for all of service@) → soak a few days → strip the 16 roles → 30-day soak → delete. Do NOT strip while the key is live + used. Nothing stripped or disabled in this pass — read-only audit only.
Source: Service Account (Local) · local session ca159e39-42a3-4569-b1cd-6e006f6639c3
2026-07-02 (CLOSED — owner decision: keep service@ for agents; web-app guardrail). After the re-audit the owner decided NOT to retire service@ — it stays as the operational identity for agents (cloud Claude Code sessions / ops tasks), acceptable because agents are trusted operators, not an internet-facing surface. Standing guardrail (owner, 2026-07-02, verbatim): "I'm actually okay with preserving and reserving it for the used by agents. Just that if it ever needs to be stored somewhere for the web app for constant use, it has to be the small keys." → any credential baked into the web app for constant runtime use MUST be a per-workload scoped SA, never service@. Already compliant: no Vercel prod env references service@; every real web-app workload runs on a scoped SA (the app's residual GOOGLE_* fallback = herald@, a separate legacy identity, not the master key). Outcome: no strip, no delete — service@ + its user key 0385e160 stay as-is for agent use. Closes both the write-gap fix (done + verified 711→805) and the service@ question. Optional / non-blocking follow-ups (spin off as separate tickets if pursued): rotate the non-expiring 2024 user key 0385e160 to an expiring one to cut standing risk while keeping it for agents; migrate the web app's GOOGLE_* fallback off herald@ to per-workload keys (spirit of the guardrail); the tighter reader/writer split for bq-billing-reader@; the gcp_billing_csv_backfill consolidation. Blast radius: none — nothing in GCP/Vercel changed by this decision; service@ untouched.
Source: Service Account (Local) · local session ca159e39-42a3-4569-b1cd-6e006f6639c3