"""Feed domain ORM models. Tables (created in migration 235): - ``feed_posts`` — top-level stream entry; user-authored or system-generated (birthday / anniversary / aggregate). - ``feed_comments`` — flat comment thread on a post. - ``feed_likes`` — one row per (post, person), UNIQUE. - ``feed_mentions`` — denormalized @-mention index (post or comment). - ``feed_attachments`` — ordered image attachments on a post. - ``feed_celebrations`` — per-person, per-day celebration event row; source-of-truth for the daily cron, UNIQUE on ``(person_id, occasion_date, celebration_type)`` so reruns are idempotent. - ``feed_blessings`` — one row per (celebration, sender), UNIQUE. Mirrors a comment onto the matching system post. - ``feed_announcements`` — sidebar announcements with markdown body, schedule + pinning + category + color. - ``identity_person_preferences`` — generic per-person KV (first keys live under the ``feed.*`` namespace). """ from __future__ import annotations import uuid from datetime import date as date_type from datetime import datetime from typing import Any from sqlalchemy import ( JSON, Boolean, Date, DateTime, ForeignKey, Index, Integer, String, Text, UniqueConstraint, func, ) from sqlalchemy.orm import Mapped, mapped_column, relationship from app.core.database import Base # ── Post types ─────────────────────────────────────────────────── POST_TYPE_USER = "user" POST_TYPE_SYSTEM_BIRTHDAY = "system_birthday" POST_TYPE_SYSTEM_ANNIVERSARY = "system_anniversary" POST_TYPE_AGGREGATE_BIRTHDAY = "aggregate_birthday" POST_TYPE_AGGREGATE_ANNIVERSARY = "aggregate_anniversary" SYSTEM_POST_TYPES = frozenset({ POST_TYPE_SYSTEM_BIRTHDAY, POST_TYPE_SYSTEM_ANNIVERSARY, POST_TYPE_AGGREGATE_BIRTHDAY, POST_TYPE_AGGREGATE_ANNIVERSARY, }) VISIBILITY_PUBLIC = "public" VISIBILITY_DEPARTMENT = "department" POST_CATEGORY_GENERAL = "general" POST_CATEGORY_WORK_PLAN = "work_plan" POST_CATEGORY_OTHER = "other" # Anonymous content is deliberately a presentation property, never an # alternate identity model: the real author remains on the row for ownership, # leaver gates, abuse response and the immutable audit trail. ANONYMITY_NAMED = "named" ANONYMITY_ANONYMOUS = "anonymous" REPORT_STATUS_OPEN = "open" REPORT_STATUS_RESOLVED = "resolved" REPORT_STATUS_DISMISSED = "dismissed" CELEBRATION_TYPE_BIRTHDAY = "birthday" CELEBRATION_TYPE_ANNIVERSARY = "anniversary" ANNOUNCEMENT_STATUS_DRAFT = "draft" ANNOUNCEMENT_STATUS_PUBLISHED = "published" ANNOUNCEMENT_STATUS_ARCHIVED = "archived" class FeedCelebration(Base): __tablename__ = "feed_celebrations" __table_args__ = ( UniqueConstraint( "person_id", "occasion_date", "celebration_type", name="uq_feed_celebrations_person_date_type", ), Index( "ix_feed_celebrations_date_type", "occasion_date", "celebration_type", ), Index("ix_feed_celebrations_person", "person_id"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) celebration_type: Mapped[str] = mapped_column(String(16), nullable=False) occasion_date: Mapped[date_type] = mapped_column(Date, nullable=False) years: Mapped[int] = mapped_column(Integer, nullable=False, default=0) display_name_snapshot: Mapped[str | None] = mapped_column(String(128)) department_snapshot: Mapped[str | None] = mapped_column(String(128)) avatar_url_snapshot: Mapped[str | None] = mapped_column(String(512)) illustration_key: Mapped[str] = mapped_column( String(32), nullable=False, default="default", ) notify_state_json: Mapped[dict[str, Any] | None] = mapped_column(JSON) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) class FeedPost(Base): __tablename__ = "feed_posts" __table_args__ = ( Index( "ix_feed_posts_visibility_created", "visibility", "created_at", ), Index( "ix_feed_posts_author_created", "author_person_id", "created_at", ), Index( "ix_feed_posts_type_created", "post_type", "created_at", ), Index( "ix_feed_posts_department", "department_snapshot", "created_at", ), Index("ix_feed_posts_celebration", "celebration_id"), Index("ix_feed_posts_pinned", "is_pinned", "created_at"), Index("ix_feed_posts_category_created", "category", "created_at"), # The idempotency constraint for ``create_user_post``. NULL tokens do # not collide in MySQL, so posts that carry no token (the celebration # scanner, announcements, every pre-existing row) are unaffected. Index( "uq_feed_posts_author_client_token", "author_person_id", "client_token", unique=True, ), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) author_person_id: Mapped[str | None] = mapped_column( String(36), ForeignKey("identity_persons.id"), ) post_type: Mapped[str] = mapped_column(String(32), nullable=False) visibility: Mapped[str] = mapped_column( String(16), nullable=False, default=VISIBILITY_PUBLIC, ) category: Mapped[str] = mapped_column( String(32), nullable=False, default=POST_CATEGORY_GENERAL, ) author_visibility: Mapped[str] = mapped_column( String(16), nullable=False, default=ANONYMITY_NAMED, ) body: Mapped[str] = mapped_column(Text, nullable=False) # System posts render from a key + params instead of stored prose # (migration 456). ``body`` stays populated as the fallback for clients # that predate these columns, and as the search haystack — but the reader's # language decides what they SEE. Storing rendered English was a 2026-06-01 # decision that put "1 teammate celebrating their work anniversary today!" # into an otherwise-Chinese feed for every Chinese reader. # NULL on user-authored posts: those are the author's own words and must # never be translated. body_i18n_key: Mapped[str | None] = mapped_column(String(64)) body_params: Mapped[dict[str, Any] | None] = mapped_column(JSON) celebration_id: Mapped[str | None] = mapped_column( String(36), ForeignKey("feed_celebrations.id"), ) department_snapshot: Mapped[str | None] = mapped_column(String(128)) like_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0) comment_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0) is_pinned: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False) # Caller-supplied idempotency key, unique per author. The composer sends a # fresh UUID per draft so a double-click or a retry after a dropped # response publishes once; the year-in-review sends the deterministic # ``wrapped:`` so a second tab / device / MCP call replays instead of # publishing a duplicate. It used to be a ``localStorage`` flag, which all # three walked straight past. # # NOT once-per-year in the absolute sense: soft-deleting the post RELEASES # the token (see ``create_user_post``), so a reader who deletes their recap # can share it again. Unbounded share→delete→share is the accepted cost of # not telling someone "you already shared" while pointing at nothing. client_token: Mapped[str | None] = mapped_column(String(128)) 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(), ) # ``last_edited_at`` is separate from ``updated_at`` because the # latter auto-bumps on counter increments (like_count / comment_count # write-through). The FE "已修改" badge keys off last_edited_at only, # so liking a post never flips the badge. Set by the PATCH endpoint. last_edited_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) edit_count: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) # Relationships attachments: Mapped[list[FeedAttachment]] = relationship( back_populates="post", lazy="selectin", order_by="FeedAttachment.position", ) celebration: Mapped[FeedCelebration | None] = relationship(lazy="joined") class FeedComment(Base): __tablename__ = "feed_comments" __table_args__ = ( Index( "ix_feed_comments_post_created", "post_id", "created_at", ), Index("ix_feed_comments_author", "author_person_id"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) post_id: Mapped[str] = mapped_column( String(36), ForeignKey("feed_posts.id"), nullable=False, ) author_person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) body: Mapped[str] = mapped_column(Text, nullable=False) author_visibility: Mapped[str] = mapped_column( String(16), nullable=False, default=ANONYMITY_NAMED, ) reply_to_comment_id: Mapped[str | None] = mapped_column( String(36), ForeignKey("feed_comments.id"), ) blessing_id: Mapped[str | None] = mapped_column(String(36)) sticker_key: Mapped[str | None] = mapped_column(String(32)) 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(), ) # See FeedPost.last_edited_at — only set by the PATCH endpoint, so # the FE "已修改" badge stays accurate. last_edited_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) edit_count: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) class FeedReport(Base): """One employee report for a feed post or comment. The reporter is kept private from the ordinary feed and the moderation response is separate from unmasking an anonymous author. This makes "hide harmful content" the routine operation and identity disclosure an exceptional, explicitly audited operation. """ __tablename__ = "feed_reports" __table_args__ = ( UniqueConstraint( "resource_type", "resource_id", "reporter_person_id", name="uq_feed_reports_resource_reporter", ), Index("ix_feed_reports_status_created", "status", "created_at"), Index("ix_feed_reports_resource", "resource_type", "resource_id"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) resource_type: Mapped[str] = mapped_column(String(16), nullable=False) resource_id: Mapped[str] = mapped_column(String(36), nullable=False) reporter_person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) reason: Mapped[str] = mapped_column(String(32), nullable=False) detail: Mapped[str | None] = mapped_column(Text) status: Mapped[str] = mapped_column( String(16), nullable=False, default=REPORT_STATUS_OPEN, ) resolved_by_person_id: Mapped[str | None] = mapped_column( String(36), ForeignKey("identity_persons.id"), ) resolution_note: Mapped[str | None] = mapped_column(Text) resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) class FeedLike(Base): __tablename__ = "feed_likes" __table_args__ = ( UniqueConstraint( "post_id", "person_id", name="uq_feed_likes_post_person", ), Index("ix_feed_likes_post", "post_id"), Index("ix_feed_likes_person", "person_id"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) post_id: Mapped[str] = mapped_column( String(36), ForeignKey("feed_posts.id"), nullable=False, ) person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) class FeedMention(Base): __tablename__ = "feed_mentions" __table_args__ = ( Index( "ix_feed_mentions_mentioned", "mentioned_person_id", "created_at", ), Index("ix_feed_mentions_post", "post_id"), Index("ix_feed_mentions_comment", "comment_id"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) mentioned_person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) post_id: Mapped[str | None] = mapped_column( String(36), ForeignKey("feed_posts.id"), ) comment_id: Mapped[str | None] = mapped_column( String(36), ForeignKey("feed_comments.id"), ) author_person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) class FeedAttachment(Base): __tablename__ = "feed_attachments" __table_args__ = ( Index("ix_feed_attachments_post", "post_id", "position"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) post_id: Mapped[str] = mapped_column( String(36), ForeignKey("feed_posts.id"), nullable=False, ) s3_key: Mapped[str] = mapped_column(String(512), nullable=False) mime_type: Mapped[str | None] = mapped_column(String(64)) size_bytes: Mapped[int | None] = mapped_column(Integer) width: Mapped[int | None] = mapped_column(Integer) height: Mapped[int | None] = mapped_column(Integer) position: Mapped[int] = mapped_column(Integer, nullable=False, default=0) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) post: Mapped[FeedPost] = relationship(back_populates="attachments") class FeedBlessing(Base): __tablename__ = "feed_blessings" __table_args__ = ( UniqueConstraint( "celebration_id", "sender_person_id", name="uq_feed_blessings_celebration_sender", ), Index("ix_feed_blessings_celebration", "celebration_id"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) celebration_id: Mapped[str] = mapped_column( String(36), ForeignKey("feed_celebrations.id"), nullable=False, ) sender_person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) sticker_key: Mapped[str] = mapped_column(String(32), nullable=False) message: Mapped[str] = mapped_column(Text, nullable=False) mirror_comment_id: Mapped[str | None] = mapped_column( String(36), ForeignKey("feed_comments.id"), ) # When the celebrant acknowledged this specific blessing (migration 456). # Only they can, and only once, so this lives on the blessing rather than # in a reactions table — the alternative models a many-to-many that cannot # exist. NULL = not yet acknowledged. acked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) class FeedAnnouncement(Base): __tablename__ = "feed_announcements" __table_args__ = ( Index( "ix_feed_announcements_status_publish", "status", "publish_at", ), Index( "ix_feed_announcements_pinned", "is_pinned", "publish_at", ), Index("ix_feed_announcements_expire", "expire_at"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) title: Mapped[str] = mapped_column(String(256), nullable=False) body_markdown: Mapped[str] = mapped_column(Text, nullable=False) category: Mapped[str] = mapped_column( String(32), nullable=False, default="general", ) color: Mapped[str] = mapped_column( String(32), nullable=False, default="default", ) is_pinned: Mapped[bool] = mapped_column( Boolean, nullable=False, default=False, ) publish_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) expire_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) status: Mapped[str] = mapped_column( String(16), nullable=False, default=ANNOUNCEMENT_STATUS_PUBLISHED, ) attachments_json: Mapped[list[dict[str, Any]] | None] = mapped_column(JSON) author_person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) 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(), ) deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) class IdentityPersonPreference(Base): """Generic per-person KV preferences. First keys live under the ``feed.*`` namespace (``feed.birthday_visibility``, ``feed.notify_comment_feishu``, ``feed.notify_like_aggregate``) but the table is intentionally generic so future domains can add their own without another migration. """ __tablename__ = "identity_person_preferences" __table_args__ = ( UniqueConstraint( "person_id", "pref_key", name="uq_identity_person_preferences_key", ), Index("ix_identity_person_preferences_person", "person_id"), ) id: Mapped[str] = mapped_column( String(36), primary_key=True, default=lambda: str(uuid.uuid4()), ) person_id: Mapped[str] = mapped_column( String(36), ForeignKey("identity_persons.id"), nullable=False, ) pref_key: Mapped[str] = mapped_column(String(64), nullable=False) pref_value: Mapped[Any | None] = mapped_column(JSON) 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(), ) # Preference keys PREF_BIRTHDAY_VISIBILITY = "feed.birthday_visibility" # Which celebrations may be generated for this person at all. # Birthday visibility was governable; the work anniversary was NOT — it was # generated unconditionally and additionally pushed to the person's manager and # HRBP, with no way to decline. ``none`` (default) preserves today's behaviour. PREF_CELEBRATION_OPT_OUT = "feed.celebration_opt_out" CELEBRATION_OPT_OUT_NONE = "none" CELEBRATION_OPT_OUT_BIRTHDAY = "birthday" CELEBRATION_OPT_OUT_ANNIVERSARY = "anniversary" CELEBRATION_OPT_OUT_ALL = "all" CELEBRATION_OPT_OUT_VALUES = ( CELEBRATION_OPT_OUT_NONE, CELEBRATION_OPT_OUT_BIRTHDAY, CELEBRATION_OPT_OUT_ANNIVERSARY, CELEBRATION_OPT_OUT_ALL, ) # Feishu push for each blessing received. Comments and likes already had a # per-channel toggle; blessings did not, and they are the highest-volume of the # three on the one day they fire. PREF_NOTIFY_BLESSING_FEISHU = "feed.notify_blessing_feishu" PREF_NOTIFY_COMMENT_FEISHU = "feed.notify_comment_feishu" PREF_NOTIFY_LIKE_AGGREGATE = "feed.notify_like_aggregate" # Appearance preferences (cross-device, hydrated by the frontend theme # store from GET /me/preferences). ``theme_mode`` drives the app-wide # light/dark ConfigProvider algorithm; ``festive_effects`` toggles the # ambient festival skin (FestiveLayer). Stored in the same generic KV # table so no migration is needed. PREF_THEME_MODE = "appearance.theme_mode" PREF_FESTIVE_EFFECTS = "appearance.festive_effects" # Opt-out of the once-a-year 年度报告 (Helix Wrapped) Feishu push. Default # False = opted in (the push has its own guardrails: once/year + storm cap). PREF_WRAPPED_PUSH_OPT_OUT = "appearance.wrapped_push_opt_out" # Birthday visibility values BIRTHDAY_VISIBILITY_MONTH_DAY = "month_day" BIRTHDAY_VISIBILITY_HIDDEN = "hidden" # Theme mode values. ``system`` follows the OS prefers-color-scheme on # every device until the user makes an explicit light/dark choice. THEME_MODE_VALUES = ("light", "dark", "system") THEME_MODE_DEFAULT = "system" DEFAULT_PREFERENCES: dict[str, Any] = { PREF_BIRTHDAY_VISIBILITY: BIRTHDAY_VISIBILITY_MONTH_DAY, PREF_THEME_MODE: THEME_MODE_DEFAULT, PREF_FESTIVE_EFFECTS: True, PREF_WRAPPED_PUSH_OPT_OUT: False, PREF_NOTIFY_COMMENT_FEISHU: True, # ``notify_like_aggregate`` is opt-OUT (default True = bell lights up # on likes). 2026-06-02 flipped from opt-in to opt-out — the old # default-False shipped a "lever that did nothing" because every # user defaulted to off, the like-handler short-circuited, and the # bell never fired for anyone. Default True restores meaningful # default behaviour AND keeps the opt-out semantics any prior user # set to False is still honoured (their explicit False survives the # default flip — the pref-resolution code reads stored value first, # default only on absence). PREF_NOTIFY_LIKE_AGGREGATE: True, # Blessings push by default, matching comments and likes. Declared here # rather than defaulted at the read site so it appears in # ``GET /me/preferences`` and is therefore visible — and switchable — in # the preference centre. A toggle the UI can't see is not a toggle. PREF_NOTIFY_BLESSING_FEISHU: True, # Celebrations are generated for everyone unless the person says otherwise. # ``none`` preserves the pre-2026-08-09 behaviour; the point of the setting # is that the work anniversary — which also pushes to the person's manager # and HRBP — previously had no off switch at all. PREF_CELEBRATION_OPT_OUT: CELEBRATION_OPT_OUT_NONE, } __all__ = [ "ANNOUNCEMENT_STATUS_ARCHIVED", "ANNOUNCEMENT_STATUS_DRAFT", "ANNOUNCEMENT_STATUS_PUBLISHED", "BIRTHDAY_VISIBILITY_HIDDEN", "BIRTHDAY_VISIBILITY_MONTH_DAY", "CELEBRATION_TYPE_ANNIVERSARY", "CELEBRATION_TYPE_BIRTHDAY", "DEFAULT_PREFERENCES", "FeedAnnouncement", "FeedAttachment", "FeedBlessing", "FeedCelebration", "FeedComment", "FeedLike", "FeedMention", "FeedPost", "IdentityPersonPreference", "POST_CATEGORY_GENERAL", "POST_CATEGORY_OTHER", "POST_CATEGORY_WORK_PLAN", "POST_TYPE_AGGREGATE_ANNIVERSARY", "POST_TYPE_AGGREGATE_BIRTHDAY", "POST_TYPE_SYSTEM_ANNIVERSARY", "POST_TYPE_SYSTEM_BIRTHDAY", "POST_TYPE_USER", "PREF_BIRTHDAY_VISIBILITY", "PREF_FESTIVE_EFFECTS", "PREF_NOTIFY_COMMENT_FEISHU", "PREF_NOTIFY_LIKE_AGGREGATE", "PREF_THEME_MODE", "PREF_WRAPPED_PUSH_OPT_OUT", "SYSTEM_POST_TYPES", "THEME_MODE_DEFAULT", "THEME_MODE_VALUES", "VISIBILITY_DEPARTMENT", "VISIBILITY_PUBLIC", ]