extraction — ServiceTitan Sync Lambda (Java / Spring Boot)

Module path: st-integration/extraction/ · Maven artifact com.maktix.jb:extraction:0.0.1-SNAPSHOT

Purpose

A Spring Boot / Spring Cloud Function application deployed as an AWS Lambda that performs bidirectional sync between ServiceTitan and the JB360 MySQL staging database (jbwst).

It is triggered per tenant (one ServiceTitan tenant = one dealer) by an SQS message whose JSON body is a Tenant object. Each invocation:

Inbound (ServiceTitan → JB DB): pulls invoices + line items, enriches locations with phone/email, and pulls installed-equipment details (manufacturer/model/serial) for agreements missing equipment data.
Outbound (JB DB → ServiceTitan): takes agreements and claims that JB360 has processed and are flagged ready-to-send, and PATCHes them back into ServiceTitan — JB tags, custom fields (JB Agreement Number, claim status/amount/date/number), location notes, and warranty start/end dates on installed equipment.
Audit: records every run in tblTransmissionDetails (current status per tenant) and tblTransmissionDetailsHistory (append-only, includes the captured in-memory run log).

Tech Stack

Concern	Choice
Language / runtime	Java 17
Framework	Spring Boot 3.0.12, Spring Cloud 2022.0.4
Lambda adapter	`spring-cloud-function-adapter-aws` (function bean `loadInvoices`)
Persistence	Spring Data JPA (Hibernate) + `JdbcTemplate` for raw SQL
Database	MySQL 8 on Amazon RDS, schema `jbwst`
HTTP client	`RestTemplate` + Apache HttpClient 5
JSON	Gson
AWS SDKs	Secrets Manager (v2), RDS IAM auth (v1), Lambda events
Build	Maven (`mvnw`), `maven-shade-plugin` produces the Lambda jar (classifier `aws`)

Entry Points

Entry point	File	Notes
Lambda handler	`src/main/java/com/maktix/jb/extraction/ExtractionApplication.java`	`@Bean Function<SQSEvent,String> loadInvoices()` — parses each SQS record body into `Tenant`, calls `JbDataLoadService.loadInvoices`
HTTP (local/dev)	`controller/InvoiceController.java`	`POST /api/invoices/load` with an `SQSEvent`-shaped JSON body — same flow

Configuration

Environment variables (no defaults — all required at runtime)

Variable	Purpose	Read in
`ST_URL`	ServiceTitan API base URL	`service/InvoiceService.java`
`ST_AUTH_URL`	ServiceTitan OAuth token base URL	`service/TokenService.java`
`ST_APP_KEY`	ServiceTitan app key (sent as `ST-App-key` header)	`service/TokenService.java`
`DATABASE_ENDPOINT` / `DATABASE_PORT` / `DATABASE_REGION`	RDS host / port / region	`config/JpaConfiguration.java`
`SECRET_NAME`	Secrets Manager secret id holding DB credentials	`config/JpaConfiguration.java`

Standard AWS credential chain is also required (RDS IAM token generation, Secrets Manager, SQS).

Per-message configuration (`model/Tenant.java`)

Each SQS message carries: tenantId, dealerId, clientId + clientSecret (ServiceTitan OAuth client-credentials, per dealer), batchId, syncStartDate (yyyy-MM-dd'T'HH:mm:ss.SSS'Z'), invoiceStatus.

Database authentication

In Lambda, the datasource is built in code (config/JpaConfiguration.java): username comes from the Secrets Manager secret, and the password is a generated RDS IAM auth token (no static password). The datasource in src/main/resources/application.yml is a dev/fallback only — see Caveats.

Data Flow

Orchestrated by service/InvoiceService.loadInvoices(Tenant):

SQS message (Tenant)
  │
  ├─ 1. OAuth token from ServiceTitan (client credentials per dealer)
  ├─ 2. GET accounting/v2 invoices (paged 1000) ──► upsert tblInvoice / tblInvoiceItem
  ├─ 3. GET crm/v2 location contacts (250/chunk) ─► fill missing phone/email on tblInvoice
  ├─ 4. GET equipmentsystems/v2 installed-equipment ─► fill tblAgreementEquipments
  │       (only agreements with dataReceivedFromST = 0)
  ├─ 5. Outbound push (callServiceTitan):
  │       agreements PAID & isSentToST=0 ─► PATCH ST location (tag + custom field),
  │                                         POST location note, PATCH installed-equipment
  │       claims isReadyForST=1 & isSentToST=0 ─► PATCH ST job (status/amount/date tags+fields)
  └─ 6. Audit ─► tblTransmissionDetails (update) + tblTransmissionDetailsHistory (insert)

Notable transforms on inbound invoices: customer name split into first/last, address preference (location address over customer address), field truncation to 44/45 chars, line-item totals summed into customerPurchasePrice, missingEquipment flag, isSeaCoast flag when SKU is JBSEA_COAST.

ServiceTitan endpoints used

All in service/InvoiceService.java unless noted:

Endpoint	Direction	Purpose
`POST {ST_AUTH_URL}token`	—	OAuth client-credentials (`TokenService.java`)
`GET accounting/v2/tenant/{t}/invoices`	in	Invoice extract (paged, status/date filters)
`GET crm/v2/tenant/{t}/locations/contacts?locationIds=…`	in	Batched phone/email enrichment
`GET equipmentsystems/v2/tenant/{t}/installed-equipment?locationIds=…`	in	Equipment details
`GET/PATCH crm/v2/tenant/{t}/locations/{id}`	out	Read/merge/write tags & custom fields
`POST crm/v2/tenant/{t}/locations/{id}/notes`	out	Agreement note
`GET/PATCH equipmentsystems/v2/tenant/{t}/installed-equipment/{id}`	out	Warranty dates + tags on equipment
`GET/PATCH jpm/v2/tenant/{t}/jobs/{id}`	out	Claim status update on jobs

Database tables touched

Writes: tblInvoice, tblInvoiceItem, tblAgreement (flags/comments), tblAgreementEquipments, tblClaims (flags), tblTransmissionDetails, tblTransmissionDetailsHistory. Reads: tblProducts, tblTenantCustomTags, tblTenantCustomFields (via raw SQL in repository/JbRepository.java).

Build & Run

# Build (produces shaded Lambda jar, classifier "aws")
./mvnw clean package

# Run locally as HTTP service (requires the env vars above)
./mvnw spring-boot:run
# then: POST http://localhost:8080/api/invoices/load  (SQSEvent-shaped JSON)

Deployment: the shaded jar is deployed as a Lambda with the Spring Cloud Function handler; the trigger is an SQS queue (see the jbw-sam module for infrastructure).

Error Handling & Logging

The whole run is wrapped in try/catch(Throwable) — failure sets syncStatus=FAILED but the audit row is still written.
Per-agreement and per-claim loops catch exceptions individually, so one bad record does not abort the batch.
A per-run in-memory log (util/LogContext.java) is joined and stored in tblTransmissionDetails.logDetails for auditing.
There is no internal scheduler — cadence is driven entirely by whatever enqueues the SQS messages (see jbw-sam).

Caveats / Known Issues

src/main/resources/application.yml contains committed plaintext DB credentials and a real RDS hostname (used as dev/fallback; production uses env vars + Secrets Manager + RDS IAM).
Several outbound UPDATE statements in InvoiceService.callServiceTitan are built by string concatenation, not parameterized.
The RDS IAM auth token and ServiceTitan bearer tokens are printed to logs (System.out.println used heavily).
Some exceptions are silently swallowed (contact-fetch and location-v2 paths).
README.md is empty; HELP.md is Spring boilerplate.