Skip to main content

Bulletin Board (Forum)

Overview​

An add-on app: a noticeboard/forum running inside LIFF where LINE friends can post threads, reply, react with emoji, and report content, paired with CMS-side moderation tools for approving, hiding posts, and blocking users.

All of its tables live in the bulletin schema, separate from public, with no Prisma definitions — they're applied with psql. The most recent migration (numbered 005) changed the app's meaning from a "social feed" into an actual "forum": every thread now requires a title, and listings sort by latest reply instead of creation date.

Core Data Structure​

bulletin.board — the board​

One board per LINE OA, enforced with a UNIQUE constraint on line_oa_id.

  • name, description, and a JSONB settings column that bundles every setting in one place — e.g. max images per post and posting permissions

bulletin.category — categories​

Categories have two completely independent permission axes:

AxisColumnMeaning
Visibility axisaccess_mode (default inherit) + audience_idsWho can see this category
Posting axispost_access (member or admin, default member)Who can post in this category

The most common combination is inherit paired with admin, which gives an "Announcements" category everyone can read but only the CMS can write to. The permission is actually enforced server-side (client-api replies with 403 and code BULLETIN_CATEGORY_ADMIN_ONLY); the database CHECK constraint is the last line of defense.

bulletin.post — threads​

  • title VARCHAR(200) is NOT NULL with intentionally no default — this column was added in migration 005 alongside a cleanup of existing content, because giving it a default would have let title-less threads slip through forever
  • body and images are JSONB
  • Authorship follows an XOR pattern: author_type (line_user or admin) paired with either author_line_user_id or author_user_id, but never both, enforced by the chk_post_author CHECK constraint
  • Status and placement: status, allow_comments, is_pinned, and pinned_at
  • Counters that triggers own exclusively — the application must never write to them: comment_count, reaction_count, and report_count
  • last_activity_date is computed by a trigger from the latest comment, and is used as the forum's sort key
  • Two keyset indexes exist because two screens sort differently: (board_id, status, created_date DESC, id DESC) for the CMS, and (board_id, status, last_activity_date DESC, id DESC) for LIFF

Supporting tables​

  • bulletin.comment — comments, using the same XOR authorship pattern as posts, with a self-referencing quote_comment_id for quoting and a JSONB images column
  • bulletin.reaction — unique on (target_type, target_id, line_user_id), enforcing one reaction per person per target
  • bulletin.report — unique on (target_type, target_id, reporter_line_user_id), with a status column (default open)
  • bulletin.block — user blocks, using a partial unique index (board_id, line_user_id) WHERE revoked_date IS NULL. A note in the code warns that an expired block still occupies this slot, so a new block must revoke the old one within the same transaction
  • bulletin.audit_log — records every moderator action with before/after snapshots as JSONB

Counter triggers​

The triggers that maintain the counters use pg_advisory_xact_lock (lock space 4201 for threads, 4202 for comments) instead of SELECT ... FOR UPDATE. The migration's comments cite real measurements: with 30 concurrent inserts and no locking, the counts end up permanently wrong; with FOR UPDATE, 25 out of 30 attempts deadlocked; with the advisory lock, all 30 came out correct.

  • apps/bulletin/migrations/001_create_schema.sql — the CREATE SCHEMA bulletin statement
  • apps/bulletin/migrations/002_create_tables.sql — all 8 core tables
  • apps/bulletin/migrations/003_create_counter_triggers.sql — the comment/reaction/report counter triggers
  • apps/bulletin/migrations/004_add_category_post_access.sql — the posting permission axis
  • apps/bulletin/migrations/005_forum_semantics.sql — thread titles, comment images, and last_activity_date, with the reasoning for the data cleanup documented in detail

Connections to Other Services​

  • client-api-go serves the feed, threads, comments, reactions, and all reporting from LIFF, enforcing both permission axes and the block check
  • cms-api-go handles the moderation queue, posting on the admin's behalf, category management, and blocking — fields owned by triggers are marked read-only in GORM with the -> tag
  • Uses audience_ids from Audience, references users via the LINE userId from LINE Friends, and is toggled on/off via line_oa_app
  • Note: the bulletin schema doesn't appear in schema-dumps/2026-07-24/schema.sql, since that dump only covers public and appointment