A guide to the tables you're most likely to query when writing SQL reports against a self-hosted Quackback database. It lists key columns only. The source of truth is the Drizzle schema in packages/db/src/schema/ in the Quackback repository.
Read from the database, but make changes through the app, the REST API, or MCP. Writing to tables directly can skip validation, events, webhooks, and search indexing.
IDs: primary and foreign keys are stored as PostgreSQL uuid. The app and API show them as TypeIDs with a type prefix, such as post_01h455vb4pex5vsknk084sn02q. The prefix is not stored.
Timestamps: every timestamp column is timestamp with time zone.
Soft deletes: most content tables have a deleted_at column. Filter on deleted_at IS NULL to match what the app shows.
Search: posts, conversation messages, and help center articles have a generated search_vector column for full-text search. Posts, changelog entries, and articles also store an embedding (pgvector) when AI is configured.
People are principals: authors, voters, agents, and customers are all referenced by principal_id, not user_id. Join principal to user for names and emails.
Roadmaps are now built from post statuses, and the post_roadmaps table is gone. On installs upgraded from 0.13, the old curated placements are kept in post_roadmaps_archived for reference until a later release. Nothing reads or writes it.
The identity that owns content and activity. A principal usually points to a user, but service principals (API keys, integrations) don't.
Column
Description
id
Principal ID
user_id
The linked user, if any
type
user, anonymous, service, or support
role
Access tier: admin or member for team members, user for portal users. Fine-grained roles are in principal_role_assignments.
display_name
Name shown for service principals
company_id
The customer's companies row, if any
blocked_at
When the principal was blocked
created_at
Creation time
To list your team with their roles:
SELECT u.name, u.email, r.name AS roleFROM principal pJOIN "user" u ON u.id = p.user_idJOIN principal_role_assignments pra ON pra.principal_id = p.idJOIN roles r ON r.id = pra.role_idWHERE p.type = 'user' AND p.role IN ('admin', 'member');
Role-based access control. roles holds the built-in roles (Owner, Admin, Manager, Contributor) and any custom roles. role_permissions maps roles to permissions. principal_role_assignments gives a principal a role, optionally scoped to a team.
post_tags is the tag catalogue. post_tag_assignments links tags to posts.
Table
Key columns
post_tags
id, name, color, is_public, deleted_at
post_tag_assignments
post_id, tag_id, auto_tagged
SELECT t.name, count(*) AS postsFROM post_tag_assignments aJOIN post_tags t ON t.id = a.tag_idJOIN posts p ON p.id = a.post_idWHERE p.deleted_at IS NULL AND t.deleted_at IS NULLGROUP BY t.nameORDER BY posts DESC;
Roadmaps are views over posts. A post appears on a roadmap because of its status (column roadmaps) or its eta (date roadmaps), not because it was placed there.
Table
Key columns
roadmaps
id, slug, name, type (column or date), base_filter (JSON), date_source, frequency, visibility (public, team, or segment), visible_segment_ids, deleted_at
conversation_tags is the tag catalogue (id, name, color, deleted_at). conversation_tag_assignments links tags to conversations (conversation_id, conversation_tag_id). conversation_participants lists extra teammates on a conversation.
Background work runs on PostgreSQL. There is no separate queue service.
Table
Key columns
events
id, type (such as post.created), entity_type, entity_id, actor_type, actor_id, payload, occurred_at, published_at
job_queue
id, queue, status (pending, running, succeeded, or failed), run_at, attempts, max_attempts, last_error, created_at, finished_at
Domain events are written to events in the same transaction as the change that raised them, then fanned out to webhooks, notifications, and workflows through job_queue. Finished jobs are deleted after the retention period (JOB_RETENTION_MS, 7 days by default).
To see recent failed jobs:
SELECT queue, last_error, attempts, finished_atFROM job_queueWHERE status = 'failed'ORDER BY finished_at DESCLIMIT 20;
A single row holding workspace configuration: name, slug, logos, and JSON (stored as text) for authentication, portal, branding, widget, help center, and feature flags. Change these through Admin → Settings or the config file, not with SQL.
