Internal Management & Automated Task Dispatch System

Category: Internal Tooling · Python · SecurityStack: Python · PyQt · PostgreSQL · Enterprise NAS · Discord API · SQL Security

---

Background

The team was using Google Sheets to track tasks. This created several fundamental problems:

• No access control: anyone could modify anyone else's data
• No status notifications: task assignments and completions required manual follow-up
• NAS files disconnected: tasks in Sheets had no link to the actual files on the NAS
• Fragile format: Sheets were easily corrupted by accidental edits

I independently built this centralized management platform from scratch to replace it.

---

Features

1. PyQt GUI

Since most users were non-technical, the frontend is a desktop application built with PyQt — an intuitive graphical interface requiring no command-line knowledge.

2. Role-Based Access Control (RBAC)

The system implements role-based permissions:

• Admin: full access — view all tasks, assign tasks, modify status, manage users
• Standard user: sees only tasks assigned to them, cannot modify others' data
• Read-only: view specific data only, for external collaborators

Every operation validates the current user's role before execution.

3. Forced Version Check and Auto-Update

To minimize ongoing maintenance overhead, I designed a forced update mechanism:

1. On startup, the app queries an internal server for the latest version number
2. If outdated, the update flow is mandatory — the new installer is downloaded automatically
3. The app restarts into the latest version

This ensures all users are on the same version, eliminating "it works on my machine" issues caused by version drift.

4. NAS File Integration and One-Click Upload

Users manage NAS files directly from the interface:

• Browse the NAS directory structure (filtered by permissions)
• One-click upload of local files to a specified NAS directory
• Tasks are automatically linked to their associated NAS files

5. SQL Injection Prevention

All database operations use parameterized queries without exception:

# Wrong — string concatenation, vulnerable to SQL Injection
cursor.execute(f"SELECT * FROM tasks WHERE user_id = '{user_input}'")

# Correct — parameterized query
cursor.execute("SELECT * FROM tasks WHERE user_id = %s", (user_input,))

No matter what a user inputs, it is never interpreted as SQL. Injection risk is eliminated at the data layer.

6. Discord API Real-Time Notifications

This became the team's most appreciated feature. A database state listener runs in the backend:

1. Any task status change (created, assigned, completed, overdue) is detected
2. The Python script fires immediately
3. Discord API sends a notification to the designated channel or user automatically

import requests

def notify_discord(webhook_url, message):
    payload = {"content": message}
    requests.post(webhook_url, json=payload)

# Triggered on task status update
notify_discord(WEBHOOK_URL, f"Task \"{task_name}\" completed by {assignee} ✅")

The team no longer tracks tasks manually — the Discord channel became a self-updating work board.

---

Results

• Replaced Google Sheets as the team's sole task management tool
• RBAC resolved data security concerns completely
• Discord notifications eliminated coordination lag between departments
• Auto-update mechanism keeps maintenance overhead near zero

---

Takeaway

Internal tool quality directly affects team efficiency. A poorly designed internal system wastes a few minutes per person every day — compounded across the entire team, that's enormous hidden cost. Investing in the right place pays back far more than it costs to build.