feat: add account creation time (#3736)
## Context
In effort to improve admin dashboard and allow admins to view account
creation time, a new column `account_creation_time` is introduced to the
`user` table. This attribute is meant to store the timestamp of a user's
account creation time and is a required column of the table. Existing
user rows have been given a set time for the column.
Closes Issue #3673.
## For Developers
Please do the following steps to incorporate with new changes:
- Apply core/scripts/sql/updates/14.sql to your local postgres instance
## Summary of Feature
When a new user is added to the system (created by admin or google
login), the user's information will be logged into the database. The
`NOT NULL ON DEFAULT SET now()` statement would make sure that every
user's account creation time is logged. And admins will be able to view
every user's account creation time in the admin dashboard.
## Database Change
A new column `account_creation_time` has been added to the `user` table
and is set to `NOT NULL`. On default when a new user is inserted into
the table, the creation time for the user will be the current timestamp.
Attached below are the current & proposed schema of the `user` table.
## Codebase Change
To cooperate with the change, we added a new attribute `accountCreation`
to `user.ts` to ensure the frontend receives the account creation time
from the backend.
Multiple files have been changed as adding the not null attribute to
`user.ts` required changes to those files.
## Current Schema
<img width="276" height="277" alt="image"
src="https://github.com/user-attachments/assets/efffa9a0-ea4a-4ef1-8a28-435f59b83d6d"
/>
## Proposed Schema
<img width="270" height="303" alt="image"
src="https://github.com/user-attachments/assets/3271d5f0-b2da-4b6e-9756-15e9289d6a86"
/>
## Screenshot of Implementation
<img width="1311" height="774" alt="image"
src="https://github.com/user-attachments/assets/e20507b7-121e-40c4-bce1-b4cb0d6ac60b"
/>
---------
Co-authored-by: ali risheh <ali.risheh876@gmail.com>
diff --git a/core/amber/src/main/scala/edu/uci/ics/texera/web/ServletAwareConfigurator.scala b/core/amber/src/main/scala/edu/uci/ics/texera/web/ServletAwareConfigurator.scala
index 75accc1..0df8481 100644
--- a/core/amber/src/main/scala/edu/uci/ics/texera/web/ServletAwareConfigurator.scala
+++ b/core/amber/src/main/scala/edu/uci/ics/texera/web/ServletAwareConfigurator.scala
@@ -64,6 +64,7 @@
null,
null,
null,
+ null,
null
)
)
diff --git a/core/amber/src/main/scala/edu/uci/ics/texera/web/auth/GuestAuthFilter.scala b/core/amber/src/main/scala/edu/uci/ics/texera/web/auth/GuestAuthFilter.scala
index 8ca7438..fb5ecc6 100644
--- a/core/amber/src/main/scala/edu/uci/ics/texera/web/auth/GuestAuthFilter.scala
+++ b/core/amber/src/main/scala/edu/uci/ics/texera/web/auth/GuestAuthFilter.scala
@@ -38,7 +38,8 @@
override protected def newInstance = new GuestAuthFilter
}
- val GUEST: User = new User(null, "guest", null, null, null, null, UserRoleEnum.REGULAR, null)
+ val GUEST: User =
+ new User(null, "guest", null, null, null, null, UserRoleEnum.REGULAR, null, null)
}
@PreMatching
diff --git a/core/amber/src/main/scala/edu/uci/ics/texera/web/auth/UserAuthenticator.scala b/core/amber/src/main/scala/edu/uci/ics/texera/web/auth/UserAuthenticator.scala
index 3571940..b820a87 100644
--- a/core/amber/src/main/scala/edu/uci/ics/texera/web/auth/UserAuthenticator.scala
+++ b/core/amber/src/main/scala/edu/uci/ics/texera/web/auth/UserAuthenticator.scala
@@ -26,6 +26,7 @@
import io.dropwizard.auth.Authenticator
import org.jose4j.jwt.consumer.JwtContext
+import java.time.OffsetDateTime
import java.util.Optional
object UserAuthenticator extends Authenticator[JwtContext, SessionUser] with LazyLogging {
@@ -40,7 +41,10 @@
UserRoleEnum.valueOf(context.getJwtClaims.getClaimValue("role").asInstanceOf[String])
val googleId = context.getJwtClaims.getClaimValue("googleId").asInstanceOf[String]
val comment = context.getJwtClaims.getClaimValue("comment").asInstanceOf[String]
- val user = new User(userId, userName, email, null, googleId, null, role, comment)
+ val accountCreation =
+ context.getJwtClaims.getClaimValue("accountCreation").asInstanceOf[OffsetDateTime]
+ val user =
+ new User(userId, userName, email, null, googleId, null, role, comment, accountCreation)
Optional.of(new SessionUser(user))
} catch {
case e: Exception =>
diff --git a/core/amber/src/main/scala/edu/uci/ics/texera/web/resource/dashboard/admin/user/AdminUserResource.scala b/core/amber/src/main/scala/edu/uci/ics/texera/web/resource/dashboard/admin/user/AdminUserResource.scala
index 844bb26..4d8cde9 100644
--- a/core/amber/src/main/scala/edu/uci/ics/texera/web/resource/dashboard/admin/user/AdminUserResource.scala
+++ b/core/amber/src/main/scala/edu/uci/ics/texera/web/resource/dashboard/admin/user/AdminUserResource.scala
@@ -44,7 +44,8 @@
role: UserRoleEnum,
googleAvatar: String,
comment: String,
- lastLogin: java.time.OffsetDateTime // will be null if never logged in
+ lastLogin: java.time.OffsetDateTime, // will be null if never logged in
+ accountCreation: java.time.OffsetDateTime
)
object AdminUserResource {
@@ -76,7 +77,8 @@
USER.ROLE,
USER.GOOGLE_AVATAR,
USER.COMMENT,
- USER_LAST_ACTIVE_TIME.LAST_ACTIVE_TIME
+ USER_LAST_ACTIVE_TIME.LAST_ACTIVE_TIME,
+ USER.ACCOUNT_CREATION_TIME
)
.from(USER)
.leftJoin(USER_LAST_ACTIVE_TIME)
diff --git a/core/amber/src/test/scala/edu/uci/ics/texera/web/resource/dashboard/file/WorkflowResourceSpec.scala b/core/amber/src/test/scala/edu/uci/ics/texera/web/resource/dashboard/file/WorkflowResourceSpec.scala
index 132d04a..ae965d3 100644
--- a/core/amber/src/test/scala/edu/uci/ics/texera/web/resource/dashboard/file/WorkflowResourceSpec.scala
+++ b/core/amber/src/test/scala/edu/uci/ics/texera/web/resource/dashboard/file/WorkflowResourceSpec.scala
@@ -35,12 +35,12 @@
import edu.uci.ics.texera.web.resource.dashboard.{DashboardResource, FulltextSearchQueryUtils}
import org.jooq.Condition
import org.jooq.impl.DSL.noCondition
-
import org.scalatest.flatspec.AnyFlatSpec
import org.scalatest.{BeforeAndAfterAll, BeforeAndAfterEach}
import java.sql.Timestamp
import java.text.{ParseException, SimpleDateFormat}
+import java.time.{OffsetDateTime, ZoneOffset, Duration}
import java.util
import java.util.Collections
import java.util.concurrent.TimeUnit
@@ -51,6 +51,10 @@
with BeforeAndAfterEach
with MockTexeraDB {
+ // An example creation time to test Account Creation Time attribute
+ private val exampleCreationTime: OffsetDateTime =
+ OffsetDateTime.parse("2025-01-01T00:00:00Z")
+
private val testUser: User = {
val user = new User
user.setUid(Integer.valueOf(1))
@@ -58,6 +62,7 @@
user.setRole(UserRoleEnum.ADMIN)
user.setPassword("123")
user.setComment("test_comment")
+ user.setAccountCreationTime(exampleCreationTime)
user
}
@@ -68,6 +73,7 @@
user.setRole(UserRoleEnum.ADMIN)
user.setPassword("123")
user.setComment("test_comment2")
+ user.setAccountCreationTime(exampleCreationTime)
user
}
@@ -197,10 +203,103 @@
keywordsList
}
+ private def insertAndAssertAccountCreation(uid: Int, ts: OffsetDateTime): Unit = {
+ val userDao = new UserDao(getDSLContext.configuration())
+ val u = new User
+ u.setUid(Integer.valueOf(uid))
+ u.setName(s"tmp_user_$uid")
+ u.setRole(UserRoleEnum.REGULAR)
+ u.setPassword("pw")
+ u.setComment("tmp")
+ u.setAccountCreationTime(ts)
+ userDao.insert(u)
+
+ try {
+ val fetched = userDao.fetchOneByUid(Integer.valueOf(uid))
+ assert(fetched.getAccountCreationTime != null)
+ assert(fetched.getAccountCreationTime.isEqual(ts))
+ } finally {
+ userDao.deleteById(Integer.valueOf(uid))
+ }
+ }
+
private def assertSameWorkflow(a: Workflow, b: DashboardWorkflow): Unit = {
assert(a.getName == b.workflow.getName)
}
+ "User.accountCreationTime" should "be persisted and retrievable via UserDao" in {
+ val userDao = new UserDao(getDSLContext.configuration())
+ val u1 = userDao.fetchOneByUid(Integer.valueOf(1))
+ val u2 = userDao.fetchOneByUid(Integer.valueOf(2))
+
+ assert(u1.getAccountCreationTime != null)
+ assert(u2.getAccountCreationTime != null)
+
+ assert(u1.getAccountCreationTime.isEqual(exampleCreationTime))
+ assert(u2.getAccountCreationTime.isEqual(exampleCreationTime))
+ }
+
+ it should "remain unchanged when updating unrelated fields" in {
+ val userDao = new UserDao(getDSLContext.configuration())
+ val u1 = userDao.fetchOneByUid(Integer.valueOf(1))
+ val originalTime = u1.getAccountCreationTime
+
+ u1.setComment("updated_comment")
+ userDao.update(u1)
+
+ val test_u1 = userDao.fetchOneByUid(Integer.valueOf(1))
+ assert(test_u1.getAccountCreationTime.isEqual(originalTime))
+ }
+
+ it should "fallback to DB default when not explicitly set on insert" in {
+ // account_creation_time TIMESTAMPTZ NOT NULL DEFAULT now()
+ val userDao = new UserDao(getDSLContext.configuration())
+ // Test user 3 on top of test user 1 and 2
+ val userId = 3
+ val tmp = new User
+ tmp.setUid(Integer.valueOf(userId))
+ tmp.setName("tmp_user")
+ tmp.setRole(UserRoleEnum.REGULAR)
+ tmp.setPassword("pw")
+ tmp.setComment("tmp")
+ // Account creation time not set
+ userDao.insert(tmp)
+
+ val fetched = userDao.fetchOneByUid(Integer.valueOf(3))
+ assert(fetched.getAccountCreationTime != null)
+
+ val now = OffsetDateTime.now(ZoneOffset.UTC)
+ val diff = Duration.between(fetched.getAccountCreationTime, now).abs()
+ assert(diff.toMinutes <= 2)
+ }
+
+ // Testing with user id 4
+ it should "persist and retrieve a non-UTC offset time (ex: +09:00 JST)" in {
+ val userId = 4
+ insertAndAssertAccountCreation(
+ uid = userId,
+ ts = OffsetDateTime.parse("2020-06-15T12:34:56+09:00")
+ )
+ }
+
+ // Testing with user id 5
+ it should "persist and retrieve a leap day timestamp" in {
+ val userId = 5
+ insertAndAssertAccountCreation(
+ uid = userId,
+ ts = OffsetDateTime.parse("2024-02-29T23:59:59Z")
+ )
+ }
+
+ // Testing with user id 6
+ it should "persist and retrieve a future timestamp" in {
+ val userId = 6
+ insertAndAssertAccountCreation(
+ uid = userId,
+ ts = OffsetDateTime.parse("2100-12-31T23:59:59Z")
+ )
+ }
+
"/search API " should "be able to search for workflows in different columns in Workflow table" in {
// testWorkflow1: {name: test_name, descrption: test_description, content: test_content}
// search "test_name" or "test_description" or "test_content" should return testWorkflow1
diff --git a/core/auth/src/main/scala/edu/uci/ics/texera/auth/JwtParser.scala b/core/auth/src/main/scala/edu/uci/ics/texera/auth/JwtParser.scala
index 998deec..0981947 100644
--- a/core/auth/src/main/scala/edu/uci/ics/texera/auth/JwtParser.scala
+++ b/core/auth/src/main/scala/edu/uci/ics/texera/auth/JwtParser.scala
@@ -52,7 +52,7 @@
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)
+ val user = new User(userId, userName, email, null, googleId, null, role, null, null)
Optional.of(new SessionUser(user))
} catch {
case _: UnresolvableKeyException =>
diff --git a/core/gui/src/app/common/type/user.ts b/core/gui/src/app/common/type/user.ts
index b1157ba..01c7b8c 100644
--- a/core/gui/src/app/common/type/user.ts
+++ b/core/gui/src/app/common/type/user.ts
@@ -33,6 +33,9 @@
ADMIN = "ADMIN",
}
+export type Second = number;
+export type MilliSecond = number;
+
export interface User
extends Readonly<{
uid: number;
@@ -44,6 +47,7 @@
googleAvatar?: string;
comment: string;
lastLogin?: number;
+ accountCreation?: Second;
}> {}
export interface File
diff --git a/core/gui/src/app/dashboard/component/admin/user/admin-user.component.html b/core/gui/src/app/dashboard/component/admin/user/admin-user.component.html
index 3e856ff..8e59e0d 100644
--- a/core/gui/src/app/dashboard/component/admin/user/admin-user.component.html
+++ b/core/gui/src/app/dashboard/component/admin/user/admin-user.component.html
@@ -92,6 +92,7 @@
User Role
</th>
<th>Quota</th>
+ <th>Account Creation Time</th>
</tr>
</thead>
<nz-dropdown-menu #nameMenu="nzDropdownMenu">
@@ -293,6 +294,12 @@
nzType="dashboard"></i>
</button>
</td>
+ <td>
+ <ng-container *ngIf="getAccountCreation(user) as ac; else noAC">
+ {{ ac | date:'MM/dd/y, h:mm a' }}
+ </ng-container>
+ <ng-template #noAC>—</ng-template>
+ </td>
</tr>
</tbody>
<button
diff --git a/core/gui/src/app/dashboard/component/admin/user/admin-user.component.ts b/core/gui/src/app/dashboard/component/admin/user/admin-user.component.ts
index 7662e84..1546488 100644
--- a/core/gui/src/app/dashboard/component/admin/user/admin-user.component.ts
+++ b/core/gui/src/app/dashboard/component/admin/user/admin-user.component.ts
@@ -23,7 +23,7 @@
import { NzModalService } from "ng-zorro-antd/modal";
import { NzMessageService } from "ng-zorro-antd/message";
import { AdminUserService } from "../../../service/admin/user/admin-user.service";
-import { Role, User } from "../../../../common/type/user";
+import { Role, User, MilliSecond } from "../../../../common/type/user";
import { UserService } from "../../../../common/service/user/user.service";
import { UserQuotaComponent } from "../../user/user-quota/user-quota.component";
import { GuiConfigService } from "../../../../common/service/gui-config.service";
@@ -196,6 +196,13 @@
return Date.now() - lastMs < active_window;
}
+ getAccountCreation(user: User): MilliSecond {
+ if (!user.accountCreation) {
+ return 0;
+ }
+ return user.accountCreation * 1000;
+ }
+
public filterByRole: NzTableFilterFn<User> = (list: string[], user: User) =>
list.some(role => user.role.indexOf(role) !== -1);
}
diff --git a/core/scripts/sql/texera_ddl.sql b/core/scripts/sql/texera_ddl.sql
index 648f96c..e98f8e2 100644
--- a/core/scripts/sql/texera_ddl.sql
+++ b/core/scripts/sql/texera_ddl.sql
@@ -81,14 +81,15 @@
-- "user" table
CREATE TABLE IF NOT EXISTS "user"
(
- uid SERIAL PRIMARY KEY,
- name VARCHAR(256) NOT NULL,
- email VARCHAR(256) UNIQUE,
- password VARCHAR(256),
- google_id VARCHAR(256) UNIQUE,
- google_avatar VARCHAR(100),
- role user_role_enum NOT NULL DEFAULT 'INACTIVE',
- comment TEXT,
+ uid SERIAL PRIMARY KEY,
+ name VARCHAR(256) NOT NULL,
+ email VARCHAR(256) UNIQUE,
+ password VARCHAR(256),
+ google_id VARCHAR(256) UNIQUE,
+ google_avatar VARCHAR(100),
+ role user_role_enum NOT NULL DEFAULT 'INACTIVE',
+ comment TEXT,
+ account_creation_time TIMESTAMPTZ NOT NULL DEFAULT now(),
-- 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/core/scripts/sql/updates/14.sql b/core/scripts/sql/updates/14.sql
new file mode 100644
index 0000000..25092ef
--- /dev/null
+++ b/core/scripts/sql/updates/14.sql
@@ -0,0 +1,40 @@
+/*
+ * 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 account_creation_time TIMESTAMPTZ;
+
+WITH ts AS (
+ SELECT '2025-01-01 00:00:00-00'::timestamptz AS t
+)
+
+UPDATE "user" u
+SET account_creation_time = ts.t
+FROM ts
+WHERE u.account_creation_time IS NULL;
+
+ALTER TABLE "user"
+ ALTER COLUMN account_creation_time SET NOT NULL,
+ ALTER COLUMN account_creation_time SET DEFAULT now();
+
+COMMIT;
\ No newline at end of file
diff --git a/deployment/k8s/texera-helmchart/files/texera_ddl.sql b/deployment/k8s/texera-helmchart/files/texera_ddl.sql
index 2593368..bece11a 100644
--- a/deployment/k8s/texera-helmchart/files/texera_ddl.sql
+++ b/deployment/k8s/texera-helmchart/files/texera_ddl.sql
@@ -116,14 +116,15 @@
-- "user" table
CREATE TABLE IF NOT EXISTS "user"
(
- uid SERIAL PRIMARY KEY,
- name VARCHAR(256) NOT NULL,
- email VARCHAR(256) UNIQUE,
- password VARCHAR(256),
- google_id VARCHAR(256) UNIQUE,
- google_avatar VARCHAR(100),
- role user_role_enum NOT NULL DEFAULT 'INACTIVE',
- comment TEXT,
+ uid SERIAL PRIMARY KEY,
+ name VARCHAR(256) NOT NULL,
+ email VARCHAR(256) UNIQUE,
+ password VARCHAR(256),
+ google_id VARCHAR(256) UNIQUE,
+ google_avatar VARCHAR(100),
+ role user_role_enum NOT NULL DEFAULT 'INACTIVE',
+ comment TEXT,
+ account_creation_time TIMESTAMPTZ NOT NULL DEFAULT now(),
-- check that either password or google_id is not null
CONSTRAINT ck_nulltest CHECK ((password IS NOT NULL) OR (google_id IS NOT NULL))
);
