Monthly Coverage Report

Each month we send the insurance carrier (Safe Store) the coverage report covering the prior calendar month, as a single PDF with one section per location.

Cadence

15th of each month for the prior month's billing period. (Proposed — confirm with carrier and update this line once locked.)

The 15th is chosen to fall after the collections cycle is fully reconciled. If collections runs late, hold the filing until reconciliation is complete and note it in the run log.

Source of truth

analytics.insurance_coverage_activity view — reservation-based monthly roster enriched with the exact insurance line item paid amount from stripe.invoices. One row per insured monthly lockout per month.

Created by apps/api/prisma/migrations/20260331101300_insurance_coverage_activity_view/.

Procedure

1. Confirm collections is reconciled

Check that all MONTHLY invoices for the target month are settled or have a documented reason for being open. If anything is still in retry/grace, wait for it to clear before generating the reports — the carrier sees one snapshot.

2. Run the export script

cd apps/api
pnpm tsx scripts/export-insurance-coverage-reports.ts --month=YYYY-MM --production

YYYY-MM is the billing period — e.g. 2026-04 for the April filing that turns in May 15th.

Reading production data only — script is read-only. Standard production access flow: pull env, run, clean up. See project root CLAUDE.md "Environment" section.

The script:

queries analytics.insurance_coverage_activity filtered to the target month
groups rows by location (mg_id + name)
writes a single landscape PDF to apps/api/scripts/.local/insurance-coverage/YYYY-MM/coverage-report-YYYY-MM.pdf
each location starts on a new page with its own header (name, address, billing period), coverage table, and totals row
adds a PDF outline (bookmark per location) for navigation
prints a per-location row count summary on completion

3. Send to carrier

Email the PDF to the carrier with subject Coverage Report — {Month YYYY}.

4. Archive

Save the sent PDF to the shared Drive under Insurance / Monthly Coverage Reports / YYYY-MM / so it is recoverable if the carrier asks for a resend.

What's in the PDF

Header (per page):

Location name (mg_id + name)
Location address
Billing period (e.g., April 2026)

Coverage table columns:

Column	Source
Customer	Member name on the reservation
Unit	Studio / resource name
Coverage	Tier label, e.g. `$2,000 Coverage, $100 Deductible`
Premium	Premium amount snapshot from reservation
Paid	Insurance line item amount paid this month (from Stripe)
Credit	Pro-rated invoice discount applied to insurance line
Due	Insurance line amount still owed (0 if paid)
New	`Y` if the coverage was added during this month
Cancelled	`Y` if the coverage was cancelled during this month
Paid Date	Date the invoice paid (blank if unpaid)

Totals row sums Premium, Paid, Credit, Due.

When something looks wrong

Row missing for a member you know is insured — check that the reservation's insurance_price_id is set and the Stripe subscription status is not trialing / incomplete (the view excludes those). If they look right and the row still doesn't appear, file a bug.
Paid is zero but the member paid — the view matches invoices by subscription_id and product. If a member's insurance was billed on a different subscription (e.g., a re-issued lockout after migration), the join can miss. Cross-reference Stripe directly and adjust the report manually before sending.
Due non-zero on a paid member — the matching invoice may still be in open status pending ACH settlement. Confirm with Stripe before sending.