work on db queries

coder · defelmnq · Mar 3, 2025 · Feb 18, 2025 · Feb 19, 2025 · Feb 19, 2025
commit 90c7b64a84edf24c0968daa22522193958dd6c60
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1417,6 +1417,14 @@ func (q *querier) FavoriteWorkspace(ctx context.Context, id uuid.UUID) error {
 	return update(q.log, q.auth, fetch, q.db.FavoriteWorkspace)(ctx, id)
 }
 
+func (q *querier) FetchInboxNotificationsByUserID(ctx context.Context, userID uuid.UUID) ([]database.NotificationsInbox, error) {
+	panic("not implemented")
+}
+
+func (q *querier) FetchInboxNotificationsByUserIDAndTemplateIDAndTargetID(ctx context.Context, arg database.FetchInboxNotificationsByUserIDAndTemplateIDAndTargetIDParams) ([]database.NotificationsInbox, error) {
+	panic("not implemented")
+}
+
 func (q *querier) FetchMemoryResourceMonitorsByAgentID(ctx context.Context, agentID uuid.UUID) (database.WorkspaceAgentMemoryResourceMonitor, error) {
 	workspace, err := q.db.GetWorkspaceByAgentID(ctx, agentID)
 	if err != nil {
@@ -1438,6 +1446,14 @@ func (q *querier) FetchNewMessageMetadata(ctx context.Context, arg database.Fetc
 	return q.db.FetchNewMessageMetadata(ctx, arg)
 }
 
+func (q *querier) FetchUnreadInboxNotificationsByUserID(ctx context.Context, userID uuid.UUID) ([]database.NotificationsInbox, error) {
+	panic("not implemented")
+}
+
+func (q *querier) FetchUnreadInboxNotificationsByUserIDAndTemplateIDAndTargetID(ctx context.Context, arg database.FetchUnreadInboxNotificationsByUserIDAndTemplateIDAndTargetIDParams) ([]database.NotificationsInbox, error) {
+	panic("not implemented")
+}
+
 func (q *querier) FetchVolumesResourceMonitorsByAgentID(ctx context.Context, agentID uuid.UUID) ([]database.WorkspaceAgentVolumeResourceMonitor, error) {
 	workspace, err := q.db.GetWorkspaceByAgentID(ctx, agentID)
 	if err != nil {
@@ -3077,6 +3093,10 @@ func (q *querier) InsertGroupMember(ctx context.Context, arg database.InsertGrou
 	return update(q.log, q.auth, fetch, q.db.InsertGroupMember)(ctx, arg)
 }
 
+func (q *querier) InsertInboxNotification(ctx context.Context, arg database.InsertInboxNotificationParams) (database.NotificationsInbox, error) {
+	panic("not implemented")
+}
+
 func (q *querier) InsertLicense(ctx context.Context, arg database.InsertLicenseParams) (database.License, error) {
 	if err := q.authorizeContext(ctx, policy.ActionCreate, rbac.ResourceLicense); err != nil {
 		return database.License{}, err
@@ -3564,6 +3584,10 @@ func (q *querier) RevokeDBCryptKey(ctx context.Context, activeKeyDigest string)
 	return q.db.RevokeDBCryptKey(ctx, activeKeyDigest)
 }
 
+func (q *querier) SetInboxNotificationAsRead(ctx context.Context, arg database.SetInboxNotificationAsReadParams) error {
+	panic("not implemented")
+}
+
 func (q *querier) TryAcquireLock(ctx context.Context, id int64) (bool, error) {
 	return q.db.TryAcquireLock(ctx, id)
 }

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -2363,6 +2363,19 @@ func (q *FakeQuerier) FavoriteWorkspace(_ context.Context, arg uuid.UUID) error
 	return nil
 }
 
+func (q *FakeQuerier) FetchInboxNotificationsByUserID(ctx context.Context, userID uuid.UUID) ([]database.NotificationsInbox, error) {
+	panic("not implemented")
+}
+
+func (q *FakeQuerier) FetchInboxNotificationsByUserIDAndTemplateIDAndTargetID(ctx context.Context, arg database.FetchInboxNotificationsByUserIDAndTemplateIDAndTargetIDParams) ([]database.NotificationsInbox, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+
+	panic("not implemented")
+}
+
 func (q *FakeQuerier) FetchMemoryResourceMonitorsByAgentID(_ context.Context, agentID uuid.UUID) (database.WorkspaceAgentMemoryResourceMonitor, error) {
 	for _, monitor := range q.workspaceAgentMemoryResourceMonitors {
 		if monitor.AgentID == agentID {
@@ -2405,6 +2418,19 @@ func (q *FakeQuerier) FetchNewMessageMetadata(_ context.Context, arg database.Fe
 	}, nil
 }
 
+func (q *FakeQuerier) FetchUnreadInboxNotificationsByUserID(ctx context.Context, userID uuid.UUID) ([]database.NotificationsInbox, error) {
+	panic("not implemented")
+}
+
+func (q *FakeQuerier) FetchUnreadInboxNotificationsByUserIDAndTemplateIDAndTargetID(ctx context.Context, arg database.FetchUnreadInboxNotificationsByUserIDAndTemplateIDAndTargetIDParams) ([]database.NotificationsInbox, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+
+	panic("not implemented")
+}
+
 func (q *FakeQuerier) FetchVolumesResourceMonitorsByAgentID(_ context.Context, agentID uuid.UUID) ([]database.WorkspaceAgentVolumeResourceMonitor, error) {
 	monitors := []database.WorkspaceAgentVolumeResourceMonitor{}
 
@@ -7959,6 +7985,15 @@ func (q *FakeQuerier) InsertGroupMember(_ context.Context, arg database.InsertGr
 	return nil
 }
 
+func (q *FakeQuerier) InsertInboxNotification(ctx context.Context, arg database.InsertInboxNotificationParams) (database.NotificationsInbox, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return database.NotificationsInbox{}, err
+	}
+
+	panic("not implemented")
+}
+
 func (q *FakeQuerier) InsertLicense(
 	_ context.Context, arg database.InsertLicenseParams,
 ) (database.License, error) {
@@ -9443,6 +9478,15 @@ func (q *FakeQuerier) RevokeDBCryptKey(_ context.Context, activeKeyDigest string
 	return sql.ErrNoRows
 }
 
+func (q *FakeQuerier) SetInboxNotificationAsRead(ctx context.Context, arg database.SetInboxNotificationAsReadParams) error {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return err
+	}
+
+	panic("not implemented")
+}
+
 func (*FakeQuerier) TryAcquireLock(_ context.Context, _ int64) (bool, error) {
 	return false, xerrors.New("TryAcquireLock must only be called within a transaction")
 }

diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000295_notifications_inbox.down.sql b/coderd/database/migrations/000295_notifications_inbox.down.sql
@@ -0,0 +1 @@
+DROP TABLE IF EXISTS notifications_inbox;
diff --git a/coderd/database/migrations/000295_notifications_inbox.up.sql b/coderd/database/migrations/000295_notifications_inbox.up.sql
@@ -0,0 +1,15 @@
+CREATE TABLE notifications_inbox (
+	id 				UUID 						PRIMARY KEY,
+	user_id 		UUID 						NOT NULL,
+	template_id 	UUID 						NOT NULL,
+	target_id 		UUID,
+	title			TEXT 						NOT NULL,
+	content 		TEXT 						NOT NULL,
+	icon 			TEXT 						NOT NULL,
+	actions 		JSONB 						NOT NULL,
+	read_at 		TIMESTAMP WITH TIME ZONE,
+	created_at 		TIMESTAMP WITH TIME ZONE 	NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX idx_notifications_inbox_user_id_read_at ON notifications_inbox(user_id, read_at);
+CREATE INDEX idx_notifications_inbox_user_id_template_id_target_id ON notifications_inbox(user_id, template_id, target_id);
diff --git a/coderd/database/models.go b/coderd/database/models.go