#Schema Naming Contracts

This document is contractual. Future schema or API changes must comply with it.

#1. Purpose & Scope

#What This Contract Governs

• PostgreSQL table, column, enum, index, constraint, and function names
• TypeScript type and interface property names
• Zod schema names and field keys
• API endpoint paths and payload keys
• Queue message types and payload keys
• File and directory naming conventions

#What This Contract Does NOT Govern

• UI display labels (e.g., "Check Frequency" vs "check_frequency")
• Marketing copy and brand names
• Analytics event names
• Log message formatting
• Third-party API integrations (Stripe, Supabase Auth, etc.)

---

#2. Canonical Naming Rules (by Layer)

#PostgreSQL

Element	Convention	Example
Tables	snake_case (plural preferred)	tracked_urls, url_checks
Columns	snake_case	user_id, created_at
Enum types	snake_case	brand_url_type
Enum values	snake_case or SCREAMING_SNAKE_CASE	'daily', 'DOMAIN_MISSING'
Indexes	idx_{table}_{columns}	idx_tracked_urls_user
Constraints	{table}_{description}	url_checks_one_per_day
Foreign keys	{table}_{column}_fkey	tracked_urls_user_id_fkey
Triggers	{table}_{action}	profiles_updated_at
Functions	snake_case	update_updated_at()

#TypeScript

Element	Convention	Example
Interface names	PascalCase	TrackedUrl, UrlCheck
Type aliases	PascalCase	CheckFrequency, BrandUrlType
Interface properties	snake_case	user_id, created_at
Variables	camelCase	trackedUrl, userId
Constants	SCREAMING_SNAKE_CASE	POLL_INTERVAL_MS
Functions	camelCase	getNextCheckTime()

Important: TypeScript interface properties use snake_case to maintain 1:1 mapping with PostgreSQL columns. This eliminates transformation overhead and reduces errors.

#Zod Schemas

Element	Convention	Example
Schema variable	PascalCase + Schema suffix	CreateTrackedUrlSchema
Enum schema	PascalCase + Schema suffix	CheckFrequencySchema
Object keys	snake_case	tracked_url_id, enqueued_at

#API Routes & Payloads

Element	Convention	Example
Endpoint paths	kebab-case or abbreviated	/api/urls, /api/enqueue-due-urls
Route parameters	[id] (Next.js convention)	/api/urls/[id]
Request body keys	snake_case	{ user_note: "..." }
Response body keys	snake_case	{ created_at: "..." }
Query parameters	snake_case	?is_active=true

#Queues / Workers

Element	Convention	Example
Message type	SCREAMING_SNAKE_CASE	TRACK_URL, RENDER_URL
Payload keys	snake_case	tracked_url_id, url
Metadata keys	snake_case	enqueued_at, attempt
Queue names	snake_case with colon prefix	queue:track_url

#Files & Directories

Element	Convention	Example
TypeScript files	kebab-case.ts	content-normalizer.ts
Test files	{name}.test.ts	utils.test.ts
React components	PascalCase.tsx	TrackedUrlCard.tsx
Documentation	kebab-case.md	url-normalization.md
Migrations	{nnnn}_{description}.sql	0006_v1_contracts.sql
Config files	standard or kebab-case	biome.json, tsconfig.json

---

#3. Locked Identifiers (DO NOT RENAME)

WARNING: Changing any of the following requires:

1. A database migration with backfill
2. Contract version bump
3. Review by db-migration-operator

#Tables (32)

#Core Tables (V1)

Table	Purpose
profiles	User profiles synced from Supabase Auth
tracked_urls	URLs being monitored by users (V1)
url_checks	Individual check records (V1)
url_changes	Change records with diff metadata (V1)
summaries	AI-generated change summaries (V1)
url_renders	Playwright render artifacts (V1)

#V1 Contract Tables

Table	Purpose
plan_definitions	Data-driven plan configuration
user_subscriptions	Maps users to plans
notification_preferences	Per-user notification settings
notification_deliveries	Audit log for sent notifications
tracking_suggestions	Discovery templates
tracking_suggestion_examples	URL type examples per suggestion
brand_sites	Domain grouping for tracked URLs

#Brand Library Tables

Table	Purpose
brand_library_sources	Provenance tracking
brand_library_brands	Canonical brand entities
brand_library_urls	Validated trackable URLs
brand_library_review_queue	Ambiguity/low-confidence items

#vNext Global URL Library Tables

Table	Purpose
urls	Global URL library (unique by canonical_url)
url_observations	Global check records (one per URL per day)
url_change_events	Global change records
url_change_summaries	Global AI summaries
url_render_artifacts	Global render artifacts
user_url_library	User-URL relationship with history gating
url_policy_decisions	URL policy audit log
url_submissions	URL submission workflow
trend_definitions	Trend configuration
trend_points	Trend time-series data
user_profile_details	Profile extension (display_name, timezone)
onboarding_state	Onboarding progress tracking
billing_customers	Stripe customer mapping
invoices	Invoice records
billing_webhook_events	Stripe webhook audit log

#Critical Columns

Table.Column	Type	Locked Reason
tracked_urls.canonical_url	TEXT	Used for uniqueness constraint
tracked_urls.check_frequency	TEXT	CHECK constraint: 'daily', 'weekly'
tracked_urls.user_note	TEXT	User-facing field
url_checks.content_hash	TEXT	Used for change detection
url_checks.has_change	BOOLEAN	Used in indexes
url_checks.checked_day	DATE	Generated column, unique constraint
url_checks.section_hashes	JSONB	Structured as [{id, hash, heading?}]
summaries.confidence	NUMERIC(3,2)	Range: 0.00-1.00
summaries.ai_provider	TEXT	Provider identifier
summaries.ai_model	TEXT	Model identifier
plan_definitions.plan_key	TEXT	Primary key, FK reference
plan_definitions.max_tracked_urls	INTEGER	Used for URL cap enforcement
plan_definitions.allowed_cadences	TEXT[]	Array of allowed frequencies
user_subscriptions.status	TEXT	CHECK: 'active', 'canceled', 'past_due'
notification_deliveries.channel	TEXT	CHECK: 'email'
notification_deliveries.status	TEXT	CHECK: 'queued', 'sending', 'pending', 'sent', 'failed'
notification_deliveries.dedupe_key	TEXT	Nullable; partial unique index
brand_sites.canonical_domain	TEXT	UNIQUE constraint for domain dedup
brand_sites.source	TEXT	CHECK: 'user_added', 'brand_seeded', 'admin_added'
tracked_urls.brand_site_id	UUID	FK to brand_sites (nullable until migration 0010)

#PostgreSQL Enums (16)

#Brand Library Enums

Enum Type	Values
brand_url_type	'pricing', 'status', 'privacy', 'terms', 'security'
brand_confidence_level	'high', 'medium', 'low'
brand_entity_type	'brand', 'url'
brand_review_status	'open', 'fixed', 'ignored'
brand_review_reason	'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'

#vNext Enums

Enum Type	Values
url_source	'brand_seeded', 'user_added', 'admin_added'
url_status	'active', 'paused', 'blocked', 'needs_review'
url_type_extended	'pricing', 'status', 'privacy', 'terms', 'security', 'changelog', 'docs', 'blog', 'other'
policy_decision	'allow', 'block', 'review'
policy_reason_code	'adult_content', 'hate_or_extremism', 'malware_or_phishing', 'spam_or_low_quality', 'illegal_or_regulated', 'auth_wall_or_uncheckable', 'duplicate', 'unknown'
policy_decider	'system', 'admin'
submission_status	'pending', 'blocked', 'approved_created_global', 'approved_linked_existing', 'needs_review'
trend_type	'category_trend', 'brand_vs_brand', 'seasonal', 'cyclical', 'url_type_trend'
invoice_status	'draft', 'open', 'paid', 'uncollectible', 'void'
webhook_event_status	'received', 'processed', 'failed'
onboarding_step	'welcome', 'add_first_url', 'explore_brand_library', 'configure_notifications', 'completed'

#vNext Critical Columns

Table.Column	Type	Locked Reason
urls.canonical_url	TEXT	UNIQUE constraint, global deduplication
urls.source	url_source	Tracks URL provenance
urls.status	url_status	Controls tracking state
url_observations.checked_day	DATE	Generated column, unique constraint
url_change_events.url_id	UUID	FK to global urls
url_change_summaries.url_change_event_id	UUID	UNIQUE FK to events
user_url_library.history_start_at	TIMESTAMPTZ	NULL = full history, set = gated
user_url_library.user_id + url_id	-	Unique constraint pair
url_submissions.status	submission_status	Workflow state
url_policy_decisions.decision	policy_decision	Allow/block/review
billing_customers.stripe_customer_id	TEXT	UNIQUE, Stripe integration
invoices.stripe_invoice_id	TEXT	UNIQUE, Stripe integration

#API Endpoints (3)

Method	Path	Purpose
GET/POST	/api/urls	List/create tracked URLs
GET/PATCH/DELETE	/api/urls/[id]	Read/update/delete tracked URL
POST	/api/enqueue-due-urls	Cron endpoint for queue
GET	/api/brands	List user's brand sites
GET	/api/brands/[id]	Get brand site with tracked URLs

#Queue Message Types (2)

Type	Purpose
TRACK_URL	Fetch and check URL for changes
RENDER_URL	Playwright render request

#Notification Delivery Status (Resolved Drift)

As of migration 0008_notification_dedupe_key.sql, the database column notification_deliveries.status now includes all 5 states:

'queued' | 'sending' | 'pending' | 'sent' | 'failed'

State definitions:

• pending — Initial state, awaiting processing (database DEFAULT)
• queued — Scheduled for delivery
• sending — Delivery in progress
• sent — Successfully delivered
• failed — Delivery failed (see error_message)

The previous drift between database (3 states) and application code (5 states) has been resolved. All states are now canonical and persisted.

History: Prior to migration 0008, the database CHECK constraint only allowed 'pending', 'sent', 'failed'. States 'queued' and 'sending' existed only at runtime. This was intentionally expanded in 0008 to support proper delivery tracking.

---

#4. Cross-Layer Mapping Rules

All layers use snake_case for data properties. Mapping is 1:1 (no transformation).

DB Column	TS Property	API Field	Notes
user_id	user_id	user_id	UUID reference to profiles
created_at	created_at	created_at	ISO 8601 string
check_frequency	check_frequency	check_frequency	'daily' or 'weekly'
what_changed	what_changed	what_changed	Summary field
tracked_url_id	tracked_url_id	tracked_url_id	FK to tracked_urls
content_hash	content_hash	content_hash	SHA-256 hash
section_hashes	section_hashes	section_hashes	SectionMeta[]

Transformation rule: None. DB value flows unchanged through TS types to API payloads.

---

#5. Agent Enforcement Notes

#Ownership

• db-migration-operator owns all schema naming decisions
• architect-steward enforces contract compliance during PR review

#Agent Behavior Requirements

1. Agents MUST refuse to rename locked identifiers
2. Agents MUST use snake_case for all new columns and TS properties
3. Agents MUST use SCREAMING_SNAKE_CASE for new enum values when adding to brand_review_reason
4. Agents MUST use snake_case for new enum values when adding to other enums
5. New tables MUST follow {domain}_{entity} pattern for namespacing
6. New API endpoints MUST use snake_case for all request/response keys

#Adding New Identifiers

When adding a new column:

-- CORRECT
ALTER TABLE tracked_urls ADD COLUMN last_error_message TEXT;

-- INCORRECT (camelCase)
ALTER TABLE tracked_urls ADD COLUMN lastErrorMessage TEXT;

When adding a new TypeScript property:

// CORRECT
interface TrackedUrl {
  last_error_message: string | null
}

// INCORRECT
interface TrackedUrl {
  lastErrorMessage: string | null
}

---

#6. Non-Goals

This contract explicitly does NOT govern:

1. UI copy/labels - The UI may display "Check Frequency" for check_frequency
2. Marketing names - "Eko Pro" is a display name, plan_key: 'pro' is the identifier
3. Analytics events - External analytics may use different conventions
4. Third-party integrations - Stripe webhook payloads, Supabase Auth responses, etc.
5. Log message formatting - Logger output is not bound by these rules
6. Internal variable names - Local variables in functions may use camelCase
7. React component props - Component-internal props may use camelCase

---

#Appendix: Common Abbreviations

Abbreviation	Full Form	Usage
url	Uniform Resource Locator	Always lowercase
id	Identifier	Always lowercase
ms	Milliseconds	Suffix for time values
fk	Foreign Key	In constraint names
pk	Primary Key	In documentation only
dlq	Dead Letter Queue	Queue suffix