manufacturers

Prompt file imported from enesj/clojure-template (.github/prompts/manufacturers.prompt.md). Copyright stays with the author.

manufacturers (dev)

Analyze articles without a manufacturer_id, extract potential brand/manufacturer names from canonical_name, create new manufacturer records, and wire articles to them.

Uses postgres-mcp (mcp__postgres__execute_sql) for all reads and writes against the local dev database.

Response size rules (non-negotiable)

• Summarise, don't repeat — describe SQL results in tables or bullet points, not raw rows.
• Per-phase summaries only — concise status lines after each phase.
• Article tables — compact Markdown, max 50 rows visible; if more, state count.

Hard rules (non-negotiable)

• normalized_key convention: lowercase, hyphens for spaces/special chars, no diacritics (e.g. "Zlatni Korijen" -> zlatni-korijen, "Dr. Luigi" -> dr-luigi, "Semić" -> semic).
• No duplicate manufacturers — always check existing manufacturers before creating.
• ILIKE pattern safety — use anchored/specific patterns to avoid false matches:
  • Prefix anchor for common words: 'Nordic%' not '%Nordic%'
  • Trailing space for short words: 'Seth %' not '%Seth%'
  • Dot anchor for abbreviations: 'Domest.%' not '%Domest%'
• Batch-first — use single INSERT for all new manufacturers, then batch UPDATE for wiring.
• Verify after every write phase — confirm counts match expectations.
• dm sub-brands — Denkmit, Dmbio, Jessa, Mivolis, Profissimo, Balea, Alverde all map to manufacturer dm (not separate manufacturer entries).

Phase 0: Survey the landscape

Step 0a — Count articles without manufacturer

SELECT COUNT(*) AS total,
			 COUNT(*) FILTER (WHERE manufacturer_id IS NULL) AS without_manufacturer
FROM articles;

Step 0b — Fetch unmanufactured article names

SELECT id, canonical_name
FROM articles
WHERE manufacturer_id IS NULL
ORDER BY canonical_name;

Step 0c — Fetch all existing manufacturers

SELECT display_name FROM manufacturers ORDER BY display_name;

Phase 1: Extract potential manufacturer names

Analyze each canonical_name and identify words/tokens that are likely brand or manufacturer names. Use these heuristics:

Identification heuristics

Anti-patterns (do NOT treat as manufacturer)

Output structure

Organize findings into three groups:

1. Already in DB — articles whose brand matches an existing manufacturer (wire immediately)
2. dm sub-brands — articles with dm house-brand names (wire to dm)
3. New manufacturers needed — articles with identifiable brands not yet in manufacturers table

For each candidate, note:

• Proposed display_name and normalized_key
• ILIKE pattern for matching
• Which article(s) it covers

Present to user as a Markdown table for review before proceeding.

Phase 2: Wire articles to existing manufacturers

For brands that match existing manufacturers, run targeted UPDATEs:

-- Pattern: one UPDATE per existing manufacturer
UPDATE articles
SET manufacturer_id = (SELECT id FROM manufacturers WHERE display_name = '<Manufacturer>')
WHERE manufacturer_id IS NULL
AND canonical_name ILIKE '<pattern>';

dm sub-brands (batch together)

UPDATE articles
SET manufacturer_id = (SELECT id FROM manufacturers WHERE display_name = 'dm')
WHERE manufacturer_id IS NULL
AND (
	canonical_name ILIKE 'Denkmit%'
	OR canonical_name ILIKE 'Dmbio%'
	OR canonical_name ILIKE 'Jessa %'
	OR canonical_name ILIKE 'Mivolis%'
	OR canonical_name ILIKE 'Profissimo%'
	OR canonical_name ILIKE 'Balea%'
	OR canonical_name ILIKE 'Alverde%'
);

Verify with:

SELECT a.canonical_name, m.display_name AS manufacturer
FROM articles a
JOIN manufacturers m ON a.manufacturer_id = m.id
WHERE m.display_name IN ('<list of matched manufacturers>')
ORDER BY m.display_name, a.canonical_name;

Phase 3: Create new manufacturers + wire articles

Step 3a — Batch INSERT all new manufacturers

INSERT INTO manufacturers (id, display_name, normalized_key) VALUES
(gen_random_uuid(), '<DisplayName1>', '<normalized-key-1>'),
(gen_random_uuid(), '<DisplayName2>', '<normalized-key-2>'),
...;

Step 3b — Batch UPDATE to wire articles

Use a single UPDATE with FROM join for efficiency:

UPDATE articles a
SET manufacturer_id = m.id
FROM manufacturers m
WHERE a.manufacturer_id IS NULL
AND (
	(m.display_name = '<Mfr1>' AND a.canonical_name ILIKE '<pattern1>')
	OR (m.display_name = '<Mfr2>' AND a.canonical_name ILIKE '<pattern2>')
	...
);

Split into multiple UPDATE batches if > 25 conditions (keeps SQL readable).

Step 3c — Verify

SELECT m.display_name AS manufacturer, COUNT(*) AS articles_linked
FROM articles a
JOIN manufacturers m ON a.manufacturer_id = m.id
WHERE m.display_name IN ('<list of new manufacturers>')
GROUP BY m.display_name
ORDER BY m.display_name;

Phase 4: Update taxonomy files (conditional)

If new brand-to-manufacturer mappings were established, update the taxonomy files so future articles_research.clj runs recognize them automatically:

Phase 5: Final report

SELECT
	COUNT(*) AS total,
	COUNT(*) FILTER (WHERE manufacturer_id IS NULL) AS without_manufacturer,
	COUNT(*) FILTER (WHERE manufacturer_id IS NOT NULL) AS with_manufacturer,
	ROUND(100.0 * COUNT(*) FILTER (WHERE manufacturer_id IS NOT NULL) / COUNT(*), 1) AS coverage_pct
FROM articles;

Report:

1. Existing manufacturers linked — count per manufacturer
2. New manufacturers created — count, with article counts
3. Coverage change — before vs after percentages
4. Remaining unmanufactured — note that ~40-60% of articles (generic produce, services, bags, etc.) legitimately have no manufacturer

Completion gate

• All identifiable brand names in article names have been matched
• No duplicate manufacturers created (checked before INSERT)
• ILIKE patterns verified to not cause false matches
• Counts verified after each write phase
• Taxonomy files updated if new brand mappings discovered
• Final coverage % reported

Common Bosnian manufacturer patterns (quick reference)