Buffer Analytics SQLite Schema Reference #
The Buffer Analytics database is created and maintained automatically by scripts/buffer_analytics.py. Database tables, indexes, and analytical views are provisioned during sync operations.
1. Relational Tables #
channels #
Stores connected social media channel profiles and account configuration.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
TEXT |
PRIMARY KEY | Unique Buffer channel ID (e.g. 1234567890abcdef12345678) |
organization_id |
TEXT |
Buffer organization ID | |
name |
TEXT |
Channel handle / username | |
service |
TEXT |
Social network (linkedin, twitter, bluesky, instagram, facebook) |
|
display_name |
TEXT |
Formatted account display name | |
timezone |
TEXT |
Account timezone string (e.g. UTC, America/New_York) |
|
is_disconnected |
INTEGER |
DEFAULT 0 | 1 if account is disconnected |
is_locked |
INTEGER |
DEFAULT 0 | 1 if account is locked by plan limits |
is_queue_paused |
INTEGER |
DEFAULT 0 | 1 if publishing queue is paused |
avatar_url |
TEXT |
URL of profile picture | |
external_link |
TEXT |
URL of the social media profile | |
raw_json |
TEXT |
NOT NULL | Complete raw JSON payload from Buffer API |
created_at |
TEXT |
ISO-8601 creation timestamp | |
updated_at |
TEXT |
ISO-8601 modification timestamp | |
synced_at |
TEXT |
NOT NULL | Timestamp of local database sync |
posts #
Stores individual social media posts, scheduling lifecycle status, and text content.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
TEXT |
PRIMARY KEY | Unique Buffer post ID |
organization_id |
TEXT |
Buffer organization ID | |
channel_id |
TEXT |
NOT NULL, FK -> channels(id) |
Associated social channel ID |
channel_service |
TEXT |
Service name (linkedin, twitter, bluesky) |
|
status |
TEXT |
NOT NULL | Post status (sent, scheduled, draft, error) |
text |
TEXT |
Full text copy of the post | |
external_link |
TEXT |
URL of the live published post on the network | |
sent_at |
TEXT |
ISO-8601 publication timestamp | |
due_at |
TEXT |
Scheduled publication timestamp | |
created_at |
TEXT |
Creation timestamp | |
updated_at |
TEXT |
Modification timestamp | |
scheduling_type |
TEXT |
Scheduling method (queue, custom_time, share_now) |
|
is_ai_generated |
INTEGER |
DEFAULT 0 | 1 if marked AI generated in Buffer |
thread_count |
INTEGER |
DEFAULT 0 | Number of posts in thread |
first_comment |
TEXT |
Scheduled first comment text | |
char_count |
INTEGER |
Total character count of post body | |
word_count |
INTEGER |
Total word count of post body | |
has_link |
INTEGER |
DEFAULT 0 | 1 if post contains URLs or external links |
has_media |
INTEGER |
DEFAULT 0 | 1 if post contains image or video attachments |
raw_json |
TEXT |
NOT NULL | Complete raw JSON payload from Buffer API |
synced_at |
TEXT |
NOT NULL | Timestamp of local database sync |
post_metrics #
Stores normalized engagement metrics recorded per post.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
INTEGER |
PRIMARY KEY AUTOINCREMENT | Unique metric record ID |
post_id |
TEXT |
NOT NULL, FK -> posts(id) |
Associated post ID |
channel_id |
TEXT |
Associated channel ID | |
channel_service |
TEXT |
Service name (linkedin, twitter, bluesky) |
|
metric_type |
TEXT |
NOT NULL | Metric key (impressions, reach, reactions, comments, reposts, clicks, engagementRate) |
name |
TEXT |
Human-readable metric name | |
value |
REAL |
NOT NULL, DEFAULT 0 | Metric numeric value |
unit |
TEXT |
Unit (count, percentage) |
|
description |
TEXT |
Metric description | |
recorded_at |
TEXT |
Timestamp recorded by Buffer | |
synced_at |
TEXT |
NOT NULL | Timestamp of local database sync |
post_assets #
Stores media attachments (images, videos, thumbnails) linked to posts.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
TEXT |
PRIMARY KEY | Asset identifier or URL hash |
post_id |
TEXT |
NOT NULL, FK -> posts(id) |
Associated post ID |
type |
TEXT |
Media type (image, video, gif) |
|
mime_type |
TEXT |
MIME format (e.g. image/png, video/mp4) |
|
source |
TEXT |
Full-resolution asset URL | |
thumbnail |
TEXT |
Thumbnail asset URL | |
raw_json |
TEXT |
Complete asset JSON payload |
post_tags #
Stores organizational tags and campaign labels assigned in Buffer.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
TEXT |
PRIMARY KEY (post_id, id) | Tag identifier |
post_id |
TEXT |
PRIMARY KEY (post_id, id), FK -> posts(id) |
Associated post ID |
name |
TEXT |
Tag name / label | |
color |
TEXT |
Hex color code |
sync_history #
Audit log recording every backfill and incremental sync operation.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
INTEGER |
PRIMARY KEY AUTOINCREMENT | Unique sync run ID |
organization_id |
TEXT |
Buffer organization ID | |
channel_id |
TEXT |
Specific channel ID if scoped | |
sync_mode |
TEXT |
NOT NULL | Sync type (incremental, full) |
start_date |
TEXT |
Starting date of sync window | |
end_date |
TEXT |
Ending date of sync window | |
posts_fetched |
INTEGER |
DEFAULT 0 | Count of records fetched from API |
posts_inserted |
INTEGER |
DEFAULT 0 | Count of newly inserted posts |
posts_updated |
INTEGER |
DEFAULT 0 | Count of existing posts refreshed |
started_at |
TEXT |
NOT NULL | Start timestamp |
finished_at |
TEXT |
Completion timestamp | |
status |
TEXT |
NOT NULL | Status (success, failed, running) |
error_message |
TEXT |
Error details if failed |
2. Analytical SQL Views #
v_posts_summary #
Primary analytical view flattening posts with pivoted metrics, engagement rate calculations, and temporal calendar dimensions.
| Column | Type | Description |
|---|---|---|
post_id |
TEXT |
Unique post ID |
service |
TEXT |
Social network (linkedin, twitter, bluesky) |
channel_name |
TEXT |
Channel username / handle |
status |
TEXT |
Post status (sent, scheduled, draft) |
sent_at |
TEXT |
Full ISO timestamp of publication |
sent_date |
TEXT |
Date string (YYYY-MM-DD) |
year_month |
TEXT |
Year and month string (YYYY-MM) |
day_of_week |
TEXT |
Full day name (Monday, Tuesday, Wednesday, etc.) |
hour_of_day |
INTEGER |
UTC hour of publication (0 to 23) |
char_count |
INTEGER |
Body text character count |
word_count |
INTEGER |
Body text word count |
has_link |
INTEGER |
1 if external links are present in text |
has_media |
INTEGER |
1 if image or video attachments are present |
thread_count |
INTEGER |
Number of posts in thread |
external_link |
TEXT |
Live social network URL |
text |
TEXT |
Full text copy of post |
impressions |
REAL |
Total post views / impressions |
reach |
REAL |
Unique viewers reached |
reactions |
REAL |
Total likes and reactions |
comments |
REAL |
Total comments received |
reposts |
REAL |
Total retweets / reshares |
clicks |
REAL |
Total link clicks |
engagement_rate |
REAL |
Overall engagement percentage |