Budget_Tool_Martinhal/README.md

# Budget & Capex

A self-hosted web app for building yearly department budgets and the "Capex e Custos" cost report.
Users enter cost lines in the browser; the app produces the monthly budget (like the *Budget IT*
sheet) and the cost report (like *Capex_e_Custos_2027*), online and as PDF and Excel.
The interface and the reports are available in Portuguese and English.

## What's inside

| Container | Role |
| --- | --- |
| `web` | Caddy: serves the web app and forwards `/api` to the API. Optional HTTPS. |
| `api` | Python (FastAPI): business rules, Excel import, PDF and Excel exports. |
| `db` | PostgreSQL 16. All data lives in the `db-data` Docker volume. |
| `backup` | Dumps the database every night to `./backups` and keeps 30 days. |

## Install

You need a server on your network with Docker Engine and the Docker Compose plugin.

```sh
# 1. copy this folder to the server, then inside it:
cp .env.example .env
# 2. edit .env: set POSTGRES_PASSWORD, SECRET_KEY (openssl rand -hex 32) and ADMIN_PASSWORD
# 3. build and start
docker compose up -d --build
```

Open `http://<server-address>:8080` and sign in with `ADMIN_EMAIL` / `ADMIN_PASSWORD` from `.env`.
Change the password under **My profile** right away. The admin account is created only on the first start;
changing `.env` later does not change it.

## First steps

1. **Departments**: the app starts with an **IT** department. Add the others (F&B, Rooms, …); each new
   department starts from the default chart of accounts, from no categories, or from a copy of another
   department's categories.
2. **Budgets → Import Excel**: choose the department and upload `Budget para 2027.xlsx` (a copy is in
   `backend/tests/fixtures/`) or a filled-in template (see below).
   The import reads the *Sheet1* and *Budget IT* sheets: 54 lines, the 5.5% rate, every multiplier
   (including the ×2.055 and ×3.055 hardware increases) and the remarks. Formulas such as `=5512*12`
   are kept as typed calculations.
3. **Users → New user** for each colleague, with their department and role:
   * **Viewer**: view and export every budget.
   * **Upper Management**: view, download and email the budgets and reports of every department, switching
     between departments; changes nothing. A department is optional.
   * **Editor**: edit their own department's draft budgets and submit them for review.
   * **Approver**: also approve their department's budgets and manage its categories.
   * **Administrator**: everything, including departments, workflows, users and sites.

   Everyone can view every department's budgets and reports; only the budget's own department can
   change it, and only the people named in its workflow can approve it.
   Each user can turn on **MFA** in *My profile* (authenticator app + recovery codes). If someone loses
   their phone, an administrator opens *Users → Edit* and presses **Reset MFA**. MFA secrets are encrypted
   with `SECRET_KEY`: keep that value when you upgrade, or everyone's MFA has to be reset.
4. **Reports**: pick the year, language and optionally one site, then *View online*, *PDF* or *Excel*.

## Entering data

* Each line has a **prior-year actual**, a **base** for the new year and an **adjustment**:
  *year rate* (the "Inflation + adjustments" % on the budget page), a custom *%*, a *factor ×*,
  or *none*. The adjusted value is rounded to cents per line.
* Amount fields accept calculations, as in Excel: `12*839,67+3292,56` or `=5512*12`.
  Both `1 234,56` and `1,234.56` are understood.
* Months are **split over 12** (December absorbs the rounding cents), **split over chosen months**
  (tick for example Jan to Apr: the adjusted amount is divided equally over those months and the rest
  stay at zero), or typed one by one (**monthly amounts**), as the revenue lines are.
* Lines can belong to one **site** (Sagres, Quinta, Chiado, Oriente) or to the **group**. Filter any page
  or report by site.
* **New year** can copy every line from an earlier year: last year's adjusted budget becomes the
  new year's actual and base, which you then replace with real figures.

## Approval workflow

Each department has its own workflow, set by an administrator under **Departments**: an ordered list of
steps, each approved either by *the department's approvers* or by *selected users* (who can be in any
department, e.g. finance). With no steps set, one approval by the department's approvers is needed.

`Draft → step 1 → step 2 → … → Approved`. Only drafts can be edited. An editor submits; whoever may
approve the current step approves (moving it to the next step) or returns it to draft; an approver of
the department or an administrator can reopen an approved budget. Every change (who, when, old and new
value) is in **History** on the budget page.

## Reopening an approved budget

Anyone can press **Request reopening** on an approved budget and must say why. The department's approvers
and the administrators see the request on the budget (and get an email when email is set up), and
**Accept and reopen** it (it goes back to draft) or **Decline** it, giving a reason. Approvers can also
reopen directly, again with a reason. All requests stay in the budget's **History**.

## Download, change and upload again

On a budget page, **Download → Budget file** gives the budget as an editable Excel file (the template
filled with its lines). Change it and upload it with **Upload Excel** on the same page (or Budgets →
Import Excel). Because that budget already exists, the app asks first. On OK, the current version is
kept but renamed with the date and time, for example `Budget 2027 (2026-10-28 23:00)` and
`Orcamento_IT_2027_2026-10-28_2300.xlsx`, and locked so it can't be edited; the uploaded file becomes
the current budget. Only drafts can be replaced; an approved budget must be reopened first. Archived
versions are under **Budgets → Show archived versions**.

## Excel template

**Budgets → Excel template** downloads a workbook for a department and year, in Portuguese or English,
blank or pre-filled with the lines of an existing budget. It has an instructions sheet and a *Lines*
sheet with drop-down lists for category, site and adjustment type. Fill it in and upload it with
**Import Excel**; unknown categories are created in the department and anything skipped is listed.

## Email

Administrators set up the sending account under **Email → Sending account**: the SMTP server, port and
security (STARTTLS on 587 or SSL/TLS on 465), the login, the sender address and name, and an optional
signature. With **Save a copy in the Sent folder** on, every message is also stored over IMAP in the
mailbox's Sent folder (found automatically, or named by hand), just like a message sent from Outlook.
Gmail already keeps that copy for SMTP, so turn the option off there to avoid duplicates. For Gmail and
Microsoft 365, use an app password or an account with SMTP sign-in allowed. **Test connection** signs in
without sending; **Send test email** sends a short message.

Then every user gets **Send by email** on the Reports page: choose recipients, subject, message and the
reports to attach (PDF and/or Excel, in the language and site chosen on the page).

Passwords are stored encrypted with `SECRET_KEY`. If you change `SECRET_KEY`, enter them again.

## Logs

- **Activity** (administrators): who did what and when: sign-ins and failed sign-ins, budgets and
  lines created, changed, imported, submitted, approved and deleted, reports viewed, downloaded and
  emailed, and admin changes. Filter by user, area, action, date or text, and export to CSV.
- **Email → Mail log** (administrators): every message sent: who sent it, to whom, the subject,
  message and attachments, whether it was delivered to the server, and whether the copy reached the
  Sent folder. Also exportable to CSV.

Only administrators can delete a budget (in any status), from the budget's page. It asks you to type the
year to confirm; the deletion stays in the activity log.

## Backups and restore

Backups are written to `BACKUP_DIR` (default `./backups`) at `BACKUP_TIME` every night and once at start.

```sh
docker compose exec backup ls /backups
docker compose exec backup restore.sh /backups/budget_20270101_023000.sql.gz
docker compose restart api
```

Copy the `backups` folder to another machine or a NAS regularly; a backup on the same disk does not
protect against losing that disk.

## Updating

Your data lives in Docker volumes, not in this folder, so replacing the folder does not touch it.

1. Replace the folder contents with the new version, keeping your `.env` and `backups` folder.
2. Run `docker compose up -d --build`.

Before the API applies database changes (migrations), it asks the backup container for a fresh dump
(`backups/budget_..._before_upgrade.sql.gz`) and waits for it; if the backup fails, the upgrade does not
run. So there is always a backup from just before an upgrade.
Upgrading from version 1 moves all existing budgets, categories and users into the **IT** department.
Never run `docker compose down -v`: `-v` deletes the data volumes.

## HTTPS (optional)

Plain HTTP on port 8080 is the default, which is fine on a trusted network. For HTTPS, set in `.env`:

```
SITE_ADDRESS=budget.yourdomain.local
HTTP_PORT=80
HTTPS_PORT=443
COOKIE_SECURE=true
```

and make that name resolve to the server. Caddy issues a certificate from its own local authority for
internal names. Browsers will warn until you install its root certificate on each PC:
`docker compose cp web:/data/caddy/pki/authorities/local/root.crt caddy-root.crt`.
With a public domain pointing at the server, Caddy gets a Let's Encrypt certificate instead.

## Development

```sh
cd backend && pip install -r requirements.txt pytest httpx && python -m pytest tests   # 44 tests
uvicorn app.main:app --reload                                                          # API on :8000 (SQLite)
cd frontend && npm install && npm run dev                                              # web on :5173
```

API documentation is at `/api/docs` when the app is running.