Feed Views

Normalized feed views for ad browsing, saved ads, and project-filtered ads

AdRecon exposes three feed views that normalize raw ad data and scope it to user context. All views use security_invoker = true so RLS policies apply based on the calling user.

ads_feed_v2

Primary feed view. Normalizes apify_ads_raw into a clean ad schema with computed niche, network, status, and days running.

Purpose

• Powers the “All Ads” scope in the dashboard
• Applies classification functions (classify_niche, classify_network) to raw ad text
• Computes days_running from start_date or updated_at
• Computes creative_count from media arrays and carousel cards
• Provides fallback values for missing fields

Columns

ad_archive_id · text

Meta Ad Library archive ID.

advertiser · text

Coalesced from page_name, snapshot.page_name, raw.page_name. Defaults to "Unknown Advertiser".

offer_name · text

Coalesced from snapshot.title, raw.snapshot.title, raw.ad_title, page_name. Defaults to "Ad {ad_archive_id}".

ad_copy · text

Coalesced from snapshot.body.text, snapshot.body, raw.snapshot.body.text, raw.ad_creative_body, raw.ad_copy, raw.description. Defaults to empty string.

niche · text

Computed via classify_niche(classification_text). Values: Health, Wealth, BizOpp, Relationship, Survival, Other.

network · text

Computed via classify_network(destination_url, classification_text). Values: ClickBank, Digistore24, BuyGoods, MaxWeb, Other.

days_running · int

Computed as floor((now() - coalesce(start_date, updated_at)) / 86400). Minimum 0.

status · text

"Active" if is_active is true or null, "Inactive" if false.

image_url · text

First available image from snapshot.images[0].original_image_url, snapshot.cards[0].original_image_url, media.imageUrls[0], video thumbnails, or fallback placeholder.

video_url · text

First available video from snapshot.videos[0].video_hd_url, snapshot.cards[0].video_hd_url, media.videoUrls[0], or null.

start_date · timestamptz

When the ad first went live.

destination_url · text

Coalesced from snapshot.link_url, snapshot.cards[0].link_url, source_url, ad_library_url. Defaults to Meta Ad Library URL.

cta_text · text

Call-to-action button text. Defaults to "Learn More".

headline · text

Coalesced from snapshot.title, raw.headline, page_name. Defaults to "Untitled Ad".

link_description · text

Coalesced from snapshot.link_description, snapshot.cards[0].link_description. Defaults to empty string.

ad_library_url · text

Direct link to the ad in Meta Ad Library.

updated_at · timestamptz

Last update timestamp from apify_ads_raw.

creative_count · int

Number of creative variants. Computed as max(1, imageUrls.length + videoUrls.length, cards.length).

Normalization Logic

The view uses cascading coalesce() to extract fields from multiple JSON paths:

coalesce(
  nullif(a.snapshot->>'title', ''),
  nullif(a.raw->'snapshot'->>'title', ''),
  nullif(a.raw->>'ad_title', ''),
  'Ad ' || a.ad_archive_id
) as offer_name

This pattern:

1. Tries snapshot.title
2. Falls back to raw.snapshot.title
3. Falls back to raw.ad_title
4. Defaults to "Ad {id}" if all are null/empty

Classification Functions

classify_niche(classification_text)

Regex-based niche classification:

Pattern	Niche
weight|metabolism|supplement|health|wellness|fat burn|prostate|vision|joint|energy|diet	Health
wealth|crypto|bitcoin|income|money|finance|trading|invest|stock|retirement|cashflow	Wealth
biz opp|business opportunity|affiliate|make money online|lead gen|agency|ecommerce|dropship	BizOpp
relationship|dating|marriage|text him|love|obsession|attract|breakup	Relationship
survival|prepper|off grid|grid down|emergency|collapse|self defense	Survival
none match	Other

classify_network(destination_url, classification_text)

Regex-based affiliate network detection:

Pattern	Network
clickbank|hop.clickbank.net|clkbank	ClickBank
digistore24|digistore	Digistore24
buygoods	BuyGoods
maxweb	MaxWeb
none match	Other

Checks destination_url first, then classification_text.

classification_text is a lowercase concatenation of all text fields from page_name, snapshot, raw, used as input to both classifiers.

---

user_saved_ads_feed_v1

Joins user_saved_ads with ads_feed_v2 to show only ads the current user has saved.

Purpose

Powers the “Saved” scope in the dashboard.

Columns

user_id · uuid

The user who saved the ad. Scoped by RLS to auth.uid().

* · all columns from ads_feed_v2

All columns from ads_feed_v2 (ad_archive_id, advertiser, offer_name, ad_copy, niche, network, days_running, status, image_url, video_url, start_date, destination_url, cta_text, headline, link_description, ad_library_url, updated_at, creative_count).

SQL Definition

create or replace view public.user_saved_ads_feed_v1
with (security_invoker = true)
as
select
  s.user_id,
  a.*
from public.user_saved_ads s
join public.ads_feed_v2 a on a.ad_archive_id = s.ad_archive_id;

Filtering Behavior

• Only shows ads where user_saved_ads.user_id = auth.uid() (enforced by RLS on user_saved_ads)
• Inner join ensures only ads still present in apify_ads_raw are returned
• If an ad is deleted from apify_ads_raw, the cascade delete removes the user_saved_ads row, so the ad disappears from this view

---

user_project_ads_feed_v1

Joins user_saved_ad_projects with ads_feed_v2 to show ads in a specific project.

Purpose

Powers the “Project” scope in the dashboard when a project is selected.

Columns

user_id · uuid

The user who owns the project. Scoped by RLS to auth.uid().

project_id · uuid

The project ID. Dashboard filters on this column.

* · all columns from ads_feed_v2

All columns from ads_feed_v2.

SQL Definition

create or replace view public.user_project_ads_feed_v1
with (security_invoker = true)
as
select
  p.user_id,
  p.project_id,
  a.*
from public.user_saved_ad_projects p
join public.ads_feed_v2 a on a.ad_archive_id = p.ad_archive_id;

Filtering Behavior

• Only shows ads where user_saved_ad_projects.user_id = auth.uid() (enforced by RLS)
• Dashboard adds project_id = <selected_project_id> filter
• Inner join ensures only ads still in apify_ads_raw are shown
• If a project is deleted, cascade delete removes all user_saved_ad_projects rows, so ads disappear from this view for that project

---

Feed Query Contract

The frontend (src/lib/ads.ts) queries these views via fetchAdsPage(params) where params.scope determines the source:

Scope	View	Additional Filter
all	ads_feed_v2	None
saved	user_saved_ads_feed_v1	None (RLS scopes to current user)
project	user_project_ads_feed_v1	project_id = params.projectId

Common Filters Applied

• Search: OR-matches offer_name, advertiser, ad_copy via ilike
• Niche: Exact match on niche column
• Network: Exact match on network column
• Status: Exact match on status column
• Performance Bucket: Range filter on days_running (New: ≤14, Promising: ≥7, Profitable: ≥30, Proven: ≥60)

Fallback Behavior

classify_niche and classify_network functions process raw::text via regex, which is expensive. Server-side filters on these columns can cause statement timeouts (HTTP 500).

When timeouts occur, the data layer:

1. Falls back to updatedAt desc sort if daysRunning sort times out
2. Fetches IDs without expensive filters, then applies niche/network matching client-side in JavaScript
3. Uses ID-first retrieval with probe batches to scan and match rows incrementally

See src/lib/ads.ts:1556 (fetchAdsPage) for full fallback chain.

---

Performance Notes

• Indexes: The views themselves are not indexed. Queries rely on indexes on apify_ads_raw.ad_archive_id, user_saved_ads(user_id, ad_archive_id), and user_saved_ad_projects composite keys.
• Statement timeout: PostgREST default is 60s. Broad queries with niche/network filters can exceed this. The app uses client-side filtering fallback to handle timeouts gracefully.
• Pagination: Uses PostgREST range() for offset-based pagination. Client-side dedup logic in src/lib/ads.ts removes duplicate ads by content hash.

creative_count was added in migration 20260226_add_creative_count_to_feed.sql to support performance bucket classification. Downstream views (user_saved_ads_feed_v1, user_project_ads_feed_v1) inherit it via a.*.