Build a product catalog CSV importer with SKU validation

create table public.csvjetty_products (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references auth.users(id) on delete cascade,
  sku text not null check (sku ~ '^[A-Z0-9][A-Z0-9_-]{0,63}$'),
  name text not null check (length(name) between 1 and 200 and name = btrim(name)),
  price_cents integer not null check (price_cents between 0 and 1000000000),
  stock integer not null check (stock between 0 and 1000000000),
  unique (owner_id, sku)
);
alter table public.csvjetty_products enable row level security;
revoke all on public.csvjetty_products from anon, authenticated;
grant select, insert on public.csvjetty_products to authenticated;
create policy "Read own catalog" on public.csvjetty_products
for select to authenticated using ((select auth.uid()) = owner_id);
create policy "Insert own products" on public.csvjetty_products
for insert to authenticated with check ((select auth.uid()) = owner_id);

npm install csvjetty @supabase/supabase-js

"use client";
import { useState } from "react";
import CSVJetty from "csvjetty";
import { parseProduct } from "./product-validation";

export default function ProductCatalogImport({ supabase, importerKey = "YOUR_IMPORTER_KEY" }) {
  const [status, setStatus] = useState("");
  return (
    <section>
      <h2>Add products to your catalog</h2>
      <CSVJetty
        importerKey={importerKey}
        accentColor="#2563eb"
        columns={[
          { name: "sku", label: "SKU", type: "text", required: true,
            validate: value => /^[A-Z0-9][A-Z0-9_-]{0,63}$/i.test(String(value).trim()) || "Enter a valid SKU." },
          { name: "name", label: "Product name", type: "text", required: true },
          { name: "price", label: "Price", type: "text", required: true,
            validate: value => /^\d{1,8}(\.\d{1,2})?$/.test(String(value).trim()) || "Use a price such as 12.50." },
          { name: "stock", label: "Stock", type: "number", required: true },
        ]}
        onData={async ({ rows, rowNumbers }) => {
          const { data: { user }, error: authError } = await supabase.auth.getUser();
          if (authError || !user) throw new Error("Sign in before importing.");
          const errors = [];
          let inserted = 0;
          for (let offset = 0; offset < rows.length; offset += 250) {
            const unique = new Map();
            rows.slice(offset, offset + 250).forEach((row, index) => {
              try {
                const product = parseProduct(row);
                if (!unique.has(product.sku)) unique.set(product.sku, { ...product, owner_id: user.id });
              } catch (issue) {
                errors.push({ row: rowNumbers[offset + index], message: issue.message });
              }
            });
            if (!unique.size) continue;
            const { data, error } = await supabase.from("csvjetty_products")
              .upsert([...unique.values()], { onConflict: "owner_id,sku", ignoreDuplicates: true })
              .select("id");
            if (error) throw new Error("Could not save this batch. Earlier batches may already be saved; retry to insert missing SKUs.");
            inserted += data.length;
          }
          setStatus(`Last acknowledged callback: ${inserted} new products, ${errors.length} rejected rows. Existing SKUs were kept.`);
          return { errors };
        }}
      >Import product CSV</CSVJetty>
      <p role="status">{status}</p>
    </section>
  );
}

export function parseProduct(row) {
  const sku = typeof row?.sku === "string" ? row.sku.trim().toUpperCase() : "";
  const name = typeof row?.name === "string" ? row.name.trim() : "";
  const price = String(row?.price ?? "").trim();
  const quantity = String(row?.stock ?? "").trim();
  if (!/^[A-Z0-9][A-Z0-9_-]{0,63}$/.test(sku)) throw new Error("Use a SKU of 1–64 letters, digits, underscores or hyphens.");
  if (!name || name.length > 200) throw new Error("Enter a product name up to 200 characters.");
  if (!/^\d{1,8}(\.\d{1,2})?$/.test(price)) throw new Error("Use a nonnegative decimal price, for example 12.50, without currency symbols.");
  const [whole, decimal = ""] = price.split(".");
  const priceCents = Number(whole) * 100 + Number(decimal.padEnd(2, "0"));
  if (priceCents > 1000000000) throw new Error("Price must be at most 10000000.00.");
  if (!/^\d{1,10}$/.test(quantity) || Number(quantity) > 1000000000) throw new Error("Stock must be a whole number from 0 to 1000000000.");
  return { sku, name, price_cents: priceCents, stock: Number(quantity) };
}

select sku, name, price_cents, stock
from public.csvjetty_products
where owner_id = auth.uid()
order by sku;