This is an automated email from the ASF dual-hosted git repository.

lahirujayathilake pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/airavata-custos.git

commit c95e6696d1752e332cc65f48f6cec4870fcb6b1a
Author: lahiruj <[email protected]>
AuthorDate: Mon Oct 13 19:42:39 2025 -0400

    included user account services and model classes
---
 .../amie/handler/RequestAccountCreateHandler.java  |  74 +++++++----
 .../handler/RequestAccountInactivateHandler.java   |  30 +++--
 .../amie/handler/RequestProjectCreateHandler.java  | 142 +++++++++++---------
 .../handler/RequestProjectInactivateHandler.java   |  47 +++----
 .../handler/RequestProjectReactivateHandler.java   |  47 ++++---
 .../amie/handler/RequestUserModifyHandler.java     |   2 +-
 .../custos/amie/model/ClusterAccountEntity.java    |  16 ++-
 ...lusterAccountEntity.java => ProjectEntity.java} |  62 +++++----
 ...untEntity.java => ProjectMembershipEntity.java} |  64 +++++----
 .../amie/repo/ProjectMembershipRepository.java     |  39 ++++++
 .../apache/custos/amie/repo/ProjectRepository.java |  28 ++++
 .../apache/custos/amie/service/PersonService.java  | 148 +++++++--------------
 .../amie/service/ProjectMembershipService.java     | 139 +++++++++++++++++++
 .../apache/custos/amie/service/ProjectService.java |  94 +++++++++++++
 .../custos/amie/service/UserAccountService.java    |  82 ++++++++++++
 .../db/migration/V1__initial_migration.sql         | 106 ++++++++++++---
 16 files changed, 770 insertions(+), 350 deletions(-)

diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestAccountCreateHandler.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestAccountCreateHandler.java
index 2e49a8602..be44aab0b 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestAccountCreateHandler.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestAccountCreateHandler.java
@@ -20,8 +20,13 @@ package org.apache.custos.amie.handler;
 
 import com.fasterxml.jackson.databind.JsonNode;
 import org.apache.custos.amie.client.AmieClient;
+import org.apache.custos.amie.model.ClusterAccountEntity;
 import org.apache.custos.amie.model.PacketEntity;
+import org.apache.custos.amie.model.PersonEntity;
 import org.apache.custos.amie.service.PersonService;
+import org.apache.custos.amie.service.ProjectMembershipService;
+import org.apache.custos.amie.service.ProjectService;
+import org.apache.custos.amie.service.UserAccountService;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.stereotype.Component;
@@ -29,7 +34,6 @@ import org.springframework.util.Assert;
 
 import java.util.HashMap;
 import java.util.Map;
-import java.util.UUID;
 
 /**
  * Handles the 'request_account_create' (RAC) AMIE packet.
@@ -45,10 +49,18 @@ public class RequestAccountCreateHandler implements 
PacketHandler {
 
     private final AmieClient amieClient;
     private final PersonService personService;
+    private final UserAccountService userAccountService;
+    private final ProjectService projectService;
+    private final ProjectMembershipService membershipService;
 
-    public RequestAccountCreateHandler(AmieClient amieClient, PersonService 
personService) {
+    public RequestAccountCreateHandler(AmieClient amieClient, PersonService 
personService,
+                                       UserAccountService userAccountService, 
ProjectService projectService,
+                                       ProjectMembershipService 
membershipService) {
         this.amieClient = amieClient;
         this.personService = personService;
+        this.userAccountService = userAccountService;
+        this.projectService = projectService;
+        this.membershipService = membershipService;
     }
 
     @Override
@@ -63,40 +75,44 @@ public class RequestAccountCreateHandler implements 
PacketHandler {
         JsonNode body = packetJson.path("body");
         String projectId = body.path("ProjectID").asText();
         String grantNumber = body.path("GrantNumber").asText();
-        String userFirstName = body.path("UserFirstName").asText();
-        String userLastName = body.path("UserLastName").asText();
-        String userOrgCode = body.path("UserOrgCode").asText();
+        String userGlobalId = body.path("UserGlobalID").asText();
 
         Assert.hasText(projectId, "'ProjectID' (the local project ID) must not 
be empty.");
-        Assert.hasText(userFirstName, "'UserFirstName' must not be empty.");
-        Assert.hasText(userLastName, "'UserLastName' must not be empty.");
-        LOGGER.info("Packet validated successfully for user [{} {}] on project 
[{}].", userFirstName, userLastName, projectId);
-
-        // TODO Replace with external source of truth (e.g., COmanage) lookup 
for PersonID and username
-        String proposedPersonId = UUID.randomUUID().toString();
-        String proposedUsername = (userFirstName.trim().charAt(0) + 
userLastName.trim().replace(" ", "-")).toLowerCase();
-        var provision = personService.createIfAbsentFromPacket(body, 
proposedPersonId, proposedUsername);
-        String localUserPersonId = provision.getLocalPersonId();
-        String localUsername = provision.getUsername();
-        LOGGER.info("Ensured local person [{}] and cluster account username 
[{}] exist.", localUserPersonId, localUsername);
-
-        // Build and send the 'notify_account_create' reply
-        Map<String, Object> replyBody = new HashMap<>();
-        Map<String, Object> bodyContent = new HashMap<>();
-        bodyContent.put("UserOrgCode", userOrgCode);
-        bodyContent.put("ProjectID", projectId);
-        bodyContent.put("UserPersonID", localUserPersonId);
+        Assert.hasText(grantNumber, "'GrantNumber' must not be empty.");
+        Assert.hasText(userGlobalId, "'UserGlobalID' must not be empty.");
+        LOGGER.info("Packet validated for UserGlobalID [{}] on project [{}].", 
userGlobalId, projectId);
 
-        // User login name (Unix username) on the actual resource
-        bodyContent.put("UserRemoteSiteLogin", localUsername);
+        PersonEntity person = personService.findOrCreatePersonFromPacket(body);
+        LOGGER.info("Ensured person record exists with local ID [{}].", 
person.getId());
 
-        bodyContent.put("GrantNumber", grantNumber);
+        ClusterAccountEntity clusterAccount = 
userAccountService.provisionClusterAccount(person);
+        LOGGER.info("Provisioned new cluster account [{}] with username 
[{}].", clusterAccount.getId(), clusterAccount.getUsername());
 
-        replyBody.put("type", "notify_account_create");
-        replyBody.put("body", bodyContent);
+        projectService.createOrFindProject(projectId, grantNumber);
+        LOGGER.info("Ensured project [{}] exists.", projectId);
 
-        amieClient.replyToPacket(packetEntity.getAmieId(), replyBody);
+        membershipService.createMembership(projectId, clusterAccount.getId(), 
"USER");
+        LOGGER.info("Created 'USER' membership for cluster account [{}] on 
project [{}].", clusterAccount.getId(), projectId);
 
+        sendSuccessReply(packetEntity.getAmieId(), body, person.getId(), 
clusterAccount.getUsername());
         LOGGER.info("Successfully completed 'request_account_create' handler 
and sent reply for packet amie_id [{}].", packetEntity.getAmieId());
     }
+
+    private void sendSuccessReply(long packetRecId, JsonNode originalBody, 
String localPersonId, String localUsername) {
+        Map<String, Object> reply = new HashMap<>();
+        Map<String, Object> bodyContent = new HashMap<>();
+
+        bodyContent.put("ProjectID", originalBody.path("ProjectID").asText());
+        bodyContent.put("GrantNumber", 
originalBody.path("GrantNumber").asText());
+        bodyContent.put("UserPersonID", localPersonId);
+        bodyContent.put("UserRemoteSiteLogin", localUsername);
+
+        bodyContent.put("UserOrgCode", 
originalBody.path("UserOrgCode").asText(null));
+        bodyContent.put("ResourceList", originalBody.path("ResourceList"));
+
+        reply.put("type", "notify_account_create");
+        reply.put("body", bodyContent);
+
+        amieClient.replyToPacket(packetRecId, reply);
+    }
 }
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestAccountInactivateHandler.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestAccountInactivateHandler.java
index 0288706c7..eadfbda24 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestAccountInactivateHandler.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestAccountInactivateHandler.java
@@ -21,6 +21,7 @@ package org.apache.custos.amie.handler;
 import com.fasterxml.jackson.databind.JsonNode;
 import org.apache.custos.amie.client.AmieClient;
 import org.apache.custos.amie.model.PacketEntity;
+import org.apache.custos.amie.service.ProjectMembershipService;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.stereotype.Component;
@@ -43,9 +44,11 @@ public class RequestAccountInactivateHandler implements 
PacketHandler {
     private static final Logger LOGGER = 
LoggerFactory.getLogger(RequestAccountInactivateHandler.class);
 
     private final AmieClient amieClient;
+    private final ProjectMembershipService membershipService;
 
-    public RequestAccountInactivateHandler(AmieClient amieClient) {
+    public RequestAccountInactivateHandler(AmieClient amieClient, 
ProjectMembershipService membershipService) {
         this.amieClient = amieClient;
+        this.membershipService = membershipService;
     }
 
     @Override
@@ -65,18 +68,19 @@ public class RequestAccountInactivateHandler implements 
PacketHandler {
         Assert.hasText(personId, "'PersonID' must not be empty.");
         LOGGER.info("Packet validated. Inactivating account for user [{}] on 
project [{}].", personId, projectId);
 
-        // TODO - perform the business logic
-        //  - find the user's project record and mark it as inactive
-        //  - remove the user from project's Slurm account
-        LOGGER.info("Simulating business logic: Removing user [{}] from 
project [{}]'s allocation.", personId, projectId);
+        membershipService.inactivateMembershipsByPersonAndProject(projectId, 
personId);
 
+        sendSuccessReply(packetEntity.getAmieId(), body);
 
-        // Send the 'notify_account_inactivate' reply.
-        Map<String, Object> replyBody = new HashMap<>();
+        LOGGER.info("Successfully completed 'request_account_inactivate' 
handler and sent reply for packet amie_id [{}].", packetEntity.getAmieId());
+    }
+
+    private void sendSuccessReply(long packetRecId, JsonNode body) {
+        Map<String, Object> reply = new HashMap<>();
         Map<String, Object> bodyContent = new HashMap<>();
 
-        bodyContent.put("ProjectID", projectId);
-        bodyContent.put("PersonID", personId);
+        bodyContent.put("ProjectID", body.path("ProjectID").asText());
+        bodyContent.put("PersonID", body.path("PersonID").asText());
 
         List<String> resourceList = new ArrayList<>();
         JsonNode rlNode = body.path("ResourceList");
@@ -85,11 +89,9 @@ public class RequestAccountInactivateHandler implements 
PacketHandler {
         }
         bodyContent.put("ResourceList", resourceList);
 
-        replyBody.put("type", "notify_account_inactivate");
-        replyBody.put("body", bodyContent);
+        reply.put("type", "notify_account_inactivate");
+        reply.put("body", bodyContent);
 
-        amieClient.replyToPacket(packetEntity.getAmieId(), replyBody);
-
-        LOGGER.info("Successfully completed 'request_account_inactivate' 
handler and sent reply for packet amie_id [{}].", packetEntity.getAmieId());
+        amieClient.replyToPacket(packetRecId, reply);
     }
 }
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectCreateHandler.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectCreateHandler.java
index 10154cde4..66d109b13 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectCreateHandler.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectCreateHandler.java
@@ -19,17 +19,23 @@
 package org.apache.custos.amie.handler;
 
 import com.fasterxml.jackson.databind.JsonNode;
+import com.fasterxml.jackson.databind.node.ObjectNode;
 import org.apache.custos.amie.client.AmieClient;
+import org.apache.custos.amie.model.ClusterAccountEntity;
 import org.apache.custos.amie.model.PacketEntity;
+import org.apache.custos.amie.model.PersonEntity;
+import org.apache.custos.amie.model.ProjectEntity;
+import org.apache.custos.amie.service.PersonService;
+import org.apache.custos.amie.service.ProjectMembershipService;
+import org.apache.custos.amie.service.ProjectService;
+import org.apache.custos.amie.service.UserAccountService;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.stereotype.Component;
+import org.springframework.util.Assert;
 
-import java.util.ArrayList;
 import java.util.HashMap;
-import java.util.List;
 import java.util.Map;
-import java.util.UUID;
 
 /**
  * Handles 'request_project_create' (RPC) by replying with 
'notify_project_create' (NPC).
@@ -40,13 +46,28 @@ public class RequestProjectCreateHandler implements 
PacketHandler {
     private static final Logger LOGGER = 
LoggerFactory.getLogger(RequestProjectCreateHandler.class);
 
     private final AmieClient amieClient;
+    private final PersonService personService;
+    private final UserAccountService userAccountService;
+    private final ProjectService projectService;
+    private final ProjectMembershipService membershipService;
 
-    public RequestProjectCreateHandler(AmieClient amieClient) {
+    public RequestProjectCreateHandler(AmieClient amieClient, PersonService 
personService, UserAccountService userAccountService,
+                                       ProjectService projectService, 
ProjectMembershipService membershipService) {
         this.amieClient = amieClient;
+        this.personService = personService;
+        this.userAccountService = userAccountService;
+        this.projectService = projectService;
+        this.membershipService = membershipService;
     }
 
     @Override
-    public void handle(JsonNode packetJson, PacketEntity packetEntity) {
+    public String supportsType() {
+        return "request_project_create";
+    }
+
+    @Override
+    public void handle(JsonNode packetJson, PacketEntity packetEntity) throws 
Exception {
+        LOGGER.info("Starting 'request_project_create' handler for packet 
amie_id [{}].", packetEntity.getAmieId());
 
         // TODO - refactor the sanity checks into Packet Router (if all the 
packets follow the same style)
         if (packetJson == null) {
@@ -60,73 +81,64 @@ public class RequestProjectCreateHandler implements 
PacketHandler {
             throw new IllegalArgumentException("packetJson.body is missing");
         }
 
-        String grantNumber = body.path("GrantNumber").asText("");
-        String projectTitle = body.path("ProjectTitle").asText("");
-        String startDate = body.path("StartDate").asText("");
-        String endDate = body.path("EndDate").asText("");
-        String recordId = body.path("RecordID").asText("");
-        String piOrgCode = body.path("PiOrgCode").asText("");
-        String pfosNumber = body.path("PfosNumber").asText("");
-        String piGlobalID = body.path("PiGlobalID").asText("");
-
-        // TODO handle for failures
-        String piFirstName = body.path("PiFirstName").asText("");
-        String piLastName = body.path("PiLastName").asText("");
-
-        // ResourceList will have only one resource (According to AMIE 
documentation)
-        List<Object> resourceList = new ArrayList<>();
-        JsonNode rl = body.path("ResourceList");
-        if (rl.isArray()) {
-            rl.forEach(node -> resourceList.add(node.asText()));
-        } else {
-            LOGGER.warn("No resource list found for amie_id [{}]", 
packetEntity.getAmieId());
-        }
+        String grantNumber = body.path("GrantNumber").asText();
+        String piGlobalId = body.path("PiGlobalID").asText();
+        String piFirstName = body.path("PiFirstName").asText();
+        String piLastName = body.path("PiLastName").asText();
 
-        // TODO - Derive a local project identifier
-        String localProjectId = deriveLocalProjectId(grantNumber);
-
-        // TODO - Derive the following two from COmange registry
-        String piPersonID = UUID.randomUUID().toString();
-        String piRemoteSiteLogin = (piFirstName.charAt(0) + 
piLastName).toLowerCase();
-
-        // Build the NPC reply body
-        Map<String, Object> replyBody = new HashMap<>();
-        Map<String, Object> npc = new HashMap<>();
-        npc.put("GrantNumber", grantNumber);
-        npc.put("ProjectTitle", projectTitle);
-        npc.put("StartDate", startDate);
-        npc.put("EndDate", endDate);
-        npc.put("RecordID", recordId);
-        npc.put("ProjectID", localProjectId);
-        npc.put("PfosNumber", pfosNumber);
-
-        npc.put("PiOrgCode", piOrgCode);
-        npc.put("PiGlobalID", piGlobalID);
-        npc.put("PiPersonID", piPersonID);
-        npc.put("PiOrgCode", piOrgCode);
-        npc.put("PiRemoteSiteLogin", piRemoteSiteLogin);
-
-        if (!resourceList.isEmpty()) {
-            npc.put("ResourceList", resourceList);
-        }
+        Assert.hasText(grantNumber, "'GrantNumber' must not be empty.");
+        Assert.hasText(piGlobalId, "'PiGlobalID' must not be empty.");
+        Assert.hasText(piFirstName, "'PiFirstName' must not be empty.");
+        Assert.hasText(piLastName, "'PiLastName' must not be empty.");
+        LOGGER.info("Packet validated for GrantNumber [{}] and PI Global ID 
[{}].", grantNumber, piGlobalId);
+
+        // Find or Create Person record for the PI
+        ObjectNode piAsUserNode = createPiAsUserNode(body);
+        PersonEntity piPerson = 
personService.findOrCreatePersonFromPacket(piAsUserNode);
+        LOGGER.info("PI person record exists with local ID [{}].", 
piPerson.getId());
 
-        replyBody.put("type", "notify_project_create");
-        replyBody.put("body", npc);
+        ClusterAccountEntity piClusterAccount = 
userAccountService.provisionClusterAccount(piPerson);
+        LOGGER.info("Provisioned cluster account for PI [{}] with username 
[{}].", piPerson.getId(), piClusterAccount.getUsername());
 
-        long packetRecId = packetEntity.getAmieId();
-        amieClient.replyToPacket(packetRecId, replyBody);
+        String localProjectId = "PRJ-" + grantNumber;
+        ProjectEntity project = 
projectService.createOrFindProject(localProjectId, grantNumber);
+        LOGGER.info("Project [{}] exists.", project.getId());
 
-        LOGGER.info("NPC sent for RPC packet_rec_id={}, LocalProjectID={}", 
packetRecId, localProjectId);
+        membershipService.createMembership(project.getId(), 
piClusterAccount.getId(), "PI");
+        LOGGER.info("Created 'PI' membership for cluster account [{}] on 
project [{}].", piClusterAccount.getId(), project.getId());
+
+        sendSuccessReply(packetEntity.getAmieId(), body, project.getId(), 
piPerson.getId(), piClusterAccount.getUsername());
+        LOGGER.info("Successfully completed 'request_project_create' handler 
and sent reply for packet amie_id [{}].", packetEntity.getAmieId());
     }
 
-    @Override
-    public String supportsType() {
-        return "request_project_create";
+    private void sendSuccessReply(long packetRecId, JsonNode originalBody, 
String localProjectId, String localPiPersonId, String localPiUsername) {
+        Map<String, Object> reply = new HashMap<>();
+        Map<String, Object> npcBody = new HashMap<>();
+
+        npcBody.put("ProjectID", localProjectId);
+        npcBody.put("GrantNumber", originalBody.path("GrantNumber").asText());
+        npcBody.put("PiPersonID", localPiPersonId);
+        npcBody.put("PiRemoteSiteLogin", localPiUsername);
+        npcBody.put("PiGlobalID", originalBody.path("PiGlobalID").asText());
+        npcBody.put("ProjectTitle", 
originalBody.path("ProjectTitle").asText(null));
+        npcBody.put("ResourceList", originalBody.path("ResourceList"));
+
+        reply.put("type", "notify_project_create");
+        reply.put("body", npcBody);
+
+        amieClient.replyToPacket(packetRecId, reply);
     }
 
-    private String deriveLocalProjectId(String grantNumber) {
-        // TODO - need to keep a DB mapping between LocalProjectID and 
GrantNumber
-        String gn = (grantNumber == null || grantNumber.isBlank()) ? "UNKNOWN" 
: grantNumber.trim();
-        return "PRJ-" + gn;
+    private ObjectNode createPiAsUserNode(JsonNode rpcBody) {
+        ObjectNode userNode = rpcBody.deepCopy();
+        userNode.put("UserGlobalID", rpcBody.path("PiGlobalID").asText());
+        userNode.put("UserFirstName", rpcBody.path("PiFirstName").asText());
+        userNode.put("UserLastName", rpcBody.path("PiLastName").asText());
+        userNode.put("UserEmail", rpcBody.path("PiEmail").asText());
+        userNode.put("UserOrganization", 
rpcBody.path("PiOrganization").asText());
+        userNode.put("UserOrgCode", rpcBody.path("PiOrgCode").asText());
+        userNode.put("NsfStatusCode", rpcBody.path("NsfStatusCode").asText());
+        userNode.set("UserDnList", rpcBody.path("PiDnList"));
+        return userNode;
     }
 }
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectInactivateHandler.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectInactivateHandler.java
index b49255210..38b980856 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectInactivateHandler.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectInactivateHandler.java
@@ -21,14 +21,14 @@ package org.apache.custos.amie.handler;
 import com.fasterxml.jackson.databind.JsonNode;
 import org.apache.custos.amie.client.AmieClient;
 import org.apache.custos.amie.model.PacketEntity;
+import org.apache.custos.amie.service.ProjectMembershipService;
+import org.apache.custos.amie.service.ProjectService;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.stereotype.Component;
 import org.springframework.util.Assert;
 
-import java.util.ArrayList;
 import java.util.HashMap;
-import java.util.List;
 import java.util.Map;
 
 /**
@@ -40,9 +40,13 @@ public class RequestProjectInactivateHandler implements 
PacketHandler {
     private static final Logger LOGGER = 
LoggerFactory.getLogger(RequestProjectInactivateHandler.class);
 
     private final AmieClient amieClient;
+    private final ProjectService projectService;
+    private final ProjectMembershipService membershipService;
 
-    public RequestProjectInactivateHandler(AmieClient amieClient) {
+    public RequestProjectInactivateHandler(AmieClient amieClient, 
ProjectService projectService, ProjectMembershipService membershipService) {
         this.amieClient = amieClient;
+        this.projectService = projectService;
+        this.membershipService = membershipService;
     }
 
     @Override
@@ -56,37 +60,30 @@ public class RequestProjectInactivateHandler implements 
PacketHandler {
 
         JsonNode body = packetJson.path("body");
         String projectId = body.path("ProjectID").asText();
-        String personId = body.path("PersonID").asText();
 
         Assert.hasText(projectId, "'ProjectID' must not be empty.");
-        Assert.hasText(personId, "'PersonID' must not be empty.");
         LOGGER.info("Packet validated. ProjectID to inactivate: [{}].", 
projectId);
 
-        // TODO - perform the business logic
-        //  - find ALL user accounts associated with this project and 
inactivate each of them --> slurm inactivate
-        LOGGER.info("Simulating business logic: Marking project [{}] and all 
its associated user accounts as inactive.", projectId);
+        projectService.inactivateProject(projectId);
+        membershipService.inactivateAllMembershipsForProject(projectId);
+        LOGGER.info("Inactivated project [{}] and all associated 
memberships.", projectId);
 
+        sendSuccessReply(packetEntity.getAmieId(), body);
 
-        // Construct and send the 'notify_project_inactivate' reply
-        Map<String, Object> replyBody = new HashMap<>();
-        Map<String, Object> bodyContent = new HashMap<>();
-
-        bodyContent.put("ProjectID", projectId);
-        bodyContent.put("PersonID", personId);
+        LOGGER.info("Successfully completed 'request_project_inactivate' 
handler and sent reply for packet amie_id [{}].", packetEntity.getAmieId());
+    }
 
-        List<String> resourceList = new ArrayList<>();
-        JsonNode rlNode = body.path("ResourceList");
-        if (rlNode.isArray()) {
-            rlNode.forEach(node -> resourceList.add(node.asText()));
-        }
-        bodyContent.put("ResourceList", resourceList);
+    private void sendSuccessReply(long packetRecId, JsonNode originalBody) {
+        Map<String, Object> reply = new HashMap<>();
+        Map<String, Object> bodyContent = new HashMap<>();
 
-        replyBody.put("type", "notify_project_inactivate");
-        replyBody.put("body", bodyContent);
+        bodyContent.put("ProjectID", originalBody.path("ProjectID").asText());
+        bodyContent.put("PersonID", 
originalBody.path("PersonID").asText(null));
+        bodyContent.put("ResourceList", originalBody.path("ResourceList"));
 
-        amieClient.replyToPacket(packetEntity.getAmieId(), replyBody);
+        reply.put("type", "notify_project_inactivate");
+        reply.put("body", bodyContent);
 
-        LOGGER.info("Successfully completed 'request_project_inactivate' 
handler and sent reply for packet amie_id [{}].", packetEntity.getAmieId());
+        amieClient.replyToPacket(packetRecId, reply);
     }
 }
-
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectReactivateHandler.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectReactivateHandler.java
index 325d2975b..0acd38992 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectReactivateHandler.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestProjectReactivateHandler.java
@@ -21,14 +21,14 @@ package org.apache.custos.amie.handler;
 import com.fasterxml.jackson.databind.JsonNode;
 import org.apache.custos.amie.client.AmieClient;
 import org.apache.custos.amie.model.PacketEntity;
+import org.apache.custos.amie.service.ProjectMembershipService;
+import org.apache.custos.amie.service.ProjectService;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.stereotype.Component;
 import org.springframework.util.Assert;
 
-import java.util.ArrayList;
 import java.util.HashMap;
-import java.util.List;
 import java.util.Map;
 
 /**
@@ -43,9 +43,13 @@ public class RequestProjectReactivateHandler implements 
PacketHandler {
     private static final Logger LOGGER = 
LoggerFactory.getLogger(RequestProjectReactivateHandler.class);
 
     private final AmieClient amieClient;
+    private final ProjectService projectService;
+    private final ProjectMembershipService membershipService;
 
-    public RequestProjectReactivateHandler(AmieClient amieClient) {
+    public RequestProjectReactivateHandler(AmieClient amieClient, 
ProjectService projectService, ProjectMembershipService membershipService) {
         this.amieClient = amieClient;
+        this.projectService = projectService;
+        this.membershipService = membershipService;
     }
 
     @Override
@@ -53,42 +57,37 @@ public class RequestProjectReactivateHandler implements 
PacketHandler {
         return "request_project_reactivate";
     }
 
+
     @Override
     public void handle(JsonNode packetJson, PacketEntity packetEntity) {
         LOGGER.info("Starting 'request_project_reactivate' handler for packet 
amie_id [{}].", packetEntity.getAmieId());
 
         JsonNode body = packetJson.path("body");
         String projectId = body.path("ProjectID").asText();
-        String personId = body.path("PersonID").asText();
 
         Assert.hasText(projectId, "'ProjectID' must not be empty.");
-        Assert.hasText(personId, "'PersonID' must not be empty.");
         LOGGER.info("Packet validated. ProjectID to reactivate: [{}].", 
projectId);
 
-        // TODO - perform the business logic
-        //  - find the project by its local ID and activate it
-        //  - find the PI's account (only this account) associated with this 
project and reactivate it
-        LOGGER.info("Simulating business logic: Marking project [{}] as active 
and reactivating the PI's ({}) account link.", projectId, personId);
+        projectService.reactivateProject(projectId);
+        membershipService.reactivatePiMembership(projectId);
+        LOGGER.info("Reactivated project [{}] and PI membership(s).", 
projectId);
 
-        // Send the 'notify_project_reactivate' reply.
-        Map<String, Object> replyBody = new HashMap<>();
-        Map<String, Object> bodyContent = new HashMap<>();
+        sendSuccessReply(packetEntity.getAmieId(), body);
 
-        bodyContent.put("ProjectID", projectId);
-        bodyContent.put("PersonID", personId);
+        LOGGER.info("Successfully completed 'request_project_reactivate' 
handler and sent reply for packet amie_id [{}].", packetEntity.getAmieId());
+    }
 
-        List<String> resourceList = new ArrayList<>();
-        JsonNode rlNode = body.path("ResourceList");
-        if (rlNode.isArray()) {
-            rlNode.forEach(node -> resourceList.add(node.asText()));
-        }
-        bodyContent.put("ResourceList", resourceList);
+    private void sendSuccessReply(long packetRecId, JsonNode originalBody) {
+        Map<String, Object> reply = new HashMap<>();
+        Map<String, Object> bodyContent = new HashMap<>();
 
-        replyBody.put("type", "notify_project_reactivate");
-        replyBody.put("body", bodyContent);
+        bodyContent.put("ProjectID", originalBody.path("ProjectID").asText());
+        bodyContent.put("PersonID", 
originalBody.path("PersonID").asText(null));
+        bodyContent.put("ResourceList", originalBody.path("ResourceList"));
 
-        amieClient.replyToPacket(packetEntity.getAmieId(), replyBody);
+        reply.put("type", "notify_project_reactivate");
+        reply.put("body", bodyContent);
 
-        LOGGER.info("Successfully completed 'request_project_reactivate' 
handler and sent reply for packet amie_id [{}].", packetEntity.getAmieId());
+        amieClient.replyToPacket(packetRecId, reply);
     }
 }
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestUserModifyHandler.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestUserModifyHandler.java
index 62c9db6a6..05f32ddbc 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestUserModifyHandler.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/handler/RequestUserModifyHandler.java
@@ -56,7 +56,7 @@ public class RequestUserModifyHandler implements 
PacketHandler {
         LOGGER.info("Starting 'request_user_modify' handler for packet amie_id 
[{}].", packetEntity.getAmieId());
 
         JsonNode body = packetJson.path("body");
-        String actionType = body.path("ActionType").asText("delete");
+        String actionType = body.path("ActionType").asText(null);
         Assert.hasText(actionType, "'ActionType' must not be empty 
(replace|delete).");
 
         switch (actionType.toLowerCase()) {
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
index 064b33e27..4d7b834dc 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
@@ -18,6 +18,7 @@
  */
 package org.apache.custos.amie.model;
 
+import jakarta.persistence.CascadeType;
 import jakarta.persistence.Column;
 import jakarta.persistence.Entity;
 import jakarta.persistence.FetchType;
@@ -26,11 +27,14 @@ import jakarta.persistence.GenerationType;
 import jakarta.persistence.Id;
 import jakarta.persistence.JoinColumn;
 import jakarta.persistence.ManyToOne;
+import jakarta.persistence.OneToMany;
 import jakarta.persistence.Table;
 import org.hibernate.annotations.CreationTimestamp;
 import org.hibernate.annotations.UpdateTimestamp;
 
 import java.time.Instant;
+import java.util.ArrayList;
+import java.util.List;
 
 /**
  * Maps to the 'cluster_accounts' table. Stores a provisioned username on the 
cluster.
@@ -51,8 +55,8 @@ public class ClusterAccountEntity {
     @Column(name = "username", nullable = false, unique = true)
     private String username;
 
-    @Column(name = "is_active", nullable = false)
-    private boolean isActive = true;
+    @OneToMany(mappedBy = "clusterAccount", cascade = CascadeType.ALL, 
orphanRemoval = true, fetch = FetchType.LAZY)
+    private List<ProjectMembershipEntity> projectMemberships = new 
ArrayList<>();
 
     @CreationTimestamp
     @Column(name = "created_at", nullable = false, updatable = false)
@@ -86,12 +90,12 @@ public class ClusterAccountEntity {
         this.username = username;
     }
 
-    public boolean isActive() {
-        return isActive;
+    public List<ProjectMembershipEntity> getProjectMemberships() {
+        return projectMemberships;
     }
 
-    public void setActive(boolean active) {
-        isActive = active;
+    public void setProjectMemberships(List<ProjectMembershipEntity> 
projectMemberships) {
+        this.projectMemberships = projectMemberships;
     }
 
     public Instant getCreatedAt() {
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
 b/amie-decoder/src/main/java/org/apache/custos/amie/model/ProjectEntity.java
similarity index 65%
copy from 
amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
copy to 
amie-decoder/src/main/java/org/apache/custos/amie/model/ProjectEntity.java
index 064b33e27..9d7a60723 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
+++ b/amie-decoder/src/main/java/org/apache/custos/amie/model/ProjectEntity.java
@@ -7,49 +7,44 @@
  * "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
+ *   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.
+ * specific language governing permissions and limitations
+ * under the License.
  */
 package org.apache.custos.amie.model;
 
+import jakarta.persistence.CascadeType;
 import jakarta.persistence.Column;
 import jakarta.persistence.Entity;
 import jakarta.persistence.FetchType;
-import jakarta.persistence.GeneratedValue;
-import jakarta.persistence.GenerationType;
 import jakarta.persistence.Id;
-import jakarta.persistence.JoinColumn;
-import jakarta.persistence.ManyToOne;
+import jakarta.persistence.OneToMany;
 import jakarta.persistence.Table;
 import org.hibernate.annotations.CreationTimestamp;
 import org.hibernate.annotations.UpdateTimestamp;
 
 import java.time.Instant;
+import java.util.ArrayList;
+import java.util.List;
 
 /**
- * Maps to the 'cluster_accounts' table. Stores a provisioned username on the 
cluster.
+ * Maps to the 'projects' table. Stores unique information about each 
project/allocation.
  */
 @Entity
-@Table(name = "cluster_accounts")
-public class ClusterAccountEntity {
+@Table(name = "projects")
+public class ProjectEntity {
 
     @Id
-    @GeneratedValue(strategy = GenerationType.UUID)
     @Column(name = "id")
     private String id;
 
-    @ManyToOne(fetch = FetchType.LAZY, optional = false)
-    @JoinColumn(name = "person_id", nullable = false)
-    private PersonEntity person;
-
-    @Column(name = "username", nullable = false, unique = true)
-    private String username;
+    @Column(name = "grant_number", nullable = false)
+    private String grantNumber;
 
     @Column(name = "is_active", nullable = false)
     private boolean isActive = true;
@@ -62,6 +57,9 @@ public class ClusterAccountEntity {
     @Column(name = "updated_at", nullable = false)
     private Instant updatedAt;
 
+    @OneToMany(mappedBy = "project", cascade = CascadeType.ALL, orphanRemoval 
= true, fetch = FetchType.LAZY)
+    private List<ProjectMembershipEntity> memberships = new ArrayList<>();
+
     public String getId() {
         return id;
     }
@@ -70,20 +68,12 @@ public class ClusterAccountEntity {
         this.id = id;
     }
 
-    public PersonEntity getPerson() {
-        return person;
-    }
-
-    public void setPerson(PersonEntity person) {
-        this.person = person;
-    }
-
-    public String getUsername() {
-        return username;
+    public String getGrantNumber() {
+        return grantNumber;
     }
 
-    public void setUsername(String username) {
-        this.username = username;
+    public void setGrantNumber(String grantNumber) {
+        this.grantNumber = grantNumber;
     }
 
     public boolean isActive() {
@@ -110,18 +100,26 @@ public class ClusterAccountEntity {
         this.updatedAt = updatedAt;
     }
 
+    public List<ProjectMembershipEntity> getMemberships() {
+        return memberships;
+    }
+
+    public void setMemberships(List<ProjectMembershipEntity> memberships) {
+        this.memberships = memberships;
+    }
+
     @Override
     public boolean equals(Object o) {
         if (o == null || getClass() != o.getClass()) return false;
 
-        ClusterAccountEntity that = (ClusterAccountEntity) o;
-        return id.equals(that.id) && username.equals(that.username);
+        ProjectEntity that = (ProjectEntity) o;
+        return id.equals(that.id) && grantNumber.equals(that.grantNumber);
     }
 
     @Override
     public int hashCode() {
         int result = id.hashCode();
-        result = 31 * result + username.hashCode();
+        result = 31 * result + grantNumber.hashCode();
         return result;
     }
 }
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/model/ProjectMembershipEntity.java
similarity index 65%
copy from 
amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
copy to 
amie-decoder/src/main/java/org/apache/custos/amie/model/ProjectMembershipEntity.java
index 064b33e27..7f71896c9 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/model/ClusterAccountEntity.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/model/ProjectMembershipEntity.java
@@ -7,14 +7,14 @@
  * "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
+ *   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.
+ * specific language governing permissions and limitations
+ * under the License.
  */
 package org.apache.custos.amie.model;
 
@@ -27,17 +27,18 @@ import jakarta.persistence.Id;
 import jakarta.persistence.JoinColumn;
 import jakarta.persistence.ManyToOne;
 import jakarta.persistence.Table;
+import jakarta.persistence.UniqueConstraint;
 import org.hibernate.annotations.CreationTimestamp;
 import org.hibernate.annotations.UpdateTimestamp;
 
 import java.time.Instant;
 
 /**
- * Maps to the 'cluster_accounts' table. Stores a provisioned username on the 
cluster.
+ * Maps to the 'project_memberships' table. Links cluster accounts to projects 
with roles.
  */
 @Entity
-@Table(name = "cluster_accounts")
-public class ClusterAccountEntity {
+@Table(name = "project_memberships", uniqueConstraints = 
{@UniqueConstraint(columnNames = {"project_id", "cluster_account_id"})})
+public class ProjectMembershipEntity {
 
     @Id
     @GeneratedValue(strategy = GenerationType.UUID)
@@ -45,11 +46,15 @@ public class ClusterAccountEntity {
     private String id;
 
     @ManyToOne(fetch = FetchType.LAZY, optional = false)
-    @JoinColumn(name = "person_id", nullable = false)
-    private PersonEntity person;
+    @JoinColumn(name = "project_id", nullable = false)
+    private ProjectEntity project;
 
-    @Column(name = "username", nullable = false, unique = true)
-    private String username;
+    @ManyToOne(fetch = FetchType.LAZY, optional = false)
+    @JoinColumn(name = "cluster_account_id", nullable = false)
+    private ClusterAccountEntity clusterAccount;
+
+    @Column(name = "role", length = 32)
+    private String role;
 
     @Column(name = "is_active", nullable = false)
     private boolean isActive = true;
@@ -70,20 +75,28 @@ public class ClusterAccountEntity {
         this.id = id;
     }
 
-    public PersonEntity getPerson() {
-        return person;
+    public ProjectEntity getProject() {
+        return project;
+    }
+
+    public void setProject(ProjectEntity project) {
+        this.project = project;
+    }
+
+    public ClusterAccountEntity getClusterAccount() {
+        return clusterAccount;
     }
 
-    public void setPerson(PersonEntity person) {
-        this.person = person;
+    public void setClusterAccount(ClusterAccountEntity clusterAccount) {
+        this.clusterAccount = clusterAccount;
     }
 
-    public String getUsername() {
-        return username;
+    public String getRole() {
+        return role;
     }
 
-    public void setUsername(String username) {
-        this.username = username;
+    public void setRole(String role) {
+        this.role = role;
     }
 
     public boolean isActive() {
@@ -109,19 +122,4 @@ public class ClusterAccountEntity {
     public void setUpdatedAt(Instant updatedAt) {
         this.updatedAt = updatedAt;
     }
-
-    @Override
-    public boolean equals(Object o) {
-        if (o == null || getClass() != o.getClass()) return false;
-
-        ClusterAccountEntity that = (ClusterAccountEntity) o;
-        return id.equals(that.id) && username.equals(that.username);
-    }
-
-    @Override
-    public int hashCode() {
-        int result = id.hashCode();
-        result = 31 * result + username.hashCode();
-        return result;
-    }
 }
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/repo/ProjectMembershipRepository.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/repo/ProjectMembershipRepository.java
new file mode 100644
index 000000000..9410c2298
--- /dev/null
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/repo/ProjectMembershipRepository.java
@@ -0,0 +1,39 @@
+/*
+ * 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.custos.amie.repo;
+
+import org.apache.custos.amie.model.ProjectMembershipEntity;
+import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.stereotype.Repository;
+
+import java.util.List;
+import java.util.Optional;
+
+@Repository
+public interface ProjectMembershipRepository extends 
JpaRepository<ProjectMembershipEntity, String> {
+
+    Optional<ProjectMembershipEntity> 
findByProjectIdAndClusterAccountId(String projectId, String clusterAccountId);
+
+    List<ProjectMembershipEntity> findByProjectId(String projectId);
+
+    List<ProjectMembershipEntity> findByProjectIdAndRole(String projectId, 
String role);
+
+    List<ProjectMembershipEntity> 
findByProjectIdAndClusterAccount_Person_Id(String projectId, String personId);
+
+}
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/repo/ProjectRepository.java 
b/amie-decoder/src/main/java/org/apache/custos/amie/repo/ProjectRepository.java
new file mode 100644
index 000000000..3c79fb3c7
--- /dev/null
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/repo/ProjectRepository.java
@@ -0,0 +1,28 @@
+/*
+ * 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.custos.amie.repo;
+
+import org.apache.custos.amie.model.ProjectEntity;
+import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.stereotype.Repository;
+
+@Repository
+public interface ProjectRepository extends JpaRepository<ProjectEntity, 
String> {
+
+}
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/service/PersonService.java 
b/amie-decoder/src/main/java/org/apache/custos/amie/service/PersonService.java
index 2b5ecd47f..309371432 100644
--- 
a/amie-decoder/src/main/java/org/apache/custos/amie/service/PersonService.java
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/service/PersonService.java
@@ -29,13 +29,18 @@ import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.stereotype.Service;
 import org.springframework.transaction.annotation.Transactional;
+import org.springframework.util.Assert;
 
 import java.util.ArrayList;
 import java.util.HashSet;
 import java.util.Optional;
 import java.util.Set;
+import java.util.UUID;
 import java.util.stream.Collectors;
 
+/**
+ * Service responsible for managing the lifecycle of Person entities and their 
associated DNs.
+ */
 @Service
 public class PersonService {
 
@@ -51,100 +56,60 @@ public class PersonService {
         this.clusterAccountRepository = clusterAccountRepository;
     }
 
-    public static class AccountProvisionResult {
-        private final String localPersonId;
-        private final String username;
-
-        public AccountProvisionResult(String localPersonId, String username) {
-            this.localPersonId = localPersonId;
-            this.username = username;
-        }
-
-        public String getLocalPersonId() {
-            return localPersonId;
-        }
-
-        public String getUsername() {
-            return username;
-        }
-    }
-
+    /**
+     * Finds a Person by their AMIE Global ID or creates a new one if not 
found.
+     *
+     * @param packetBody The "body" of the AMIE packet containing user details.
+     * @return The existing or newly created PersonEntity.
+     */
     @Transactional
-    public AccountProvisionResult createIfAbsentFromPacket(JsonNode 
packetBody, String localPersonId, String proposedUsername) {
-        String accessGlobalId = packetBody.path("UserGlobalID").asText(null);
-        if (accessGlobalId == null || accessGlobalId.isBlank()) {
-            LOGGER.warn("Missing required 'UserGlobalID' (ACCESS Global ID) in 
account create packet body");
-            throw new IllegalArgumentException("Missing required 
'UserGlobalID' (ACCESS Global ID) in account create packet body");
+    public PersonEntity findOrCreatePersonFromPacket(JsonNode packetBody) {
+        String accessGlobalId = packetBody.path("UserGlobalID").asText();
+        Assert.hasText(accessGlobalId, "Packet body must contain a 
'UserGlobalID'.");
+
+        Optional<PersonEntity> existingPerson = 
personRepository.findByAccessGlobalId(accessGlobalId);
+        if (existingPerson.isPresent()) {
+            LOGGER.info("Found existing person with local ID [{}] for 
access_global_id [{}]", existingPerson.get().getId(), accessGlobalId);
+            return existingPerson.get();
         }
 
-        Optional<PersonEntity> existingByGlobal = 
personRepository.findByAccessGlobalId(accessGlobalId);
-        PersonEntity person;
-        if (existingByGlobal.isPresent()) {
-            person = existingByGlobal.get();
-            LOGGER.info("Person already exists for access_global_id [{}] as 
local id [{}]", accessGlobalId, person.getId());
-
-        } else {
-            person = new PersonEntity();
-            person.setId(localPersonId);
-            person.setAccessGlobalId(accessGlobalId);
-            person.setFirstName(packetBody.path("UserFirstName").asText(""));
-            person.setLastName(packetBody.path("UserLastName").asText(""));
-            person.setEmail(packetBody.path("UserEmail").asText(""));
-            
person.setOrganization(packetBody.path("UserOrganization").asText(null));
-            person.setOrgCode(packetBody.path("UserOrgCode").asText(null));
-            
person.setNsfStatusCode(packetBody.path("NsfStatusCode").asText(null));
-            personRepository.save(person);
-
-            JsonNode dnList = packetBody.path("UserDnList");
-            if (dnList.isArray()) {
-                for (JsonNode dnNode : dnList) {
-                    String dn = dnNode.asText(null);
-                    if (dn == null || dn.isBlank()) continue;
-                    if 
(!personDnsRepository.existsByPerson_IdAndDn(person.getId(), dn)) {
-                        PersonDnsEntity pde = new PersonDnsEntity();
-                        pde.setPerson(person);
-                        pde.setDn(dn);
-                        personDnsRepository.save(pde);
-                    }
+        // If not found, create a new person
+        LOGGER.info("No person found for access_global_id [{}]. Creating a new 
person record.", accessGlobalId);
+        PersonEntity newPerson = new PersonEntity();
+        newPerson.setId(UUID.randomUUID().toString());
+        newPerson.setAccessGlobalId(accessGlobalId);
+        newPerson.setFirstName(packetBody.path("UserFirstName").asText(""));
+        newPerson.setLastName(packetBody.path("UserLastName").asText(""));
+        newPerson.setEmail(packetBody.path("UserEmail").asText(""));
+        
newPerson.setOrganization(packetBody.path("UserOrganization").asText(null));
+        newPerson.setOrgCode(packetBody.path("UserOrgCode").asText(null));
+        
newPerson.setNsfStatusCode(packetBody.path("NsfStatusCode").asText(null));
+        personRepository.save(newPerson);
+
+        // Save their associated DNs
+        JsonNode dnList = packetBody.path("UserDnList");
+        if (dnList.isArray()) {
+            for (JsonNode dnNode : dnList) {
+                String dn = dnNode.asText(null);
+                if (dn != null && !dn.isBlank()) {
+                    PersonDnsEntity pde = new PersonDnsEntity();
+                    pde.setPerson(newPerson);
+                    pde.setDn(dn);
+                    personDnsRepository.save(pde);
                 }
             }
         }
-
-        String uniqueUsername = ensureUniqueUsername(proposedUsername);
-        if (clusterAccountRepository.findByUsername(uniqueUsername).isEmpty()) 
{
-            ClusterAccountEntity clusterAccount = new ClusterAccountEntity();
-            clusterAccount.setPerson(person);
-            clusterAccount.setUsername(uniqueUsername);
-            clusterAccount.setActive(true);
-            clusterAccountRepository.save(clusterAccount);
-        } else {
-            LOGGER.error("Username [{}] is already taken by another account", 
uniqueUsername);
-            throw new IllegalStateException("Username " + uniqueUsername + " 
is already taken by another account");
-        }
-
-        return new AccountProvisionResult(person.getId(), uniqueUsername);
+        return newPerson;
     }
 
-    // TODO - this will not be needed when source of truth (e.g., COmanage) is 
integrated
-    private String ensureUniqueUsername(String base) {
-        String candidate = base;
-        int suffix = 0;
-        while (clusterAccountRepository.findByUsername(candidate).isPresent()) 
{
-            suffix++;
-            candidate = base + suffix;
-        }
-        return candidate;
-    }
 
     @Transactional
     public void replaceFromModifyPacket(JsonNode body) {
         String personId = body.path("PersonID").asText(null);
-        if (personId == null || personId.isBlank()) {
-            LOGGER.warn("Missing required 'PersonID' in request_user_modify 
replace body");
-            throw new IllegalArgumentException("Missing required 'PersonID' in 
request_user_modify replace body");
-        }
+        Assert.hasText(personId, "Missing required 'PersonID' in 
request_user_modify replace body");
 
-        PersonEntity person = 
personRepository.findById(personId).orElseThrow(() -> new 
IllegalArgumentException("Unknown local PersonID: " + personId));
+        PersonEntity person = personRepository.findById(personId)
+                .orElseThrow(() -> new IllegalArgumentException("Unknown local 
PersonID: " + personId));
 
         // Update fields
         if (body.has("FirstName")) 
person.setFirstName(body.path("FirstName").asText(person.getFirstName()));
@@ -165,13 +130,9 @@ public class PersonService {
         }
 
         if (newDns.isEmpty()) {
-            // If no DnList provided in replace, clear all
             personDnsRepository.deleteByPerson_Id(personId);
         } else {
-
-            // delete all not in new set
             personDnsRepository.deleteByPerson_IdAndDnNotIn(personId, new 
ArrayList<>(newDns));
-            // insert missing
             for (String dn : newDns) {
                 if (!personDnsRepository.existsByPerson_IdAndDn(personId, dn)) 
{
                     PersonDnsEntity p = new PersonDnsEntity();
@@ -186,20 +147,12 @@ public class PersonService {
     @Transactional
     public void deleteFromModifyPacket(JsonNode body) {
         String personId = body.path("PersonID").asText(null);
-        if (personId == null || personId.isBlank()) {
-            throw new IllegalArgumentException("Missing required 'PersonID' in 
request_user_modify delete body");
-        }
+        Assert.hasText(personId, "Missing required 'PersonID' in 
request_user_modify delete body");
+
         // Cascades will remove DNs and cluster accounts
         personRepository.deleteById(personId);
     }
 
-    /**
-     * Merges two person records. All cluster accounts and unique DNs from the 
retiring
-     * person are moved to the surviving person. The retiring person record is 
then deleted.
-     *
-     * @param survivingPersonId The local ID of the person to keep.
-     * @param retiringPersonId  The local ID of the person to merge and delete.
-     */
     @Transactional
     public void mergePersons(String survivingPersonId, String 
retiringPersonId) {
         LOGGER.info("Merging person {} into {}", retiringPersonId, 
survivingPersonId);
@@ -228,7 +181,6 @@ public class PersonService {
                 retiringDn.setPerson(survivingPerson);
                 personDnsRepository.save(retiringDn);
             } else {
-                // Remove the already existing DN
                 personDnsRepository.delete(retiringDn);
             }
         }
@@ -237,6 +189,4 @@ public class PersonService {
         personRepository.delete(retiringPerson);
         LOGGER.info("Successfully merged and deleted retiring person record 
{}", retiringPersonId);
     }
-}
-
-
+}
\ No newline at end of file
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/service/ProjectMembershipService.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/service/ProjectMembershipService.java
new file mode 100644
index 000000000..9b9de3770
--- /dev/null
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/service/ProjectMembershipService.java
@@ -0,0 +1,139 @@
+/*
+ * 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.custos.amie.service;
+
+import org.apache.custos.amie.model.ClusterAccountEntity;
+import org.apache.custos.amie.model.ProjectEntity;
+import org.apache.custos.amie.model.ProjectMembershipEntity;
+import org.apache.custos.amie.repo.ClusterAccountRepository;
+import org.apache.custos.amie.repo.ProjectMembershipRepository;
+import org.apache.custos.amie.repo.ProjectRepository;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+
+import java.util.List;
+import java.util.Optional;
+
+@Service
+public class ProjectMembershipService {
+
+    private static final Logger LOGGER = 
LoggerFactory.getLogger(ProjectMembershipService.class);
+
+    private final ProjectMembershipRepository membershipRepository;
+    private final ProjectRepository projectRepository;
+    private final ClusterAccountRepository clusterAccountRepository;
+
+    public ProjectMembershipService(ProjectMembershipRepository 
membershipRepository, ProjectRepository projectRepository, 
ClusterAccountRepository clusterAccountRepository) {
+        this.membershipRepository = membershipRepository;
+        this.projectRepository = projectRepository;
+        this.clusterAccountRepository = clusterAccountRepository;
+    }
+
+    /**
+     * Creates a membership linking a cluster account to a project with a role.
+     *
+     * @param projectId        The project ID.
+     * @param clusterAccountId The cluster account ID.
+     * @param role             The role (PI, USER).
+     * @return The created membership entity.
+     */
+    @Transactional
+    public ProjectMembershipEntity createMembership(String projectId, String 
clusterAccountId, String role) {
+        Optional<ProjectMembershipEntity> existing = 
membershipRepository.findByProjectIdAndClusterAccountId(projectId, 
clusterAccountId);
+        if (existing.isPresent()) {
+            LOGGER.info("Membership already exists for project [{}] and 
cluster account [{}]", projectId, clusterAccountId);
+            return existing.get();
+        }
+
+        ProjectEntity project = projectRepository.findById(projectId)
+                .orElseThrow(() -> new IllegalArgumentException("Project not 
found: " + projectId));
+
+        ClusterAccountEntity clusterAccount = 
clusterAccountRepository.findById(clusterAccountId)
+                .orElseThrow(() -> new IllegalArgumentException("Cluster 
account not found: " + clusterAccountId));
+
+        ProjectMembershipEntity membership = new ProjectMembershipEntity();
+        membership.setProject(project);
+        membership.setClusterAccount(clusterAccount);
+        membership.setRole(role);
+        membership.setActive(true);
+
+        membershipRepository.save(membership);
+        LOGGER.info("Created membership for project [{}], cluster account [{}] 
with role [{}]", projectId, clusterAccountId, role);
+
+        return membership;
+    }
+
+    /**
+     * Inactivates a specific membership (user removed from project).
+     *
+     * @param projectId The project ID.
+     * @param personId  Person ID
+     */
+    @Transactional
+    public void inactivateMembershipsByPersonAndProject(String projectId, 
String personId) {
+        // TODO - If the user is a PI of a project?
+        //  - right now only the membership is turned inactive, no changes to 
the project
+        List<ProjectMembershipEntity> memberships = 
membershipRepository.findByProjectIdAndClusterAccount_Person_Id(projectId, 
personId);
+
+        if (memberships.isEmpty()) {
+            LOGGER.warn("No memberships found for person [{}] on project [{}]. 
No action taken.", personId, projectId);
+            return;
+        }
+
+        memberships.forEach(membership -> membership.setActive(false));
+        membershipRepository.saveAll(memberships);
+        LOGGER.info("Inactivated {} membership(s) for person [{}] on project 
[{}]", memberships.size(), personId, projectId);
+    }
+
+    /**
+     * Inactivates all memberships for a project.
+     *
+     * @param projectId The project ID.
+     */
+    @Transactional
+    public void inactivateAllMembershipsForProject(String projectId) {
+        List<ProjectMembershipEntity> projectMemberships = 
membershipRepository.findByProjectId(projectId);
+
+        if (projectMemberships.isEmpty()) {
+            LOGGER.info("No memberships to inactivate for project [{}]", 
projectId);
+            return;
+        }
+
+        projectMemberships.forEach(membership -> membership.setActive(false));
+        membershipRepository.saveAll(projectMemberships);
+
+        LOGGER.info("Inactivated {} memberships for project [{}]", 
projectMemberships.size(), projectId);
+    }
+
+    /**
+     * Reactivates PI membership for a project.
+     *
+     * @param projectId The project ID.
+     */
+    @Transactional
+    public void reactivatePiMembership(String projectId) {
+        List<ProjectMembershipEntity> piMemberships = 
membershipRepository.findByProjectIdAndRole(projectId, "PI");
+        piMemberships.forEach(membership -> membership.setActive(true));
+        membershipRepository.saveAll(piMemberships);
+        LOGGER.info("Reactivated {} PI memberships for project [{}]", 
piMemberships.size(), projectId);
+    }
+
+}
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/service/ProjectService.java 
b/amie-decoder/src/main/java/org/apache/custos/amie/service/ProjectService.java
new file mode 100644
index 000000000..dad06c9e5
--- /dev/null
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/service/ProjectService.java
@@ -0,0 +1,94 @@
+/*
+ * 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.custos.amie.service;
+
+import org.apache.custos.amie.model.ProjectEntity;
+import org.apache.custos.amie.repo.ProjectRepository;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+
+import java.util.Optional;
+
+@Service
+public class ProjectService {
+
+    private static final Logger LOGGER = 
LoggerFactory.getLogger(ProjectService.class);
+
+    private final ProjectRepository projectRepository;
+
+    public ProjectService(ProjectRepository projectRepository) {
+        this.projectRepository = projectRepository;
+    }
+
+    /**
+     * Creates a new project or returns an existing one if found.
+     *
+     * @param projectId   The project ID from AMIE.
+     * @param grantNumber The grant number from AMIE.
+     * @return The project entity (created or existing).
+     */
+    @Transactional
+    public ProjectEntity createOrFindProject(String projectId, String 
grantNumber) {
+        Optional<ProjectEntity> existing = 
projectRepository.findById(projectId);
+        if (existing.isPresent()) {
+            LOGGER.info("Project [{}] already exists with grant number [{}]", 
projectId, existing.get().getGrantNumber());
+            return existing.get();
+        }
+
+        ProjectEntity project = new ProjectEntity();
+        project.setId(projectId);
+        project.setGrantNumber(grantNumber);
+        project.setActive(true);
+        projectRepository.save(project);
+
+        LOGGER.info("Created new project [{}] with grant number [{}]", 
projectId, grantNumber);
+        return project;
+    }
+
+    /**
+     * Inactivates a project.
+     *
+     * @param projectId The project ID to inactivate.
+     */
+    @Transactional
+    public void inactivateProject(String projectId) {
+        ProjectEntity project = 
projectRepository.findById(projectId).orElseThrow(() -> new 
IllegalArgumentException("Project not found: " + projectId));
+
+        project.setActive(false);
+        projectRepository.save(project);
+        LOGGER.info("Inactivated project [{}]", projectId);
+    }
+
+    /**
+     * Reactivates a project.
+     *
+     * @param projectId The project ID to reactivate.
+     */
+    @Transactional
+    public void reactivateProject(String projectId) {
+        ProjectEntity project = 
projectRepository.findById(projectId).orElseThrow(() -> new 
IllegalArgumentException("Project not found: " + projectId));
+
+        project.setActive(true);
+        projectRepository.save(project);
+        LOGGER.info("Reactivated project [{}]", projectId);
+    }
+
+}
diff --git 
a/amie-decoder/src/main/java/org/apache/custos/amie/service/UserAccountService.java
 
b/amie-decoder/src/main/java/org/apache/custos/amie/service/UserAccountService.java
new file mode 100644
index 000000000..d1db2f541
--- /dev/null
+++ 
b/amie-decoder/src/main/java/org/apache/custos/amie/service/UserAccountService.java
@@ -0,0 +1,82 @@
+/*
+ * 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.custos.amie.service;
+
+import org.apache.custos.amie.model.ClusterAccountEntity;
+import org.apache.custos.amie.model.PersonEntity;
+import org.apache.custos.amie.repo.ClusterAccountRepository;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+
+import java.util.UUID;
+
+/**
+ * Service for provisioning and managing Cluster Accounts (usernames).
+ */
+@Service
+public class UserAccountService {
+
+    private static final Logger LOGGER = 
LoggerFactory.getLogger(UserAccountService.class);
+
+    private final ClusterAccountRepository clusterAccountRepository;
+
+    public UserAccountService(ClusterAccountRepository 
clusterAccountRepository) {
+        this.clusterAccountRepository = clusterAccountRepository;
+    }
+
+    /**
+     * Provisions a new, unique cluster account for a given person.
+     *
+     * @param person The PersonEntity that the user account should be created
+     * @return The newly created and saved ClusterAccountEntity
+     */
+    @Transactional
+    public ClusterAccountEntity provisionClusterAccount(PersonEntity person) {
+        // TODO Replace with external source of truth (e.g., COmanage) lookup 
for PersonID and username
+
+        String proposedUsername = (person.getFirstName().trim().charAt(0) + 
person.getLastName().trim().replace(" ", "-")).toLowerCase();
+        String uniqueUsername = ensureUniqueUsername(proposedUsername);
+
+        LOGGER.info("Provisioning new cluster account with username [{}] for 
person [{}]", uniqueUsername, person.getId());
+
+        ClusterAccountEntity newClusterAccount = new ClusterAccountEntity();
+        newClusterAccount.setId(UUID.randomUUID().toString());
+        newClusterAccount.setPerson(person);
+        newClusterAccount.setUsername(uniqueUsername);
+        clusterAccountRepository.save(newClusterAccount);
+
+        return newClusterAccount;
+    }
+
+    private String ensureUniqueUsername(String baseUsername) {
+        String candidate = baseUsername;
+        int suffix = 0;
+        while (clusterAccountRepository.findByUsername(candidate).isPresent()) 
{
+            suffix++;
+            candidate = baseUsername + suffix;
+        }
+        if (suffix > 0) {
+            LOGGER.warn("Base username '{}' was already taken. Generated 
unique username '{}'.", baseUsername, candidate);
+        }
+        return candidate;
+    }
+}
+
diff --git 
a/amie-decoder/src/main/resources/db/migration/V1__initial_migration.sql 
b/amie-decoder/src/main/resources/db/migration/V1__initial_migration.sql
index 9929d3236..8ab97c78a 100644
--- a/amie-decoder/src/main/resources/db/migration/V1__initial_migration.sql
+++ b/amie-decoder/src/main/resources/db/migration/V1__initial_migration.sql
@@ -4,8 +4,7 @@
 -- =================================================================
 
 SET NAMES utf8mb4;
-SET
-time_zone = '+00:00';
+SET time_zone = '+00:00';
 
 -- -----------------------------
 -- TABLE: persons
@@ -20,13 +19,16 @@ CREATE TABLE persons
     email            VARCHAR(255) NOT NULL,
     organization     VARCHAR(255) NULL,
     org_code         VARCHAR(255) NULL,
-    nsf_status_code  VARCHAR(32) NULL,
+    nsf_status_code  VARCHAR(32)  NULL,
     created_at       TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
     updated_at       TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON 
UPDATE CURRENT_TIMESTAMP(6),
 
     PRIMARY KEY (id),
     UNIQUE KEY uq_persons_amie_global_id (access_global_id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+) ENGINE = InnoDB
+  DEFAULT CHARSET = utf8mb4
+  COLLATE = utf8mb4_unicode_ci;
 
 
 -- -----------------------------
@@ -42,7 +44,10 @@ CREATE TABLE person_dns
     PRIMARY KEY (id),
     CONSTRAINT fk_dns_person FOREIGN KEY (person_id) REFERENCES persons (id) 
ON DELETE CASCADE,
     UNIQUE KEY uq_person_dn (person_id, dn)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+) ENGINE = InnoDB
+  DEFAULT CHARSET = utf8mb4
+  COLLATE = utf8mb4_unicode_ci;
 
 
 -- -----------------------------
@@ -54,18 +59,66 @@ CREATE TABLE cluster_accounts
     id         VARCHAR(255) NOT NULL,
     person_id  VARCHAR(255) NOT NULL,
     username   VARCHAR(255) NOT NULL,
-    is_active  BOOLEAN      NOT NULL DEFAULT TRUE,
     created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
     updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE 
CURRENT_TIMESTAMP(6),
 
     PRIMARY KEY (id),
     UNIQUE KEY uq_accounts_username (username),
     CONSTRAINT fk_accounts_person FOREIGN KEY (person_id) REFERENCES persons 
(id) ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+) ENGINE = InnoDB
+  DEFAULT CHARSET = utf8mb4
+  COLLATE = utf8mb4_unicode_ci;
+
+
+-- -----------------------------
+-- TABLE: projects
+-- Stores unique information about each project/allocation
+-- -----------------------------
+CREATE TABLE projects
+(
+    id           VARCHAR(255) NOT NULL, -- AMIE ProjectID (local), e.g., 
"PRJ-TRA258601"
+    grant_number VARCHAR(255) NOT NULL,
+    is_active    BOOLEAN      NOT NULL DEFAULT TRUE,
+    created_at   TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+    updated_at   TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE 
CURRENT_TIMESTAMP(6),
+
+    PRIMARY KEY (id),
+    UNIQUE KEY uq_projects_grant_number (grant_number),
+    KEY idx_projects_active (is_active)
+
+) ENGINE = InnoDB
+  DEFAULT CHARSET = utf8mb4
+  COLLATE = utf8mb4_unicode_ci;
 
 
 -- -----------------------------
--- TABLE: packets
+-- TABLE: project_memberships
+-- Links cluster accounts to projects with roles (user memberships on 
projects).
+-- -----------------------------
+CREATE TABLE project_memberships
+(
+    id                 VARCHAR(255) NOT NULL,
+    project_id         VARCHAR(255) NOT NULL, -- FK to projects.id
+    cluster_account_id VARCHAR(255) NOT NULL, -- FK to cluster_accounts.id
+    role               VARCHAR(32)  NULL,     -- PI, USER
+    is_active          BOOLEAN      NOT NULL DEFAULT TRUE,
+    created_at         TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
+    updated_at         TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON 
UPDATE CURRENT_TIMESTAMP(6),
+
+    PRIMARY KEY (id),
+    CONSTRAINT fk_membership_project FOREIGN KEY (project_id) REFERENCES 
projects (id) ON DELETE CASCADE,
+    CONSTRAINT fk_membership_account FOREIGN KEY (cluster_account_id) 
REFERENCES cluster_accounts (id) ON DELETE CASCADE,
+    UNIQUE KEY uq_project_account (project_id, cluster_account_id),
+    KEY idx_memberships_project (project_id),
+    KEY idx_memberships_account (cluster_account_id),
+    KEY idx_memberships_active (is_active)
+
+) ENGINE = InnoDB
+  DEFAULT CHARSET = utf8mb4
+  COLLATE = utf8mb4_unicode_ci;
+
+
 -- Stores each unique AMIE packet received from the polling endpoint
 -- -----------------------------
 CREATE TABLE packets
@@ -79,13 +132,16 @@ CREATE TABLE packets
     decoded_at   TIMESTAMP(6) NULL,
     processed_at TIMESTAMP(6) NULL,
     retries      INT          NOT NULL DEFAULT 0,
-    last_error   TEXT NULL,
+    last_error   TEXT         NULL,
 
     PRIMARY KEY (id),
     UNIQUE KEY uq_packets_amie_id (amie_id),
-    KEY          idx_packets_status (status),
-    KEY          idx_packets_received_at (received_at)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+    KEY idx_packets_status (status),
+    KEY idx_packets_received_at (received_at)
+
+) ENGINE = InnoDB
+  DEFAULT CHARSET = utf8mb4
+  COLLATE = utf8mb4_unicode_ci;
 
 
 -- -----------------------------
@@ -103,16 +159,19 @@ CREATE TABLE processing_events
     created_at  TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
     started_at  TIMESTAMP(6) NULL,
     finished_at TIMESTAMP(6) NULL,
-    last_error  TEXT NULL,
+    last_error  TEXT         NULL,
 
     PRIMARY KEY (id),
     CONSTRAINT fk_events_packet FOREIGN KEY (packet_id) REFERENCES packets 
(id) ON DELETE CASCADE,
     UNIQUE KEY uq_events_packet_type (packet_id, type),
 
-    KEY         idx_events_status (status),
-    KEY         idx_events_packet_id (packet_id),
-    KEY         idx_events_type (type)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+    KEY idx_events_status (status),
+    KEY idx_events_packet_id (packet_id),
+    KEY idx_events_type (type)
+
+) ENGINE = InnoDB
+  DEFAULT CHARSET = utf8mb4
+  COLLATE = utf8mb4_unicode_ci;
 
 
 -- -----------------------------
@@ -126,13 +185,16 @@ CREATE TABLE processing_errors
     event_id    VARCHAR(255) NULL,
     occurred_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
     summary     TEXT         NOT NULL,
-    detail      TEXT NULL,
+    detail      TEXT         NULL,
 
     PRIMARY KEY (id),
     CONSTRAINT fk_errors_packet FOREIGN KEY (packet_id) REFERENCES packets 
(id) ON DELETE SET NULL,
     CONSTRAINT fk_errors_event FOREIGN KEY (event_id) REFERENCES 
processing_events (id) ON DELETE SET NULL,
 
-    KEY         idx_errors_packet_id (packet_id),
-    KEY         idx_errors_event_id (event_id),
-    KEY         idx_errors_occurred_at (occurred_at)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+    KEY idx_errors_packet_id (packet_id),
+    KEY idx_errors_event_id (event_id),
+    KEY idx_errors_occurred_at (occurred_at)
+
+) ENGINE = InnoDB
+  DEFAULT CHARSET = utf8mb4
+  COLLATE = utf8mb4_unicode_ci;

Reply via email to