tinker_tickets/Claude.md

# Tinker Tickets - Project Documentation for AI Assistants

## Project Status (January 2026)

**Current Phase**: All core features implemented. System is production-ready.

**Completed Features**:
- Activity Timeline, Ticket Assignment, Status Transitions with Workflows
- Ticket Templates, Bulk Actions (Admin Only)
- File Attachments, Ticket Dependencies, @Mentions in Comments
- Recurring Tickets, Custom Fields, Advanced Search with Saved Filters
- Export to CSV/JSON, API Key Management
- Ticket Visibility Levels (public/internal/confidential)
- Collapsible Sidebar, Kanban Card View, Inline Ticket Preview
- Mobile Responsive Design, Ticket Linking in Comments
- Admin Pages (Templates, Workflow, Recurring, Custom Fields, User Activity, Audit Log, API Keys)
- Comment Edit/Delete (owner or admin can modify their comments)
- Markdown Tables Support, Auto-linking URLs in Comments

**Security Features** (January 2026):
- CSP with nonce-based script execution (no unsafe-inline)
- IP-based rate limiting (prevents session bypass attacks)
- Visibility checks on attachment downloads
- Unique ticket ID generation with collision prevention
- Internal visibility requires groups validation

## Design Decisions

**Not Planned / Out of Scope**:
- Email integration - Discord webhooks are the notification method for this system
- SLA management - Not required for internal infrastructure use
- Time tracking - Out of scope for current requirements
- OAuth2/External identity providers - Authelia is the only approved SSO method
- GraphQL API - REST API is sufficient for current needs

**Wiki Documentation**: https://wiki.lotusguild.org/en/Services/service-tinker-tickets

## Project Overview

Tinker Tickets is a feature-rich, self-hosted ticket management system built for managing data center infrastructure issues. It features SSO integration with Authelia/LLDAP, workflow management, Discord notifications, and a retro terminal-style web interface.

**Tech Stack:**
- Backend: PHP 7.4+ with MySQLi
- Frontend: Vanilla JavaScript, CSS3
- Database: MariaDB on separate LXC (10.10.10.50)
- Web Server: nginx with PHP-FPM on production (10.10.10.45)
- Authentication: Authelia SSO with LLDAP backend

**Production Environment:**
- **Primary URL**: https://t.lotusguild.org
- **Web Server**: nginx at 10.10.10.45 (`/var/www/html/tinkertickets`)
- **Database**: MariaDB at 10.10.10.50 (`ticketing_system` database)
- **Authentication**: Authelia provides SSO via headers
- **Dev Environment**: `/root/code/tinker_tickets` (not production)

## Architecture

### MVC Pattern
```
Controllers → Models → Database
     ↓
   Views
```

### Project Structure
```
/tinker_tickets/
├── api/                                  # API endpoints
│   ├── add_comment.php                   # POST: Add comment
│   ├── assign_ticket.php                 # POST: Assign ticket to user
│   ├── bulk_operation.php                # POST: Bulk operations - admin only
│   ├── check_duplicates.php              # GET: Check for duplicate tickets
│   ├── delete_attachment.php             # POST/DELETE: Delete attachment
│   ├── delete_comment.php                # POST: Delete comment (owner/admin)
│   ├── download_attachment.php           # GET: Download attachment (with visibility check)
│   ├── export_tickets.php                # GET: Export tickets to CSV/JSON
│   ├── generate_api_key.php              # POST: Generate API key (admin)
│   ├── get_template.php                  # GET: Fetch ticket template
│   ├── get_users.php                     # GET: Get user list
│   ├── manage_recurring.php              # CRUD: Recurring tickets (admin)
│   ├── manage_templates.php              # CRUD: Templates (admin)
│   ├── manage_workflows.php              # CRUD: Workflow rules (admin)
│   ├── revoke_api_key.php                # POST: Revoke API key (admin)
│   ├── ticket_dependencies.php           # GET/POST/DELETE: Ticket dependencies
│   ├── update_comment.php                # POST: Update comment (owner/admin)
│   ├── update_ticket.php                 # POST: Update ticket (workflow validation)
│   └── upload_attachment.php             # GET/POST: List or upload attachments
├── assets/
│   ├── css/
│   │   ├── dashboard.css                 # Dashboard + terminal styling
│   │   └── ticket.css                    # Ticket view styling
│   ├── js/
│   │   ├── advanced-search.js            # Advanced search modal
│   │   ├── ascii-banner.js               # ASCII art banner
│   │   ├── dashboard.js                  # Dashboard + bulk actions + kanban + sidebar
│   │   ├── keyboard-shortcuts.js         # Keyboard shortcuts
│   │   ├── markdown.js                   # Markdown rendering + ticket linking
│   │   ├── settings.js                   # User preferences
│   │   ├── ticket.js                     # Ticket + comments + visibility
│   │   └── toast.js                      # Toast notifications
│   └── images/
│       └── favicon.png
├── config/
│   └── config.php                        # Config + .env loading
├── controllers/
│   ├── DashboardController.php           # Dashboard with stats + filters
│   └── TicketController.php              # Ticket CRUD + timeline + visibility
├── cron/
│   └── create_recurring_tickets.php      # Process recurring ticket schedules
├── helpers/
│   └── ResponseHelper.php                # Standardized JSON responses
├── middleware/
│   ├── AuthMiddleware.php                # Authelia SSO integration
│   ├── CsrfMiddleware.php                # CSRF protection
│   ├── RateLimitMiddleware.php           # Session + IP-based rate limiting
│   └── SecurityHeadersMiddleware.php     # CSP with nonces, security headers
├── models/
│   ├── ApiKeyModel.php                   # API key generation/validation
│   ├── AuditLogModel.php                 # Audit logging + timeline
│   ├── BulkOperationsModel.php           # Bulk operations tracking
│   ├── CommentModel.php                  # Comment data access
│   ├── CustomFieldModel.php              # Custom field definitions/values
│   ├── DependencyModel.php               # Ticket dependencies
│   ├── RecurringTicketModel.php          # Recurring ticket schedules
│   ├── StatsModel.php                    # Dashboard statistics
│   ├── TemplateModel.php                 # Ticket templates
│   ├── TicketModel.php                   # Ticket CRUD + assignment + visibility
│   ├── UserModel.php                     # User management + groups
│   ├── UserPreferencesModel.php          # User preferences
│   └── WorkflowModel.php                 # Status transition workflows
├── scripts/
│   ├── cleanup_orphan_uploads.php        # Clean orphaned uploads
│   └── create_dependencies_table.php     # Create ticket_dependencies table
├── uploads/                              # File attachment storage
├── views/
│   ├── admin/
│   │   ├── ApiKeysView.php               # API key management
│   │   ├── AuditLogView.php              # Audit log browser
│   │   ├── CustomFieldsView.php          # Custom field management
│   │   ├── RecurringTicketsView.php      # Recurring ticket management
│   │   ├── TemplatesView.php             # Template management
│   │   ├── UserActivityView.php          # User activity report
│   │   └── WorkflowDesignerView.php      # Workflow transition designer
│   ├── CreateTicketView.php              # Ticket creation with visibility
│   ├── DashboardView.php                 # Dashboard with kanban + sidebar
│   └── TicketView.php                    # Ticket view with visibility editing
├── .env                                  # Environment variables (GITIGNORED)
├── Claude.md                             # This file
├── README.md                             # User documentation
└── index.php                             # Main router
```

## Admin Pages

All admin pages are accessible via the **Admin dropdown** in the dashboard header (for admin users only).

| Route | Description |
|-------|-------------|
| `/admin/templates` | Create and edit ticket templates |
| `/admin/workflow` | Visual workflow transition designer |
| `/admin/recurring-tickets` | Manage recurring ticket schedules |
| `/admin/custom-fields` | Define custom fields per category |
| `/admin/user-activity` | View per-user activity statistics |
| `/admin/audit-log` | Browse all audit log entries |
| `/admin/api-keys` | Generate and manage API keys |

## Database Schema

**Database**: `ticketing_system` at 10.10.10.50
**User**: `tinkertickets`

### Core Tables

| Table | Description |
|-------|-------------|
| `tickets` | Core ticket data with assignment, visibility, and tracking |
| `ticket_comments` | Markdown-supported comments with user_id reference |
| `ticket_attachments` | File attachment metadata |
| `ticket_dependencies` | Ticket relationships (blocks, blocked_by, relates_to, duplicates) |
| `users` | User accounts synced from LLDAP (includes groups) |
| `user_preferences` | User settings and preferences |
| `audit_log` | Complete audit trail with indexed queries |
| `status_transitions` | Workflow configuration |
| `ticket_templates` | Reusable ticket templates |
| `recurring_tickets` | Scheduled ticket definitions |
| `custom_field_definitions` | Custom field schemas per category |
| `custom_field_values` | Custom field data per ticket |
| `saved_filters` | User-saved dashboard filters |
| `bulk_operations` | Bulk operation tracking |
| `api_keys` | API key storage with hashed keys |

### tickets Table Key Columns

| Column | Type | Description |
|--------|------|-------------|
| `ticket_id` | varchar(9) | Unique 9-digit identifier |
| `visibility` | enum | 'public', 'internal', 'confidential' |
| `visibility_groups` | varchar(500) | Comma-separated group names (for internal) |
| `created_by` | int | Foreign key to users |
| `assigned_to` | int | Foreign key to users (nullable) |
| `updated_by` | int | Foreign key to users |
| `priority` | int | 1-5 (1=Critical, 5=Minimal) |
| `status` | varchar(20) | Open, Pending, In Progress, Closed |

### Indexed Columns (for performance)

- `tickets`: ticket_id (unique), status, priority, created_at, created_by, assigned_to, visibility
- `audit_log`: user_id, action_type, entity_type, created_at

## Dashboard Features

- **View Toggle**: Switch between Table view and Kanban card view
- **Collapsible Sidebar**: Click arrow to collapse/expand filter sidebar
- **Stats Widgets**: Clickable cards for quick filtering
- **Inline Ticket Preview**: Hover over ticket IDs for 300ms to see preview popup
- **Sortable Columns**: Click headers to sort
- **Advanced Search**: Date ranges, priority ranges, user filters
- **Saved Filters**: Save and load custom filter combinations
- **Bulk Actions** (admin): Select multiple tickets for bulk operations
- **Export**: Export selected tickets to CSV or JSON

## Ticket Visibility Levels

- **Public**: All authenticated users can view
- **Internal**: Only users in specified groups can view (groups required)
- **Confidential**: Only creator, assignee, and admins can view

**Important**: Internal visibility requires at least one group to be specified. Attempting to create/update a ticket with internal visibility but no groups will fail validation.

## Important Notes for AI Assistants

1. **Session format**: `$_SESSION['user']['user_id']` (not `$_SESSION['user_id']`)
2. **API auth**: Check `$_SESSION['user']['user_id']` exists
3. **Admin check**: `$_SESSION['user']['is_admin'] ?? false`
4. **Config path**: `config/config.php` (not `config/db.php`)
5. **Comments table**: `ticket_comments` (not `comments`)
6. **CSRF**: Required for POST/DELETE requests via `X-CSRF-Token` header
7. **Cache busting**: Use `?v=YYYYMMDD` query params on JS/CSS files
8. **Ticket linking**: Use `#123456789` in markdown-enabled comments
9. **User groups**: Stored in `users.groups` as comma-separated values
10. **API routing**: All API endpoints must be added to `index.php` router
11. **Session in APIs**: RateLimitMiddleware starts session; don't call session_start() again
12. **Database collation**: Use `utf8mb4_general_ci` (not unicode_ci) for new tables
13. **Ticket ID extraction**: Use `getTicketIdFromUrl()` helper in JS files
14. **CSP Nonces**: All inline scripts require `nonce="<?php echo $nonce; ?>"` attribute
15. **Visibility validation**: Internal visibility requires groups; code validates this
16. **Rate limiting**: Both session-based AND IP-based limits are enforced

## File Reference Quick Guide

| File | Purpose |
|------|---------|
| `index.php` | Main router for all routes |
| `api/update_ticket.php` | Ticket updates with workflow + visibility |
| `api/download_attachment.php` | File downloads with visibility check |
| `api/bulk_operation.php` | Bulk operations with visibility filtering |
| `models/TicketModel.php` | Ticket CRUD, visibility filtering, collision-safe ID generation |
| `models/ApiKeyModel.php` | API key generation and validation |
| `middleware/SecurityHeadersMiddleware.php` | CSP headers with nonce generation |
| `middleware/RateLimitMiddleware.php` | Session + IP-based rate limiting |
| `assets/js/dashboard.js` | Dashboard UI, kanban, sidebar, bulk actions |
| `assets/js/ticket.js` | Ticket UI, visibility editing |
| `assets/js/markdown.js` | Markdown parsing + ticket linking (XSS-safe) |
| `assets/css/dashboard.css` | Terminal styling, kanban, sidebar |

## Security Implementations

| Feature | Implementation |
|---------|---------------|
| SQL Injection | All queries use prepared statements with parameter binding |
| XSS Prevention | HTML escaped in markdown parser, CSP with nonces |
| CSRF Protection | Token-based with constant-time comparison |
| Session Security | Fixation prevention, secure cookies, timeout |
| Rate Limiting | Session-based + IP-based (file storage) |
| File Security | Path traversal prevention, MIME validation |
| Visibility | Enforced on views, downloads, and bulk operations |

## Repository

- **Gitea**: https://code.lotusguild.org/LotusGuild/tinker_tickets
- **Production**: https://t.lotusguild.org
- **Wiki**: https://wiki.lotusguild.org/en/Services/service-tinker-tickets