Inventory Sync Service

Problem/Feature Description

A retail company receives a daily product feed from their warehouse system. The feed is a JSON array containing 500-5000 products. Each product has a SKU (unique identifier), name, category, quantity in stock, and wholesale price. The feed contains both new products and updates to existing ones -- there is no way to tell which are new and which are updates just by looking at the data.

The company's backend uses TypeScript with Drizzle ORM and SQLite. They need a sync module that processes the daily feed: for each product, it should be added to the database if new, or have its details updated if it already exists. The sync must be reliable -- if it fails partway through, the database should not be left in an inconsistent state with some products updated and others not.

After the sync completes, the system needs to record a sync log entry with the timestamp and the count of products processed.

The existing schema has a products table with columns: id, sku (unique), name, category, quantity, wholesalePriceCents, createdAt, updatedAt. There is also a sync_logs table with: id, syncedAt, productCount.

Output Specification

Produce:

• src/db/schema.ts -- Drizzle schema for products and sync_logs tables
• src/db/sync.ts -- The sync function that processes the product feed
• src/db/db.ts -- Database connection setup

The sync function should be exported as syncProducts(products: ProductFeed[]): Promise<SyncResult> where ProductFeed has sku, name, category, quantity, wholesalePriceCents fields.

The following starter file is provided:

=============== FILE: src/db/schema.ts =============== import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'; import { sql } from 'drizzle-orm';

export const products = sqliteTable('products', { id: integer('id').primaryKey({ autoIncrement: true }), sku: text('sku').notNull().unique(), name: text('name').notNull(), category: text('category').notNull(), quantity: integer('quantity').notNull(), wholesalePriceCents: integer('wholesale_price_cents').notNull(), createdAt: text('created_at').default(sql(datetime('now'))).notNull(), updatedAt: text('updated_at').default(sql(datetime('now'))).notNull(), });

export const syncLogs = sqliteTable('sync_logs', { id: integer('id').primaryKey({ autoIncrement: true }), syncedAt: text('synced_at').default(sql(datetime('now'))).notNull(), productCount: integer('product_count').notNull(), });