PostgreSQL Analytics

PostgreSQL serves as the time-series analytics database for social media and content performance metrics.

Overview

Container Details

Property	Value
Container	mediamagic-postgres
Port	5432
Version	PostgreSQL 16
Database	mediamagic_analytics
User	mediamagic

Schema

social_metrics

Daily aggregated metrics per platform.

CREATE TABLE social_metrics (
    id SERIAL PRIMARY KEY,
    metric_date DATE NOT NULL,
    platform VARCHAR(50) NOT NULL,
    impressions INTEGER DEFAULT 0,
    engagement INTEGER DEFAULT 0,
    reach INTEGER DEFAULT 0,
    followers INTEGER DEFAULT 0,
    posts_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(metric_date, platform)
);

Column	Type	Description
metric_date	DATE	Date of metrics
platform	VARCHAR(50)	Platform name (x, facebook, etc.)
impressions	INTEGER	Total impressions
engagement	INTEGER	Total engagement actions
reach	INTEGER	Unique accounts reached
followers	INTEGER	Follower count snapshot
posts_count	INTEGER	Posts made that day

post_performance

Individual post performance tracking.

CREATE TABLE post_performance (
    id SERIAL PRIMARY KEY,
    post_id VARCHAR(100) NOT NULL,
    platform VARCHAR(50) NOT NULL,
    ayrshare_id VARCHAR(100),
    content TEXT,
    posted_at TIMESTAMP,
    likes INTEGER DEFAULT 0,
    comments INTEGER DEFAULT 0,
    shares INTEGER DEFAULT 0,
    impressions INTEGER DEFAULT 0,
    clicks INTEGER DEFAULT 0,
    engagement_rate DECIMAL(5,2),
    collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(post_id, platform, collected_at)
);

Column	Type	Description
post_id	VARCHAR(100)	Platform's post ID
platform	VARCHAR(50)	Platform name
ayrshare_id	VARCHAR(100)	Ayrshare post ID
likes	INTEGER	Like/favorite count
comments	INTEGER	Comment count
shares	INTEGER	Share/retweet count
impressions	INTEGER	View count
engagement_rate	DECIMAL	Calculated rate

platform_followers

Historical follower counts for trend analysis.

CREATE TABLE platform_followers (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    platform VARCHAR(50) NOT NULL,
    count INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(date, platform)
);

Column	Type	Description
date	DATE	Snapshot date
platform	VARCHAR(50)	Platform name
count	INTEGER	Follower count

content_metrics

Video and podcast performance metrics.

CREATE TABLE content_metrics (
    id SERIAL PRIMARY KEY,
    content_id VARCHAR(100) NOT NULL,
    platform VARCHAR(50) NOT NULL,
    platform_content_id VARCHAR(100),
    views INTEGER DEFAULT 0,
    watch_hours DECIMAL(10,2) DEFAULT 0,
    likes INTEGER DEFAULT 0,
    comments INTEGER DEFAULT 0,
    shares INTEGER DEFAULT 0,
    downloads INTEGER DEFAULT 0,
    collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(content_id, platform, collected_at)
);

Column	Type	Description
content_id	VARCHAR(100)	EspoCRM content ID
platform	VARCHAR(50)	Platform name
views	INTEGER	View/play count
watch_hours	DECIMAL	Total watch time
downloads	INTEGER	Download count (podcasts)

Entity Relationship Diagram

Common Queries

Follower Trend (Last 30 Days)

SELECT 
    date,
    platform,
    count
FROM platform_followers
WHERE date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY date, platform;

Top Posts by Engagement

SELECT 
    post_id,
    platform,
    content,
    likes + comments + shares as total_engagement,
    engagement_rate
FROM post_performance
WHERE collected_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY total_engagement DESC
LIMIT 10;

Daily Platform Performance

SELECT 
    metric_date,
    platform,
    impressions,
    engagement,
    ROUND(engagement::numeric / NULLIF(impressions, 0) * 100, 2) as engagement_rate
FROM social_metrics
WHERE metric_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY metric_date DESC, platform;

Content Performance Comparison

SELECT 
    content_id,
    platform,
    SUM(views) as total_views,
    SUM(watch_hours) as total_watch_hours,
    SUM(likes) as total_likes
FROM content_metrics
GROUP BY content_id, platform
ORDER BY total_views DESC;

Data Collection Flow

Initialization

The schema is created on first run via postgres/init.sql:

-- postgres/init.sql
CREATE DATABASE mediamagic_analytics;

\c mediamagic_analytics

-- Create tables (as shown above)
CREATE TABLE social_metrics (...);
CREATE TABLE post_performance (...);
CREATE TABLE platform_followers (...);
CREATE TABLE content_metrics (...);

-- Create indexes
CREATE INDEX idx_social_metrics_date ON social_metrics(metric_date);
CREATE INDEX idx_post_performance_platform ON post_performance(platform);
CREATE INDEX idx_followers_date ON platform_followers(date);

Seeding Test Data

docker compose exec -T postgres psql -U mediamagic -d mediamagic_analytics < postgres/seed-data.sql

Backup & Restore

Backup

docker compose exec postgres pg_dump -U mediamagic mediamagic_analytics > backup.sql

Restore

docker compose exec -T postgres psql -U mediamagic mediamagic_analytics < backup.sql

Grafana Integration

PostgreSQL is configured as a data source in Grafana:

# grafana/provisioning/datasources/postgres.yml
datasources:
  - name: PostgreSQL
    type: postgres
    url: postgres:5432
    database: mediamagic_analytics
    user: mediamagic
    secureJsonData:
      password: ${POSTGRES_PASSWORD}

Performance Tips

Use Indexes

Add indexes on frequently filtered columns (date, platform).

Aggregate Old Data

Consider aggregating old granular data into daily/weekly summaries.

Partition by Date

For large datasets, consider partitioning tables by date.

Related

• Grafana Dashboards
• Analytics API
• Bridge Architecture