Spaces:

cuatrolabs
/

cuatrolabs-scm-ms

Runtime error

cuatrolabs-scm-ms / docs /database /migrations /migration_add_inventory_column.sql

feat(catalogues/pricing): standardize pricing level keys to lowercase format

b3b8847 11 months ago

886 Bytes

-- Migration: Add inventory JSON column to catalogue_ref table
-- Date: 2025-12-19
-- Description: Add inventory column to store multi-level inventory configurations from MongoDB

-- Add inventory column as JSONB type (better performance and indexing support)
ALTER TABLE trans.catalogue_ref 
ADD COLUMN IF NOT EXISTS inventory JSONB;

-- Add comment to document the column
COMMENT ON COLUMN trans.catalogue_ref.inventory IS 'Multi-level inventory configuration with ncnf, cnf, distributor, and retail levels';

-- Create index on inventory column for better query performance
CREATE INDEX IF NOT EXISTS idx_catalogue_ref_inventory 
ON trans.catalogue_ref USING GIN (inventory);

-- Verify the column was added
SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_schema = 'trans' 
  AND table_name = 'catalogue_ref' 
  AND column_name = 'inventory';

	-- Migration: Add inventory JSON column to catalogue_ref table
	-- Date: 2025-12-19
	-- Description: Add inventory column to store multi-level inventory configurations from MongoDB

	-- Add inventory column as JSONB type (better performance and indexing support)
	ALTER TABLE trans.catalogue_ref
	ADD COLUMN IF NOT EXISTS inventory JSONB;

	-- Add comment to document the column
	COMMENT ON COLUMN trans.catalogue_ref.inventory IS 'Multi-level inventory configuration with ncnf, cnf, distributor, and retail levels';

	-- Create index on inventory column for better query performance
	CREATE INDEX IF NOT EXISTS idx_catalogue_ref_inventory
	ON trans.catalogue_ref USING GIN (inventory);

	-- Verify the column was added
	SELECT column_name, data_type, is_nullable
	FROM information_schema.columns
	WHERE table_schema = 'trans'
	AND table_name = 'catalogue_ref'
	AND column_name = 'inventory';