Offers Database Schema

Database table and enum for offers

Enums

`offer_status`

Thirteen 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',
  'APPROVED',
  'COUNTERED',
  'ACCEPTED',
  'PENDING_PAY_CAPTURE',
  'PAID',
  'DELIVERED',
  'REVISION_REQUESTED',
  'COMPLETED',
  'DISPUTED',
  'REJECTED',
  'CANCELLED',
  'EXPIRED'
);

`offer_expire_policy`

What 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_kind`

Defined 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).

Table: `offers`

Source of truth: packages/db/src/schema/offers.ts. Mirrored as Offer in packages/types/src/types/Offer.ts.

Identity & participants

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

Campaign & creative brief

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

Pricing

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).
`is_demo`	boolean	NOT NULL	`false`	Set when the Z-Agent room that minted this offer was running a scripted test scenario (a `mock_tape_id`), not a real negotiation. Shown in Offers & Deals marked as Demo so it is not mistaken for a live deal.
`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;
}

Status

Column	Type	Nullable	Default	Description
`status`	offer_status	NOT NULL	`'DRAFT'`	Current state in the 13-state FSM
`brand_approved_at`	timestamptz	NULL	—	Brand-first Approve on the conversation document. Does not change `status`. Cleared (`BRAND_APPROVAL_CLEARED`) when a material rewrite lands while the deal is still waiting on talent.
`document`	jsonb	NULL	—	One-page `OfferDocument` (payment plan, when, where, brief, content, terms). Unlocks the Offer tab when present.
`admin_reject_reason`	text	NULL	—	Set by the below-threshold auto-reject rule; marks offers the talent never saw so lists hide them

Delivery, payment & lifecycle

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()

Expiry & re-engagement

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

Table: `offer_agreement`

One current lawyer-reviewable draft per Offer. Source of truth: packages/db/src/schema/offerAgreement.ts. Mirrored as OfferAgreement in @zooly/types. AgreementAg writes this row from the Offer's commercial fields; it is not a copy of the chat.

Column	Type	Nullable	Default	Description
`offer_id`	text	NOT NULL	—	Primary key; FK to `offers.id` (cascade delete)
`document`	jsonb	NOT NULL	—	`AgreementDocument`: title, introduction, sections, parties (Talent / Brand names from the Offer)
`source_fingerprint`	text	NOT NULL	—	SHA-256 of the commercial Offer fields used to author the document. A mismatch means the draft is stale
`created_at`	timestamptz	NOT NULL	now()
`updated_at`	timestamptz	NOT NULL	now()

Indexes

offers_seller_idx on seller_account_id
offers_buyer_idx on buyer_account_id
offers_status_idx on status
offers_expires_at_idx on expires_at

Foreign Keys

seller_account_id → account.id
buyer_account_id → account.id
blast_offer_id → blast_offers.id
offer_agreement.offer_id → offers.id (cascade delete)

Migrations

Key migrations in chronological order (full list in packages/db/drizzle/):

0016_moaning_risque.sql — initial offers table
0017_offers_buyer_account.sql — replace buyer_email / buyer_name with buyer_account_id
amount_minor_unit_migration — switch all amounts to minor units (6eaa6062)
0072_complete_randall_flagg.sql — add counter_changes jsonb (3b6f6cbf, made idempotent in 4371456c)
0047_offer_agreement.sql — offer_agreement table (one current AgreementAg draft per Offer)

Generate new migrations with:

cd packages/db && npm run db:generate

Apply migrations with:

cd packages/db && npm run db:migrate

(Never use drizzle-kit push — only generated migrations, per project rules.)

Offers Architecture Offers API Reference

On This Page

Enums [object Object][object Object][object Object]Table: ,[object Object]Identity & participants Campaign & creative brief Pricing Status Delivery, payment & lifecycle Expiry & re-engagement Table: ,[object Object]Indexes Foreign Keys Migrations