Database Schema

This document provides a comprehensive reference for the Quackback database schema. All tables use PostgreSQL with Drizzle ORM for type-safe database access.

Overview

Quackback uses a PostgreSQL database with the following characteristics:

TypeIDs: All primary keys use TypeID format (UUID storage with type-prefixed strings in the application layer, e.g., post_01h455vb4pex5vsknk084sn02q)
Timestamps: All timestamp columns use timestamp with time zone
Soft deletes: Most entity tables support soft deletion via deleted_at columns
Full-text search: Posts have a generated search_vector column for PostgreSQL full-text search
Relational queries: Drizzle relations enable type-safe joins and nested queries

When working with the database, always import from @/lib/db rather than @quackback/db directly. This ensures proper connection handling.

Table Categories

Category	Tables
Authentication	user, session, account, verification, one_time_token, member, invitation, settings
Content	boards, posts, comments, votes, tags, post_tags, roadmaps, post_roadmaps
Status & Workflow	post_statuses
History & Notes	post_edit_history, comment_edit_history, post_notes, comment_reactions
Integrations	integrations, integration_platform_credentials, integration_event_mappings, post_external_links
Notifications	in_app_notifications, post_subscriptions, notification_preferences, unsubscribe_tokens
Changelog	changelog_entries, changelog_entry_posts
AI Features	post_sentiment, merge_suggestions
Feedback Pipeline	feedback_sources, raw_feedback_items, feedback_signals, feedback_suggestions, feedback_signal_corrections, external_user_mappings
Segments	segments, user_segments, user_attribute_definitions
API	api_keys, webhooks

Authentication Tables

user

User identities for the application. Managed by Better Auth with custom extensions.

Column	Type	Constraints	Description
`id`	TypeID (user)	PRIMARY KEY	Unique user identifier
`name`	text	NOT NULL	Display name
`email`	text	NOT NULL, UNIQUE	Email address
`email_verified`	boolean	NOT NULL, DEFAULT false	Email verification status
`image`	text		Profile image URL
`image_key`	text		S3 storage key for profile image
`metadata`	text		General user metadata (JSON)
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Indexes:

user_email_idx (unique) on email

Relations:

Has many: sessions, accounts, members, invitations

session

User authentication sessions.

Column	Type	Constraints	Description
`id`	text	PRIMARY KEY	Session identifier (Better Auth generated)
`expires_at`	timestamptz	NOT NULL	Session expiration time
`token`	text	NOT NULL, UNIQUE	Session token
`ip_address`	text		Client IP address
`user_agent`	text		Client user agent
`user_id`	TypeID (user)	NOT NULL, FK -> user.id ON DELETE CASCADE	Associated user
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL	Last update timestamp

Indexes:

session_userId_idx on user_id

Relations:

Belongs to: user

account

OAuth and authentication provider accounts.

Column	Type	Constraints	Description
`id`	TypeID (account)	PRIMARY KEY	Account identifier
`account_id`	text	NOT NULL	External account ID
`provider_id`	text	NOT NULL	Auth provider (google, github, etc.)
`user_id`	TypeID (user)	NOT NULL, FK -> user.id ON DELETE CASCADE	Associated user
`access_token`	text		OAuth access token
`refresh_token`	text		OAuth refresh token
`id_token`	text		OAuth ID token
`access_token_expires_at`	timestamptz		Access token expiration
`refresh_token_expires_at`	timestamptz		Refresh token expiration
`scope`	text		OAuth scopes
`password`	text		Hashed password (for password auth)
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL	Last update timestamp

Indexes:

account_userId_idx on user_id

Relations:

Belongs to: user

verification

Email and other verification tokens.

Column	Type	Constraints	Description
`id`	text	PRIMARY KEY	Verification identifier
`identifier`	text	NOT NULL	Target identifier (email, etc.)
`value`	text	NOT NULL	Verification code/token
`expires_at`	timestamptz	NOT NULL	Expiration time
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Indexes:

verification_identifier_idx on identifier

one_time_token

Secure cross-domain session transfer tokens (used during workspace provisioning).

Column	Type	Constraints	Description
`id`	text	PRIMARY KEY	Token identifier
`token`	text	NOT NULL	One-time token value
`user_id`	TypeID (user)	NOT NULL, FK -> user.id ON DELETE CASCADE	Associated user
`expires_at`	timestamptz	NOT NULL	Expiration time
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp

Relations:

Belongs to: user

member

Unified membership records linking users to workspace roles.

Column	Type	Constraints	Description
`id`	TypeID (member)	PRIMARY KEY	Member identifier
`user_id`	TypeID (user)	NOT NULL, FK -> user.id ON DELETE CASCADE, UNIQUE	Associated user
`role`	text	NOT NULL, DEFAULT 'member'	Role: 'admin', 'member', or 'user'
`created_at`	timestamptz	NOT NULL	Creation timestamp

Role Descriptions:

admin: Full administrative access, can manage settings and team
member: Team member access, can manage feedback
user: Portal user access only, can vote/comment on public portal

Indexes:

member_user_idx (unique) on user_id
member_role_idx on role

Relations:

Belongs to: user
Has many: posts (as author), posts (as owner), comments, votes, post_subscriptions

invitation

Team member invitations.

Column	Type	Constraints	Description
`id`	TypeID (invite)	PRIMARY KEY	Invitation identifier
`email`	text	NOT NULL	Invitee email address
`name`	text		Invitee name
`role`	text		Assigned role on acceptance
`status`	text	NOT NULL, DEFAULT 'pending'	Status: pending, accepted, expired
`expires_at`	timestamptz	NOT NULL	Expiration time
`last_sent_at`	timestamptz		Last email sent timestamp
`inviter_id`	TypeID (user)	NOT NULL, FK -> user.id ON DELETE CASCADE	User who sent invitation
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp

Indexes:

invitation_email_idx on email
invitation_email_status_idx on (email, status)

Relations:

Belongs to: user (inviter)

settings

Application settings and branding configuration. Single row in self-hosted deployments.

Column	Type	Constraints	Description
`id`	TypeID (workspace)	PRIMARY KEY	Settings identifier
`name`	text	NOT NULL	Workspace/application name
`slug`	text	NOT NULL, UNIQUE	URL-safe identifier
`logo_key`	text		S3 storage key for logo image
`favicon_key`	text		S3 storage key for favicon
`header_logo_key`	text		S3 storage key for header logo (horizontal wordmark)
`header_display_mode`	text	DEFAULT 'logo_and_name'	Header style: 'logo_and_name', 'logo_only', 'custom_logo'
`header_display_name`	text		Custom header display name
`auth_config`	text		Team auth settings (JSON)
`portal_config`	text		Portal feature settings (JSON)
`branding_config`	text		Theme/branding settings (JSON)
`custom_css`	text		Custom CSS for portal
`developer_config`	text		Developer settings (JSON), e.g. MCP config
`setup_state`	text		Onboarding state tracking (JSON)
`metadata`	text		Additional metadata (JSON)
`created_at`	timestamptz	NOT NULL	Creation timestamp

JSON Column Schemas:

auth_config:

{
  "oauth": { "google": boolean, "github": boolean, "microsoft": boolean },
  "ssoRequired": boolean,
  "openSignup": boolean
}

portal_config:

{
  "oauth": { "password": boolean, "email": boolean, "google": boolean, "github": boolean },
  "features": { "publicView": boolean, "submissions": boolean, "comments": boolean, "voting": boolean }
}

branding_config:

{
  "preset": string,
  "light": { /* ThemeColors */ },
  "dark": { /* ThemeColors */ }
}

setup_state:

{
  "version": number,
  "steps": { "core": boolean, "workspace": boolean, "boards": boolean },
  "completedAt": "ISO timestamp",
  "source": "cloud" | "self-hosted",
  "useCase": "saas" | "consumer" | "marketplace" | "internal"
}

Content Tables

boards

Feedback boards for organizing posts by topic or product area.

Column	Type	Constraints	Description
`id`	TypeID (board)	PRIMARY KEY	Board identifier
`slug`	text	NOT NULL, UNIQUE	URL-safe identifier
`name`	text	NOT NULL	Display name
`description`	text		Board description
`is_public`	boolean	NOT NULL, DEFAULT true	Public visibility
`settings`	jsonb	NOT NULL, DEFAULT {}	Board-specific settings
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
`deleted_at`	timestamptz		Soft delete timestamp

Indexes:

Unique constraint on slug
boards_is_public_idx on is_public
boards_deleted_at_idx on deleted_at

Settings Schema:

{
  "roadmapStatusIds": ["status_xxx", "status_yyy"]
}

Relations:

Has many: posts

posts

Feedback posts submitted by users or team members.

Column	Type	Constraints	Description
`id`	TypeID (post)	PRIMARY KEY	Post identifier
`board_id`	TypeID (board)	NOT NULL, FK -> boards.id ON DELETE CASCADE	Parent board
`title`	text	NOT NULL	Post title
`content`	text	NOT NULL	Plain text content
`content_json`	jsonb		Rich content (TipTap JSON)
`member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE RESTRICT	Author member
`status_id`	TypeID (status)	FK -> post_statuses.id ON DELETE SET NULL	Current status
`owner_member_id`	TypeID (member)	FK -> member.id ON DELETE SET NULL	Assigned team member
`vote_count`	integer	NOT NULL, DEFAULT 0, CHECK >= 0	Cached vote count
`comment_count`	integer	NOT NULL, DEFAULT 0, CHECK >= 0	Cached comment count
`official_response`	text		Team response text
`official_response_member_id`	TypeID (member)	FK -> member.id ON DELETE SET NULL	Response author
`official_response_at`	timestamptz		Response timestamp
`pinned_comment_id`	TypeID (comment)		Pinned comment as official response
`is_comments_locked`	boolean	NOT NULL, DEFAULT false	Prevent portal users from commenting (team members bypass)
`moderation_state`	text	NOT NULL, DEFAULT 'published'	State: published, pending, spam, archived, closed, deleted
`canonical_post_id`	TypeID (post)		Canonical post this was merged into
`merged_at`	timestamptz		When this post was merged
`merged_by_member_id`	TypeID (member)	FK -> member.id ON DELETE SET NULL	Who performed the merge
`search_vector`	tsvector	GENERATED	Full-text search vector
`embedding`	vector(1536)		Semantic embedding (AI)
`embedding_model`	text		Model used for embedding
`embedding_updated_at`	timestamptz		Embedding generation time
`summary_json`	jsonb		AI-generated summary (structured)
`summary_model`	text		Model used for summary generation
`summary_updated_at`	timestamptz		Summary generation timestamp
`summary_comment_count`	integer		Comment count when summary was last generated
`merge_checked_at`	timestamptz		Last merge suggestion check timestamp
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
`deleted_at`	timestamptz		Soft delete timestamp
`deleted_by_member_id`	TypeID (member)	FK -> member.id ON DELETE SET NULL	Who deleted

Indexes:

posts_board_id_idx on board_id
posts_status_id_idx on status_id
posts_member_id_idx on member_id
posts_owner_member_id_idx on owner_member_id
posts_created_at_idx on created_at
posts_vote_count_idx on vote_count
posts_board_vote_idx on (board_id, vote_count)
posts_board_created_at_idx on (board_id, created_at)
posts_board_status_idx on (board_id, status_id)
posts_member_created_at_idx on (member_id, created_at)
posts_with_status_idx partial on (status_id, vote_count) WHERE status_id IS NOT NULL
posts_search_vector_idx (GIN) on search_vector
posts_deleted_at_idx on deleted_at
posts_board_deleted_at_idx on (board_id, deleted_at)
posts_moderation_state_idx on moderation_state
posts_pinned_comment_id_idx on pinned_comment_id
posts_canonical_post_id_idx on canonical_post_id

Relations:

Belongs to: board, post_status, member (author), member (owner), post (canonical, for merges)
Has many: votes, comments, post_tags, post_roadmaps, post_notes, post_external_links, merged posts

comments

Comments on feedback posts, supporting nested replies.

Column	Type	Constraints	Description
`id`	TypeID (comment)	PRIMARY KEY	Comment identifier
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Parent post
`parent_id`	TypeID (comment)		Parent comment (for replies)
`member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE RESTRICT	Author member
`content`	text	NOT NULL	Comment text
`is_team_member`	boolean	NOT NULL, DEFAULT false	Team member flag
`is_private`	boolean	NOT NULL, DEFAULT false	Private internal note (visible only to team)
`status_change_from_id`	TypeID (status)	FK -> post_statuses.id ON DELETE SET NULL	Previous status (for status changes recorded with comments)
`status_change_to_id`	TypeID (status)	FK -> post_statuses.id ON DELETE SET NULL	New status (for status changes recorded with comments)
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`deleted_at`	timestamptz		Soft delete timestamp

Indexes:

comments_post_id_idx on post_id
comments_parent_id_idx on parent_id
comments_member_id_idx on member_id
comments_created_at_idx on created_at
comments_post_created_at_idx on (post_id, created_at)

Relations:

Belongs to: post, member (author), comment (parent), post_status (statusChangeFrom), post_status (statusChangeTo)
Has many: comments (replies), comment_reactions

votes

User votes on posts. Each member can vote once per post.

Column	Type	Constraints	Description
`id`	TypeID (vote)	PRIMARY KEY	Vote identifier
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Voted post
`member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE CASCADE	Voting member
`source_type`	text		Integration source that created the vote
`source_external_url`	text		URL to the source ticket/conversation
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Vote timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Indexes:

votes_post_id_idx on post_id
votes_member_post_idx (unique) on (post_id, member_id)
votes_member_id_idx on member_id
votes_member_created_at_idx on (member_id, created_at)

Relations:

Belongs to: post, member

post_tags

Junction table linking posts to tags (many-to-many).

Column	Type	Constraints	Description
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Post reference
`tag_id`	TypeID (tag)	NOT NULL, FK -> tags.id ON DELETE CASCADE	Tag reference

Indexes:

post_tags_pk (unique) on (post_id, tag_id) - composite primary key
post_tags_post_id_idx on post_id
post_tags_tag_id_idx on tag_id

Relations:

Belongs to: post, tag

roadmaps

Public roadmap views for displaying planned work.

Column	Type	Constraints	Description
`id`	TypeID (roadmap)	PRIMARY KEY	Roadmap identifier
`slug`	text	NOT NULL, UNIQUE	URL-safe identifier
`name`	text	NOT NULL	Display name
`description`	text		Roadmap description
`is_public`	boolean	NOT NULL, DEFAULT true	Public visibility
`position`	integer	NOT NULL, DEFAULT 0	Display order
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
`deleted_at`	timestamptz		Soft delete timestamp

Indexes:

Unique constraint on slug
roadmaps_position_idx on position
roadmaps_is_public_idx on is_public
roadmaps_deleted_at_idx on deleted_at

Relations:

Has many: post_roadmaps

post_roadmaps

Junction table linking posts to roadmaps (many-to-many).

Column	Type	Constraints	Description
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Post reference
`roadmap_id`	TypeID (roadmap)	NOT NULL, FK -> roadmaps.id ON DELETE CASCADE	Roadmap reference
`position`	integer	NOT NULL, DEFAULT 0	Position within roadmap

Indexes:

post_roadmaps_pk (unique) on (post_id, roadmap_id) - composite primary key
post_roadmaps_post_id_idx on post_id
post_roadmaps_roadmap_id_idx on roadmap_id
post_roadmaps_position_idx on (roadmap_id, position)

Relations:

Belongs to: post, roadmap

Status & Workflow Tables

post_statuses

Customizable status definitions for tracking post lifecycle.

Column	Type	Constraints	Description
`id`	TypeID (status)	PRIMARY KEY	Status identifier
`name`	text	NOT NULL	Display name
`slug`	text	NOT NULL, UNIQUE	URL-safe identifier
`color`	text	NOT NULL, DEFAULT '#6b7280'	Hex color code
`category`	text	NOT NULL, DEFAULT 'active'	Category: 'active', 'complete', 'closed'
`position`	integer	NOT NULL, DEFAULT 0	Display order within category
`show_on_roadmap`	boolean	NOT NULL, DEFAULT false	Show on public roadmap
`is_default`	boolean	NOT NULL, DEFAULT false	Default for new posts
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`deleted_at`	timestamptz		Soft delete timestamp

Status Categories:

active: Posts currently being worked on (Open, Under Review, Planned, In Progress)
complete: Successfully delivered posts (Complete)
closed: Posts that won't be implemented (Closed)

Indexes:

Unique constraint on slug
post_statuses_position_idx on (category, position)
post_statuses_deleted_at_idx on deleted_at

Relations:

Has many: posts

Default Statuses:

Name	Slug	Color	Category	Show on Roadmap
Open	open	#3b82f6	active	No
Under Review	under_review	#eab308	active	No
Planned	planned	#a855f7	active	Yes
In Progress	in_progress	#f97316	active	Yes
Complete	complete	#22c55e	complete	Yes
Closed	closed	#6b7280	closed	No

History & Notes Tables

post_edit_history

Audit trail for post edits.

Column	Type	Constraints	Description
`id`	TypeID (post_edit)	PRIMARY KEY	Edit record identifier
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Edited post
`editor_member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE SET NULL	Editor
`previous_title`	text	NOT NULL	Title before edit
`previous_content`	text	NOT NULL	Content before edit
`previous_content_json`	jsonb		Rich content before edit
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Edit timestamp

Indexes:

post_edit_history_post_id_idx on post_id
post_edit_history_created_at_idx on created_at

Relations:

Belongs to: post, member (editor)

comment_edit_history

Audit trail for comment edits.

Column	Type	Constraints	Description
`id`	TypeID (comment_edit)	PRIMARY KEY	Edit record identifier
`comment_id`	TypeID (comment)	NOT NULL, FK -> comments.id ON DELETE CASCADE	Edited comment
`editor_member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE SET NULL	Editor
`previous_content`	text	NOT NULL	Content before edit
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Edit timestamp

Indexes:

comment_edit_history_comment_id_idx on comment_id
comment_edit_history_created_at_idx on created_at

Relations:

Belongs to: comment, member (editor)

post_notes

Internal staff notes on posts (not visible to public users).

Column	Type	Constraints	Description
`id`	TypeID (note)	PRIMARY KEY	Note identifier
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Associated post
`member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE RESTRICT	Note author
`content`	text	NOT NULL	Note content
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp

Indexes:

post_notes_post_id_idx on post_id
post_notes_member_id_idx on member_id
post_notes_created_at_idx on created_at

Relations:

Belongs to: post, member (author)

comment_reactions

Emoji reactions on comments.

Column	Type	Constraints	Description
`id`	TypeID (reaction)	PRIMARY KEY	Reaction identifier
`comment_id`	TypeID (comment)	NOT NULL, FK -> comments.id ON DELETE CASCADE	Reacted comment
`member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE CASCADE	Reacting member
`emoji`	text	NOT NULL	Emoji character
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Reaction timestamp

Supported Emojis:

Thumbs up, Heart, Party, Smile, Thinking, Eyes

Indexes:

comment_reactions_comment_id_idx on comment_id
comment_reactions_member_id_idx on member_id
comment_reactions_unique_idx (unique) on (comment_id, member_id, emoji)

Relations:

Belongs to: comment, member

Integration Tables

integrations

Third-party integration configurations (Slack, Discord, etc.).

Column	Type	Constraints	Description
`id`	TypeID (integration)	PRIMARY KEY	Integration identifier
`integration_type`	varchar(50)	NOT NULL, UNIQUE	Type: slack, discord, linear, etc.
`status`	varchar(20)	NOT NULL, DEFAULT 'pending'	Status: pending, active, error
`secrets`	text		Encrypted secrets blob (AES-256-GCM JSON)
`config`	jsonb	NOT NULL, DEFAULT {}	Integration-specific config
`connected_by_member_id`	TypeID (member)	FK -> member.id	Who connected
`connected_at`	timestamptz		Connection timestamp
`last_sync_at`	timestamptz		Last sync timestamp
`last_error`	text		Last error message
`last_error_at`	timestamptz		Last error timestamp
`error_count`	integer	NOT NULL, DEFAULT 0, CHECK >= 0	Consecutive error count
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Indexes:

integration_type_unique (unique) on integration_type
idx_integrations_type_status on (integration_type, status)

Relations:

Belongs to: member (connected_by)
Has many: integration_event_mappings, post_external_links

integration_event_mappings

Event-to-action mappings for integrations.

Column	Type	Constraints	Description
`id`	TypeID (event_mapping)	PRIMARY KEY	Mapping identifier
`integration_id`	TypeID (integration)	NOT NULL, FK -> integrations.id ON DELETE CASCADE	Parent integration
`event_type`	varchar(100)	NOT NULL	Event: post.created, status.changed, etc.
`action_type`	varchar(50)	NOT NULL	Action: send_message, create_issue, etc.
`action_config`	jsonb	NOT NULL, DEFAULT {}	Action configuration
`filters`	jsonb		Event filters
`enabled`	boolean	NOT NULL, DEFAULT true	Mapping enabled
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Action Config Schema:

{
  "templateId": "tpl_xxx",
  "message": "New feedback: {{title}}"
}

Filters Schema:

{
  "boardIds": ["board_xxx", "board_yyy"],
  "statusIds": ["status_xxx"]
}

Indexes:

mapping_unique (unique) on (integration_id, event_type, action_type)
idx_event_mappings_lookup on (integration_id, event_type, enabled)

Relations:

Belongs to: integration

integration_platform_credentials

Platform-level OAuth app credentials for integrations. One row per provider. Secrets are AES-256-GCM encrypted.

Column	Type	Constraints	Description
`id`	TypeID (platform_cred)	PRIMARY KEY	Credential identifier
`integration_type`	varchar(50)	NOT NULL, UNIQUE	Provider type: slack, teams, linear, etc.
`secrets`	text	NOT NULL	Encrypted platform credentials (AES-256-GCM)
`configured_by_member_id`	TypeID (member)	FK -> member.id ON DELETE SET NULL	Who configured
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Indexes:

platform_cred_type_unique (unique) on integration_type

Relations:

Belongs to: member (configured_by)

post_external_links

External links between posts and external platform issues (e.g., Linear issues, GitHub issues).

Column	Type	Constraints	Description
`id`	TypeID (linked_entity)	PRIMARY KEY	Link identifier
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Associated post
`integration_id`	TypeID (integration)	NOT NULL, FK -> integrations.id ON DELETE CASCADE	Associated integration
`integration_type`	varchar(50)	NOT NULL	Integration type
`external_id`	text	NOT NULL	External issue/item ID
`external_url`	text		URL to external item
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp

Indexes:

post_external_links_type_external_id (unique) on (integration_type, external_id)
post_external_links_post_id_idx on post_id
post_external_links_type_external_id_idx on (integration_type, external_id)

Relations:

Belongs to: post, integration

Notification Tables

in_app_notifications

In-app notifications displayed in the UI.

Column	Type	Constraints	Description
`id`	TypeID (notification)	PRIMARY KEY	Notification identifier
`member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE CASCADE	Recipient member
`type`	varchar(50)	NOT NULL	Type: post_status_changed, comment_created
`title`	varchar(255)	NOT NULL	Notification title
`body`	text		Notification body
`post_id`	TypeID (post)	FK -> posts.id ON DELETE CASCADE	Related post
`comment_id`	TypeID (comment)	FK -> comments.id ON DELETE CASCADE	Related comment
`metadata`	jsonb		Additional data
`read_at`	timestamptz		When marked as read
`archived_at`	timestamptz		When archived
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp

Indexes:

in_app_notifications_member_created_idx on (member_id, created_at)
in_app_notifications_member_unread_idx partial on member_id WHERE read_at IS NULL AND archived_at IS NULL
in_app_notifications_post_idx on post_id

Relations:

Belongs to: member, post, comment

post_subscriptions

Tracks which members are subscribed to which posts for notifications.

Column	Type	Constraints	Description
`id`	TypeID (post_sub)	PRIMARY KEY	Subscription identifier
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Subscribed post
`member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE CASCADE	Subscribed member
`reason`	varchar(20)	NOT NULL	Reason: author, vote, comment, manual
`notify_comments`	boolean	NOT NULL, DEFAULT true	Receive comment notifications
`notify_status_changes`	boolean	NOT NULL, DEFAULT true	Receive status change notifications
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Notification Levels:

All activity: notify_comments=true, notify_status_changes=true
Status changes only: notify_comments=false, notify_status_changes=true
Unsubscribed: Row deleted

Indexes:

post_subscriptions_unique (unique) on (post_id, member_id)
post_subscriptions_member_idx on member_id
post_subscriptions_post_idx on post_id
post_subscriptions_post_comments_idx partial on post_id WHERE notify_comments = true
post_subscriptions_post_status_idx partial on post_id WHERE notify_status_changes = true

Relations:

Belongs to: post, member

notification_preferences

Per-member email notification settings.

Column	Type	Constraints	Description
`id`	TypeID (notif_pref)	PRIMARY KEY	Preference identifier
`member_id`	TypeID (member)	NOT NULL, UNIQUE, FK -> member.id ON DELETE CASCADE	Member
`email_status_change`	boolean	NOT NULL, DEFAULT true	Email on status changes
`email_new_comment`	boolean	NOT NULL, DEFAULT true	Email on new comments
`email_muted`	boolean	NOT NULL, DEFAULT false	Mute all emails
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Indexes:

Unique constraint on member_id

Relations:

Belongs to: member

unsubscribe_tokens

One-time tokens for email unsubscribe links.

Column	Type	Constraints	Description
`id`	TypeID (unsub_token)	PRIMARY KEY	Token identifier
`token`	text	NOT NULL, UNIQUE	Token value
`member_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE CASCADE	Member
`post_id`	TypeID (post)	FK -> posts.id ON DELETE CASCADE	Related post (null = global)
`action`	varchar(30)	NOT NULL	Action: unsubscribe_post, unsubscribe_all, mute_post
`expires_at`	timestamptz	NOT NULL	Expiration time
`used_at`	timestamptz		When token was used
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp

Indexes:

Unique constraint on token
unsubscribe_tokens_member_idx on member_id

Relations:

Belongs to: member, post

Changelog Tables

changelog_entries

Public changelog/release notes entries.

Column	Type	Constraints	Description
`id`	TypeID (changelog)	PRIMARY KEY	Entry identifier
`title`	text	NOT NULL	Entry title
`content`	text	NOT NULL	Plain text content
`content_json`	jsonb		Rich content (TipTap JSON)
`member_id`	TypeID (member)	FK -> member.id ON DELETE SET NULL	Author member
`published_at`	timestamptz		Publication timestamp
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
`deleted_at`	timestamptz		Soft delete timestamp

Indexes:

changelog_published_at_idx on published_at
changelog_member_id_idx on member_id
changelog_deleted_at_idx on deleted_at

Relations:

Belongs to: member (author)
Has many: changelog_entry_posts

changelog_entry_posts

Junction table linking changelog entries to shipped posts (many-to-many).

Column	Type	Constraints	Description
`changelog_entry_id`	TypeID (changelog)	NOT NULL, FK -> changelog_entries.id ON DELETE CASCADE	Changelog entry
`post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Linked post
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp

Indexes:

changelog_entry_posts_pk (unique) on (changelog_entry_id, post_id) - composite primary key
changelog_entry_posts_changelog_id_idx on changelog_entry_id
changelog_entry_posts_post_id_idx on post_id

Relations:

Belongs to: changelog_entry, post

AI Feature Tables

post_sentiment

AI-generated sentiment analysis results for posts (one-to-one with posts).

Column	Type	Constraints	Description
`id`	TypeID (sentiment)	PRIMARY KEY	Sentiment record identifier
`post_id`	TypeID (post)	NOT NULL, UNIQUE, FK -> posts.id ON DELETE CASCADE	Analyzed post
`sentiment`	text	NOT NULL	Result: positive, neutral, negative
`confidence`	real	NOT NULL	Confidence score (0-1)
`model`	text	NOT NULL	AI model used
`processed_at`	timestamptz	NOT NULL, DEFAULT now()	Analysis timestamp
`input_tokens`	integer		Input token count
`output_tokens`	integer		Output token count

Indexes:

post_sentiment_processed_at_idx on processed_at
post_sentiment_sentiment_idx on sentiment

Relations:

Belongs to: post

API Tables

api_keys

API keys for public REST API authentication.

Column	Type	Constraints	Description
`id`	TypeID (api_key)	PRIMARY KEY	API key identifier
`name`	varchar(255)	NOT NULL	Human-readable key name
`key_hash`	varchar(64)	NOT NULL, UNIQUE	SHA-256 hash of the API key
`key_prefix`	varchar(12)	NOT NULL	First 12 chars for identification
`created_by_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE CASCADE	Member who created the key
`last_used_at`	timestamptz		Last authentication timestamp
`expires_at`	timestamptz		Optional expiration date
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`revoked_at`	timestamptz		Soft delete / revocation timestamp

Indexes:

Unique constraint on key_hash
api_keys_created_by_id_idx on created_by_id
api_keys_revoked_at_idx on revoked_at

Relations:

Belongs to: member (created_by)

webhooks

Webhook configurations for external event notifications.

Column	Type	Constraints	Description
`id`	TypeID (webhook)	PRIMARY KEY	Webhook identifier
`created_by_id`	TypeID (member)	NOT NULL, FK -> member.id ON DELETE CASCADE	Member who created the webhook
`url`	text	NOT NULL	HTTPS endpoint URL
`secret`	text	NOT NULL	Encrypted HMAC-SHA256 signing secret
`events`	text[]	NOT NULL	Event types to trigger
`board_ids`	text[]		Optional board filter
`status`	text	NOT NULL, DEFAULT 'active'	Status: active, disabled
`failure_count`	integer	NOT NULL, DEFAULT 0	Consecutive delivery failures
`last_error`	text		Last error message
`last_triggered_at`	timestamptz		Last trigger timestamp
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
`deleted_at`	timestamptz		Soft delete timestamp

Indexes:

webhooks_status_idx on status
webhooks_created_by_id_idx on created_by_id
webhooks_deleted_at_idx on deleted_at

Relations:

Belongs to: member (created_by)

merge_suggestions

AI-generated merge suggestions for duplicate posts.

Column	Type	Constraints	Description
`id`	TypeID (merge_sug)	PRIMARY KEY	Suggestion identifier
`source_post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Post suggested as duplicate
`target_post_id`	TypeID (post)	NOT NULL, FK -> posts.id ON DELETE CASCADE	Canonical post to merge into
`status`	text	NOT NULL, DEFAULT 'pending'	Status: pending, accepted, dismissed
`vector_score`	real		Vector similarity score
`fts_score`	real		Full-text search score
`hybrid_score`	real		Combined hybrid score
`llm_confidence`	real		LLM verification confidence
`llm_reasoning`	text		LLM explanation for the match
`llm_model`	text		LLM model used for verification
`resolved_at`	timestamptz		When accepted or dismissed
`resolved_by_principal_id`	TypeID (member)	FK -> member.id ON DELETE SET NULL	Who resolved
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Relations:

Belongs to: post (source), post (target), member (resolved_by)

Feedback Pipeline Tables

feedback_sources

External feedback source configurations for the ingest pipeline.

Column	Type	Constraints	Description
`id`	TypeID (fb_source)	PRIMARY KEY	Source identifier
`source_type`	text	NOT NULL	Source type (e.g. integration name)
`delivery_mode`	text	NOT NULL	How feedback is delivered
`name`	text	NOT NULL	Display name
`integration_id`	TypeID (integration)	FK -> integrations.id	Linked integration
`enabled`	boolean	NOT NULL, DEFAULT true	Active flag
`config`	jsonb	NOT NULL, DEFAULT {}	Source configuration
`secrets`	text		Encrypted secrets
`cursor`	text		Pagination cursor for polling
`last_synced_at`	timestamptz		Last sync timestamp
`last_success_at`	timestamptz		Last successful sync
`last_error`	text		Most recent error
`error_count`	integer	NOT NULL, DEFAULT 0	Consecutive errors
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

raw_feedback_items

Unprocessed feedback items from external sources, queued for AI extraction.

Column	Type	Constraints	Description
`id`	TypeID (raw_fb)	PRIMARY KEY	Item identifier
`source_id`	TypeID (fb_source)	NOT NULL, FK -> feedback_sources.id	Parent source
`source_type`	text	NOT NULL	Source type
`external_id`	text		External item ID
`dedupe_key`	text		Deduplication key
`external_url`	text		Link back to source
`source_created_at`	timestamptz		When created in source system
`author`	jsonb		Author information
`content`	text	NOT NULL	Raw feedback content
`context_envelope`	jsonb		Additional context metadata
`processing_state`	text	NOT NULL, DEFAULT 'pending'	State: pending, processing, done, failed
`attempt_count`	integer	NOT NULL, DEFAULT 0	Processing attempts
`last_error`	text		Last processing error
`processed_at`	timestamptz		When processing completed
`principal_id`	TypeID (member)		Matched user principal
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

feedback_signals

AI-extracted signals from raw feedback items.

feedback_suggestions

Suggestions generated from AI analysis of feedback signals (e.g. create a new post, merge with existing).

feedback_signal_corrections

User corrections and feedback on AI signal extraction accuracy.

external_user_mappings

Maps external user identifiers to internal Quackback principals for cross-system identity resolution.

Segment Tables

segments

User segments for filtering and analytics. Supports manual and dynamic (rule-based) membership.

Column	Type	Constraints	Description
`id`	TypeID (segment)	PRIMARY KEY	Segment identifier
`name`	text	NOT NULL	Display name
`description`	text		Segment description
`type`	text	NOT NULL	Type: manual, dynamic
`color`	text		Display color
`rules`	jsonb		Dynamic segment rules
`evaluation_schedule`	text		Cron schedule for re-evaluation
`weight_config`	jsonb		Weighting configuration
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
`deleted_at`	timestamptz		Soft delete timestamp

user_segments

Junction table for user-to-segment membership.

user_attribute_definitions

Admin-defined custom user attributes for enrichment.

Column	Type	Constraints	Description
`id`	TypeID (user_attr)	PRIMARY KEY	Attribute identifier
`key`	text	NOT NULL, UNIQUE	Attribute key
`label`	text	NOT NULL	Display label
`description`	text		Attribute description
`type`	text	NOT NULL	Data type: text, number, boolean, date, currency
`currency_code`	text		Currency code (for currency type)
`external_key`	text		Mapping key for integration sync
`created_at`	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
`updated_at`	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

{type}_{uuid_base32}

Examples:

post_01h455vb4pex5vsknk084sn02q
board_01h455vb4pex5vsknk084sn03r
member_01h455vb4pex5vsknk084sn04s

Type prefixes used:

user, session, account, member, invite, workspace, verification, domain, transfer_token
board, post, comment, vote, tag, status, roadmap
integration, platform_cred, event_mapping, linked_entity, sync_log
notification, post_sub, notif_pref, unsub_token
changelog, sentiment, note, reaction
post_edit, comment_edit
api_key, webhook
subscription, invoice (billing)

Schema Files

The schema is defined in Drizzle ORM format in these files:

File	Tables
`packages/db/src/schema/auth.ts`	user, session, account, verification, one_time_token, settings, member, invitation, jwks, oauth_client, oauth_access_token, oauth_refresh_token, oauth_consent
`packages/db/src/schema/boards.ts`	boards, roadmaps, tags
`packages/db/src/schema/posts.ts`	posts, post_tags, post_roadmaps, votes, comments, comment_reactions, post_edit_history, comment_edit_history, post_notes
`packages/db/src/schema/statuses.ts`	post_statuses
`packages/db/src/schema/integrations.ts`	integrations, integration_platform_credentials, integration_event_mappings
`packages/db/src/schema/notifications.ts`	in_app_notifications, post_subscriptions, notification_preferences, unsubscribe_tokens
`packages/db/src/schema/changelog.ts`	changelog_entries, changelog_entry_posts
`packages/db/src/schema/sentiment.ts`	post_sentiment
`packages/db/src/schema/merge-suggestions.ts`	merge_suggestions
`packages/db/src/schema/feedback.ts`	feedback_sources, raw_feedback_items, feedback_signals, feedback_suggestions, feedback_signal_corrections, external_user_mappings
`packages/db/src/schema/segments.ts`	segments, user_segments
`packages/db/src/schema/user-attributes.ts`	user_attribute_definitions
`packages/db/src/schema/api-keys.ts`	api_keys
`packages/db/src/schema/webhooks.ts`	webhooks
`packages/db/src/schema/external-links.ts`	post_external_links

Migrations are located in packages/db/drizzle/.