Email Job Model

sonikagutharequested to merge
phoenix/gsoc/feature/email-job-model into phoenix/gsoc/scalable-email-impn

Description

Introduces the database and model foundation for the new email queue system. Adds the email_jobs table along with the CakePHP entity, table model, validation, test fixture, and unit tests.

Database schema

The table DDL is provided as app/cake/config/schema/email_jobs.sql, following the repository's existing SQL-based schema management approach.The file is applied once against an existing environment:

mariadb -u root cdli_db < app/cake/config/schema/email_jobs.sql

New environments will receive email_jobs via the phoenix_database dump once the DDL is merged there.

The table stores:

  • Job identity: mailer (e.g. User), action (e.g. welcome), payload (JSON text)
  • Lifecycle: status ENUM — pending, processing, sent, failed, cancelled (default pending)
  • Scheduling: priority, attempt_count, max_attempts, next_retry_at
  • Worker locking: locked_at, locked_by
  • Outcome tracking: sent_at, failed_at, last_error
  • Timestamps: created, modified

Three indexes are defined:

Index Columns Purpose
email_jobs_status_next_retry_at (status, next_retry_at) General pending-job lookup
email_jobs_worker_claim (status, priority, next_retry_at, created) Efficient ordered job claiming by the worker
email_jobs_watchdog (status, locked_at) Stuck-job recovery queries

Model layer

  • EmailJob entity — maps one row. Worker-managed lifecycle fields (status, attempt_count, locked_at, locked_by, sent_at, failed_at, last_error) are not mass-assignable, preventing controllers or API callers from forging terminal states via newEntity()/patchEntity(). The worker sets these directly.
  • EmailJobsTable — maps to email_jobs, uses Timestamp behavior, defines status constants (STATUS_PENDING, etc.) to avoid magic strings.

Validation

PHP-level validation via validationDefault():

  • mailer and action required, max 100 chars, must appear in ALLOWED_ACTIONS
  • payload required, must be valid JSON, and must contain all keys declared for the given mailer/action pair
  • user_id in payload must be a positive integer
  • url in payload (where required) must be a properly-formed URL (FILTER_VALIDATE_URL), not just a non-empty string
  • status must be one of the defined constants
  • attempt_count ≥ 0, max_attempts > 0
  • Datetime fields optional where appropriate

Supported actions (ALLOWED_ACTIONS)

All currently used UserMailer actions are enumerated:

Action Required payload keys
welcome user_id
adminNewUser user_id
adminCrowdsourcingPrivilege user_id
resetPassword user_id, url
contributionDeclined user_id, url

Tests

  • EmailJobsFixture — sample pending job for PHPUnit
  • EmailJobsTableTest — covers valid saves, invalid status, invalid JSON, unsupported mailer/action, missing payload keys, invalid user_id, missing/malformed URL, and all five supported actions including contributionDeclined
Edited by sonikagutha

Merge request reports