Seeding strategy

How reference data and demo data are separated so a full DB reset never loses catalog structure

The problem

Reference data (categories, brands, colors, conditions, attributes, option groups, banks, delivery providers, platform settings) and demo data (fake users, items, transactions) were mixed in a single db:seed command. After a full database reset, reference data was missing and had to be manually restored from backups.

The solution

Two separate entry points:

Command	Script	Purpose
`pnpm run db:seed:reference`	`scripts/seed-reference.ts`	Loads all catalog/reference data. Idempotent (skips if exists).
`pnpm run db:seed`	`scripts/seed.ts`	Calls reference seed first, then cleanup + fake demo data.

Running db:seed always runs db:seed:reference first. Running db:seed:reference standalone is safe and fast — it skips every table that already has data.

Reference data tables

Tables with existing seed scripts (moved to reference seed)

Table	Rows	Data source
`banks`	30	Inline TS (`data/banks.ts`)
`brand_categories`	11,544	TS data file (`data/brand-categories.ts`)
`brand_assets`	12	TS data file (`data/brand-assets.ts`)
`carriers`	5	TS data file (`data/delivery-providers.ts`)
`option_value_translations`	1,740	TS data file (`data/option-value-translations.ts`)

Tables with new seed scripts

Table	Rows	Data source
`categories`	2,909	TS data file (`data/categories.ts`)
`category_translations`	4,856	TS data file (`data/category-translations.ts`)
`brands`	6,707	TS data file (`data/brands.ts`)
`colors`	28	TS data file (`data/colors.ts`)
`conditions`	4	TS data file (`data/conditions.ts`)
`attributes`	15	TS data file (`data/attributes.ts`)
`attribute_translations`	30	TS data file (`data/attribute-translations.ts`)
`attribute_sets`	8	TS data file (`data/attribute-sets.ts`)
`attribute_set_attributes`	34	TS data file (`data/attribute-set-attributes.ts`)
`option_groups`	49	TS data file (`data/option-groups.ts`)
`option_values`	870	TS data file (`data/option-values.ts`)
`platform_settings`	1	TS data file (`data/platform-settings.ts`)

FK-safe execution order

The reference seed runner (seed-reference-runner.ts) seeds tables in this order to respect foreign key constraints:

 1. categories              — no deps
 2. brands + brands_vinted  — no deps
 3. brand_categories        — depends on brands + categories
 4. brand_assets            — no deps
 5. colors                  — no deps
 6. conditions              — no deps
 7. option_groups + values  — no deps
 8. attributes + translations + sets + set_attributes
                            — set_attributes FK → option_groups
 9. option_value_translations — depends on option_values
10. banks                   — no deps
11. carriers      — no deps
12. platform_settings       — no deps

Idempotency pattern

Every seed function checks if data already exists before inserting:

const existing = await db
  .select({ count: sql<number>`count(*)` })
  .from(table);

if (Number(existing[0]?.count ?? 0) > 0) {
  console.log(`  Table: ${count} already exist, skipping.`);
  return;
}

For individual row inserts, .onConflictDoNothing() ensures no duplicates.

Identity column handling

Tables with generatedAlwaysAsIdentity() primary keys (categories, brands, colors, etc.) need explicit ID insertion using raw SQL, since Drizzle's ORM layer doesn't allow setting identity columns directly:

await db.execute(sql`
  INSERT INTO categories (id, code, title, ...)
  VALUES (${cat.id}, ${cat.code}, ${cat.title}, ...)
  ON CONFLICT DO NOTHING
`);

// Reset sequence so new inserts get the next ID
await db.execute(sql`
  SELECT setval(
    pg_get_serial_sequence('categories', 'id'),
    (SELECT COALESCE(MAX(id), 0) FROM categories)
  )
`);

Batch processing

Large datasets (1,000+ rows) use batch processing with BATCH_SIZE = 500:

for (let i = 0; i < data.length; i += BATCH_SIZE) {
  const batch = data.slice(i, i + BATCH_SIZE);
  // insert batch...
}

Demo data (unchanged)

These seeds remain in seed/index.ts and are only run by db:seed, not db:seed:reference:

seedUsers() — creates test users in Supabase Auth + DB
seedAddresses() — shipping addresses for test users
seedPagarmeRecipients() — creates provider-owned test recipients and stores only safe payout metadata
seedItems() — items with images, colors, attributes
seedTransactions() — conversations, messages, offers, orders, reviews
seedGroups() — groups with members and invitations
seedPayouts() — payouts for delivered orders
seedSocial() — follows, favorites, cart, notifications
seedSearches() — search history

File structure

packages/db/scripts/
├── seed.ts                        # Entry: db:seed (reference + demo)
├── seed-reference.ts              # Entry: db:seed:reference
└── seed/
    ├── index.ts                   # Demo seed orchestrator
    ├── seed-reference-runner.ts   # Reference seed orchestrator
    ├── seed-categories.ts         # categories + category_translations
    ├── seed-brands.ts             # brands + brands_vinted
    ├── seed-brand-categories.ts   # brand_categories junction
    ├── seed-brand-assets.ts       # brand_assets
    ├── seed-colors.ts             # colors
    ├── seed-conditions.ts         # conditions
    ├── seed-attributes.ts         # attributes + translations + sets + junction
    ├── seed-option-groups.ts      # option_groups + option_values
    ├── seed-option-value-translations.ts
    ├── seed-banks.ts              # banks
    ├── seed-delivery-providers.ts # carriers
    ├── seed-platform-settings.ts  # platform_settings
    ├── seed-users.ts              # demo: users
    ├── seed-addresses.ts          # demo: addresses
    ├── pagarme-recipients.ts      # demo: provider recipients + safe payout metadata
    ├── seed-items.ts              # demo: items
    ├── seed-transactions.ts       # demo: transactions
    ├── seed-groups.ts             # demo: groups
    ├── seed-payouts.ts            # demo: payouts
    ├── seed-social.ts             # demo: social
    ├── seed-searches.ts           # demo: searches
    ├── cleanup.ts                 # demo data cleanup
    └── data/                      # Static data files
        ├── categories.ts          # 2,909 rows
        ├── category-translations.ts # 4,856 rows
        ├── brands.ts              # 6,707 rows
        ├── brands-vinted.ts       # (exported, currently 0 rows)
        ├── brand-categories.ts    # 11,544 rows
        ├── brand-assets.ts        # 12 rows
        ├── colors.ts              # 28 rows
        ├── conditions.ts          # 4 rows
        ├── attributes.ts          # 15 rows
        ├── attribute-translations.ts # 30 rows
        ├── attribute-sets.ts      # 8 rows
        ├── attribute-set-attributes.ts # 34 rows
        ├── option-groups.ts       # 49 rows
        ├── option-values.ts       # 870 rows
        ├── option-value-translations.ts # 1,740 rows
        ├── banks.ts               # 30 rows
        ├── delivery-providers.ts  # 5 rows
        └── platform-settings.ts   # 1 row

Verification

After a full DB reset:

# 1. Seed only reference data
pnpm run db:seed:reference

# 2. Verify counts match
# categories: 2,909 | brands: 6,707 | colors: 28 | conditions: 4
# option_groups: 49 | option_values: 870 | attributes: 15

# 3. Full seed (reference + demo)
pnpm run db:seed

# 4. Run again — reference data skipped, demo re-created
pnpm run db:seed

On this page