Skip to content

Database Models

FenLiu uses SQLAlchemy ORM with these core models.

Post

Represents a post fetched from the Fediverse.

Fields: - id (int): Primary key - post_id (str): ActivityPub URI (unique) - url (str, optional): Human-readable web URL - content (str): Post text (may include HTML) - author_username (str, optional): Account username - author_display_name (str, optional): Display name - author_url (str, optional): Author profile URL - author_is_bot (bool): Whether the account is a bot - instance (str): Fediverse instance the post came from - hashtags (JSON list, optional): Hashtags in the post - media_attachments (JSON list, optional): Images/videos - boosts (int): Number of boosts/reblogs - likes (int): Number of favourites/likes - replies (int): Number of replies - created_at (datetime, optional): Post creation time on Fediverse - fetched_at (datetime): When FenLiu fetched the post - processed (bool): Whether the post has been processed - reviewed (bool): Has been reviewed - reviewed_at (datetime, optional): When reviewed - auto_reject_reason (str, optional): Why auto-reject rejected the post (set by reject_blocked_posts) - approved (bool, optional): True = approved, False = rejected, None = not reviewed - curated_exported (bool): Whether exported to curated queue - export_date (datetime, optional): When first exported - text_flagged (bool, optional): AI text check — the text stage recommends rejection - text_reason (str, optional): Why the AI flagged the text - vision_accepted (bool, optional): AI vision check — the image passed the vision policy - vision_category (str, optional): category from the configured vision policy (default: real_cat / comic / illustration / meme / …) - vision_confidence (float, optional): 0.0–1.0 - vision_reason (str, optional): Why the AI made its call - ai_classified_at (datetime, optional): When the AI finished classifying - ai_would_reject (bool, optional): Combined AI recommendation (indexed) - queue_status (str, optional): pending / reserved / delivered / error - reserved_at (datetime, optional): When reserved for delivery - delivered_at (datetime, optional): When successfully delivered (ack'd) - error_reason (str, optional): Export failure reason (stable string, no variable data) - error_details (JSON, optional): Structured metadata for richer error display (e.g., duplicate-attachment image URLs and dhashes) - errored_at (datetime, optional): When the error occurred

Relationships: - stream: Parent HashtagStream - review_feedback: Related ReviewFeedback records

HashtagStream

Configuration for monitoring a hashtag.

Fields: - id (int): Primary key - hashtag (str): Hashtag name (without #, unique) - instance (str): Mastodon instance to fetch from - active (bool): Monitoring enabled - last_check (datetime, optional): Last successful fetch time - created_at (datetime): Creation time - updated_at (datetime, optional): Last modification time - enable_scheduling (bool): Whether auto-fetch is enabled (default: true) - fetch_interval_minutes (int): Minutes between scheduled fetches (default: 60) - next_scheduled_fetch (datetime, optional): When the next auto-fetch will run

Relationships: - posts: Related Post records (cascade delete)

ReviewFeedback

Records user review decisions.

Fields: - id (int): Primary key - post_id (int, FK): Foreign key to Post (cascade-deleted when Post is deleted) - decision (str): "approved" or "rejected" - created_at (datetime): When the decision was made

Relationships: - post: Parent Post

BlockedUser

Blocklist of accounts to exclude from export. Supports pattern matching.

Fields: - id (int): Primary key - account_identifier (str): Account or pattern (e.g. @user@instance.social, *.bsky.app) - pattern_type (str): Match mode — exact, suffix, prefix, or contains - notes (str, optional): Reason for blocking - created_at (datetime): When added

BlockedHashtag

Blocklist of hashtags to exclude from export.

Fields: - id (int): Primary key - hashtag (str): Hashtag name (lowercase, without #, unique) - notes (str, optional): Reason for blocking - created_at (datetime): When added

AppSetting

Configuration storage (key-value pairs, JSON-encoded values).

Fields: - key (str): Setting name (primary key) - value (str): JSON-encoded value - updated_at (datetime): Last update time

Common Keys: - attachments_only: "true" or "false" - auto_reject_blocked: "true" or "false" - auto_reject_bots: "true" or "false"

QueueStats

Singleton row tracking aggregate counts for posts that have been deleted (to preserve all-time statistics).

Fields: - id (int): Primary key (always 1) - total_deleted_delivered (int): Delivered posts removed by cleanup - total_deleted_reserved (int): Reserved posts removed by cleanup - total_deleted_pending (int): Pending posts removed by trim

Used by the Queue Preview and Statistics pages to show all-time delivered/pending counts.

ErrorHistory

Audit trail of deleted error posts. Preserves the error reason and details after cleanup so statistics remain accurate over time.

Fields: - id (int): Primary key - error_reason (str, optional): The error reason recorded before deletion - error_details (JSON, optional): Structured error metadata copied from the post at deletion time - deleted_at (datetime): When the error post was deleted

Entity Relationships

HashtagStream
  └── Post (one-to-many, cascade delete)
      └── ReviewFeedback (one-to-many)

BlockedUser (standalone)
BlockedHashtag (standalone)
AppSetting (standalone key-value)
QueueStats (singleton — id always 1)
ErrorHistory (append-only audit log)

Queue State Machine

Posts transition through states:

pending → reserved → delivered (ack)
               ↓
            pending  (nack or 5-min timeout)

pending → reserved → error (permanent failure)
                          ↓
                       pending  (manual requeue)

AI Classification Fields

Eight columns track the AI verdict:

  • text_flagged / text_reason — text-stage check
  • vision_accepted / vision_category / vision_confidence / vision_reason — image check
  • ai_classified_at — when classification finished
  • ai_would_reject — combined recommendation (indexed, drives the review filters)

Database Schema

Created via SQLAlchemy and Alembic migrations. See alembic/versions/ for schema history.

Current schema version automatically applied on startup via alembic upgrade head.

Indexing

Indexes created for performance:

  • Post.post_id (unique)
  • Post.queue_status
  • Post.delivered_at
  • HashtagStream.hashtag (unique)

Next Steps