Build a ColdFusion MX Service Request Tracker

Learn ColdFusion by assembling a realistic database-backed application in controlled milestones.

Learning Objectives

• Configure a datasource and application lifecycle.
• Use explicit scopes and MX-compatible arrays/structures.
• Validate forms and parameterize SQL.
• Separate templates from CFC-based services.
• Use sessions for identity and roles for authorization.
• Log changes and handle errors safely.
• Create a scheduled reminder task.

Directory Layout

support-tracker/
|-- Application.cfc
|-- index.cfm
|-- login.cfm
|-- logout.cfm
|-- customers/
|   |-- index.cfm
|   |-- view.cfm
|   `-- save.cfm
|-- tickets/
|   |-- index.cfm
|   |-- view.cfm
|   |-- create.cfm
|   |-- save.cfm
|   `-- close.cfm
|-- components/
|   |-- CustomerService.cfc
|   |-- TicketService.cfc
|   `-- AuthService.cfc
|-- includes/
|   |-- header.cfm
|   |-- footer.cfm
|   `-- access-check.cfm
|-- tasks/
|   `-- overdue-reminders.cfm
|-- errors/
|   `-- generic.cfm
`-- sql/
    |-- schema.sql
    `-- sample-data.sql

Database Schema

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY,
    email VARCHAR(254) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    roles VARCHAR(255) NOT NULL,
    is_active SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE customers (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE tickets (
    id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    subject VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR(20) NOT NULL,
    priority VARCHAR(20) NOT NULL,
    assigned_user_id INT NULL,
    created_at TIMESTAMP NOT NULL,
    closed_at TIMESTAMP NULL
);

CREATE TABLE ticket_notes (
    id INT NOT NULL PRIMARY KEY,
    ticket_id INT NOT NULL,
    user_id INT NOT NULL,
    note_text TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE audit_log (
    id INT NOT NULL PRIMARY KEY,
    user_id INT NULL,
    event_type VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NOT NULL,
    event_text VARCHAR(500) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

Adjust identity/auto-increment syntax and foreign-key definitions for the database engine used in the lab.

Application.cfc Skeleton MX 7+

<cfcomponent output="false">
    <cfset this.name = "LegacySupportTracker">
    <cfset this.sessionManagement = true>
    <cfset this.sessionTimeout = CreateTimeSpan(0, 0, 30, 0)>
    <cfset this.applicationTimeout = CreateTimeSpan(1, 0, 0, 0)>
    <cfset this.scriptProtect = "all">

    <cffunction name="onApplicationStart" returntype="boolean" output="false">
        <cfset application.dsn = "support_portal">
        <cfset application.supportEmail = "[email protected]">
        <cfreturn true>
    </cffunction>

    <cffunction name="onSessionStart" returntype="void" output="false">
        <cfset session.isAuthenticated = false>
        <cfset session.userId = 0>
        <cfset session.roles = "">
    </cffunction>

    <cffunction name="onRequestStart" returntype="boolean" output="false">
        <cfargument name="targetPage" type="string" required="true">
        <cfset request.correlationId = CreateUUID()>
        <cfreturn true>
    </cffunction>
</cfcomponent>

TicketService.cfc Skeleton

<cfcomponent output="false">

    <cffunction name="init" access="public" returntype="TicketService" output="false">
        <cfargument name="dsn" type="string" required="true">
        <cfset variables.dsn = arguments.dsn>
        <cfreturn this>
    </cffunction>

    <cffunction name="listOpen" access="public" returntype="query" output="false">
        <cfset var tickets = "">

        <cfquery name="tickets" datasource="#variables.dsn#">
            SELECT t.id, t.subject, t.priority, t.created_at,
                   c.name AS customer_name
            FROM tickets t
            INNER JOIN customers c ON c.id = t.customer_id
            WHERE t.status = 'open'
            ORDER BY t.priority, t.created_at
        </cfquery>

        <cfreturn tickets>
    </cffunction>

</cfcomponent>

Build Milestones

1. Read-only customer list. Configure the DSN, query customers, and encode output.
2. Customer create/edit. Add validation, parameterized insert/update, error summary, and redirect.
3. Ticket queue. Join customers and tickets, filter status, and display priority.
4. CFC extraction. Move customer and ticket queries into service components.
5. Authentication. Add login, logout, session initialization, and role checks.
6. Audit history. Record creates, updates, assignments, notes, and closure.
7. Email notification. Send a confirmation after commit, with failure logging.
8. Scheduled reminders. Process overdue tickets without overlapping runs.
9. Error handling. Add correlation IDs, generic user errors, and operator logs.
10. Migration exercise. Run the same app on a supported CFML engine and document differences.

Acceptance Checks

• All dynamic query values use cfqueryparam.
• Every CFC method temporary variable is function-local.
• All state-changing forms have an anti-CSRF control.
• Output containing stored or request data is encoded for its context.
• Authorization is checked on the server for every restricted action.
• Uploaded files, if added, are outside the web root and non-executable.
• Logs identify a request without recording passwords or tokens.
• The database and application can be restored to a fresh lab.