appserver/database/init/03-rename-system-tables.sql

-- Migration: Rename system tables with __sys_ prefix
-- This separates internal platform tables from user-created tables

-- Step 1: Drop foreign key constraints
ALTER TABLE api_keys DROP CONSTRAINT IF EXISTS api_keys_created_by_fkey;
ALTER TABLE applications DROP CONSTRAINT IF EXISTS applications_created_by_fkey;
ALTER TABLE deployments DROP CONSTRAINT IF EXISTS deployments_application_id_fkey;
ALTER TABLE audit_logs DROP CONSTRAINT IF EXISTS audit_logs_user_id_fkey;
ALTER TABLE sessions DROP CONSTRAINT IF EXISTS sessions_user_id_fkey;

-- Step 2: Rename tables
ALTER TABLE users RENAME TO __sys_users;
ALTER TABLE sessions RENAME TO __sys_sessions;
ALTER TABLE api_keys RENAME TO __sys_api_keys;
ALTER TABLE applications RENAME TO __sys_applications;
ALTER TABLE deployments RENAME TO __sys_deployments;
ALTER TABLE audit_logs RENAME TO __sys_audit_logs;

-- Step 3: Recreate foreign key constraints with new table names
ALTER TABLE __sys_api_keys
  ADD CONSTRAINT __sys_api_keys_created_by_fkey
  FOREIGN KEY (created_by) REFERENCES __sys_users(id) ON DELETE SET NULL;

ALTER TABLE __sys_applications
  ADD CONSTRAINT __sys_applications_created_by_fkey
  FOREIGN KEY (created_by) REFERENCES __sys_users(id) ON DELETE SET NULL;

ALTER TABLE __sys_deployments
  ADD CONSTRAINT __sys_deployments_application_id_fkey
  FOREIGN KEY (application_id) REFERENCES __sys_applications(id) ON DELETE CASCADE;

ALTER TABLE __sys_audit_logs
  ADD CONSTRAINT __sys_audit_logs_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES __sys_users(id) ON DELETE SET NULL;

ALTER TABLE __sys_sessions
  ADD CONSTRAINT __sys_sessions_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES __sys_users(id) ON DELETE CASCADE;

-- Step 4: Rename indexes
ALTER INDEX IF EXISTS users_pkey RENAME TO __sys_users_pkey;
ALTER INDEX IF EXISTS idx_users_email RENAME TO __sys_idx_users_email;
ALTER INDEX IF EXISTS idx_users_email_verified RENAME TO __sys_idx_users_email_verified;
ALTER INDEX IF EXISTS users_email_key RENAME TO __sys_users_email_key;

ALTER INDEX IF EXISTS sessions_pkey RENAME TO __sys_sessions_pkey;
ALTER INDEX IF EXISTS idx_sessions_user_id RENAME TO __sys_idx_sessions_user_id;
ALTER INDEX IF EXISTS idx_sessions_token_hash RENAME TO __sys_idx_sessions_token_hash;

ALTER INDEX IF EXISTS api_keys_pkey RENAME TO __sys_api_keys_pkey;
ALTER INDEX IF EXISTS api_keys_key_hash_key RENAME TO __sys_api_keys_key_hash_key;

ALTER INDEX IF EXISTS applications_pkey RENAME TO __sys_applications_pkey;
ALTER INDEX IF EXISTS applications_slug_key RENAME TO __sys_applications_slug_key;
ALTER INDEX IF EXISTS idx_applications_status RENAME TO __sys_idx_applications_status;

ALTER INDEX IF EXISTS deployments_pkey RENAME TO __sys_deployments_pkey;
ALTER INDEX IF EXISTS idx_deployments_app_id RENAME TO __sys_idx_deployments_app_id;

ALTER INDEX IF EXISTS audit_logs_pkey RENAME TO __sys_audit_logs_pkey;
ALTER INDEX IF EXISTS idx_audit_logs_user_id RENAME TO __sys_idx_audit_logs_user_id;
ALTER INDEX IF EXISTS idx_audit_logs_created_at RENAME TO __sys_idx_audit_logs_created_at;

-- Step 5: Drop and recreate triggers with new table names
DROP TRIGGER IF EXISTS update_users_updated_at ON __sys_users;
DROP TRIGGER IF EXISTS update_applications_updated_at ON __sys_applications;

CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON __sys_users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_applications_updated_at BEFORE UPDATE ON __sys_applications
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Verification queries (commented out)
-- SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;