Prolo — DB Schema (Quotes + RFQ)

↳ F1 · AWS architecture ↳ F2 · Data flow ↳ F4 · AI strategy ↳ API contract

PK primary key FK foreign key UQ unique enum NN not null. One RDS instance; the two sides meet only at rfq_line_matches.quote_line_id → line_items.

Quotes side — ingestion · pure extraction record, zero RFQ data

Relationships: quotes 1—∞ line_items 1—∞ quote_locations · quotes 1—∞ quote_locations · quotes ⟲ supersedes_id (revisions)

quotesparent · one row per extracted quote version

Column	Type	Key	Notes
id	uuid	PK	app-generated uuid7 (time-ordered)
data_tenancy_id	text	NN CHECK	tenancy (RLS); format `a.b.c`
message_id	text	NN UQ	Gmail id → idempotency
body_ref	text		S3 key: raw email body
attachment_ref	text		S3 key: attachment
quote_group_id	uuid	NN UQ	stable across all revisions
revision	int	NN	1, 2, 3 … (def 1)
is_latest	boolean	NN	exactly one true per group
supersedes_id	uuid	FK → quotes	version this replaces (null on v1)
vendor_identifier	text		brief: Vendor identifier
quote_number	text		supplier's quote # (revision key)
quote_date	date		optional
currency	char(3)		ISO-4217, optional
tax	numeric(14,2)		brief: Financial totals
shipping	numeric(14,2)
net	numeric(14,2)		validated: Σ = net
vendor_confidence	real		0..1
totals_confidence	real		0..1
status	quote_status	enum NN	lifecycle flag — no RFQ ref (def EXTRACTED)
attempts	int	NN	def 1
error_code / error_message	text		failure detail
created / updated	timestamptz	NN	def now()

Indexes / constraints: UQ(data_tenancy_id, message_id) dedupe · UQ(quote_group_id, revision) · ix(data_tenancy_id, status) · UNIQUE ix(quote_group_id) WHERE is_latest — one current version.

enum quote_status = EXTRACTED · NEEDS_REVIEW · MATCHED · SUBMITTED · SUPERSEDED · FAILED

line_itemschild · one row per pricing-table line

Column	Type	Key	Notes
id	uuid	PK
quote_id	uuid	FK → quotes NN	ON DELETE CASCADE
position	int	NN	preserves table order
description	text	NN	brief
quantity	numeric(14,4)	NN	brief
unit_of_measure	text	NN	brief
price	numeric(14,4)	NN	brief: per-unit price
confidence	real		0..1
description_embedding	vector(1024)		precomputed Stage 4 — semantic key for matching
created	timestamptz	NN	def now()

Indexes: ix(quote_id) · HNSW(description_embedding vector_cosine_ops) — kNN for matching.

quote_locationsprovenance · page + bbox per grounded value

Column	Type	Key	Notes
id	uuid	PK
quote_id	uuid	FK → quotes NN	CASCADE
line_item_id	uuid	FK → line_items	null = header/total field
field	text	NN	e.g. 'net', 'price', 'vendor_identifier'
source	text	NN	'attachment' \| 'body'
page	int
x0, y0, x1, y1	real		bounding box
relative_position	real
created	timestamptz	NN	def now()

Index: ix(quote_id).

Raw SQL DDL — quotes side

CREATE TYPE quote_status AS ENUM
  ('EXTRACTED','NEEDS_REVIEW','MATCHED','SUBMITTED','SUPERSEDED','FAILED');

CREATE TABLE quotes (
    id                uuid PRIMARY KEY,
    data_tenancy_id   text NOT NULL
        CONSTRAINT check_tenancy_format CHECK (data_tenancy_id ~ '^[^.]+\.[^.]+\..+$'),
    message_id        text NOT NULL,
    body_ref          text,  attachment_ref text,
    quote_group_id    uuid NOT NULL,
    revision          int  NOT NULL DEFAULT 1,
    is_latest         boolean NOT NULL DEFAULT true,
    supersedes_id     uuid REFERENCES quotes(id),
    vendor_identifier text, quote_number text, quote_date date, currency char(3),
    tax numeric(14,2), shipping numeric(14,2), net numeric(14,2),
    vendor_confidence real, totals_confidence real,
    status            quote_status NOT NULL DEFAULT 'EXTRACTED',
    attempts int NOT NULL DEFAULT 1, error_code text, error_message text,
    created timestamptz NOT NULL DEFAULT now(),
    updated timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_quote_per_message UNIQUE (data_tenancy_id, message_id),
    CONSTRAINT uq_group_revision    UNIQUE (quote_group_id, revision)
);
CREATE INDEX ix_quotes_tenancy_status ON quotes (data_tenancy_id, status);
CREATE UNIQUE INDEX uq_one_latest_per_group ON quotes (quote_group_id) WHERE is_latest;

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE line_items (
    id uuid PRIMARY KEY,
    quote_id uuid NOT NULL REFERENCES quotes(id) ON DELETE CASCADE,
    position int NOT NULL, description text NOT NULL,
    quantity numeric(14,4) NOT NULL, unit_of_measure text NOT NULL,
    price numeric(14,4) NOT NULL, confidence real,
    description_embedding vector(1024),
    created timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_line_items_quote ON line_items (quote_id);
CREATE INDEX ix_line_items_embedding ON line_items USING hnsw (description_embedding vector_cosine_ops);

CREATE TABLE quote_locations (
    id uuid PRIMARY KEY,
    quote_id uuid NOT NULL REFERENCES quotes(id) ON DELETE CASCADE,
    line_item_id uuid REFERENCES line_items(id) ON DELETE CASCADE,
    field text NOT NULL, source text NOT NULL, page int,
    x0 real, y0 real, x1 real, y1 real, relative_position real,
    created timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_locations_quote ON quote_locations (quote_id);

RFQ side — matching · read-model synced from core + the match association

Relationships: rfqs 1—∞ rfq_line_items 1—∞ rfq_line_matches ∞—1 line_items (quote side). The match lives here, not on the quote.

rfqsparent · read-model synced from the core system

Column	Type	Key	Notes
id	uuid	PK	uuid7
data_tenancy_id	text	NN CHECK	tenancy (RLS)
source_rfq_id	text	NN UQ	core system's RFQ id — sync key
customer_identifier	text
rfq_number	text
rfq_date	date
currency	char(3)		ISO-4217
synced_at	timestamptz	NN	read-model freshness watermark
created / updated	timestamptz	NN	def now()

Constraint / index: UQ(data_tenancy_id, source_rfq_id) upsert key on sync · ix(data_tenancy_id).

rfq_line_itemschild · requested line + embedding + match flag

Column	Type	Key	Notes
id	uuid	PK
rfq_id	uuid	FK → rfqs NN	CASCADE
position	int	NN	request order
description	text	NN
quantity	numeric(14,4)	NN
unit_of_measure	text	NN
description_embedding	vector(1024)		SAME model/dim as quote side, or kNN is meaningless
match_status	rfq_line_match_status	enum NN	roll-up of this line's edges (def UNMATCHED)
synced_at / created	timestamptz	NN	def now()

Indexes: ix(rfq_id) · HNSW(description_embedding vector_cosine_ops).

enum rfq_line_match_status = UNMATCHED · MATCHED · AMBIGUOUS

rfq_line_matchesmatch edges · RFQ line ← provider's quote line (many-to-many)

Column	Type	Key	Notes
id	uuid	PK
rfq_line_id	uuid	FK → rfq_line_items NN	the requested line
quote_line_id	uuid	FK → line_items NN	the two sides meet here (quote side)
quote_id	uuid	NN	denormalized parent quote — fast filter
provider	text	NN	vendor_identifier of the matched quote
match_confidence	real		kNN → rerank → LLM score
match_evidence	jsonb		neighbor scores + LLM rationale
edge_status	match_edge_status	enum NN	def PROPOSED
created	timestamptz	NN	def now()

Constraint / indexes: UQ(rfq_line_id, quote_line_id) one edge per pair · ix(rfq_line_id) · ix(quote_line_id) reverse · ix(quote_id).

enum match_edge_status = PROPOSED · CONFIRMED · REJECTED

Raw SQL DDL — RFQ side

CREATE TABLE rfqs (
    id uuid PRIMARY KEY,
    data_tenancy_id text NOT NULL
        CONSTRAINT check_tenancy_format CHECK (data_tenancy_id ~ '^[^.]+\.[^.]+\..+$'),
    source_rfq_id text NOT NULL, customer_identifier text,
    rfq_number text, rfq_date date, currency char(3),
    synced_at timestamptz NOT NULL DEFAULT now(),
    created timestamptz NOT NULL DEFAULT now(),
    updated timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_rfq_source UNIQUE (data_tenancy_id, source_rfq_id)
);
CREATE INDEX ix_rfqs_tenancy ON rfqs (data_tenancy_id);

CREATE TYPE rfq_line_match_status AS ENUM ('UNMATCHED','MATCHED','AMBIGUOUS');
CREATE TABLE rfq_line_items (
    id uuid PRIMARY KEY,
    rfq_id uuid NOT NULL REFERENCES rfqs(id) ON DELETE CASCADE,
    position int NOT NULL, description text NOT NULL,
    quantity numeric(14,4) NOT NULL, unit_of_measure text NOT NULL,
    description_embedding vector(1024),
    match_status rfq_line_match_status NOT NULL DEFAULT 'UNMATCHED',
    synced_at timestamptz NOT NULL DEFAULT now(),
    created timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_rfq_lines_rfq ON rfq_line_items (rfq_id);
CREATE INDEX ix_rfq_lines_embedding ON rfq_line_items USING hnsw (description_embedding vector_cosine_ops);

CREATE TYPE match_edge_status AS ENUM ('PROPOSED','CONFIRMED','REJECTED');
CREATE TABLE rfq_line_matches (
    id uuid PRIMARY KEY,
    rfq_line_id   uuid NOT NULL REFERENCES rfq_line_items(id) ON DELETE CASCADE,
    quote_line_id uuid NOT NULL REFERENCES line_items(id)     ON DELETE CASCADE,
    quote_id uuid NOT NULL, provider text NOT NULL,
    match_confidence real, match_evidence jsonb,
    edge_status match_edge_status NOT NULL DEFAULT 'PROPOSED',
    created timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_rfqline_quoteline UNIQUE (rfq_line_id, quote_line_id)
);
CREATE INDEX ix_matches_rfq_line   ON rfq_line_matches (rfq_line_id);
CREATE INDEX ix_matches_quote_line ON rfq_line_matches (quote_line_id);
CREATE INDEX ix_matches_quote      ON rfq_line_matches (quote_id);

Revisions — a revised quote is a new version, not an overwrite

Answering the brief's "update a quote if a revision is received": a revision is a new quotes row, appended, never an in-place edit — so full price history is preserved (auditable) and matching/submission always act on the current version.

quote_group_id is stable across every revision; revision counts up; is_latest flags the current one (a partial unique index enforces exactly one latest per group); supersedes_id links back.
On a new revision (one transaction): insert the new version (revision = prev+1, is_latest = true, supersedes_id = prior.id), then flip the prior row is_latest = false, status = SUPERSEDED.
Revision vs. duplicate — each revision is its own email → its own message_id, so dedupe is untouched: same message_id = duplicate (drop); same business key + new message_id = revision.
Queries — current view → WHERE is_latest; full history → WHERE quote_group_id = $1 ORDER BY revision.