Schema · Entity relationship diagram
Nectar Social tables and relationships
UGC and brand mentions across Instagram and TikTok (with daily metric snapshots), owned-account daily metrics, and your connected social accounts.
nectarugc_postsholds the current state of every UGC item;ugc_metrics_dailysnapshots each post's cumulative metrics per sync day for day-over-day deltas.account_metrics_dailyis long format with several variants per date and metric (workspace roll-up, attribution, stories). Filter to one variant before summing.
Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.
Table reference
4 tables · click a table for its columnsugc_posts32 columnsshow in diagram
Current state of every UGC item (feed posts, reels, story mentions, tagged media).
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | varchar(64) | PK | Primary key. |
| platform | varchar(32) | ||
| surface | varchar(32) | ||
| format | varchar(32) | ||
| published_at | timestamp | ||
| permalink | varchar(2500) | ||
| caption | varchar(8000) | ||
| sentiment | varchar(16) | ||
| ai_summary | varchar(4000) | ||
| is_owned_content | boolean | ||
| is_untagged_brand_mention | boolean | ||
| is_favorited | boolean | ||
| brand_replied | boolean | ||
| media_count | integer | ||
| media_type | varchar(32) | ||
| media_url | varchar(2500) | ||
| thumbnail_url | varchar(2500) | ||
| likes | bigint | ||
| comments | bigint | ||
| shares | bigint | ||
| views_estimate | bigint | ||
| engagement_rate | decimal(18,6) | ||
| emv | decimal(18,4) | ||
| creator_id | varchar(64) | ||
| creator_handle | varchar(255) | ||
| creator_follower_count | bigint | ||
| creator_is_verified | boolean | ||
| labels | varchar(2000) | ||
| usage_rights_status | varchar(100) | ||
| usage_rights_expires_at | timestamp | ||
| refresh_date | date | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
ugc_metrics_daily12 columnsshow in diagram
Snapshot of each post's cumulative metrics on each sync day (posts inside the trailing refresh window), for day-over-day view/engagement deltas.
Primary key: snapshot_key
| Column | Type | Key | Description |
|---|---|---|---|
| snapshot_keyPrimary key. | varchar(80) | PK | Primary key. |
| idReferences ugc_posts.id. | varchar(64) | FK | References ugc_posts.id. |
| refresh_date | date | ||
| platform | varchar(32) | ||
| published_at | timestamp | ||
| likes | bigint | ||
| comments | bigint | ||
| shares | bigint | ||
| views_estimate | bigint | ||
| engagement_rate | decimal(18,6) | ||
| emv | decimal(18,4) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
account_metrics_daily11 columnsshow in diagram
Owned-account daily metric series, long format. Several variants per date/platform/metric: account_id NULL = workspace roll-up; attribution ORGANIC | PAID | BLENDED (no filter); excludes_stories. Filter to one variant before summing. ENGAGEMENT_RATE_* are not recomputed by the API on excludes_stories rows — derive rates from components there.
Primary key: row_key
| Column | Type | Key | Description |
|---|---|---|---|
| row_keyPrimary key. | varchar(200) | PK | Primary key. |
| metric_date | date | ||
| platform | varchar(32) | ||
| metric | varchar(64) | ||
| value | decimal(24,6) | ||
| refresh_date | date | ||
| account_idReferences accounts.id. | varchar(64) | FK | References accounts.id. |
| account_handle | varchar(255) | ||
| attribution | varchar(16) | ||
| excludes_stories | boolean | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
accounts6 columnsshow in diagram
Connected social accounts.
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | varchar(64) | PK | Primary key. |
| handle | varchar(255) | ||
| platform | varchar(32) | ||
| profile_picture_url | varchar(2500) | ||
| refresh_date | date | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |