#Brand Library Schema (V1)

Deprecated: The V1 brand_library_* tables have been superseded by the V2 brands, domains, and brand_categories tables. See Architecture Overview for the current schema.

The Brand Library is a parallel schema for curated brand identity data and high-signal tracked URLs. It is system-owned seed data with no user ownership or RLS policies.

Note: Brand Library (brand_library_* tables) is distinct from Brand Sites (brand_sites table). Brand Library contains curated, admin-maintained brand metadata used for tracking suggestions. Brand Sites is a user-facing domain grouping derived automatically from tracked URLs. See v1-business-rules.md for brand sites documentation (archived v1 spec).

#Purpose

• Power Tracking Suggestions for user onboarding
• Provide consistent brand context for URL change summaries
• Maintain auditable provenance for all ingested data
• Support confidence-aware querying and filtering

#Design Principles

Principle	Implementation
URL-scoped only	No site crawling or link following
Non-substitutive	Summaries describe purpose, not content
Confidence-aware	Every derived field carries explicit confidence
System-owned	No RLS policies; service role access only

#Schema Overview

┌─────────────────────────┐
│  brand_library_sources  │  ← Provenance tracking
└─────────────────────────┘
           │
           ▼
┌─────────────────────────┐
│  brand_library_brands   │  ← Canonical brand entities
└─────────────────────────┘
           │
           ▼
┌─────────────────────────┐
│   brand_library_urls    │  ← Validated trackable URLs
└─────────────────────────┘
           │
           ▼
┌─────────────────────────┐
│ brand_library_review_queue │  ← Ambiguity/QA queue
└─────────────────────────┘

---

#Enums

#brand_url_type

URL page types supported in V1:

Value	Description
pricing	Pricing/plans pages
status	Service status pages
privacy	Privacy policy pages
terms	Terms of service pages
security	Security/trust pages

#brand_confidence_level

Confidence indicators for derived fields:

Value	Meaning
high	Strong signal, multiple confirmations
medium	Reasonable confidence, may need review
low	Weak signal, flagged for review

#brand_entity_type

Entity types for the review queue:

Value	Description
brand	Brand identity record
url	URL record

#brand_review_status

Review queue item status:

Value	Description
open	Pending review
fixed	Issue resolved
ignored	Intentionally skipped

#brand_review_reason

Reasons for review queue entries:

Brand-related:

Value	Trigger
DOMAIN_AMBIGUOUS	Multiple possible canonical domains
DOMAIN_MISSING	No domain could be determined
CONFLICTING_DOMAINS	Sources disagree on canonical domain
SUMMARY_LOW_CONFIDENCE	LLM summary confidence too low
SUMMARY_TOO_MARKETING	Summary contains promotional language
INSUFFICIENT_INPUTS	Not enough data to generate summary

URL-related:

Value	Trigger
NO_MATCH_FOR_URL_TYPE	No valid URL found for type
MULTIPLE_STRONG_CANDIDATES	Multiple URLs scored equally
OFF_DOMAIN_REDIRECT	Final URL on different domain
URL_TYPE_MISMATCH	Page content doesn't match URL type

---

#Tables

#brand_library_sources

Tracks provenance of all ingested data.

Column	Type	Nullable	Description
id	UUID	No	Primary key
name	TEXT	No	Source name (e.g., "PDL Companies")
source_url	TEXT	Yes	URL to source dataset
license	TEXT	Yes	License terms
notes	TEXT	Yes	Additional notes
created_at	TIMESTAMPTZ	No	Record creation time
updated_at	TIMESTAMPTZ	No	Last update time

---

#brand_library_brands

Canonical brand entities with identity and categorization data.

Column	Type	Nullable	Description
id	UUID	No	Primary key
brand_name	TEXT	No	Display name
canonical_domain	TEXT	No	Primary domain (unique)
domain_aliases	JSONB	Yes	Alternate domains array
category_path	TEXT	No	Taxonomy path (e.g., "software/saas")
hq_city	TEXT	Yes	Headquarters city
hq_region	TEXT	Yes	Headquarters region/state
hq_country	TEXT	Yes	Headquarters country
business_summary	TEXT	Yes	1-2 sentence description
business_model	TEXT	Yes	SaaS, retailer, marketplace, etc.
audience	TEXT	Yes	B2B, B2C, hybrid
logo_url	TEXT	Yes	Logo image URL
confidence_identity	brand_confidence_level	No	Identity confidence
confidence_category	brand_confidence_level	No	Category confidence
source_refs	JSONB	No	Source attribution
created_at	TIMESTAMPTZ	No	Record creation time
updated_at	TIMESTAMPTZ	No	Last update time

Constraints:

• canonical_domain is UNIQUE

Indexes:

• confidence_identity
• confidence_category

---

#brand_library_urls

Validated, trackable URLs associated with brands.

Column	Type	Nullable	Description
id	UUID	No	Primary key
brand_id	UUID	No	FK to brand_library_brands
url_type	brand_url_type	No	Page type enum
tracked_url	TEXT	No	Original URL
final_url	TEXT	No	Resolved URL after redirects
http_status	INTEGER	No	HTTP status code
title	TEXT	Yes	Page title
h1	TEXT	Yes	First H1 element
why_track	TEXT	No	1-2 sentences on change value
summary	TEXT	No	2-3 sentences on page purpose
confidence_url_match	brand_confidence_level	No	URL type match confidence
confidence_summary	brand_confidence_level	No	Summary confidence
source_refs	JSONB	No	Source attribution
created_at	TIMESTAMPTZ	No	Record creation time
updated_at	TIMESTAMPTZ	No	Last update time

Constraints:

• brand_id references brand_library_brands(id) ON DELETE CASCADE

Indexes:

• brand_id
• url_type
• confidence_url_match
• confidence_summary

---

#brand_library_review_queue

Centralized queue for ambiguous or low-confidence items requiring manual review.

Column	Type	Nullable	Description
id	UUID	No	Primary key
entity_type	brand_entity_type	No	brand or url
entity_id	UUID	No	ID of the entity
reason	brand_review_reason	No	Why flagged
details	JSONB	Yes	Additional context
status	brand_review_status	No	open, fixed, ignored
created_at	TIMESTAMPTZ	No	Record creation time
updated_at	TIMESTAMPTZ	No	Last update time

Indexes:

• status
• (entity_type, entity_id)

---

#Relationships

brand_library_sources
        │
        │ (referenced via source_refs JSONB)
        ▼
brand_library_brands ──────────────────────┐
        │                                  │
        │ 1:N                              │
        ▼                                  │
brand_library_urls                         │
        │                                  │
        │ (polymorphic via entity_type)    │
        ▼                                  │
brand_library_review_queue ◄───────────────┘

---

#Migration

File: supabase/migrations/0005_brand_library_v1.sql

The migration:

1. Creates 5 Postgres enums
2. Creates 4 tables with timestamps
3. Adds foreign key constraints
4. Creates performance indexes
5. Attaches update_updated_at triggers

---

#TypeScript Types

#Location: packages/db/src/client.ts

type BrandUrlType = 'pricing' | 'status' | 'privacy' | 'terms' | 'security'
type BrandConfidenceLevel = 'high' | 'medium' | 'low'
type BrandEntityType = 'brand' | 'url'
type BrandReviewStatus = 'open' | 'fixed' | 'ignored'
type BrandReviewReason =
  | 'DOMAIN_AMBIGUOUS' | 'DOMAIN_MISSING' | 'CONFLICTING_DOMAINS'
  | 'SUMMARY_LOW_CONFIDENCE' | 'SUMMARY_TOO_MARKETING' | 'INSUFFICIENT_INPUTS'
  | 'NO_MATCH_FOR_URL_TYPE' | 'MULTIPLE_STRONG_CANDIDATES'
  | 'OFF_DOMAIN_REDIRECT' | 'URL_TYPE_MISMATCH'

#Location: packages/shared/src/types.ts

Interfaces: BrandLibrarySource, BrandLibraryBrand, BrandLibraryUrl, BrandLibraryReviewQueueItem

#Location: packages/shared/src/schemas.ts

Zod schemas for runtime validation of all entities.

---

#Access Patterns

Operation	Access Method
Seed data ingestion	Service role (bypasses RLS)
Admin review queue	Service role
Tracking suggestions	Service role read
User-facing brand display	Service role read

---

#What This Schema Does NOT Include

Per V1 scope, these are explicitly excluded:

• RLS policies (system-owned data)
• Ingestion scripts
• Brandfetch API integration
• LLM prompt definitions
• Admin UI
• Seed data

---

#Related Documentation

• Brand Library Master Plan - Product requirements (archived v1)
• Architecture Overview - System context
• Database Guide - Migration patterns