no-mistakes(review): Extend ensure_legacy_schema to add tickets.reported_at with backfill

Wahab can now enter historical tickets that keep their true reported date:

- Add nullable tickets.reported_at (DateTime) via alembic d5e0f2a1c3b4;
  backfill existing rows from created_at so nothing shows empty.
- TicketCreate.reported_at (optional) is persisted by create_ticket and
  defaults to now when omitted, so existing create behavior is unchanged.
- New-issue form gains a 'Reported Date' date picker (defaults to today,
  past dates allowed, future blocked) and sends reported_at in the payload.
- List and detail pages show 'Reported' next to 'Created' (date-only,
  labeled) so backdated tickets are obvious; SLA deadline is unchanged and
  still runs from creation time so backfilling never instantly breaches.
- Tests: backdated create persists + is exposed on list/detail; omitted
  reported_at defaults to now; SLA window unchanged; full datetime round trip.

AGENTS.md

@@ -94,6 +94,11 @@ Frontend: Alpine.js (CDN) + Tailwind CSS (CDN). Auth state in localStorage. Role
 
 ## Ticket System (Sprint 2)
 
+- `reported_at` (nullable DateTime, alembic `d5e0f2a1c3b4`) records a ticket's original reported date;
+  `TicketCreate.reported_at` lets Admin/Wahab enter backdated tickets that stay active. It defaults to
+  now when omitted (migration backfilled existing rows from `created_at`). SLA deadlines run from
+  `created_at`, not `reported_at` — backfilling history never instantly breaches a ticket.
+
 ### Status Lifecycle (16 statuses)
 New → Logged → Triage → Assigned → Accepted → Travelling → On Site → In Progress → Waiting Parts → Escalated → Completed → On-Field Verification → Wahab Review → Closed → Reopened → Cancelled
 

alembic/versions/d5e0f2a1c3b4_tickets_reported_at.py

@@ -0,0 +1,47 @@
+"""tickets.reported_at column — original reported date for backdated tickets
+
+Revision ID: d5e0f2a1c3b4
+Revises: c4e8f1a2d3b4
+Create Date: 2026-08-03 00:00:00.000000
+
+Backdated-ticket support (Wahab demo): one schema change.
+
+* Add ``tickets.reported_at`` (nullable DateTime) so historical/backfilled
+  tickets keep their true report date instead of inheriting today's
+  ``created_at``.
+* Backfill existing rows with their ``created_at`` value so no ticket shows
+  an empty reported date after the upgrade. The service layer also defaults
+  new tickets without a ``reported_at`` to now, so the column is effectively
+  always populated from here on.
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'd5e0f2a1c3b4'
+down_revision: Union[str, Sequence[str], None] = 'c4e8f1a2d3b4'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    """Add tickets.reported_at (nullable) and backfill from created_at."""
+    op.add_column(
+        'tickets',
+        sa.Column(
+            'reported_at',
+            sa.DateTime(),
+            nullable=True,
+            comment='Original reported date; backdated/backfilled tickets keep their true report date',
+        ),
+    )
+    # Backfill: every existing ticket was reported when it was created.
+    op.execute('UPDATE tickets SET reported_at = created_at WHERE reported_at IS NULL')
+
+
+def downgrade() -> None:
+    """Drop tickets.reported_at."""
+    op.drop_column('tickets', 'reported_at')

app/main.py

@@ -38,6 +38,14 @@ async def ensure_legacy_schema(conn) -> None:
                 text("ALTER TABLE tickets ADD COLUMN phone VARCHAR(50)")
             )
             logger.info("Added missing tickets.phone column (legacy database)")
+        if "reported_at" not in ticket_columns:
+            await conn.execute(
+                text("ALTER TABLE tickets ADD COLUMN reported_at DATETIME")
+            )
+            await conn.execute(
+                text("UPDATE tickets SET reported_at = created_at WHERE reported_at IS NULL")
+            )
+            logger.info("Added missing tickets.reported_at column (legacy database)")
     result = await conn.execute(
         text(
             "UPDATE categories SET name = 'Missing Item' "

app/models/ticket.py

@@ -55,6 +55,11 @@ class Ticket(Base):
     customer_rating: Mapped[int | None] = mapped_column(Integer, nullable=True)
     reopen_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
     closed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
+    reported_at: Mapped[datetime | None] = mapped_column(
+        DateTime,
+        nullable=True,
+        comment="Original reported date. Backdated/backfilled tickets keep their true report date; NULL falls back to created_at.",
+    )
     created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), nullable=False)
     updated_at: Mapped[datetime] = mapped_column(
         DateTime,

app/schemas/ticket.py

@@ -46,6 +46,7 @@ class TicketCreate(BaseModel):
     assigned_to: int | None = None
     customer_name: str | None = None
     phone: str | None = None
+    reported_at: datetime | None = None  # original report date for backdated/backfilled tickets; defaults to now when omitted
 
 
 class TicketUpdate(BaseModel):
@@ -100,6 +101,7 @@ class TicketBrief(BaseModel):
     description: str | None = None
     sla_deadline: datetime | None = None
     reopen_count: int = 0
+    reported_at: datetime | None = None
     created_at: datetime
     updated_at: datetime
 

app/services/ticket.py

@@ -114,6 +114,10 @@ async def create_ticket(
     ticket_number = await _generate_ticket_number(db)
     priority = data.get("priority")
     sla_deadline = compute_sla_deadline(priority) if priority else None
+    # Original report date: backdated/backfilled tickets keep their true date;
+    # when omitted the ticket is considered reported right now. The SLA clock
+    # is unchanged — deadlines run from creation time, not the reported date.
+    reported_at = data.get("reported_at") or datetime.now(timezone.utc)
 
     ticket = Ticket(
         ticket_number=ticket_number,
@@ -126,6 +130,7 @@ async def create_ticket(
         reported_via=data.get("reported_via"),
         description=data.get("description"),
         assigned_to=data.get("assigned_to"),
+        reported_at=reported_at,
         sla_deadline=sla_deadline,
     )
     db.add(ticket)

app/templates/tickets/detail.html

@@ -23,7 +23,11 @@
                             <span class="px-3 py-1 rounded-full text-sm font-medium" :class="statusClass(ticket.status)" x-text="ticket.status"></span>
                             <span class="px-3 py-1 rounded text-sm font-medium" :class="priorityClass(ticket.priority)" x-text="priorityBadge(ticket.priority)"></span>
                         </div>
-                        <p class="text-gray-500 mt-1">Created <span x-text="formatDate(ticket.created_at)"></span></p>
+                        <p class="text-gray-500 mt-1">Created <span x-text="formatDate(ticket.created_at)"></span>
+                            <template x-if="ticket.reported_at && formatDateShort(ticket.reported_at) !== formatDateShort(ticket.created_at)">
+                                <span> · Reported <span class="font-medium text-gray-600" x-text="formatDateShort(ticket.reported_at)"></span></span>
+                            </template>
+                        </p>
                     </div>
                     <div class="flex items-center space-x-2">
                         <button @click="showStatusModal = true" class="px-4 py-2 bg-denya-600 text-white rounded-lg hover:bg-denya-700 transition text-sm font-medium">Update Status</button>
@@ -129,6 +133,10 @@
                                 <dt class="text-sm text-gray-500">Reported Via</dt>
                                 <dd class="text-sm font-medium text-gray-900 capitalize" x-text="ticket.reported_via || '—'"></dd>
                             </div>
+                            <div class="flex justify-between">
+                                <dt class="text-sm text-gray-500">Reported</dt>
+                                <dd class="text-sm font-medium text-gray-900" x-text="ticket.reported_at ? formatDateShort(ticket.reported_at) : formatDateShort(ticket.created_at)"></dd>
+                            </div>
                             <div class="flex justify-between">
                                 <dt class="text-sm text-gray-500">Priority</dt>
                                 <dd><span class="px-2 py-0.5 rounded text-xs font-medium" :class="priorityClass(ticket.priority)" x-text="priorityBadge(ticket.priority)"></span></dd>

app/templates/tickets/list.html

@@ -130,6 +130,7 @@
                         <th class="px-5 py-3 text-left cursor-pointer hover:text-gray-900" @click="sortBy('created_at')">
                             Created <span x-show="sortField === 'created_at'" x-text="sortDir === 'asc' ? '↑' : '↓'"></span>
                         </th>
+                        <th class="px-5 py-3 text-left">Reported</th>
                         <th class="px-5 py-3 text-left">SLA</th>
                     </tr>
                 </thead>
@@ -142,6 +143,7 @@
                             <td class="px-5 py-3 text-gray-600 max-w-xs truncate" x-text="ticket.description || ''"></td>
                             <td class="px-5 py-3 text-gray-500 text-xs" x-text="ticket.assigned_technician_name || '—'"></td>
                             <td class="px-5 py-3 text-gray-500 text-xs" x-text="formatDate(ticket.created_at)"></td>
+                            <td class="px-5 py-3 text-gray-500 text-xs" x-text="formatDateShort(ticket.reported_at || ticket.created_at)"></td>
                             <td class="px-5 py-3">
                                 <span x-show="ticket.sla_deadline" class="text-xs" :class="new Date(ticket.sla_deadline) < new Date() && !['Closed','Completed','Cancelled'].includes(ticket.status) ? 'text-red-600 font-medium' : 'text-gray-400'">
                                     <span x-text="formatDateShort(ticket.sla_deadline)"></span>
@@ -151,7 +153,7 @@
                         </tr>
                     </template>
                     <tr x-show="!tickets.length && !loading">
-                        <td colspan="7" class="px-5 py-16 text-center text-gray-400">
+                        <td colspan="8" class="px-5 py-16 text-center text-gray-400">
                             <svg class="w-12 h-12 mx-auto text-gray-300 mb-3" fill="none" stroke="currentColor" viewBox="0 0 24 24"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="1.5" d="M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2"/></svg>
                             No tickets match your filters
                         </td>
@@ -178,6 +180,7 @@
                                 <th class="px-5 py-3 text-left">Description</th>
                                 <th class="px-5 py-3 text-left">Assigned To</th>
                                 <th class="px-5 py-3 text-left">Created</th>
+                                <th class="px-5 py-3 text-left">Reported</th>
                                 <th class="px-5 py-3 text-left">SLA</th>
                             </tr>
                         </thead>
@@ -190,6 +193,7 @@
                                     <td class="px-5 py-3 text-gray-600 max-w-xs truncate" x-text="ticket.description || ''"></td>
                                     <td class="px-5 py-3 text-gray-500 text-xs" x-text="ticket.assigned_technician_name || '—'"></td>
                                     <td class="px-5 py-3 text-gray-500 text-xs" x-text="formatDate(ticket.created_at)"></td>
+                                    <td class="px-5 py-3 text-gray-500 text-xs" x-text="formatDateShort(ticket.reported_at || ticket.created_at)"></td>
                                     <td class="px-5 py-3">
                                         <span x-show="ticket.sla_deadline" class="text-xs" :class="new Date(ticket.sla_deadline) < new Date() && !['Closed','Completed','Cancelled'].includes(ticket.status) ? 'text-red-600 font-medium' : 'text-gray-400'">
                                             <span x-text="formatDateShort(ticket.sla_deadline)"></span>

app/templates/tickets/new.html

@@ -154,6 +154,15 @@
                 </div>
             </div>
 
+            <!-- Reported date (backdating support) -->
+            <div class="grid grid-cols-1 md:grid-cols-2 gap-4">
+                <div>
+                    <label class="block text-sm font-medium text-gray-700 mb-1">Reported Date</label>
+                    <input type="date" x-model="form.reported_date" :max="todayStr" class="w-full px-4 py-2.5 rounded-lg border border-gray-300 focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none">
+                    <p class="mt-1 text-xs text-gray-400">Defaults to today. Use a past date when entering an old/backlogged issue — it stays active in the normal workflow.</p>
+                </div>
+            </div>
+
             <!-- Photo Upload -->
             <div>
                 <label class="block text-sm font-medium text-gray-700 mb-1">Photos (Before)</label>
@@ -205,8 +214,10 @@
                 priority: '',
                 description: '',
                 reporter: '',
-                reported_via: ''
+                reported_via: '',
+                reported_date: ''
             },
+            todayStr: '',
             priorityAuto: false,
             categories: [],
             subCategories: [],
@@ -223,6 +234,14 @@
             async init() {
                 await this.loadCategories();
                 await this.loadUnits();
+                // Default reported date to today (local), allow backdating via the date picker
+                this.todayStr = this.localDateStr(new Date());
+                if (!this.form.reported_date) this.form.reported_date = this.todayStr;
+            },
+
+            localDateStr(d) {
+                const offset = d.getTimezoneOffset();
+                return new Date(d.getTime() - offset * 60000).toISOString().slice(0, 10);
             },
 
             // ── Report mode ──────────────────────────────────────────
@@ -384,6 +403,7 @@
                         unit_id: this.form.unit.id,
                         customer_name: this.form.customer_name || null,
                         phone: this.form.phone || null,
+                        reported_at: this.form.reported_date || null,
                     };
 
                     const ticket = await app().apiPost('/api/tickets', payload);

tests/test_backdated_reported_date.py

@@ -0,0 +1,118 @@
+"""Tests for backdated reported-date support (Wahab demo).
+
+Anchors:
+* A ticket created with a past ``reported_at`` persists that date and it is
+  exposed on list + detail responses — this is how Wahab enters old tickets
+  that stay active in the normal workflow.
+* A ticket created without ``reported_at`` defaults to "now", so existing
+  create behavior is unchanged.
+* The reported date is metadata only: SLA deadlines still run from creation
+  time and no age/backdate restriction kicks in.
+"""
+
+from __future__ import annotations
+
+from datetime import datetime
+
+import pytest
+
+pytestmark = pytest.mark.asyncio
+
+
+def _naive(iso: str) -> datetime:
+    """Parse an ISO datetime and strip any tz offset for safe comparison."""
+    dt = datetime.fromisoformat(iso)
+    return dt.replace(tzinfo=None) if dt.tzinfo is not None else dt
+
+
+async def _login(client, email="wahab@denya.com", password="denya123") -> str:
+    resp = await client.post(
+        "/api/auth/login",
+        json={"email": email, "password": password},
+    )
+    assert resp.status_code == 200, resp.text
+    return resp.json()["access_token"]
+
+
+async def _create_ticket(client, token: str, **overrides) -> dict:
+    payload = {
+        "unit_id": 2,
+        "category_id": 3,
+        "priority": "medium",
+        "reporter": "Backdate Test",
+        "reported_via": "walk-in",
+        "description": "backdate test ticket",
+        **overrides,
+    }
+    resp = await client.post(
+        "/api/tickets",
+        json=payload,
+        headers={"Authorization": f"Bearer {token}"},
+    )
+    assert resp.status_code == 201, resp.text
+    return resp.json()
+
+
+async def test_create_with_backdated_reported_at_persists(client):
+    """Wahab (Admin/Wahab) can enter an old ticket and its date sticks."""
+    token = await _login(client)
+    ticket = await _create_ticket(
+        client,
+        token,
+        reported_at="2026-07-20",
+        description="Old plumbing issue reported weeks ago",
+    )
+    assert ticket["reported_at"] is not None
+    assert ticket["reported_at"].startswith("2026-07-20")
+
+    # Still an active ticket in the normal workflow — no age restriction.
+    assert ticket["status"] in {"New", "Logged"}
+
+    # Detail endpoint exposes the reported date.
+    detail = await client.get(f"/api/tickets/{ticket['id']}")
+    assert detail.status_code == 200
+    assert detail.json()["reported_at"].startswith("2026-07-20")
+
+    # List endpoint exposes it too.
+    listing = await client.get("/api/tickets")
+    assert listing.status_code == 200
+    listed = next(t for t in listing.json()["items"] if t["id"] == ticket["id"])
+    assert listed["reported_at"].startswith("2026-07-20")
+
+
+async def test_create_without_reported_at_defaults_to_now(client):
+    """Omitting reported_at behaves exactly as before: reported == created."""
+    token = await _login(client)
+    ticket = await _create_ticket(client, token, description="normal today ticket")
+    assert ticket["reported_at"] is not None
+    reported = _naive(ticket["reported_at"])
+    created = _naive(ticket["created_at"])
+    assert abs((reported - created).total_seconds()) < 60
+
+
+async def test_reported_at_does_not_shift_sla_deadline(client):
+    """SLA computation is unchanged: deadlines run from creation time."""
+    token = await _login(client)
+    ticket = await _create_ticket(
+        client,
+        token,
+        priority="urgent",
+        reported_at="2026-01-01",
+        description="old urgent ticket",
+    )
+    assert ticket["sla_deadline"] is not None
+    created = _naive(ticket["created_at"])
+    deadline = _naive(ticket["sla_deadline"])
+    hours = (deadline - created).total_seconds() / 3600
+    assert 3.5 <= hours <= 4.5  # urgent → 4 h resolution window from creation
+
+
+async def test_reported_at_round_trips_full_datetime(client):
+    """A precise datetime (not just a date) survives the round trip."""
+    token = await _login(client)
+    reported = "2026-07-20T14:30:00"
+    ticket = await _create_ticket(client, token, reported_at=reported, description="datetime round trip")
+    assert ticket["reported_at"] is not None
+    parsed = _naive(ticket["reported_at"])
+    assert parsed.date().isoformat() == "2026-07-20"
+    assert parsed.hour == 14 and parsed.minute == 30