DROP TABLE workspace_app_audit_sessions;

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.';

INSERT INTO workspace_app_audit_sessions

(agent_id, app_id, user_id, ip, started_at, updated_at)

('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');