# Vermietungs-Tool

A local web app for running a small room-rental business in Austria: the guest register, the room
ledger, invoices, the monthly tourist-tax report and the quarterly VAT list, all from one database.
It is built for one non-technical user — German UI, no training needed — and handles the bookkeeping
rules that make this work tedious by hand.

This repository presents the tool. It contains the application code only; it ships no data.

![Belegungsplan — the occupancy timeline](docs/screenshots/plan.png)

*All names, companies and addresses in the screenshots are fictitious.*

## What it does

| Page | Purpose |
| --- | --- |
| **Gästeblätter** | The guest register: one registration sheet per guest and stay (Meldegesetz), with room, employer, main residence and tourist-tax liability. A room change continues the same sheet in another room. |
| **Zimmerbelegung** | The room ledger: what each room is billed for, per period and person — weeks, nights or months, Ortstaxe, payer, invoice number and payments (also in parts). |
| **Belegungsplan** | A rooms × days timeline of who is in the house, today's free beds, and bookings ahead. |
| **Rechnungen** | Invoices generated from ledger or register rows: per-person lines, VAT breakdown, cancellation fees as non-taxable damages, Ortstaxe, A4 layout for window envelopes, PDF download. |
| **Firmen** | Companies with all their spellings and address versions; old invoices keep the address that was true at the time. |
| **TVB-Meldung** | The monthly tourist-tax report (Sammelmeldung) per business, ready to type into Feratel: persons and nights by country or Austrian/German region, liable and exempt. |
| **Umsatzsteuer** | The quarterly VAT list (cash basis): every receipt in the quarter it was paid, part payments split across quarters, non-taxable items kept out of the base. |
| **Datenprüfung** | Data-quality checks — register and ledger disagreeing, departures never billed, recipients without an address, duplicate invoice numbers — and merge suggestions for near-duplicate companies and guests. |

### Rules it applies so nobody has to

- **Billing in weeks**: a stay of four nights or more is billed in whole weeks, rounded up; shorter stays
  are billed by the night.
- **Ortstaxe**: charged per person and night at the rate in force on the arrival date, and waived for
  professional stays of more than ten nights — judged on the *whole* stay, across room changes and month-end
  invoices, and never for a guest who is still in the house.
- **The ledger follows the register**: a departure entered on a guest sheet closes that guest's ledger row
  with quantity, amount and Ortstaxe; a guest leaving before the others gets a row of their own, and rows that
  end up alike merge again. Month-end billing closes a row and continues the stay in a new one.
- **The ledger follows the invoice**: when an invoice settles a price or period differently, the ledger rows it
  was made from are corrected to match; when a ledger row changes after invoicing, the invoice is flagged and
  can be regenerated in one click.
- **Issued invoices never change as a side effect**: recording a payment touches only the payment fields, and
  payment information from the ledger is offered, not applied.
- **Invoice numbers follow the dates** within each business and year; saving an invoice out of order, or one
  whose recipient has no postal address, asks first.
- **Payments**: a date in "bezahlt am" marks a row paid, payments in parts keep it open until amount and
  Ortstaxe are covered, and a paid invoice pays its ledger rows.

## Screenshots

| | |
| --- | --- |
| ![Zimmerbelegung](docs/screenshots/ledger.png) | ![Gästeblätter](docs/screenshots/register.png) |
| **Zimmerbelegung** — the room ledger | **Gästeblätter** — the guest register |
| ![TVB-Meldung](docs/screenshots/tvb.png) | ![Datenprüfung](docs/screenshots/quality.png) |
| **TVB-Meldung** — the monthly tourist-tax report | **Datenprüfung** — data-quality checks |

![Rechnung](docs/screenshots/invoice.png)

## Under the hood

- **Flask + Jinja2**, server-rendered pages; all UI text comes from [locales/de.json](locales/de.json).
- **PostgreSQL** through psycopg, behind a thin adapter ([app/db.py](app/db.py)) that gives the code a
  sqlite3-style interface (`?` placeholders, `lastrowid`, rows by name or index). Two roles: the app's role may
  only read and write data; the owner applies migrations and takes snapshots.
- **Single source of truth**: every value lives in one place; totals, nights, quantities and payment states are
  computed at read time ([app/compute.py](app/compute.py), [app/repo.py](app/repo.py)). Money is summed at full
  precision and rounded once.
- **Audit log** ([migrations/002_audit_log.sql](migrations/002_audit_log.sql), [app/audit.py](app/audit.py)):
  a trigger records every row change with before/after values and who made it; the app's role can read the log
  but not alter it. `python -m app.audit revert` undoes chosen entries or a whole transaction, refusing if the
  rows changed since.
- **A grid engine in plain JavaScript** ([app/static/grids.js](app/static/grids.js)) for lists of thousands of
  rows: rows ship in an inert `<template>` and only the visible window is attached, column widths are measured
  once and cached per data version, multi-level sorting, live search and filters, pinned columns, live sums, and
  print-to-one-page.
- **Change detection by trigger**: every write bumps a `data_version` counter, which keys a server-side page
  cache and the browser's cached column layouts.
- **PDF invoices** rendered by a headless Chromium-family browser through Playwright
  ([app/pdf.py](app/pdf.py)), from the same HTML the browser prints, placed to the millimetre for DIN 5008
  window envelopes.

```text
app/
  web.py        routes            repo.py      service layer (all reads and writes)
  compute.py    money and stay rules            tvb.py, vat.py, plan.py, quality.py   reports and checks
  db.py         database access, migrations     audit.py     audit log and revert
  pdf.py        invoice PDFs                    templates/, static/
locales/        UI text (German)
migrations/     schema (numbered SQL)
```

## Running it

Requirements: Python 3.13 or newer, a PostgreSQL database, and Microsoft Edge or Google Chrome for PDFs.

```sh
python -m venv .venv
.venv/bin/pip install -r requirements.txt           # Windows: .venv\Scripts\pip
export DATABASE_URL=postgresql://user:password@host:5432/dbname
python -m app.db migrate                             # create the schema
python -m app.web                                    # http://127.0.0.1:5000
```

- Settings come from the environment or from a `.env` file in the repository root (`KEY=VALUE` lines):
  `DATABASE_URL`; optionally `DATABASE_OWNER_URL` for migrations when the app runs with a restricted role, and
  `PDF_BROWSER` (`msedge`, `chrome`, or `chromium` after `playwright install chromium`).
- The database starts empty. The businesses (`unit`: name, address, bank details, VAT and tourist-tax numbers,
  invoice number prefix, colour theme) and their rooms (`room`) are set up directly in the database; everything
  else is entered in the app. Logos and photos for the invoice header go into `app/static/invoice/`, named in
  `unit.logo` / `unit.photo`.

## License

All rights reserved. This is not open-source software: the code is published to be read, and to be run
unmodified for your own private evaluation — nothing else. See [LICENSE](LICENSE).