Bid Library Import Guide

Reference guide for importing Q&A pairs from Word documents into Knowledge Hub.

1. Overview

The bid library import pipeline extracts question-and-answer pairs from .docx tables, deduplicates them, classifies them by domain, generates vector embeddings, and stores them in Supabase as content_items records with content_type = "q_a_pair" and platform = "extraction".

Pipeline stages (9 steps):

1
.docx files  -->  Extract tables  -->  Exact dedup (MD5)
2
             -->  Near-duplicate detection (flagged, not removed)
3
             -->  Keyword classification (7 categories)
4
             -->  Quality validation (empty/fragment detection)
5
             -->  Idempotency check (skip existing records)
6
             -->  Embedding generation (OpenAI text-embedding-3-large @ 1024 dims)
7
             -->  Supabase storage (service_role, bypasses RLS)

When to use:

First import for a new client’s bid library documents
When updated or additional bid library documents are received
After re-exporting corrected documents from the original authors

Source files (scripts/):

File	Purpose
`import_bid_library.py`	CLI orchestrator — chains all stages
`extract_docx_tables.py`	Table format detection, header mapping, Q&A extraction
`dedup.py`	Exact dedup (MD5) and near-duplicate detection (SequenceMatcher)
`keyword_classifier.py`	7-category keyword-based classification
`kb_pipeline/embed.py`	OpenAI embedding generation
`kb_pipeline/store.py`	Supabase REST insert via service_role key
`kb_pipeline/config.py`	Environment loading, model constants, pricing

2. Document Format Requirements

Supported file types

.docx only (Word Open XML)
.pdf and .xlsx are not supported
Temporary files (names starting with ~) are automatically skipped

Supported table formats

The extraction script detects four table layout patterns, plus a positional fallback for tables with empty or unrecognised headers.

Pattern A: Audit 6-column

Used by the “2026 Audit” series of documents.

No	Section	Question	Standard Response	Advanced Response	Notes

Pattern B: DRAFT 5-column

Used by the “DRAFT 2026” series of documents. No advanced response column.

No	Section	Question	Standard Response	Notes

Pattern C: Numbered 6-column

Alternative ordering where the section column appears after the answers.

No	Question	Standard Answer	Advanced Answer	Section	Notes

Positional fallback

When all header cells are empty or contain unrecognised text, the script falls back to positional assignment based on column count:

5 columns: column 0 = question, column 1 = standard answer, no advanced
6+ columns: column 0 = question, column 1 = standard answer, column 2 = advanced answer

In positional mode, the first row is treated as data (not a header row). In all other modes, the first row is consumed as a header row and data starts from row 2.

Recognised header variants (full _HEADER_MAP)

The script normalises headers by stripping whitespace, lowercasing, and removing trailing punctuation before matching. The complete set of recognised variants:

Question columns (mapped to question):

question, questions, query, requirement, requirements, suggested questions

Standard response columns (mapped to standard):

standard response, standard answer, standard, response, answer, answer for standard audit system, answer for standard audits, standard configuration answer

Advanced response columns (mapped to advanced):

advanced response, advanced answer, advanced, enhanced response, enhanced answer, answer for advanced audits, advanced audits answer

Section columns (mapped to section):

section, category, topic, area

Number columns (mapped to number):

no, no., #, number, ref, id

Notes columns (mapped to notes):

notes, comments, note, comment

Empty header inference

When column 0 is detected as question but subsequent columns have empty headers, the script infers:

First empty column after question = standard
Second empty column after question = advanced

This handles Audit template files where the header row has “Question” in column 0 but blank cells for the answer columns.

Section name resolution

Section names come from two sources, in priority order:

Column value: If the table has a section column and the cell for that row is non-empty, the cell value is used
Document headings: The script tracks Heading 1/2/3 styles as it walks through the document body. The most recently encountered heading is assigned as the section name for tables that follow it

Minimum viable table

At least 2 rows (1 header + 1 data row), or 1 row in positional fallback mode
A detectable question column (by header or position)
A detectable standard answer column (by header or position)
Non-Q&A tables (e.g., formatting tables, cover pages) are silently skipped

3. Running the Import

Prerequisites

Python dependencies installed:
Terminal window
```
1
pip install -r requirements.txt
```
Key packages: python-docx (extraction), openai (embeddings)
Environment variables in .env at the project root:
```
1
SUPABASE_URL=https://rovrymhhffssilaftdwd.supabase.co
2
SUPABASE_SECRET_KEY=<service_role key>
3
OPENAI_API_KEY=sk-...
```
The pipeline uses SUPABASE_SECRET_KEY (service_role), not the anon key. This bypasses RLS, which is required for pipeline inserts.
Documents placed in a directory (typically .planning/client-documentation/)

Dry run (recommended first step)

Run extraction, dedup, and classification without generating embeddings or writing to Supabase:

1
PYTHONUNBUFFERED=1 python3 scripts/import_bid_library.py \
2
  .planning/client-documentation/ \
3
  --dry-run

What to look for in dry run output:

[1/9] — Correct number of .docx files found (the .pdf is ignored)
[2/9] — Per-file extraction counts. Non-Q&A documents (e.g., “Sector-Intelligence-Brief”) should show 0 pairs — this is correct
[3/9] — Exact duplicates removed (questions repeated across Audit and DRAFT versions of the same document)
[4/9] — Near-duplicate candidates flagged for review. These are NOT removed automatically
[5/9] — Classification breakdown across the 7 categories. Check for a reasonable distribution and note the unclassified count
[6/9] — Quality validation. Empty and fragment items are flagged with warnings but still imported. Review the quality report at the end

Full import

1
PYTHONUNBUFFERED=1 python3 scripts/import_bid_library.py \
2
  .planning/client-documentation/ \
3
  --batch-name "initial-bid-library-2026"

Options:

Flag	Default	Purpose
`--dry-run`	off	Extract + dedup + classify + validate only, no embed/store
`--skip-embed`	off	Store records without generating embeddings
`--near-dedup-threshold`	`0.85`	Similarity threshold for near-duplicate detection
`--batch-name`	auto (`bid-library-YYYYMMDD-HHMMSS`)	Label for this import batch (stored in metadata)
`--force`	off	Skip idempotency check — import even if matching records already exist

Timing estimate: For ~180 unique Q&A pairs, expect roughly 2-3 minutes (dominated by embedding API calls at ~10 pairs per progress update).

Verification queries

After a full import, verify the results using Supabase MCP or direct SQL:

1
-- Count imported items
2
SELECT COUNT(*)
3
FROM content_items
4
WHERE content_type = 'q_a_pair'
5
  AND platform = 'extraction';
6

7
-- Classification breakdown
8
SELECT primary_domain, COUNT(*)
9
FROM content_items
10
WHERE content_type = 'q_a_pair'
11
GROUP BY primary_domain
12
ORDER BY count DESC;
13

14
-- Check a specific batch
15
SELECT COUNT(*), MIN(created_at), MAX(created_at)
16
FROM content_items
17
WHERE metadata->>'import_batch' = 'initial-bid-library-2026';
18

19
-- Items missing embeddings
20
SELECT COUNT(*)
21
FROM content_items
22
WHERE content_type = 'q_a_pair'
23
  AND embedding IS NULL;
24

25
-- Spot-check: view first 5 items
26
SELECT id, title, primary_domain, primary_subtopic,
27
       classification_confidence, metadata->>'source_file' AS source
28
FROM content_items
29
WHERE content_type = 'q_a_pair'
30
ORDER BY created_at DESC
31
LIMIT 5;

4. Gotchas

Non-Q&A documents return 0 pairs

Documents like “Sector-Intelligence-Brief-Liam-Final.docx” do not contain Q&A tables. The extraction script correctly returns 0 pairs for these — this is expected behaviour, not an error.

Near-duplicates across Audit and DRAFT versions

The same Q&A content often appears in both the “2026 Audit” and “DRAFT 2026” versions of a document. Exact duplicates (identical question text after normalisation) are removed automatically. Near-duplicates (similar but not identical wording) are flagged for manual review but not removed. Both versions are stored.

The 207 vs 205 discrepancy

The source documents contain 207 table rows that look like Q&A entries, but 2 rows have empty question cells (formatting/spacing rows in the original Word documents). These are correctly skipped, resulting in 205 extracted pairs before dedup.

Empty rows in source documents

Rows where the question cell is empty after stripping whitespace are silently skipped. This handles formatting rows, section dividers, and other non-content rows in the source tables.

Classification accuracy

The keyword-based classifier achieves approximately 93% classification rate. Roughly 13 out of 179 unique pairs may remain unclassified — these are pairs whose question and answer text does not contain enough keyword matches for any of the 7 categories. Unclassified pairs are still stored and searchable; they simply have empty primary_domain and primary_subtopic fields.

PYTHONUNBUFFERED=1

Always prefix the command with PYTHONUNBUFFERED=1 when running the script in the background or monitoring output in real time. Without this, Python buffers stdout and progress updates are invisible until the script completes.

Supabase REST PATCH silent no-op

If you attempt to update a record by UUID and the UUID does not match any row, Supabase returns 200 OK with 0 rows affected. This is a Supabase REST API behaviour, not a bug. Always verify updates by re-querying.

Service role key required

The pipeline uses SUPABASE_SECRET_KEY (service_role) to bypass RLS. The anon key will fail with permission errors because the pipeline does not authenticate as a Supabase Auth user.

5. Content Format Spec

Requirements for new documents

For the pipeline to extract Q&A pairs from a new .docx file:

File format: .docx (Word Open XML). Not .doc, .pdf, or .xlsx
Table structure: At least one table with:
- A header row containing recognised column names (or all-empty headers for positional fallback)
- One or more data rows below the header
- A question column (required)
- A standard answer column (required)
- An advanced answer column (optional)
Cell content: Plain text in table cells. Paragraph breaks within a cell are preserved (joined with \n). Formatting (bold, italic, etc.) is stripped — only text content is extracted.

Column naming conventions

Use any of the recognised header variants listed in section 2. The most reliable options are:

Question: Question or Requirement
Standard answer: Standard Response or Standard Answer
Advanced answer: Advanced Response or Advanced Answer (if applicable)
Section: Section or Category
Number: No or #
Notes: Notes or Comments

Headers are case-insensitive and trailing punctuation is stripped. Question:, QUESTION, and question all resolve to the same canonical name.

Adding new header variants

If a new document uses header text that is not currently recognised (e.g., “Tender Question” or “Supplier Response”), add it to the _HEADER_MAP dictionary in scripts/extract_docx_tables.py:

1
_HEADER_MAP = {
2
    # ... existing entries ...
3
    # Add new variants:
4
    "tender question": "question",
5
    "supplier response": "standard",
6
}

The key is the lowercased, stripped header text. The value is one of the canonical names: question, standard, advanced, section, number, or notes.

How records are constructed

Each Q&A pair becomes a content_items record with:

Field	Value
`title`	Question text, truncated at word boundary near 120 chars (with `...` suffix)
`content`	`Q: {question}\n\n{standard}\n{advanced}` — includes question text for search
`content_type`	`q_a_pair`
`platform`	`extraction`
`primary_domain`	Keyword classifier result (e.g., `security`)
`primary_subtopic`	Keyword classifier result (e.g., `data-protection`)
`classification_confidence`	0.0—1.0, based on keyword score distribution
`ai_summary`	First ~200 chars of answer text, truncated at word boundary
`ai_keywords`	`[primary_domain, section-name-slug]`
`metadata.source_file`	Original `.docx` filename
`metadata.section_name`	Section heading from the document
`metadata.table_index`	Which table in the document (0-indexed)
`metadata.row_index`	Which row in the table (0-indexed from data start)
`metadata.has_standard`	`true`/`false`
`metadata.has_advanced`	`true`/`false`
`metadata.import_batch`	Batch name (auto-generated or `--batch-name` value)

Embedding input: The embedding combines the question text (title), first 500 chars of combined answer text (ai_summary slot), and the full combined answer text (both standard and advanced). This ensures semantic search finds Q&A pairs by both question and answer content.

Truncation: Title and ai_summary use word-boundary-aware truncation. If the text exceeds the limit, it is cut at the last space before the limit (provided that space is within 70% of the limit), followed by .... This avoids mid-word cuts like "What is your approac..." in favour of "What is your approach to...".

6. Post-Import Steps

Verification checklist

Count check: Run the count query from section 3. The number of stored items should match the “Unique classified” count from the import summary.
Classification breakdown: Run the classification query. Expect a reasonable distribution across categories. A high unclassified count (more than ~10%) may indicate new question patterns that need keyword additions.
Embedding check: Run the missing-embeddings query. All items should have embeddings unless --skip-embed was used.
Search test: Run a semantic search to verify embeddings are working:
Terminal window
```
1
bun run scripts/kb-search.ts "data protection policy" --limit 5
```
Q&A pairs about GDPR and data protection should appear in results.
Spot check: Review 5—10 items via the browse page or direct SQL to verify titles, content, and classifications look correct.

Near-duplicate resolution

The import flags near-duplicate pairs but stores all of them. After import:

Review the near-duplicate candidates from the import output
For each pair, decide whether both versions should be kept (different nuance) or one should be removed
Remove unwanted duplicates via the content review page (when built) or direct SQL:
```
1
DELETE FROM content_items WHERE id = '<uuid-of-duplicate>';
```

Ongoing review

Use the browse page with filter content_type = q_a_pair to review imported items
Check items with classification_confidence < 0.4 for possible misclassification
Items with empty primary_domain may benefit from manual classification or keyword additions to the classifier

7. Troubleshooting

Common errors

Error	Cause	Fix
`ModuleNotFoundError: No module named 'docx'`	`python-docx` not installed	`pip install python-docx`
`ModuleNotFoundError: No module named 'openai'`	OpenAI package not installed	`pip install openai`
`ERROR: Could not import embedding module`	Missing OpenAI key or package	Check `OPENAI_API_KEY` in `.env`
`ERROR: Could not import store module`	Missing Supabase config	Check `SUPABASE_URL` and `SUPABASE_SECRET_KEY` in `.env`
`No .docx files found`	Wrong directory path or only non-docx files	Verify the directory path and file extensions
`Store error for pair #N: ...`	Supabase insert failure	Check error message — usually a constraint violation or network issue
`0 pairs` for a document you expected to have Q&A	Unrecognised table format	Check headers against the `_HEADER_MAP` or add new variants
Progress output not visible	Python output buffering	Prefix command with `PYTHONUNBUFFERED=1`

Is the import idempotent?

Yes (by default). The import script checks whether each Q&A pair already exists in Supabase by matching the first 80 characters of the question text against the title field of existing q_a_pair records. Existing records are skipped and reported in the output.

To override the idempotency check and force re-import of all records (e.g., after updating the content format), use --force:

1
PYTHONUNBUFFERED=1 python3 scripts/import_bid_library.py \
2
  .planning/client-documentation/ \
3
  --batch-name "re-import-2026" \
4
  --force

Note: --force does not delete existing records. If you want to replace records rather than add duplicates, delete the previous batch first (see below), then re-import.

Deleting a bad import batch

To remove all records from a specific import batch:

1
-- Check what would be deleted
2
SELECT COUNT(*), metadata->>'import_batch' AS batch
3
FROM content_items
4
WHERE metadata->>'import_batch' = 'initial-bid-library-2026'
5
GROUP BY batch;
6

7
-- Delete the batch
8
DELETE FROM content_items
9
WHERE metadata->>'import_batch' = 'initial-bid-library-2026';

The import_batch metadata field is specifically designed for this purpose. Every record from a single import run shares the same batch name.

Re-running after a partial failure

If the import fails partway through (e.g., network error during embedding or storage), simply re-run the same command. The idempotency check will skip records that were already stored and only import the remaining ones.

If you prefer a clean re-import:

Note the batch name from the import output
Delete any partially stored records for that batch (see above)
Re-run the full import with the same --batch-name

Adding classification coverage

If too many pairs are unclassified, add keywords to the CATEGORY_KEYWORDS dictionary in scripts/keyword_classifier.py. Each category has subtopics, and each subtopic has a list of keywords:

1
CATEGORY_KEYWORDS = {
2
    "security": {
3
        "data-protection": [
4
            "data protection", "gdpr", ...
5
            "new keyword here",  # add new keywords
6
        ],
7
    },
8
    # ...
9
}

Multi-word phrases are weighted more heavily in scoring (2x for 2-word phrases, 3x for 3+ word phrases, capped at 3x).

Checking the 7 classification categories

The keyword classifier assigns one of these categories as primary_domain:

Category	Covers
`security`	Data protection, cyber security, encryption, access control, ISO 27001
`compliance`	Standards (ISO 9001, Cyber Essentials), regulatory, audit, certification
`implementation`	Deployment, migration, onboarding, integration
`support`	SLAs, helpdesk, maintenance, incident management
`corporate`	Company info, financials, insurance, references, staffing
`product-feature`	Functionality, technical architecture, reporting, usability
`methodology`	Approach, project management, quality, delivery

Each category has 3—5 subtopics. The classifier assigns both a primary_domain and primary_subtopic, plus optional secondary_domain and secondary_subtopic when multiple categories score above zero.

Appendix: Current client documents

As of the initial import, the following documents are in .planning/client-documentation/:

Document	Type	Expected pairs
2026 Audit - … FAQs.docx	Audit 6-col	~30
2026 Audit - … Functionality.docx	Audit 6-col	~25
2026 Audit - … Implementation & Support.docx	Audit 6-col	~45
2026 Audit - … Security & Compliance.docx	Audit 6-col	~50
DRAFT 2026 … Implementation & Support.docx	DRAFT 5-col	~45
DRAFT 2026 … FAQs - Copy (1).docx	DRAFT 5-col	~30
DRAFT 2026 … Security and Compliance - Copy.docx	DRAFT 5-col	~50
Sector-Intelligence-Brief-Liam-Final.docx	Non-Q&A	0 (correct)
Telehouse-South-Fact-Sheet-2025.pdf	PDF (skipped)	n/a

The Audit and DRAFT versions of the same topic (e.g., “Security & Compliance”) contain overlapping content. Exact duplicates are removed; near-duplicates are flagged for review.