backend_fiesta/CATALOGUE_IMPORT_INTEGRATION.md

Store Catalogue Import — Frontend Integration Spec

Backend work is done and verified live. This doc is the handoff: build the Admin Catalogue UI flow (browse global catalogue → choose products → import into a specific store) against the endpoints below.

1. Architecture (why the API looks like this)

There are two separate Postgres databases that never talk to each other directly:

• CatalogueDB (pgvector) — the global catalogue, one table per brand (brand_dabur, brand_nestle, brand_pepsico, brand_sakthi, brand_manna, brand_naga). ~237 products total today.
• nearledb — your tenant/store data (products, productlocations, productstocks).

The backend bridges them using a composite key: (brand, catalogueid). A catalogue row's bare id is only unique within its own brand table — brand_dabur.id=1 and brand_nestle.id=1 are different products. Every call that references a catalogue product must send both brand and catalogueid, never just an id.

When a product is imported, the backend snapshots it into the tenant's own products table (tagged with that brand+catalogueid) and links it to the location via the existing stock/location system. After that, it behaves exactly like a product the tenant created by hand — reading a store's catalogue never touches CatalogueDB again.

2. Endpoints

Base path: /live/api/v1 (replace host with your environment's API host).

Method	Path	Purpose
GET	/web/catalogue/getproducts	Browse the global catalogue. Query: brand (optional — omit to search all brands merged), category, keyword, pageno, pagesize. This is the "show everything" entry point.
GET	/web/catalogue/getbrands	List brands with product counts, for a brand filter chip row.
GET	/web/products/getimportedcatalogueproducts	Query: tenantid (required), brand (optional — omit to check across every brand). Returns [{brand, catalogueid}, …] already imported by this tenant, for badging "Imported" in the browser.
GET	/web/products/getproductsubcategories	Query: tenantid, categoryid. Use to populate the category/subcategory picker shown before import (see §4).
POST	/web/products/importcatalogueproduct	Body is an array — import one or many in a batch. Idempotent: re-importing the same (tenantid, brand, catalogueid) tops up stock and updates price instead of duplicating.
GET	/web/products/getlocationproducts	Query: tenantid, locationid, pageno, pagesize. The store's own catalogue view — what's actually imported.
DELETE	/web/products/deleteproductlocation	Body: tenantid, locationid, productid. Unlinks from the store; keeps the product row and order history intact (safely re-importable after).

Mobile mirrors exist at /mob/products/importcatalogueproduct and /mob/products/getimportedcatalogueproducts if the mobile app needs this flow too.

3. Integration flow

Order matters — each step depends on data fetched in the one before it.

1. Show everything first. Call catalogue/getproducts with no brand. That's the full catalogue, merged and paginated. Don't gate the list behind a brand selector — brand/category/keyword are filters applied on top of an already-visible list, not a prerequisite to seeing it.
2. Mark what's already imported. Call products/getimportedcatalogueproducts?tenantid= (no brand, since the list mixes brands) in parallel with step 1. Build a lookup keyed on `${brand}:${catalogueid}` and badge matching items as "Imported".
3. Collect what the catalogue can't supply. The catalogue has no exact price (only a price_range display string) and no mapping to this tenant's own categories. Before enabling the Import action on a product, require the store owner to pick categoryid/subcategoryid (from getproductsubcategories) and enter retailprice/productcost/taxpercent.
4. Import. POST products/importcatalogueproduct with the batch. On success, invalidate both the imported-refs query and the store-catalogue query.
5. Show it in the store. Refetch products/getlocationproducts — the imported item now appears like any other product, with live stock computed from the stock ledger.
6. Remove, if needed. DELETE products/deleteproductlocation, then invalidate the same two queries as import.

4. Code

TypeScript + TanStack Query (React Query), matching the existing admin app pattern of invalidating queries after mutations.

api/catalogue.ts

const API_BASE = "https://<host>/live/api/v1";

export interface CatalogueProduct {
  id: number;
  brand: string;
  product_name: string;
  category?: string;
  images?: string[];
  size?: string;
  product_sku?: string;
  price_range?: string; // display only — never an exact price
}

// brand omitted → the entire catalogue, all brands merged.
export async function getCatalogueProducts(opts: {
  brand?: string; keyword?: string; pageno?: number; pagesize?: number;
} = {}) {
  const { brand, keyword, pageno = 1, pagesize = 50 } = opts;
  const url = new URL(`${API_BASE}/web/catalogue/getproducts`);
  if (brand) url.searchParams.set("brand", brand);
  if (keyword) url.searchParams.set("keyword", keyword);
  url.searchParams.set("pageno", String(pageno));
  url.searchParams.set("pagesize", String(pagesize));
  const res = await fetch(url);
  const json = await res.json();
  return { products: json.details as CatalogueProduct[], total: json.total as number };
}

export interface ImportedRef { brand: string; catalogueid: number; }

// brand omitted → imported refs across every brand.
export async function getImportedCatalogueRefs(tenantid: number, brand?: string) {
  const url = new URL(`${API_BASE}/web/products/getimportedcatalogueproducts`);
  url.searchParams.set("tenantid", String(tenantid));
  if (brand) url.searchParams.set("brand", brand);
  const res = await fetch(url);
  const json = await res.json();
  const refs = json.details as ImportedRef[];
  return new Set(refs.map((r) => `${r.brand}:${r.catalogueid}`));
}

export interface ImportCatalogueProductRequest {
  tenantid: number;
  locationid: number;
  brand: string;          // bridge key part 1
  catalogueid: number;    // bridge key part 2 — the catalogue row's `id`
  categoryid: number;     // this tenant's own category
  subcategoryid: number;  // this tenant's own subcategory
  quantity: number;
  stocktype: "in" | "out";
  status: string;
  retailprice: number;
  productcost: number;
  taxpercent: number;
}

export async function importCatalogueProducts(items: ImportCatalogueProductRequest[]) {
  const res = await fetch(`${API_BASE}/web/products/importcatalogueproduct`, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(items),
  });
  const json = await res.json();
  if (!json.status) throw new Error(json.message);
  return json;
}

export async function removeFromStoreCatalogue(tenantid: number, locationid: number, productid: number) {
  const res = await fetch(`${API_BASE}/web/products/deleteproductlocation`, {
    method: "DELETE",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ tenantid, locationid, productid }),
  });
  return res.json();
}

hooks/useCatalogueImport.ts

export function useCatalogueProducts(brand?: string, keyword?: string) {
  return useQuery({
    queryKey: ["catalogue", "products", brand ?? "all", keyword ?? ""],
    queryFn: () => getCatalogueProducts({ brand, keyword, pagesize: 100 }),
  });
}

export function useImportedCatalogueRefs(tenantid: number, brand?: string) {
  return useQuery({
    queryKey: ["catalogue", "imported", tenantid, brand ?? "all"],
    queryFn: () => getImportedCatalogueRefs(tenantid, brand),
  });
}

export function useImportCatalogueProduct(tenantid: number, locationid: number) {
  const queryClient = useQueryClient();
  return useMutation({
    mutationFn: (items: ImportCatalogueProductRequest[]) => importCatalogueProducts(items),
    onSuccess: () => {
      queryClient.invalidateQueries({ queryKey: ["catalogue", "imported", tenantid] });
      queryClient.invalidateQueries({ queryKey: ["store-catalogue", tenantid, locationid] });
    },
  });
}

Component usage

// brand starts undefined: the screen opens showing the whole catalogue.
// Selecting a brand chip narrows it — it's a filter, never a gate.
function CatalogueBrowser({ tenantid, locationid }: Props) {
  const [brand, setBrand] = useState<string | undefined>(undefined);
  const { data } = useCatalogueProducts(brand);
  const products = data?.products ?? [];
  const { data: imported = new Set<string>() } = useImportedCatalogueRefs(tenantid);
  const importProduct = useImportCatalogueProduct(tenantid, locationid);

  function handleImport(product: CatalogueProduct, form: ImportForm) {
    importProduct.mutate([{
      tenantid, locationid,
      brand: product.brand,
      catalogueid: product.id,
      categoryid: form.categoryid,
      subcategoryid: form.subcategoryid,
      quantity: form.quantity,
      stocktype: "in",
      status: "Active",
      retailprice: form.retailprice,
      productcost: form.productcost,
      taxpercent: form.taxpercent,
    }]);
  }

  return (
    <ul>
      {products.map((p) => (
        <li key={`${p.brand}:${p.id}`}>
          {p.product_name} ({p.brand})
          {imported.has(`${p.brand}:${p.id}`)
            ? <span className="badge">Imported</span>
            : <ImportButton onImport={(form) => handleImport(p, form)} />}
        </li>
      ))}
    </ul>
  );
}

5. Gotchas

• Always send brand with catalogueid. Ids repeat across brands; either one alone is ambiguous.
• Category/subcategory must already exist for the tenant. There's no automatic mapping from the catalogue's free-text category string to this tenant's categoryid/subcategoryid yet — the UI must require a pick from getproductsubcategories before enabling Import.
• Price is store-set, not catalogue-set. The catalogue only has a price_range display string. retailprice/productcost/taxpercent always come from the store owner's input.
• Re-importing tops up, it doesn't duplicate. Same (tenantid, brand, catalogueid) twice reuses the same product row: quantity adds via the stock ledger, price fields overwrite with whatever was sent that call.
• Delete unlinks, it doesn't erase. The product row (and any order history referencing it) survives; the item becomes instantly re-importable.
• Known brands today: dabur, nestle, pepsico, sakthi, manna, naga — pull the live list from getbrands rather than hardcoding it.

6. Implementation checklist

• API client functions (§4) added to the frontend's API layer, with API_BASE pointed at the real environment host.
• Catalogue browse screen: loads with no brand filter (all products), brand/category/keyword as UI filters on top.
• Already-imported badge wired to getImportedCatalogueRefs, keyed on brand:catalogueid.
• Import action collects categoryid, subcategoryid, retailprice, productcost, taxpercent from the user before enabling submit.
• Import mutation invalidates both the imported-refs query and the store-catalogue query on success.
• Store catalogue screen (getlocationproducts) reflects imports immediately after the above invalidation.
• Remove action wired to deleteproductlocation, same invalidation.