UserSession table in Hatchet Postgres grows unboundedly from Bearer API requests

[luislee818]

Problem

In our self-hosted Hatchet deployment, the UserSession table in the Hatchet Postgres instance has grown to 2.6M rows (~1 GB) over ~30 days.

SELECT count(*) FILTER (WHERE "userId" IS NULL), count(*) FILTER (WHERE "userId" IS NOT NULL)
FROM "UserSession";
-- 2,604,487 anonymous   |   9 with userId (4 real users)

• ~1 row/sec sustained insertion rate, matching our REST API call volume.
• Every row has userId = NULL, expiresAt = createdAt + 30d, createdAt == updatedAt (written once, never read again).
• The only DELETE in pkg/repository/sqlcv1/users.sql is DeleteUserSession by id (explicit logout). Nothing purges by expiresAt. Tenant retention (SERVER_LIMITS_DEFAULT_TENANT_RETENTION_PERIOD) doesn't cover this table.

Likely cause

Bearer-authenticated REST requests (SDK calls, worker callbacks) appear to trip the cookie session middleware, which calls Save() → no cookie → fresh UserSession row inserted. Our setup: ALB OIDC for dashboard humans, Bearer tokens for SDK/workers — so essentially every row is from Bearer traffic.

Suggested fix

Skip cookie session creation for Bearer-authenticated requests, and/or add a periodic DELETE FROM "UserSession" WHERE "expiresAt" < NOW() to the retention cron.

Version: hatchet-lite:v0.82.3.

[gregfurman]

Thanks for the report here! I believe I've recreated this issue in my local environment 👇

Expand for recreation steps

1. Build the frontend, start the DB, and then launch hatchet-lite locally:

(cd frontend/app && npm run build)
task start-db migrate seed
LITE_STATIC_ASSET_DIR=./frontend/app/dist LITE_FRONTEND_PORT=5173 task start-lite

2. Connect to the postgres instance and execute the count query

PGPASSWORD=hatchet psql -p 5431 -h localhost -U hatchet hatchet -c 'SELECT count(*) FILTER (WHERE "userId" IS NULL), count(*) FILTER (WHERE "userId" IS NOT NULL)

 count | count 
-------+-------
     0 |     0

3. Then generate an API token (used as bearer) and pass that to a simple GET workflows request on the default tenant:

HATCHET_CLIENT_TOKEN=$(task generate-dev-api-token 2>/dev/null)
curl -H "Authorization: Bearer $HATCHET_CLIENT_TOKEN" "http://localhost:8080/api/v1/tenants/707d0855-80ab-4e1f-a156-f1c4546cbf52/workflows?limit=1&offset=0" | jq

4. Run the postgres query again and see how the count of users without an ID increases:

 count | count 
-------+-------
    1 |     0

Regarding the solution:

Skip cookie session creation for Bearer-authenticated requests [...]

This seems easiest to me tbh. If we go this route, I'm wondering if it's worth including a cleanup command during the migration to remove all these (supposedly orphaned) session records. Maybe including a batch delete if they've expired 🤔

Otherwise, will check with the team if we care about this session information being stored at the UserSession level (am doubtful 😅 though)