Data Model & Schema Reference

Complete database schema for MotoPartPicker — motorcycle parts compatibility platform. Hosted on Neon Postgres (serverless), managed via Drizzle ORM with timestamp-based migrations.

Entity Relationship Diagram

Core

Community

Commerce

System

Solid lines = direct FK relationships | Dashed lines = cross-domain references | PII = personally identifiable data | ★ = central query table

Domain Overview

◆

Core Domain — 3 tables

bikes, parts, compatibility_records. Every query in the application starts from compatibility_records. This is the fundamental value proposition: knowing whether a part fits a specific bike with confidence scoring.

◆

Community Domain — 5 tables

users, verifications, user_bikes, builds, build_parts. The flywheel. Users verify fitment, which improves data quality. Builds drive engagement and sharing. Garage features increase retention.

◆

Commerce Domain — 4 tables

retailers, part_prices, affiliate_clicks, retailer_subscriptions. Revenue infrastructure. Affiliate clicks tracked at part-price level. Retailer billing through Stripe with tiered subscriptions.

◆

System Domain — 1 table

audit_log. Append-only compliance ledger. Partitioned by month. 2-year retention. Captures all actor-driven mutations across all entities for GDPR compliance and data integrity auditing.

Table Definitions

bikes Core

Year/Make/Model/Variant lookup. Primary entity for all compatibility searches. Deduplicated with a compound unique constraint.

Year 1 / Year 3

200 / 2K

Column	Type	Constraints	Notes
id	uuid	PK	gen_random_uuid() default
year	smallint	NOT NULL	Model year, e.g. 2020
make	text	NOT NULL	e.g. "Kawasaki"
model	text	NOT NULL	e.g. "Ninja 400"
variant	text	NULLABLE	e.g. "ABS", "SE", "Final Edition"
engine_cc	smallint	NULLABLE	Displacement in cc
category	enum	ENUM NOT NULL	sportcruiseradvnakeddualcafetouring
image_url	text	NULLABLE	R2 CDN URL for bike photo
created_at	timestamptz	DEFAULT now()
updated_at	timestamptz	DEFAULT now()	Trigger: auto-update on change

Indexes

UNIQUE (year, make, model, variant) idx_bikes_make_model idx_bikes_category

parts Core

Aftermarket parts catalog. Brand-agnostic. Part numbers are optional as many aftermarket parts lack consistent SKUs across retailers.

Year 1 / Year 3

5K / 50K

Column	Type	Constraints	Notes
id	uuid	PK
name	text	NOT NULL	e.g. "Akrapovic Slip-On Exhaust"
brand	text	NOT NULL	Manufacturer brand name
part_number	text	NULLABLE	Manufacturer SKU when available
category	enum	ENUM	exhaustprotectionergonomicscosmeticelectronicssuspensionluggage
subcategory	text	NOT NULL	Free-text sub-classification
description	text	NOT NULL	Markdown-safe product description
image_url	text	NULLABLE	R2 CDN URL
msrp_cents	integer	NULLABLE	MSRP in cents. Use part_prices for current retail.
created_at	timestamptz	DEFAULT now()
updated_at	timestamptz	DEFAULT now()

Indexes

idx_parts_brand idx_parts_category idx_parts_part_number

compatibility_records Core PRIMARY TABLE

The central junction table. Every compatibility query resolves here. Confidence scoring drives the UI display. The conflicts_with array enables conflict detection across build parts.

Year 1 / Year 3

20K / 500K

Column	Type	Constraints	Notes
id	uuid	PK
bike_id	uuid	FK → bikes NOT NULL	ON DELETE CASCADE
part_id	uuid	FK → parts NOT NULL	ON DELETE CASCADE
confidence	enum	ENUM NOT NULL	verified reported no_data
verification_count	integer	DEFAULT 0	Denormalized count from verifications table
notes	text	NULLABLE	Admin/curator notes on fitment
install_difficulty	enum	ENUM NOT NULL	easymoderatehard
install_time_minutes	smallint	NULLABLE	Estimated install time
tools_required	text	NULLABLE	Comma-separated tool list
conflicts_with	uuid[]	ARRAY NULLABLE	part_ids of incompatible parts. GIN index candidate for @> queries.
created_at	timestamptz	DEFAULT now()
updated_at	timestamptz	DEFAULT now()

Indexes

UNIQUE (bike_id, part_id) idx_compat_bike_confidence (bike_id, confidence) idx_compat_part_id

users Community Contains PII

Rider accounts. Soft-delete pattern via deleted_at. Reputation scoring drives contributor trust. Badges are a simple text array for flexibility.

Year 1 / Year 3

25K / 300K

Column	Type	Constraints	Notes
id	uuid	PK
email	citext	UNIQUE PII NOT NULL	Case-insensitive. RLS protected.
display_name	text	NOT NULL PII	Public-facing username. Pseudonymous OK.
avatar_url	text	NULLABLE	R2 CDN URL
reputation_score	integer	DEFAULT 0	Computed from verified contributions
badges	text[]	ARRAY	e.g. ["early_adopter", "top_contributor"]
created_at	timestamptz	DEFAULT now()
updated_at	timestamptz	DEFAULT now()
deleted_at	timestamptz	NULLABLE	Soft delete. NULL = active. All RLS policies filter on deleted_at IS NULL.

Indexes

UNIQUE (email) idx_users_reputation

verifications Community

User-submitted fitment confirmations. Each verification updates the parent compatibility_record.verification_count via trigger. Photos stored in R2.

Year 1 / Year 3

10K / 200K

Column	Type	Constraints	Notes
id	uuid	PK
compatibility_record_id	uuid	FK → compatibility_records	ON DELETE CASCADE
user_id	uuid	FK → users	ON DELETE SET NULL (preserve data)
fits	boolean	NOT NULL	True = confirmed fit. False = does not fit.
notes	text	NULLABLE	Rider's free-text install notes
photos	text[]	ARRAY	R2 CDN URLs. Max 5 photos per verification.
install_date	date	NULLABLE	When rider installed the part
upvotes	integer	DEFAULT 0	Community helpfulness votes
created_at	timestamptz	DEFAULT now()

Indexes

idx_verifications_record (compatibility_record_id) idx_verifications_user (user_id)

user_bikes Community

Garage: which bikes a user owns. Drives personalized compatibility feeds. is_primary flags the default bike for quick lookups.

Avg per user

1.4 bikes

Column	Type	Constraints	Notes
id	uuid	PK
user_id	uuid	FK → users NOT NULL	ON DELETE CASCADE
bike_id	uuid	FK → bikes NOT NULL	ON DELETE RESTRICT
is_primary	boolean	DEFAULT false	Only one primary per user (partial unique index)
nickname	text	NULLABLE	User's name for their bike
created_at	timestamptz	DEFAULT now()

Indexes

idx_user_bikes_user (user_id) UNIQUE (user_id, bike_id) PARTIAL UNIQUE (user_id) WHERE is_primary = true

builds Community

Saved build plans. Public builds are shareable and drive organic SEO. view_count enables trending builds feed.

Year 1 / Year 3

8K / 120K

Column	Type	Constraints	Notes
id	uuid	PK
user_id	uuid	FK → users	ON DELETE CASCADE
bike_id	uuid	FK → bikes	ON DELETE RESTRICT
name	text	NOT NULL	User-given build name
is_public	boolean	DEFAULT true	Controls visibility. RLS enforced.
view_count	integer	DEFAULT 0	Incremented via background job, not per-request
created_at	timestamptz	DEFAULT now()
updated_at	timestamptz	DEFAULT now()

Indexes

idx_builds_user (user_id) idx_builds_bike (bike_id) idx_builds_public_views (view_count DESC) WHERE is_public = true

build_parts Community

Parts belonging to a build. sort_order enables user-defined part sequencing. is_installed tracks real-world build progress.

Avg parts/build

Column	Type	Constraints	Notes
id	uuid	PK
build_id	uuid	FK → builds	ON DELETE CASCADE
part_id	uuid	FK → parts	ON DELETE RESTRICT
is_installed	boolean	DEFAULT false
installed_at	date	NULLABLE	Actual install date
notes	text	NULLABLE	Build-specific part notes
sort_order	smallint	NOT NULL	User-defined display order

Indexes

idx_build_parts_build (build_id) UNIQUE (build_id, part_id)

retailers Commerce

Partner retailers. affiliate_url_template uses {part_url} placeholder for dynamic link generation. Inactive retailers remain for historical click data.

Year 1 / Year 3

5 / 30

Column	Type	Constraints	Notes
id	uuid	PK
name	text	NOT NULL	e.g. "RevZilla"
slug	text	UNIQUE NOT NULL	URL-safe identifier e.g. "revzilla"
website_url	text	NOT NULL
affiliate_url_template	text	NOT NULL	Template with {part_url} token
commission_rate	numeric(4,2)	NOT NULL	Percentage, e.g. 8.50
cookie_days	smallint	NOT NULL	Attribution window in days
logo_url	text	NULLABLE	R2 CDN URL for retailer logo
is_active	boolean	DEFAULT true	Controls display in price comparisons

Indexes

UNIQUE (slug) idx_retailers_active WHERE is_active = true

part_prices Commerce

Current prices at each retailer, updated periodically via price-scraping jobs. One record per part/retailer pair. Historical prices not retained in this table — use analytics DB if needed.

Year 1 / Year 3

50K / 500K

Column	Type	Constraints	Notes
id	uuid	PK
part_id	uuid	FK → parts	ON DELETE CASCADE
retailer_id	uuid	FK → retailers	ON DELETE RESTRICT
price_cents	integer	NOT NULL	Current price in cents. Always store in smallest unit.
in_stock	boolean	NOT NULL
stock_note	text	NULLABLE	e.g. "Ships in 3-5 days"
affiliate_url	text	NOT NULL	Pre-generated affiliate URL (not template)
last_checked_at	timestamptz	NOT NULL	When price scraper last verified this record

Indexes

UNIQUE (part_id, retailer_id) — idx_prices_part_retailer idx_prices_last_checked (last_checked_at)

affiliate_clicks Commerce Partitioned

High-volume click tracking. Partitioned by month for query performance. ip_hash stored (not raw IP) for GDPR compliance. Feeds revenue reporting.

Year 1 / Year 3

500K / 10M

Column	Type	Constraints	Notes
id	uuid	PK
part_price_id	uuid	FK → part_prices	ON DELETE SET NULL
user_id	uuid	FK → users NULLABLE	NULL for anonymous clicks
session_id	text	NOT NULL	Browser session for deduplication
clicked_at	timestamptz	DEFAULT now()	Partition key
ip_hash	text	NOT NULL	SHA-256 of IP. Not reversible.

Indexes

idx_clicks_date (clicked_at) idx_clicks_part_price (part_price_id)

Partition: PARTITION BY RANGE (clicked_at) — monthly partitions created by cron job

retailer_subscriptions Commerce

Stripe billing records for retailer partner subscriptions. Stripe is source of truth — this table syncs via webhook. Never mutate directly; always reconcile from Stripe events.

Relation

1:1 retailer

Column	Type	Constraints	Notes
id	uuid	PK
retailer_id	uuid	FK → retailers UNIQUE	One subscription per retailer
stripe_customer_id	text	NOT NULL	cus_xxx Stripe customer ID
stripe_subscription_id	text	NOT NULL	sub_xxx Stripe subscription ID
tier	enum	ENUM	affiliateverifieddata
status	enum	ENUM	trialingactivepast_duecanceled
current_period_end	timestamptz	NOT NULL	Access revokes after this timestamp

audit_log System Partitioned Append-Only

Immutable compliance ledger. All application-layer mutations append here. No UPDATE or DELETE ever executed on this table. 2-year retention enforced by partition drop cron job.

Retention

2 years

Column	Type	Constraints	Notes
id	bigint	PK GENERATED ALWAYS AS IDENTITY	Sequential for temporal ordering
actor_id	uuid	NULLABLE	NULL for system/background actions
action	text	NOT NULL	e.g. "verification.created", "user.deleted"
entity_type	text	NOT NULL	Table name, e.g. "verifications"
entity_id	uuid	NOT NULL	PK of the affected row
metadata	jsonb	NULLABLE	Before/after state, IP, user-agent, etc.
created_at	timestamptz	DEFAULT now()	Partition key

Indexes

idx_audit_actor (actor_id) idx_audit_entity (entity_type, entity_id)

Partition: PARTITION BY RANGE (created_at) — monthly partitions. Oldest partitions detached and dropped at 24-month boundary.

Data Store Decision Matrix

ℹ

Single-database-first approach

MotoPartPicker starts with Neon Postgres for all structured data. Secondary stores are added only when a specific constraint demands it. Avoid premature infrastructure complexity.

Data	Store	Reason	When to Reconsider
All structured entities (13 tables)	Neon Postgres	ACID transactions, FK enforcement, complex joins, full-text search via `tsvector`	At 10M+ compat records, consider read replicas
User photos (verification photos, avatars)	Cloudflare R2	Blob storage not suited for Postgres. R2 zero egress cost. CDN edge delivery.	Stay on R2 indefinitely for binary assets
Retailer logos, bike images, part images	Cloudflare R2	Same as above. URLs stored as `text` columns in Postgres.	Stay on R2 indefinitely
Session / auth tokens	Neon Postgres	Adequate at current scale. Simpler ops than Redis.	Move to Redis/Upstash at 50K+ concurrent sessions
Price feed cache (hot reads)	Neon Postgres	part_prices is a small table. Neon's connection pooler + warm cache handles it.	Add Redis cache layer when price queries exceed 50ms p95
Bike/part search (typeahead)	Neon Postgres	pg_trgm trigram index + tsvector handles typeahead at this scale.	Evaluate Typesense/Algolia at 500K+ parts with complex filtering
Stripe billing state	Stripe	Stripe is source of truth. Local `retailer_subscriptions` is a read-through cache synced by webhooks.	Never migrate off Stripe for billing
Analytics / click funnels	Neon Postgres	affiliate_clicks partitioned table handles Year 1-2 analytics queries.	Export to ClickHouse/BigQuery when queries exceed 5s or complex attribution needed
Rate limiting counters	Neon Postgres	Simple counters with TTL via pg_cron cleanup adequate for MVP.	Move to Upstash Redis for sub-millisecond rate limit checks at scale

Row Count Projections

Conservative growth estimates based on motorcycle parts market size and comparable platforms (RevZilla has ~400K SKUs; PCPartPicker ~800K parts). Year 1 represents focused North American market; Year 3 includes international expansion.

Table	Domain	Year 1	Year 3	Growth	Growth Driver
bikes	Core	200	2,000	10x	Curator data entry + community requests
parts	Core	5,000	50,000	10x	Retailer data feeds + community additions
compatibility_records	Core	20,000	500,000	25x	bikes x parts matrix growth — non-linear expansion
verifications	Community	10,000	200,000	20x	User growth x engagement rate x verifications per user
users	Community	25,000	300,000	12x	SEO organic + community flywheel
user_bikes	Community	35,000	420,000	12x	~1.4 bikes per active user
builds	Community	8,000	120,000	15x	~1 active build per 3 users average
build_parts	Community	56,000	840,000	15x	~7 parts per build average
retailers	Commerce	5	30	6x	Sales-driven; intentionally limited early
part_prices	Commerce	50,000	500,000	10x	parts x retailers matrix
affiliate_clicks	Commerce	500,000	10,000,000	20x	Highest growth table — reason for monthly partitioning
retailer_subscriptions	Commerce	5	30	6x	1:1 with retailers
audit_log	System	~200,000	~5,000,000	25x	All mutation events. Rolling 2yr window = steady state.

Top 10 Query Patterns

All queries are ranked by expected call frequency. Indexes were designed around these patterns.

1 Parts compatible with a specific bike (paginated) Very High — Core product page

-- Primary product query: bike compatibility page SELECT p.id, p.name, p.brand, p.category, p.subcategory, p.image_url, p.msrp_cents, cr.confidence, cr.install_difficulty, cr.install_time_minutes, cr.verification_count, MIN(pp.price_cents) AS min_price_cents FROM compatibility_records cr JOIN parts p ON p.id = cr.part_id LEFT JOIN part_prices pp ON pp.part_id = p.id AND pp.in_stock = true WHERE cr.bike_id = $1 AND cr.confidence != 'no_data' GROUP BY p.id, cr.id ORDER BY cr.confidence DESC, cr.verification_count DESC LIMIT $2 OFFSET $3;

Primary Index: idx_compat_bike_confidence (bike_id, confidence)

Secondary Index: idx_prices_part_retailer

Expected: <10ms at Year 1 scale

2 Bike search / typeahead (make + model) Very High — Every user session start

-- Typeahead: search bikes by make/model fragment SELECT id, year, make, model, variant, category FROM bikes WHERE to_tsvector('english', concat_ws(' ', make, model, variant)) @@ plainto_tsquery('english', $1) ORDER BY year DESC, make, model LIMIT 10;

Index: GIN index on tsvector(make, model, variant) — add at schema creation

Fallback: idx_bikes_make_model for exact prefix

Expected: <5ms

3 Check if a specific part fits a specific bike Very High — Part detail page

-- Single-row compatibility lookup with verification details SELECT cr.*, json_agg(json_build_object( 'fits', v.fits, 'notes', v.notes, 'photos', v.photos, 'upvotes', v.upvotes ) ORDER BY v.upvotes DESC) AS verifications FROM compatibility_records cr LEFT JOIN verifications v ON v.compatibility_record_id = cr.id WHERE cr.bike_id = $1 AND cr.part_id = $2 GROUP BY cr.id;

Index: UNIQUE (bike_id, part_id) — direct lookup O(log n)

Expected: <3ms

4 User garage: all bikes + compatible parts summary High — Dashboard load

-- Garage view: user's bikes with compat record counts SELECT b.id, b.year, b.make, b.model, b.variant, b.image_url, b.category, ub.is_primary, ub.nickname, COUNT(cr.id) FILTER (WHERE cr.confidence = 'verified') AS verified_parts, COUNT(cr.id) FILTER (WHERE cr.confidence = 'reported') AS reported_parts FROM user_bikes ub JOIN bikes b ON b.id = ub.bike_id LEFT JOIN compatibility_records cr ON cr.bike_id = b.id WHERE ub.user_id = $1 GROUP BY b.id, ub.id ORDER BY ub.is_primary DESC;

Index: idx_user_bikes_user, idx_compat_bike_confidence

Expected: <15ms

5 Price comparison for a part across all retailers High — Part detail page commerce section

-- Part prices ranked by cost, filtered to in-stock only SELECT r.name AS retailer_name, r.logo_url, r.cookie_days, pp.price_cents, pp.in_stock, pp.stock_note, pp.affiliate_url, pp.last_checked_at FROM part_prices pp JOIN retailers r ON r.id = pp.retailer_id AND r.is_active = true WHERE pp.part_id = $1 ORDER BY pp.in_stock DESC, pp.price_cents ASC;

Index: idx_prices_part_retailer UNIQUE(part_id, retailer_id)

Expected: <5ms (few retailers per part)

6 Build conflict detection (parts that conflict with current build) High — Build planner on every part add

-- Detect conflicts: any part in build that conflicts with new_part_id SELECT p.id, p.name, p.brand, cr.conflicts_with FROM build_parts bp JOIN compatibility_records cr ON cr.bike_id = $1 AND cr.part_id = bp.part_id JOIN parts p ON p.id = bp.part_id WHERE bp.build_id = $2 AND cr.conflicts_with @> ARRAY[$3::uuid]; -- $1=bike_id, $2=build_id, $3=new_part_id being added

Index: GIN on conflicts_with array — add: CREATE INDEX ON compatibility_records USING GIN (conflicts_with)

Expected: <8ms

7 Public builds for a specific bike (trending) Medium — Bike SEO pages, community feed

-- Trending public builds for a bike, with part count and total cost SELECT b.id, b.name, b.view_count, b.created_at, u.display_name, u.avatar_url, u.reputation_score, COUNT(bp.id) AS part_count FROM builds b JOIN users u ON u.id = b.user_id AND u.deleted_at IS NULL LEFT JOIN build_parts bp ON bp.build_id = b.id WHERE b.bike_id = $1 AND b.is_public = true GROUP BY b.id, u.id ORDER BY b.view_count DESC LIMIT 20;

Index: idx_builds_public_views partial index (view_count DESC WHERE is_public)

Expected: <20ms

8 Bikes a part is compatible with (reverse lookup) Medium — Part detail page "Fits these bikes" section

-- All bikes a part fits, grouped by make for display SELECT b.year, b.make, b.model, b.variant, b.category, cr.confidence, cr.verification_count FROM compatibility_records cr JOIN bikes b ON b.id = cr.bike_id WHERE cr.part_id = $1 AND cr.confidence != 'no_data' ORDER BY b.make, b.year DESC, cr.confidence DESC;

Index: idx_compat_part_id

Expected: <10ms

9 Revenue report: affiliate clicks by retailer (monthly) Low — Internal analytics, retailer dashboards

-- Monthly click counts per retailer for revenue reporting SELECT r.name AS retailer_name, DATE_TRUNC('month', ac.clicked_at) AS month, COUNT(*) AS total_clicks, COUNT(DISTINCT ac.session_id) AS unique_sessions FROM affiliate_clicks ac JOIN part_prices pp ON pp.id = ac.part_price_id JOIN retailers r ON r.id = pp.retailer_id WHERE ac.clicked_at >= $1 AND ac.clicked_at < $2 GROUP BY r.id, month ORDER BY month DESC, total_clicks DESC;

Index: idx_clicks_date — partition pruning eliminates irrelevant months

Expected: <500ms for current month partition

10 Leaderboard: top contributors by reputation Low — Community pages, cached aggressively

-- Top contributors ranked by reputation with verification stats SELECT u.display_name, u.avatar_url, u.reputation_score, u.badges, COUNT(v.id) AS verification_count, SUM(v.upvotes) AS total_upvotes FROM users u LEFT JOIN verifications v ON v.user_id = u.id WHERE u.deleted_at IS NULL GROUP BY u.id ORDER BY u.reputation_score DESC LIMIT 50;

Index: idx_users_reputation

Caching: Cache result for 15 minutes in application layer

Expected: <30ms uncached

Migration Strategy

⚙

Drizzle ORM with timestamp-based migrations

Migrations are plain SQL files prefixed with a Unix timestamp (e.g. 0001_1712345600_create_bikes.sql). Drizzle tracks applied migrations in __drizzle_migrations table. All production migrations must be backward-compatible unless paired with a coordinated deploy.

File naming convention

{seq}_{timestamp}_{description}.sql — e.g. 0001_1712345600_create_core_tables.sql. Sequential prefix ensures ordering. Timestamp provides auditability. Description is human-readable.

Backward-compatible change rules

Always add columns with DEFAULT or NULLABLE. Never rename columns — add new + migrate + drop old in separate deploys. Never change enum values directly — add new enum, migrate data, drop old. Index creation uses CREATE INDEX CONCURRENTLY to avoid table locks.

Breaking change procedure

1. Deploy new code that reads both old and new column. 2. Run migration to add new column. 3. Backfill data. 4. Deploy code that writes to new column only. 5. Remove old column in follow-up migration after 1+ deploy cycle.

Partition management (affiliate_clicks, audit_log)

Monthly partitions created by pg_cron job on the 20th of each month for the following month. Example: CREATE TABLE affiliate_clicks_2026_05 PARTITION OF affiliate_clicks FOR VALUES FROM ('2026-05-01') TO ('2026-06-01'). Old partitions detached and dropped via retention cron.

CI/CD integration

Migrations run automatically in CI against a shadow Neon branch. If migration fails CI, deploy is blocked. Neon branch feature enables zero-risk migration testing — branch from main, apply migration, run tests, merge. Neon handles branch deletion automatically.

Rollback strategy

Neon point-in-time restore to any second in the last 7 days (Neon Pro feature). For logical rollbacks: every migration file has a corresponding down.sql that is never run automatically but available for emergency use. Drizzle drizzle-kit drop handles programmatic rollback in development.

Security & PII

⚠ PII Column Inventory

users.email — Direct identifier. citext. RLS policy: visible only to owning user and admins.
users.display_name — Public pseudonym. PII by GDPR definition. Included in DSAR export.
users.avatar_url — Derived PII if photo contains face. Handle as PII in export/delete flows.
affiliate_clicks.ip_hash — SHA-256 of IP. Not reversible. Stored for fraud detection, not attribution.
verifications.photos (R2) — User-submitted. May contain PII. GDPR deletion requires R2 object deletion.
audit_log.metadata (jsonb) — May contain PII snapshot from before-state. Retention 2yr, then partition drop.

✓ Soft Delete Pattern

users.deleted_at is set on GDPR deletion request. Row is never physically deleted.
All RLS policies on users include deleted_at IS NULL filter.
30 days after deletion, background job nullifies PII fields (email → anonymized hash, display_name → "Deleted User", avatar_url → NULL).
Verifications and builds by deleted users retain data but display_name shows "Deleted User".
R2 objects (photos, avatar) deleted immediately on GDPR request, not at 30-day mark.
audit_log retains actor_id but metadata is scrubbed of PII values at anonymization time.

Row Level Security (RLS) Policies

-- Enable RLS on user-scoped tables ALTER TABLE users ENABLE ROW LEVEL SECURITY; ALTER TABLE user_bikes ENABLE ROW LEVEL SECURITY; ALTER TABLE builds ENABLE ROW LEVEL SECURITY; ALTER TABLE build_parts ENABLE ROW LEVEL SECURITY; ALTER TABLE verifications ENABLE ROW LEVEL SECURITY; -- users: select own row only (admins bypass via SET ROLE) CREATE POLICY users_select_own ON users FOR SELECT USING (id = current_setting('app.current_user_id')::uuid AND deleted_at IS NULL); -- builds: public builds visible to all; private builds owner-only CREATE POLICY builds_select ON builds FOR SELECT USING ( is_public = true OR user_id = current_setting('app.current_user_id')::uuid ); CREATE POLICY builds_mutate ON builds FOR ALL USING (user_id = current_setting('app.current_user_id')::uuid); -- user_bikes: owner-only access CREATE POLICY user_bikes_owner ON user_bikes FOR ALL USING (user_id = current_setting('app.current_user_id')::uuid); -- verifications: public reads; write = owner only CREATE POLICY verifications_select ON verifications FOR SELECT USING (true); CREATE POLICY verifications_mutate ON verifications FOR ALL USING (user_id = current_setting('app.current_user_id')::uuid);

⚠

Admin role bypass

Admin users operate under a privileged Postgres role that bypasses RLS (BYPASSRLS privilege or SET ROLE app_admin within transactions). Never expose admin role credentials to frontend or public API paths. Admin mutations are double-logged in audit_log with actor_id set.

Seed Data

Development and staging seed data for testing all major code paths. Production seed contains only the 5 initial retailers and curated bikes/parts — no fake user accounts.

Year	Make	Model	Variant	CC	Category
2020	Kawasaki	Ninja 400	ABS	399	sport
2021	Kawasaki	Ninja 400	—	399	sport
2023	Kawasaki	Z900	ABS	948	naked
2022	Yamaha	MT-07	—	689	naked
2023	Yamaha	MT-07	—	689	naked
2021	Yamaha	R7	—	689	sport
2023	Honda	CB500F	ABS	471	naked
2022	Honda	CB650R	—	649	naked
2023	Honda	Africa Twin	Adventure Sports	1084	adv
2022	Ducati	Monster	—	937	naked
2023	Ducati	Scrambler	Icon	803	cafe
2022	BMW	R1250GS	Adventure	1254	adv
2023	BMW	S1000R	—	999	naked
2021	KTM	390 Duke	—	373	naked
2023	KTM	890 Duke R	—	889	naked
2022	Triumph	Street Triple RS	—	765	naked
2023	Royal Enfield	Meteor 350	—	349	cruiser
2022	Harley-Davidson	Iron 883	—	883	cruiser
2023	Suzuki	GSX-8S	—	776	naked
2022	Aprilia	RS 660	—	659	sport

Name	Slug	Commission	Cookie Days	Tier
RevZilla	revzilla	8.00%	30	verified
Cycle Gear	cycle-gear	6.50%	14	affiliate
Twisted Throttle	twisted-throttle	9.00%	45	verified
Amazon Moto	amazon-moto	4.00%	1	affiliate
BikeBandit	bikebandit	7.50%	30	data

Email	Display Name	Role	Reputation	Badges	Notes
`free@test.motopartpicker.com`	FreeRider	free	0	—	No bikes, no builds. Tests empty states.
`contributor@test.motopartpicker.com`	SpeedWrench	contributor	1250	early_adopter, top_contributor	2 bikes, 1 public build, 23 verifications. Tests all community features.
`admin@test.motopartpicker.com`	SiteAdmin	admin	9999	admin, early_adopter	Bypasses RLS. Tests admin workflows: adding bikes, editing compat records, managing retailers.
`deleted@test.motopartpicker.com`	DeletedUser	deleted	0	—	deleted_at is set. Tests soft-delete display behavior (verifications show "Deleted User").
`retailer@test.motopartpicker.com`	RetailerRep	retailer	0	—	Linked to RevZilla retailer account. Tests retailer dashboard, subscription management.

⚠

Test user passwords

All test accounts use password TestPass123! in development/staging. These accounts are never seeded in production. The seed script checks NODE_ENV !== 'production' before inserting user rows.

MotoPartPicker Data Model v1.0 · Last updated 2026-04-08 · Neon Postgres (serverless) · Drizzle ORM