---
title: 'TimeTrackr Stack: PostgreSQL 17 + SSH tunnel + Odoo sync'
url: https://blog.guigpap.com/en/infrastructure/timetrackr-stack/
url_md: https://blog.guigpap.com/en/infrastructure/timetrackr-stack.md
category: infrastructure
date: '2026-05-04'
maturite: production
techno:
  - postgresql
  - n8n
  - odoo
application:
  - infrastructure
  - automation
---

# TimeTrackr Stack: PostgreSQL 17 + SSH tunnel + Odoo sync

> Local time-tracking stack for Windows, hardened PostgreSQL database, SSH-tunnelled access, sync to Odoo timesheets

## 1. What? — Definition and context

The **TimeTrackr Stack** is the data layer of the time-tracking system that feeds Odoo timesheets. It stands out from the other stacks by exposing **no port to the Internet**: only the desktop client, after opening a dedicated SSH tunnel, can reach the database.

| Component | Role | Location |
|-----------|------|----------|
| **TimeTrackr.exe** | Windows desktop client (timer + entry input) | User workstation |
| **SSH tunnel** | Encrypted transport to the VPS, restricted to the PG port | User connection ↔ VPS |
| **PostgreSQL 17** | Local storage of time entries | VPS (`timetrackr-stack`) |
| **N8N webhooks** | Sync to Odoo (projects read, entries write) | VPS (`n8n-stack`) |
| **Odoo** | Source of projects and target of timesheets | VPS (`odoo-stack`) |

> **Note - Why a separate system**
>
> Odoo ships a native timesheet module, but the web UI is awkward for starting/stopping a timer during the day. TimeTrackr.exe lives in the Windows tray, captures time locally, and syncs in batches. Postgres acts here as a reliable local buffer, not a second source of truth.

### Architecture diagram

```mermaid
flowchart TD
  Client["TimeTrackr.exe · Windows · tray"]
  SSH["SSH tunnel · timetrackr-tunnel@VPS:22"]

  subgraph VPS["Hostinger VPS"]
    direction TB

    subgraph Auth["sshd · Match User timetrackr-tunnel"]
      Force["ForceCommand /bin/false"]
      Permit["PermitOpen localhost:5433"]
      NoTTY["PermitTTY no"]
    end

    subgraph Stack["timetrackr-stack · timetrackr-internal"]
      PG["timetrackr-postgres · PG 17 · 127.0.0.1:5433"]
      SSL["SSL/TLS · SCRAM-SHA-256"]
    end

    subgraph N8N["n8n-stack"]
      Projects["Webhook · /webhook/timetrackr-projects"]
      Entries["Webhook · /webhook/timetrackr-entries"]
      Mapping["Data Table · timetrackr_user_mapping"]
    end

    subgraph Odoo["odoo-stack"]
      Project["project.project / project.task"]
      Timesheet["account.analytic.line"]
    end
  end

  Client -->|"port 22 · SSH key"| Auth --> Stack
  Stack --> SSL
  Client -->|"HTTPS · X-TimeTrackr-Token"| Projects
  Client -->|"HTTPS · X-TimeTrackr-Token"| Entries
  Projects --> Project
  Entries --> Mapping
  Entries --> Timesheet
```

---

## 2. Why? — Stakes and motivations

### Why not expose PostgreSQL directly?

| Approach | Pros | Cons |
|----------|------|------|
| **Public port 5432** | Simple, allowed IPs in firewall | Permanent attack surface, brute force, scans, leak risk on misconfigured firewall |
| **Dedicated SSH tunnel** | No DB port on the Internet, SSH key auth, granular sshd restrictions | Heavier initial setup (key to deploy on the client) |
| **VPN (WireGuard)** | Convenient for many services | Overkill for a single DB port, IP/conf maintenance |

The SSH tunnel wins for this case: a single user, a single port, and the `timetrackr-tunnel` user **cannot do anything else** than forward to `localhost:5433` (no shell, no execution).

### Why a local buffer rather than write straight into Odoo?

> **Tip - Offline mode**
>
> The client must keep working when the network drops (travel, flaky Wi-Fi). Local Postgres lets entries be buffered and re-synced automatically when connectivity comes back. Writing directly to Odoo via XML-RPC would block the input on any incident.

### Why sync via N8N rather than a direct Odoo connection?

| Choice | Benefit |
|--------|---------|
| **N8N webhooks** | Mapping logic (TimeTrackr username → Odoo employee) lives outside the client, editable without redeploying the .exe |
| **Header Auth** | Rotatable token, independent of Odoo credentials |
| **No XML-RPC in the client** | The client never sees Odoo credentials |
| **Centralised audit** | All timesheet creations are visible in N8N executions |

---

## 3. How? — Technical implementation

### The TimeTrackr.exe client

A Windows desktop app that lives in the system tray. Main features:

- **Project/task timer**: start / stop a timer attached to an Odoo (project, task) pair.
- **Dynamic project list**: fetched on startup via `/webhook/timetrackr-projects`.
- **Batch sync**: pushes accumulated entries via `/webhook/timetrackr-entries`.
- **Offline mode**: keeps recording locally (PostgreSQL), retries the sync when the network returns.

### Client configuration (`.env`)

```env
# DB connection (via local SSH tunnel)
DB_HOST=127.0.0.1
DB_PORT=15433              # locally forwarded port (configurable)
DB_NAME=timetrackr_db
DB_USER=timetrackr_user
DB_PASSWORD=<from VPS .env>
DB_SSLMODE=require

# N8N webhooks (public HTTPS)
PROJECTS_URL=https://n8n.guigpap.com/webhook/timetrackr-projects
WEBHOOK_URL=https://n8n.guigpap.com/webhook/timetrackr-entries
WEBHOOK_TOKEN=<same token as the Header Auth credential in N8N>
```

### Opening the SSH tunnel

The client (or an associated Windows service) opens the tunnel before reaching the database:

```bash
ssh -N -L 15433:localhost:5433 timetrackr-tunnel@85.31.237.23 -i ~/.ssh/timetrackr_key
```

- `-N`: no command, just the forward.
- `-L 15433:localhost:5433`: local port 15433 forwards to `localhost:5433` on the VPS.
- The `timetrackr-tunnel` user is restricted to that single forward (see sshd block below).

### sshd restrictions on the VPS

The `sshd_timetrackr-tunnel.conf` file is deployed in `/etc/ssh/sshd_config.d/`:

```sshd
Match User timetrackr-tunnel
    AllowTcpForwarding yes
    PermitOpen localhost:5433
    ForceCommand /bin/false
    PermitTTY no
    X11Forwarding no
    AllowAgentForwarding no
    PermitTunnel no
```

| Directive | Effect |
|-----------|--------|
| `ForceCommand /bin/false` | The user **cannot** obtain a shell |
| `PermitOpen localhost:5433` | No other port forward allowed |
| `PermitTTY no` | No interactive terminal |
| `X11Forwarding no` / `AllowAgentForwarding no` / `PermitTunnel no` | All other forward types disabled |

A client key is deployed via the helper script:

```bash
./timetrackr-stack/setup.sh tunnel-user           # creation + sshd reload (one-time)
./timetrackr-stack/deploy_tunnel_key.sh client.pub  # add a client key
```

### PostgreSQL configuration

```yaml
# timetrackr-stack/docker-compose.yaml (excerpt)
postgres:
  image: postgres:17-alpine
  container_name: timetrackr-postgres
  environment:
    POSTGRES_INITDB_ARGS: "--auth-host=scram-sha-256 --auth-local=scram-sha-256"
    TIMETRACKR_APP_USER: ${TIMETRACKR_APP_USER:-timetrackr_user}
    TIMETRACKR_APP_PASSWORD: ${TIMETRACKR_APP_PASSWORD:?...}
  volumes:
    - timetrackr-db:/var/lib/postgresql/data
    - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro
    - ./pg_hba.conf:/etc/postgresql/pg_hba.conf:ro
    - ./init-ssl.sh:/usr/local/bin/init-ssl.sh:ro
    - ./init-app-user.sh:/docker-entrypoint-initdb.d/10-init-app-user.sh:ro
    - ./ssl:/etc/postgresql/ssl:ro
  entrypoint: ["/bin/bash", "/usr/local/bin/init-ssl.sh"]
  ports:
    - "127.0.0.1:5433:5432"   # NEVER 0.0.0.0
  deploy:
    resources:
      limits:
        memory: 512M
        reservations: { memory: 128M }
```

| Layer | Protection |
|-------|------------|
| **Network** | `127.0.0.1:5433` only (no Internet exposure) |
| **Transport** | Mandatory SSL/TLS (`hostnossl reject` in `pg_hba.conf`), AEAD + ECDHE only |
| **Authentication** | SCRAM-SHA-256 (no MD5, no plaintext) |
| **Authorisation** | App user limited to SELECT/INSERT/UPDATE/DELETE on the public schema |
| **Quotas** | App user capped at 10 concurrent connections |
| **Timeouts** | 60 s per statement, 300 s in idle-in-transaction, TCP keepalives |
| **Container** | `no-new-privileges:true`, capped memory |

### init-ssl.sh — SSL modes

The `init-ssl.sh` entrypoint dynamically picks how certificates are mounted:

| Mode | Trigger | Use case |
|------|---------|----------|
| **Pre-mounted** | `./ssl/server.crt` + `server.key` + `ca.crt` present | `sslmode=verify-full` on the client (production) |
| **Persistent volume** | A valid certificate already exists in the volume | Restart recovery |
| **Self-signed** | No cert available | On-the-fly generation (10-year validity), `sslmode=require` |

### init-app-user.sh — least privilege

On first init, the app user is created with exactly what's needed:

```sql
CREATE ROLE timetrackr_user WITH LOGIN PASSWORD '...';
ALTER ROLE timetrackr_user CONNECTION LIMIT 10;
GRANT CONNECT ON DATABASE timetrackr_db TO timetrackr_user;
GRANT USAGE, CREATE ON SCHEMA public TO timetrackr_user;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO timetrackr_user;
```

The `postgres` admin user stays restricted to `127.0.0.1` via `pg_hba.conf` — accessible only from inside the container.

### Sync to Odoo: the two N8N webhooks

Two active workflows handle the round-trip on the N8N side:

| Workflow | Endpoint | Method | Role |
|----------|----------|--------|------|
| **TimeTrackr - Projects** | `/webhook/timetrackr-projects` | GET | List of Odoo projects/tasks for the client menus |
| **TimeTrackr - Receive Entries** | `/webhook/timetrackr-entries` | POST | Creates `account.analytic.line` records in Odoo |

Both webhooks are protected by **Header Auth** (`X-TimeTrackr-Token`). They are not in the Caddy block list (unlike internal webhooks) — accessible from the client workstation over public HTTPS.

The `TimeTrackr username → Odoo employee` mapping is stored in the N8N Data Table `timetrackr_user_mapping`. No Odoo secret crosses the client, no DB connection from N8N → TimeTrackr.

See [TimeTrackr → Odoo](/en/workflows/timetrackr/) for the node breakdown, payload format, and validation rules.

### Operational commands

```bash
# Stack management
docker compose -f timetrackr-stack/docker-compose.yaml up -d
docker compose -f timetrackr-stack/docker-compose.yaml logs -f

# Verify SSL is on
docker exec timetrackr-postgres psql -U postgres -c "SHOW ssl;"

# Verify the port is bound only locally
ss -tlnp | grep 5433     # must show 127.0.0.1:5433, never 0.0.0.0

# Test the app user locally
docker exec timetrackr-postgres psql -U timetrackr_user -d timetrackr_db -c "SELECT 1;"

# Database size
docker exec timetrackr-postgres psql -U postgres -d timetrackr_db \
  -c "SELECT pg_size_pretty(pg_database_size('timetrackr_db'));"

# Verify a tunnel client gets no shell
ssh timetrackr-tunnel@localhost     # must close immediately
```

---

## 4. What if? — Outlook and limits

### Current limits

| Limit | Impact | Mitigation |
|-------|--------|------------|
| **Effectively single-user** | Only one entry in `timetrackr_user_mapping` | Fine for my current use; add employees via the Data Table |
| **Windows-only client** | No macOS / Linux / mobile | To consider if needed (the tunnel + webhook protocol stays portable) |
| **Self-signed by default** | Warning on first client startup | Mount Let's Encrypt certs in `./ssl/` for `verify-full` |
| **No automatic re-sync on the N8N side** | If a POST fails on the Odoo side, the entry stays on the client | The client retries; a reconciliation workflow remains to be written |

### Evolution scenarios

**If I want to onboard a second user**:
- Deploy a second key via `deploy_tunnel_key.sh`.
- Add a row to `timetrackr_user_mapping` (username, Odoo employee_id).
- The rest of the config (DB user, port forward) is shared.

**If I want to replace the SSH tunnel**:
- WireGuard at `wg.guigpap.com` would expose a private subnet including Postgres. More convenient for multi-services (Postgres admin in addition to the single client), but higher operational cost (IP, MTU, peer config).

**If I want to change the timesheet mapping**:
- Edit the N8N workflow `TimeTrackr - Receive Entries` directly (e.g. add an analytic category, filter some tasks).
- No client deployment required — that's the whole point of webhook-based sync.

### Metrics to watch

| Metric | Source | Attention threshold |
|--------|--------|---------------------|
| Database size | `pg_database_size('timetrackr_db')` | Abnormal growth = revisit local retention on the client |
| Active connections | `SELECT count(*) FROM pg_stat_activity` | Close to 10 = app user limit reached |
| N8N sync failures | Executions of the `TimeTrackr - Receive Entries` workflow | Spike = check the token, Odoo state, mapping |
| `timetrackr-tunnel` SSH attempts | `auth.log` | Brute force = revisit firewall-allowed IPs |

---

## Related pages

### Infrastructure
- [VPS Architecture](/en/infrastructure/architecture-vps/) — Big picture
- [Security Stack](/en/infrastructure/security-stack/) — Caddy does not route TimeTrackr (dedicated SSH tunnel)
- [Odoo 18 Setup](/en/infrastructure/odoo-18-setup/) — Target `account.analytic.line` model

### Workflows
- [TimeTrackr → Odoo](/en/workflows/timetrackr/) — N8N sync workflows (Projects + Entries)

### Reference
- [Glossary](/en/reference/glossary/) — SCRAM-SHA-256, SSH tunnel, timesheet

## Metadonnees agent

- Cet article est issu du blog GuiGPaP Lab.
- Contexte global du blog: https://blog.guigpap.com/llms.txt
- Contact auteur: https://odoo.guigpap.com/mon-cv
- Licence: CC-BY-SA 4.0