Complete forwarded invoice processing through DataOps, bookkeeping sheet and Dropbox

Start with backend/src/routes/emailDocuments.ts, backend/src/routes/bookkeeping.ts, and backend/src/db/bookkeeping.ts, then read the two private process sources named in the issue without copying their contents into public materials. Run the listed backend, frontend, CLI, and Playwright checks; the issue also requires local-emulator idempotency tests. Done requires passing automated checks and the specified human-verified real invoice flow through intake, current-revision review, Dropbox read-back, and a single spreadsheet row.

Complete forwarded invoice processing through DataOps, spreadsheet and Dropbox

Status: implementation accepted; deployment and HUMAN live verification pending
Tags: enhancement, backend, frontend, data, P1
Depends on: None for implementation; live publication requires verified broker access, operator authentication and private destination configuration below.
Blocks: None

Scope

A forwarded expense invoice reaches DataOps through Dapier's enabled invoice-intake workflow. DataOps imports the private documents, extracts a reviewable expense draft, lets an authenticated operator correct and verify the current fields, and automatically publishes the verified result to the configured Dropbox expense archive and existing bookkeeping spreadsheet. The operator can see independent destination outcomes and retry incomplete work without duplicate files or rows.

Dapier remains the email transport and provider OAuth/token broker. All invoice extraction, bookkeeping validation, review state, destination field mapping, and publication/retry ownership live in DataOps. Do not resurrect deleted Dapier bookkeeping actions or stores.

Private process sources: dataops-knowledge/content/06-finance-and-compliance/maintain-bookkeeping-records/sops/record-an-expense-in-the-bookkeeping-ledger.md and dataops-knowledge/content/10-engineering-and-automation/manage-ai-agents-and-automations/reference/invoice-email-parsing-readiness.md. Read these privately; never copy raw SOPs, real invoice samples, financial account identifiers, sheet IDs, archive paths, or operational URLs into this public issue/repository. The readiness note's claims of shipped Dapier parsing/review are obsolete. The user now explicitly requests automatic publication when all required fields are verified. Verification attests actual reviewed facts and immediately publishes; extraction confidence or merely complete fields do not establish verification.

Existing capability and implementation architecture

• backend/src/routes/emailDocuments.ts already authenticates POST /api/v1/intake/email-documents, verifies private S3 references/checksums, copies documents into managed encrypted storage, and idempotently registers sensitive intake/artifacts. Receipt acceptance currently proves ingestion only, not extraction or external publication. Preserve this contract and its partial-copy retry behavior.
• backend/src/routes/bookkeeping.ts, backend/src/db/bookkeeping.ts, the existing Finance surface, and authenticated CLI/API are the owning DataOps surfaces. Reuse their transaction/document references and existing declared bookkeeping table rather than introducing a parallel financial product.
• Add a bounded extraction/staging service called for successfully imported invoice/receipt documents, plus an authenticated process/reprocess path for an already received intake so the user's email can be completed after rollout. Intake acknowledgement must not incorrectly imply publication success. Extraction failures retain the imported documents and a visible actionable review state.
• Parse supported AWS and Stripe receipt/invoice layouts deterministically from bounded PDF text, using sanitized fixtures. Preserve extraction method and source evidence. Unsupported/scanned/ambiguous inputs remain editable drafts requiring manual completion; do not invent amounts, payment dates, bank conversion, or a successful parse. AI extraction is optional only if an existing properly configured DataOps provider can be reused safely; do not make a new AI account a prerequisite for the supported path.
• Authenticated review operations provide list/detail, correction, confirmation/rejection, publication retry, and safe configuration/readiness status. Connect them to the existing Finance view and DataOps CLI using the same API/domain behavior. No hidden script-only operational path.
• DataOps uses Dapier's authenticated /api/agent/token broker with a dedicated bounded agent grant to obtain short-lived Google/Dropbox access tokens, then invokes the official provider APIs. Store the broker credential through the managed runtime secret/configuration path; do not read Dapier credential tables directly or store provider refresh tokens in DataOps. A generic existing Dapier orchestration endpoint is also acceptable if it gives equivalent scoped authorization, stable operation identity and independently verifiable results.
• Persist deterministic invoice identity, review revision, confirmation/audit actor, and each destination's operation/result/reconciliation state in the existing declared DataOps storage. Atomically claim publication to prevent concurrent execution. A timeout after a provider write is an unknown outcome requiring provider reconciliation before retry; blind append is forbidden.

Acceptance criteria

• A valid newly imported invoice produces one sensitive, source-linked pending expense draft. Duplicate deliveries/reprocessing reuse it. One email with multiple attachments yields separately reviewable PDFs, never a ZIP or an assumed single merged invoice. An empty/unsupported manifest produces a visible actionable state instead of false success.
• The authenticated Finance view, HTTP API and CLI can list/detail pending records, correct fields, confirm/reject and retry publication. Review shows the original document, extracted values, extraction method, missing evidence, destination states and actionable errors; operator navigation stays integrated with existing intake/Finance.
• Unconfirmed/rejected drafts never write the bookkeeping sheet or participate as confirmed ledger entries/reports. Publication runs automatically when an authenticated operator verifies all required fields of the current revision, with no separate confirmation action. Verification records revision, actor and time; ordinary edits cannot reuse stale verification. Extraction or field completeness alone never supplies actual payment verification.
• Expense mapping matches the verified private target's headers: Date sent, Date paid, Provider, What, Price $, Price EUR, Statement, Count, Entry Type, Type, Period, Category. Read and validate current tab/header mapping before writing; configuration mistakes or incompatible layouts block safely. Expenses are negative in the sheet; DataOps may retain its existing positive magnitude plus expense type internally. EUR-only expenses leave USD blank. Preserve unrelated rows, formulas, tabs and existing formatting.
• Paid date and actual EUR payment amount require documented payment evidence or explicit operator-supplied actual values. A USD invoice alone cannot supply actual bank EUR conversion; receipt tax-display conversion is not bank evidence. Missing actual EUR/payment evidence blocks final publication with a clear request for correction. Any exceptional historical-rate/manual policy must be explicit, reviewed and distinguishable from actual bank payment evidence.
• After confirmation, one original PDF is stored in the configured verified expense destination using the private archive's date/vendor organization and collision-safe deterministic identity. The confirmed sheet row and DataOps transaction preserve document/source provenance and external file/row references. The privately documented recurring-payment archive exception is an explicit reviewable rule, never a hardcoded public counterparty name.
• Publication completion requires independent read-back verification of the correct Dropbox bytes/file and correct spreadsheet row. Partial failure remains visible and retryable. Repeated confirmation/retry, duplicate email forwarding, simultaneous requests, provider timeouts, or crash after a successful write cannot create duplicate ledger entries/files/rows or overwrite another invoice. Cross-email dedup uses stable invoice identity plus document evidence with merchant/account context; ambiguous matches require review.
• Readiness/configuration status names missing broker authentication/grants, provider scopes, destination identifiers/tab/header mapping and storage configuration without returning secrets. Private destination/account values are configured through managed secrets/runtime config; public docs name only variable/parameter keys and sanitized examples. Use existing infra/CI paths and declare any added production resources; runtime must never create infrastructure.
• Durable financial draft/publication state and audit records have portable export/restore coverage and use the existing managed backup boundary. Restoring a record does not replay completed external effects automatically.
• Existing non-invoice email intake and bookkeeping CRUD/report/document behavior remain intact. No raw operational knowledge, unsanitized invoice fixtures, financial account identifiers, access tokens or private links appear in public code, issue comments, logs or screenshots.
• [HUMAN] Verify the dedicated broker grant and existing provider consent with the correct accounts/destinations; renew consent only if fresh token/readiness checks prove scopes are missing; then trace the user's actual forwarded invoice from Dapier run/message identity through DataOps intake/artifact/draft and current-revision verification to the verified Dropbox file and single spreadsheet row. Report each stage accurately and leave this issue open until the real external verification passes.

Live prerequisites and unknowns

Operator inventory confirms both connections exist. Default dapier connections list is grant-filtered; use operator --all for inventory. The list's ACCOUNT summary omits some granted scopes and must not be used to infer missing write consent. Detailed dapier connections show google-sheets reports the Sheets scope alongside Docs/Drive read consent, and dapier connections show dropbox reports files.content.write, files.metadata.read, files.content.read and account-info consent. Both therefore already report the required write scopes. Verify fresh broker-token capability and correct destination access; do not require or mutate provider consent unless those checks demonstrate a real scope gap. An expired access token may refresh and is not itself proof of a broken connection.

Operator must verify the private spreadsheet's current writable format/tab/header layout and intended Dropbox expense folder (the reference also mentions a monthly workbook; do not silently pick a different ledger). Configure private destinations and broker credentials using their owning supported interfaces. Unverified broker grants/token issuance, destination account access and private configuration must not block implementing/testing code locally, but they block claims of live end-to-end success. The user explicitly selected automatic publication when all required fields are verified. The implementation provides current-revision authenticated operator attestation followed immediately by publication; current parsers have no trusted bank feed, so uncertain or missing payment evidence remains pending. DataOps CLI is not signed in and its production device-login start currently returns Unauthorized; operator authentication must be restored through the supported DataOps login/configuration path before live review/confirmation can be verified. Dapier operator authentication is available and does not substitute for DataOps authorization.

Required verification

Tester runs the current consolidated-backend workflow: npm --prefix backend test, npm --prefix backend run typecheck, npm --prefix backend run build, frontend unit tests, CLI tests, and Playwright E2E for the complete changed operator journey. Capture and inspect screenshots of pending review, missing evidence/configuration, confirmation, partial failure/retry and verified completion with sanitized data. Run any new DynamoDB publication-claim/idempotency tests against a real local emulator, including concurrent requests; mocked send-call counts alone are insufficient. If SAM/deploy configuration changes, run SAM validation/build and affected infrastructure tests. Legacy retired Python/work-engine package commands are not applicable to this TypeScript implementation.

Required scenarios: supported sanitized AWS/Stripe extraction; EUR and USD amount/sign/date/header mapping; malformed/unknown/scanned PDFs; multiple attachments; source checksum rejection; unauthorized operations; pending/rejected/stale-review no-write gate; missing bank evidence; wrong scope/account/header configuration; duplicate message and cross-message invoice identity; concurrent confirm; Dropbox success/Sheets failure and inverse; lost-response/provider timeout reconciliation; crash after write before checkpoint; safe retry; provider read-back mismatch; export/restore without automatic replay; existing email/bookkeeping regressions. Mock provider adapters locally; real account consent and user invoice final proof are the explicit HUMAN checks.

Accepted verification boundary

Follow-up user policy accepted: publication runs automatically after authenticated current-revision verification of all required invoice and actual payment facts. Finance supports standalone verify or save-and-verify; CLI supports invoices verify and edit --verified; both call the same API service. No separate confirmation step is required. Current extraction does not verify bank facts, so incomplete/uncertain or merely field-complete drafts remain pending. This is not a claim of zero-review email-only processing.

Independent follow-up Tester PASS: https://github.com/DataTalksClub/dataops/issues/232#issuecomment-5953260890. Full backend 1,119 passed/one intentional skip, frontend 302, CLI 16, native publication 13, independent native harness 16 and refreshed SAM build passed. Four actual HTTP/router/DynamoDB journeys verified UI standalone and save-and-verify plus both real CLI forms through partial failure and retry. Three additional independent native checks cover strict boolean inputs, rejected/corrupted-source verified edits, and edit-versus-verification concurrency. Live account/user invoice evidence is still HUMAN.

PM acceptance follows the independent Tester report at https://github.com/DataTalksClub/dataops/issues/232#issuecomment-5952623228. Consolidated backend tests (1,114 passed, one intentional skip), typecheck/build, frontend 302, CLI 15, native DynamoDB publication nine, independent native safety six, extraction/provider eleven, restore and final SAM build all passed. The complete changed invoice journey was rendered and exercised through real HTTP/backend routing and DynamoDB with provider test doubles, including actual CLI wire values, source document access, confirmation, partial failure and retry to independently verified completion. Sanitized pending/partial/complete screenshots were captured and inspected.

Browser acceptance is narrowed to the changed Finance flow and existing bookkeeping/navigation regression specs: fresh current execution of bookkeeping-production-portal, bookkeeping-sponsors-design-production-portal, canonical-frontend and canonical-route-parity passed 28/28. The broad E2E attempt is not green: it completed 43 tests (21 passed, 22 failed) before interruption for isolation. A clean unchanged-base checkout at d4eca5b06e0a406dd321062a6119e6a5dc8a2e0e reproduces the unrelated fixed September Newsletter/Calendar fixture failure at canonical-capability-behavior.spec.js:949 under the October date; its isolated navigation cases pass, as do the current bounded suites. This finite exception accepts no Newsletter changes or full-E2E-green claim.

Actual bank EUR remains independent from invoice EUR: equivalent decimal formats and a differing actual paid amount with explicit reviewed evidence are accepted and retained. A receipt tax conversion does not supply bank evidence. All agent-verifiable criteria above are accepted locally; provider test doubles do not satisfy the unchecked HUMAN criterion. Keep the issue open for deployment, supported operator authentication and the real user invoice trace.

Out of scope

Income invoicing, accounting reports redesign, Finom banking integration, generic historical archive migration, arbitrary URL downloading from email, moving domain logic into Dapier, raw knowledge migration, and unrelated backlog. Unsupported extraction can be completed manually through the same review flow.