add migrations

coder · mafredri · Mar 18, 2025 · Mar 3, 2025 · Mar 3, 2025 · Mar 3, 2025
commit d235001ec80c15722136e8b5cc4a062a666bbf4e
diff --git a/coderd/database/migrations/000301_add_workspace_app_audit_sessions.down.sql b/coderd/database/migrations/000301_add_workspace_app_audit_sessions.down.sql
@@ -0,0 +1 @@
+DROP TABLE workspace_app_audit_sessions;
diff --git a/coderd/database/migrations/000301_add_workspace_app_audit_sessions.up.sql b/coderd/database/migrations/000301_add_workspace_app_audit_sessions.up.sql
@@ -0,0 +1,25 @@
+CREATE UNLOGGED TABLE workspace_app_audit_sessions (
+	id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
+	agent_id UUID NOT NULL,
+	app_id UUID NULL,
+	user_id UUID,
+	ip inet,
+	started_at TIMESTAMP WITH TIME ZONE NOT NULL,
+	updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
+	FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
+	FOREIGN KEY (agent_id) REFERENCES workspace_agents (id) ON DELETE CASCADE,
+	FOREIGN KEY (app_id) REFERENCES workspace_apps (id) ON DELETE CASCADE
+);
+
+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.';
+COMMENT ON COLUMN workspace_app_audit_sessions.id IS 'Unique identifier for the workspace app audit session.';
+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.';
+COMMENT ON COLUMN workspace_app_audit_sessions.ip IS 'The IP address of the user that is currently using the workspace app.';
+COMMENT ON COLUMN workspace_app_audit_sessions.agent_id IS 'The agent that is currently in the workspace app.';
+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.';
+COMMENT ON COLUMN workspace_app_audit_sessions.started_at IS 'The time the user started the session.';
+COMMENT ON COLUMN workspace_app_audit_sessions.updated_at IS 'The time the session was last updated.';
+
+CREATE INDEX workspace_app_audit_sessions_agent_id_app_id ON workspace_app_audit_sessions (agent_id, app_id);
+
+COMMENT ON INDEX workspace_app_audit_sessions_agent_id_app_id IS 'Index for the agent_id and app_id columns to perform updates.';
diff --git a/coderd/database/migrations/testdata/fixtures/000301_add_workspace_app_audit_sessions.up.sql b/coderd/database/migrations/testdata/fixtures/000301_add_workspace_app_audit_sessions.up.sql
@@ -0,0 +1,6 @@
+INSERT INTO workspace_app_audit_sessions
+	(agent_id, app_id, user_id, ip, started_at, updated_at)
+VALUES
+	('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'),
+	('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'),
+	('45e89705-e09d-4850-bcec-f9a937f5d78d', NULL, NULL, '::1', '2025-03-04 15:25:55.555306+02', '2025-03-04 15:25:55.555306+02');