Skip to content

Agent handover — bookkeeping reconciliation for ERL

Audience. A fresh AI coding/analysis agent (Claude Code, Codex CLI, ChatGPT with code-execution, etc.) being asked to help reconcile two specific bookkeeping concerns at Establish Records Limited (ERL), a Hong Kong company.

Brief read order. "From the owner" → "Stack overview" → "The reconciliation tasks". Use everything else as reference.

The two concerns

  1. Unexplained owner-director withdrawals from the ERL bank account — money has been pulled with no clear business purpose; the GL hasn't been able to fully classify them. They've been parked on GL 1120 — Due from Director.
  2. Sub-contractor fees paid to the owner (Jeffero Chan / "JC") that don't equal what ERL invoiced the end client for the same project / coaching engagement. When ERL invoices a client X for a project, the sub-contractor fee paid to JC should equal the portion of X attributed to JC's labour, not whatever the owner happened to transfer out that month.

Goal: a precise reconciliation per project (and per coaching engagement): client paid ERL → what should have gone to JC → what JC actually received → variance + suggested classification.

From the owner (read this first)

There's a Google SA stored directly on your env's env var. Use that, connect to GCP Secret Manager, list out all keys and see which one that might be useful to you.

A pointer I would like to provide to you that may or may not be useful:

(a) Re-into each project/coaching invoice, look at the total amount, and see how much I get paid per those invoices to evaluate at which point did I "underpaid" myself.

(b) Also look into transactions that I matched to 1120 Due From Director and see if they could be reconciled by issuing an additional WOPC and frame it as retro pay for projects that I underpaid myself at that point of the timeline, or with receipts occurred at that point in time that were submitted and could be used to reframe the 1120 as expense reimbursement.

Implications for how you should approach this:

  • Treat the timeline as the master axis. Sort 1120 debits and the project/coaching invoice schedule chronologically; the question is always "as of the date of this 1120 debit, was JC owed money already (under-paid sub-contractor fee on a previously-issued invoice)? Or was there a contemporaneous business expense receipt that this withdrawal could reasonably cover?"
  • Output proposals in the two reframings the owner asked for:
  • Retro WOPC — reclassify 1120 → 5050 Sub-Contractor Fee by issuing an additional WOPC referencing the under-paid project / coaching invoice (a RetroPayment WOPC).
  • Expense reimbursement — reclassify 1120 → the appropriate expense GL by linking the 1120 debit to a contemporaneous receipt that had been submitted but never matched.
  • Anything that can't be reframed under either lens stays on 1120 with a free-text note for the owner to consciously accept as a director loan.

Stack overview

The web app is a Next.js application living in girafeev1/ArtifactoftheEstablisher. Preview env is https://p-eop.theestablishers.com/, deployed by Vercel from the nightly branch.

You don't strictly need to run the web app to reconcile — most data lives in Firestore + Google Drive, and the repo's lib/accounting/* helpers + raw collection paths are enough to read everything directly.

Repo paths you'll touch most:

Path What's there
lib/accounting/types.ts Authoritative shapes (Receipt, BankTransaction, DocumentType, MatchRecordType). Read this first.
lib/accounting/receipts.ts Server-side Firestore CRUD for the file-archive collection: listVendorInvoices, listGcpInvoices, listGcpStatements, listWorkspaceInvoices, createDocument.
lib/accounting/vendorInvoiceFeed.server.ts Unified Workspace + GCP + manual vendor invoice feed.
lib/accounting/expenseRecordsRow.ts + expenseRecordsFeed.server.ts Unified expense-record feed merging receipts + service invoices.
lib/telegram/receiptStore.ts Telegram-uploaded receipts (the dominant receipt source). Stored in subsidiary DBs.
lib/wopc/* and lib/accounting/wopc* WOPC = Work Order Payment Confirmation — the document that records every sub-contractor payment out of ERL. Primary artefact for concern #2.
pages/api/records/wopcs.ts HTTP endpoint that lists WOPCs (same data shape as listWopcs helpers).
pages/api/accounting/matchable-invoices.ts Project invoices (the ones ERL issues to clients).
pages/api/accounting/matchable-coaching-invoices.ts Coaching-session invoices (under MEL subsidiary, but JC is also paid via WOPCs against them).
docs/eop-tasks/tasks/T-047.md Detailed model + business rules around IR56M / sub-contractor payments to JC. Read this for context — explains WOPC flow, GL 5050 = Sub-Contractor Fees mapping, why JC is a director AND a contractor.

Authentication & credentials

All credentials live in Google Cloud Secret Manager, project aote-pms. The session you're in already has a Google service account JSON reachable as one of:

  • An env var GCP_SM_SA_KEY containing the full JSON, OR
  • Three split env vars: GOOGLE_PROJECT_ID, GOOGLE_CLIENT_EMAIL, GOOGLE_PRIVATE_KEY — reassemble these into a credentials dict (the private key has escaped \ns; replace them with real newlines).

The SA has roles/secretmanager.secretAccessor on the project. Step zero per the owner's instructions: list every secret and decide what's useful before doing anything else.

import os, base64, requests
from google.oauth2 import service_account
from google.auth.transport.requests import Request

pk = os.environ['GOOGLE_PRIVATE_KEY'].replace('\\n', '\n')
sa_info = {
    'type': 'service_account',
    'project_id': os.environ['GOOGLE_PROJECT_ID'],
    'private_key': pk,
    'client_email': os.environ['GOOGLE_CLIENT_EMAIL'],
    'token_uri': 'https://oauth2.googleapis.com/token',
}
SCOPES = ['https://www.googleapis.com/auth/cloud-platform']
creds = service_account.Credentials.from_service_account_info(sa_info, scopes=SCOPES)
creds.refresh(Request())
H = {'Authorization': f'Bearer {creds.token}'}

PROJ = sa_info['project_id']

def list_secrets() -> list[str]:
    url = f'https://secretmanager.googleapis.com/v1/projects/{PROJ}/secrets?pageSize=200'
    out: list[str] = []
    nxt = url
    while True:
        j = requests.get(nxt, headers=H, timeout=15).json()
        out.extend(s['name'].split('/')[-1] for s in j.get('secrets', []))
        if not j.get('nextPageToken'): break
        nxt = url + f'&pageToken={j["nextPageToken"]}'
    return out

def secret(name: str) -> str:
    url = f'https://secretmanager.googleapis.com/v1/projects/{PROJ}/secrets/{name}/versions/latest:access'
    return base64.b64decode(requests.get(url, headers=H, timeout=15).json()['payload']['data']).decode().strip()

# Print every secret name → decide which to fetch.
print('\n'.join(list_secrets()))

gRPC's cert verification sometimes fails through sandboxed egress proxies. Use the REST endpoints (as above) — the system trust store handles MITM CAs cleanly.

Likely-useful secrets for reconciliation:

Secret name pattern Why it might help
GOOGLE_CLIENT_EMAIL / GOOGLE_PRIVATE_KEY / GOOGLE_PROJECT_ID The SA itself (meta; used for Firestore + Drive).
DRIVE_ACCOUNTING_* Drive SA with rights to the subsidiary shared drives where invoice / receipt / WOPC PDFs live.
GITHUB_PAT_CODEX_READER Fine-grained GitHub PAT scoped to this repo. Read + Actions:write.
VERCEL_API_TOKEN Vercel team token. Useful for deploy state, not for data.
Anything containing TELEGRAM, GEMINI, INNGEST, NEXTAUTH, OAUTH Web-app runtime infra. Not needed for reconciliation.

If the SA listed above doesn't itself have Firestore / Drive scopes, fetch the Drive-specific SA from Secret Manager (look for DRIVE_ACCOUNTING_PRIVATE_KEY / DRIVE_ACCOUNTING_CLIENT_EMAIL) and use that for Firestore + Drive reads.

Firestore — what lives where

One GCP project, several databases. Use @google-cloud/firestore (Node) or google-cloud-firestore (Python) admin SDK; pass databaseId explicitly. The REST API is the most reliable transport from Python.

databaseId Holds
aote-system The "file archive" — every uploaded document (receipts, vendor invoices, GCP invoices/statements, Workspace invoices, manually-uploaded service invoices), users, RBAC, signing requests, app-level config. Single source of truth for documents.
tebs-erl All accounting state for ERL: bank transactions, project invoices, COA, WOPCs. This is where your reconciliation data lives.
tebs-mel Same shape as tebs-erl but for the coaching company.

Collection paths + field shapes

# aote-system (single source of truth for documents)
file-archive/documents/entries/{documentId}
   ├── type: 'receipt' | 'invoice_pdf' | 'workspace_invoice'
   │        | 'gcp_invoice' | 'gcp_statement' | 'vendor_invoice'
   │        | 'wopc' | 'contract' | 'quote' | 'other'
   ├── storagePath: 'drive:<fileId>'  OR  'receipts/<gcs/path>'
   ├── referenceNumber  (e.g. 'GCS-20251031', 'ERL-WOPC/JN-2025-017', 'VND-20260601-AB12')
   ├── metadata.invoiceCsv: { invoiceNumber, invoiceDate, invoiceAmount, currency, lineItems }
   ├── subsidiaryId: 'erl' | 'mel'
   ├── status: 'inbox' | 'matched' | 'orphaned'
   ├── transactionId  (when matched to a bank tx)
   └── ...

# tebs-erl (ERL accounting state)
accounting/transactions/entries/{transactionId}
   ├── date            (ISO YYYY-MM-DD)
   ├── description     (cleaned bank-statement memo)
   ├── originalDescription  (raw memo)
   ├── amount          (NEGATIVE for debits / money OUT of ERL)
   ├── glAccountCode   ('5050' = Sub-Contractor Fees,
   │                    '1120' = Due from Director,
   │                    '6000' = Cloud Services, …)
   ├── glTaxTreatment  ('CR' | 'DR' | 'NT')
   ├── matchStatus     ('matched' | 'unmatched' | 'manual')
   ├── receiptIds[]    (links back to file-archive doc IDs)
   ├── source          ('ocbc' | 'airwallex' | 'manual')
   └── ...

accounting/projects/entries/{projectId}/invoices/{invoiceNumber}
   ├── invoiceNumber   ('ERL-INV-2025-001' or similar)
   ├── invoiceDate
   ├── invoiceAmount   (positive — amount BILLED to client)
   ├── amountDue       (remaining unpaid)
   ├── paymentStatus   ('drafted' | 'issued' | 'partial' | 'paid' | 'cleared')
   ├── lineItems[]     ({ description, amount, glAccountHint? })
   ├── projectId
   ├── companyName     (the client)
   └── ...

# WOPCs (sub-contractor payouts) — KEY artefact for concern #2.
# Likely path: accounting/wopcs/entries/{wopcReference} in tebs-erl,
# but verify by reading lib/wopc/* code. Each WOPC has:
   ├── referenceNumber          ('ERL-WOPC/JN-2025-017')
   ├── payeeName                ('Jeffero Chan' for JC payouts)
   ├── payeeAbbreviation        ('JC' or 'JN')
   ├── totalAmount              (what was actually paid to JC)
   ├── relatedInvoices[]        ({ kind:'project-invoice'|'coaching-invoice',
   │                              invoiceNumber, year?, projectId?, sessionId? })
   ├── relatedInvoiceNumbers[]  (denormalized list of strings)
   ├── transactionId            (the matched debit on the bank account)
   ├── status                   ('matched' | 'completed' | 'voided' | null)
   └── ...

Read pattern (Firestore REST from Python)

def fs_get(database: str, path: str) -> dict:
    base = f'https://firestore.googleapis.com/v1/projects/{PROJ}/databases/{database}/documents/'
    return requests.get(base + path, headers=H, timeout=20).json()

def fs_list(database: str, parent_path: str, page_size=200) -> list[dict]:
    base = f'https://firestore.googleapis.com/v1/projects/{PROJ}/databases/{database}/documents/{parent_path}?pageSize={page_size}'
    out, nxt = [], base
    while True:
        j = requests.get(nxt, headers=H, timeout=20).json()
        out.extend(j.get('documents', []))
        if not j.get('nextPageToken'): break
        nxt = base + f'&pageToken={j["nextPageToken"]}'
    return out
# Field values come back wrapped in Firestore's "Value" envelope —
# decode via google.cloud.firestore_v1._helpers._decode_value
# or a small recursive walker.

If you have Node available, @google-cloud/firestore is friendlier:

import { Firestore } from '@google-cloud/firestore'
const fs = new Firestore({ projectId: 'aote-pms', databaseId: 'tebs-erl' })
const snap = await fs.collection('accounting/transactions/entries')
  .where('glAccountCode', '==', '5050')
  .where('date', '>=', '2024-04-01')
  .where('date', '<=', '2025-03-31')
  .get()

Google Drive — where the PDFs live

Each subsidiary has its own Shared Drive ("ERL", "MEL"). Look up the drive ID via getSubsidiaryDriveId('erl') in lib/drive/client.ts. Folder convention:

{subsidiary shared drive}
└── 50. Operational Strategy
    └── 14. Expenses Records           ← top-level; must already exist
        ├── {YYYY}                      ← lazy-created per year
        │   ├── 14a. Receipts           ← Telegram + web-uploaded receipts
        │   │   └── {Category Label}/   ← "Transportation", "F&B", etc.
        │   ├── 14b. Vendor Invoices    ← Workspace + GCP tax invoices,
        │   │                             also gcp_statement PDFs since T-053
        │   ├── 14c. WOPC               ← signed sub-contractor payment PDFs
        │   └── 14d. Service Invoices   ← manually-uploaded service invoices
        │       └── {Category Label}/   ← unified ExpenseCategory slug
        └── ...

storagePath is one of:

  • drive:<fileId> — Drive-resident; read via Drive v3 API. The accounting SA must have viewer rights on the drive.
  • receipts/<gcs path> — legacy GCS uploads; get a signed URL via lib/storage/receipts.ts > getReceiptDownloadUrl.

Read PDF bytes:

DRIVE_SCOPES = ['https://www.googleapis.com/auth/drive.readonly']
creds = service_account.Credentials.from_service_account_info(sa_info, scopes=DRIVE_SCOPES)
creds.refresh(Request())
def drive_bytes(file_id: str) -> bytes:
    url = f'https://www.googleapis.com/drive/v3/files/{file_id}?alt=media&supportsAllDrives=true'
    r = requests.get(url, headers={'Authorization': f'Bearer {creds.token}'}, timeout=60)
    r.raise_for_status()
    return r.content
# Then run pypdf / pdfplumber / pdf-parse on the bytes.

The reconciliation tasks — concrete approach

Concern 2 (do this first, per the owner's pointer (a))

For each project invoice ERL issued, derive what JC's share was supposed to be vs what JC actually got via WOPCs.

  1. Read the project invoice (Firestore + Drive PDF). Line items often spell out JC's labour explicitly ("Sub-contracting fee — {project} — HK$X") or implicitly (total minus pass-through costs, taxes, and ERL's margin).
  2. Read the WOPC(s) that reference that invoice via wopc.relatedInvoiceNumbers[]. Sum amounts paid.
  3. Variance = expected − actual. Carry the variance forward in time so concern #1 can pick it up.
# Pseudocode — wire to your Firestore helpers
for project_invoice in list_project_invoices(year=2024_25):
    paid_to_jc = sum(
        w.totalAmount
        for w in list_wopcs(payee='JC')
        if project_invoice.invoiceNumber in w.relatedInvoiceNumbers
    )
    expected = derive_jc_share_from_invoice(project_invoice)  # human read of line items
    variance = expected - paid_to_jc
    yield {
        'project': project_invoice.projectId,
        'invoice': project_invoice.invoiceNumber,
        'invoice_date': project_invoice.invoiceDate,
        'client_paid_ERL': project_invoice.invoiceAmount,
        'expected_to_JC': expected,
        'actual_to_JC': paid_to_jc,
        'variance': variance,
    }

For the coaching side, swap list_project_invoices for list_coaching_invoices (under tebs-mel, joined by sessionId). The WOPC shape is { kind:'coaching-invoice', sessionId } rather than { projectId, year }.

Concern 1 (do this second, per the owner's pointer (b))

The marker GL is 1120 — Due from Director. Pull every 1120 debit in the audit window:

# fs_query is your wrapper; collection: accounting/transactions/entries
# where: glAccountCode == '1120', date in [2024-04-01, 2025-03-31]
# For each tx: receiptIds[] tells you if it's matched to a doc.

For each 1120 debit, walk the two reframings the owner asked for, in order:

Reframing A — retro WOPC against an under-paid invoice. - Look at concern-#2's running variance ledger as of tx.date. - If JC was already owed money on an issued invoice (positive variance outstanding) at that date, propose a RetroPayment WOPC referencing that invoice, with totalAmount = min(|tx.amount|, outstanding_variance). - The reclassification on the books: 1120 → 5050 Sub-Contractor Fee, tied to the new WOPC.

Reframing B — expense reimbursement. - Query receipts (file-archive/documents/entries with type='receipt') with status in ('inbox', 'orphaned') and date within ±N days of tx.date (start with N=14, tighten if too noisy). - If amounts plausibly match (single receipt or a small bundle), propose the 1120 debit be re-matched to those receipts as an expense reimbursement. The receipt's category drives the destination GL.

Anything that can't be reframed under A or B stays on 1120 with a free-text note. The owner accepts those consciously as a director loan.

Output format

A single XLSX, three sheets:

  1. subcontractor-variance — one row per project invoice / coaching session JC contracted on. Columns: invoice date, client-paid ERL, expected-to-JC, actual-to-JC, variance, cumulative-outstanding-variance. Highlight rows where variance > 0.
  2. 1120-reconciliation-proposals — one row per 1120 debit, with columns: date, amount, description, proposed reframing (retro-wopc | expense-reimbursement | keep-as-director-loan), target invoice or receipt(s), proposed destination GL, free-text owner notes.
  3. 1120-unreconciled — the rows from sheet 2 with proposed reframing keep-as-director-loan, ready for the owner to annotate.

Relevant context to read before starting

  • docs/eop-tasks/tasks/T-045.md — IR56M candidate scan (per-payee GL 5050 aggregation; re-usable).
  • docs/eop-tasks/tasks/T-047.md — Read first. IR56M filing data model + business rules around JC being a director AND sub-contractor (服務身分 "capacity" rules, period-of-service window, 2024-25 schedule).
  • docs/eop-tasks/tasks/T-053.md — recent GCP statements work; demonstrates read/write/exec patterns.
  • These task files are also published at https://tasks.theestablishers.com/ (Cloudflare Access, email-OTP) if you'd rather read them in a browser.

Hard rules

  • Never write to Firestore without explicit owner approval. The admin SDK is perfectly capable of set() / delete(). Don't.
  • Never silently re-classify a transaction. Output proposals, let the owner approve.
  • Don't run the matcher hooks (anything in lib/accounting/matching/hooks.server.ts). They fire side-effects.
  • Drive is auditor-facing. Don't move, rename, or delete files there. Read-only.
  • Don't issue WOPCs programmatically. The WOPC signing flow has a chop/seal step and an audit trail; a retro WOPC must be signed by the owner through the web app, not minted in Firestore.

Suggested first deliverable

Sheets 1–3 above in a single XLSX. Once the owner signs off on the proposed reframings, the second phase is the write side — posting reclassifications + drafting retro WOPCs through the app — which needs explicit go-ahead.