This is an automated email from the ASF dual-hosted git repository.
aicam pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/texera.git
The following commit(s) were added to refs/heads/main by this push:
new 2fbe37ab71 feat: add affiliation attribute to user (#4113)
2fbe37ab71 is described below
commit 2fbe37ab7185cf7ed411ad8d933d8294cbc049fa
Author: Jaeyun Kim <[email protected]>
AuthorDate: Wed Dec 31 12:34:52 2025 -0800
feat: add affiliation attribute to user (#4113)
# What changes were proposed in this PR?
## Summary
To gather more user information for a better overview of users, this PR
introduces a new column `affiliation` to the `user` table. Now when a
user logins to the Texera for the first time (after getting approved to
REGULAR role), they will be prompted to enter their affiliation. The
answer will be recorded to the database and retrieved when admins enter
the admin dashboard.
## For Developers
Please do the following steps to incorporate with new changes:
- Apply sql/updates/16.sql to your local postgres instance
- Run
common/dao/src/main/scala/org/apache/texera/dao/JooqCodeGenerator.scala
to generate jooq tables
## Sample Video
https://github.com/user-attachments/assets/61e895db-8e30-4c59-8e98-fa527995b486
## Design of the Feature
When a user logins to the system for the first time, they will be
prompted to enter their affiliation after getting approved to REGULAR
role. The user can submit their affiliation and the frontend would send
this information to the backend to save in the database. Users can
choose to either enter the affiliation or skip the prompt and the system
would remember if the user has been prompted or not by checking the user
data from the database. Depending on the user's answer, the
`affiliation` column would have different data (more details are
included in "Backend Changes"). The system would only prompt once when
the user logins to the system for the first time and would never ask
again. To view the affiliation information, admins can go to the admin
dashboard to view the affiliations of users.
## Backend Changes
Introduced column `affiliation` to the `user` table. This column would
have three types of entry:
1. null: Indicates the user has never been prompted before. Next time
when the user logins to the system, they will be prompted to answer the
affiliation question.
2. emptry string "": the user has been prompted and did not answer the
affiliation question. This is to indicate that the user did not answer
this question (whether by hitting the skip button, ESC, X, or pressing
spaces outside of the prompt).
3. Actual value.
`16.sql` adds the column to the `user` table and ensures the existing
users' affiliation column is set to null. ddl file changed as well.
Added a `UserResource.scala` file to include the functions/apis related
to retrieving & updating User data. Currently it only contains functions
related to this PR, but in future other related functions can be added
to this file as well.
### Original `user` Schema
<img width="300" height="400" alt="image"
src="https://github.com/user-attachments/assets/5be89398-583e-486c-96af-448fffbbf2d5";
/>
### Proposed `user` Schema
<img width="300" height="400" alt="image"
src="https://github.com/user-attachments/assets/b1522ce0-f905-4865-a62d-813770eef3d7";
/>
## Frontend Changes
Added the prompt window to pop up in the main page after logging in.
Added `affiliation` column to admin dashboard to cooperate with the new
data.
Changed files that contain class `User` as new attribute `affiliation`
is added to the class.
### Any related issues, documentation, discussions?
<!--
Please use this section to link other resources if not mentioned
already.
1. If this PR fixes an issue, please include `Fixes #1234`, `Resolves
#1234`
or `Closes #1234`. If it is only related, simply mention the issue
number.
5. If there is design documentation, please add the link.
6. If there is a discussion in the mailing list, please add the link.
-->
Closes Issue #4118.
### How was this PR tested?
<!--
If tests were added, say they were added here. Or simply mention that if
the PR
is tested with existing test cases. Make sure to include/update test
cases that
check the changes thoroughly including negative and positive cases if
possible.
If it was tested in a way different from regular unit tests, please
clarify how
you tested step by step, ideally copy and paste-able, so that other
reviewers can
test and check, and descendants can verify in the future. If tests were
not added,
please describe why they were not added and/or why it was difficult to
add.
-->
Manually tested.
### Was this PR authored or co-authored using generative AI tooling?
<!--
If generative AI tooling has been used in the process of authoring this
PR,
please include the phrase: 'Generated-by: ' followed by the name of the
tool
and its version. If no, write 'No'.
Please refer to the [ASF Generative Tooling
Guidance](https://www.apache.org/legal/generative-tooling.html) for
details.
-->
Generated-by: ChatGPT 5.1 (bug fixing)
---
.../texera/web/ServletAwareConfigurator.scala | 2 +
.../apache/texera/web/TexeraWebApplication.scala | 2 +
.../apache/texera/web/auth/GuestAuthFilter.scala | 2 +-
.../apache/texera/web/auth/UserAuthenticator.scala | 13 +++-
.../dashboard/admin/user/AdminUserResource.scala | 6 +-
.../web/resource/dashboard/user/UserResource.scala | 74 ++++++++++++++++++++++
.../scala/org/apache/texera/auth/JwtParser.scala | 2 +-
.../app/common/service/user/stub-user.service.ts | 8 +++
.../app/common/service/user/user.service.spec.ts | 2 +
.../src/app/common/service/user/user.service.ts | 37 ++++++++++-
frontend/src/app/common/type/user.ts | 1 +
.../component/admin/user/admin-user.component.html | 2 +
.../dashboard/component/dashboard.component.html | 32 ++++++++++
.../app/dashboard/component/dashboard.component.ts | 69 +++++++++++++++++++-
sql/texera_ddl.sql | 1 +
sql/updates/16.sql | 29 +++++++++
16 files changed, 275 insertions(+), 7 deletions(-)
diff --git
a/amber/src/main/scala/org/apache/texera/web/ServletAwareConfigurator.scala
b/amber/src/main/scala/org/apache/texera/web/ServletAwareConfigurator.scala
index 357c731ee3..6ee33a3855 100644
--- a/amber/src/main/scala/org/apache/texera/web/ServletAwareConfigurator.scala
+++ b/amber/src/main/scala/org/apache/texera/web/ServletAwareConfigurator.scala
@@ -78,6 +78,7 @@ class ServletAwareConfigurator extends
ServerEndpointConfig.Configurator with La
null,
null,
null,
+ null,
null
)
)
@@ -107,6 +108,7 @@ class ServletAwareConfigurator extends
ServerEndpointConfig.Configurator with La
null,
null,
null,
+ null,
null
)
)
diff --git
a/amber/src/main/scala/org/apache/texera/web/TexeraWebApplication.scala
b/amber/src/main/scala/org/apache/texera/web/TexeraWebApplication.scala
index c2780add35..4264a9ca18 100644
--- a/amber/src/main/scala/org/apache/texera/web/TexeraWebApplication.scala
+++ b/amber/src/main/scala/org/apache/texera/web/TexeraWebApplication.scala
@@ -38,6 +38,7 @@ import
org.apache.texera.web.resource.dashboard.admin.execution.AdminExecutionRe
import
org.apache.texera.web.resource.dashboard.admin.settings.AdminSettingsResource
import org.apache.texera.web.resource.dashboard.admin.user.AdminUserResource
import org.apache.texera.web.resource.dashboard.hub.HubResource
+import org.apache.texera.web.resource.dashboard.user.UserResource
import org.apache.texera.web.resource.dashboard.user.project.{
ProjectAccessResource,
ProjectResource,
@@ -140,6 +141,7 @@ class TexeraWebApplication
environment.jersey.register(classOf[WorkflowAccessResource])
environment.jersey.register(classOf[WorkflowResource])
environment.jersey.register(classOf[HubResource])
+ environment.jersey.register(classOf[UserResource])
environment.jersey.register(classOf[WorkflowVersionResource])
environment.jersey.register(classOf[ProjectResource])
environment.jersey.register(classOf[ProjectAccessResource])
diff --git
a/amber/src/main/scala/org/apache/texera/web/auth/GuestAuthFilter.scala
b/amber/src/main/scala/org/apache/texera/web/auth/GuestAuthFilter.scala
index 40f90ee8ea..5946c40f11 100644
--- a/amber/src/main/scala/org/apache/texera/web/auth/GuestAuthFilter.scala
+++ b/amber/src/main/scala/org/apache/texera/web/auth/GuestAuthFilter.scala
@@ -39,7 +39,7 @@ import javax.ws.rs.core.SecurityContext
}
val GUEST: User =
- new User(null, "guest", null, null, null, null, UserRoleEnum.REGULAR,
null, null)
+ new User(null, "guest", null, null, null, null, UserRoleEnum.REGULAR,
null, null, null)
}
@PreMatching
diff --git
a/amber/src/main/scala/org/apache/texera/web/auth/UserAuthenticator.scala
b/amber/src/main/scala/org/apache/texera/web/auth/UserAuthenticator.scala
index 57109273e3..2a6a2e4770 100644
--- a/amber/src/main/scala/org/apache/texera/web/auth/UserAuthenticator.scala
+++ b/amber/src/main/scala/org/apache/texera/web/auth/UserAuthenticator.scala
@@ -44,7 +44,18 @@ object UserAuthenticator extends Authenticator[JwtContext,
SessionUser] with Laz
val accountCreation =
context.getJwtClaims.getClaimValue("accountCreation").asInstanceOf[OffsetDateTime]
val user =
- new User(userId, userName, email, null, googleId, null, role, comment,
accountCreation)
+ new User(
+ userId,
+ userName,
+ email,
+ null,
+ googleId,
+ null,
+ role,
+ comment,
+ accountCreation,
+ null
+ )
Optional.of(new SessionUser(user))
} catch {
case e: Exception =>
diff --git
a/amber/src/main/scala/org/apache/texera/web/resource/dashboard/admin/user/AdminUserResource.scala
b/amber/src/main/scala/org/apache/texera/web/resource/dashboard/admin/user/AdminUserResource.scala
index 03ccd9296e..7372ab6fc0 100644
---
a/amber/src/main/scala/org/apache/texera/web/resource/dashboard/admin/user/AdminUserResource.scala
+++
b/amber/src/main/scala/org/apache/texera/web/resource/dashboard/admin/user/AdminUserResource.scala
@@ -45,7 +45,8 @@ case class UserInfo(
googleAvatar: String,
comment: String,
lastLogin: java.time.OffsetDateTime, // will be null if never logged in
- accountCreation: java.time.OffsetDateTime
+ accountCreation: java.time.OffsetDateTime,
+ affiliation: String
)
object AdminUserResource {
@@ -78,7 +79,8 @@ class AdminUserResource {
USER.GOOGLE_AVATAR,
USER.COMMENT,
USER_LAST_ACTIVE_TIME.LAST_ACTIVE_TIME,
- USER.ACCOUNT_CREATION_TIME
+ USER.ACCOUNT_CREATION_TIME,
+ USER.AFFILIATION
)
.from(USER)
.leftJoin(USER_LAST_ACTIVE_TIME)
diff --git
a/amber/src/main/scala/org/apache/texera/web/resource/dashboard/user/UserResource.scala
b/amber/src/main/scala/org/apache/texera/web/resource/dashboard/user/UserResource.scala
new file mode 100644
index 0000000000..eaafe7f323
--- /dev/null
+++
b/amber/src/main/scala/org/apache/texera/web/resource/dashboard/user/UserResource.scala
@@ -0,0 +1,74 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.texera.web.resource.dashboard.user
+
+import org.apache.texera.dao.SqlServer
+import org.apache.texera.dao.jooq.generated.tables.daos.UserDao
+import org.apache.texera.dao.jooq.generated.tables.User.USER
+import javax.ws.rs._
+import javax.ws.rs.core.{MediaType, Response}
+
+case class AffiliationUpdateRequest(uid: Int, affiliation: String)
+
+object UserResource {
+ private lazy val context = SqlServer.getInstance().createDSLContext()
+ private lazy val userDao = new UserDao(context.configuration)
+}
+
+@Path("/user")
+class UserResource {
+
+ /**
+ * Update the affiliation of a user.
+ * Used by a first-time user to set their own affiliation.
+ */
+ @PUT
+ @Path("/affiliation")
+ @Consumes(Array(MediaType.APPLICATION_JSON))
+ def updateAffiliation(request: AffiliationUpdateRequest): Unit = {
+ val rowsUpdated = UserResource.context
+ .update(USER)
+ .set(USER.AFFILIATION, request.affiliation)
+ .where(USER.UID.eq(request.uid))
+ .execute()
+
+ if (rowsUpdated == 0) {
+ throw new WebApplicationException("User not found",
Response.Status.NOT_FOUND)
+ }
+ }
+
+ /**
+ * Gets affiliation with uid. Returns "", null or affiliation.
+ * "": Prompted and no response
+ * null: never prompted
+ * @param uid
+ * @return
+ */
+ @GET
+ @Path("/affiliation")
+ @Produces(Array(MediaType.APPLICATION_JSON))
+ def needsAffiliation(@QueryParam("uid") uid: Int): java.lang.Boolean = {
+ val user = UserResource.userDao.fetchOneByUid(uid)
+ if (user == null) {
+ throw new WebApplicationException("User not found",
Response.Status.NOT_FOUND)
+ }
+ java.lang.Boolean.valueOf(user.getAffiliation == null)
+ }
+}
diff --git a/common/auth/src/main/scala/org/apache/texera/auth/JwtParser.scala
b/common/auth/src/main/scala/org/apache/texera/auth/JwtParser.scala
index 1f7673c275..48c6bacafc 100644
--- a/common/auth/src/main/scala/org/apache/texera/auth/JwtParser.scala
+++ b/common/auth/src/main/scala/org/apache/texera/auth/JwtParser.scala
@@ -52,7 +52,7 @@ object JwtParser extends LazyLogging {
val role =
UserRoleEnum.valueOf(jwtClaims.getClaimValue("role").asInstanceOf[String])
val googleId = jwtClaims.getClaimValue("googleId", classOf[String])
- val user = new User(userId, userName, email, null, googleId, null, role,
null, null)
+ val user = new User(userId, userName, email, null, googleId, null, role,
null, null, null)
Optional.of(new SessionUser(user))
} catch {
case _: UnresolvableKeyException =>
diff --git a/frontend/src/app/common/service/user/stub-user.service.ts
b/frontend/src/app/common/service/user/stub-user.service.ts
index 06f09bf05d..b703d40331 100644
--- a/frontend/src/app/common/service/user/stub-user.service.ts
+++ b/frontend/src/app/common/service/user/stub-user.service.ts
@@ -84,4 +84,12 @@ export class StubUserService implements
PublicInterfaceOf<UserService> {
getAvatar(googleAvatar: string): Observable<string | undefined> {
return of(undefined);
}
+
+ checkAffiliation(): Observable<Boolean> {
+ return of(true);
+ }
+
+ updateAffiliation(_affiliation: string): Observable<void> {
+ return of(void 0);
+ }
}
diff --git a/frontend/src/app/common/service/user/user.service.spec.ts
b/frontend/src/app/common/service/user/user.service.spec.ts
index 2cec48de09..70b595ca9d 100644
--- a/frontend/src/app/common/service/user/user.service.spec.ts
+++ b/frontend/src/app/common/service/user/user.service.spec.ts
@@ -23,6 +23,7 @@ import { AuthService } from "./auth.service";
import { StubAuthService } from "./stub-auth.service";
import { skip } from "rxjs/operators";
import { commonTestProviders } from "../../testing/test-utils";
+import { HttpClientTestingModule } from "@angular/common/http/testing";
describe("UserService", () => {
let service: UserService;
@@ -30,6 +31,7 @@ describe("UserService", () => {
beforeEach(() => {
AuthService.removeAccessToken();
TestBed.configureTestingModule({
+ imports: [HttpClientTestingModule],
providers: [UserService, { provide: AuthService, useClass:
StubAuthService }, ...commonTestProviders],
});
diff --git a/frontend/src/app/common/service/user/user.service.ts
b/frontend/src/app/common/service/user/user.service.ts
index 88ab020c08..689a95d028 100644
--- a/frontend/src/app/common/service/user/user.service.ts
+++ b/frontend/src/app/common/service/user/user.service.ts
@@ -18,6 +18,8 @@
*/
import { Injectable } from "@angular/core";
+import { HttpClient } from "@angular/common/http";
+import { AppSettings } from "../../app-setting";
import { Observable, of, ReplaySubject } from "rxjs";
import { Role, User } from "../../type/user";
import { AuthService } from "./auth.service";
@@ -39,7 +41,8 @@ export class UserService {
constructor(
private authService: AuthService,
- private config: GuiConfigService
+ private config: GuiConfigService,
+ private http: HttpClient
) {
const user = this.authService.loginWithExistingToken();
this.changeUser(user);
@@ -82,6 +85,38 @@ export class UserService {
.pipe(map(({ accessToken }) => this.handleAccessToken(accessToken)));
}
+ /**
+ * Retrieves affiliation from backend and return if affiliation has been
prompted
+ * true: already prompted
+ * false: never prompted
+ */
+ public checkAffiliation(): Observable<Boolean> {
+ const user = this.currentUser;
+ if (!user) {
+ return of(false);
+ }
+ return
this.http.get<Boolean>(`${AppSettings.getApiEndpoint()}/user/affiliation`, {
+ params: { uid: user.uid.toString() },
+ });
+ }
+
+ /**
+ * updates a new registered user's affiliation
+ * @param affiliation
+ */
+ public updateAffiliation(affiliation: string): Observable<void> {
+ const user = this.currentUser;
+
+ if (!user) {
+ return of(void 0);
+ }
+
+ return
this.http.put<void>(`${AppSettings.getApiEndpoint()}/user/affiliation`, {
+ uid: user.uid,
+ affiliation: affiliation,
+ });
+ }
+
/**
* changes the current user and triggers currentUserSubject
* @param user
diff --git a/frontend/src/app/common/type/user.ts
b/frontend/src/app/common/type/user.ts
index 4d8b02cc39..2a191d52dc 100644
--- a/frontend/src/app/common/type/user.ts
+++ b/frontend/src/app/common/type/user.ts
@@ -46,6 +46,7 @@ export interface User
comment: string;
lastLogin?: number;
accountCreation?: Second;
+ affiliation?: string;
}> {}
export interface File
diff --git
a/frontend/src/app/dashboard/component/admin/user/admin-user.component.html
b/frontend/src/app/dashboard/component/admin/user/admin-user.component.html
index 446b35c9a2..e3f3d5a2ec 100644
--- a/frontend/src/app/dashboard/component/admin/user/admin-user.component.html
+++ b/frontend/src/app/dashboard/component/admin/user/admin-user.component.html
@@ -69,6 +69,7 @@
nzType="search"></span>
</nz-filter-trigger>
</th>
+ <th>Affiliation</th>
<th
[nzSortFn]="sortByComment"
[nzSortDirections]="['ascend', 'descend']"
@@ -230,6 +231,7 @@
</ng-template>
</div>
</td>
+ <td>{{ user.affiliation }}</td>
<td>
<div (focusout)="saveEdit()">
<ng-container *ngIf="editUid !== user.uid || editAttribute !==
'comment'; else editCommentTemplate">
diff --git a/frontend/src/app/dashboard/component/dashboard.component.html
b/frontend/src/app/dashboard/component/dashboard.component.html
index b238f56b93..d4d3d82d70 100644
--- a/frontend/src/app/dashboard/component/dashboard.component.html
+++ b/frontend/src/app/dashboard/component/dashboard.component.html
@@ -213,4 +213,36 @@
</nz-content>
</nz-layout>
</div>
+ <nz-modal
+ [(nzVisible)]="affiliationModalVisible"
+ [nzMaskClosable]="true"
+ [nzClosable]="true"
+ (nzOnCancel)="onAffiliationCancel()"
+ nzTitle="Tell us your affiliation">
+ <ng-container *nzModalContent>
+ <p>
+ To help us understand our users better, please tell us your
affiliation (for example, your university, company,
+ or organization).
+ </p>
+ <input
+ nz-input
+ [(ngModel)]="affiliationInput"
+ placeholder="e.g. UC Irvine" />
+ </ng-container>
+
+ <ng-container *nzModalFooter>
+ <button
+ nz-button
+ (click)="skipAffiliation()">
+ Skip
+ </button>
+ <button
+ nz-button
+ nzType="primary"
+ [nzLoading]="affiliationSaving"
+ (click)="saveAffiliation()">
+ Save
+ </button>
+ </ng-container>
+ </nz-modal>
</nz-layout>
diff --git a/frontend/src/app/dashboard/component/dashboard.component.ts
b/frontend/src/app/dashboard/component/dashboard.component.ts
index 076b9d2862..26448c88d6 100644
--- a/frontend/src/app/dashboard/component/dashboard.component.ts
+++ b/frontend/src/app/dashboard/component/dashboard.component.ts
@@ -42,6 +42,7 @@ import {
} from "../../app-routing.constant";
import { Version } from "../../../environments/version";
import { SidebarTabs } from "../../common/type/gui-config";
+import { User } from "../../common/type/user";
@Component({
selector: "texera-dashboard",
@@ -74,6 +75,10 @@ export class DashboardComponent implements OnInit {
forum_enabled: false,
about_enabled: false,
};
+ // Variables related to updating user's affiliation
+ affiliationModalVisible = false;
+ affiliationInput: string = "";
+ affiliationSaving = false;
protected readonly DASHBOARD_USER_PROJECT = DASHBOARD_USER_PROJECT;
protected readonly DASHBOARD_USER_WORKFLOW = DASHBOARD_USER_WORKFLOW;
@@ -114,11 +119,12 @@ export class DashboardComponent implements OnInit {
this.userService
.userChanged()
.pipe(untilDestroyed(this))
- .subscribe(() => {
+ .subscribe(user => {
this.ngZone.run(() => {
this.isLogin = this.userService.isLogin();
this.isAdmin = this.userService.isAdmin();
this.forumLogin();
+ this.checkAffiliationPrompt(user);
this.cdr.detectChanges();
});
});
@@ -194,6 +200,67 @@ export class DashboardComponent implements OnInit {
}
}
+ /**
+ * Prompts user to enter affiliation if they have not been prompted before
+ * @param user
+ */
+ checkAffiliationPrompt(user: User | undefined): void {
+ // Null affiliation = never prompted before
+ if (!user || !this.config.env.googleLogin) {
+ return;
+ }
+
+ this.userService
+ .checkAffiliation()
+ .pipe(untilDestroyed(this))
+ .subscribe(response => {
+ if (response) {
+ this.affiliationInput = "";
+ this.affiliationModalVisible = true;
+ } else {
+ this.affiliationModalVisible = false;
+ }
+ });
+ }
+
+ /**
+ * Saves the affiliation
+ */
+ saveAffiliation(): void {
+ const value = this.affiliationInput?.trim() ?? "";
+ this.affiliationSaving = true;
+
+ this.userService
+ .updateAffiliation(value)
+ .pipe(untilDestroyed(this))
+ .subscribe({
+ next: () => {
+ this.affiliationSaving = false;
+ this.affiliationModalVisible = false;
+ },
+ error: () => {
+ this.affiliationSaving = false;
+ this.affiliationModalVisible = false;
+ },
+ });
+ }
+
+ /**
+ * Skips the affiliation input and update the database to store an empty
string, which means the user has
+ * already been prompted.
+ */
+ skipAffiliation(): void {
+ this.affiliationInput = "";
+ this.saveAffiliation();
+ }
+
+ /**
+ * Skips the affiliation input when user closed the prompt window via
outside click, ESC
+ */
+ onAffiliationCancel(): void {
+ this.skipAffiliation();
+ }
+
checkRoute() {
const currentRoute = this.router.url;
this.displayNavbar = this.isNavbarEnabled(currentRoute);
diff --git a/sql/texera_ddl.sql b/sql/texera_ddl.sql
index 7b0f9b9063..48e51dca87 100644
--- a/sql/texera_ddl.sql
+++ b/sql/texera_ddl.sql
@@ -101,6 +101,7 @@ CREATE TABLE IF NOT EXISTS "user"
role user_role_enum NOT NULL DEFAULT 'INACTIVE',
comment TEXT,
account_creation_time TIMESTAMPTZ NOT NULL DEFAULT now(),
+ affiliation VARCHAR(128),
-- check that either password or google_id is not null
CONSTRAINT ck_nulltest CHECK ((password IS NOT NULL) OR (google_id IS NOT
NULL))
);
diff --git a/sql/updates/16.sql b/sql/updates/16.sql
new file mode 100644
index 0000000000..8776415c46
--- /dev/null
+++ b/sql/updates/16.sql
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+\c texera_db
+
+SET search_path TO texera_db;
+
+BEGIN;
+
+ALTER TABLE "user"
+ ADD COLUMN IF NOT EXISTS affiliation VARCHAR(128);
+
+COMMIT;
