offer_statusFourteen states (OFFER_STATUS_VALUES). The lifecycle is enforced by OFFER_TRANSITIONS in @zooly/types (packages/types/src/types/Offer.ts); see Architecture for the diagram.
CREATE TYPE "public"."offer_status" AS ENUM(
'DRAFT',
'ADMIN_REVIEW',
'APPROVED',
'COUNTERED',
'ACCEPTED',
'PENDING_PAY_CAPTURE',
'PAID',
'DELIVERED',
'REVISION_REQUESTED',
'COMPLETED',
'DISPUTED',
'REJECTED',
'CANCELLED',
'EXPIRED'
);offer_expire_policyWhat the auto-expiry cron does when an APPROVED / COUNTERED offer's expires_at elapses (EXPIRE_POLICY_VALUES).
CREATE TYPE "public"."offer_expire_policy" AS ENUM(
'expire',
'remind_talent',
'ping_brand'
);image_kindDefined alongside image_candidate_session. Carried on offers.image_kind for image campaigns so the talent UI can render the picked sample with the right framing (DRAFT vs FINAL).
offersSource of truth: packages/db/src/schema/offers.ts. Mirrored as Offer in packages/types/src/types/Offer.ts.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | text | NOT NULL | nanoid() | Primary key |
seller_account_id | text | NOT NULL | — | FK to account.id (talent / seller) |
buyer_account_id | text | NOT NULL | — | FK to account.id (brand / buyer) |
buyer_brand_name | text | NULL | — | Free-text brand label captured at submit time (independent of account display name) |
blast_offer_id | text | NULL | — | FK to blast_offers.id when this row was fanned out from a blast |
idempotency_key | text | NULL (unique) | — | Per-request key set by the submit / respond / deliver / complete routes |
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
campaign_type | text | NULL | — | Single canonical campaign type from the CAMPAIGN_TYPES registry. Set at submit. |
campaign_types | text[] | NOT NULL | '{}' | Legacy multi-select chips (kept for back-compat with older payloads) |
campaign_description | text | NOT NULL | — | Free-text brief |
script_text | text | NULL | — | Radio / voice-over script |
usage_types | text[] | NOT NULL | '{}' | Where the content will be used |
custom_usage | text | NULL | — | Free-text custom usage |
wants_sharing | boolean | NULL | — | Whether the brand wants a social-share bundle |
sharing_description | text | NULL | — | Free-text sharing-bundle details |
product_image_urls | text[] | NOT NULL | '{}' | Up to 3 brand-supplied product photos used as image-generation input |
image_kind | image_kind | NULL | — | DRAFT or FINAL; only meaningful for IMAGE campaign types |
selected_sample_url | text | NULL | — | URL of the image candidate the brand picked at submit |
generated_sample_urls | jsonb | NOT NULL | [] | Array of { modelKey, modelLabel, url } — every image candidate rendered for this offer |
image_candidate_session_id | text | NULL | — | Set on blast-created image/video offers so the talent's review UI can poll the candidate session. ZLink offers extract URLs into the columns above at submit, so this is typically null for them. |
voice_candidate_session_id | text | NULL | — | Voice-flow analogue of image_candidate_session_id |
selected_voice_url | text | NULL | — | URL of the voice candidate the brand picked at submit |
generated_voice_urls | jsonb | NOT NULL | [] | Array of { modelKey, modelLabel, url } — every voice candidate for this offer |
All amounts are stored as integers in the currency's minor unit (cents for USD, yen for JPY, etc.). See packages/util/src/currency.ts for zero-decimal handling. Multi-currency landed in 6a07f00f.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
currency | varchar(3) | NOT NULL | 'USD' | ISO 4217 currency code |
below_talent_threshold | boolean | NOT NULL | false | Set at submit when offer_amount_minor_unit is under the talent's effective minimum. Drives the below-threshold policy (flag / ask / auto-counter / auto-reject). |
offer_amount_minor_unit | integer | NOT NULL | — | Brand's offered price (talent take-home, before fees) |
counter_amount_minor_unit | integer | NULL | — | Legacy talent counter price. New clients should set counter_changes.offerAmountMinorUnit instead. |
counter_note | text | NULL | — | Talent's note attached to a counter |
counter_changes | jsonb | NULL | — | Whitelisted talent overrides on a counter (OfferCounterChanges). Applied onto the canonical columns when the brand accepts. |
zooly_fee_minor_unit | integer | NOT NULL | 0 | Platform fee snapshot |
total_brand_price_minor_unit | integer | NOT NULL | 0 | What the brand actually pays (offer + fees) |
counter_changes is a Postgres jsonb typed as Partial<Offer>. The whitelist of allowed keys lives in offerCounterChangesSchema (packages/contracts/src/schemas/offers.ts):
{
offerAmountMinorUnit?: number;
usageTypes?: string[];
customUsage?: string | null;
wantsSharing?: boolean | null;
sharingDescription?: string | null;
imageKind?: ImageKind | null;
scriptText?: string | null;
selectedSampleUrl?: string | null;
selectedVoiceUrl?: string | null;
}| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
status | offer_status | NOT NULL | 'DRAFT' | Current state in the 14-state FSM |
admin_reviewed_by | text | NULL | — | Admin account id who approved/rejected the most recent ADMIN_REVIEW |
admin_reviewed_at | timestamptz | NULL | — | When the admin acted |
admin_reject_reason | text | NULL | — | Free-text rejection reason |
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
revision_count | integer | NOT NULL | 0 | Number of REVISION_REQUESTED cycles |
delivery_file_urls | text[] | NULL | — | Talent-uploaded deliverable URLs |
delivery_notes | text | NULL | — | Optional note the talent attaches to the delivery |
delivery_submitted_at | timestamptz | NULL | — | When the talent uploaded the deliverable |
payment_stripe_id | text | NULL | — | Stripe PaymentIntent id for the brand's payment |
payment_authorized_at | timestamptz | NULL | — | When the card was authorized (hold placed) on PENDING_PAY_CAPTURE; used by the cron to void auths nearing Stripe's 7-day limit |
auto_release_at | timestamptz | NULL | — | When DELIVERED auto-promotes to COMPLETED (auto-release timer, set +30d on payment) |
deleted_at | timestamptz | NULL | — | Soft delete |
created_at | timestamptz | NOT NULL | now() | |
updated_at | timestamptz | NOT NULL | now() |
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
expires_at | timestamptz | NULL | — | Auto-expiry deadline; set when the offer enters APPROVED / COUNTERED |
expire_policy | offer_expire_policy | NOT NULL | 'remind_talent' | What the expiry cron does at the deadline |
expires_in_days | integer | NOT NULL | 30 | Brand override for the expiry window (1–365) |
stale_reminder_sent_at | timestamptz | NULL | — | Dedup signal + deadline-bump marker for OFFER_STALE_REMINDER |
suggest_similar_sent_at | timestamptz | NULL | — | Dedup signal for the BRAND_SUGGEST_SIMILAR re-engagement card |
suggest_similar_dismissed_at | timestamptz | NULL | — | When the brand dismissed the suggestion card |
offers_seller_idx on seller_account_idoffers_buyer_idx on buyer_account_idoffers_status_idx on statusoffers_expires_at_idx on expires_atseller_account_id → account.idbuyer_account_id → account.idblast_offer_id → blast_offers.idKey migrations in chronological order (full list in packages/db/drizzle/):
0016_moaning_risque.sql — initial offers table0017_offers_buyer_account.sql — replace buyer_email / buyer_name with buyer_account_idamount_minor_unit_migration — switch all amounts to minor units (6eaa6062)0072_complete_randall_flagg.sql — add counter_changes jsonb (3b6f6cbf, made idempotent in 4371456c)Generate new migrations with:
cd packages/db && npm run db:generateApply migrations with:
cd packages/db && npm run db:migrate(Never use drizzle-kit push — only generated migrations, per project rules.)