AlertSignal

AlertSignal tracks the business licenses, permits and regulatory documents of a group of companies and emails the people in charge before anything expires. It was built for a group of companies that kept these dates in a spreadsheet by hand. It is meant to run on one machine inside the company, with no cloud, and keeps everything in a single SQLite file. The interface is in Brazilian Portuguese.

This README is also available in Portuguese.

In 30 seconds

1. Run the demo, with fictitious data and no email ever sent:

   pip install -r requirements.txt
   python app.py --demo

   On Windows, just double-click DEMONSTRACAO.bat: it opens the browser by itself, on port 5001.
2. Open http://localhost:5000 and click Entrar como administrador (sign in as admin).
3. On the overview, open a document under Atenção urgente, renew it, and watch the row move and the history record who did it.
4. In Relatório, pick "Vencidos e a renovar" and use Imprimir ou salvar PDF.
5. Sign out and sign in as visualizador (viewer): everything is still visible, but read-only.

Screens

Captured from demo mode. The blue bar at the top of each screen says the data is fictitious.

Overview, dark theme	Company page, light theme
Report, light theme	Companies, dark theme
Sign in, with the demo buttons	The report as a PDF

On a phone, light theme	On a phone, dark theme

---

Features

• Automated email alerts. A check runs every day at a configurable time. Each document gets one notice when it reaches 90, 30 and 7 days before expiration (all three thresholds are configurable) and one reminder a day once it has expired. Each notice goes out once to each person in charge: running the check twice on the same day repeats nothing; a document added inside a range, or a threshold passed on a day the check did not run, gets the most recent notice on the next run; and a renewed document starts over with its new date. A notice whose email fails is tried again the next day. The email groups documents by urgency and includes a plain-text version.
• Many companies and categories. Each company has its own documents; categories group them by line of business.
• More than one assignee per document, so no alert depends on a single person.
• Renew, edit and update protocol numbers on the company page, with the history recording who did what.
• Expiration report filtered by status, category and company, printed in landscape or saved as PDF.
• Export to a formatted Excel file (status colors, filter, totals, print setup) and to semicolon CSV, which Excel in Portuguese opens straight into columns.
• Chart of expirations over the next 12 months, with hover details and a table view.
• Two access levels. Admins can change data; viewers can only look, enforced on the server too.
• Dark and light themes, following the system on the first visit, and a phone layout.
• Works without internet. Fonts and icons are served by the app itself, so nothing is loaded from third parties.
• Demo mode with fictitious data in a separate database.

---

Getting started

Requirements

• Python 3.11 or later (the tests run on 3.11 and 3.13)

Demo (fictitious data)

pip install -r requirements.txt
python app.py --demo

Open http://localhost:5000 and use Entrar como administrador (admin) or Entrar como visualizador (viewer). The demo writes to a separate demo.db, rebuilt on every start, and never sends email. On Windows, DEMONSTRACAO.bat does the same on port 5001, so it can run next to a real install on 5000, and opens the browser by itself.

Real use

1. Copy .env.example to .env and set a long random SECRET_KEY. Optionally, set ADMIN_EMAIL and ADMIN_SENHA for the first administrator.
2. Run python app.py (or double-click INICIAR.bat on Windows).
3. On the first run, the console shows the administrator's email and, unless ADMIN_SENHA is set, a random password that is shown only once. Sign in and change it under Meu perfil.

Data lives in zen.db, which is not committed. If a planilha.xlsx spreadsheet is in the folder, it is imported on the first run.

The server listens on every network interface, so other computers on the company network can open it. Set HOST=127.0.0.1 in .env to keep it on this machine only.

Upgrading an install that runs from an old clone: earlier versions kept zen.db in the repository. Back up zen.db before git pull, and if git refuses to update because of it, restore the backup after the pull. Never run git reset --hard or git checkout -- zen.db on that machine without the backup.

Tests

python -m unittest discover -s tests -v

The same tests run on GitHub Actions on every push. They use demo mode on a temporary database and cover the pages, viewer permissions, CSRF protection, the sign-in lockout, security headers, sessions revoked when a user is deleted, deactivated or has the password reset, exports (including spreadsheet formula injection), the alert email, the alert rules on a fixed date (each threshold, no repeats, daily reminders after expiration, configurable thresholds) and the scheduler.

---

Email setup

AlertSignal sends through Gmail with an App Password.

1. At myaccount.google.com, turn on 2-Step Verification.
2. Search for App passwords and create one named "AlertSignal".
3. In AlertSignal, open Configurações, fill in the address and the App Password, and save.
4. Use Enviar teste to check.
5. Optional: set Endereço do sistema (e.g. http://192.168.0.10:5000) so the email gets an "Abrir no AlertSignal" button.

---

Tech stack

Layer	Technology
Server	Python 3 + Flask
Database	SQLite
Scheduler	APScheduler
Email	smtplib + Gmail (SSL)
Interface	Jinja2, CSS and plain JavaScript
Fonts and icons	Plus Jakarta Sans, JetBrains Mono and Tabler Icons, served by the app
Spreadsheets	openpyxl (export) and pandas (import)

---

Technical decisions

SQLite over PostgreSQL. A local app on one machine with few writes. SQLite needs no setup, and a backup is a file copy.

APScheduler over cron. It runs inside the Flask process, works on Windows and lets the send time be changed from the settings page.

Plain SQL, no ORM. Short, explicit, parameterized queries. With nine tables, an ORM would only add layers.

No front-end framework. Modals use <dialog>; inline editing, filters and notices fit in a few functions. The chart is built with HTML and CSS and stays readable on a phone.

CSRF protection without a dependency. A per-session token goes into every form and every data-changing fetch.

Security without extra packages. Every request reads the signed-in user from the database again, so a user who is deleted, deactivated or has the password reset is signed out on the next request. Five wrong passwords for the same email lock sign-in for 15 minutes, every response carries security headers (CSP, X-Frame-Options, nosniff, Referrer-Policy), and exported text that starts with =, +, - or @ stays text in Excel instead of becoming a formula.

---

Project structure

alertsignal/
├── app.py                  # Routes, permissions, report and exports
├── database.py             # SQLite schema and connection
├── notificacoes.py         # Alert rules and email sending
├── demo_seed.py            # Demo-mode fictitious data
├── importar_planilha.py    # One-time import of the old spreadsheet
├── tests/                  # Tests (unittest): routes and security, alert rules and scheduler
├── .github/workflows/      # Tests on GitHub Actions on every push
├── docs/telas/             # Images for this README and the link preview
├── INICIAR.bat             # Starts the app on Windows
├── DEMONSTRACAO.bat        # Starts the demo on Windows
├── .env.example            # Settings for real use (copy to .env)
├── static/
│   ├── css/app.css         # Styles, themes, phone layout and print
│   ├── js/app.js           # Modals, confirmations, tabs, theme and menu
│   ├── img/                # Link preview image
│   └── vendor/             # Fonts and icons served locally, with their licenses
└── templates/              # Jinja pages (layout, screens, report and errors)

---

Database

usuarios              app users (sign in and access level)
categorias            lines of business
empresas              companies, with CNPJ, category and active flag
documentos            documents per company (type, protocol, expiration, status)
responsaveis          people who receive alerts
documento_responsavel who is in charge of each document
historico             alerts sent and changes, with date and author
alerta_enviado        notices already sent (document, person, threshold, expiration date, day)
configuracoes         email, send time and alert thresholds

---

Known limitations

• The Gmail App Password is stored as plain text in the database. For use outside the company network, it should be encrypted with cryptography.fernet.
• Flask's built-in server is fine on a local network; for internet access, put the app behind a server such as Waitress or Gunicorn, with HTTPS, and set ALERTSIGNAL_HTTPS=1 so the session cookie only travels over HTTPS.
• The sign-in lockout counter lives in memory: restarting the server clears it.

---

License

MIT.

---

Author

Built by Alyssom Fernandes. A first Python project, built for a group of companies that tracked these documents in a spreadsheet, and to show the whole stack: business rules, database design, scheduled jobs, email and interface.