504c07ddbc
Deployment Features: - Production-ready Linux deployment script with automated setup - Standalone database deployment for multi-application access - Environment-based configuration with security hardening - Automated daily backups with 30-day retention - Firewall configuration (UFW/firewalld) - Health checks and monitoring - System-wide management commands Database Schema Updates: - Added upload_directory field to master_assets table - Migration script for existing databases (001_add_upload_directory.sql) - Updated views to include upload_directory - Updated seed data with sample upload paths Script Enhancements: - import_opentext_asset.py now accepts upload_directory parameter - import-asset.sh wrapper updated for upload_directory - Migration application script (apply-migrations.sh) Production Features: - MD5 authentication for MAMP PHP compatibility - Docker Compose production configuration - Automated backup cron job (2 AM daily) - Management scripts: ferrero-db-status.sh, ferrero-db-backup.sh, ferrero-db-restart.sh - Comprehensive deployment logging Documentation: - DEPLOY-LINUX.md - Complete Linux deployment guide - DATABASE-DEPLOYMENT-PACKAGE.md - Quick start guide - Updated README-POSTGRES.md references Security: - Password change instructions - Firewall configuration - Network access restrictions - SSL/TLS recommendations The database can now be deployed as a standalone service on Linux servers and accessed by multiple Ferrero applications for asset tracking. 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
78 lines
2 KiB
SQL
78 lines
2 KiB
SQL
-- Migration: Add upload_directory field to master_assets table
|
|
-- Date: 2025-10-27
|
|
-- Description: Adds upload_directory column to track file system location of master assets
|
|
|
|
-- Check if column already exists
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_name = 'master_assets'
|
|
AND column_name = 'upload_directory'
|
|
) THEN
|
|
-- Add column
|
|
ALTER TABLE master_assets
|
|
ADD COLUMN upload_directory VARCHAR(1000);
|
|
|
|
-- Add comment
|
|
COMMENT ON COLUMN master_assets.upload_directory IS 'File system path where the master asset is stored';
|
|
|
|
RAISE NOTICE 'Column upload_directory added successfully';
|
|
ELSE
|
|
RAISE NOTICE 'Column upload_directory already exists, skipping';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Update view to include new field
|
|
DROP VIEW IF EXISTS v_master_assets_complete CASCADE;
|
|
|
|
CREATE VIEW v_master_assets_complete AS
|
|
SELECT
|
|
ma.id,
|
|
ma.tracking_id,
|
|
ma.opentext_id,
|
|
ma.original_filename,
|
|
ma.file_extension,
|
|
ma.upload_directory,
|
|
ma.original_filename || ma.file_extension as full_filename,
|
|
ma.brand_code,
|
|
ma.brand_name,
|
|
ma.country_code,
|
|
ma.country_name,
|
|
ma.language_code,
|
|
ma.language_name,
|
|
ma.subject_title,
|
|
ma.asset_type,
|
|
ma.asset_type_name,
|
|
ma.duration_seconds,
|
|
ma.aspect_ratio,
|
|
ma.file_size_bytes,
|
|
ma.mime_type,
|
|
ma.width_px,
|
|
ma.height_px,
|
|
ma.tags,
|
|
ma.categories,
|
|
ma.description,
|
|
ma.status,
|
|
ma.created_at,
|
|
ma.updated_at,
|
|
ma.ingested_by,
|
|
COUNT(DISTINCT da.id) as derivative_count
|
|
FROM master_assets ma
|
|
LEFT JOIN derivative_assets da ON ma.id = da.master_asset_id
|
|
WHERE ma.is_deleted = FALSE
|
|
GROUP BY ma.id;
|
|
|
|
-- Verify migration
|
|
SELECT
|
|
CASE
|
|
WHEN EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_name = 'master_assets'
|
|
AND column_name = 'upload_directory'
|
|
)
|
|
THEN '✅ Migration completed successfully'
|
|
ELSE '❌ Migration failed'
|
|
END as migration_status;
|