# RFQ/EOI — Data Model

This document defines the PostgreSQL entities for RFQ and EOI. It supports header-level and line-level awards, vendor login submissions, invitations, reminders, and audit.

Core Entities
- rfq_headers
  - rfq_id (uuid, PK)
  - type (text, e.g., RFQ or EOI)
  - rfq_number (text, unique per org and year per type)
  - org_id (uuid) — owning org
  - department_id (uuid, nullable)
  - title (text), category (text, nullable)
  - currency (text, nullable), incoterms (text, nullable)
  - summary_markdown (text, nullable), summary_html (text, nullable)
  - numbering_format (text) — stored snapshot used to generate rfq_number
  - publish_at (timestamptz, nullable), due_at (timestamptz)
  - status (text: draft, open, closed, awarded, archived, paused)
  - created_by (uuid), created_at (timestamptz), updated_at (timestamptz)
  - Indexes: (org_id, type, year_of_publish, rfq_number unique), (status, due_at)

- rfq_sections
  - rfq_section_id (uuid, PK), rfq_id (uuid, FK rfq_headers)
  - section_key (text), heading (text)
  - body_markdown (text), body_html (text)
  - sort_order (int), include_in_print (bool), include_in_email (bool)

- rfq_items (extend existing)
  - id (PK), rfq_id (uuid, FK rfq_headers)
  - description (text), quantity (numeric or int), uom (text)
  - required_specs (jsonb, nullable)
  - delivery_terms (text, nullable), delivery_location (text, nullable)
  - is_optional (bool default false)
  - item_attachments (jsonb, nullable)
  - Indexes: (rfq_id), GIN on required_specs

- rfq_invites
  - invite_id (uuid, PK), rfq_id (uuid, FK), vendor_id (uuid FK vendors)
  - contact_id (uuid FK vendor_contacts, nullable)
  - vendor_user_id (uuid FK users, nullable until activation)
  - channel (text: email or portal), status (text: invited, delivered, opened, responded, declined)
  - sent_at, delivered_at, opened_at, responded_at (timestamptz, nullable)
  - declined_reason (text, nullable)
  - reminder_count (int default 0), last_reminder_at (timestamptz, nullable)
  - Unique: (rfq_id, vendor_id)

- vendor_quotes (extend existing)
  - quote_id (uuid, PK)
  - rfq_id (uuid FK), invite_id (uuid FK rfq_invites), vendor_id (uuid FK)
  - currency (text), total_amount (numeric, nullable), tax_total (numeric, nullable)
  - delivery_days (int, nullable), valid_thru (date, nullable)
  - notes (text, nullable), attachments (jsonb, nullable)
  - revision_no (int default 1), submitted_at (timestamptz), status (text: draft, submitted, revised, withdrawn)
  - Indexes: (rfq_id, vendor_id, revision_no desc)

- vendor_quote_items (new)
  - quote_item_id (uuid, PK), quote_id (uuid FK vendor_quotes), rfq_item_id (int or uuid FK rfq_items)
  - is_alternate (bool default false), alt_label (text, nullable)
  - unit_price (numeric), tax (numeric, nullable), lead_time_days (int, nullable)
  - notes (text, nullable)
  - Unique: (quote_id, rfq_item_id, is_alternate, alt_label)

- rfq_events
  - event_id (uuid, PK), rfq_id (uuid), actor_id (uuid), actor_type (text: internal or vendor)
  - type (text: publish, invite_sent, opened, responded, reminder_sent, closed, awarded, addendum, qa_q, qa_a, reopened, paused, resumed)
  - payload (jsonb), created_at (timestamptz)
  - Indexes: (rfq_id, created_at), GIN on payload

- numbering_sequences
  - org_id (uuid), type (text), year (int), next_seq (int)
  - Unique: (org_id, type, year)

Awarding Model
- Header-level award: award records link rfq_id to vendor_id with scope header.
- Line-level award: award records link rfq_item_id to vendor_id, optional split ratios.
- Suggested table: rfq_awards (award_id uuid, rfq_id, scope text: header or item, rfq_item_id nullable, vendor_id, notes, created_at).

Relationships (summary)
- rfq_headers 1 to many rfq_sections
- rfq_headers 1 to many rfq_items
- rfq_headers 1 to many rfq_invites, each invite 1 to many vendor_quotes, each quote 1 to many vendor_quote_items
- rfq_headers 1 to many rfq_events
- rfq_headers 1 to many rfq_awards (header or item scope)

Constraints and Data Integrity
- Prevent submissions after due_at unless RFQ reopened.
- Enforce vendor_quotes.invite_id references rfq_invites of the same rfq_id.
- Cascade delete sections, items, invites, quotes only while draft; otherwise soft-delete.
- Maintain revision_no sequencing per rfq and vendor.

Performance Notes
- Use jsonb for flexible specs and attachments; add GIN indexes for frequent filters.
- Covering indexes for (status, due_at) on rfq_headers and (rfq_id, status) on rfq_invites.
- Consider materialized view for compare matrix for large datasets.

Open Items
- Decide int vs uuid PK for rfq_items for consistency.
- Align vendor_id and user_id types with existing models.
- Reuse central audit if present; otherwise implement rfq_events.