Project Finance

A new CouchDB-backed service for tracking project finances:

• Forecast vs actual summaries against each purchase order.
• Monthly spend forecasting by spend line with cell-level calculation methods.
• Purchase request state plus flexible ticket/reference tracking for ServiceNow, CERF, PR, PO, and future request systems.
• SAP actual cost line imports from CSV/XLSX/XLSM with deterministic deduplication.
• HaulAI budget workbook import from Purchase Orders and FY26 Data.
• Budget analytics by PO, month, vendor, and approval state.
• Supporting document upload stored as CouchDB document attachments.

Sync And Temporary Auth

CouchDB is the sync boundary between the browser and the backend:

• The browser runs a local PouchDB database.
• PouchDB live-syncs directly to CouchDB using the limited sync user from .env.
• Normal app operations, including SAP actuals import, write to local browser PouchDB first and then sync to CouchDB.
• The FastAPI backend also reads CouchDB for analytics and supports operational endpoints.

The browser startup key is the limited CouchDB sync user's password. The browser keeps the key in runtime memory and a session cookie named pf_couchdb_key; it is not stored in localStorage/sessionStorage.

The legacy FastAPI API key is still required for /api/* endpoints except /api/auth/status, but normal application data should not depend on those endpoints.

Default local sync key:

dev-project-finance-key

Run

cd /home/coder/project-finance
cp .env.example .env
docker compose up --build

Open:

• App: http://localhost:8080
• CouchDB Fauxton: http://localhost:5984/_utils

Default local credentials are in .env.example.

The app will ask for the CouchDB sync key before opening the UI and starting direct native PouchDB/CouchDB sync.

Login flow:

1. The static app loads from FastAPI.
2. If there is no pf_couchdb_key session cookie, the app stays locked and asks for the CouchDB sync key.
3. The browser opens local PouchDB and connects directly to CouchDB as project_finance_sync using that key.
4. The browser runs an initial native PouchDB/CouchDB sync, unlocks the UI, then starts live sync.
5. The entered sync key is not sent to FastAPI as X-API-Key.

Data Model

CouchDB stores one JSON document per finance object, using doc_type:

doc_type	Purpose
purchase_order	PO forecast, approved budget, vendor, cost centre, project code
purchase_request	Request state, ticket/reference list, vendor, description, cost coding, original currency value, AUD value, and documents
approval_event	Timeline entry for PR state transitions
forecast_spend_line	Forecast row metadata such as category, expense, type, linked requests, and People day rate
forecast_line	Monthly forecast cell for a PO, request, or manual spend line
actual_cost_line	Imported SAP actual cost line
document_attachment	Metadata document with the uploaded file stored as a CouchDB attachment

Money values are stored as integer cents, then exposed by the API as currency amounts.

API

Useful endpoints:

• GET /api/auth/status - public auth metadata
• GET /api/sync/config - legacy protected CouchDB sync configuration; the browser no longer uses this for normal sync
• /couchdb/* - legacy same-origin CouchDB proxy; the browser no longer uses this for normal sync
• GET /api/summary
• GET /api/analytics/budget
• GET|POST /api/purchase-orders
• GET|POST /api/purchase-requests
• PATCH /api/purchase-requests/{pr_number}/state
• POST /api/import/sap - legacy/server-side fallback; the browser Actuals page imports SAP files into PouchDB directly
• POST /api/import/workbook
• GET /api/actuals
• GET|POST /api/documents
• POST /api/demo/seed

Example:

curl -fsS \
  -H "X-API-Key: dev-project-finance-key" \
  http://localhost:8080/api/summary | python3 -m json.tool

SAP Import Columns

The actuals importer accepts SAP transaction exports with these fields. It stores the SAP columns directly on each actual_cost_line, keeps LC3 amount AUD as the summary amount, and derives a transaction type for purchase-order spend, recoveries, recharges, accruals, and accrual reversals.

If LC3 amount AUD is blank or zero and the transaction currency is AUD, the importer uses Value TranCurr as the effective AUD amount for summaries while retaining the raw SAP row for audit. PO-like purchasing document numbers in the Name or Purchase Order Text fields are used for association when Purchasing Document is blank.

Canonical field	Common accepted headers
purchase_order_text	Purchase Order Text
reference_procedure	Reference procedure
user_name	User Name
document_date	Document Date
transaction_currency	Transaction Currency
fiscal_period	Period
wbs_element	WBS Element
cost_element	Cost Element
cost_element_name	Cost element name
item	Item
name	Name
posting_date	Posting Date
value_tran_curr	Value TranCurr
supplier	Supplier
name_1	Name 1
purchasing_document / po_number	Purchasing Document
lc2_amount_usd	LC2 amount USD
lc3_amount_aud / amount	LC3 amount AUD
object	Object
created_on	Created on
time_of_entry	Time of Entry

Forecast Shape

The Forecast view writes directly to browser PouchDB and syncs to CouchDB. It stores row metadata as forecast_spend_line documents and each monthly grid cell as a forecast_line document. The row identity is stored separately from the month so a spend line can forecast across many months.

Key fields:

Field	Purpose
spend_type	Usually manual for the current named rows
spend_id	Stable row key
spend_name	Display name shown in the left grid columns
category / expense / type	Row metadata shown in the sticky Forecast columns
project_name	Optional project budget assignment for the row's forecast and linked actual spend; monthly values still belong to their financial year
request_ids	Zero, one, or many linked purchase request identifiers
day_rate_cents	Optional row-level AUD day rate used by People forecast cells
position_index	Forecast row display order; drag-and-drop row reordering updates this value
archived	Optional flag used to hide deleted default rows
forecast_month	Month in YYYY-MM format
amount_cents	Calculated AUD value for the cell
method	value, working_days_day_rate, days_day_rate, or split_total
method_inputs	Stored inputs such as day rate, days, working days, total, and split months
po_number	Legacy/derived PO association for summaries when a linked request has a PO

Budget Workbook Import

POST /api/import/workbook accepts the uploaded HaulAI budget workbook. It imports:

• Purchase Orders rows into purchase_order documents when PO# is present.
• Purchase Orders rows into purchase_request documents using PR#, Service Now, CERF, or the workbook row as the stable request key.
• FY26 Data rows into actual_cost_line documents when LC3 amount AUD is populated.

The current example workbook at example-docs/HaulAI Budget and Forecast.xlsx has the SAP columns in FY26 Data, but no populated LC3 amount AUD rows, so the actual-line part reports a warning until a populated SAP export is added.

Purchase Request Shape

A purchase request stores the commercial metadata separately from the approval trail:

• Internal key: pr_number is the hidden CouchDB request key and should not be user-edited.
• Tickets: tickets[] stores variable ServiceNow, CERF, Purchase Request, Purchase Order, and other ticket/reference records with type, number, request date, and status.
• Commercial details: vendor, description, currency, currency_value_cents, and aud_value_cents.
• Cost coding: cost_element_name and cost_object.
• Overall state: in_progress, approved, or complete; adding a Purchase Order ticket may move browser edits to approved, but requests do not require a PO.
• Evidence: document_attachment records can be marked as invoice, scope_of_work, approval, or other.