Google Sheets Schema — International Purchasing

State store for the International Purchasing pipeline. One Google Sheets workbook with five tabs.

Conventions

• Primary key column is the first column of every tab and must be unique.
• Foreign keys reference the primary key of another tab.
• Timestamps are stored as ISO-8601 strings (YYYY-MM-DDTHH:mm:ss.sssZ).
• Lists (e.g. certifications, countries) are stored as JSON strings: ["OEKO-TEX","GOTS"].
• Status enums use lowercase snake_case strings.
• Append-only: rows are inserted on create; subsequent updates rewrite the same row by primary key. Never delete — use a status column for soft deletion.

Workbook setup

1. Create a new Google Sheets workbook.
2. Add the five tabs listed below, with the exact column headers in row 1.
3. Share the workbook with the Google account used by the n8n googleSheetsOAuth2Api credential.
4. The workbook ID (1mucA7L-CvAkBO_mQ_2KcbJ6Lj4KCDNxjRuxvDN4ABNA) is hardcoded in the three Google Sheets nodes of the workflow.

---

Tab: briefs

One row per submitted brief. Created by Stage 0; status is advanced by later stages.

Column	Type	Notes
brief_id	string	PK. ISO-8601 timestamp (also serves as creation time).
supply_type	string	Raw Material / Finished Product / Industrial Machinery.
category	string	raw_material / finished_product / machinery.
specification	string	Free-text technical spec.
certifications	json	JSON array, e.g. ["OEKO-TEX"].
priority_countries	json	JSON array, e.g. ["India","China"].
amount_range	string	Form option.
moq	string	Nullable.
incoterm	string	Any / FOB / CIF / EXW / DDP / FCA.
max_lead_time	string	Form option.
target_count	integer	Target supplier count.
notes	string	Free text.
status	enum	pending → data_entry_done → analyst_done → contactor_done → closed.
executed_at	string	ISO-8601, brief creation time.
last_updated_at	string	ISO-8601, set on every status change.

---

Tab: candidates

One row per discovered supplier candidate for a brief. Created by Stage 1; qualification_* and artifact_* columns are updated by Stage 2.

Column	Type	Notes
candidate_id	string	PK. UUID.
brief_id	string	FK → briefs.brief_id.
name	string	Listing title from the SERP. Often the product page title, not the manufacturer — enriched in Stage 2.
country	string	Priority country the search was scoped to.
website	string	Alibaba supplier / product URL.
email	string	Supplier contact. Not scrapeable — populated in Stage 3.
whatsapp	string	Supplier WhatsApp. Not scrapeable — populated in Stage 3 if disclosed.
certifications	json	JSON array, e.g. ["OEKO-TEX"]. Empty [] at discovery; enriched in Stage 2.
moq	string	Empty at discovery; enriched in Stage 2 or quoted in Stage 3.
lead_time	string	Empty at discovery; quoted in Stage 3.
indicative_price	string	Empty at discovery; quoted in Stage 3.
source	string	Scraper identifier, e.g. alibaba.
confidence_score	number	0.0 – 1.0, heuristic by SERP position (1.0 for the first hit, decays -0.05 per position, floor 0.1).
qualification_status	enum	pending / qualified / rejected. Default pending.
analyst_notes	string	Stage 1 stores the SERP snippet here. Stage 2 overwrites with the analyst’s notes.
artifact_drawing_status	enum	n/a / pending / approved / rejected. Machinery only.
artifact_sample_status	enum	n/a / requested / received / failed.
discovered_at	string	ISO-8601.
last_updated_at	string	ISO-8601.

Dedup key: brief_id + website (proposed). Currently no dedup is applied on append.

---

Tab: quotes

One row per quote thread initiated with a qualified candidate. Created and maintained by Stage 3.

Column	Type	Notes
quote_id	string	PK. UUID.
brief_id	string	FK → briefs.brief_id.
candidate_id	string	FK → candidates.candidate_id.
channel	enum	gmail / whatsapp.
thread_id	string	Gmail thread ID or Twilio conversation SID.
price	number	As quoted by supplier.
currency	string	ISO-4217 (e.g. USD, EUR).
incoterm	string	As quoted.
lead_time	string	As quoted.
payment_terms	string	As quoted.
price_normalized_usd	number	Computed for comparison in Stage 4.
last_message_at	string	ISO-8601.
status	enum	open / quoted / stalled / closed.
created_at	string	ISO-8601.
last_updated_at	string	ISO-8601.

---

Tab: selections

One row per brief recording the CPO’s final decision. Created by Stage 4.

Column	Type	Notes
selection_id	string	PK. UUID.
brief_id	string	FK → briefs.brief_id. Unique (one selection / brief).
winner_candidate_id	string	FK → candidates.candidate_id.
runner_up_candidate_ids	json	JSON array of candidate_ids.
final_price	number
final_currency	string	ISO-4217.
final_incoterm	string
rationale	string	Free text from FB.
decided_by	string	FB (CPO).
decided_at	string	ISO-8601.

---

Tab: logs

Append-only audit log of every external call made by the pipeline.

Column	Type	Notes
log_id	string	PK. UUID.
brief_id	string	FK → briefs.brief_id. Nullable for system events.
stage	enum	0 / 1 / 2 / 3 / 4.
action	string	e.g. search_duckduckgo, send_gmail, send_whatsapp.
payload_summary	string	Truncated JSON or human summary.
status	enum	ok / error.
error_message	string	Nullable.
created_at	string	ISO-8601.

---

Relationships

briefs (1) ──< candidates (N)
briefs (1) ──< quotes (N) >── (1) candidates
briefs (1) ──< selections (1)
briefs (1) ──< logs (N)