I now need to implement the CRON JOB functionality for the Agent Registry approval workflow.
Please use the existing approval reminder and auto-rejection cron-job architecture as much as possible. Do not create a completely separate framework unless the existing design cannot safely support Agent Registry.
CONFIRMED REQUIREMENTS:
Agent Registry:
- module_id = 7
- approval_days_limit = 10 days
Agent Registry action types:
- submit
- edit
- promote
- subscribe
- unsubscribe
All five action types require:
- 7-day reminder
- 1-day/final reminder
- auto-rejection after the approval due date
The due date is:
created_at + 10 days
Use PostgreSQL/database time for the calculation. Do not use Python/server local time for business date calculations.
Existing reminder tracking columns must be reused:
- one_week_reminder_sent_at
- final_reminder_sent_at
Do NOT create a separate reminder tracking table.
==================================================
PART 1 — INSPECT EXISTING CRON IMPLEMENTATION
==================================================
Before changing anything, inspect the existing:
python_jobs/approval_reminder/
python_jobs/auto_reject_approvals/
and:
services/approval/reminder_service.py
services/approval/auto_rejection_service.py
Also inspect the relevant queries in:
utils/pg_queries.py
Understand exactly how the existing Semantic/MCP/InApp KB modules are processed.
Preserve their existing behavior.
==================================================
PART 2 — APPROVAL REMINDER CRON
==================================================
Extend the existing approval reminder job to support Agent Registry.
Agent Registry should be processed for:
- submit
- edit
- promote
- subscribe
- unsubscribe
The reminder flow should be:
Pending approval
↓
Calculate due date using DB
↓
7 days before due date
↓
Send 7-day reminder
↓
Update one_week_reminder_sent_at
↓
1 day before due date
↓
Send final reminder
↓
Update final_reminder_sent_at
Use the existing reminder tracking logic.
The tracking timestamp should only be updated after the email is successfully sent.
If the email fails:
- do not mark the reminder as sent
- log the failure
- allow a later cron execution to retry
Do not send the same reminder repeatedly after the corresponding tracking column is populated.
==================================================
PART 3 — AGENT REGISTRY RECIPIENT
==================================================
Agent Registry currently has its own Champion/admin-group resolution logic.
Reuse:
resolve_admin_group_email()
or the existing Agent Registry recipient-resolution mechanism.
Do not assume the recipient logic used by Semantic/MCP/InApp is correct for Agent Registry.
For each reminder verify that the correct Champion/admin recipient is resolved.
==================================================
PART 4 — REMINDER QUERY
==================================================
Reuse the existing generic reminder SQL where appropriate.
However, verify that it correctly supports:
module_id = 7 / Agent Registry
and all five action types.
The query must correctly identify:
- approval_id
- ref_txn_id
- module_id
- action_type
- created_at
- due_date
- approver/champion information
- reminder tracking fields
Do not silently depend on a missing module-master or approval-limit configuration.
Agent Registry is confirmed as module_id = 7 with a 10-day approval limit.
==================================================
PART 5 — AUTO-REJECTION CRON
==================================================
Extend the existing auto-rejection cron to support Agent Registry.
DO NOT simply reuse the generic:
auto_reject_approval_request
if it only updates:
scdi_common_approval_request_txn
That is insufficient for Agent Registry.
Agent Registry has two related statuses.
For:
submit
edit
promote
update:
scdi_common_approval_request_txn.approval_status
+
scdi_agent_registry_master.status
Both must become REJECTED.
For:
subscribe
unsubscribe
update:
scdi_common_approval_request_txn.approval_status
+
scdi_agent_subscriptions.status
Both must become REJECTED.
==================================================
PART 6 — ATOMIC AUTO-REJECTION
==================================================
This is mandatory.
The two status updates for an Agent Registry request MUST happen in the same database transaction.
Example:
BEGIN
1. Verify approval_status = pending
2. Update scdi_common_approval_request_txn
approval_status = rejected
3. Update the corresponding Agent Registry business record
status = rejected
COMMIT
If either update fails:
ROLLBACK
Both records must remain in their original state.
Do not allow:
approval transaction = REJECTED
Agent Registry record = PENDING
This must never be left as a partial update.
==================================================
PART 7 — CONCURRENCY
==================================================
Protect against a manager manually approving/rejecting while the auto-rejection cron is running.
The auto-rejection update must only operate on:
approval_status = pending
If the request was already approved/rejected:
- do not change it
- do not send auto-rejection email
Follow the existing project's concurrency/status-guard pattern.
==================================================
PART 8 — AUTO-REJECTION EMAIL
==================================================
Only send the auto-rejection email AFTER the database transaction successfully commits.
Flow:
BEGIN
Update approval transaction
Update Agent Registry status
COMMIT
↓
Send auto-rejection email
If DB transaction fails:
ROLLBACK
↓
Do NOT send auto-rejection email
If DB succeeds but email fails:
- request remains REJECTED
- log the email failure
- do not roll back the successful DB transaction
Reuse the existing auto-rejection email template/wording.
Use the correct Agent Registry recipient.
==================================================
PART 9 — ACTION TYPE MAPPING
==================================================
Make the action-type mapping explicit and safe.
submit:
ref_txn_id → scdi_agent_registry_master.id
edit:
ref_txn_id → scdi_agent_registry_master.id
promote:
ref_txn_id → scdi_agent_registry_master.id
subscribe:
ref_txn_id → scdi_agent_subscriptions identifier
unsubscribe:
ref_txn_id → scdi_agent_subscriptions identifier
Do not assume every Agent Registry ref_txn_id is a master_id.
==================================================
PART 10 — WITHDRAW / RESUBMIT
==================================================
Make sure the cron does not process withdrawn/obsolete requests.
For a withdraw + resubmit flow:
- old withdrawn request must not receive reminders
- old withdrawn request must not be auto-rejected
- new pending approval request must receive its own 10-day approval window
- new request must have its own reminder tracking
Use the existing status/active flags to determine eligibility.
==================================================
PART 11 — CRON SCRIPTS
==================================================
Prefer extending the existing:
python_jobs/approval_reminder/approval_reminder.py
and:
python_jobs/auto_reject_approvals/auto_reject_approvals.py
rather than creating unnecessary duplicate cron frameworks.
The existing cron schedules must NOT be changed.
Existing schedules are expected to remain:
Approval reminder:
0 8 * * *
Auto rejection:
30 8 * * *
Do not modify the cron configuration unless explicitly required.
The Python jobs should remain runnable manually for testing.
==================================================
PART 12 — LOGGING
==================================================
Add/use useful logging for Agent Registry.
For reminders log:
- module_id
- approval_id
- ref_txn_id
- action_type
- due_date
- reminder type
- recipient
- email success/failure
- tracking update
For auto-rejection log:
- module_id
- approval_id
- ref_txn_id
- action_type
- due_date
- approval status update
- Agent Registry status update
- transaction commit
- transaction rollback
- email success/failure
Do not log sensitive data.
==================================================
PART 13 — TESTS
==================================================
Add/update tests for the Agent Registry cron functionality.
Reminder tests:
1. submit → 7-day reminder
2. edit → 7-day reminder
3. promote → 7-day reminder
4. subscribe → 7-day reminder
5. unsubscribe → 7-day reminder
Repeat for 1-day/final reminder.
Verify:
- correct request selected
- correct due date
- correct recipient
- email sent
- tracking timestamp updated
- duplicate reminder prevented
Auto-rejection tests:
1. submit
2. edit
3. promote
4. subscribe
5. unsubscribe
Verify:
For submit/edit/promote:
approval txn = REJECTED
agent registry master = REJECTED
For subscribe/unsubscribe:
approval txn = REJECTED
subscription = REJECTED
Also test:
- already approved request
- already rejected request
- withdrawn request
- email failure
- transaction failure
- duplicate cron execution
- concurrent manual decision
==================================================
PART 14 — EXISTING MODULE REGRESSION
==================================================
VERY IMPORTANT:
Do not break existing:
- Semantic
- MCP
- InApp KB
Run the existing reminder and auto-rejection tests.
Confirm that adding Agent Registry does not alter their behavior.
==================================================
PART 15 — IMPLEMENTATION SAFETY
==================================================
Keep the implementation focused.
Do not:
- create a new reminder table
- create duplicate cron frameworks
- change existing cron schedules
- change unrelated modules
- change approval behavior for Semantic/MCP/InApp
- use local machine/server time for due-date calculation
- update Agent Registry approval status without updating its business status
- send auto-rejection email before successful DB commit
Reuse existing patterns wherever possible.
==================================================
FINAL OUTPUT
==================================================
After implementation, provide:
1. Files changed
2. Functions/classes changed
3. SQL/query changes
4. Reminder cron changes
5. Auto-rejection cron changes
6. Agent Registry action-type mapping
7. Transaction/rollback implementation
8. Email changes
9. Tests added/updated
10. Test commands executed
11. Test results
12. Any remaining issues or assumptions
Do not claim tests passed unless they were actually executed and passed.