canopy-notices Data Model

notices

One row per generated notice. PDF is the canonical output; the schema deliberately omits body_text / body_html and stores only the S3 pointer + metadata. Carries the recipient pair (household_id, recipient_person_id), the notice_type (drives template selection), optional program / application_id / determination_id context, the subject line, the templating pair (template_key, template_version) used for replay, the optional federal/state form_number, the locale (DEFAULT 'en-US'), the regulatory_basis (CFR / PAMMS cite carried for audit), date fields (effective_date, notice_date, advance_notice_days with advance_notice_adjusted recording whether the 10-day clock under 7 CFR 273.13 was extended for cause), and the storage sextet (pdf_storage_path — S3 key in the Garage / S3 bucket — plus pdf_size_bytes, page_count, and the #435 Pub 1075 §9 integrity trio: content_sha256 (SHA-256 of stored PDF bytes), content_type (magic-byte-verified at upload time, DEFAULT 'application/pdf'), scan_status (AV scanner result; reflects the Scanner::name() of the wired backend — 'noop' today, future values when a real scanner lands)). Delivery tracking (#1091, reshaped by #1216): delivery_status (pending → dispatched, or terminally failed after bounded refused attempts; the dispatcher loop’s 'pending' fence makes the stamp race-safe), dispatched_at (provider ACCEPTED the notice for delivery — the epic &72 dispatch evidence), delivered_at (confirmed receipt; stays NULL until a receipt-capable production carrier exists — a tracked &72 production gap), and delivery_channel (DEFAULT 'test'). The #1216 dispatch ledger — dispatch_claimed_at / dispatch_claimed_by (the FOR UPDATE SKIP LOCKED lease that partitions the queue across replicas, the duplicate-physical-mail guard), dispatch_attempts (counted at claim), dispatch_next_attempt_at (exponential retry gate doubling from 60s, 30s · 2^attempts on the post-claim count, capped ~2.1h; NULLed on terminal park), dispatch_last_error (provider refusal, truncated to 1024 chars) — makes dispatch leased bounded-retry; an operator resend (delivery_status → 'pending') resets the ledger. idx_notices_dispatch_due (created_at, id) partial over the dispatch-due predicate serves the claim scan. Read tracking (#721): read_at (nullable; first-read timestamp set when the recipient opens the notice in the applicant-portal Letters inbox — NULL means unread; mark-read is idempotent via COALESCE(read_at, now()) so the original read time survives repeat opens). Recipient-block provenance (#1188): acp_applied (BOOLEAN NOT NULL DEFAULT FALSE; TRUE iff the letter’s postal block was rendered with the jurisdiction’s [notices.acp] substitute address per the #1146 AcpOutcome::Applied swap, stamped by the render worker — the applicant portal suppresses inline PDF streaming for address_confidential/both households unless this is TRUE; DEFAULT FALSE is the fail-closed backfill: pre-#1188 letters have no provenance and are all treated as real-address — over-suppression is the safe direction). Since #1101 (epic &72 MR 3.1) an action-bound notice also persists its adverse-action binding — adverse_action_id (the spine id), adverse_action_generation (source provenance), action_reason_code, exemption_authority — and the legal cb_election_deadline (Chart B2: notice_date + continued_benefits_election_days, INCLUSIVE, the same [appeals] window canopy-appeals enforces).

notice_work_items (#1091)

The event-routed generation queue: the subscriber persists one row per (source_event_id, notice_type) (UNIQUE — a redelivered/replayed event upserts into the same item, never a second notice) atomically with its inbox row, carrying the full typed GenerateNoticeRequest as JSONB request. The worker loop claims items via single-statement FOR UPDATE SKIP LOCKED + lease (claimed_at/claimed_by — also the completion FENCE: complete/fail match only the exact claim, so a lease stolen mid-render can never double-mint), performs recipient resolution + render + scan + upload OUTSIDE any transaction, then commits the fence flip + notice rows in one short transaction. status (pending → done / failed CHECK), attempts, last_error, due_at (exponential backoff, 30s·2^attempts capped 1h; terminal failed at 8 attempts), notice_id back-pointer once done.

notice_appeal_rights

Per-notice appeal-rights companion under 7 CFR 273.13(a)(3). One row per notice that carries adverse-action consequences. Carries hearing_request_deadline (the calendar date by which the household must request a hearing to preserve continued-benefits eligibility per 7 CFR 273.15(k)), continued_benefits_available + optional continued_benefits_request_deadline, and the contact strip (hearing_phone, optional hearing_address) rendered into the notice PDF and surfaced via the appeal-rights API.

event_outbox (ADR-018 + ADR-039)

Per-service transactional outbox (ADR-018), schema single-sourced in crates/canopy-mq/outbox-migrations/ and generated into this service (ADR-039). Columns, indexes, hold semantics, and the migration inventory are documented ONCE in the cross-cutting description — see the data-models index.

event_inbox (#433)

Per-service consumer inbox (#433 / ADR-018 amendment). Subscriber writes a row before invoking the handler; PK on event_id (the envelope’s UUID v7) makes RabbitMQ redelivery idempotent. Carries (event_id, routing_key, payload, enqueued_at, processed_at, attempts, last_error) plus the #1089 parked-state columns (parked_at, park_reason, park_min_schema, queue_name — see the event-delivery protocol). Janitor (canopy-mq::InboxDrainer) sweeps processed rows older than 7 days.