"""Notification outbox — guaranteed-delivery persistence for dispatches. Every outbound notification writes one row here *first*, then channels are tried. Per-channel success/failure is recorded; any channel that failed puts the row into retry (exponential backoff via ``next_attempt_at``). Provider calls made by a retry/manual-send worker are preceded by a committed ``delivering`` claim. If the provider may have accepted a send but the local result cannot be committed, the row is fenced as ``delivery_uncertain`` for explicit provider reconciliation instead of an automatic duplicate. After ``max_attempts`` unsuccessful confirmed failures the row is marked ``dead`` and surfaced in the admin outbox page for manual re-send. Invariant: no :meth:`NotificationService.dispatch` call may skip the outbox. That guarantees every intended send has an audit row and every delivery failure has a retry path — satisfying the platform's "no silent drops" commitment. """ from __future__ import annotations import uuid from datetime import datetime from typing import Any from sqlalchemy import ( CHAR, JSON, Boolean, DateTime, ForeignKey, Index, Integer, String, Text, func, ) from sqlalchemy.orm import Mapped, mapped_column from app.core.database import Base from app.core.sql_types import GUID as Uuid # Status values — kept narrow and stable so admin queries can key off them. STATUS_PENDING = "pending" STATUS_DELIVERED = "delivered" STATUS_PARTIAL = "partial" # some channels delivered, others failed STATUS_FAILED = "failed" # all channels failed in most recent attempt STATUS_DEAD = "dead" # exceeded max_attempts STATUS_DELIVERING = "delivering" # durable claim committed before provider I/O STATUS_DELIVERY_UNCERTAIN = "delivery_uncertain" # provider outcome is unknowable # Dead-letter acknowledgement marker (2026-06-10 dead-letter ack): a dead # row whose ``last_error`` starts with this prefix was consciously closed # by an admin. It is the discriminator between the triage buckets # "needs action" (dead, unacknowledged) and "closed" (dead, acknowledged) # — every counter / filter / cluster that talks about actionable dead # rows must key off this single constant, not a re-typed string literal. ACK_NOTE_PREFIX = "cancelled by admin" class NotificationOutbox(Base): __tablename__ = "notification_outbox" __table_args__ = ( Index( "ix_notification_outbox_status_next", "status", "next_attempt_at", ), Index( "ix_notification_outbox_recipient", "recipient_person_id", ), # Composite index for the inbox-summary unread query (migration # 085) — ``WHERE recipient_person_id = :me AND read_at IS NULL``. Index( "ix_notification_outbox_recipient_read_at", "recipient_person_id", "read_at", ), # Time-window scans (automation-health insights, error-clusters) # — migration 272. Index("ix_notification_outbox_created_at", "created_at"), Index( "ix_notification_outbox_delivery_lease", "status", "delivery_lease_expires_at", ), # "这个实体的通知行" — 12 处既有调用点都是这个形状,但一直没索引: # 它们全是 cron / digest,全表扫看不出来。年假到期提醒的批次明细把同一个 # 形状放到了**交互式** GET 上,而这张表随平台发过的每一条通知增长。 # 迁移 509。 Index( "ix_notification_outbox_related_resource", "related_resource_type", "related_resource_id", ), # 「有 message_id、还没结案、而它说的那件事已经终态了」—— 回执对账的 # 形状。上面那条索引答不了「未结案」这一半,于是一次对账要把每种资源 # 类型的全部历史行都扫出来。迁移 624。 Index( "ix_notification_outbox_settlement", "settled_at", "related_resource_type", "related_resource_id", ), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) # Recipient: usually a Person, but we accept raw email too for the # rare "notify a vendor" case. The retry worker treats them the same. recipient_person_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("identity_persons.id"), ) recipient_email: Mapped[str | None] = mapped_column(String(255)) # Envelope payload template_key: Mapped[str | None] = mapped_column(String(64)) title: Mapped[str] = mapped_column(String(512), nullable=False) body_text: Mapped[str] = mapped_column(Text, nullable=False) body_html: Mapped[str | None] = mapped_column(Text) url: Mapped[str | None] = mapped_column(String(512)) context: Mapped[dict[str, Any] | None] = mapped_column(JSON) # Frozen Feishu rich-card payload (action buttons / stats / # highlights / sections / subtitle / footer). The retry worker # rebuilds the envelope FROM this row, so without a frozen payload a # retried card silently degrades to the plain title+url "Open in # Helix" fallback — which is exactly how cloud-grant expiry reminders # lost their "一键续期" button when their first send failed and the # worker resent them. NULL for plain notifications (the common case). # Migration 400. card_payload: Mapped[dict[str, Any] | None] = mapped_column(JSON) # Frozen CC list for the email channel (same precedent as # ``card_payload``): the retry worker AND the admin resend both # rebuild the ``NotificationEnvelope`` FROM this row, so without a # persisted copy a retried/resent email silently drops its CCs — # which is exactly how the Day-1 welcome lost its HRBP + HR Ops CC # when the first send failed. NULL for the common no-CC case. # Migration 409. cc_emails: Mapped[list[str] | None] = mapped_column(JSON) # Channels requested and per-channel outcome channels: Mapped[list[str]] = mapped_column(JSON, nullable=False) delivered_channels: Mapped[list[str] | None] = mapped_column(JSON) failed_channels: Mapped[list[str] | None] = mapped_column(JSON) # Retry bookkeeping status: Mapped[str] = mapped_column( String(24), nullable=False, server_default=STATUS_PENDING, ) attempts: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0") max_attempts: Mapped[int] = mapped_column( Integer, nullable=False, server_default="5", ) last_error: Mapped[str | None] = mapped_column(Text) # When set, the retry handler picks this row up on the next tick # whose clock is >= this timestamp. NULL means "try immediately". next_attempt_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) delivered_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) # Durable provider-I/O boundary (migration 443). The retry worker # commits these fields BEFORE the network call. A live claim excludes # concurrent workers; an expired claim is reconciled conservatively # instead of blindly re-sending a possibly successful message. delivery_claim_token: Mapped[uuid.UUID | None] = mapped_column(Uuid) delivery_claimed_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) delivery_lease_expires_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) delivery_claimed_channels: Mapped[list[str] | None] = mapped_column(JSON) delivery_claim_count: Mapped[int] = mapped_column( Integer, nullable=False, server_default="0", ) # Stable keys are persisted before I/O: Feishu request ``uuid`` and the # RFC Message-ID used by SMTP/Gmail. They are evidence for operator # reconciliation as well as the strongest provider-side dedup available. provider_delivery_keys: Mapped[dict[str, str] | None] = mapped_column(JSON) # The message_id Feishu assigned to the card we sent (migration 571). # Distinct from ``provider_delivery_keys`` in both direction and use: # those are keys WE mint before the call, for provider-side dedup; # this is the id the provider hands BACK, and it is the only thing # that can answer "which card is this employee replying to". # # Notification cards go out through the same Feishu app the assistant # listens on, into the same 1:1 chat — so a reply already reaches the # assistant today. It just arrives context-free, because nothing # recorded what was sent. NULL for non-Feishu channels, failed sends, # and every card sent before this column existed. feishu_message_id: Mapped[str | None] = mapped_column(String(64)) # Push-only marker (migration 375): True = this row is a delivery # record for an EXTRA push channel whose content the recipient # already has as another inbox row (e.g. the Day-1 Feishu welcome # card that follows the Day-1 welcome email once the hire's Feishu # account exists). Hidden rows never surface on the personal bell / # inbox — a duplicate would read as a bug — but stay fully visible # on the admin outbox surfaces for delivery forensics. hidden_from_inbox: Mapped[bool] = mapped_column( Boolean, nullable=False, server_default="0", ) # ── Settlement: what happened to the thing this notification is about ── # # A notification says "somebody is waiting for you to do X". Until # migration 624 nothing ever came back to say X was done, so an approved # request left its card sitting in Feishu with a live 「去审批」 button — # tapping it landed on a page that had already moved on. The card was a # snapshot of an intent, forever. # # These four columns make the row carry the OUTCOME too, and they are # stamped on every channel's row (not just the ones with a card), because # the bell, the mobile action inbox and the card all read this same table. # Settling in one place is what keeps those three surfaces from # disagreeing about whether a thing is still open. # # ``settled_outcome`` is a ``card_design.SETTLED_STATUSES`` member — # approved / rejected / expired / withdrawn / done. It is deliberately the # SAME vocabulary the card renderer uses for its header state, so the # receipt's look is derived from the recorded fact rather than chosen # again at render time. settled_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) settled_outcome: Mapped[str | None] = mapped_column(String(24)) # CHAR(36) with NO foreign key, matching ``related_resource_id`` above and # migration 624. This table is a delivery log with its own retention, and # people do get hard-deleted; an FK here would turn "delete this person" # into "first rewrite a year of notification history". The tests build # their schema with ``create_all``, so a declared FK the migration does # not create is drift that only shows up in production. settled_by_person_id: Mapped[uuid.UUID | None] = mapped_column(CHAR(36)) # Optional domain detail, as a ``{"zh": …, "en": …, "ja": …}`` fragment. # A localized DICT rather than a rendered string for the same reason # ``context`` keeps its locale fragments: the row outlives the send, and a # reader who switches language must not be shown a receipt pinned to # whatever language happened to be current when it settled. settled_note: Mapped[dict[str, Any] | None] = mapped_column(JSON) # Per-recipient read timestamp. ``NULL`` = unread. Only the # recipient's bell-icon "mark all read" / per-row click flips this # via ``POST /notifications/inbox/mark-read``. Admin-side outbox # surfaces (``/admin/notifications/outbox``) ignore the column. read_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) # Provenance — so the admin can click back to the entity that asked # for the notification ("this was the T-3 reminder for process X"). related_resource_type: Mapped[str | None] = mapped_column(String(64)) related_resource_id: Mapped[uuid.UUID | None] = mapped_column(CHAR(36)) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), onupdate=func.now(), ) class NotificationNagState(Base): """How many times we have already nagged one person about one thing. The state behind :mod:`app.notifications.cadence`. Deliberately NOT derived from ``notification_outbox``: that table is a delivery log with its own retention and its own reasons to contain several rows for one logical reminder (retries, per-channel splits), so counting it would make the cadence drift with delivery mechanics rather than with how often a human was actually asked. ``subject_key`` is the THING being nagged about — an inventory-cycle id, an owner-confirmation id — not the message. Two open asks taper independently, because slowing one down must never mute the other. Rows are deleted, not archived, when the ask is settled: this table is a counter, and a stale counter is worse than none (it starts the next unrelated ask on the weekly rung). """ __tablename__ = "notification_nag_state" __table_args__ = ( Index( "uq_nag_state_recipient_template_subject", "recipient_person_id", "template_key", "subject_key", unique=True, ), ) id: Mapped[uuid.UUID] = mapped_column( Uuid, primary_key=True, default=uuid.uuid4, ) recipient_person_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("identity_persons.id"), nullable=False, index=True, ) template_key: Mapped[str] = mapped_column(String(96), nullable=False) subject_key: Mapped[str] = mapped_column(String(128), nullable=False) first_sent_at: Mapped[datetime | None] = mapped_column( DateTime, nullable=True, ) last_sent_at: Mapped[datetime | None] = mapped_column( DateTime, nullable=True, ) sent_count: Mapped[int] = mapped_column( Integer, nullable=False, default=0, ) created_at: Mapped[datetime] = mapped_column( DateTime, server_default=func.now(), nullable=False, ) updated_at: Mapped[datetime] = mapped_column( DateTime, server_default=func.now(), onupdate=func.now(), nullable=False, ) __all__ = [ "NotificationNagState", "NotificationOutbox", "STATUS_DEAD", "STATUS_DELIVERING", "STATUS_DELIVERY_UNCERTAIN", "STATUS_DELIVERED", "STATUS_FAILED", "STATUS_PARTIAL", "STATUS_PENDING", ]