Job Creation Form with Excel Sync

Unified system that combines job creation form with immediate Excel synchronization.

Architecture

This system merges:

• JobSubmissionFlow: User form with auto-incrementing Job_Number
• SyncToExcelFromPb: Microsoft Graph API Excel sync logic

Flow

1. User submits job form
2. Server authenticates with PocketBase (agent credentials)
3. Auto-calculates Job_Number = max(existing) + 1
4. Creates record in PocketBase Job_Info_Prod
5. Captures record.id from creation response
6. Acquires Microsoft Graph API token
7. Gets Excel table columns
8. Maps PocketBase fields to Excel columns (case-insensitive, only existing columns)
9. Adds pb_id = record.id to Excel row
10. POSTs new row to Excel Test_Table

Configuration

All credentials are in .env (copied from existing systems):

# Azure AD
CLIENT_ID=3c846e71-9609-40e1-b458-0eb805e21b9f
CLIENT_SECRET=7aD8Q~d5K~_PzQv6KqDdrEnmyXHE60eVDpbcnaK_
TENANT_ID=3fd97ea7-b124-41f1-855f-52d8ac3b16c7

# PocketBase Agent
PB_DB=https://pocketbase.ccllc.pro
PB_AGENT_EMAIL=pbserviceupdate@cardoza.construction
PB_AGENT_PASSWORD=gJFjYx5y0_BSMgX
PB_COLLECTION=Job_Info_Prod
PB_AUTH_COLLECTION=Users

# Excel
DRIVE_ID=b!9YOqqr2xM0G2DFBwMEJYY4UzQgocFddEtpLYWuS9_AtkCKl2q8yjQJteQ7Ti4QSx
ITEM_ID=01SPNXLDQRICHB63BFUNGKZ3GE6RL7LGBT
EXCEL_TABLE=/drives/.../tables/Test_Table

# Server
PORT=4000

Installation

Use npm install (not bun install) to avoid Windows/OneDrive symlink issues:

npm install

Usage

Start the server:

bun run dev

Server runs at: http://localhost:4000

Key Features

Auto-Increment Job Numbers

• Queries all existing Job_Number values
• Calculates max + 1 for new job
• No manual input required

Excel Column Mapping

• Case-insensitive: Matches Job_Number to job_number, Company_Client to company_client, etc.
• Only existing columns: Ignores PocketBase fields not in Excel table
• Special mapping: record.id → Excel column pb_id

Error Handling

• If Excel sync fails, PocketBase record is still created
• Returns warning with partial success
• Logs Excel error details

Endpoints

• POST /api/submit - Submit job form (creates in PB + Excel)
• GET /api/health - Health check

Technical Details

Dependencies

• Hono: Web framework
• PocketBase SDK: Database client
• @microsoft/microsoft-graph-client: Excel API
• dotenv: Environment config

Authentication

• PocketBase: Service account (pbserviceupdate@cardoza.construction)
• Microsoft Graph: OAuth2 client credentials flow

Excel Operations

• Table path: EXCEL_TABLE from .env
• Operation: POST /rows with { values: [[...]] }
• Row format: 2D array matching column order

Differences from Other Systems

vs. SyncToExcelFromPb

• No batch sync: Creates one record at a time
• No duplicate detection: Always adds new row
• No change comparison: Simple create operation
• No collision warnings: Assumes Job_Number is unique

vs. JobSubmissionFlow

• Excel sync included: Adds to Excel immediately after PB creation
• pb_id captured: Stores PocketBase record.id in Excel
• Graph token acquisition: Handles Microsoft authentication
• Column-aware: Only maps fields that exist in Excel table

Troubleshooting

Bun Package Resolution Error

Symptom: Cannot find package 'hono'
Solution: Use npm install instead of bun install

Excel Sync Fails

• Check EXCEL_TABLE path in .env
• Verify Azure app has Files.ReadWrite.All permission
• Check Excel table name matches exactly (case-sensitive in Graph API)

PocketBase Auth Fails

• Verify PB_AGENT_EMAIL and PB_AGENT_PASSWORD are correct
• Check agent account exists in Users collection
• Ensure PB_DB URL is accessible