LT_QR_Codes_Management/Claude.md

CLAUDE.md — QR Code Attendance Management System

Unified reference — LT Services, Inc. & GOV Services, Inc.

This file is the authoritative reference for Claude across all sessions. Every session must treat this file as ground truth for architecture, conventions, domain rules, and developer preferences. Read it fully before making any changes.

---

1. Project Overview

A Flask/Python web application for employee attendance tracking via QR codes with GPS validation, Excel import/export, payroll calculations, role-based access control, and per-QR photo verification toggle.

Deployed for two companies on separate servers:

• LT Services, Inc. — THEME_NAME empty (blue theme). Production: test.ltservicesinc.com. Gitea: gitea.ngodanguyen.tech/nngo/LT_QR_Codes_Management.
• GOV Services, Inc. — THEME_NAME=gov (green theme). Production: qr.govservicesinc.com. Key .env differences: COMPANY_NAME=GOV. Services, Inc, THEME_NAME=gov, QR_BASE_URL=https://qr.govservicesinc.com.

Both codebases are structurally identical in all Python files, routes, models, and templates. They differ only in .env values and static/css/theme-gov.css. Every code fix must be applied to both instances.

• Stack: Flask 3.1, SQLAlchemy 2.0 / MySQL (PyMySQL only — mysql-connector-python removed), openpyxl 3.1, pandas 2.2, Python 3.12
• Frontend: Jinja2 templates + vanilla JS + Font Awesome 6 icons
• Auth guard: Cloudflare Turnstile (optional, toggled via .env)
• Production server: Gunicorn + gevent workers, Nginx reverse proxy, Ubuntu Server

---

2. Folder Structure

QR_Code_Management/
│
├── app.py                              # Application factory (create_app) + startup entry point
├── config.py                           # All env-var reads — single source of truth
├── extensions.py                       # Shared singletons: db (SQLAlchemy) + logger_handler (AppLogger)
├── logger_handler.py                   # AppLogger class + log_user_activity / log_database_operations decorators
├── location_logging.py                 # Android GPS debug routes (/api/log-location-action, /api/location-debug-info)
├── turnstile_utils.py                  # Cloudflare Turnstile verification helper (TurnstileUtils class)
├── advanced_security_middleware.py     # SecurityManager (CSRF token gen, rate limiting, suspicious IP tracking)
│                                       # Wired into create_app() — do not import separately in blueprints
├── working_hours_calculator.py         # CANONICAL calculator for Excel exports (WorkingHoursCalculator)
├── single_checkin_calculator.py        # Legacy calculator — NOT used for exports; never switch to this
├── time_attendance_import_service.py   # Excel import pipeline with duplicate detection
├── qr_code_import_service.py           # Bulk QR code import from Excel
├── address_normalization_fix.py        # normalize_address() + addresses_are_similar() helpers
├── app_performance_middleware.py       # PerformanceMonitor — dev-mode only; lazy-imported inside __main__
├── db_audit_tables.py                  # DB audit table helpers (standalone operational script)
├── db_health_check.py                  # DB connectivity health check (standalone)
├── db_maintenance.py                   # DB maintenance utilities (standalone)
├── db_performance_optimization.py      # Index/query optimization helpers
├── employee_data_merger.py             # Merge duplicate employee records (standalone)
├── employee_duplicate_remove.py        # Remove employee duplicates (standalone)
├── employee_sync_scheduler.py          # Scheduled employee sync (schedule library)
├── employee_table_sync.py              # Employee table sync logic
├── requirements.txt                    # All pinned Python dependencies (single MySQL driver: PyMySQL)
│
├── models/
│   ├── __init__.py                     # set_db(db) → unpacks and returns all model classes
│   ├── base.py                         # Shared db reference (base.db); all models import from here
│   ├── user.py                         # User model (table: users)
│   ├── employee.py                     # Employee model (table: employee)
│   ├── attendance.py                   # AttendanceData model (table: attendance_data)
│   ├── time_attendance.py              # TimeAttendance model (table: time_attendance)
│   ├── qrcode.py                       # QRCode, QRCodeStyle, QRCodeLocation models
│   ├── project.py                      # Project model (table: projects)
│   └── permissions.py                  # UserProjectPermission, UserLocationPermission models
│
├── routes/
│   ├── __init__.py                     # Empty (intentional)
│   ├── auth.py                         # Blueprint 'auth': /, /register (admin-only), /login, /logout, /profile
│   ├── dashboard.py                    # Blueprint 'dashboard': /dashboard, project QR views, stats APIs
│   ├── users.py                        # Blueprint 'users': /users/*, user management APIs
│   ├── admin.py                        # Blueprint 'admin': /admin/logs, /api/logs/*
│   ├── projects.py                     # Blueprint 'projects': /projects/*
│   ├── qr_codes.py                     # Blueprint 'qr_codes': /qr-codes/*, /qr/<url> check-in flow
│   ├── attendance.py                   # Blueprint 'attendance': /attendance report + API endpoints
│   ├── attendance_edit.py              # Side-effect module: edit, add manual, save, delete routes
│   │                                   # Imports bp FROM attendance.py — does NOT define its own blueprint
│   ├── verification.py                 # Side-effect module: /verification-review/* routes
│   │                                   # Imports bp FROM attendance.py — does NOT define its own blueprint
│   ├── attendance_export.py            # Side-effect module: /export-configuration, /generate-excel-export
│   │                                   # Imports bp FROM attendance.py — does NOT define its own blueprint
│   ├── statistics.py                   # Blueprint 'statistics': /statistics
│   ├── employees.py                    # Blueprint 'employees': /employees/*
│   ├── time_attendance.py              # Blueprint 'time_attendance': /time-attendance/* (all TA routes)
│   └── time_attendance_export.py       # Plain module (NO Blueprint): export helper functions only
│
├── utils/
│   ├── __init__.py
│   ├── helpers.py                      # Role decorators, QR generation, role/permission helpers
│   ├── geocoding.py                    # Haversine distance, Google Maps client, reverse geocode
│   ├── template_helpers.py             # Context processors: get_employee_name, format_hours, etc.
│   └── excel_safety.py                 # Formula-injection guard for every Excel/CSV export (Set 23)
│
├── static/
│   ├── css/
│   │   ├── style.css                   # MASTER stylesheet — all CSS variables and layout
│   │   ├── theme-gov.css               # GOV Services brand theme overrides (loaded when THEME_NAME=gov)
│   │   └── *.css                       # Page-specific stylesheets
│   └── js/
│       ├── script.js                   # Global JS — includes CSRF fetch wrapper
│       ├── qr_destination.js           # Check-in page: GPS, bilingual UI, camera — CSRF-EXEMPT
│       ├── attendance_report.js        # Attendance report: pagination, sorting, charts
│       ├── dashboard.js
│       ├── export_configuration.js
│       ├── users.js
│       ├── attendance_fullscreen.js
│       └── android_location_handler.js # Android-specific GPS workaround
│
├── templates/
│   ├── base.html                       # Base for unauthenticated pages — loads theme CSS conditionally
│   ├── base_authenticated.html         # Base for protected pages — loads theme CSS conditionally
│   │                                   # Brand text uses {{ COMPANY_NAME }} — no hardcoded strings
│   ├── [all other page templates]      # All POST forms include {{ csrf_token() }} hidden field
│   └── errors/
│       ├── 403.html / 404.html / 500.html
│
└── tools/
    ├── migration_photo_verification_toggle.py  # Adds photo_verification_enabled to qr_codes
    ├── migration_PM_permissions.py             # One-time: user_project/location_permissions tables
    ├── migration_dynamic_qr_locations.py       # One-time: qr_type column + qr_code_locations table
    ├── migration_attendance_indexes.py         # attendance_data secondary indexes (online DDL, re-runnable)
    └── optimize_time_attendance_db.py          # Standalone DB index optimization script

---

3. Database Tables

Table	Model	Purpose
users	User	System user accounts with RBAC
employee	Employee	Employee master data (firstName, lastName, title, contractId)
attendance_data	AttendanceData	QR check-in records with GPS, photo verification
time_attendance	TimeAttendance	Imported time-clock records from Excel
qr_codes	QRCode	QR code definitions (standard and dynamic types)
qr_code_styles	QRCodeStyle	Reusable QR code visual styles
qr_code_locations	QRCodeLocation	Selectable locations for dynamic QR codes
projects	Project	Projects that group QR codes and employees
user_project_permissions	UserProjectPermission	Project-level access for Project Managers
user_location_permissions	UserLocationPermission	Location-level access for Project Managers
log_events	(raw SQL)	Application event log (created by AppLogger)

qr_codes — key columns

Column	Type	Notes
qr_type	VARCHAR(20)	'standard' or 'dynamic'
photo_verification_enabled	TINYINT(1) DEFAULT 1	Per-QR photo verification toggle (added May 2026)
active_status	BOOLEAN
project_id	FK → projects

attendance_data — key columns

Column	Type	Notes
employee_id	VARCHAR(50)	Always stored uppercased; carries the work-type code (1234SP) — see §11
location_name	VARCHAR(100)	Resolved location — never stores 'Dynamic'
location_accuracy	FLOAT	Haversine distance in miles
is_dynamic_qr	BOOLEAN	True when checked in via dynamic QR
verification_photo	TEXT	Base64 encoded image
verification_required	BOOLEAN
verification_status	VARCHAR(20)	pending / approved / rejected

Key relationships:

• Employee.contractId → Project.id
• QRCode.project_id → Project.id
• AttendanceData.qr_code_id → QRCode.id (CASCADE DELETE)
• TimeAttendance.project_id → Project.id

---

4. User Roles & Access Control

VALID_ROLES       = ['admin', 'staff', 'payroll', 'project_manager', 'accounting']
STAFF_LEVEL_ROLES = ['staff', 'payroll', 'project_manager', 'accounting']

Role	Key Access
admin	Full access; only role that sees System Logs, Users, all export tools
staff	Create/edit QR codes; view dashboard and reports; no delete, no admin
payroll	Same as staff + Time Attendance section
accounting	Same as payroll (identical menu items)
project_manager	Reports only; scoped to assigned projects and locations via permission tables

Auth decorators (in utils/helpers.py):

• @login_required — redirects to /login if no session
• @admin_required — 403 if role is not admin
• @staff_or_admin_required — 403 if not admin or staff-level

/register is restricted to @admin_required — public self-registration is disabled.

Server-side role enforcement (Sept 2026, Set 23) — the sidebar in base_authenticated.html is the reference for who may use what; routes enforce the same rules:

• roles_required(*roles) decorator and restrict_blueprint_to_roles(bp, roles) (a blueprint before_request) in utils/helpers.py. Anonymous → login redirect; wrong role → flash + dashboard redirect, or JSON 401/403 for /api/, X-Requested-With or JSON callers.
• PAYROLL_AREA_ROLES = ('admin','payroll','accounting') → whole blueprints: time_attendance, legacy_attendance, employees, statistics.
• QR_MANAGEMENT_ROLES = ('admin','staff','payroll','accounting') → QR create / edit / bulk import (not project managers). QR toggle / activate / deactivate → admin only (the dashboard shows those buttons to admins only).
• User management state changes (delete, reactivate, promote, demote, activate, deactivate, permanently-delete) are POST-only — never re-add GET: CSRF validation only runs on POST.
• New routes in a gated blueprint are covered automatically; new routes elsewhere need an explicit decorator.

Project Manager data scoping (Set 27) — role gates decide which pages a PM may open; load_project_manager_scope() (utils/helpers.py) decides which rows they see. It returns (is_project_manager, allowed_project_ids, allowed_location_names) and fails closed (a PM with no assignments, or a permission lookup error, sees nothing). Applied in:

• routes/attendance.py: the report, live updates, attendance_locations_api, time_attendance_locations_api, search_employees_api, get_project_locations_api
• routes/dashboard.py: dashboard QR list + project list, project_qr_codes (403-style redirect for someone else's project), dashboard_stats_api, dashboard_realtime_api
• Projects and locations combine with AND, never OR (Set 28) — assigned projects, narrowed to the assigned locations when there are any. The report has always worked this way (_attendance_filter_conditions); the dashboard used OR, so a PM assigned location "Bldg A" also saw every OTHER project's QR codes that used that same location name. A PM with locations but no project assignment still sees those locations across projects — that is the only way such a user sees anything at all
• A PM scoped only by locations is resolved to the projects behind those locations when employee names are searched, so their filter still works without exposing other projects.
• Any new endpoint returning attendance, employee, QR or project rows must call it.

---

5. Application Factory & Initialization Order

1. load_dotenv()
2. from extensions import db, init_logger
3. create_app():
   a. app.config.from_object(get_config())
   b. SECRET_KEY guard — sys.exit(1) if default value in non-debug mode
   c. db.init_app(app)
   d. set_db(db) → unpacks all model classes
   e. init_logger(app, db) → binds logger_handler in extensions.py
   f. register_blueprints() — in canonical order (see §6)
      + side-effect import of attendance_edit, verification, attendance_export
   g. create_location_logging_routes() ← from location_logging.py
   h. SecurityManager.init_app() ← wires CSRF before_request + rate limiting
   i. inject_csrf_token() context processor
   j. inject_company_name() context processor (COMPANY_NAME, THEME_NAME, CURRENT_YEAR)
   k. inject_logging_status(), inject_turnstile() context processors
   l. template_filters (strftime, days_since, time_ago)
   m. before_request: adjust_session_lifetime + g.start_time + suspicious UA scan
   n. after_request: slow-query detection + error response logging
   o. Error handlers: 403, 404, 500
   p. Startup init: create_tables() + update_existing_qr_codes() ← runs under Gunicorn too
4. if __name__ == '__main__':
   a. lazy import PerformanceMonitor (dev-mode only)
   b. initialize_performance_optimizations()
   c. app.run()

Critical notes:

• create_tables() and update_existing_qr_codes() run inside with app.app_context() inside create_app() — they execute under gunicorn, not only under __main__.
• PerformanceMonitor is imported lazily inside __main__ only — gunicorn workers never load it.
• update_existing_qr_codes() builds startup QR images from QR_BASE_URL only. When it is not set, missing images are not generated at startup (the old FLASK_HOST/FLASK_PORT fallback wrote http://localhost:5000/... into QR codes). Note: the QR routes in routes/qr_codes.py build URLs from request.url_root, not QR_BASE_URL — there is no _get_qr_base_url() helper despite older notes.

---

6. Blueprint Registration Order

auth_bp, dashboard_bp, users_bp, admin_bp, projects_bp,
qr_codes_bp, attendance_bp, statistics_bp,
employees_bp, time_attendance_bp

Attendance blueprint split: attendance.py defines bp = Blueprint('attendance', __name__) once. attendance_edit.py, verification.py, and attendance_export.py each do:

from routes.attendance import bp  # shared blueprint — do not redefine

app.py registers only attendance_bp once. Sub-modules are loaded as side-effect imports:

import routes.attendance_edit    # noqa: F401
import routes.verification       # noqa: F401
import routes.attendance_export  # noqa: F401

time_attendance_export.py is not a Blueprint. Plain module providing export helper functions. Never register it separately.

---

7. All Routes

auth (Blueprint: auth)

URL	Endpoint	Notes
/	auth.index
/register	auth.register	@admin_required — not public
/login	auth.login	Rate-limited via SecurityManager
/logout	auth.logout
/profile	auth.profile

dashboard (Blueprint: dashboard)

URL	Endpoint
/dashboard	dashboard.dashboard
/project/<id>/qr-codes	dashboard.project_qr_codes
/dashboard/search	dashboard.search_qr_codes
/api/dashboard/stats	dashboard.dashboard_stats_api
/api/dashboard/realtime	dashboard.dashboard_realtime_api

users (Blueprint: users)

URL	Endpoint
/users	users.users
/users/create	users.create_user
/users/<id>/edit	users.edit_user
/users/<id>/delete	users.delete_user
/users/<id>/reactivate	users.reactivate_user
/users/<id>/promote	users.promote_user
/users/<id>/demote	users.demote_user
/users/<id>/toggle-status	users.toggle_user_status
/users/<id>/activate	users.activate_user
/users/<id>/deactivate	users.deactivate_user
/users/<id>/permanently-delete	users.permanently_delete_user
/api/users/stats	users.user_stats_api
/api/locations-by-projects	users.get_locations_by_projects
/api/roles/permissions	users.role_permissions_api
/api/geocode	users.geocode_address_api
/api/reverse-geocode	users.reverse_geocode_api

admin (Blueprint: admin)

URL	Endpoint
/admin/logs	admin.admin_logs
/admin/health/google-maps	admin.google_maps_health
/api/logs/recent	admin.api_recent_logs
/api/logs/stats	admin.api_log_stats
/api/logs/cleanup	admin.api_cleanup_logs
/api/logs/clear	admin.api_clear_logs
/api/logs/clear-old	admin.api_clear_old_logs
/api/logs/export	admin.api_export_logs

projects (Blueprint: projects)

URL	Endpoint
/projects	projects.projects
/projects/create	projects.create_project
/projects/<id>/edit	projects.edit_project
/projects/<id>/toggle	projects.toggle_project
/api/projects/active	projects.api_active_projects

qr_codes (Blueprint: qr_codes)

URL	Endpoint	Notes
/qr-codes/create	qr_codes.create_qr_code
/qr-codes/bulk-import	qr_codes.import_bulk_qr_codes
/qr-codes/bulk-import/template	qr_codes.download_qr_import_template
/qr-codes/<id>/edit	qr_codes.edit_qr_code
/qr-codes/<id>/delete	qr_codes.delete_qr_code
/qr-codes/<id>/toggle-status	qr_codes.toggle_qr_status
/qr-codes/<id>/activate	qr_codes.activate_qr_code
/qr-codes/<id>/deactivate	qr_codes.deactivate_qr_code
/qr-codes/<id>/copy-url	qr_codes.copy_qr_url
/qr-codes/<id>/open-link	qr_codes.open_qr_link
/qr/<url>	qr_codes.qr_destination
/qr/<url>/checkin	qr_codes.qr_checkin	CSRF-exempt — public unauthenticated
/qr/<url>/locations	qr_codes.qr_get_locations
/qr/<url>/last-work-type	qr_codes.qr_last_work_type	GET, public — work type of the employee's open check-in

attendance (Blueprint: attendance — split across 4 files)

URL	Endpoint	File
/attendance	attendance.attendance_report	attendance.py
/api/attendance/locations	attendance.attendance_locations_api	attendance.py
/api/attendance/stats	attendance.attendance_stats_api	attendance.py
/api/search_employees	attendance.search_employees_api	attendance.py
/api/attendance/live-updates	attendance.attendance_live_updates_api	attendance.py — live table polling, see §19
/api/get_project_locations	attendance.get_project_locations_api	attendance.py
/api/time-attendance/locations	attendance.time_attendance_locations_api	attendance.py
/attendance/<id>/edit	attendance.edit_attendance	attendance_edit.py
/attendance/<id>/delete	attendance.delete_attendance	attendance_edit.py
/attendance/add	attendance.add_manual_attendance	attendance_edit.py
/attendance/save_manual	attendance.save_manual_attendance	attendance_edit.py
/verification-review	attendance.verification_review	verification.py
/verification-review/<id>	attendance.verification_review_detail	verification.py
/verification-review/<id>/update	attendance.update_verification_status	verification.py
/api/attendance/<id>/verification-details	attendance.get_verification_details	verification.py
/export-configuration	attendance.export_configuration	attendance_export.py
/generate-excel-export	attendance.generate_excel_export	attendance_export.py

statistics (Blueprint: statistics)

URL	Endpoint
/statistics	statistics.qr_statistics
/api/statistics/export	statistics.export_statistics

employees (Blueprint: employees)

URL	Endpoint
/employees	employees.employees
/employees/create	employees.create_employee
/employees/<idx>/edit	employees.edit_employee
/employees/<idx>/delete	employees.delete_employee
/employees/<idx>	employees.employee_detail
/api/employees/search	employees.api_employees_search

time_attendance (Blueprint: time_attendance)

URL	Endpoint
/time-attendance	time_attendance.time_attendance_dashboard
/time-attendance/import	time_attendance.import_time_attendance
/time-attendance/import/analyze-duplicates	time_attendance.analyze_import_duplicates
/time-attendance/import/analyze-invalid	time_attendance.analyze_import_invalid
/time-attendance/import/start	time_attendance.start_import_job
/time-attendance/import/stream/<job_id>	time_attendance.stream_import_progress
/time-attendance/import/cancel-pending	time_attendance.cancel_pending_import
/time-attendance/import/validate	time_attendance.validate_import_file
/time-attendance/import/batch/<batch_id>	time_attendance.view_import_batch
/time-attendance/import/batch/<batch_id>/delete	time_attendance.delete_import_batch
/time-attendance/import/download-template	time_attendance.download_import_template
/time-attendance/export	time_attendance.export_time_attendance
/time-attendance/export/excel	time_attendance.excel_export_time_attendance
/time-attendance/export-by-building	time_attendance.export_time_attendance_by_building
/time-attendance/records	time_attendance.time_attendance_records
/time-attendance/record/<id>	time_attendance.time_attendance_record_detail
/time-attendance/delete/<id>	time_attendance.delete_time_attendance_record
/api/time-attendance/employee/<id>	time_attendance.api_time_attendance_by_employee
/api/time-attendance/location/<n>	time_attendance.api_time_attendance_by_location

Location logging (registered directly on app, not a Blueprint)

URL	Purpose
/api/log-location-action	Android GPS debug logging
/api/location-debug-info	Return GPS debug info

Security API (registered by SecurityManager on app)

URL	Purpose
/api/security/status	Admin-only security dashboard stats
/api/security/clear-blocks	Admin-only: clear rate-limit blocks

---

8. Template System

Two Base Templates

• base.html — unauthenticated pages (login, register, QR scan, errors). Body class: login-layout.
• base_authenticated.html — all protected pages. Body class: has-sidebar. Fixed collapsible sidebar, top header with user/role badge, flash message rendering.

Template Block Names

Block	Purpose
{% block title %}	Page <title> text only — no CSS or JS here
{% block page_title %}	Top header <h1> (authenticated only)
{% block extra_head %}	Page-specific CSS — inside <head>
{% block content %}	Main page body
{% block extra_scripts %}	Page-specific JS — before </body>

Rule: CSS always goes in extra_head. JS always goes in extra_scripts. Never inject either into {% block title %}.

Theme System

Both base.html and base_authenticated.html load the theme override after style.css:

<!-- Theme Override (set THEME_NAME in .env to activate, e.g. THEME_NAME=gov) -->
{% if THEME_NAME %}
<link rel="stylesheet" href="{{ url_for('static', filename='css/theme-' + THEME_NAME + '.css') }}" />
{% endif %}

Loaded before {% block extra_head %} so page-specific CSS loads after and can override.

theme-gov.css overrides: --primary-color: #16a34a / --primary-hover: #15803d (institutional green). No CSS text hacks — brand names handled via {{ COMPANY_NAME }}.

Adding a third company theme: Create static/css/theme-{name}.css and set THEME_NAME={name} in .env. No code changes required.

Global Context Variables

Variable	Source
COMPANY_NAME	.env (COMPANY_NAME)
THEME_NAME	.env (THEME_NAME) — empty string when not set
CURRENT_YEAR	datetime.now().year
csrf_token	generate_csrf_token() from advanced_security_middleware
is_admin	bool from session role
turnstile_enabled, turnstile_site_key	Turnstile config

CSRF in Templates

Every POST form:

<input type="hidden" name="csrf_token" value="{{ csrf_token() }}">

Every AJAX POST:

'X-CSRF-Token': (window.qrConfig && window.qrConfig.csrfToken) || ''

Exception: /qr/<url>/checkin is CSRF-exempt (public, unauthenticated).

---

9. Design System

CSS Custom Properties (defined in style.css)

--primary-color:   #2563eb   /* Blue — LT default */
--primary-hover:   #1d4ed8
--secondary-color: #64748b
--success-color:   #10b981
--warning-color:   #f59e0b
--danger-color:    #ef4444
--info-color:      #0891b2
--gray-50:  #f8fafc          /* Page background */
--gray-700: #334155          /* Body text */
--gray-900: #0f172a          /* Headings */

Light mode only — color-scheme: light only !important enforced globally.

Layout

• Sidebar: 280px expanded, 64px collapsed. Gradient #2563eb → #1d4ed8 (LT). GOV theme: #16a34a → #15803d.
• Header height: 64px.
• Z-index: dropdown 1000, modal 1050, sidebar 1100, overlay 1200.

Excel Export Styling

• Header rows: white bold text on solid black fill (000000)
• Miss-punch / amber: FFC000 fill
• Font: Aptos Narrow 11pt everywhere; 14pt report title, 12pt section summary

---

10. Security Architecture

CSRF Protection

• SecurityManager from advanced_security_middleware.py wired via init_app() in create_app()
• before_request validates csrf_token form field or X-CSRF-Token header on every POST/PUT/PATCH/DELETE
• Token stored in session['csrf_token']; compared with hmac.compare_digest()
• CSRF-exempt: auth.login, auth.register, qr_codes.qr_checkin, static

Rate Limiting

• Failed logins are counted over 15 minutes per IP + username (blocked after 5) and per username from any IP (blocked after 20) — SecurityManager.is_auth_rate_limited(username). Not a plain per-IP counter, so users behind one shared IP cannot lock each other out. In-memory per worker. The check that runs is in auth.login; the middleware's request.endpoint in ['login', ...] check never matches (endpoint is auth.login)
• Client IP = request.remote_addr after ProxyFix(x_for=TRUSTED_PROXY_COUNT) in create_app(). Never read the first X-Forwarded-For entry — the client controls it (that bypassed the limiter). Behind Cloudflare + Nginx set TRUSTED_PROXY_COUNT=2
• SecurityManager.create_secure_session() called on login success (clears failed-attempt counter)

Session Security

• session.clear() before setting new keys on login (prevents session fixation)
• Remember Me → permanent cookie, 30 days (PERMANENT_SESSION_LIFETIME), renewed on each request. Without it → browser-session cookie; before_request hook adjust_session_lifetime() clears the session 10 hours after session['login_epoch'] (set in auth.login)
• Never assign app.permanent_session_lifetime per request, and never lower PERMANENT_SESSION_LIFETIME below 30 days. Flask checks every session cookie's age against it in open_session() before any before_request hook, and the value is shared by the whole worker — setting it to 10 h for anonymous traffic (QR scans) logged Remember Me users out (Set 19)
• SESSION_COOKIE_SECURE=True requires HTTPS — HTTP-only deployments must set false or login loops

validate_session_security() — DO NOT USE in before_request

In-memory dict per worker — breaks under multi-worker gunicorn (login worker A, next request hits worker B with empty dict → 401). Intentionally not called. Flask signed cookie + CSRF handles integrity.

SQL Injection Prevention

All dynamic SQL uses SQLAlchemy parameterized queries:

conditions, params = [], {}
conditions.append("ad.check_in_date >= :date_from")
params["date_from"] = date_from
db.session.execute(text("... WHERE 1=1 " + filter_clause), params)

---

11. QR Code System

QR Types

• standard (default) — fixed single location
• dynamic — employee selects location at scan time from list auto-generated from all active standard QR codes. No manual management UI — always queried live via SELECT DISTINCT location, location_address FROM qr_codes WHERE qr_type='standard'.

Photo Verification — Two-Layer Toggle

Both must be True for photo verification to trigger:

1. Global: PHOTO_VERIFICATION_ENABLED in .env
2. Per-QR: qr_codes.photo_verification_enabled (TINYINT(1) DEFAULT 1)

qr_photo_verification = getattr(qr_code, 'photo_verification_enabled', True)
if qr_photo_verification and current_app.config.get('PHOTO_VERIFICATION_ENABLED', True) \
        and location_accuracy > threshold:
    # require photo

Toggle UI in create_qr_code.html and edit_qr_code.html — uses addEventListener('change', ...) in extra_scripts block.

Type of Work — Check-In Dropdown (Sept 2026)

The check-in page carries a Type of Work / Tipo de Trabajo <select> directly after the Employee ID field. The employee enters a numeric-only ID and picks the type; Regular is selected by default.

Option value	Label shown (bilingual, one plain string)
""	Regular / Trabajo Regular
PW	Periodic Work / Trabajo Periódico (PW)
SP	Special Project / Proyecto Especial (SP)
C	Covering / Cobertura (C)

<option> cannot hold the coloured english-text / spanish-text spans, so both languages share one label separated by /.

Storage — no new column. qr_checkin appends the code to the numeric ID, keeping the format every calculator and export already parses:

work_type = request.form.get('work_type', '').strip().upper()   # '' = Regular
if work_type and work_type not in VALID_CHECKIN_WORK_TYPES:     # ('SP','PW','PT','C')
    return jsonify({...}), 400                                   # reject unknown codes
if employee_id and work_type:
    base_employee_id, existing = parse_employee_id_for_work_type(employee_id)
    employee_id = f"{base_employee_id}{work_type}"               # never "1234SPSP"

PT is accepted server-side for backward compatibility but is not offered in the dropdown. WORK_TYPE_LABELS + _work_type_label() in routes/qr_codes.py are the source of the bilingual labels echoed back to the page.

Employee ID is numeric only — inputmode="numeric", pattern="[0-9]*", an input listener stripping non-digits (paste / autofill), a re-strip at submit, and a digits-only guard before the POST. A localStorage value stored before this rule (e.g. 1234SP) is cleaned to 1234 on auto-fill.

Employee ID is at most 4 digits (Sept 15, 2026, user decision) — maxlength="4", plus .slice(0, 4) in both input listeners (script-set values ignore maxlength), a bilingual guard on both submit paths, and an HTTP 400 in qr_checkin when the base ID has more than 4 digits; last-work-type stays quiet for longer IDs. Shorter IDs are still accepted. Longer IDs are refused, never truncated (a cut-down ID is a different employee), and a stored ID longer than 4 digits is not auto-filled. An employee with a 5+ digit ID cannot check in — if that changes, raise all four together: the maxlength attribute, EMPLOYEE_ID_MAX_DIGITS (qr_destination.html), QR_EMPLOYEE_ID_MAX_DIGITS (qr_destination.js) and CHECKIN_EMPLOYEE_ID_MAX_DIGITS (routes/qr_codes.py). The two JS constants have different names on purpose: both scripts share the page's global scope, and a duplicate top-level const is a SyntaxError that would break the whole page.

Type of Work — Anti-Mistake Measures (Sept 2026)

1. Work type echoed on the submit button — REMOVED (Sept 2026). The button briefly carried a second line naming the selected type; it broke the button layout and was reverted at the user's request. The label is plain Check In / Entrada or Check Out / Salida again. Do not re-add it. renderSubmitButton() survives as the single place that builds the button markup, so resetEnhancedSubmitButton() does not duplicate it — it still returns early while the button is disabled, so it cannot clobber the "Processing" spinner. Confirmation of the type is measures 2 and 3 only.

2. The success card shows what was stored. A Type of Work / Tipo de Trabajo row sits under Employee ID. Its value comes from the check-in response (data.work_type, data.work_type_label), not the form, so a wrong pick is visible immediately. Both renderers fill it: the inline one in qr_destination.html and handleCheckinSuccess() in qr_destination.js (updateElement("successWorkType", ...)).

3. Check-out inherits the open check-in's type. GET /qr/<url>/last-work-type?employee_id=&selected_location_name= returns the work type of the employee's most recent unpaired check-in, and the page pre-selects it with an amber bilingual reminder ("You checked in as Special Project at 07:12 AM (Bldg A). Confirm or change the type of work.").

• Answers only on Check Out scans. _resolve_qr_event() resolves the scan's effective event exactly as qr_checkin does — a dynamic QR inherits the event of the standard QR behind the selected location, and returns None while no location is chosen.
• _resolve_record_event() resolves a stored record's event the same way (attendance_data does not store the event).
• Latest scan in a one-day window (so overnight shifts resolve). If it was a check-in the pair is open → suggest its type; if it was a check-out the pair is closed → quiet.
• Matches every stored ID spelling via expand_employee_id_filter().
• Every failure path returns {'work_type': None}, HTTP 200 — a broken hint must never block a check-out.
• work_type: "" means an open Regular check-in (still worth confirming); work_type: null means "no suggestion, stay quiet".
• Fires 500 ms after ID entry, on load (the ID is usually auto-filled), and again after a dynamic QR's location is confirmed. A manual dropdown change sets workTypeChosenByEmployee and the suggestion never overwrites it afterwards.

The form is replaced 1.5 s after load — the biggest trap here. initializeLocationServicesCheck() in qr_destination.js clones #checkinForm and swaps it in, to take over the submit handler. cloneNode(true) copies attributes, not live control state, so the <select> reverted to the option carrying the selected attribute (Regular) and typed input values were lost — 1.5 s after load, i.e. after every re-apply had already run. The amber reminder survived because its text and inline style are attributes on the cloned nodes, which is exactly what the symptom looked like: correct type shown briefly, then back to Regular, reminder still right.

Three things keep this working — do not remove any of them:

1. preserveFormControlState(oldForm, newForm) copies each control's live value / checked into the clone (matched by position, since the clone is structurally identical — no selector escaping).
2. The replace dispatches a checkinFormReplaced CustomEvent; the page re-binds attachEmployeeIdListeners() + attachWorkTypeListeners() on the new nodes and re-asserts the suggestion. Every listener bound to the old controls dies with them.
3. All re-attachable listeners live in those two functions — never bind directly to form controls in an init function, or the binding is lost at 1.5 s.

This also silently affected a manual pick: choosing Covering within 1.5 s of load and submitting afterwards would have submitted Regular.

iOS Safari — do not undo these three guards. On iPhone the dropdown reverted to Regular while desktop browsers worked, because Safari restores form-control state after load (i.e. after the fetch resolves) and caches plain GETs aggressively:

1. suggestedWorkTypeCode is retained and re-applied by applyWorkTypeSuggestion() — on response, at +250 ms, at +900 ms, and on pageshow. A single set at response time is not enough.
2. A change event only counts as the employee's choice when a real interaction (pointerdown / touchstart / focus / keydown) preceded it — tracked by workTypeTouchedByEmployee. Safari's restore fires change too, and without this guard it permanently latched workTypeChosenByEmployee, blocking every suggestion.
3. The fetch uses cache: "no-store" and the endpoint replies through _no_store_json() (Cache-Control: no-store, no-cache, max-age=0, must-revalidate + Pragma: no-cache) on all seven return paths — a cached "no suggestion" is indistinguishable from the bug this endpoint exists to prevent.

• The URL is built from window.location.pathname with trailing slashes stripped, so a scanner that appends / cannot produce //last-work-type.
• The amber reminder is shown even if the dropdown did not take the value, so the employee always sees what they checked in as. Diagnostic: reminder visible but dropdown on Regular = the <select> is being overwritten; no reminder at all = the fetch failed or was served stale.
• GET, so the CSRF before_request (POST/PUT/PATCH/DELETE only) does not apply.
• Public and unauthenticated like the check-in page: a guessed ID reveals whether it has an open check-in, with time and location — the same disclosure class as the existing 30-minute cooldown message.

Check-In Flow

1. Employee scans QR → qr_destination.html
2. Enters numeric ID and picks Type of Work (Regular by default); GPS captured by browser
3. On a Check Out scan the page pre-selects the open check-in's work type
4. Work-type code appended to the ID server-side (1234 → 1234SP)
5. 30-min interval guard (configurable via TIME_INTERVAL)
6. Dynamic QR: server rejects if selected_location_name is empty; location_name in record is always the resolved name, never 'Dynamic'
7. Haversine distance calculated; photo required if beyond threshold (and both toggles enabled)
8. Server-side photo size check: rejects > VERIFICATION_PHOTO_MAX_SIZE with HTTP 413
9. Record saved to attendance_data; success card echoes the stored Type of Work

Interval guard caveat: the cooldown keys on the composed ID, so 1234 and 1234SP do not block each other — an employee can check in as Regular and again as Covering within the interval. This is pre-existing behaviour for suffixed IDs.

Check-In Page Features

• Bilingual: English and Spanish shown side by side — coloured english-text / spanish-text spans separated by language-separator. There is no toggle button in this template; the data-en / data-es toggle code in qr_destination.js is inert here
• Type of Work dropdown + submit-button echo + check-out reminder (see above)
• Staff ID persistence: localStorage remembers last employee ID
• Android GPS: special handler (android_location_handler.js)
• QR URLs must be full https:// absolute URLs — relative URLs parsed as search queries by phones
• Base URL constructed from QR_BASE_URL env var — never from request.url_root

---

12. Time Attendance Import Pipeline

Excel File Requirements

• Required columns: ID, Date, Time, Location Name, Action Description
• Optional columns: Name, Platform, Event Description, Recorded Address, Distance

Import Flow

1. Upload → validate_import_file
2. Duplicate analysis → analyze_import_duplicates → user reviews
3. Invalid record analysis → analyze_import_invalid
4. Start job → start_import_job (SSE streaming progress via stream_import_progress)
5. Result in time_attendance_import_result.html

Special Handling

• Recorded Address: read via openpyxl directly (not pandas) to preserve HYPERLINK formulas
• Duplicate detection: hash of employee_id + date + time + action_description
• _clean_employee_id() normalises IDs to the stored form (Set 26): 1234.0 → 1234, 1759.PW / 1759 - PW / PW.1759 → 1759PW. Zero padding is preserved (01234) and a real fraction (1234.5) is never truncated — both would change the duplicate hash of rows already imported, or silently move hours to another employee
• Import tracked by import_batch_id (UUID)
• self.db not db — in TimeAttendanceImportService, always access SQLAlchemy via self.db
• Dates go through _parse_date_field() (Set 27) — never pd.to_datetime() directly. It handles real date cells, Excel serial numbers (a General-formatted column: 45123 → 2023-07-16, which pd.to_datetime() read as 1970-01-01), and text dates month-first (US time clocks), falling back to day-first only when the first number cannot be a month (13/04/2026). Unparseable dates fail the row instead of importing a wrong one
• A failed import removes its own rows (_rollback_partial_batch()): the loop commits every 50 records, so a failure part-way used to leave a partial batch with nothing marking it incomplete
• Validation except blocks must not silently swallow exceptions (fail-open prevention)

---

13. Time Attendance Excel Export

Calculator — CRITICAL

Always use WorkingHoursCalculator from working_hours_calculator.py. Never switch to SingleCheckInCalculator — legacy, causes silent calculation errors.

Rounding Rules

• Daily / Weekly / Grand Total + SP/PW/PT summary columns: _qtr() — quarter-hour rounding
• Individual Hours/Building entry cells: raw round(..., 2) — no quarter rounding
• _qtr() pipeline: decimal hours → minutes → round_time_to_quarter_hour() → convert_minutes_to_base100() → round_base100_hours()

Work Type Codes

• SP = Special Project, PW = Periodic Work, PT = Project Team (Part-Time), C = Covering (added Sept 2026)
• Parsed from employee_id via parse_employee_id_for_work_type()
• Suffix and prefix forms, separated by any run of non-alphanumeric characters, or nothing: 1234SP, 1234 SP, 1234.PW, 1234-PT, 1234 . C, SP1234, SP 1234, PW.1234. Two-letter codes are matched before the single-letter C
• The separator class is [^0-9A-Z]* in all three parsers — parse_employee_id_for_work_type(), build_employee_id_regex() (SQL filters) and parseEmployeeIdWorkType() (report JS). Keep them in sync: until Sept 16 2026 the Python one accepted only a space, so 1759.PW was counted as a separate Regular employee in the exports while the report showed it as PW (Set 26)
• Not work types: 1234.5 (no code) and 1234SPX (code runs into a word) — both stay regular with the ID unchanged
• Like SP/PW/PT, C hours are excluded from the 40-hour overtime rule
• Codes are declared in four places — keep them in sync:

  File	Symbol
  working_hours_calculator.py	work_type_codes list in parse_employee_id_for_work_type()
  utils/helpers.py	WORK_TYPE_CODES (drives report/export ID filters)
  routes/qr_codes.py	VALID_CHECKIN_WORK_TYPES + WORK_TYPE_LABELS
  static/js/attendance_report.js	regex alternations in parseEmployeeIdWorkType()

• single_checkin_calculator.py is legacy and deliberately not updated — its aggregation dicts are keyed regular/SP/PW/PT, so adding C to its parser alone would raise KeyError

Overtime — 40-Hour Rule (Sept 16, 2026, confirmed by the user)

SP / PW / PT / C hours are paid but never build toward overtime. Both exports compute the weekly Regular and OT columns from regular hours only:

week_regular  = min(weekly_regular_hours, 40.0)   # NOT weekly_total_hours
week_overtime = max(0, weekly_regular_hours - 40.0)

• weekly_regular_hours accumulates _qtr() of each day's regular-only pair hours; _pair_is_regular(in, out) decides, preferring the OUT record's work type (a Regular IN paired with an SP OUT counts as SP, mirroring effective_work_type).
• Column H (Weekly Total) still shows every hour worked, so H ≠ Regular + OT whenever the employee has special hours — the SP/PW/PT/C and Regular rows below break that down.
• Example: 36 h Regular + 8 h SP → H 44, Regular 36, OT 0 (before Sept 16: OT 4).
• Export by Building applies the same rule per BUILDING, so an employee with 30 h at two buildings shows 0 OT in each block while the main export shows 20 h. A note row under the date range says so. Treat that sheet as review-only; pay from the main export (§20 Set 25).

Overnight Shift Handling

Rule 1 — Sort key: early-morning OUTs (hour <= 3) use _overnight_aware_sort_key() which adds 86400 seconds — pushes them past midnight so they sort after same-day evening INs.

Rule 2 — Detection threshold: hour >= 12 (noon). Any IN at or after 12:00 PM is an overnight IN candidate if no matching OUT exists same day AND early-morning OUT (hour <= 3) exists next calendar day.

Orphan guard: early-morning OUTs with check_in_date <= current_day are orphans — skip. Only OUTs with check_in_date > current_day (moved by overnight detection) pair with evening INs.

Cross-Type SP Pairs

is_cross_type = True when SP IN pairs with non-SP OUT (or vice versa). Accumulates separately in cross_type_sp/pw/pt/c_hours to prevent double-counting in summary rows. effective_work_type prefers the OUT's code, so forgetting the type on one scan of a pair still attributes the hours to the special type — forgetting on both is the real payroll error, which is what the check-out reminder in §11 targets.

Summary Rows (SP / PW / PT / C + Regular)

col8='SP'       col9=_qtr(sp_hours)           ← only if sp_hours > 0
col8='PW'       col9=_qtr(pw_hours)           ← only if pw_hours > 0
col8='PT'       col9=_qtr(pt_hours)           ← only if pt_hours > 0
col8='C'        col9=_qtr(c_hours)            ← only if c_hours > 0
col8='Regular'  col9=_qtr(regular_only_hours) ← always when any special type exists
col7='GRAND TOTAL:' col9=_qtr(grand_regular) col10=_qtr(grand_ot)

Both exports emit the C row: the main export from grand_totals['c_hours'] + cross_type_c_hours, the by-building export from _building_special_hours(emp_records, 'C'). WorkingHoursCalculator returns c_hours / c_minutes at daily, weekly, and grand-total level alongside the existing sp/pw/pt keys.

Export Date Range

_resolve_date_range() enforces a 14-day cap by default. Both export functions accept unlimited=False; pass unlimited=True to bypass.

UI: "Unlimited" checkbox in time_attendance_records.html before export buttons.

Export by Building — Extra Sheets (Sept 2026)

export_time_attendance_by_building_excel() writes Sheet0 unchanged, then adds two sheets built from row bookkeeping collected while Sheet0 is written (_bb_blocks, _bb_sp_summary_rows, each employee's weeks dict) — Sheet0 is never re-read or modified:

1. Filtered Report (_build_filtered_building_sheet) — copy of Sheet0 minus every employee block whose base ID is in BUILDING_SUMMARY_EXCLUDED_EMPLOYEE_IDS (project managers; leading zeros ignored), minus the SP summary rows. Punch rows whose location carries (SP) and all GRAND TOTAL rows are kept. Buildings left with no employee are dropped and the rest renumbered.
2. Weekly Hours by Location (_build_weekly_hours_by_location_sheet) — one row per employee per building, hours per report week (anchored to the report start date like the Weekly Total rows), a Location Totals table (SUMIF formulas) and the project total. SP hours are excluded (pair is SP when its effective type — OUT's code first — is SP); PW/PT/C are counted. Each day is _qtr()-rounded before summing, so non-SP employees match Sheet0's Weekly Total exactly. Both are wrapped in try/except — a failure removes them and still delivers Sheet0.

Employee Name Format

"Lastname, Firstname" — f"{emp.lastName}, {emp.firstName}"

Export Date Iteration

Cap sorted_dates to <= end_date — prevents overnight buffer day from rendering as a display row.

Cross-Building Pairing

Same-day IN at Building A + OUT at Building B → pair and label "IN: Building A → OUT: Building B". Suppress spurious Missed Punch rows.

---

14. Logging System (logger_handler.py)

Single AppLogger instance in extensions.py. Import everywhere as:

from extensions import logger_handler
logger_handler.logger.info("...")
logger_handler.logger.error("...", exc_info=True)  # always pass exc_info=True in except blocks

logger_handler is a proxy object, not the AppLogger (extensions.py, Set 24). Modules imported before create_app() runs init_logger() — utils/helpers.py, utils/template_helpers.py, utils/geocoding.py — copy this reference at import time. It used to be a plain None, so any logging call from those modules raised AttributeError: 'NoneType' object has no attribute 'logger'. The proxy forwards to the real AppLogger once init_logger() builds it, and to a stdlib logger before that. Do not restore logger_handler = None, and do not test it for None/truthiness — it is always truthy.

Log Destinations

• logs/application.log — rotating 10MB/5 backups
• logs/errors.log — rotating 5MB/10 backups
• logs/security.log — rotating 2MB/20 backups
• log_events DB table — admin dashboard at /admin/logs

Log Every Action

All create, edit, and delete operations must include a log entry:

logger_handler.logger.info(f"User {session['username']} created employee {new_employee.id}")

traceback Convention

• Use exc_info=True on logger.error() — never import traceback inline
• traceback.format_exc() only acceptable when passing stack_trace= to log_flask_error()

---

15. Configuration (config.py + .env)

All env-var reads centralized in config.py. Blueprints use current_app.config['KEY'].

Key .env Variables

DATABASE_URL                           # mysql+pymysql://user:pass@host/db — special chars in password OK
SECRET_KEY                             # MUST differ from default
COMPANY_NAME / CONTRACT_NAME
FLASK_HOST / FLASK_PORT / FLASK_ENV / DEBUG
SESSION_COOKIE_SECURE                  # MUST be 'false' for HTTP-only deployments
SESSION_COOKIE_HTTPONLY / SESSION_COOKIE_SAMESITE
TIME_INTERVAL                          # Check-in cooldown in minutes (default 30)
BUILDING_SUMMARY_EXCLUDED_EMPLOYEE_IDS # Export by Building PM IDs to filter (default 4921,4944,4816,3979; empty = none)
GOOGLE_MAPS_API_KEY                    # Optional; falls back to Haversine-only
TURNSTILE_ENABLED / TURNSTILE_SITE_KEY / TURNSTILE_SECRET_KEY
ENABLE_PHOTO_VERIFICATION              # Global toggle (default 'true')
PHOTO_VERIFICATION_DISTANCE_THRESHOLD  # Miles (default 0.3)
VERIFICATION_PHOTO_MAX_SIZE            # Bytes (default 5MB)
UPLOAD_FOLDER                          # Temp path (default /tmp)
DEFAULT_ADMIN_PASSWORD                 # CHANGE IN PRODUCTION
QR_BASE_URL                            # Public-facing domain — used for QR images generated at startup (none generated if unset)
TRUSTED_PROXY_COUNT                    # Reverse proxies in front of the app (default 1 = Nginx; 2 with Cloudflare; 0 = none)
MAX_UPLOAD_SIZE_MB                     # Whole-request cap, default 50 (Nginx client_max_body_size must be >= this)
                                       # MAX_FORM_MEMORY_SIZE = VERIFICATION_PHOTO_MAX_SIZE + 1 MB (Flask 3.1 default 500 KB
                                       # would 413 the base64 photo field) — set in config.py, not .env
THEME_NAME                             # Activates static/css/theme-{THEME_NAME}.css
SYNC_INTERVAL_MINUTES
SQLALCHEMY_ENGINE_OPTIONS_POOL_SIZE / POOL_TIMEOUT / POOL_RECYCLE / MAX_OVERFLOW

---

16. Distance Calculation

Uses Haversine formula only (straight-line geodesic distance). Google Maps Distance Matrix API removed.

utils/geocoding.py initializes gmaps_client for geocoding (address → coordinates) if GOOGLE_MAPS_API_KEY is set.

Google Maps Guard — ALWAYS USE

from utils.geocoding import is_gmaps_available, gmaps_client
if is_gmaps_available():
    result = gmaps_client.geocode(address)
else:
    # fall back to OpenStreetMap / Haversine

Never call gmaps_client.method() without a None guard.

Address Normalization

Multi-strategy matching: core street extraction → component matching → fuzzy fallback. Handles geocoding drift for near-identical addresses.

---

17. Migration Scripts

All in tools/. Always use pymysql directly — never import the Flask app or SQLAlchemy ORM. ORM loads models at import time; if target column doesn't exist yet, it crashes on startup.

Pattern:

from dotenv import load_dotenv
load_dotenv()
import pymysql, re

def parse_db_url(url):
    """Use regex — urlparse breaks on special chars (@, :) in passwords."""
    url = re.sub(r'^mysql\+pymysql://', '', url)
    m = re.match(
        r'^(?P<user>[^:]+):(?P<password>.+)@(?P<host>[^@:/]+)(?::(?P<port>\d+))?/(?P<db>[^?]+)',
        url
    )
    return {'host': m.group('host'), 'port': int(m.group('port') or 3306),
            'user': m.group('user'), 'password': m.group('password'), 'database': m.group('db')}

def run():
    conn = pymysql.connect(**parse_db_url(os.environ['DATABASE_URL']), charset='utf8mb4', autocommit=False)
    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE ...")
        if cur.fetchone()[0] > 0:
            print("[SKIP] already exists"); return
        cur.execute("ALTER TABLE `table` ADD COLUMN `col` TINYINT(1) NOT NULL DEFAULT 1")
        conn.commit()

Completed Migrations

Script	What it adds
migration_photo_verification_toggle.py	qr_codes.photo_verification_enabled TINYINT(1) DEFAULT 1
migration_dynamic_qr_locations.py	qr_type column + qr_code_locations table
migration_PM_permissions.py	user_project_permissions + user_location_permissions tables
migration_attendance_indexes.py	attendance_data indexes: idx_ad_date_time, idx_ad_emp_date_time, idx_ad_qr_emp_date, idx_ad_location_name, idx_ad_verif_status — skips names/leading columns that already exist; online DDL with plain ALTER fallback (Set 23)

---

18. Coding Conventions & Developer Preferences

Change Philosophy

• Minimal, additive changes only — preserve all route names, function names, endpoint names, variable names, URL patterns
• New functionality added alongside existing code, not replacing it
• Never rename routes, functions, endpoints, or variables
• Every code fix applies to both LT and GOV instances

File Delivery

• ≤ 4 files changed → present each file individually
• ≥ 5 files changed → deliver as a single zip archive

File Editing Approach

• Pull latest code at start of each session
• CRLF normalization first if needed: content.replace('\r\n', '\n')
• Use surgical str_replace edits — never rewrite large blocks wholesale
• AST parse check after every Python edit: python3 -c "import ast; ast.parse(open(f).read())"
• Simulate logic before and after any calculator or pairing logic changes

SQLAlchemy Patterns

# Correct (SQLAlchemy 2.0):
record = db.session.get(Model, record_id)
if record is None:
    abort(404)

# Deprecated — do not use:
record = Model.query.get_or_404(record_id)
record = Model.query.get(record_id)

# Raw SQL requires text():
db.session.execute(text("SELECT ..."), params)
# Never pass raw strings to conn.execute() — ObjectNotExecutableError in SQLAlchemy 2.0

JavaScript Patterns

• Use createElement + addEventListener — never inline onchange, onclick, etc.
• Wrap in DOMContentLoaded
• CSS → {% block extra_head %}, JS → {% block extra_scripts %}, never into {% block title %}
• All AJAX POST: 'X-CSRF-Token': (window.qrConfig && window.qrConfig.csrfToken) || ''
• No localStorage / sessionStorage in artifacts

Error Handling Pattern

Every route except block must:

1. db.session.rollback()
2. logger_handler.logger.error(f"...: {e}", exc_info=True)
3. flash(...) a user-facing message
4. Return redirect or error response

No bare except: — always except Exception as e:.

QR Code Name — Locked After Creation

The QR code name cannot be changed after creation because it is used to generate the qr_url slug. Changing the name would break all existing printed/distributed QR codes.

• Template (edit_qr_code.html): name <input> has readonly attribute + cursor: not-allowed style + lock icon in label
• Route (routes/qr_codes.py): edit POST ignores request.form['name'] — sets new_name = qr_code.name (original value). Do NOT change this back to reading from the form.

# CORRECT — name locked after creation:
new_name = qr_code.name  # always use existing name, never request.form['name']

# WRONG — never do this in the edit route:
# new_name = request.form['name']

Employee Autocomplete (attendance and time-attendance)

• Visible text input + hidden employee_id field synced on numeric input
• Fetches /api/search_employees (includes unregistered IDs from attendance_data, not only Employee table)
• CSS inlined in extra_head; JS uses createElement + addEventListener

Timestamp Convention

Use datetime.now() (local time) throughout — not datetime.utcnow().

Check-in dates/times are stored local, so anything compared against them must be local too. Fixed in Set 27: routes/attendance_edit.py (record timestamps + audit note), routes/dashboard.py ("today" / "last 30 days" stats, which rolled over mid-evening) and app.py (CURRENT_YEAR). Still UTC on purpose or harmlessly: users.last_login_date, security-middleware event stamps, qr_codes.coordinates_updated_date, import batch import_date.

Context Safety

• Use has_request_context() (not if not request:) to check Flask request context
• Capture current_app._get_current_object() in route body, not inside lazy generators

---

19. Attendance Report

Query fetches 1,001 rows, trims to 1,000 if extra row present, sets records_truncated = True. Template displays yellow banner when truncated:

Showing the most recent 1,000 records. Narrow the date range or apply additional filters.

Employee Filter — Two Layers, Both Must Be Work-Type Aware

The employee filter is applied twice, and both passes must agree or extra-work rows silently disappear:

1. Server — routes/attendance.py expands each selected ID via expand_employee_id_filter() into an IN (...) list plus REGEXP patterns.
2. Client — applyFilters() in static/js/attendance_report.js re-filters the server-rendered rows on every page load (initializeReport() calls it). It uses parseEmployeeIdWorkType() and compares base IDs, never whole strings.

Semantics (identical on both sides, mirroring _work_type_codes_for()):

• a plain 1234 matches the regular record and 1234SP / 1234PW / 1234PT / 1234C
• an ID that already carries a code (1234SP) matches only that code
• leading zeros tolerated (01234 = 1234); 12345 must never match a 1234 filter

Do not reintroduce record.employeeId.toLowerCase() === id in applyFilters() — that exact-match test is what dropped every SP/PW/PT row the query had already returned.

Live Updates (Sept 15, 2026)

The records table picks up new check-ins without a reload. It uses polling, not SSE/WebSockets: a held-open stream per tab would pin a gevent worker connection and still need a DB poll behind it (the workers share no pub/sub).

• Endpoint: GET /api/attendance/live-updates?since_id=&date_from=&date_to=&location=&employee=&project= (attendance_live_updates_api, @login_required), JSON with no-store headers.
• Cheap path: SELECT COALESCE(MAX(id), 0) FROM attendance_data (primary key). Not newer than since_id → empty answer, no other query. Otherwise ONE query over ad.id > since_id AND ad.id <= latest plus the page's filters, ORDER BY ad.id LIMIT 201 (200 per batch; has_more → the page fetches the next batch after 1 s). verification_photo (base64) is never selected; the location_accuracy information_schema check is cached per process.
• Same filters as the page: _attendance_filter_conditions() builds the WHERE clause for both the report route and the endpoint, Project Manager scope included. Never duplicate that logic.
• Cursor: the route reads MAX(id) before the report query and renders it as data-live-since-id on #attendanceReportContainer. No attribute (PM without access, lookup failed) → live updates stay off.
• Payload shape: _live_record_payload() returns exactly what loadTableData() reads from the server-rendered <tbody> (text, truncation, accuracy parsing, verification badge). If the row markup in attendance_report.html changes, update _live_record_payload() too.
• Client (attendance_report.js, LIVE UPDATES section): polls every 20 s, never overlapping; paused while document.hidden, one immediate check when the tab is shown; exponential backoff 40 s → 5 min after errors; stops for good on redirect / 401 / 403 (session ended). New rows are prepended newest-first, de-duplicated by id, highlighted for 8 s, and refreshTableKeepingView() keeps the user's search, sort and page. A page showing the empty state reloads once when a matching record arrives. A green Live pill in the table header shows the state.
• Not live: edits, deletions and the summary statistics — they refresh on reload.
• createTableRow() HTML-escapes every value (escapeHtml; escapeJsString inside onclick; truncateText runs before escaping) — device and address text come from the public check-in page.

---

20. Known Bugs Fixed — Do Not Reintroduce

Set 1 — Critical

File	Fix
routes/time_attendance_export.py	Removed stray second docstring
app.py	Error handlers wired to render_template('errors/*.html'); 403 added
templates/errors/*.html	url_for('dashboard') → url_for('dashboard.dashboard')
routes/payroll.py	Removed duplicate get_employee_name, get_qr_code_checkin_count, @bp.context_processor
models/attendance.py	check_in_time default: datetime.now().time → lambda: datetime.now().time()
models/user.py	Added @staticmethod to has_export_permissions()

Set 2 — Moderate / Minor

File	Fix
app.py	Merged duplicate before/after request hooks
app.py	update_existing_qr_codes() uses QR_BASE_URL first, not request.url_root
config.py	SQLALCHEMY_ENGINE_OPTIONS pool wiring
utils/geocoding.py	is_gmaps_available() helper
working_hours_calculator.py	CRLF → LF

Set 3 — Structural

File	Fix
routes/time_attendance_export.py	Removed 14 unused imports
routes/dashboard.py, statistics.py, payroll.py	db.session.rollback() in all except blocks
models/employee.py	cls.id.like() on BigInteger → cast(cls.id, String).like()

Set 4 — Export Pipeline

File	Fix
routes/time_attendance_export.py	Overnight IN threshold: >= 19 → >= 12
routes/time_attendance_export.py	SP/PW/PT summary rows + Regular row
routes/time_attendance_export.py	unlimited=False parameter throughout
templates/time_attendance_records.html	"Unlimited" checkbox

Set 5 — GOV Deployment

File	Fix
routes/qr_codes.py	_get_qr_base_url() helper; replaced all request.url_root
config.py	QR_BASE_URL env var
nginx (GOV)	Removed duplicate proxy_set_header Host (doubled hostname in QR links)

Set 6 — Theme System

File	Fix
static/css/theme-gov.css	New file — GOV brand green overrides
app.py	inject_company_name() returns THEME_NAME and CURRENT_YEAR
templates/base.html + base_authenticated.html	Conditional theme CSS; {{ COMPANY_NAME }} for brand text; {{ CURRENT_YEAR }} in footer

Set 7 — Security Hardening

File	Fix
routes/statistics.py, routes/payroll.py	SQL injection: f-string filters → parameterized queries
routes/auth.py	Open redirect: _is_safe_url() on next param
app.py	SECRET_KEY default guard: sys.exit(1)
routes/auth.py	Session fixation: session.clear() before new keys
routes/auth.py	@login_required + @admin_required on /register
app.py	CSRF SecurityManager wired; before_request validator
All POST forms (22 templates)	{{ csrf_token() }} hidden field
All AJAX POST calls	X-CSRF-Token header
advanced_security_middleware.py	request.json guarded with content-type check (fixes 415)
advanced_security_middleware.py	Stable key from SECRET_KEY via SHA-256 (fixes per-worker warning)
advanced_security_middleware.py	validate_session_security() removed from security_check() (fixes 401 under multi-worker)
All route files	Bare except: → except Exception (11 locations)
routes/qr_codes.py	Server-side photo size enforcement; HTTP 413 on oversized payload

Set 8 — Code Quality

File	Fix
7 route files	Model.query.get_or_404() → db.session.get() + abort(404) (21 call sites)
config.py	PERMANENT_SESSION_LIFETIME → timedelta(hours=10) (later restored to 30 days — the 10-hour limit now lives in adjust_session_lifetime(), see Set 19)
routes/attendance.py	Split into 4 files sharing one blueprint
requirements.txt	mysql-connector-python removed
routes/attendance.py	LIMIT 1001 + records_truncated flag + yellow banner

Set 9 — Template Sync (May 2026)

File	Fix
templates/base.html (GOV)	Footer hardcoded 2025 QR Code Management System → {{ CURRENT_YEAR }} {{ COMPANY_NAME }}
templates/base.html (GOV)	Theme CSS block moved to after style.css, before Font Awesome (matches LT order)
templates/base_authenticated.html (GOV)	Indentation sync with LT
templates/projects.html (GOV)	Confirm dialog on project toggle + loading state on Edit buttons (sync with LT)

Set 10 — Per-QR Photo Verification Toggle (May 2026)

File	Change
models/qrcode.py	Added photo_verification_enabled TINYINT(1) DEFAULT 1
routes/qr_codes.py	Create: reads toggle from form, passes to constructor + logs
routes/qr_codes.py	Edit: reads toggle from form, assigns to record + logs
routes/qr_codes.py	Checkin: checks per-QR AND global flag (both must be True)
templates/create_qr_code.html	Photo Verification toggle section added
templates/edit_qr_code.html	Photo Verification toggle section added (CSS in extra_head, JS in extra_scripts with addEventListener)
tools/migration_photo_verification_toggle.py	pymysql migration — adds column safely

Set 11 — QR Name Lock Restored + CSRF Fix (May 2026)

File	Change
templates/edit_qr_code.html	Name field restored to readonly — lock icon in label, cursor: not-allowed, help text explaining why
routes/qr_codes.py	Edit route: new_name = qr_code.name (never reads from form) — prevents name change server-side
templates/create_qr_code.html	window.qrConfig with csrfToken injected in <head> — fixes 403 on /api/geocode (standalone template, doesn't extend base)
templates/base_authenticated.html	window.qrConfig block moved before {% block extra_scripts %} — fixes token availability for all pages extending base

---

Set 12 — Type of Work on Check-In + C Work Type (Sept 2026)

File	Change
templates/qr_destination.html	Type of Work <select> (Regular / PW / SP / C) after Employee ID, bilingual labels
templates/qr_destination.html	Employee ID numeric-only: inputmode, pattern, input-strip, submit guard, stored-ID cleanup
static/js/qr_destination.js	Same numeric-only filter + work_type appended to the check-in FormData
routes/qr_codes.py	VALID_CHECKIN_WORK_TYPES; work_type validated and appended to the numeric ID; existing typed suffix stripped first
working_hours_calculator.py	C added to the parser and to every regular/SP/PW/PT dict; c_hours / c_minutes at daily, weekly, grand-total level
utils/helpers.py	WORK_TYPE_CODES includes C (report + export ID filters)
routes/time_attendance_export.py	C location suffix, cross_type_c_hours, excluded from regular_only_hours, C summary row in both exports
—	No migration — the code lives inside attendance_data.employee_id

Set 13 — Attendance Report Client-Side Filter Dropped Extra-Work Rows (Sept 2026)

File	Fix
static/js/attendance_report.js	applyFilters() compared record.employeeId === id exactly, filtering out the SP/PW/PT rows the SQL had correctly returned. Added parseEmployeeIdWorkType() + base-ID comparison — see §19

Set 14 — Type of Work Anti-Mistake Measures (Sept 2026)

File	Change
routes/qr_codes.py	WORK_TYPE_LABELS + _work_type_label(); work_type / work_type_label added to the check-in success payload
routes/qr_codes.py	_resolve_qr_event(), _resolve_record_event(), GET /qr/<url>/last-work-type — check-out inherits the open check-in's work type
templates/qr_destination.html	renderSubmitButton() centralises the button markup; resetEnhancedSubmitButton() delegates to it (the work-type line it briefly carried was reverted — see Set 17)
templates/qr_destination.html	Type of Work row on the success card; #workTypeHint amber reminder; initializeWorkTypeSelector() + loadOpenCheckInWorkType()
static/js/qr_destination.js	updateElement("successWorkType", ...) in handleCheckinSuccess()

Set 15 — iOS Safari Reset the Suggested Work Type (Sept 2026)

File	Fix
templates/qr_destination.html	Suggestion retained in suggestedWorkTypeCode and re-applied via applyWorkTypeSuggestion() on response, +250 ms, +900 ms and pageshow — Safari's form restore lands after the fetch
templates/qr_destination.html	change counts as the employee's choice only after a real interaction (workTypeTouchedByEmployee); Safari's restore-fired change no longer latches the flag
templates/qr_destination.html	fetch(..., { cache: "no-store" }); URL built from pathname with trailing slashes stripped
routes/qr_codes.py	_no_store_json() sets Cache-Control / Pragma on all seven return paths of qr_last_work_type

Set 16 — Form Clone Discarded the Selected Work Type (Sept 2026)

File	Fix
static/js/qr_destination.js	preserveFormControlState() copies live value / checked into the clone before replaceChild — cloneNode(true) had reset the work-type <select> to Regular and cleared typed input values 1.5 s after load
static/js/qr_destination.js	Dispatches checkinFormReplaced after the swap so the page can re-bind listeners the clone destroyed
templates/qr_destination.html	Listeners split into re-attachable attachEmployeeIdListeners() / attachWorkTypeListeners(); checkinFormReplaced handler re-binds both, re-applies the suggestion, re-renders the button

Set 17 — Submit Button Label Reverted to Plain Check In / Check Out (Sept 2026)

File	Change
templates/qr_destination.html	Work-type line removed from the submit button — it broke the button layout. .submit-work-type CSS deleted, and the renderSubmitButton() calls that only existed to refresh it (dropdown change, applyWorkTypeSuggestion, form re-bind, init) dropped
—	The type of work is still confirmed by the amber check-out reminder and the success-card row — do not re-add it to the button

Set 18 — Export by Building: Filtered Report + Weekly Hours by Location (Sept 2026)

File	Change
routes/time_attendance_export.py	Row bookkeeping in the by-building loop; _build_filtered_building_sheet() + _build_weekly_hours_by_location_sheet() add two sheets after Sheet0 (see §13). Sheet0 verified cell-for-cell identical to the previous export
config.py	BUILDING_SUMMARY_EXCLUDED_EMPLOYEE_IDS (comma-separated, default 4921,4944,4816,3979)
templates/time_attendance_records.html	Tooltip on the Export by Building button
—	Replaces the manual "Copilot" procedure (delete PM rows + SP rows, then build a weekly table). Decisions: PM IDs removed from both sheets; SP hours excluded from weekly hours

Set 19 — Remember Me Logged Users Out Within 30 Days (Sept 15, 2026)

File	Fix
app.py	adjust_session_lifetime() no longer assigns app.permanent_session_lifetime (10 h / 30 d per request). Flask validates the cookie's age against that worker-wide value in open_session() before hooks run, so any request without Remember Me — including the login POST itself and anonymous QR scans — made Remember Me cookies older than 10 h unreadable; under gevent it could also stamp a 10 h expiry on them. The hook now clears non-Remember-Me sessions 10 h after login_epoch instead
routes/auth.py	Login sets session['login_epoch']
config.py	Comment corrected: PERMANENT_SESSION_LIFETIME stays 30 days — it is the cookie age limit for every session
—	Verified by loading the real hook (before/after) into a Flask app with a controlled clock: Remember Me now survives 11 h, 25 days of daily use and 29 idle days, and expires after 31 idle days; non-Remember-Me still ends at 10 h
—	No forced re-login on deploy: valid sessions keep working; non-Remember-Me sessions from before the deploy (no login_epoch) get their 10 hours counted from their first request after it

Set 20 — Attendance Report Live Updates (Sept 15, 2026)

File	Change
routes/attendance.py	_attendance_filter_conditions() extracted from attendance_report(); live_latest_id cursor; GET /api/attendance/live-updates + _live_record_payload() (see §19)
templates/attendance_report.html	data-live-since-id on the container; Live status pill + new-row highlight CSS
static/js/attendance_report.js	LIVE UPDATES module; filterRecords() split out of applyFilters(), sortFilteredData() split out of sortTable()
static/js/attendance_report.js	Security: createTableRow() put raw device / address / name text into innerHTML. Device comes from the check-in User-Agent, so a crafted UA could inject markup into the report — every value is now escaped
—	Verified: filter helper produces identical SQL + params to the previous inline block for 180 input combinations; _live_record_payload() matches what loadTableData() reads from the Jinja-rendered <tbody> for 20 row variants; the real attendance_report.js driven with a fake DOM/timers/fetch passes 26 checks (insert, de-dup, page + sort kept, hidden-tab pause, no overlap, backoff + cap, stop on logout, escaping). Not yet exercised against a live MySQL server or a real browser

Set 21 — Check-In Employee ID Limited to 4 Digits (Sept 15, 2026)

File	Change
templates/qr_destination.html	maxlength="4"; EMPLOYEE_ID_MAX_DIGITS; input listener cuts to 4 digits; stored IDs over 4 digits not auto-filled; submit guard with bilingual message
static/js/qr_destination.js	QR_EMPLOYEE_ID_MAX_DIGITS; same cut in initializeForm(); loadLastStaffId() ignores longer IDs; guards in handleFormSubmit() and submitCheckin()
routes/qr_codes.py	CHECKIN_EMPLOYEE_ID_MAX_DIGITS; qr_checkin rejects a base ID over 4 digits (HTTP 400, bilingual); qr_last_work_type returns no suggestion for longer IDs
—	"Up to 4 digits" chosen over "exactly 4": 1–3 digit IDs still check in. Supersedes the earlier "no maxlength cap" note in §11

Set 22 — Employee ID Placeholder Stuck on "Checking location services..." (Sept 15, 2026)

File	Fix
static/js/qr_destination.js	disableFormImmediately() replaced the Employee ID placeholder with "Checking location services..." and nothing restored it after location services were confirmed. The override is removed — the submit button already shows the checking state
templates/qr_destination.html	Placeholder is now "Enter your employee ID / Ingrese su ID de empleado"

Set 23 — Review Step 1: Security, Limits, Indexes (Sept 15, 2026)

File	Fix
routes/users.py, templates/users.html, static/js/users.js	7 user-management actions POST-only (were GET+POST, so a forged link could promote/delete — CSRF only checks POST). Promote/demote links → CSRF forms with a data-confirm listener. users.js (not loaded by any template) switched to POST
utils/helpers.py, routes/time_attendance.py, employees.py, legacy_attendance.py, statistics.py, qr_codes.py	roles_required / restrict_blueprint_to_roles; role rules in §4. Previously any logged-in user (incl. project managers) could import/export time attendance, delete employees, create/deactivate QR codes
advanced_security_middleware.py, routes/auth.py, app.py, config.py	Login limiter: real IP via ProxyFix, counters per IP+username and per username (§10). The first X-Forwarded-For entry was trusted, so rotating it gave unlimited attempts
utils/excel_safety.py + routes/attendance_export.py, routes/time_attendance_export.py, legacy_attendance_service.py, routes/statistics.py	Formula injection: excel_hyperlink() escapes " in HYPERLINK arguments; neutralize_unexpected_formulas(wb) before every export wb.save stores any formula not matching the app's own shapes (Google Maps HYPERLINK, SUM, SUMIF) as text; csv_safe() in the statistics CSV. New formula types in exports must be added to _ALLOWED_FORMULA_PATTERNS, or they are written as text
routes/qr_codes.py	Check-in refuses an employee ID whose base is not 1–4 ASCII digits (was: only counted digits, so =HYPERLINK(...) passed into attendance_data)
location_logging.py	/api/location-debug-info admin-only; no longer echoes request headers (incl. the session Cookie)
app.py, config.py	Startup QR images use QR_BASE_URL or are skipped (no more localhost QR codes); MAX_FORM_MEMORY_SIZE / MAX_CONTENT_LENGTH; @app.errorhandler(413) (bilingual JSON for /qr/ + /api/, flash + same-host redirect otherwise)
static/js/qr_destination.js	updateSubmitButton() looked for #submitCheckin, but the button is #submitButton, so the post-clone submit path never showed "Processing" — now finds either, bilingual label, restores via renderSubmitButton()
routes/attendance.py	Report query selects NULL AS verification_photo (the page never used the base64 photos it loaded for up to 1,000 rows)
tools/migration_attendance_indexes.py	New migration — see §17
—	Verified offline (67 checks): formula guard incl. openpyxl save/reload, role gates per role and for JSON callers, limiter behind ProxyFix (spoofed XFF, shared IP, per-username cap, reset on login), debug endpoint, check-in ID rule, config limits, POST-only routes, migration against a fake cursor (create / covered / re-run / fallback / missing column). Not run against MySQL, Nginx or a browser
—	Known, not changed: templates/confirm_delete_qr.html links (GET) to the POST-only deactivate_qr_code, so that button returns 405

Set 24 — Access-Denied Path Raised 500 (logger_handler was None) (Sept 16, 2026)

File	Fix
extensions.py	logger_handler is now a proxy (see §14). utils/helpers.py imports it at module import time, which happens BEFORE init_logger(), so it was permanently None: the Set 23 role check logged a warning when denying access and raised AttributeError: 'NoneType' object has no attribute 'logger' → 500 for a project manager opening /time-attendance or editing a QR code. The same latent bug affected generate_qr_code() logging in utils/helpers.py and every call in utils/template_helpers.py / utils/geocoding.py
—	The step-1 tests had stubbed a working logger into extensions, which hid it. They now import the real module and run the role checks BEFORE init_logger(), exactly like a gunicorn worker

Set 25 — Overtime Counted SP/PW/PT/C Hours (Sept 16, 2026)

File	Fix
routes/time_attendance_export.py	Both exports added EVERY paired hour to the weekly total that overtime is calculated from, so 36 h Regular + 8 h SP produced 4 h OT. Added _pair_is_regular() and a weekly_regular_hours counter; the weekly-boundary and final weekly rows now take Regular/OT from it (§13). Column H is unchanged (all hours worked)
routes/time_attendance_export.py	Export by Building: a note row under the date range states that its Weekly Total / Regular / OT are per building and exclude SP/PW/PT/C — the user was unsure whether that sheet is used for pay, so it is marked rather than changed. Shifts every Sheet0 row down by one (the Filtered Report is built from row bookkeeping, so it follows)
—	Verified by running both real export functions over the same fixture week, before (HEAD) vs after: 36 h Reg + 8 h SP → OT 4 → 0; 40 h Reg + 4 h C → 4 → 0; Regular-IN/SP-OUT cross-type pair → 4 → 0; 44 h plain Regular unchanged at 4; two-building employee unchanged (20 h in the main export, 0 per building block); punch rows identical; SP/C summary rows unchanged
—	Payroll impact: weeks where an employee had both special-type hours and 40+ total hours will now show less overtime than the same export produced before

Set 26 — Work-Type IDs With a Separator (1759.PW) (Sept 16, 2026)

File	Fix
working_hours_calculator.py	parse_employee_id_for_work_type() accepted only a space (\s*), so 1759.PW — a spelling that really exists in imported data — parsed as base 1759.PW / regular. The exports then showed a separate REGULAR employee "1759.PW" while the report and SQL filters (which use [^0-9A-Z]*) treated it as PW hours for 1759. Separator class aligned with the other two parsers (§13)
time_attendance_import_service.py	_clean_employee_id() now stores work-type IDs canonically (1759.PW → 1759PW), keeps zero padding, and no longer truncates 1234.5 to 1234 (silent data loss)
—	Verified: 17 ID spellings through the parser (before vs after), agreement with the report's JS parser, 11 cleaner cases (incl. the two old bugs), and an end-to-end export where a 7 h 1759.PW shift now appears as PW hours for employee 1759 (39 h worked / 32 regular / 0 OT) instead of its own block. Overtime (24) and step-1 (71) suites still pass
—	No data migration: existing rows keep their stored spelling and are read correctly now. Exports for past weeks will move those hours from "Employee 1759.PW" Regular into 1759's PW row — and out of the overtime base (Set 25)

Set 27 — Import Dates, PM Data Scoping, Manual Entry (Sept 16, 2026)

File	Fix
time_attendance_import_service.py	_parse_date_field() replaces pd.to_datetime() at all four call sites: Excel serial numbers imported as 1970-01-01, and 03/04/2026 was read month-first with no rule written down (§12)
time_attendance_import_service.py	_rollback_partial_batch() — a mid-import failure left a partial batch (commits every 50 rows); the batch is now deleted and reported, so an import is all-or-nothing
utils/helpers.py	load_project_manager_scope() — one fail-closed source for a PM's projects/locations (§4)
routes/attendance.py	Scope applied to attendance_locations_api, time_attendance_locations_api, search_employees_api (location-only PMs resolved to their projects), get_project_locations_api
routes/dashboard.py	Scope applied to the dashboard QR + project lists, project_qr_codes (redirect for another project), dashboard_stats_api, dashboard_realtime_api. "Today" now uses local time
routes/attendance_edit.py	_normalize_manual_employee_id() — manual add did int(employee_id), so 1234SP could not be entered at all, and edit accepted any text (pseudo-employees in the exports). Both now accept 1–4 digits with an optional work type and store it canonically; timestamps local
app.py	CURRENT_YEAR from local time
—	Verified offline (42 checks): 16 date cases + 6 rejections, partial-batch rollback against a fake session, PM scope loading, scoped vs unscoped SQL for both locations APIs (values bound as parameters), empty result for an unassigned PM, 9 manual-ID cases, and no utcnow() left in the two route files. Suites still green: 71 (step 1), 24 (overtime), 38 (work-type IDs). Not run against MySQL or a browser

Set 28 — Dashboard Showed a PM Other Projects' QR Codes (Sept 18, 2026)

File	Fix
routes/dashboard.py	_project_manager_qr_filter() combined the assigned projects and locations with or_, so a PM assigned a location saw every project's QR codes carrying that location name. Now and_, matching the Attendance Report (§4). The same OR appeared in dashboard_stats_api and dashboard_realtime_api (counts and recent activity) — both switched too; or_ is no longer imported
—	Verified against the real models on in-memory SQLite, committed vs edited module: PM with project Alpha + location "Bldg A" went from A1, A2, B1 (B1 belongs to project Beta) to A1; project-only PM unchanged (A1, A2); location-only PM unchanged (A1, B1); PM with no assignments sees nothing; admin unfiltered. The Set 23/27 suites could not be re-run (scratch scripts from that session are gone), but the change is confined to routes/dashboard.py, which they do not cover
—	Deploy: routes/dashboard.py only, then restart. No migration

---

21. Infrastructure & Deployment

• Deploy user: qrcode on both servers
• Restart: sudo supervisorctl restart qrcode
• Template-only changes: no restart needed
• Python/model changes: always run migration first, then deploy files, then restart
• Gunicorn: gevent workers — avoid threading-unsafe patterns

Deploy Order for DB Column Additions

1. Run python3 tools/migration_<name>.py (pymysql, safe to re-run)
2. Deploy updated Python files
3. Restart service

---

22. Session Changelog

Sessions 1–8 — Foundational Work

See §20 Sets 1–8 for detailed bug fix history. Covers: initial critical fixes, security hardening (CSRF, SQL injection, session fixation, rate limiting, open redirect), export pipeline enhancements, GOV deployment, theme system, code quality sprint.

Session (May 2026) — Template Sync + Photo Verification Toggle

• Diffed LT and GOV codebases — confirmed only 3 template files differed (whitespace + minor logic)
• Synced base.html, base_authenticated.html, projects.html — GOV brought to parity with LT
• Added per-QR photo_verification_enabled toggle (DB column + model + routes + UI + migration)
• Fixed migration script: replaced urlparse with regex parser (handles special chars in DB passwords), replaced SQLAlchemy conn.execute(str) with pymysql direct (avoids ORM loading model before column exists)
• Fixed edit_qr_code.html toggle bug: JS was injected into {% block title %} (corrupted by earlier injection) — moved CSS to extra_head, JS to extra_scripts using addEventListener, removed inline onchange attribute
• Restored QR code name lock on edit page: field is readonly in template; route ignores submitted name (new_name = qr_code.name)
• Fixed /api/geocode 403: create_qr_code.html is standalone HTML (no {% extends %}), so window.qrConfig was never injected — added direct injection in <head>; also fixed ordering in base_authenticated.html for pages that do extend it

Session (September 2–3, 2026) — Type of Work on Check-In

• Added the Type of Work dropdown to the check-in page (Regular default, PW / SP / C) and made the Employee ID numeric-only; the code is appended to the ID server-side so no schema change or migration was needed (§11, §20 Set 12)
• Added C (Covering) as a first-class work type through the parser, the calculator's daily/weekly/grand totals, the ID filters, and the C summary row in both Excel exports
• Deliberately left single_checkin_calculator.py untouched (legacy; its dicts would KeyError on a C key) and did not cap the ID at 4 characters (would break 5-digit IDs)
• Made the dropdown labels bilingual as one plain string per option — <option> cannot hold the page's coloured english-text / spanish-text spans
• Fixed the Attendance Report employee filter (§20 Set 13): the July server-side fix was being undone by an exact-match client-side re-filter in attendance_report.js, so 1234SP / 1234PT rows were dropped after the query returned them
• Added two anti-mistake measures chosen from a set of options (§20 Set 14): the submit button and success card now name the selected type, and a Check Out scan pre-selects the work type of the employee's still-open check-in with a bilingual reminder
• Verification used throughout: ast.parse on every Python edit, Jinja parse of the template, node --check on the JS (including the extracted inline script), an end-to-end WorkingHoursCalculator run proving regular/OT totals unchanged, and offline rule checks for the filter matcher and the last-work-type decision paths
• Both LT and GOV instances need these changes; only LT was reachable in this session (the Gitea MCP server failed to connect)
• iPhone follow-up: the pre-selected work type reverted to Regular on iOS Safari only. Cause was Safari-specific form-state restore (fires after the async response, and fires a change event that was being read as an employee choice) plus aggressive GET caching. Fixed with re-application on a schedule, interaction-gated change handling, and no-store on both ends (§11, §20 Set 15)
• iPhone follow-up 2 — the actual root cause: the reminder was correct but the dropdown reverted, because qr_destination.js clones and replaces the whole form 1.5 s after load and cloneNode(true) drops live control state. Fixed with preserveFormControlState() + a checkinFormReplaced event that re-binds the page's listeners (§11, §20 Set 16). The Safari guards from Set 15 stay — they address a different, real failure mode
• Reverted the work-type line on the submit button at the user's request (layout); the plain Check In / Check Out label is back. The reminder and success-card row keep the confirmation (§20 Set 17)