nick-doc/02 - Data Models/PurchaseRequest.md

title, tags, aliases

title	tags	aliases
PurchaseRequest	data-model postgres drizzle	Purchase Request Buy Request IPurchaseRequest

PurchaseRequest

Last updated: 2026-06-06 — MongoDB/Mongoose fully removed; PostgreSQL + Drizzle ORM is the only database layer (backend v2.9.12). Removed dual-write/Mongo sections; updated IDs to UUID; clarified deliveryDate nesting and paymentId absence.

The central buyer-side document. A PurchaseRequest captures what a buyer wants to acquire (physical product, digital product, service, or consultation), the budget envelope, urgency, delivery details, and the entire lifecycle from creation through payment, delivery, and completion. Sellers respond by attaching SellerOffer documents; the buyer accepts one, a Payment is opened, and delivery is verified by a 6-digit code.

[!note] Sources PostgreSQL schema (Drizzle): backend/src/db/schema/purchaseRequest.ts Mongoose model removed in v2.9.12 — src/models/ directory deleted.

Migration Status

Complete. MongoDB and Mongoose are fully removed from the backend runtime. PostgreSQL + Drizzle ORM is the only database layer. No dual-write mode; all domain stores use Postgres exclusively. 19 migrations landed (0000–0019), 32 tables total.

---

PostgreSQL Schema (Drizzle)

Source: backend/src/db/schema/purchaseRequest.ts

The PG model normalises prior embedded subdocuments into 7 tables. The offers[] array is not present; SellerOffer holds purchase_request_id as a back-reference.

ID note: All primary keys are PostgreSQL UUIDs (.id field, string). There is no _id / ObjectId field in runtime code. A legacy_object_id column exists on each table solely for backfill traceability — do not use it in application logic.

paymentId note: PurchaseRequest does not have a top-level paymentId field. Payment records reference the purchase request via Payment.purchaseRequestId; to find the payment for a request, query Payment WHERE purchase_request_id = ?.

preferredSellerIds note: Stored in the purchase_request_preferred_sellers junction table as UUID seller_id references to users(id) (specifically users.pgId). They are UUID strings, not populated document objects.

deliveryDate note: deliveryDate (and all other delivery logistics) are nested inside the purchase_request_delivery_info child table (delivery_date column). There is no top-level deliveryDate field on purchase_requests. Use updatePurchaseRequestDeliveryInfo() to update it.

Enums (PG-level)

Enum name	Values
purchase_request_status	pending_payment, pending, active, received_offers, in_negotiation, payment, processing, delivery, delivered, confirming, completed, cancelled, seller_paid
product_type	physical_product, digital_product, service, consultation
request_urgency	low, medium, high, urgent
delivery_type	physical, online
service_session_type	online, in_person, hybrid
pr_metadata_source	manual, template, api
budget_currency	USD, EUR, IRR, USDT, USDC

Table: purchase_requests (main)

Column	PG type	Nullable	Default	Notes
id	uuid PK	no	gen_random_uuid()	Application primary key — use this everywhere
legacy_object_id	text	yes	—	24-char former Mongo ObjectId; partial-unique index; traceability only
buyer_id	uuid	no	—	FK → users(id)
category_id	uuid	no	—	FK → categories(id)
title	varchar(200)	no	—
description	text	no	—
product_type	enum	yes	physical_product
product_link	varchar(2000)	yes	—	CHECK: ^https?://.+
size	varchar(100)	yes	—
color	varchar(100)	yes	—
brand	varchar(100)	yes	—
quantity	integer	yes	1	CHECK ≥ 1
budget_min	numeric(38,18)	yes	—	CHECK ≥ 0
budget_max	numeric(38,18)	yes	—	CHECK ≥ 0
budget_currency	enum	yes	USDT
urgency	enum	no	medium
status	enum	no	pending	13-value escrow-critical enum
is_public	boolean	yes	true
tags	text[]	yes	'{}'
attachments	text[]	yes	'{}'
selected_offer_id	uuid	yes	—	FK → seller_offers(id)
rating	smallint	yes	—	CHECK 1–5 or NULL
feedback	text	yes	—	CHECK length ≤ 1000 or NULL
delivery_confirmed	boolean	yes	false
delivery_confirmed_at	timestamptz	yes	—
dispute_raised	boolean	no	false
dispute_raised_at	timestamptz	yes	—
dispute_resolved	boolean	no	false
dispute_resolved_at	timestamptz	yes	—
dispute_hold_reason	text	yes	—
hold_until	timestamptz	yes	—	Partial index WHERE NOT NULL
metadata_source	enum	yes	manual
metadata_template_id	varchar(100)	yes	—
metadata_version	varchar(50)	yes	—
created_at	timestamptz	no	now()
updated_at	timestamptz	no	now()

Indexes on purchase_requests:

Index	Type	Columns / condition
idx_pr_buyer_id	btree	buyer_id
idx_pr_category_id	btree	category_id
idx_pr_product_type	btree	product_type
idx_pr_status	btree	status
idx_pr_created_at	btree	created_at
idx_pr_urgency	btree	urgency
purchase_requests_legacy_object_id_uq	partial-unique	legacy_object_id WHERE NOT NULL
idx_pr_product_type_status	btree	(product_type, status)
idx_pr_category_product_type	btree	(category_id, product_type)
idx_pr_hold_until	partial btree	hold_until WHERE NOT NULL
idx_pr_dispute_raised	partial btree	dispute_raised WHERE dispute_raised = true

CHECK constraints on purchase_requests:

Name	Expression
pr_rating_ck	rating IS NULL OR (rating >= 1 AND rating <= 5)
pr_feedback_len_ck	feedback IS NULL OR length(feedback) <= 1000
pr_quantity_min_ck	quantity IS NULL OR quantity >= 1
pr_budget_min_ck	budget_min IS NULL OR budget_min >= 0
pr_budget_max_ck	budget_max IS NULL OR budget_max >= 0
pr_product_link_ck	product_link IS NULL OR product_link ~ '^https?://.+'

---

Table: purchase_request_delivery_info (1:1)

Child of purchase_requests. Holds all delivery logistics. deliveryDate and all delivery timestamps live here, not on the parent table. Update via updatePurchaseRequestDeliveryInfo().

Column	PG type	Nullable	Default	Notes
id	uuid PK	no	random
legacy_object_id	text	yes	—	Parent PR's legacy ObjectId for traceability
purchase_request_id	uuid UNIQUE	no	—	FK → purchase_requests(id) CASCADE
delivery_type	enum	no	physical
address	varchar(500)	yes	—
preferred_date	timestamptz	yes	—
notes	text	yes	—
email	varchar(255)	yes	—	CHECK: email regex or NULL
delivery_date_time	timestamptz	yes	—
delivery_date	date	yes	—	Confirmed delivery date (nested inside deliveryInfo, not top-level on PurchaseRequest)
shipped_at	timestamptz	yes	—
delivery_code	varchar(6)	yes	—	CHECK: length = 6 or NULL
delivery_code_generated_at	timestamptz	yes	—
delivery_code_expires_at	timestamptz	yes	—
delivery_code_used	boolean	yes	false
delivery_code_used_at	timestamptz	yes	—
delivery_code_used_by	uuid	yes	—	FK → users(id)
delivered_at	timestamptz	yes	—
created_at	timestamptz	no	now()
updated_at	timestamptz	no	now()

Indexes: idx_pr_delivery_info_pr_id on purchase_request_id

CHECK constraints: pr_di_delivery_code_len_ck (length = 6 or NULL), pr_di_email_fmt_ck (email regex)

---

Table: purchase_request_delivery_address (1:1 under delivery_info)

Column	PG type	Nullable	Notes
id	uuid PK	no
legacy_object_id	text	yes
delivery_info_id	uuid UNIQUE	no	FK → purchase_request_delivery_info(id) CASCADE
recipient_name	varchar(200)	yes
phone_number	varchar(20)	yes
full_address	text	yes
address_type	varchar(50)	yes	e.g. Home / Office

Index: idx_pr_delivery_addr_info_id on delivery_info_id

---

Table: purchase_request_seller_delivery_info (1:1 under delivery_info)

Column	PG type	Nullable	Default	Notes
id	uuid PK	no	random
legacy_object_id	text	yes	—
delivery_info_id	uuid UNIQUE	no	—	FK → purchase_request_delivery_info(id) CASCADE
estimated_delivery_date	timestamptz	yes	—
estimated_delivery_time	varchar(50)	yes	—
tracking_number	varchar(100)	yes	—
delivery_notes	text	yes	—
shipping_method	varchar(100)	yes	—
download_link	varchar(2000)	yes	—
digital_files	text[]	yes	'{}'
created_at	timestamptz	no	now()
updated_at	timestamptz	no	now()

Index: idx_pr_seller_di_info_id on delivery_info_id

---

Table: delivery_attempts (1:N under delivery_info)

Append-only audit log of code-entry attempts.

Column	PG type	Nullable	Default	Notes
id	uuid PK	no	random
delivery_info_id	uuid	no	—	FK → purchase_request_delivery_info(id) CASCADE
seller_id	uuid	no	—	FK → users(id)
attempted_at	timestamptz	no	now()
success	boolean	no	—
code	varchar(100)	yes	—	Only stored on successful attempts

Indexes: idx_delivery_attempts_info_id, idx_delivery_attempts_seller_id, idx_delivery_attempts_success

---

Table: purchase_request_service_info (1:1)

Only populated for service / consultation product types.

Column	PG type	Nullable	Default	Notes
id	uuid PK	no	random
legacy_object_id	text	yes	—
purchase_request_id	uuid UNIQUE	no	—	FK → purchase_requests(id) CASCADE
duration	numeric(5,2)	yes	—	CHECK ≥ 0.5
session_type	enum	yes	—	online / in_person / hybrid
location	varchar(200)	yes	—
requirements	text[]	yes	'{}'
created_at	timestamptz	no	now()
updated_at	timestamptz	no	now()

Index: idx_pr_service_info_pr_id CHECK: pr_si_duration_min_ck (duration IS NULL OR duration >= 0.5)

---

Table: purchase_request_specifications (1:N)

Queryable {key, value, label} specs.

Column	PG type	Nullable	Default	Notes
id	uuid PK	no	random
purchase_request_id	uuid	no	—	FK → purchase_requests(id) CASCADE
key	varchar(255)	no	—
value	text	no	—
label	varchar(255)	yes	—
position	integer	no	0	Preserves array order for round-trip fidelity

Indexes: idx_pr_specs_pr_id, idx_pr_specs_key, partial-unique purchase_request_specifications_request_key_uq on (purchase_request_id, key)

---

Table: purchase_request_preferred_sellers (N:M junction)

Stores the buyer's targeted seller list. Each row is a UUID reference to users(id) (i.e. users.pgId). There are no populated document objects — only UUID strings.

Column	PG type	Nullable	Notes
purchase_request_id	uuid	no	FK → purchase_requests(id)
seller_id	uuid	no	FK → users(id) — matches users.pgId

Indexes: composite unique idx_pr_preferred_sellers_uq on (purchase_request_id, seller_id); idx_pr_preferred_sellers_seller_id on seller_id

---

Design Notes

• offers[] not present in PG. Query SellerOffer WHERE purchase_request_id = ? instead.
• paymentId not present. PurchaseRequest has no top-level paymentId. Payments reference the request; query Payment WHERE purchase_request_id = ?.
• deliveryDate is nested. delivery_date lives in purchase_request_delivery_info, not on the main purchase_requests table. Update it via updatePurchaseRequestDeliveryInfo().
• Money scale. budget_min / budget_max use numeric(38,18) (project-wide crypto convention) for consistency with Payment and FundsLedgerEntry.
• tags / attachments stored as text[] (not JSONB) to enable ANY() array queries without a child table.
• legacy_object_id on every table uses a partial-unique index (WHERE NOT NULL) for idempotent backfill upserts. Do not use in application logic.
• Dispute / escrow hold fields (dispute_raised, dispute_raised_at, dispute_resolved, dispute_resolved_at, dispute_hold_reason, hold_until) are escrow-critical and present on the main purchase_requests table.

---

Status enum — all valid values

pending_payment · pending · active · received_offers · in_negotiation · payment · processing · delivery · delivered · confirming · completed · seller_paid · cancelled

Note: finalized and archived are not valid status values. Using either would cause a validation error.

---

Relationships

• References: User (buyer_id, preferred_sellers[].seller_id — UUIDs, delivery_code_used_by, delivery_attempts[].seller_id), Category (category_id), SellerOffer (selected_offer_id).
• Referenced by: SellerOffer (purchase_request_id), Payment (purchase_request_id), Dispute (purchase_request_id), Chat (relatedTo.id when relatedTo.type === 'PurchaseRequest'), Review (purchase_request_id).

Template Checkout Mapping

When a buyer converts a RequestTemplate, the seller's template remains authoritative for delivery mode:

• physical templates require a buyer billing/delivery address in checkout. The generated request stores both deliveryInfo.address and deliveryInfo.deliveryAddress.
• online templates require a buyer email in checkout. The generated request stores it in deliveryInfo.email.
• Mixed carts can produce multiple requests with different delivery modes; the checkout UI asks for the union of required buyer details.

State Transitions

stateDiagram-v2
    [*] --> pending_payment
    [*] --> pending
    pending_payment --> pending : payment confirmed
    pending --> active : published
    active --> received_offers : first offer
    received_offers --> in_negotiation : buyer engages
    in_negotiation --> payment : offer accepted
    payment --> processing : payment captured
    processing --> delivery : shipped
    delivery --> delivered : handed over
    delivered --> confirming : code redeemed
    confirming --> completed : buyer confirms
    completed --> seller_paid : payout released
    pending --> cancelled
    active --> cancelled
    received_offers --> cancelled
    in_negotiation --> cancelled
    completed --> [*]
    seller_paid --> [*]
    cancelled --> [*]

Common Queries

// Buyer's open requests (Drizzle)
db.select().from(purchaseRequests)
  .where(and(eq(purchaseRequests.buyerId, buyerId), inArray(purchaseRequests.status, ['pending', 'active', 'received_offers'])));

// Public marketplace feed
db.select().from(purchaseRequests)
  .where(and(eq(purchaseRequests.isPublic, true), eq(purchaseRequests.status, 'active')))
  .orderBy(desc(purchaseRequests.createdAt));

// Sellers' eligible queue
db.select().from(purchaseRequests)
  .where(and(eq(purchaseRequests.productType, productType), eq(purchaseRequests.status, 'active'), eq(purchaseRequests.categoryId, categoryId)));

// Offers for a request
// SELECT * FROM seller_offers WHERE purchase_request_id = $1;

// Payment for a request (no paymentId on PurchaseRequest — query payments table)
// SELECT * FROM payments WHERE purchase_request_id = $1;

// Delivery info including deliveryDate
// SELECT * FROM purchase_request_delivery_info WHERE purchase_request_id = $1;

// Requests with live escrow hold
// SELECT * FROM purchase_requests WHERE hold_until IS NOT NULL AND hold_until > now();

// Preferred sellers (UUID strings)
// SELECT seller_id FROM purchase_request_preferred_sellers WHERE purchase_request_id = $1;

Related: SellerOffer, Payment, Chat, Dispute, Review, RequestTemplate, Category.