slackdump-database

name	slackdump-database
description	Query and manipulate slackdump SQLite archives. Use when searching Slack messages, filtering by date/privacy, generating statistics, or creating smaller database subsets. Requires sqlite3 CLI only.

Slackdump Database Skill

This skill enables querying and manipulating SQLite databases created by slackdump, a tool for archiving Slack workspaces.

Prerequisites

• sqlite3 command-line tool (pre-installed on macOS/Linux)
• A slackdump SQLite database (typically named slackdump.sqlite)

Conceptual Overview

Why This Structure Exists

Slackdump archives Slack data incrementally using a chunk-based architecture. Rather than storing data in normalized tables, it preserves the raw API responses as JSON blobs while extracting key fields for indexing. This design:

1. Supports incremental updates - New data appends as chunks without rewriting existing data
2. Preserves full API fidelity - The DATA blob contains complete Slack API responses
3. Enables resume/recovery - Sessions track progress; incomplete archives can be resumed
4. Handles deduplication - Multiple chunks may contain the same entity; views/queries select the latest

Channel ID Prefixes

Slack uses prefixes to identify channel types:

Prefix	Type	Privacy	Example
C	Public channel	Public	C01234ABCDE
D	Direct message (1:1)	Private	D01234ABCDE
G	Group DM / Private channel	Private	G01234ABCDE

Privacy rule: C channels are public; D and G channels are private.

Chunk Types

Each piece of data is associated with a chunk type (stored in CHUNK.TYPE_ID):

ID	Name	Description
0	MESSAGES	Channel messages
1	THREAD_MESSAGES	Thread replies
2	FILES	File metadata
3	USERS	User profiles
4	CHANNELS	Channel list
5	CHANNEL_INFO	Individual channel metadata
6	WORKSPACE_INFO	Workspace details
7	CHANNEL_USERS	Channel membership lists

Timestamps

Slack timestamps (TS fields) are strings in format "1234567890.123456" (Unix seconds with microseconds after the dot). To convert:

-- String timestamp to datetime
datetime(CAST(TS AS REAL), 'unixepoch')

-- With timezone (localtime)
datetime(CAST(TS AS REAL), 'unixepoch', 'localtime')

-- Filter by date range
CAST(TS AS REAL) BETWEEN strftime('%s', '2024-01-01') AND strftime('%s', '2024-12-31')

Core Tables

MESSAGE

The primary table for Slack messages.

Column	Purpose
ID	Numeric timestamp (for sorting/joins)
CHANNEL_ID	Channel this message belongs to
TS	Slack timestamp string
TXT	Plain text content (extracted for searching)
DATA	Full JSON blob from Slack API
IS_PARENT	True if this is a thread parent
THREAD_TS	Thread timestamp (links replies to parent)
PARENT_ID	Numeric ID of parent message (for thread children)

CHANNEL

Channel metadata.

Column	Purpose
ID	Channel ID (C/D/G prefix)
NAME	Channel name (null for DMs)
DATA	Full JSON with topic, purpose, members, etc.

S_USER

User profiles (S_ prefix = Slack User, avoiding SQL reserved word).

Column	Purpose
ID	User ID (e.g., U01234ABCDE)
USERNAME	Display name
DATA	Full profile JSON (real_name, email, avatar, etc.)

CHUNK

Metadata about each data chunk (for internal tracking).

Column	Purpose
TYPE_ID	Chunk type (see table above)
CHANNEL_ID	Associated channel (if applicable)
SESSION_ID	Which archive session created this
FINAL	True if this chunk completes a channel/thread

Common Queries

Search Messages

-- Simple text search
SELECT
  datetime(CAST(m.TS AS REAL), 'unixepoch', 'localtime') as time,
  c.NAME as channel,
  u.USERNAME as author,
  m.TXT
FROM MESSAGE m
LEFT JOIN CHANNEL c ON m.CHANNEL_ID = c.ID
LEFT JOIN S_USER u ON json_extract(m.DATA, '$.user') = u.ID
WHERE m.TXT LIKE '%search term%'
ORDER BY m.TS DESC
LIMIT 50;

Messages in Date Range

-- Messages from January 2024
SELECT
  datetime(CAST(m.TS AS REAL), 'unixepoch', 'localtime') as time,
  c.NAME as channel,
  m.TXT
FROM MESSAGE m
LEFT JOIN CHANNEL c ON m.CHANNEL_ID = c.ID
WHERE CAST(m.TS AS REAL) BETWEEN strftime('%s', '2024-01-01') AND strftime('%s', '2024-02-01')
ORDER BY m.TS;

Public Channels Only

-- Filter to public channels (C prefix)
SELECT DISTINCT c.NAME, c.ID
FROM CHANNEL c
WHERE c.ID LIKE 'C%'
ORDER BY c.NAME;

-- Messages from public channels only
SELECT m.* FROM MESSAGE m
WHERE m.CHANNEL_ID LIKE 'C%';

Private Channels/DMs Only

-- DMs and private channels (D and G prefixes)
SELECT m.* FROM MESSAGE m
WHERE m.CHANNEL_ID LIKE 'D%' OR m.CHANNEL_ID LIKE 'G%';

Statistics

-- Messages per month
SELECT
  strftime('%Y-%m', datetime(CAST(TS AS REAL), 'unixepoch')) as month,
  COUNT(*) as messages
FROM MESSAGE
GROUP BY month
ORDER BY month;

-- Most active channels
SELECT
  c.NAME,
  COUNT(*) as message_count
FROM MESSAGE m
JOIN CHANNEL c ON m.CHANNEL_ID = c.ID
WHERE c.ID LIKE 'C%'  -- public only
GROUP BY m.CHANNEL_ID
ORDER BY message_count DESC
LIMIT 20;

-- Latest message date
SELECT datetime(MAX(CAST(TS AS REAL)), 'unixepoch', 'localtime') FROM MESSAGE;

-- Total counts
SELECT
  (SELECT COUNT(*) FROM MESSAGE) as messages,
  (SELECT COUNT(DISTINCT CHANNEL_ID) FROM MESSAGE) as channels_with_messages,
  (SELECT COUNT(DISTINCT ID) FROM S_USER) as users;

Thread Context

-- Get a thread with all replies
SELECT
  datetime(CAST(m.TS AS REAL), 'unixepoch', 'localtime') as time,
  u.USERNAME,
  m.TXT
FROM MESSAGE m
LEFT JOIN S_USER u ON json_extract(m.DATA, '$.user') = u.ID
WHERE m.CHANNEL_ID = 'C01234ABCDE'
  AND (m.THREAD_TS = '1234567890.123456' OR m.TS = '1234567890.123456')
ORDER BY m.TS;

User Lookup

-- Find user by name
SELECT ID, USERNAME, json_extract(DATA, '$.real_name') as real_name
FROM S_USER
WHERE USERNAME LIKE '%john%' OR json_extract(DATA, '$.real_name') LIKE '%John%';

-- Get user's profile details
SELECT
  json_extract(DATA, '$.real_name') as name,
  json_extract(DATA, '$.profile.email') as email,
  json_extract(DATA, '$.profile.title') as title,
  json_extract(DATA, '$.deleted') as is_deleted
FROM S_USER
WHERE ID = 'U01234ABCDE';

Creating Smaller Databases

To create filtered copies, use SQLite's ability to attach databases and copy data.

Remove Private Messages (Keep Public Only)

-- Create new database with public data only
-- Run: sqlite3 public_only.sqlite

ATTACH 'original.sqlite' AS src;

-- Copy schema
CREATE TABLE MESSAGE AS SELECT * FROM src.MESSAGE WHERE 0;
CREATE TABLE CHANNEL AS SELECT * FROM src.CHANNEL WHERE 0;
CREATE TABLE S_USER AS SELECT * FROM src.S_USER WHERE 0;
CREATE TABLE CHUNK AS SELECT * FROM src.CHUNK WHERE 0;

-- Copy public messages only
INSERT INTO MESSAGE SELECT * FROM src.MESSAGE WHERE CHANNEL_ID LIKE 'C%';

-- Copy public channels only
INSERT INTO CHANNEL SELECT * FROM src.CHANNEL WHERE ID LIKE 'C%';

-- Copy all users (needed for attribution)
INSERT INTO S_USER SELECT * FROM src.S_USER;

-- Copy relevant chunks
INSERT INTO CHUNK SELECT * FROM src.CHUNK
WHERE CHANNEL_ID LIKE 'C%' OR CHANNEL_ID IS NULL;

DETACH src;
VACUUM;

Date Range Export

-- Export messages from specific date range
-- Run: sqlite3 date_range.sqlite

ATTACH 'original.sqlite' AS src;

CREATE TABLE MESSAGE AS SELECT * FROM src.MESSAGE WHERE 0;
CREATE TABLE CHANNEL AS SELECT * FROM src.CHANNEL;
CREATE TABLE S_USER AS SELECT * FROM src.S_USER;

-- Copy messages in date range (2024 only)
INSERT INTO MESSAGE
SELECT * FROM src.MESSAGE
WHERE CAST(TS AS REAL) BETWEEN strftime('%s', '2024-01-01') AND strftime('%s', '2025-01-01');

DETACH src;
VACUUM;

Combined: Public + Date Range

-- Public messages from 2024 only
ATTACH 'original.sqlite' AS src;

CREATE TABLE MESSAGE AS SELECT * FROM src.MESSAGE WHERE 0;
CREATE TABLE CHANNEL AS SELECT * FROM src.CHANNEL WHERE ID LIKE 'C%';
CREATE TABLE S_USER AS SELECT * FROM src.S_USER;

INSERT INTO MESSAGE
SELECT * FROM src.MESSAGE
WHERE CHANNEL_ID LIKE 'C%'
  AND CAST(TS AS REAL) BETWEEN strftime('%s', '2024-01-01') AND strftime('%s', '2025-01-01');

DETACH src;
VACUUM;

Working with JSON Data

The DATA columns contain full Slack API responses. Use json_extract() to access nested fields:

-- Message reactions
SELECT
  m.TXT,
  json_extract(m.DATA, '$.reactions') as reactions
FROM MESSAGE m
WHERE json_extract(m.DATA, '$.reactions') IS NOT NULL
LIMIT 10;

-- Messages with files
SELECT
  m.TXT,
  json_extract(m.DATA, '$.files[0].name') as filename
FROM MESSAGE m
WHERE json_extract(m.DATA, '$.files') IS NOT NULL;

-- Channel topic and purpose
SELECT
  NAME,
  json_extract(DATA, '$.topic.value') as topic,
  json_extract(DATA, '$.purpose.value') as purpose
FROM CHANNEL
WHERE ID LIKE 'C%';

Slack Message Permalinks

To generate Slack permalinks from the database:

https://{workspace}.slack.com/archives/{channel_id}/p{timestamp_without_dot}

-- Generate permalink
SELECT
  'https://workspace.slack.com/archives/' ||
  CHANNEL_ID || '/p' || REPLACE(TS, '.', '') as permalink,
  TXT
FROM MESSAGE
WHERE TXT LIKE '%important%';

Reference

For complete schema details including all columns, indexes, and views, see references/schema.md.