Ads Data Model

Entity Relationship

ads_placements (1)
  └── has_many ads_placement_prices (M)
        └── referenced by ads_campaign_placements

Org::Company (1)
  ├── has_many ads_campaigns
  └── has_many ads_creatives

ads_campaigns (1)
  ├── has_many ads_campaign_placements
  └── has_many ads_campaign_reviews

ads_campaign_placements (M)
  ├── belongs_to ads_campaign
  ├── belongs_to ads_placement
  ├── belongs_to ads_placement_price
  ├── belongs_to ads_creative
  ├── has_many ads_impressions
  └── has_many ads_clicks

ads_impressions (1)
  └── has_many ads_clicks

---

Tables

ads_placements

Admin-managed supply of ad slots on the platform. Defines WHERE an ad can appear and what image dimensions are required. Pricing lives in ads_placement_prices, not here.

Column	Type	Notes
id	bigint PK
key	string	Unique identifier used by frontend (e.g. home_hero, job_list)
name	string	Human-readable name
description	text (nullable)	Optional description
required_width	integer	Expected creative width in pixels
required_height	integer	Expected creative height in pixels
is_active	boolean	Only active placements appear to employers

Key constraints:

• key is unique — used as the route parameter for serving (GET /marketplace/ads/campaign_placements/:key)
• is_active = false hides the placement from the employer's browser and prevents new bookings, but does not affect running campaigns

---

ads_placement_prices

Tiered duration-and-credit deals per placement. Admin creates and manages these. Employers select a price when adding a placement to their campaign.

Column	Type	Notes
id	bigint PK
ads_placement_id	bigint FK	Which placement this price belongs to
name	string	Display label (e.g. "3 Days", "Weekly", "10 Days")
duration_days	integer	How many days the ad runs
credit_cost	integer	Total placement credits charged for this price tier
is_active	boolean	Only active prices are shown to employers
created_at	datetime
updated_at	datetime

Example rows:

placement	name	duration_days	credit_cost
home_hero	3 Days	3	3
home_hero	Weekly	7	5
home_hero	10 Days	10	7
job_list	Weekly	7	3
bottom_banner	Weekly	7	2

Key design: Pricing lives here, not on ads_placements. If you want to offer a new deal (e.g. "Monthly"), you add a new price row without touching the placement definition. Admin can add/remove prices independently.

---

ads_campaigns

The top-level campaign container owned by an employer. Tracks the approval workflow state and the employer's requested start date (shared by all placements in the campaign).

Column	Type	Notes
id	bigint PK
org_company_id	bigint FK	Org::Company — campaign owner
created_by_id	bigint FK	Org::UserProfile — who created it
name	string	Campaign display name
requested_start_date	datetime (nullable)	Optional employer-preferred start date for all placements. Stored as 00:00 SGT of the chosen date (employer inputs date only, time is implicit). If set: must be ≥ today + MIN_START_DATE_LEAD_TIME days (2 days, validated at create/update and again at submission). If null: placements go live DEFAULT_START_DAYS_AFTER_APPROVAL day(s) after admin approves at 00:00 SGT.
status	string	See status table below
submitted_at	datetime (nullable)	When employer submitted for review
approved_at	datetime (nullable)	When admin approved the campaign. Credit reservation timestamp for billing.
cancelled_at	datetime (nullable)	When the campaign was cancelled (employer or admin). Used to calculate pro-rata credit refunds once billing is implemented.
deleted_at	datetime (nullable)	Soft delete

Status enum:

Status	Meaning	Editable?	Credits
draft	Employer building campaign	Yes	None reserved
pending_review	Submitted, awaiting admin review	No	Reserved at submission
approved	Admin approved, placements running	No	Consumed daily
rejected	Admin rejected, employer can revise	Yes	Released on rejection
cancelled	Cancelled by employer or admin	No	Remaining released
completed	All placements completed or cancelled	No	Fully consumed or released

note

budget, start_date, end_date, and is_paid from the previous implementation are removed:

• requested_start_date is optional on the campaign — now stored as datetime (employer inputs date only; stored at 00:00 SGT of that date). All placements share this start time when set; if not set, they go live DEFAULT_START_DAYS_AFTER_APPROVAL day(s) after admin approves at 00:00 SGT.
• Per-placement start_time / end_time (datetime) are set by admin at approval. For on-time approvals: start_time = requested_start_date or tomorrow at 00:00 SGT; end_time = start_time + duration_days. For late approvals (approval time > requested_start_date): start_time = Time.now, placement goes active immediately.
• Budget is derivable: SUM(campaign_placements.credit_cost_snapshot)
• is_paid replaced by ::Billing credit reservation

---

ads_placement_prices → ads_campaign_placements (Credit Cost Snapshot)

When an employer books a CampaignPlacement, ads_placement_prices.credit_cost is copied to ads_campaign_placements.credit_cost_snapshot. This freeze ensures:

• Future admin price edits do not affect existing campaigns
• Billing always reserves the exact snapshotted amount

This is the same pattern as billing_invoice_items snapshotting price terms.

---

ads_campaign_placements

One concrete ad unit within a campaign: a creative running on a placement price. Owns its own schedule and status.

Column	Type	Notes
id	bigint PK
ads_campaign_id	bigint FK	Parent campaign
ads_placement_id	bigint FK	Which slot
ads_placement_price_id	bigint FK	Which price (duration + credit cost)
ads_creative_id	bigint FK	Which image
credit_cost_snapshot	integer	Snapshot of ads_placement_prices.credit_cost at booking time
start_time	datetime (nullable)	Exact moment the ad goes live — set at admin approval. For future-dated approvals: requested_start_date (or tomorrow) at PLACEMENT_START_HOUR:00 SGT. For late approvals (approval time > requested_start_date): set to Time.now and status transitions directly to active.
end_time	datetime (nullable)	start_time + duration_days.days. The ad expires once end_time <= Time.current.
status	string	See status table below
destination_url	text	Where user lands after clicking the ad
headline	string	Ad headline text
description	text (nullable)	Optional supporting copy
last_served_at	datetime (nullable)	Set each time served — drives fair rotation
deleted_at	datetime (nullable)	Soft delete

Status enum:

Status	Meaning	Billing
pending	Campaign in draft or pending_review	Hold active (after submission)
scheduled	Approved, start_time is in the future — waiting for scheduler	Hold active
active	Running — start_time reached (or admin approved late, set immediately)	ConsumeEntitlements(units: credit_cost_snapshot / duration_days) daily
completed	end_time reached — ad finished running	Hold closed
cancelled	Cancelled mid-campaign	ReleaseHold on remaining
rejected	Campaign was rejected	ReleaseHold

Key constraints:

• destination_url must belong to an allowed domain (jodapp.com, jodapp.dev)
• credit_cost_snapshot is set once at creation and never updated
• Editing is only allowed while the parent campaign is in draft or rejected status
• start_time / end_time are null until admin approves; the serving layer uses status = active not datetime ranges

Scope: active → filters where deleted_at IS NULL

---

ads_campaign_reviews

Append-only audit log of every admin action (approve or reject) on a campaign. Provides full history across multiple submission cycles.

Column	Type	Notes
id	bigint PK
ads_campaign_id	bigint FK	Which campaign was reviewed
admin_id	bigint FK	Identities::Admin who acted
action	string	approved or rejected
notes	text (nullable)	Rejection reason or approval notes
created_at	datetime	When the action was taken

Why a separate table (not a column on ads_campaigns)?

A single rejection_reason column on the campaign gets overwritten on each submission cycle. The ads_campaign_reviews table preserves the full history — if a campaign is rejected twice before approval, you can see all feedback the employer received.

---

ads_creatives

Reusable image assets owned by a company.

Column	Type	Notes
id	bigint PK
org_company_id	bigint FK	Owning company
created_by_id	bigint FK	Org::UserProfile who uploaded it
name	string	Display name (e.g. "February 2025 Banner")
asset_url	string	S3 path: public/org/companies/{uuid}/ads-creatives/...
width	integer	Actual image width in pixels
height	integer	Actual image height in pixels
deleted_at	datetime (nullable)	Soft delete

Upload constraints enforced at creation:

• Allowed formats: .jpg, .jpeg, .png, .webp
• Max file size: 2 MB
• Aspect ratio must match the target placement within 5% tolerance

Soft delete restriction: A creative cannot be soft-deleted if it is referenced by any non-deleted CampaignPlacement.

---

ads_impressions

One record per ad serving event. Created synchronously when the marketplace serve endpoint is called.

Column	Type	Notes
id	UUID PK	Auto-generated via gen_random_uuid() — used as public identifier
ads_campaign_placement_id	bigint FK	Which ad was served
viewer_id	bigint FK (nullable)	Identities::User — set only if viewer is logged in
viewed_at	datetime (nullable)	Set when IntersectionObserver beacon fires (ad entered viewport)
created_at	datetime	When the ad was served

Why UUID PK? The impression ID is exposed publicly in the click URL (/marketplace/ads/clicks/:uuid). UUIDs prevent enumeration attacks vs sequential integer IDs.

viewed_at is nullable — an impression without viewed_at means the ad was returned to the browser but never scrolled into view.

---

ads_clicks

One record per click event. Created synchronously when the redirect endpoint is hit.

Column	Type	Notes
id	bigint PK
ads_campaign_placement_id	bigint FK	Which ad was clicked
ads_impression_id	UUID FK (nullable)	Linked impression — nullable for orphaned clicks
clicker_id	bigint FK (nullable)	Identities::User — set if clicker is logged in
created_at	datetime	When the click happened

Deduplication: Multiple clicks can exist per impression. Unique click count is computed at query time: COUNT DISTINCT ads_impression_id.

---

Key Design Decisions

Prices for Tiered Pricing

Pricing is managed in ads_placement_prices, not on ads_placements. This allows multiple deals per placement (3 days, 7 days, 10 days) with volume discounts in credits, without touching the placement definition. Admin can add/remove prices independently.

One Start Datetime on Campaign; Per-Placement End Datetimes on CampaignPlacement

The employer sets a single requested_start_date (datetime, stored at 00:00 SGT) on the campaign. All placements in the campaign share this start moment. Because each placement has its own duration_days (from the selected price), each placement ends at a different end_time — mixed-duration campaigns are fully supported. The actual start_time / end_time per placement are set by admin at approval and stored on ads_campaign_placements.

For late admin approvals (when approval happens after requested_start_date), start_time is set to the approval moment (Time.now) and the placement goes live immediately (status → active). end_time = start_time + duration_days.days so the employer always receives the full contracted duration.

Credit Cost Snapshotting

credit_cost_snapshot is set once at booking from the price and never updated — the same pattern as billing_invoice_items in Billing. Protects running campaigns from admin price edits.

Reserve at Submission, Not Creation

Credits are reserved when the employer submits the campaign (draft → pending_review), not during draft editing. This prevents noisy ledger entries during the draft phase and only locks credits when the employer formally commits.

Campaign-Level Approval

Admin approves or rejects the whole campaign in one action — not individual placements. If one placement has an issue, admin notes it in the rejection reason and the employer fixes and resubmits everything.

Append-Only Review Log

ads_campaign_reviews is append-only — one row per approve/reject action. Never update or delete rows. This gives a clean audit trail for multi-cycle submissions.

Fair Rotation via last_served_at

Instead of random selection, the serving layer picks the CampaignPlacement with the oldest last_served_at (or NULL first). All active ads get equal exposure over time.

Soft Deletes Everywhere

All major resources use deleted_at. This preserves referential integrity for analytics — impressions and clicks for a deleted campaign placement remain queryable.