TimeTrackr Stack: PostgreSQL 17 + SSH tunnel + Odoo sync

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)

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

---

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?

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)

# 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:

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/:

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:

./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

# 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:

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 for the node breakdown, payload format, and validation rules.

Operational commands

# 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 — Big picture
• Security Stack — Caddy does not route TimeTrackr (dedicated SSH tunnel)
• Odoo 18 Setup — Target account.analytic.line model

Workflows

• TimeTrackr → Odoo — N8N sync workflows (Projects + Entries)

Reference

• Glossary — SCRAM-SHA-256, SSH tunnel, timesheet