Skip to content

database-schema.mdx

Latest commit

 

History

History
1212 lines (929 loc) · 46.4 KB

database-schema.mdx

File metadata and controls

1212 lines (929 loc) · 46.4 KB
Raw
title Database Schema
description Tables, columns, and relationships in the Quackback database.
icon database

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

tags

Labels for categorizing posts.

Column Type Constraints Description
id TypeID (tag) PRIMARY KEY Tag identifier
name text NOT NULL, UNIQUE Tag name
color text NOT NULL, DEFAULT '#6b7280' Hex color code
created_at timestamptz NOT NULL, DEFAULT now() Creation timestamp
deleted_at timestamptz Soft delete timestamp

Indexes:

  • Unique constraint on name
  • tags_deleted_at_idx on deleted_at

Relations:

  • Has many: post_tags

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

Database Triggers

comment_count

The comment_count column on posts is maintained in application code (not a database trigger). It's updated whenever comments are created or deleted.

TypeID Format

All entity IDs use the TypeID format, which combines a type prefix with a UUID:

{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/.