feat: station_catalog_category migration, seed data, and ETL mapping

[GitAddRemote]

User Story

As a developer, I need the `station_catalog_category` table created and seeded with the full CIG taxonomy tree so that catalog entries can be correctly classified.

Background

Part of the finalized inventory and catalog schema. See `docs/schema/inventory-catalog-design.md` for full design rationale. Seed data was derived from live UEX API endpoints (`/categories`, `/commodities`, `/vehicles`, `/items`) and cross-referenced against CIG's in-game taxonomy.

Definition of Done

• Migration creates `station_catalog_category` table with all columns
• Migration seeds all rows listed in the seed data section below with correct `path`, `path_ids`, `depth`, and `sort_order`
• A DB trigger or service method updates `path`, `path_ids`, `depth` when a category is inserted or re-parented
• Migration `down()` drops the table and trigger cleanly
• `pnpm migration:revert` tested — rolls back cleanly
• TypeORM entity created and registered in `data-source.ts`
• All existing tests pass, no regressions

Schema

Column	Type	Nullable	Default	Notes
`id`	UUID	NO	UUIDv7	PK
`parent_id`	UUID	YES	NULL	FK → self; NULL = root node
`name`	VARCHAR(100)	NO
`slug`	VARCHAR(100)	NO		UNIQUE
`path`	TEXT	NO		Materialized dot-path e.g. `vehicle.ship.fighter`
`path_ids`	UUID[]	NO		Materialized ancestor UUID array
`depth`	INTEGER	NO	0	0 = root node
`description`	TEXT	YES	NULL
`sort_order`	INTEGER	NO	0	Display order within siblings
`is_active`	BOOLEAN	NO	TRUE
`is_locally_managed`	BOOLEAN	NO	FALSE	ETL skips when TRUE
`created_at`	TIMESTAMPTZ	NO	now()
`updated_at`	TIMESTAMPTZ	NO	now()

Seed Data

Seed all rows in the migration `up()`. `path_ids` is populated by the trigger/service — seed only needs to provide `name`, `slug`, `parent_id` (resolved by slug), and `sort_order`.

Root nodes (depth 0)

name	slug	sort_order
Vehicle	vehicle	1
Item	item	2
Commodity	commodity	3

Vehicle (depth 1–2)

name	slug	path	depth	parent_slug
Ship	ship	vehicle.ship	1	vehicle
Ground Vehicle	ground-vehicle	vehicle.ground-vehicle	1	vehicle
Add-on / Module	addon-module	vehicle.addon-module	1	vehicle
Starter	starter	vehicle.ship.starter	2	ship
Civilian	civilian	vehicle.ship.civilian	2	ship
Exploration	exploration	vehicle.ship.exploration	2	ship
Cargo	cargo	vehicle.ship.cargo	2	ship
Mining	mining	vehicle.ship.mining	2	ship
Industrial	industrial	vehicle.ship.industrial	2	ship
Military	military	vehicle.ship.military	2	ship
Medical	medical	vehicle.ship.medical	2	ship
Salvage	salvage	vehicle.ship.salvage	2	ship
Racing	racing	vehicle.ship.racing	2	ship
Passenger	passenger	vehicle.ship.passenger	2	ship
Science & Research	science-research	vehicle.ship.science-research	2	ship
Support	support	vehicle.ship.support	2	ship
Stealth	stealth	vehicle.ship.stealth	2	ship
Data Running	data-running	vehicle.ship.data-running	2	ship

Item (depth 1)

name	slug	path	depth	parent_slug
Armor	armor	item.armor	1	item
Clothing	clothing	item.clothing	1	item
Personal Weapons	personal-weapons	item.personal-weapons	1	item
Ship Components	ship-components	item.ship-components	1	item
Avionics	avionics	item.avionics	1	item
Propulsion	propulsion	item.propulsion	1	item
Vehicle Weapons	vehicle-weapons	item.vehicle-weapons	1	item
Utility	utility	item.utility	1	item
Liveries	liveries	item.liveries	1	item
Miscellaneous	miscellaneous	item.miscellaneous	1	item
Technology	technology	item.technology	1	item
Decorations	decorations	item.decorations	1	item
Flair	flair	item.flair	1	item
Undersuits	undersuits	item.undersuits	1	item
Other	other	item.other	1	item

Item (depth 2)

name	slug	path	depth	parent_slug
Arms	arms	item.armor.arms	2	armor
Backpacks	backpacks	item.armor.backpacks	2	armor
Helmets	helmets	item.armor.helmets	2	armor
Legs	legs	item.armor.legs	2	armor
Torso	torso	item.armor.torso	2	armor
Full Set	armor-full-set	item.armor.armor-full-set	2	armor
Footwear	footwear	item.clothing.footwear	2	clothing
Gloves	gloves	item.clothing.gloves	2	clothing
Hats	hats	item.clothing.hats	2	clothing
Jackets	jackets	item.clothing.jackets	2	clothing
Legwear	legwear	item.clothing.legwear	2	clothing
Shirts	shirts	item.clothing.shirts	2	clothing
Eyewear	eyewear	item.clothing.eyewear	2	clothing
Full Set	clothing-full-set	item.clothing.clothing-full-set	2	clothing
Attachments	attachments	item.personal-weapons.attachments	2	personal-weapons
Weapons	weapons	item.personal-weapons.weapons	2	personal-weapons
Coolers	coolers	item.ship-components.coolers	2	ship-components
Power Plants	power-plants	item.ship-components.power-plants	2	ship-components
Quantum Drives	quantum-drives	item.ship-components.quantum-drives	2	ship-components
Shield Generators	shield-generators	item.ship-components.shield-generators	2	ship-components
Life Support	life-support	item.ship-components.life-support	2	ship-components
Flight Blade	flight-blade	item.avionics.flight-blade	2	avionics
Radar	radar	item.avionics.radar	2	avionics
Jump Modules	jump-modules	item.propulsion.jump-modules	2	propulsion
Guns	guns	item.vehicle-weapons.guns	2	vehicle-weapons
Missile Racks	missile-racks	item.vehicle-weapons.missile-racks	2	vehicle-weapons
Missiles	missiles	item.vehicle-weapons.missiles	2	vehicle-weapons
Turrets	turrets	item.vehicle-weapons.turrets	2	vehicle-weapons
Bombs	bombs	item.vehicle-weapons.bombs	2	vehicle-weapons
Bomb Racks	bomb-racks	item.vehicle-weapons.bomb-racks	2	vehicle-weapons
Point Defense	point-defense	item.vehicle-weapons.point-defense	2	vehicle-weapons
Docking Collars	docking-collars	item.utility.docking-collars	2	utility
External Fuel Tanks	external-fuel-tanks	item.utility.external-fuel-tanks	2	utility
Gadgets	gadgets	item.utility.gadgets	2	utility
Mining Laser Heads	mining-laser-heads	item.utility.mining-laser-heads	2	utility
Mining Modules	mining-modules	item.utility.mining-modules	2	utility
Scraper Beams	scraper-beams	item.utility.scraper-beams	2	utility
Tractor Beams	tractor-beams	item.utility.tractor-beams	2	utility
Containers	containers	item.utility.containers	2	utility
Fabricators	fabricators	item.utility.fabricators	2	utility
Salvage Beams	salvage-beams	item.utility.salvage-beams	2	utility
Consumables	consumables	item.miscellaneous.consumables	2	miscellaneous
Drinks	drinks	item.miscellaneous.drinks	2	miscellaneous
Foods	foods	item.miscellaneous.foods	2	miscellaneous
General	general	item.miscellaneous.general	2	miscellaneous
MobiGlas Apps	mobiglas-apps	item.technology.mobiglas-apps	2	technology
Surface	surface	item.flair.surface	2	flair

Commodity (depth 1)

name	slug	path	depth	parent_slug
Metal	metal	commodity.metal	1	commodity
Mineral	mineral	commodity.mineral	1	commodity
Gas	gas	commodity.gas	1	commodity
Halogen	halogen	commodity.halogen	1	commodity
Non-Metal	non-metal	commodity.non-metal	1	commodity
Raw Material	raw-material	commodity.raw-material	1	commodity
Synthetic Material	synthetic-material	commodity.synthetic-material	1	commodity
Drug	drug	commodity.drug	1	commodity
Vice	vice	commodity.vice	1	commodity
Food & Drink	food-drink	commodity.food-drink	1	commodity
Agricultural	agricultural	commodity.agricultural	1	commodity
Medical Supply	medical-supply	commodity.medical-supply	1	commodity
Natural	natural	commodity.natural	1	commodity
Scrap	scrap	commodity.scrap	1	commodity
Ammunition	ammunition	commodity.ammunition	1	commodity
Fuel	fuel	commodity.fuel	1	commodity
Explosive	explosive	commodity.explosive	1	commodity
Organic	organic	commodity.organic	1	commodity
Chemical	chemical	commodity.chemical	1	commodity
Electronics	electronics	commodity.electronics	1	commodity
Crafting	crafting	commodity.crafting	1	commodity
Animal Product	animal-product	commodity.animal-product	1	commodity
Seasonal	seasonal	commodity.seasonal	1	commodity
Waste	waste	commodity.waste	1	commodity
Other	other-commodity	commodity.other-commodity	1	commodity

Technical Notes

• Production database: The production PostgreSQL instance runs in an existing Docker container — do not create a new container or database. Migrations run against the existing instance via pnpm migration:run.

• Seed rows must be inserted in parent-before-child order so FK constraints don't fail

• `path_ids` should be populated by a recursive CTE or trigger at insert time — do not hardcode UUID arrays in the seed

• The trigger fires on INSERT and UPDATE of `parent_id` only — category moves are rare but must be handled

• `sort_order` within each sibling group should reflect natural display order (alphabetical is acceptable for initial seed)

Related Issues

• ISSUE-184 — creates `station_uex_category_map` which FKs to this table (depends on this issue)
• ISSUE-277 — `station_catalog_entry` migration (depends on this issue)
• ISSUE-282 — super admin UI for managing mappings (depends on this issue, v0.5.0)
• `docs/schema/inventory-catalog-design.md`