How to Sync InstantDM Data to PostgreSQL

PostgreSQL is the most popular open-source relational database. Syncing InstantDM events to PostgreSQL gives you persistent storage, SQL queries, and a foundation for dashboards and analytics.

---

Schema

CREATE TABLE instagram_events (
  id SERIAL PRIMARY KEY,
  event_type VARCHAR(50) NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  instagram_user_id VARCHAR(50),
  username VARCHAR(100),
  flow_name VARCHAR(200),
  full_name VARCHAR(200),
  email VARCHAR(200),
  phone VARCHAR(50),
  interest VARCHAR(200),
  comment_text TEXT,
  message_text TEXT,
  post_url TEXT,
  raw_payload JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_events_type ON instagram_events(event_type);
CREATE INDEX idx_events_timestamp ON instagram_events(timestamp);
CREATE INDEX idx_events_username ON instagram_events(username);
CREATE INDEX idx_events_email ON instagram_events(email);

---

Webhook Receiver (Node.js)

const express = require('express');
const { Pool } = require('pg');
const app = express();
app.use(express.json());

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

app.post('/webhook', async (req, res) => {
  const { event, timestamp, data } = req.body;
  
  try {
    await pool.query(
      `INSERT INTO instagram_events 
       (event_type, timestamp, instagram_user_id, username, flow_name, 
        full_name, email, phone, interest, comment_text, message_text, 
        post_url, raw_payload)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)`,
      [
        event,
        timestamp,
        data.instagram_user_id,
        data.username,
        data.flow_name || null,
        data.response_variables?.full_name || null,
        data.response_variables?.email || null,
        data.response_variables?.phone || null,
        data.response_variables?.interest || null,
        data.comment_text || null,
        data.message_text || null,
        data.post_url || null,
        JSON.stringify(req.body),
      ]
    );
    
    res.status(200).json({ status: 'inserted' });
  } catch (error) {
    console.error('Insert error:', error);
    res.status(500).json({ status: 'error' });
  }
});

app.listen(3000);

---

Useful Queries

-- Leads today
SELECT * FROM instagram_events 
WHERE event_type = 'flow_completed' AND DATE(timestamp) = CURRENT_DATE;

-- Leads per flow
SELECT flow_name, COUNT(*) as leads 
FROM instagram_events 
WHERE event_type = 'flow_completed' 
GROUP BY flow_name ORDER BY leads DESC;

-- Unique leads with email
SELECT DISTINCT ON (email) * 
FROM instagram_events 
WHERE email IS NOT NULL 
ORDER BY email, timestamp DESC;

---

Hosting Options

• Supabase - free PostgreSQL with a dashboard and API
• Neon - serverless PostgreSQL with a generous free tier
• Railway - managed PostgreSQL, easy setup
• AWS RDS - production-grade, free tier available
• Self-hosted - Docker or bare metal

---

What's Next

• Set up MongoDB if you prefer document databases.
• Connect to Firebase for real-time sync.
• Build Custom Dashboards on top of your data.
• Explore the full API docs at instantdm.com/instagram-api-docs.