v1.0
Powered by

# Automatic Propagation: Tags, Campaign & Budget

Three values are automatically assigned to videos via PostgreSQL triggers — no application code needed. They differ in when they fire and what they affect:

Property Trigger type Scope Table
Tags AFTER INSERT/DELETE on blogger_tags + AFTER INSERT on videos Existing + future videos video_tags
Campaign BEFORE INSERT on videos Future videos only videos.campaign_tag
Budget BEFORE INSERT on videos Future videos only videos.budget

Tags propagate retroactively (to all existing videos). Campaign and budget are set at insert time only — they don't update videos that already exist.

# How It Works 

Add tag to blogger
  ↓
blogger_tags (INSERT)
  ↓
Trigger: propagate_blogger_tag_change_to_videos()
  ↓
video_tags (INSERT for all videos from this blogger)

New video created with blogger_id
  ↓
videos (INSERT)
  ↓
Trigger: propagate_blogger_tags_to_video()
  ↓
Fetch all tags from blogger_tags
  ↓
video_tags (INSERT for this video)

# Database Triggers

# Trigger 1: trigger_propagate_blogger_tags_on_video_insert

  • Event: AFTER INSERT on videos
  • Condition: NEW.blogger_id IS NOT NULL
  • Action: Inserts all tags from blogger_tags for this blogger into video_tags

# Trigger 2: trigger_propagate_blogger_tags_on_video_update

  • Event: AFTER UPDATE OF blogger_id on videos
  • Condition: blogger_id changed and is not NULL
  • Action: Removes old blogger's tags, adds new blogger's tags

# Trigger 3: trigger_propagate_tag_add_to_videos

  • Event: AFTER INSERT on blogger_tags
  • Action: Adds new tag to all existing videos from this blogger

# Trigger 4: trigger_propagate_tag_remove_from_videos

  • Event: AFTER DELETE on blogger_tags
  • Action: Removes the tag from all videos from this blogger

All inserts use ON CONFLICT DO NOTHING to handle duplicates gracefully.

# Schema

# blogger_tags

Links bloggers to tags. Changing this table triggers propagation to video_tags.

Column Type Description
blogger_id UUID FK → blogger_information.id
tag_id UUID FK → tags.id
organization_id UUID FK → organizations.id

# video_tags

Automatically maintained — do not write to directly.

Column Type Description
external_video_id TEXT FK → videos.external_video_id
tag_id UUID FK → tags.id
organization_id UUID  

# Scenarios

Adding a tag to a blogger: Trigger fires → all existing videos get the tag immediately. All future videos will also get it on INSERT.

Removing a tag from a blogger: Trigger fires → tag removed from all videos from this blogger.

Changing a video's blogger_id: Old blogger's tags are removed; new blogger's tags are added.

Excel/Sheets import with blogger_id set: Trigger fires on INSERT → video automatically gets all blogger tags.

# API 

POST /blogger/set-tags
{
  "tracking_id": "uuid",
  "tag_ids": ["tag-uuid-1", "tag-uuid-2"]
}

This replaces all current tags (delete + insert). The triggers handle video propagation automatically.

# Campaign Auto-Assignment

When a campaign is set on a blogger, every new video fetched for that blogger automatically gets campaign_tag set — via a BEFORE INSERT trigger on the videos table.

POST /blogger/set-campaign { tracking_id, campaign: "Q1 2026" }
  ↓
blogger_campaign (UPSERT: blogger_id + organization_id + campaign)
  ↓
[next video INSERT for this blogger]
  ↓
BEFORE INSERT trigger on videos
  ↓
SELECT campaign FROM blogger_campaign WHERE blogger_id = NEW.blogger_id
  ↓
SET NEW.campaign_tag = campaign

# Key behavior

  • Only affects future videos — existing videos are NOT updated
  • If campaign is removed, future videos get campaign_tag = NULL
  • Works for all sources: blogger tracking, Excel import, Sheets import — any INSERT on videos

# Schema

# blogger_campaign

Column Type Description
blogger_id UUID FK → blogger_information.id
organization_id UUID  
campaign TEXT Campaign name written to videos.campaign_tag

# API 

POST /blogger/set-campaign
{
  "tracking_id": "uuid",
  "campaign": "Q1 2026"
}

# Budget Auto-Assignment

Works identically to campaign — a BEFORE INSERT trigger reads from blogger_budget and sets videos.budget on each new video.

POST /blogger/set-budget-per-video { tracking_id, budget_per_video: 5000 }
  ↓
blogger_budget (UPSERT: blogger_id + organization_id + budget_per_video)
  ↓
[next video INSERT for this blogger]
  ↓
BEFORE INSERT trigger on videos
  ↓
SELECT budget_per_video FROM blogger_budget WHERE blogger_id = NEW.blogger_id
  ↓
SET NEW.budget = budget_per_video

# Key behavior

  • Only affects future videos — existing videos are NOT updated
  • Used to compute CPM/payout in analytics

# Schema

# blogger_budget

Column Type Description
blogger_id UUID FK → blogger_information.id
organization_id UUID  
budget_per_video NUMERIC Written to videos.budget on INSERT

# API 

POST /blogger/set-budget-per-video
{
  "tracking_id": "uuid",
  "budget_per_video": 5000
}

# Summary: All Three Propagation Mechanisms 

blogger_tags (change)
  ↓ AFTER trigger
  → video_tags updated for ALL videos (retroactive)

blogger_campaign / blogger_budget (change)
  ↓ (no immediate effect on existing videos)
  → BEFORE INSERT trigger fires on next video INSERT
  → videos.campaign_tag / videos.budget set at creation time

© Influtics