Pulse - from front-end
to back-end

Product goals

Every architectural choice below serves one of these five outcomes.

Screens delivered

Seven fully designed screens, all linked in the navigation hub.

Tech stack

Why this stack

• Static front-end phase giúp bạn test đủ UX trước khi build backend thật - cùng component cho internal và client view.
• Postgres + Timescale lets you keep both the relational brief (campaigns, influencers, contracts) and the time-series (hourly metric snapshots) in one database. One query layer, one backup strategy.
• n8n orchestration keeps schedule, retry, logs, and alerts easy to debug. Provider complexity stays outside the browser.

System architecture

Three loops run continuously. The dashboard is just a view over the state they produce.

Loop 1 · Ingestion

• Scheduled n8n runs. 5-6 times/day, n8n reads booked post URLs and calls ScrapeCreators API for public metrics.
• Normalized payload. n8n or backend maps provider output into Pulse's campaign, booking, post and daily metric schema.
• Raw snapshot. Each poll inserts a row into metrics_hourly (Timescale hypertable). We never overwrite - history is gold for the Daily Growth chart.
• Easy debug. n8n execution history shows which node failed, the runId, payload, retry status, and alert destination.
• CSV import. For one-off manual updates, the agency can upload a CSV in-app; same schema lands in the same table.

Loop 2 · Aggregation

• A second cron rolls hourly data into campaign_daily and influencer_daily materialized views.
• These power the dashboard - never query metrics_hourly directly from the UI. That table can hit millions of rows; aggregates stay under 10k.
• kpi_snapshots stores the "right now" %: actual / committed for views, engagement, posts. One row per influencer × campaign, updated after every aggregation pass.

Loop 3 · Alerting

• After every aggregation, a rule engine evaluates each active campaign at timeline milestones: 50%, 80%, 95%. If KPI is behind that milestone, alert severity rises.
• New alerts are inserted into alerts with a debounce key (no duplicate alerts within 24h).
• Google Chat webhook is the target notification channel, but it ships after the core campaign, KPI, sync, and alert system is fully functional.

PostgreSQL schema

Only the essentials - expand as you go.

-- CORE ENTITIES
CREATE TABLE organizations (
  id UUID PRIMARY KEY, name TEXT, created_at TIMESTAMPTZ
);

CREATE TABLE users (
  id UUID PRIMARY KEY, org_id UUID REFERENCES organizations,
  email CITEXT UNIQUE, role TEXT -- owner | manager | analyst
);

CREATE TABLE clients (
  id UUID PRIMARY KEY, org_id UUID,
  name TEXT, brand_color TEXT, logo_url TEXT
);

CREATE TABLE campaigns (
  id UUID PRIMARY KEY, org_id UUID, client_id UUID,
  name TEXT, status TEXT, -- draft | live | ended
  start_date DATE, end_date DATE, budget_cents BIGINT,
  committed_views BIGINT, committed_engagement BIGINT
);

CREATE TABLE influencers (
  id UUID PRIMARY KEY, display_name TEXT, avatar_url TEXT,
  tier TEXT -- nano | micro | mid | macro | mega
);

CREATE TABLE influencer_handles (
  id UUID PRIMARY KEY, influencer_id UUID,
  platform TEXT, -- tiktok | instagram | facebook
  handle TEXT, platform_user_id TEXT, followers BIGINT
);

-- THE BOOKING - a KOL committed to a campaign
CREATE TABLE bookings (
  id UUID PRIMARY KEY,
  campaign_id UUID, influencer_id UUID,
  fee_cents BIGINT,
  committed_views BIGINT, committed_engagement BIGINT,
  committed_posts INT, min_engagement_rate NUMERIC(5,2),
  exclusivity_days INT, usage_rights_days INT
);

-- POSTS - each booked piece of content
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  booking_id UUID, platform TEXT,
  platform_post_id TEXT, url TEXT, thumb_url TEXT,
  type TEXT, -- video | reel | feed | live | short
  caption TEXT, pull_status TEXT,
  error_message TEXT, posted_at TIMESTAMPTZ,
  last_pulled_at TIMESTAMPTZ, source TEXT
);

-- TIME-SERIES (Timescale hypertable)
CREATE TABLE metrics_hourly (
  post_id UUID, ts TIMESTAMPTZ,
  views BIGINT, likes BIGINT, comments BIGINT,
  shares BIGINT, saves BIGINT, reach BIGINT
);
SELECT create_hypertable('metrics_hourly', 'ts');

-- DERIVED SNAPSHOTS (updated by Loop 2)
CREATE TABLE kpi_snapshots (
  booking_id UUID PRIMARY KEY,
  actual_views BIGINT, actual_engagement BIGINT,
  actual_posts INT, kpi_pct NUMERIC(5,2),
  engagement_rate NUMERIC(5,2), updated_at TIMESTAMPTZ
);

-- ALERTS
CREATE TABLE alerts (
  id UUID PRIMARY KEY, campaign_id UUID, booking_id UUID,
  type TEXT, -- under_kpi | engagement_drop | deadline | sentiment
  severity TEXT, message TEXT, status TEXT,
  raised_at TIMESTAMPTZ, resolved_at TIMESTAMPTZ
);

-- SHARE LINKS
CREATE TABLE share_links (
  token TEXT PRIMARY KEY, -- 32-char random
  campaign_id UUID, created_by UUID,
  expires_at TIMESTAMPTZ, password_hash TEXT,
  last_viewed_at TIMESTAMPTZ, view_count INT DEFAULT 0
);

API surface

Current REST shape for the pilot backend. Without ?api=1, the front-end still uses localStorage as a safe demo fallback. Admin CRUD currently persists through the full snapshot endpoint; granular create/update endpoints can be added after the pilot schema is stable.

Real-time strategy

Three layers - each cheaper than the last.

"Real-time" is a spectrum - here's what the client actually sees

Security & client sharing

• Share tokens are 32-char random, stored hashed. The URL carries the plaintext token; the DB stores only its hash. Lose the DB, you don't leak active links.
• Expiry defaults to campaign end + 60 days. Agency can revoke any time with one click.
• Optional password for high-sensitivity campaigns. Bcrypt hash in share_links.password_hash.
• View logging. Every share view logs timestamp + coarse geo (city). Agency can see "client opened the report 14 times".
• Row-level security in Postgres. All internal queries are scoped by org_id; the share endpoint is the only public surface and it resolves the campaign scope from the token server-side.

Build roadmap

6 weeks to v1 with one full-stack engineer + one designer.

What I'd NOT build in v1

• Conversion tracking. Hidden in v1 until the measurement source is clear: pixel, UTM, promo code, client CRM, or another source.
• Comment sentiment analysis. Tempting but expensive; use a simple keyword flag for now.
• Custom dashboards. One great default beats a configurator. Add it when 10+ clients ask for the same tweak.
• YouTube. You said TikTok + IG + FB; don't let scope creep in.

Agency decisions for v1