Skip to content

Commit 217a0d3

Browse files
mafredrimafredri
committed
update migrations, add status and ua, unique entries
1 parent c723b95 commit 217a0d3

File tree

2 files changed

+23
-17
lines changed

2 files changed

+23
-17
lines changed

‎coderd/database/migrations/000301_add_workspace_app_audit_sessions.up.sql

Lines changed: 19 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,27 +1,33 @@
1+
-- Keep all unique fields as non-null because `UNIQUE NULLS NOT DISTINCT`
2+
-- requires PostgreSQL 15+.
13
CREATE UNLOGGED TABLE workspace_app_audit_sessions (
2-
id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
34
agent_id UUID NOT NULL,
4-
app_id UUID NULL,
5-
user_id UUID NULL,
6-
ip inet NULL,
5+
app_id UUID NOT NULL, -- Can be NULL, but must be uuid.Nil.
6+
user_id UUID NOT NULL, -- Can be NULL, but must be uuid.Nil.
7+
ip inet NOT NULL,
8+
user_agent TEXT NOT NULL,
79
slug_or_port TEXT NOT NULL,
10+
status_code int4 NOT NULL,
811
started_at TIMESTAMP WITH TIME ZONE NOT NULL,
912
updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
10-
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
1113
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)
1318
);
1419

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.';
2024
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.';
2126
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.';
2228
COMMENT ON COLUMN workspace_app_audit_sessions.started_at IS 'The time the user started the session.';
2329
COMMENT ON COLUMN workspace_app_audit_sessions.updated_at IS 'The time the session was last updated.';
2430

25-
CREATE INDEX workspace_app_audit_sessions_agent_id_app_id_slug_or_port ON workspace_app_audit_sessions (agent_id, app_id, slug_or_port);
31+
CREATE UNIQUE INDEX workspace_app_audit_sessions_unique_index ON workspace_app_audit_sessions (agent_id, app_id, user_id, ip, user_agent, slug_or_port, status_code);
2632

27-
COMMENT ON INDEX workspace_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 INDEX workspace_app_audit_sessions_unique_index IS 'Unique index to ensure that we do not allow duplicate entries from multiple transactions.';

‎coderd/database/migrations/testdata/fixtures/000301_add_workspace_app_audit_sessions.up.sql

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
INSERT INTO workspace_app_audit_sessions
2-
(agent_id, app_id, user_id, ip, slug_or_port, started_at, updated_at)
2+
(agent_id, app_id, user_id, ip, user_agent, slug_or_port, status_code, started_at, updated_at)
33
VALUES
4-
('45e89705-e09d-4850-bcec-f9a937f5d78d', '36b65d0c-042b-4653-863a-655ee739861c', '30095c71-380b-457a-8995-97b8ee6e5307', '127.0.0.1', '', '2025-03-04 15:08:38.579772+02', '2025-03-04 15:06:48.755158+02'),
5-
('45e89705-e09d-4850-bcec-f9a937f5d78d', '36b65d0c-042b-4653-863a-655ee739861c', NULL, '127.0.0.1', '', '2025-03-04 15:08:44.411389+02', '2025-03-04 15:08:44.411389+02'),
6-
('45e89705-e09d-4850-bcec-f9a937f5d78d', NULL, NULL, '::1', 'terminal', '2025-03-04 15:25:55.555306+02', '2025-03-04 15:25:55.555306+02');
4+
('45e89705-e09d-4850-bcec-f9a937f5d78d', '36b65d0c-042b-4653-863a-655ee739861c', '30095c71-380b-457a-8995-97b8ee6e5307', '127.0.0.1', 'curl', '', 200, '2025-03-04 15:08:38.579772+02', '2025-03-04 15:06:48.755158+02'),
5+
('45e89705-e09d-4850-bcec-f9a937f5d78d', '36b65d0c-042b-4653-863a-655ee739861c', NULL, '127.0.0.1', 'curl', '', 200, '2025-03-04 15:08:44.411389+02', '2025-03-04 15:08:44.411389+02'),
6+
('45e89705-e09d-4850-bcec-f9a937f5d78d', NULL, NULL, '::1', 'curl', 'terminal', 0, '2025-03-04 15:25:55.555306+02', '2025-03-04 15:25:55.555306+02');

0 commit comments

Comments
 (0)