Add Auth Columns to employee_auth
This migration will add new authorization columns to the employee_auth table for all agency databases.
⚠️ Important: This migration will modify the employee_auth table structure for ALL agencies.
Always backup your database before running migrations.
What This Migration Does:
- Adds the following columns after
eaonlinechat:
- r_Auth
- r_Assmt_QA
- r_Sched_Assmt
- r_Sched_Routine
- r_Visit_Freq
- r_Missed_Visit
- r_CP_QA
- r_485_Create
- r_485_QA
- r_485_Send
- r_487_Create
- r_487_QA
- r_487_Send
- r_CP_Track
- r_Admit_Pend
- r_Datasets
- r_Proc
- r_PN_QA
- All columns are INT type, NULL allowed, default NULL
- Applies to all agency databases (agency_*)
SQL to Execute:
-- Add columns after eaonlinechat
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Auth INT NULL DEFAULT NULL AFTER eaonlinechat;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Assmt_QA INT NULL DEFAULT NULL AFTER r_Auth;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Sched_Assmt INT NULL DEFAULT NULL AFTER r_Assmt_QA;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Sched_Routine INT NULL DEFAULT NULL AFTER r_Sched_Assmt;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Visit_Freq INT NULL DEFAULT NULL AFTER r_Sched_Routine;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Missed_Visit INT NULL DEFAULT NULL AFTER r_Visit_Freq;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_CP_QA INT NULL DEFAULT NULL AFTER r_Missed_Visit;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_485_Create INT NULL DEFAULT NULL AFTER r_CP_QA;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_485_QA INT NULL DEFAULT NULL AFTER r_485_Create;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_485_Send INT NULL DEFAULT NULL AFTER r_485_QA;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_487_Create INT NULL DEFAULT NULL AFTER r_485_Send;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_487_QA INT NULL DEFAULT NULL AFTER r_487_Create;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_487_Send INT NULL DEFAULT NULL AFTER r_487_QA;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_CP_Track INT NULL DEFAULT NULL AFTER r_487_Send;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Admit_Pend INT NULL DEFAULT NULL AFTER r_CP_Track;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Datasets INT NULL DEFAULT NULL AFTER r_Admit_Pend;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_Proc INT NULL DEFAULT NULL AFTER r_Datasets;
ALTER TABLE {agency_db}.employee_auth ADD COLUMN r_PN_QA INT NULL DEFAULT NULL AFTER r_Proc;
Migration Progress:
SELECT DISTINCT table_schema as db_name
FROM information_schema.tables
WHERE table_name = 'employee_auth'
AND table_schema LIKE 'agency_%'
AND table_schema NOT LIKE '%lookup%'
ORDER BY table_schema
Found #getAgencies.recordCount# agency database(s) to migrate
Processing #db_name#...
SELECT COUNT(*) as col_exists
FROM information_schema.columns
WHERE table_schema =
AND table_name = 'employee_auth'
AND column_name =
ALTER TABLE #db_name#.employee_auth
ADD COLUMN #columnInfo.name# INT NULL DEFAULT NULL
AFTER #columnInfo.after#
✓ Added column #columnInfo.name# after #columnInfo.after# in #db_name#.employee_auth
ℹ Column #columnInfo.name# already exists in #db_name# - skipping
✗ Failed to add column #columnInfo.name# to #db_name#: #cfcatch.message#
✗ Failed to retrieve agency databases: #cfcatch.message#
🎉 Migration Complete!
- Total Agencies: #getAgencies.recordCount#
- Total Columns Per Agency: #ArrayLen(columnsToAdd)#
- Successfully Added: #totalSuccess#
- Already Existed (Skipped): #totalSkipped#
- Errors: #totalErrors#
The authorization columns have been successfully added to the employee_auth table across all agencies.
Migration complete.
⚠️ Some errors occurred during migration. Please review the error messages above and retry if necessary.
Back to Migrations