# 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 |