Database Schema

This document describes the PostgreSQL database schema used by the Signal Bot.

Quick Start (Fresh Installation)

For a new installation, PostgreSQL needs the base schema before the bot can run:

# 1. Start the postgres container
docker compose up -d postgres

# 2. Apply the initial schema (creates all tables)
docker exec -i signal-bot-postgres psql -U signal_bot signal_bot < database/000_init_schema.sql

# 3. Start the bot
docker compose up -d signal-bot

That’s it! The 000_init_schema.sql file contains everything needed for a fresh installation.

Schema Files

The database/ directory contains:

File	Description
000_init_schema.sql	Clean initial schema - Use this for fresh installations. Contains all tables, indexes, constraints with no data.
000_base_schema.sql	Production schema dump (for reference/comparison)
001_*.sql - 054_*.sql	Incremental migrations - Schema changes added over time. Only needed if upgrading from an older version.

Important Notes:

• For fresh installs, just use 000_init_schema.sql
• Migrations (001+) are incremental and use IF NOT EXISTS / ADD COLUMN IF NOT EXISTS
• On an existing install, apply only the migrations you haven’t run yet

---

Core Tables (~50 total)

Signal Core

Table	Purpose
signal_groups	Registered Signal groups with settings (AI enabled, meme enabled, etc.)
signal_members	User profiles (UUID, phone, display name, profile name)
signal_member_group_memberships	Which members are in which groups
signal_messages	Message history for context and rollups

Q&A System

Table	Purpose
q_and_a_questions	Questions asked via !q or !ask
q_and_a_answers	Answers to questions via !a
q_and_a_clarifications	Follow-up clarifications
q_and_a_inquiries	!inquiry prompts for elaboration

Verification System

Table	Purpose
verification_requests	User verification workflow (INDOC, safety number changes)
user_removals	Audit log for user removals

Breakout Rooms

Table	Purpose
breakout_rooms	Temporary discussion groups
breakout_room_members	Participants in breakout rooms
breakout_room_messages	Messages within breakout sessions
breakout_annotations	Tasks/notes from breakout discussions

Identity Tracking

Table	Purpose
known_identity_fingerprints	Stores signal-cli identity fingerprints for safety number change detection (polled every 5 min)
identity_change_log	Audit trail of detected identity/fingerprint changes with action taken

Content Tracking

Table	Purpose
news_links	Shared news URLs
repository_links	Shared git repos
social_media_links	Shared social media content
memes	Stored meme library
archived_files	File archive metadata
archived_videos	Video metadata
youtube_transcripts	Cached video transcripts (YouTube, Instagram, TikTok, etc.)

Entity Analysis

Table	Purpose
entity_aliases	Maps alternate entity names to canonical form (e.g., “USA” → “United States”)

Note: Entity data (people, organizations, locations) is extracted from news_links and social_media_links via JSONB entities column. The controversial_claims JSONB column tracks disputed claims for cross-article analysis.

RSS & Bookmarks

Table	Purpose
rss_feeds	Registered RSS feed sources
rss_bookmarks	User-bookmarked RSS articles
rss_admin_settings	RSS reader admin configuration

User Profiles & Admin

Table	Purpose
user_profiles	Extended user profile data (backfilled from signal_members)
bot_admins	Bot admin roles and permissions (DB-managed admin access)
portal_announcements	Community portal announcements

Message Tracking

Table	Purpose
missed_messages	Protocol exceptions and quote-detected message gaps
dm_link_tracking	DM link submission tracking
youtube_transcripts	Cached video transcripts (YouTube, Instagram, TikTok, etc.)

Scheduling

Table	Purpose
scheduled_announcements	Pending announcements
reminders	User reminders via !remindme
scheduled_rollups	Weekly/daily digest schedules

Analytics & Logging

Table	Purpose
bot_command_usage	Track command usage statistics
bot_errors	Error logging for debugging
debug_logs	Optional debug logging
today_i_learned	TIL entries
tasks	Task tracking

---

Environment Variables

The bot connects to PostgreSQL using these env vars:

DB_HOST=signal-bot-postgres   # Use full container name to avoid DNS conflicts
DB_PORT=5432
DB_NAME=signal_bot
DB_USER=signal_bot
DB_PASSWORD=<your-password>

Important: Use DB_HOST=signal-bot-postgres (full container name) instead of just postgres to avoid DNS resolution conflicts with other postgres containers on shared networks.

---

Adding New Tables

1. Create a new migration file: database/NNN_description.sql
2. Use CREATE TABLE IF NOT EXISTS and ADD COLUMN IF NOT EXISTS for safety
3. Add the table to ALLOWED_TABLES in src/src/db/postgres-client.ts
4. Apply to production:

   docker exec -i signal-bot-postgres psql -U signal_bot signal_bot < database/NNN_description.sql

Migration Best Practices

-- Always use IF NOT EXISTS for safety (idempotent migrations)
CREATE TABLE IF NOT EXISTS my_new_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Adding columns safely
ALTER TABLE existing_table ADD COLUMN IF NOT EXISTS new_column TEXT;

-- Creating indexes
CREATE INDEX IF NOT EXISTS idx_my_table_name ON my_new_table(name);

---

Extracting Schema from Production

To update 000_base_schema.sql from production:

ssh proxmox "docker exec signal-bot-postgres pg_dump -U signal_bot -s signal_bot" > database/000_base_schema.sql

---

Troubleshooting

”relation does not exist” errors

The base schema hasn’t been applied. Run:

docker exec -i signal-bot-postgres psql -U signal_bot signal_bot < database/000_base_schema.sql

Migration fails with “column already exists”

This is fine - migrations use IF NOT EXISTS so they’re idempotent. The column was already added.

Bot can’t connect to database

1. Check DB_HOST uses full container name (signal-bot-postgres)
2. Verify postgres container is healthy: docker compose ps
3. Check credentials match what’s in the postgres container env

Schema drift between environments

Extract fresh schema from production and compare:

ssh proxmox "docker exec signal-bot-postgres pg_dump -U signal_bot -s signal_bot" > /tmp/prod_schema.sql
diff database/000_base_schema.sql /tmp/prod_schema.sql

View table structure

# Connect to database
docker exec -it signal-bot-postgres psql -U signal_bot signal_bot

# List all tables
\dt

# Describe a specific table
\d signal_groups

# Show table with indexes
\d+ signal_messages

Query examples

-- Count messages by group
SELECT g.name, COUNT(m.id) as message_count
FROM signal_groups g
LEFT JOIN signal_messages m ON g.id = m.group_id
GROUP BY g.name
ORDER BY message_count DESC;

-- Find recent questions
SELECT hash, question_text, created_at
FROM q_and_a_questions
ORDER BY created_at DESC
LIMIT 10;

-- Check pending reminders
SELECT id, user_name, message, remind_at
FROM reminders
WHERE delivered = false
ORDER BY remind_at;

---

Backup and Recovery

Create a backup

# Full backup
ssh proxmox "docker exec signal-bot-postgres pg_dump -U signal_bot signal_bot > /home/signal-bot-selfhosted/data/backups/backup_$(date +%Y%m%d_%H%M%S).sql"

# Compressed backup
ssh proxmox "docker exec signal-bot-postgres pg_dump -U signal_bot signal_bot | gzip > /home/signal-bot-selfhosted/data/backups/backup_$(date +%Y%m%d).sql.gz"

Restore from backup

# Stop the bot first
docker compose stop signal-bot

# Restore
docker exec -i signal-bot-postgres psql -U signal_bot signal_bot < backup.sql

# Start the bot
docker compose start signal-bot

---

Related Documentation

• Troubleshooting - Common issues and solutions
• Deployment Guide - Full deployment instructions
• Self-Hosting Guide - Quick start for self-hosting