|
| 1 | +-- Keep all unique fields as non-null because `UNIQUE NULLS NOT DISTINCT` |
| 2 | +-- requires PostgreSQL 15+. |
1 | 3 | CREATE UNLOGGED TABLE workspace_app_audit_sessions ( |
2 | | -id UUIDPRIMARY KEYNOT NULL DEFAULT gen_random_uuid(), |
3 | 4 | agent_id UUIDNOT NULL, |
4 | | -app_id UUIDNULL, |
5 | | -user_id UUIDNULL, |
6 | | -ipinetNULL, |
| 5 | +app_id UUIDNOT NULL,-- Can be NULL, but must be uuid.Nil. |
| 6 | +user_id UUIDNOT NULL,-- Can be NULL, but must be uuid.Nil. |
| 7 | +ipinetNOT NULL, |
| 8 | +user_agentTEXTNOT NULL, |
7 | 9 | slug_or_portTEXTNOT NULL, |
| 10 | +status_code int4NOT NULL, |
8 | 11 | started_atTIMESTAMP WITH TIME ZONENOT NULL, |
9 | 12 | updated_atTIMESTAMP WITH TIME ZONENOT NULL, |
10 | | -FOREIGN KEY (user_id)REFERENCES users (id)ON DELETE CASCADE, |
11 | 13 | FOREIGN KEY (agent_id)REFERENCES workspace_agents (id)ON DELETE CASCADE, |
12 | | -FOREIGN KEY (app_id)REFERENCES workspace_apps (id)ON DELETE CASCADE |
| 14 | +-- Skip foreign keys that we can't enforce due to NOT NULL constraints. |
| 15 | +-- FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, |
| 16 | +-- FOREIGN KEY (app_id) REFERENCES workspace_apps (id) ON DELETE CASCADE, |
| 17 | +UNIQUE (agent_id, app_id, user_id, ip, user_agent, slug_or_port, status_code) |
13 | 18 | ); |
14 | 19 |
|
15 | | -COMMENT ON TABLE workspace_app_audit_sessions IS'Audit sessions for workspace apps, the data in this table is ephemeral and is used to track the current session of a user in a workspace app.'; |
16 | | -COMMENT ON COLUMN workspace_app_audit_sessions.id IS'Unique identifier for the workspace app audit session.'; |
17 | | -COMMENT ON COLUMN workspace_app_audit_sessions.agent_id IS'The agent that is currently in the workspace app.'; |
18 | | -COMMENT ON COLUMN workspace_app_audit_sessions.app_id IS'The app that is currently in the workspace app. This is nullable because ports are not associated with an app.'; |
19 | | -COMMENT ON COLUMN workspace_app_audit_sessions.user_id IS'The user that is currently using the workspace app. This is nullable because the app may be public.'; |
| 20 | +COMMENT ON TABLE workspace_app_audit_sessions IS'Audit sessions for workspace apps, the data in this table is ephemeral and is used to deduplicate audit log entries for workspace apps. While a session is active, the same data will not be logged again. This table does not store historical data.'; |
| 21 | +COMMENT ON COLUMN workspace_app_audit_sessions.agent_id IS'The agent that the workspace app or port forward belongs to.'; |
| 22 | +COMMENT ON COLUMN workspace_app_audit_sessions.app_id IS'The app that is currently in the workspace app. This is may be uuid.Nil because ports are not associated with an app.'; |
| 23 | +COMMENT ON COLUMN workspace_app_audit_sessions.user_id IS'The user that is currently using the workspace app. This is may be uuid.Nil if we cannot determine the user.'; |
20 | 24 | COMMENT ON COLUMN workspace_app_audit_sessions.ip IS'The IP address of the user that is currently using the workspace app.'; |
| 25 | +COMMENT ON COLUMN workspace_app_audit_sessions.user_agent IS'The user agent of the user that is currently using the workspace app.'; |
21 | 26 | COMMENT ON COLUMN workspace_app_audit_sessions.slug_or_port IS'The slug or port of the workspace app that the user is currently using.'; |
| 27 | +COMMENT ON COLUMN workspace_app_audit_sessions.status_code IS'The HTTP status produced by the token authorization. Defaults to 200 if no status is provided.'; |
22 | 28 | COMMENT ON COLUMN workspace_app_audit_sessions.started_at IS'The time the user started the session.'; |
23 | 29 | COMMENT ON COLUMN workspace_app_audit_sessions.updated_at IS'The time the session was last updated.'; |
24 | 30 |
|
25 | | -CREATEINDEXworkspace_app_audit_sessions_agent_id_app_id_slug_or_portON workspace_app_audit_sessions (agent_id, app_id, slug_or_port); |
| 31 | +CREATEUNIQUEINDEXworkspace_app_audit_sessions_unique_indexON workspace_app_audit_sessions (agent_id, app_id,user_id, ip, user_agent,slug_or_port, status_code); |
26 | 32 |
|
27 | | -COMMENT ON INDEXworkspace_app_audit_sessions_agent_id_app_id_slug_or_port IS'Index for the agent_id and app_id columns to perform updates.'; |
| 33 | +COMMENT ON INDEXworkspace_app_audit_sessions_unique_index IS'Unique index to ensure that we do not allow duplicate entries from multiple transactions.'; |