Skip to main content
AdRecon uses Supabase (PostgreSQL) for data persistence. The schema is organized around ad storage, user saves, projects, and integrations.

​
Core Tables

​
apify_ads_raw

Source table for all ad data. Contains raw ad metadata from Meta Ad Library scrapers.
​
ad_archive_id
text
required
Primary key. Meta Ad Library archive ID (e.g., 123456789012345).
​
page_name
text
Advertiser page name from Meta.
​
snapshot
jsonb
Structured ad snapshot from Meta Ad Library API. Contains fields like:
  • title: ad headline
  • body.text: ad copy
  • link_url: destination URL
  • cta_text: call-to-action button text
  • images: array of image objects with original_image_url, resized_image_url
  • videos: array of video objects with video_hd_url, video_sd_url, video_preview_image_url
  • cards: carousel cards with media + link data
​
media
jsonb
Normalized media URLs:
  • imageUrls: array of image URLs
  • videoUrls: array of video URLs
  • previewImageUrls: array of video preview/thumbnail URLs
​
raw
jsonb
Full raw scraper payload. Fallback source for fields not in snapshot.
​
source_url
text
Original landing page URL if captured during scraping.
​
ad_library_url
text
Direct link to the ad in Meta Ad Library (e.g., https://www.facebook.com/ads/library/?id=...).
​
start_date
timestamptz
When the ad first went live. Used to compute days_running.
​
is_active
boolean
Whether the ad is currently running. Mapped to Active/Inactive status in feed views.
​
updated_at
timestamptz
Last time the row was updated. Fallback for days_running when start_date is null.
​
created_at
timestamptz
Row creation timestamp.

​
user_saved_ads

Per-user saved ads. Junction table linking users to ads they’ve bookmarked.
​
user_id
uuid
required
References auth.users(id). Defaults to auth.uid(). Cascade deletes when user is deleted.
​
ad_archive_id
text
required
References apify_ads_raw(ad_archive_id). Cascade deletes when ad is removed.
​
created_at
timestamptz
When the ad was saved.
​
updated_at
timestamptz
Auto-updated via trigger trg_user_saved_ads_updated_at.
Primary Key: (user_id, ad_archive_id) Indexes:
  • idx_user_saved_ads_user on user_id
  • idx_user_saved_ads_ad on ad_archive_id

​
user_projects

User-created folders for organizing saved ads.
​
id
uuid
required
Primary key. Auto-generated via gen_random_uuid().
​
user_id
uuid
required
References auth.users(id). Defaults to auth.uid(). Cascade deletes.
​
name
text
required
Project name. Must be trimmed, 1-100 characters, unique per user (case-insensitive). Cannot be "Saved".
​
created_at
timestamptz
Project creation time.
​
updated_at
timestamptz
Auto-updated when project is modified or ads are added/removed via trigger trg_user_projects_updated_at.
Constraints:
  • unique (user_id, name) — case-insensitive via migration 20260224193000
  • unique (id, user_id) — compound uniqueness for foreign key reference
  • check (name = btrim(name))
  • check (char_length(name) between 1 and 100)
  • check (name <> 'Saved')
Indexes:
  • idx_user_projects_user_id on user_id
  • idx_user_projects_updated_at on (user_id, updated_at desc)

​
user_saved_ad_projects

Links saved ads to projects. Many-to-many relationship between user_saved_ads and user_projects.
​
user_id
uuid
required
References auth.users(id). Cascade deletes.
​
ad_archive_id
text
required
Ad archive ID.
​
project_id
uuid
required
References user_projects(id, user_id). Cascade deletes when project is deleted.
​
created_at
timestamptz
When the ad was added to the project.
Primary Key: (user_id, ad_archive_id, project_id) Foreign Keys:
  • (user_id, ad_archive_id) references user_saved_ads on delete cascade
  • (project_id, user_id) references user_projects(id, user_id) on delete cascade
Indexes:
  • idx_user_saved_ad_projects_user_project on (user_id, project_id, ad_archive_id)
  • idx_user_saved_ad_projects_user_ad on (user_id, ad_archive_id, project_id)
Triggers:
  • trg_user_saved_ad_projects_touch_project — updates parent project’s updated_at on insert/update/delete

​
Fanbasis Integration Tables

​
fanbasis_enabled_offers

Tracks which Fanbasis products trigger user provisioning on purchase.
​
service_id
text
required
Primary key. Fanbasis product/service ID.
​
title
text
Product name.
​
price
numeric
Product price.
​
enabled
boolean
Whether purchases of this product should provision AdRecon access.
​
created_at
timestamptz
Row creation time.
​
updated_at
timestamptz
Auto-updated via trigger trg_fanbasis_enabled_offers_updated_at.

​
fanbasis_webhook_log

Immutable audit log of all Fanbasis webhook events.
​
id
bigint
required
Primary key. Auto-increment.
​
event_type
text
required
Fanbasis event type (e.g., payment.succeeded, payment.refunded).
​
service_id
text
Associated product/service ID.
​
fan_email
text
Customer email from webhook payload.
​
payload
jsonb
Full webhook event payload.
​
result
text
Processing result: received, provisioned, revoked, skipped, error.
​
error_message
text
Error detail if result = 'error'.
​
created_at
timestamptz
Event receipt time.
Index: idx_fanbasis_webhook_log_created on created_at desc

​
Utility Tables

​
page_rip_log

Rate-limit tracking for Page Ripper (/api/download-page).
​
id
bigint
required
Primary key. Auto-increment.
​
user_id
uuid
required
References auth.users(id). Cascade deletes.
​
url
text
required
Captured landing page URL.
​
created_at
timestamptz
Capture timestamp.
Index: idx_page_rip_log_user_created on (user_id, created_at desc)
Service-role only access. No RLS policies for regular users. API enforces 10 captures per user per 15-minute window.

​
Relationships

  • auth.users (Supabase Auth) → user_saved_ads, user_projects, page_rip_log (cascade delete)
  • apify_ads_raw → user_saved_ads (cascade delete if ad removed)
  • user_saved_ads + user_projects → user_saved_ad_projects (cascade delete if either parent is removed)
Deleting a user cascades to all saved ads, projects, and project links. Deleting an ad from apify_ads_raw removes all user saves and project associations.

Build docs developers (and LLMs) love

Get started for freeTalk to us