kooltab/docs/remote_tab_control_api.md

Remote Tab Control — Backend Specification

This document describes the backend API and database requirements for the KoolTap remote tab control feature. The bar app (Flutter) is the sync client; the backend serves both the bar app API and a web dashboard.

---

Database Schema

venues

Column	Type	Notes
id	uuid	Primary key
name	text	Venue name
created_at	timestamptz
updated_at	timestamptz

devices

Column	Type	Notes
id	uuid	Primary key, assigned by server on registration
venue_id	uuid	FK → venues.id
name	text	Human-readable label set during registration
token_hash	text	BCrypt or SHA-256 hash of the device bearer token
token_prefix	text	First 8 chars of the raw token for support identification
token_expires_at	timestamptz	Token expiry
last_seen_at	timestamptz	Updated on every sync
revoked_at	timestamptz?	Null while active
created_at	timestamptz

Index: (venue_id, revoked_at).

client_users

Column	Type	Notes
id	uuid	Primary key
venue_id	uuid	FK → venues.id
email	text	Login identifier
password_hash	text
name	text	Display name, logged as requested_by on commands
role	text	admin or viewer
created_at	timestamptz

Unique: (email).
A client user is scoped to one venue. A venue may have many client users.

open_tab_snapshots

Stores the last-known open-tab state reported by each device.

Column	Type	Notes
id	uuid	Primary key
device_id	uuid	FK → devices.id
tab_id	text	The tab's ID in the bar app's local database
customer_name	text
version	int	Tab version at time of snapshot
total_in_cents	int
item_count	int
reported_at	timestamptz	When the device reported this state

Index: (device_id).
Index: (tab_id, reported_at).
The server keeps only the most recent snapshot per (device_id, tab_id). On each sync upload, upsert by (device_id, tab_id).

tab_ownership

Resolves which device currently owns a tab.

Column	Type	Notes
tab_id	text	The bar app's tab ID
venue_id	uuid	FK → venues.id
device_id	uuid	FK → devices.id
last_reported_at	timestamptz

Primary key: (venue_id, tab_id).
Updated on every snapshot upload. If the same tab_id is reported by a different device_id, the most recent reported_at wins — the older one is flagged in the audit log.

remote_commands

Column	Type	Notes
id	uuid	Primary key
venue_id	uuid	FK → venues.id
device_id	uuid	FK → devices.id, resolved from tab_ownership
target_tab_id	text
type	text	Currently only close_tab
expected_version	int	Sent by the dashboard, validated by the bar app
payment_method	text	cash, payconiq, or payment_pending
requested_by	text	Client user's display name
status	text	pending, completed, failed, conflict, expired
result	text?	Free-text result from the bar app
acknowledged_at	timestamptz?	When the bar app acked
created_at	timestamptz
expires_at	timestamptz	created_at + 24 hours

Index: (device_id, status, created_at).
Index: (venue_id, status, created_at) for the dashboard.

audit_log

Column	Type	Notes
id	uuid	Primary key
venue_id	uuid	FK → venues.id
actor_type	text	client_user or device or system
actor_id	text	The ID of who did it
action	text	command_created, command_acknowledged, command_expired, device_registered, device_revoked, snapshot_conflict
details	jsonb?	Arbitrary context
created_at	timestamptz

Index: (venue_id, created_at).

---

API Endpoints

Base URL: https://api.kooltap.com/api/v1

All endpoints require HTTPS. The bar app endpoints require a valid device bearer token. The dashboard endpoints require a valid client session token.

---

1. Device Registration

POST /devices/register

Registers a new bar device and returns a bearer token. The device calls this once during setup (initiated from the Admin screen with an Admin PIN + a registration code from the dashboard).

Request:

{
  "venueId": "uuid",
  "name": "Bar terminal 1",
  "registrationCode": "ABC123"
}

Response (201):

{
  "deviceId": "uuid",
  "token": "dev_abc123...",
  "tokenExpiresAt": "2026-01-01T00:00:00Z"
}

Response (401): Invalid registration code or venue mismatch.

The raw token is returned only once. The device stores it in secure storage. The server stores only the hash.

---

2. Sync (Bar App → Server)

POST /devices/:deviceId/sync

Two-direction sync: the bar app uploads its current open-tab snapshot and receives any pending commands.

Headers: Authorization: Bearer <device_token>

Request:

{
  "deviceId": "uuid",
  "cursor": "optional-cursor-from-last-response",
  "openTabs": [
    {
      "id": "local-tab-uuid",
      "customerName": "Jan",
      "version": 5,
      "totalInCents": 2450,
      "itemCount": 3
    }
  ]
}

• cursor is opaque to the device. The server uses it to determine which commands the device has already seen. If absent, the server returns all pending commands.
• openTabs is the full list of currently open tabs. The server upserts open_tab_snapshots and tab_ownership.

Response (200):

{
  "cursor": "new-cursor-value",
  "commands": [
    {
      "id": "cmd-uuid",
      "type": "close_tab",
      "tabId": "local-tab-uuid",
      "expectedVersion": 5,
      "paymentMethod": "cash",
      "requestedBy": "Eigenaar",
      "createdAt": "2026-09-16T21:00:00Z"
    }
  ]
}

If no commands are pending, commands is an empty array.

Server-side logic:

1. Validate the device token and look up venue_id.
2. Upsert each reported open tab into open_tab_snapshots by (device_id, tab_id).
3. Upsert tab_ownership by (venue_id, tab_id). If the tab was previously owned by a different device, log a snapshot_conflict audit event.
4. Update devices.last_seen_at.
5. Query remote_commands where device_id matches, status = 'pending', and created_at > cursor_cutoff. Exclude commands that appear before the cursor.
6. Mark any commands past expires_at as expired.
7. Return the new cursor (e.g. ISO 8601 timestamp of now()) and the pending command list.

Rate limiting: One request per 30 seconds per device. Return 429 with Retry-After.

---

3. Command Acknowledgement

POST /devices/:deviceId/commands/:commandId/ack

The bar app calls this after it has processed a command (success, failure, or conflict).

Headers: Authorization: Bearer <device_token>

Request:

{
  "commandId": "cmd-uuid",
  "status": "completed",
  "result": "Tab closed successfully"
}

status is one of: completed, failed, conflict, expired.

Response (200):

{
  "ok": true
}

Server-side logic:

1. Update remote_commands.status and remote_commands.result and remote_commands.acknowledged_at.
2. Write an audit log entry.

The ack endpoint is idempotent. A second ack with the same commandId is accepted but does not change anything.

---

4. Create Command (Dashboard)

POST /venues/:venueId/commands

The dashboard calls this when a client user requests a remote tab close.

Headers: Authorization: Bearer <client_session_token>

Request:

{
  "tabId": "local-tab-uuid",
  "type": "close_tab",
  "expectedVersion": 5,
  "paymentMethod": "cash"
}

The paymentMethod must be one of cash, payconiq, or payment_pending. The dashboard must require the client to explicitly select one — never default to cash silently.

Response (201):

{
  "id": "cmd-uuid",
  "status": "pending"
}

Server-side logic:

1. Validate the client session and look up venue_id.
2. Resolve the target device_id from tab_ownership using (venue_id, tabId).
3. If no device owns this tab, return 404 with a message that the tab is not associated with any device.
4. If the owning device has not been seen (last_seen_at) in the last 10 minutes, return 200 but include a warning: "warning": "Device has not been seen recently". The dashboard should display this warning.
5. Insert into remote_commands with status = 'pending', expires_at = now() + 24 hours, and device_id from step 2.
6. Write an audit log entry.

Rate limiting: 10 requests per minute per client user. Return 429 with Retry-After.

---

5. Dashboard: List Open Tabs

GET /venues/:venueId/tabs

Returns the most recent snapshot of every open tab across all devices in the venue.

Headers: Authorization: Bearer <client_session_token>

Response (200):

{
  "tabs": [
    {
      "tabId": "local-tab-uuid",
      "customerName": "Jan",
      "version": 5,
      "totalInCents": 2450,
      "itemCount": 3,
      "deviceId": "device-uuid",
      "deviceName": "Bar terminal 1",
      "deviceLastSeenAt": "2026-09-17T20:30:00Z",
      "lastReportedAt": "2026-09-17T20:30:00Z"
    }
  ]
}

The dashboard should show a warning next to tabs whose deviceLastSeenAt is more than 5 minutes ago.

---

6. Dashboard: List Devices

GET /venues/:venueId/devices

Headers: Authorization: Bearer <client_session_token>

Response (200):

{
  "devices": [
    {
      "id": "uuid",
      "name": "Bar terminal 1",
      "lastSeenAt": "2026-09-17T20:30:00Z",
      "revokedAt": null
    }
  ]
}

---

7. Dashboard: Revoke Device

POST /venues/:venueId/devices/:deviceId/revoke

Headers: Authorization: Bearer <client_session_token>
Role required: admin

Request: (empty body)

Response (200):

{
  "ok": true
}

Server-side logic:

1. Set devices.revoked_at to now().
2. The device's token is immediately invalid. The next sync from this device returns 401.
3. Any pending commands for this device are set to expired.
4. Write an audit log entry.

---

8. Dashboard: Command History

GET /venues/:venueId/commands

Query params: ?status=pending&limit=50&offset=0

Headers: Authorization: Bearer <client_session_token>

Response (200):

{
  "commands": [
    {
      "id": "uuid",
      "tabId": "local-tab-uuid",
      "type": "close_tab",
      "status": "pending",
      "paymentMethod": "cash",
      "requestedBy": "Eigenaar",
      "createdAt": "2026-09-16T21:00:00Z",
      "processedAt": null,
      "result": null
    }
  ],
  "total": 1
}

---

Authentication & Security

Device Tokens

• Format: dev_ prefix + 48 random bytes, base64-url-encoded (64 chars).
• Stored as a SHA-256 hash in devices.token_sh.
• Expire after 90 days. The device receives a new token on re-registration.
• Revocation sets evoked_at immediately.

Client Authentication

• Standard email/password with bcrypt.
• Sessions via JWT with 24-hour expiry and refresh token.
• Optional 2FA (TOTP) for admin role.

General Rules

• Every endpoint validates the caller's venue scope. A device or client from venue A cannot access data from venue B.
• No public endpoint accepts a tabId without authentication.
• CORS is restricted to the dashboard origin only.

---

Command Expiry

A close_tab command expires 24 hours after creation.

Mechanism:

• The expires_at column is set on insert.
• The sync endpoint queries WHERE status = 'pending' AND expires_at > now(). Any rows where expires_at <= now() are updated to status = 'expired' before returning.
• A separate cron job (every hour) marks expired commands and writes audit log entries for housekeeping.

---

Error Responses

All errors follow this shape:

{
  "error": {
    "code": "device_not_found",
    "message": "No device found with the given ID"
  }
}

HTTP status codes:

Code	Meaning
200	Success
201	Created
400	Bad reqest (validation)
401	Unauthorized (missing or expired token)
403	Forbidden (wrong role)
404	Not found
409	Conflict (version mismatch etc.)
429	Too many request (Retry-After header)

---

Sequence: Full Close Flow

Dashboard client selects tab → clicks "Close"
        │
        ▼
Dashboard POST /venues/:id/commands { tabId, expectedVersion, paymentMethod }
        │
        ▼
Server resolves deviceId from tab_ownership
Server inserts remote_command (status=pending)
Server returns commandId + status=pending
        │
        ▼
Bar app syncs (every 90s or on resume)
  POST /devices/:id/sync { openTabs, cursor }
         │
         ▼
  Server returns [pending command]
         │
         ▼
  Bar app processes command:
    ├─ Tab not found       → ack conflict
    ├── Tab already closed → ack conflict
    ├── Version mismatch   → ack conflict
    └── Success → closes tab → ak completed
         │
         ▼
  Bar app POST /devices/:id/commands/:id/ack { status, result }
         │
         ▼
  Server updates command status + audit log
```essages notification

The bar app shows a toast. The dashboard sees `status = completed` on next poll.

---

## Implementation Priority

1. **Phase 1** — `venues`, `devices`, `client_users` tables + device registration + basic auth
2. **Phase 2** — `open_tab_snapshots` + `tab_ownership` + sync endpoint (ignore commands, just store state)
3. **Phase 3** — `remote_commands` + `audit_log` + command creation + command ack + full sync loop
4. **Phase 4** — Dashboard read endpoints (list tabs, list devices, command history)
5. **Phase 5** — Device revocation + rate limiting + command expiry cron
6. **Phase 6** — Optional: 2FA, refresh tokens, outbound state push after mutations