B-04 · Ingest a document — WhatsApp / folder channels

SOP: SOP_AI_Bookkeeping_Automation.md §3.2 (WhatsApp) + §3.3 (Folder)Actors: Client (sender) — system handles the rest. Platform Admin / Developer for channel setup. Pre-state: Channel configured per B-02 and enabled = true. Entity zenith_e has EntityProduct BKP ACTIVE (seeded). Post-state: Same as B-03: BookkeepingDocument + draft JournalEntry. Difference is source_channel = WHATSAPP | CLOUD_FOLDER instead of CLIENT_PORTAL.

Greenfield note: The tables used below are ingestion_channels (with entity_id) and bookkeeping_documents. The legacy client_ingestion_channels table does not exist in the greenfield DB. Run SQL as spade_migrate (postgresql://spade:spade@127.0.0.1:5433/breezycorp) to bypass RLS.

0. Prerequisites

bash

docker compose up -d
pnpm --filter @breezycorp/db db:setup   # migrate:deploy → db:roles → db:seed (idempotent)
pnpm --filter @breezycorp/db test:rls   # confirm isolation is healthy
pnpm --filter @breezycorp/worker dev    # start the worker

Worker startup should show within ~3 s:

pg-boss started
Registered bookkeeping handlers
Registered outbox poller schedule (every 10s)

Minimum .env (already set by cp .env.example .env for MOCK_FS and WhatsApp):

env

OCR_PROVIDER=mock
# INGESTION_OAUTH_KEY is only needed for Google Drive / Dropbox OAuth paths

1. WhatsApp path

1.1 Webhook receiver

Inbound media webhooks land at POST /hooks/whatsapp. The handler:

Validates the inbound signature (Meta X-Hub-Signature).
Resolves the IngestionChannel by phoneNumberId in config_json.
If the channel is disabled or unknown → 200 (so Meta does not retry) but writes a whatsapp.unmatched_channel audit event.
If the message is text-only, surfaces it to the reviewer's inbox (no doc ingestion).
If the message has media:
- Downloads the media from the Meta CDN.
- Stores in S3 at bookkeeping/<entityId>/inbound/whatsapp/<messageId>.<ext>.
- Creates a File row with sha256 computed.
- Enqueues bookkeeping.ingest-document with sourceChannel = 'WHATSAPP'.

1.2 Trigger via curl (local test)

bash

curl -X POST http://localhost:3001/hooks/whatsapp \
  -H 'Content-Type: application/json' \
  -d '{
        "entry": [{
          "changes": [{
            "field": "messages",
            "value": {
              "metadata": { "phone_number_id": "1234567890" },
              "messages": [{
                "id": "wamid.HBgMNjU5MTIzNDU2NwIYEDA1RkE5N0NBNzZBNkJC",
                "from": "6591234567",
                "type": "image",
                "image": {
                  "id": "<media-id>",
                  "mime_type": "image/jpeg",
                  "sha256": "<sha256>"
                }
              }]
            }
          }]
        }]
      }'

The mock-Meta-media adapter returns a placeholder PDF for any media-id. The pipeline then proceeds exactly like B-03.

1.3 Verify

sql

SELECT source_channel, ingested_at
FROM   bookkeeping_documents
WHERE  entity_id = 'zenith_e' AND source_channel = 'WHATSAPP'
ORDER BY ingested_at DESC LIMIT 1;

2. MOCK_FS path (dev/test — no OAuth required)

MOCK_FS is a local-filesystem adapter for isolated pipeline testing. The UI does not expose it — insert via SQL only.

2.1 Create the MOCK_FS channel

Run as spade_migrate:

sql

INSERT INTO ingestion_channels (
  id, tenant_id, entity_id, channel_type, label, enabled, config_json, created_at
) VALUES (
  'test-mock-fs-channel-01',
  't_spade',
  'zenith_e',
  'MOCK_FS',
  'Dev drop folder',
  true,
  '{"directory":"/tmp/bkp-ingest-test"}',
  now()
)
ON CONFLICT (id) DO UPDATE SET
  config_json = EXCLUDED.config_json,
  enabled     = true,
  last_cursor = NULL;  -- reset so files are re-picked on next poll

Do not insert an EntityProduct — zenith_e already has one from the seed. The entity_products table has a unique constraint on (entity_id, product).

2.2 Prepare the drop directory

bash

mkdir -p /tmp/bkp-ingest-test

# Use unique content each run — dedup fires on SHA-256.
# An unchanged file is always deduped even if you reset last_cursor.
printf "Invoice #001\nVendor: Acme Corp\nAmount: SGD 1200.00\nDate: $(date)\n" \
  > /tmp/bkp-ingest-test/acme-invoice-001.txt

Supported extensions (MIME auto-detected): .pdf, .png, .jpg, .jpeg, .csv, .xlsx, .xls, .txt

2.3 Trigger and verify

sql

-- psql as spade_migrate or spade_app (both can write to pgboss.job)
INSERT INTO pgboss.job (name, data, state)
VALUES ('bookkeeping.folder-sync', '{}', 'created');

Worker picks up within ~1 s. Look for:

Channel sync complete  channelId: "test-mock-fs-channel-01"  ingested: 1  deduped: 0

ingested: 0, deduped: 1 means the file content hasn't changed — add a new file or change its content.

Then run the Verification queries in section 4.

3. Google Drive path (OAuth)

3.1 One-time GCP setup

Google OAuth credentials must be provisioned once per environment before any staff user can click Connect. See OAuth ingestion channels runbook. Required env vars:

env

GOOGLE_OAUTH_CLIENT_ID=<client-id>.apps.googleusercontent.com
GOOGLE_OAUTH_CLIENT_SECRET=<client-secret>
GOOGLE_OAUTH_REDIRECT_URI=http://localhost:3001/admin/oauth/callback
GOOGLE_PICKER_APP_ID=<numeric-project-number>
GOOGLE_PICKER_DEVELOPER_KEY=<browser-api-key>
INGESTION_OAUTH_KEY=<64-char-hex>   # node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"
OAUTH_STATE_SECRET=dev-oauth-state-secret

Verify the Picker endpoint is live (returns appId + developerKey, not 503):

bash

curl -s http://localhost:3001/admin/oauth/google/picker-config \
  -H "Cookie: <staff-session-cookie>" | jq .

3.2 Option A — UI connect flow (preferred)

The seeded GOOGLE_DRIVE channel for zenith_e starts with no refresh token and no folder. folder-sync logs "Channel not connected" and skips it.

Navigate to http://localhost:3000/dashboard/clients/zenith_e/ingestion-channels
The GOOGLE_DRIVE row shows Connect Google Drive — click it
Sign in with Google and grant drive.file openid email scopes
Browser returns to the ingestion-channels page; row shows Connected as <email> and a Select folder button
Click Select folder — pick a Drive folder through the Picker
Row updates to show the folder name in config_json

config_json.folderId must be populated before folder-sync will pull files.

Seeded channel IDs for reference:

GOOGLE_DRIVE: cmq5g56am0010n0o10ns1igek
WHATSAPP: cmq5g56ak000zn0o1tdl9uqgp

3.3 Option B — SQL (bypass UI for isolated tests)

Use the Google OAuth 2.0 Playground to get a refresh token with scope https://www.googleapis.com/auth/drive.file, then encrypt it:

bash

node --input-type=module <<'EOF'
import { encryptAtRest } from './packages/auth/src/crypto.js';
process.env.INGESTION_OAUTH_KEY = '<your-64-char-hex-key>';
const encrypted = encryptAtRest('<your-refresh-token>', 'INGESTION_OAUTH_KEY');
console.log(encrypted);
EOF

Then update the seeded channel (run as spade_migrate):

sql

-- Verify first:
SELECT id, config_json, oauth_refresh_token_encrypted
FROM   ingestion_channels
WHERE  entity_id = 'zenith_e' AND channel_type = 'GOOGLE_DRIVE';

-- Populate:
UPDATE ingestion_channels
SET
  config_json                   = '{"folderId": "<google-drive-folder-id>"}',
  oauth_refresh_token_encrypted = '<encrypted-base64-from-above>',
  oauth_account_email           = '<your-email>',
  oauth_provider                = 'GOOGLE',
  oauth_scope                   = 'https://www.googleapis.com/auth/drive.file',
  oauth_connected_at            = now(),
  oauth_needs_reconnect         = false,
  enabled                       = true,
  last_cursor                   = NULL   -- reset so all existing files are picked up
WHERE entity_id = 'zenith_e' AND channel_type = 'GOOGLE_DRIVE';

Folder ID: open the folder in Drive, copy the last URL segment from https://drive.google.com/drive/folders/<FOLDER_ID>.

3.4 Trigger and verify

Put a real file (PDF, PNG, JPG, JPEG, CSV, XLSX) into the connected Drive folder. Google-native files (Docs/Sheets/Slides) are silently skipped by the adapter.

sql

INSERT INTO pgboss.job (name, data, state)
VALUES ('bookkeeping.folder-sync', '{}', 'created');

Expected worker log:

Channel sync complete  channelId: "..."  channelType: "GOOGLE_DRIVE"  ingested: 1  deduped: 0

OAuth error paths recorded to last_error:

Scenario	`last_error` value	`oauth_needs_reconnect`
No refresh token in DB	`Channel not connected — click Connect in the admin UI`	unchanged
Bad `INGESTION_OAUTH_KEY`	`Refresh token decryption failed — reconnect required`	`true`
Revoked/expired token	`Reauthorization required`	`true`
Drive API error (non-auth)	`Google Drive files.list failed: HTTP <N>`	`false`

Then run the Verification queries in section 4.

4. Verification queries

Run all as spade_migrate to bypass RLS.

Step 1 — folder-sync completed

sql

SELECT id,
       last_polled_at,
       last_cursor,       -- epoch ms (MOCK_FS) or ISO8601 string (GOOGLE_DRIVE)
       last_error,        -- must be NULL for a clean run
       config_json->>'directory' AS dir_mock_fs,
       config_json->>'folderId'  AS folder_id_drive
FROM   ingestion_channels
WHERE  entity_id = 'zenith_e';

Step 2 — File row created

sql

SELECT id, object_key, checksum, size_bytes, content_type
FROM   files
ORDER BY created_at DESC LIMIT 3;

object_key pattern: bookkeeping/zenith_e/inbound/{timestamp_ms}-{filename}

Step 3 — BookkeepingDocument created

sql

SELECT id, entity_id, source_channel, file_id, original_name, document_type, batch_id
FROM   bookkeeping_documents
ORDER BY ingested_at DESC LIMIT 3;

Expected: source_channel = 'CLOUD_FOLDER', document_type = NULL, batch_id = NULL.

Step 4 — Queue verification (pass/fail gate)

sql

SELECT name,
       state,
       data->>'documentId'    AS document_id,
       data->>'payrollFileId' AS file_id,
       created_on
FROM   pgboss.job
WHERE  name IN ('ocr-process', 'bookkeeping.classify-and-draft', 'bookkeeping.ingest-document')
  AND  created_on > now() - interval '10 minutes'
ORDER BY created_on DESC;

PASS if one ocr-process row and one bookkeeping.classify-and-draft row are visible in any state, with file_id / document_id matching steps 2–3.

5. Negative & edge cases

ingested: 0, deduped: 1 — same file SHA-256 seen before; add new content.
last_error = 'Channel not connected' — GOOGLE_DRIVE channel has no refresh token; run the UI connect flow or Option B SQL.
Inbound from unauthorized phone (WhatsApp) — routes by phoneNumberId, not sender. To enforce an allow-list, implement in apps/api/src/routes/hooks/whatsapp.ts; today it writes a whatsapp.unauthorized_sender audit event.
File > 25 MB — adapter skips; writes bookkeeping.folder.file_too_large audit event.
Google-native file (application/vnd.google-apps.*) — adapter skips; no audit event.
Multiple channels, same file — dedup is SHA-256; the second BookkeepingDocument is not created; audit event records the duplicate channel.
Disabled channel + new file — folder-sync skips polling for it; WhatsApp webhook short-circuits when channel is disabled.

6. Known issues (outstanding)

`ocr-process` reads `payrollFile` model (greenfield mismatch) — RESOLVED (Run 4)

The OCR/extraction spine (DocumentClassification, DocumentExtraction, ExtractedField) keys off PayrollFile, the product-neutral file spine — it is not the greenfield File model (which only relates to Document/TicketAttachment). Earlier runs had folder-sync creating File rows and ingest-document reading tx.file, which stranded OCR (ocr-process reads tx.payrollFile, found nothing, skipped).

Resolution: the whole bookkeeping cloud-folder ingest now writes PayrollFile rows (folder-sync's inline makeFileRepo → tx.payrollFile, entity-scoped sha256 dedup), ingest-document reads tx.payrollFile, and ocr-process is unchanged. The pipeline is consistent end-to-end and DocumentExtraction rows are written. (We did not migrate the extraction FKs to File — that is a larger, payroll-touching refactor left for later.)

Dead-letter audit write fails with RLS 42501

The dead-letter handler in phase0.ts instantiates AuditEventRepository(prisma) directly (no withTenantContext), so app.tenant_id GUC is never set. Pre-existing; not introduced by ingestion work.

Change log

Run 1 — 2026-06-12 (MOCK_FS baseline)

Discovered entity_products column is config not config_json (fixed in SQL above).
Discovered pgboss.job columns are snake_case (created_on, retry_limit).
zenith_e BKP EntityProduct is already seeded — do not re-insert.
ingest-document.ts was querying tx.payrollFile (legacy) instead of tx.file (greenfield); caused silent abort. Fixed.
Result: PASS at queue verification boundary — both ocr-process and bookkeeping.classify-and-draft visible in pgboss.job.

Run 2 — 2026-06-13 (Google Drive OAuth connect)

Six bugs fixed to unblock the OAuth connect flow:

oauth.ts used ClientIngestionChannelRepository (legacy, queries non-existent client_ingestion_channels) — rewrote to use app.withTenant + tx.ingestionChannel, checking channel.entityId.
oauth.ts was in apps/api/tsconfig.json exclude array (quarantined) — removed.
oauth.ts was only registered inside the quarantined routes/admin/index.ts — registered directly in app.ts under prefix /admin.
oauth.ts missing routeScope: 'staff-only' — scope-enforcer plugin returns 500 without it; added onRoute hook.
oauth-connect-button.tsx had wrong returnPath (/dashboard/entities/... → /dashboard/clients/...).
ingestion-channels-section.tsx detected oauth_connected=1 but didn't call load() — channel row showed stale "Not connected".

GCP project provisioned: spadedev (project number 1064779498019). New GCP Auth Platform UI (Branding / Audience / Clients) documented in OAuth ingestion channels runbook.

Result: GOOGLE_DRIVE channel OAuth-connected (ahmdkabeerm@gmail.com). Folder selection + end-to-end sync with a real Drive folder still pending.

Run 3 — 2026-06-13 (UI bugs after OAuth redirect)

Three bugs fixed that manifested only after Run 2 had the OAuth flow working end-to-end:

"Not found" toast on OAuth return + race condition in db.ts — activeRequest was a module-level singleton set in the auth preHandler. When the OAuth return page fired two simultaneous load() calls (one from mount, one from the oauth_connected effect), the second request's bindActiveRequest() call overwrote the pointer before the first request's route handler called withTenant. The first request then read system scope → tenantId: null → entity lookup returned null → 404. Fix: removed activeRequest entirely; withTenant now reads currentScope() from ALS only.
als.enterWith() doesn't propagate from preHandler to route handler in Fastify 5 — the ALS-only fix above revealed that als.enterWith() called inside an async preHandler does not reach the route handler (Fastify creates the handler's async context before the preHandler finishes). Fix: made RequestContext.clientScope mutable and changed upgradeScope() to mutate the existing object in-place rather than creating a new one. Since als.run() in onRequest stores a reference to the context object, mutations are immediately visible to currentScope() throughout the request chain.
"Not connected" persisted after successful OAuth — GET /admin/clients/:id/ingestion-channels mapped only 7 fields (id, channelType, label, enabled, configJson, lastPolledAt, lastError); all OAuth fields (oauthProvider, oauthAccountEmail, oauthConnectedAt, oauthNeedsReconnect, etc.) were absent. The UI check if (!channel.oauthProvider) always rendered the "Not connected" branch regardless of DB state. Fix: added all IngestionChannelResponse fields to the response map in clients.ts.
Stale lastError after folder selection — PATCH .../folder updated configJson but did not clear lastError, so the "Google Drive folder not selected" message from the previous folder-sync run persisted even after a folder was chosen. Fix: added lastError: null to the folder-selection update in oauth.ts.

Result: PASS — OAuth connect, folder selection, and UI state all work correctly end-to-end. End-to-end Drive sync with a real folder confirmed working.

Run 4 — period-aware routing + PayrollFile spine

OCR pipeline fixed at the spine. Bookkeeping cloud-folder ingest now writes PayrollFile rows (not greenfield File), so OCR + extraction resolve. See the resolved Known issue above. ingest-document.ts reverted to tx.payrollFile.
Period routing (configJson.periodRouting). A connected folder uses subfolder-per-period routing (the only mode): the folder holds one subfolder per period (e.g. 2026-04); each file routes to the period its subfolder names, parsed with parsePeriodFolderName (numeric 2026-04 / month-name April 2026; format selectable in the UI). Files in the folder root are skipped. On period open the worker also auto-creates the period subfolder(s) in each connected channel (bookkeeping.provision-period-folders; one per month for QUARTERLY/ANNUAL). If the target period has no batch, the worker auto-opens one (autoOpened = true, via ensureAutoOpenedBatch) reading cadence/FY-end from the entity's BKP config. Files are never stranded.
UI. A "Period routing" control on the connected channel picks the subfolder name format; it persists via PATCH .../period-routing.
Scope guard. upgradeScope no longer throws when the request-context plugin is absent (test-harness hardening).

Result: files dropped in a connected Drive folder are ingested into the correct bookkeeping period and OCR'd end-to-end.

Source files

File	Purpose
apps/worker/src/handlers/bookkeeping/folder-sync.ts	Channel sweep, period routing + auto-open, `ingestInboundDocument` call
apps/worker/src/handlers/bookkeeping/ingest-document.ts	Resolves `PayrollFile`, creates `BookkeepingDocument` (with `batchId`), enqueues `ocr-process` + `classify-and-draft`
apps/worker/src/handlers/ocr-process.ts	`ocr-process` handler — reads `PayrollFile`, writes `DocumentExtraction`
packages/domain/src/bookkeeping/period-folder.ts	`parsePeriodFolderName` — subfolder name → `YYYY-MM`
packages/domain/src/bookkeeping/services/bookkeeping-period.service.ts	`ensureAutoOpenedBatch` — lean auto-open for the ingest path
packages/domain/src/bookkeeping/channels/google-drive.adapter.ts	Drive API `files.list` + `files.get`; per-period subfolder scan; `ensurePeriodFolder` auto-provisioning
packages/domain/src/bookkeeping/channels/oauth/google-oauth.provider.ts	Token refresh — `POST oauth2.googleapis.com/token`
packages/domain/src/bookkeeping/services/channel-ingest.service.ts	`ingestInboundDocument`: SHA-256 dedup, S3 put, `PayrollFile` row, enqueue (carries `batchId`)
apps/api/src/routes/admin/oauth.ts	OAuth start/callback/disconnect + folder + period-routing routes (greenfield)
packages/auth/src/crypto.ts	`encryptAtRest` / `decryptAtRest` — AES-256-GCM

After ingestion, every channel converges on the same review surface — proceed to B-05 · Review a journal batch.

B-04 · Ingest a document — WhatsApp / folder channels ​

0. Prerequisites ​

1. WhatsApp path ​

1.1 Webhook receiver ​

1.2 Trigger via curl (local test) ​

1.3 Verify ​

2. MOCK_FS path (dev/test — no OAuth required) ​

2.1 Create the MOCK_FS channel ​

2.2 Prepare the drop directory ​

2.3 Trigger and verify ​

3. Google Drive path (OAuth) ​

3.1 One-time GCP setup ​

3.2 Option A — UI connect flow (preferred) ​

3.3 Option B — SQL (bypass UI for isolated tests) ​

3.4 Trigger and verify ​

4. Verification queries ​

Step 1 — folder-sync completed ​

Step 2 — File row created ​

Step 3 — BookkeepingDocument created ​

Step 4 — Queue verification (pass/fail gate) ​

5. Negative & edge cases ​

6. Known issues (outstanding) ​

ocr-process reads payrollFile model (greenfield mismatch) — RESOLVED (Run 4) ​

Dead-letter audit write fails with RLS 42501 ​

Change log ​

Run 1 — 2026-06-12 (MOCK_FS baseline) ​

Run 2 — 2026-06-13 (Google Drive OAuth connect) ​

Run 3 — 2026-06-13 (UI bugs after OAuth redirect) ​

Run 4 — period-aware routing + PayrollFile spine ​

Source files ​

Next ​

B-04 · Ingest a document — WhatsApp / folder channels

0. Prerequisites

1. WhatsApp path

1.1 Webhook receiver

1.2 Trigger via curl (local test)

1.3 Verify

2. MOCK_FS path (dev/test — no OAuth required)

2.1 Create the MOCK_FS channel

2.2 Prepare the drop directory

2.3 Trigger and verify

3. Google Drive path (OAuth)

3.1 One-time GCP setup

3.2 Option A — UI connect flow (preferred)

3.3 Option B — SQL (bypass UI for isolated tests)

3.4 Trigger and verify

4. Verification queries

Step 1 — folder-sync completed

Step 2 — File row created

Step 3 — BookkeepingDocument created

Step 4 — Queue verification (pass/fail gate)

5. Negative & edge cases

6. Known issues (outstanding)

`ocr-process` reads `payrollFile` model (greenfield mismatch) — RESOLVED (Run 4)

Dead-letter audit write fails with RLS 42501

Change log

Run 1 — 2026-06-12 (MOCK_FS baseline)

Run 2 — 2026-06-13 (Google Drive OAuth connect)

Run 3 — 2026-06-13 (UI bugs after OAuth redirect)

Run 4 — period-aware routing + PayrollFile spine

Source files

Next