Monthly Coverage Report

Each month we deliver the prior calendar month's coverage report to the insurance carrier (Safe Store). The deliverable is a single landscape PDF with one section per location. Generating the PDF and emailing it to the carrier are the two steps that matter; saving a copy to Drive is durability for resends.

Cadence

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

The report is a coverage roster, not a collections statement, so it does not depend on the billing cycle being reconciled — generate on the 1st once the month is closed.

Source of truth

analytics.insurance_coverage_activity view — reservation-based monthly roster of insured units. One row per insured monthly lockout per month. Roster only; member collections are tracked in bookkeeping, not here.

The view feeds three callers. The export script and the staff-only GET /api/insurance/coverage-report route both read it via InsuranceCoverageReportService (apps/api/src/services/insurance/) — the route returns the roster + totals as JSON. The export_insurance_coverage_report staff MCP tool calls that route and renders the PDF itself (pdfkit runs in the staff-mcp runtime; Vercel never renders it).

Procedure

1. Generate the deliverable PDF

Preferred: the staff MCP tool

With the Metrognome staff and Google Workspace tools connected, ask the assistant: "export the insurance coverage report for YYYY-MM". It calls export_insurance_coverage_report, which generates the PDF on the homelab, saves it under the shared export volume, and returns the file path plus a per-location summary. No production-env pull is needed — the tool reads production through the staff API with your delegated, audited identity.

The next two steps — delivering the PDF to the carrier (step 2) and saving the canonical copy to Drive (step 3) — are both required. The MCP tool can handle the Drive save automatically via create_drive_file; you handle the carrier email yourself.

Fallback: the export script

Use this if the MCP is unavailable.

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.

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

2. Deliver to the carrier

This is the actual handoff — Safe Store needs the PDF for the month to be filed.

Email the PDF to the carrier with subject Coverage Report — {Month YYYY}. Treat this step as not-done until the email is sent; the PDF on disk is not the deliverable, the email to Safe Store is.

3. Save the canonical copy to Drive

Save the same PDF you delivered to the shared Drive under Insurance / Monthly Coverage Reports / YYYY-MM /. This is the authoritative record of what was filed for the month — used to resend if the carrier asks, and to audit historical filings.

The MCP tool does this automatically via create_drive_file (into the folder configured as INSURANCE_DRIVE_FOLDER_ID in the staff MCP). When using the script fallback, upload to the same folder manually.

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
New	`Y` if the coverage was added during this month
Cancelled	`Y` if the coverage was cancelled during this month

Totals row sums Premium across the location's units.

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.