grabbit
f557c06771
## Database Migrations (18 new) - Migrate all primary keys from SERIAL to UUID - Add soft delete (deleted_at) to all 19 entities - Add missing indexes for performance optimization - Add CHECK constraints for data validation - Add user audit fields (last_login_at, timezone, locale) - Add weather station location fields (latitude, longitude, elevation) - Add foreign key relationships (CameraDevice→Device, ValidatedEvent→WeatherStation) - Prepare private key encryption fields ## Backend Entity Updates - All entities updated with UUID primary keys - Added @DeleteDateColumn for soft delete support - Updated relations and foreign key types ## Backend Service/Controller Updates - Changed ID parameters from number to string (UUID) - Removed ParseIntPipe from controllers - Updated TypeORM queries for string IDs ## Frontend Updates - Updated all service interfaces to use string IDs - Fixed CameraDevice.location as JSONB object - Updated weather.ts with new fields (elevation, timezone) - Added Supabase integration hooks and lib - Fixed chart components for new data structure ## Cleanup - Removed deprecated .claude/agents configuration files 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
90 lines
2.4 KiB
JavaScript
90 lines
2.4 KiB
JavaScript
/**
|
|
* Migration: Link Device to InventoryDevice
|
|
*
|
|
* Fixes: InventoryDevice and Device have no explicit relationship
|
|
* Changes:
|
|
* - Add inventory_device_id FK to devices table
|
|
* - Migrate existing data by matching hardware_id
|
|
*
|
|
* @type {import('node-pg-migrate').ColumnDefinitions | undefined}
|
|
*/
|
|
export const shorthands = undefined;
|
|
|
|
/**
|
|
* @param pgm {import('node-pg-migrate').MigrationBuilder}
|
|
* @param run {() => void | undefined}
|
|
* @returns {Promise<void> | void}
|
|
*/
|
|
export const up = (pgm) => {
|
|
console.log('Linking Device to InventoryDevice...');
|
|
|
|
// Add inventory_device_id column
|
|
pgm.addColumn('devices', {
|
|
inventory_device_id: {
|
|
type: 'uuid',
|
|
notNull: false,
|
|
comment: 'FK to inventory_devices - the inventory record for this device',
|
|
},
|
|
});
|
|
|
|
// Migrate data by matching hardware_id
|
|
pgm.sql(`
|
|
UPDATE devices d
|
|
SET inventory_device_id = inv.id
|
|
FROM inventory_devices inv
|
|
WHERE d.hardware_id = inv.hardware_id;
|
|
`);
|
|
|
|
// Add FK constraint
|
|
pgm.addConstraint('devices', 'devices_inventory_device_id_fkey', {
|
|
foreignKeys: {
|
|
columns: 'inventory_device_id',
|
|
references: 'inventory_devices(id)',
|
|
onDelete: 'SET NULL',
|
|
},
|
|
});
|
|
|
|
// Add unique constraint - one device per inventory record
|
|
pgm.createIndex('devices', 'inventory_device_id', {
|
|
name: 'idx_devices_inventory_device_id_unique',
|
|
unique: true,
|
|
where: 'inventory_device_id IS NOT NULL',
|
|
});
|
|
|
|
// Update inventory_devices.is_claimed based on linked devices
|
|
pgm.sql(`
|
|
UPDATE inventory_devices inv
|
|
SET is_claimed = true
|
|
WHERE EXISTS (
|
|
SELECT 1 FROM devices d WHERE d.inventory_device_id = inv.id
|
|
);
|
|
`);
|
|
|
|
console.log('Device to InventoryDevice link added.');
|
|
};
|
|
|
|
/**
|
|
* @param pgm {import('node-pg-migrate').MigrationBuilder}
|
|
* @param run {() => void | undefined}
|
|
* @returns {Promise<void> | void}
|
|
*/
|
|
export const down = (pgm) => {
|
|
console.log('Rolling back Device to InventoryDevice link...');
|
|
|
|
// Drop unique index
|
|
pgm.dropIndex('devices', 'inventory_device_id', {
|
|
name: 'idx_devices_inventory_device_id_unique',
|
|
ifExists: true,
|
|
});
|
|
|
|
// Drop FK constraint
|
|
pgm.dropConstraint('devices', 'devices_inventory_device_id_fkey', {
|
|
ifExists: true,
|
|
});
|
|
|
|
// Drop column
|
|
pgm.dropColumn('devices', 'inventory_device_id', { ifExists: true });
|
|
|
|
console.log('Device to InventoryDevice link rollback complete.');
|
|
};
|