Bulletin Board (Forum)

Column-level detail for the entire bulletin schema (board/category/post/comment/reaction/report/block)

Overview

The bulletin schema has 8 tables: board (one board per LINE OA) → category → post → comment, supported by reaction, report, block (user moderation blocks) and audit_log. This schema is applied separately with psql rather than through Prisma (migrations 001-005). Since migration 005 the board is deliberately a forum — threads with titles, ordered by last reply — rather than a social feed.

Table `bulletin.board`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`line_oa_id`	`INTEGER`	NO	-	Owning LINE OA (unique — one board per OA)
`organization_id`	`INTEGER`	NO	-	Owning organization
`name`	`VARCHAR(255)`	NO	`'Bulletin Board'`	Board name
`description`	`TEXT`	YES	-	Board description
`settings`	`JSONB`	NO	`'{}'`	Board-level settings such as write access and image limits
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`created_by`	`INTEGER`	YES	-	Creator user id
`updated_by`	`INTEGER`	YES	-	Last updater user id
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp

Table `bulletin.category`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`board_id`	`INTEGER`	NO	-	FK to `bulletin.board(id)`, ON DELETE CASCADE
`line_oa_id`	`INTEGER`	NO	-	LINE OA this category belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`name`	`VARCHAR(100)`	NO	-	Category name
`color`	`VARCHAR(20)`	YES	-	Category badge colour
`sort_order`	`INTEGER`	NO	`0`	Display order
`access_mode`	`VARCHAR(20)`	NO	`'inherit'`	View axis — `inherit` follows the board's rules
`audience_ids`	`JSONB`	NO	`'[]'`	Audiences allowed to see this category (used with `access_mode`)
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`created_by`	`INTEGER`	YES	-	Creator user id
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Soft-delete timestamp
`post_access`	`VARCHAR(20)`	NO	`'member'`	Post axis — `member` lets LINE users post, `admin` restricts posting to the CMS (migration 004)

Table `bulletin.post`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`board_id`	`INTEGER`	NO	-	FK to `bulletin.board(id)`, ON DELETE CASCADE
`category_id`	`INTEGER`	YES	-	FK to `bulletin.category(id)`, ON DELETE SET NULL
`line_oa_id`	`INTEGER`	NO	-	LINE OA this thread belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`author_type`	`VARCHAR(20)`	NO	-	Author kind: `line_user` or `admin`
`author_line_user_id`	`VARCHAR(255)`	YES	-	Author's LINE user id (when `author_type = 'line_user'`)
`author_user_id`	`INTEGER`	YES	-	CMS user id (when `author_type = 'admin'`)
`body`	`TEXT`	NO	-	Thread body
`images`	`JSONB`	NO	`'[]'`	Attached images
`status`	`VARCHAR(20)`	NO	`'published'`	Thread status (published / pending / hidden)
`allow_comments`	`BOOLEAN`	NO	`true`	Whether replies are accepted
`is_pinned`	`BOOLEAN`	NO	`false`	Whether the thread is pinned to the top
`pinned_at`	`TIMESTAMPTZ(3)`	YES	-	When it was pinned (orders the pinned set)
`comment_count`	`INTEGER`	NO	`0`	Published reply count — trigger-owned, never written by the app
`reaction_count`	`INTEGER`	NO	`0`	Reaction count — trigger-owned
`report_count`	`INTEGER`	NO	`0`	Open report count — trigger-owned
`edited_date`	`TIMESTAMPTZ(3)`	YES	-	When the author last edited the content
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Soft-delete timestamp
`title`	`VARCHAR(200)`	NO	-	Thread subject — NOT NULL with no default, deliberately (migration 005)
`last_activity_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Last reply time, used for forum ordering — trigger-owned (migration 005)

Table `bulletin.comment`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`post_id`	`INTEGER`	NO	-	FK to `bulletin.post(id)`, ON DELETE CASCADE
`board_id`	`INTEGER`	NO	-	Owning board (denormalized for board-wide moderation queries)
`line_oa_id`	`INTEGER`	NO	-	LINE OA this reply belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`author_type`	`VARCHAR(20)`	NO	-	Author kind: `line_user` or `admin`
`author_line_user_id`	`VARCHAR(255)`	YES	-	Author's LINE user id
`author_user_id`	`INTEGER`	YES	-	CMS user id
`body`	`TEXT`	NO	-	Reply body
`quote_comment_id`	`INTEGER`	YES	-	FK to `bulletin.comment(id)` — the quoted reply, ON DELETE SET NULL
`status`	`VARCHAR(20)`	NO	`'published'`	Reply status
`reaction_count`	`INTEGER`	NO	`0`	Reaction count — trigger-owned
`report_count`	`INTEGER`	NO	`0`	Open report count — trigger-owned
`edited_date`	`TIMESTAMPTZ(3)`	YES	-	When the author last edited the content
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Soft-delete timestamp
`images`	`JSONB`	NO	`'[]'`	Images attached to the reply (migration 005)

Table `bulletin.reaction`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`target_type`	`VARCHAR(10)`	NO	-	Target kind: `post` or `comment`
`target_id`	`INTEGER`	NO	-	Id of the reacted-to thread or reply
`line_oa_id`	`INTEGER`	NO	-	LINE OA this reaction belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`line_user_id`	`VARCHAR(255)`	NO	-	LINE user id of the reactor
`emoji`	`VARCHAR(16)`	NO	-	The emoji used
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp

Table `bulletin.report`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`target_type`	`VARCHAR(10)`	NO	-	Target kind: `post` or `comment`
`target_id`	`INTEGER`	NO	-	Id of the reported thread or reply
`board_id`	`INTEGER`	NO	-	Owning board (drives the moderation queue)
`line_oa_id`	`INTEGER`	NO	-	LINE OA this report belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`reporter_line_user_id`	`VARCHAR(255)`	NO	-	LINE user id of the reporter
`reason`	`VARCHAR(50)`	NO	-	Report reason (from a fixed list)
`detail`	`TEXT`	YES	-	Free-text detail from the reporter
`status`	`VARCHAR(20)`	NO	`'open'`	Handling status
`resolved_by`	`INTEGER`	YES	-	Moderator who closed the report
`resolved_date`	`TIMESTAMPTZ(3)`	YES	-	When it was closed
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp

Table `bulletin.block`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`board_id`	`INTEGER`	NO	-	Board the block applies to
`line_oa_id`	`INTEGER`	NO	-	LINE OA this block belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`line_user_id`	`VARCHAR(255)`	NO	-	LINE user id of the blocked user
`scope`	`VARCHAR(20)`	NO	`'all'`	Block scope, e.g. `all` or `comment`
`reason`	`TEXT`	YES	-	Reason for the block
`expires_date`	`TIMESTAMPTZ(3)`	YES	-	When the block expires (NULL = indefinite)
`created_by`	`INTEGER`	YES	-	Moderator who applied the block
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`revoked_date`	`TIMESTAMPTZ(3)`	YES	-	When the block was revoked
`revoked_by`	`INTEGER`	YES	-	Moderator who revoked it

Table `bulletin.audit_log`

Column	Type	Nullable	Default	Description
`id`	`BIGSERIAL`	NO	auto increment	Primary key
`board_id`	`INTEGER`	YES	-	Related board (nullable for events not tied to one)
`line_oa_id`	`INTEGER`	NO	-	LINE OA this event belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`actor_type`	`VARCHAR(20)`	NO	-	Actor kind, e.g. admin / line_user / system
`actor_id`	`VARCHAR(255)`	YES	-	Actor identifier
`actor_name`	`VARCHAR(255)`	YES	-	Actor name as of the time of the event
`action`	`VARCHAR(50)`	NO	-	Action taken, e.g. delete post, approve comment, block user
`target_type`	`VARCHAR(20)`	NO	-	Kind of the affected object
`target_id`	`INTEGER`	YES	-	Id of the affected object
`before`	`JSONB`	YES	-	State before the change
`after`	`JSONB`	YES	-	State after the change
`ip`	`VARCHAR(45)`	YES	-	Actor IP address
`user_agent`	`TEXT`	YES	-	Actor user agent
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp

Notes

Check constraints chk_post_author / chk_comment_author: an XOR on author identity — author_type = 'line_user' requires author_line_user_id with author_user_id NULL, and author_type = 'admin' requires author_user_id with author_line_user_id NULL. Without the IS NULL halves a row could carry both identities
Check constraint chk_category_post_access: post_access must be member or admin. It is the last line of defence — enforcement is server side (client-api returns 403 BULLETIN_CATEGORY_ADMIN_ONLY); hiding the category in the composer is a convenience, not the control
A category carries two independent access axes: the view axis (access_mode + audience_ids) and the post axis (post_access). They compose, and neither implies the other. The useful pairing is access_mode = 'inherit' with post_access = 'admin': everyone reads it, only the CMS writes it — an Announcements category
Unique: board.line_oa_id (one board per OA); idx_bul_reaction_one on (target_type, target_id, line_user_id) — one reaction per user per target; idx_bul_report_once on (target_type, target_id, reporter_line_user_id); idx_bul_block_active on (board_id, line_user_id) WHERE revoked_date IS NULL
Block caveat: an expired block still has revoked_date IS NULL and therefore still occupies the unique slot. Creating a block must revoke any existing row for that user in the same transaction (supersede semantics); otherwise re-blocking an expired user, or escalating comment to all, raises a unique violation
Counter triggers (migration 003, revised in 005): trg_bulletin_comment_count, trg_bulletin_reaction_count and trg_bulletin_report_count recompute comment_count, reaction_count, report_count and last_activity_date whenever the source rows change, holding pg_advisory_xact_lock (lock space 4201 = post, 4202 = comment) to serialise writers. Measured alternatives failed: no lock left counts permanently wrong, and SELECT ... FOR UPDATE deadlocked most concurrent inserts. The application must never write these columns
last_activity_date can un-bump: it is computed as GREATEST(post.created_date, COALESCE(max(comment.created_date), post.created_date)), so deleting or hiding the last reply pulls the thread back down to its post time rather than stranding it at the top
Feed indexes: idx_bul_post_feed (ordered by created_date) serves the CMS, and idx_bul_post_activity (ordered by last_activity_date) serves the LIFF forum list. Both end in id DESC as the keyset cursor tiebreak and carry WHERE deleted_date IS NULL
Migration 005 wipes content: TRUNCATE ... RESTART IDENTITY on reaction, report, comment and post so that post.title could land NOT NULL with no lingering default. board, category, block and audit_log are untouched — they are operator state, not content

Overview​

Table bulletin.board​

Table bulletin.category​

Table bulletin.post​

Table bulletin.comment​

Table bulletin.reaction​

Table bulletin.report​

Table bulletin.block​

Table bulletin.audit_log​

Notes​