summary: "Add three read/create REST endpoints in API2 for the existing xyz.Asset\n\ table: POST create, GET single, and GET list (cursor-paginated), all\nscoped under\ \ /api/v2/projects/:projectId/assets and gated on project\nmembership. No new tables\ \ are introduced \u2014 Asset already exists in the\nschema. Work is API2 + Postgres\ \ (new procs/functions only).\n" target_service: api2 postgres_changes: - kind: function schema: xyz name: fn_GetAsset intent: 'Table-returning function to fetch a single Asset by ProjectId (UUID) and AssetId (UUID). Resolves _projectShardId via xyz."fn_GetProjectShardId"(_projectId), then filters xyz."Asset" WHERE "ProjectShardId" = _projectShardId AND "AssetId" = _assetId AND "IsDeleted" = FALSE. Returns full Asset column set: AssetId, ProjectId(passed through), AssetTypeId, ParentAssetId, Name, CreatedBy, InsertedOn, LastModifiedOn, LastModifiedBy, IsDeleted, DeletedOn, DeletedBy. ' params: - _projectId UUID - _assetId UUID file: Database/xyz/Functions/fn_GetAsset.sql - kind: function schema: xyz name: fn_GetAssetList intent: "Table-returning function for cursor-based (keyset) pagination of\nAssets\ \ in a project. Resolves _projectShardId, filters\nxyz.\"Asset\" WHERE \"ProjectShardId\"\ \ = _projectShardId\nAND \"IsDeleted\" = FALSE. Cursor is the internal \"Id\"\ \ (INT\nGENERATED ALWAYS AS IDENTITY) which is monotonic within a shard \u2014\ \nuse it as the keyset column so pagination is stable.\nSignature: (_projectId\ \ UUID, _lastFetchedIndexId INT, _size INT).\nWhen _lastFetchedIndexId IS NULL,\ \ start from the beginning.\nORDER BY \"Id\" ASC, LIMIT _size. Returns the same\ \ full Asset\ncolumn set as fn_GetAsset plus the internal \"Id\" so API2 can\n\ populate lastFetchedIndexId.\n" params: - _projectId UUID - _lastFetchedIndexId INT - _size INT file: Database/xyz/Functions/fn_GetAssetList.sql - kind: procedure schema: xyz name: usp_InsertAsset intent: "Procedure to insert a new Asset for a project. Resolves\n_projectShardId\ \ via xyz.\"fn_GetProjectShardId\"(_projectId); if the\nproject does not exist\ \ the resolved shard id is NULL \u2014 raise a\nNOT_FOUND-style exception so the\ \ API2 layer can map it to 404.\nInsert into xyz.\"Asset\" (\"ProjectShardId\"\ , \"AssetId\",\n\"AssetTypeId\", \"ParentAssetId\", \"Name\", \"CreatedBy\", \"\ IsDeleted\")\nVALUES (_projectShardId, GEN_RANDOM_UUID(), _assetTypeId,\n_parentAssetId,\ \ _name, _createdBy, FALSE). Validate that\n_assetTypeId exists for the shard,\ \ and if _parentAssetId is\nprovided that it exists for the shard (both project-scoped).\n\ Returns/records the new AssetId so it can be selected back via\nfn_GetAsset (single\ \ round-trip: proc does insert + validation).\nStandard EXCEPTION block logging\ \ to xyz.\"DbException\".\nParameter names mirror columns: _createdBy (NOT _insertedBy).\n" params: - _projectId UUID - _assetTypeId UUID - _parentAssetId UUID - _name TEXT - _createdBy TEXT file: Database/xyz/Procedures/usp_InsertAsset.sql notes: 'Asset is a pre-existing table; no table DDL changes. This adds only new function/procedure files (idempotent, runOnChange:true, DROP ... IF EXISTS then CREATE OR REPLACE, endDelimiter:/). ' citus_changes: none index_changes: none reporting_changes: none seed_data_changes: none mongo_changes: none api2_changes: - method: POST path: /api/v2/projects/{projectId}/assets tag: Assets permission: project membership (any role) files: routes: src/api/v2/projects/assets/assets.routes.ts controller: src/api/v2/projects/assets/assets.controller.ts validator: src/api/v2/projects/assets/assets.validator.ts service: src/services/assets.service.ts request_dto: name: CreateAssetRequest properties: assetTypeId: type: string format: uuid required: true parentAssetId: type: string format: uuid required: false nullable: true name: type: string required: true max_length: 512 response_dto: name: AssetResponse properties: assetId: type: string format: uuid assetTypeId: type: string format: uuid parentAssetId: type: string format: uuid nullable: true name: type: string createdBy: type: string insertedOn: type: string format: date-time lastModifiedOn: type: string format: date-time nullable: true lastModifiedBy: type: string nullable: true db_calls: - usp_InsertAsset - fn_GetAsset notes: "Controller enforces project membership before the write (see\ninter_service_calls:\ \ membership check against hc-iam). A\nnon-existent projectId results in usp_InsertAsset\ \ raising a\nNOT_FOUND-style error which the controller maps to 404\n(open question\ \ resolved: POST on unknown project returns 404,\nconsistent with the other project-scoped\ \ endpoints). After the\ninsert, the new asset is read back once via fn_GetAsset\ \ using the\nreturned AssetId \u2014 no redundant pre-fetch.\n" responses: - 201 - 400 - 401 - 403 - 404 - 500 - method: GET path: /api/v2/projects/{projectId}/assets/{assetId} tag: Assets permission: project membership (any role) files: routes: src/api/v2/projects/assets/assets.routes.ts controller: src/api/v2/projects/assets/assets.controller.ts service: src/services/assets.service.ts response_dto: name: AssetResponse properties: assetId: type: string format: uuid assetTypeId: type: string format: uuid parentAssetId: type: string format: uuid nullable: true name: type: string createdBy: type: string insertedOn: type: string format: date-time lastModifiedOn: type: string format: date-time nullable: true lastModifiedBy: type: string nullable: true db_calls: - fn_GetAsset notes: '404 when fn_GetAsset returns no rows (asset not found, soft-deleted, or project not found). Membership check runs before the fetch. ' responses: - 200 - 400 - 401 - 403 - 404 - 500 - method: GET path: /api/v2/projects/{projectId}/assets tag: Assets permission: project membership (any role) files: routes: src/api/v2/projects/assets/assets.routes.ts controller: src/api/v2/projects/assets/assets.controller.ts service: src/services/assets.service.ts query_params: - name: lastFetchedIndexId in: query required: false description: 'Cursor: the internal Id of the last asset from the previous page.' - name: size in: query required: false description: Page size (defaults applied via parsePagingQueryParam). response_dto: name: AssetPaginationEnvelope description: 'PaginationEnvelope { records: AssetResponse[], recordCount, lastFetchedIndexId }. lastFetchedIndexId is the internal Id of the last record in the page, fed back on the next request. ' db_calls: - fn_GetAssetList notes: 'Uses parsePagingQueryParam to read the cursor/size and buildPaginatedQueryResponse to wrap the result. Cursor field is the internal INT Id (keyset), decided because Asset has no other guaranteed-monotonic single column and Id is per-shard identity. Membership check runs before the query. ' responses: - 200 - 400 - 401 - 403 - 500 api1_changes: none inter_service_calls: - from: api2 to: api1-hc-iam endpoint: existing project-membership/authorization lookup used by other API2 project-scoped endpoints request_shape: projectId + caller identity (bearer token) response_shape: membership/role for caller on project; used to allow (any member) or 403 notes: "Reuse the existing membership-verification path already used by\nother /api/v2/projects/{projectId}/*\ \ endpoints (e.g. users, files).\nNo new hc-iam endpoint is introduced. API2 never\ \ reads Mongo\ndirectly \u2014 membership comes via hc-iam.\n" new_permissions: none java_frozen_resources: - resource: projects confirmation: 'The Project entity (dual-API) is only READ for membership scoping and shard resolution. No changes are made to API1 hc-project''s ProjectResource or any project write path on the Java side. ' risks: - The spec lists a 'ProjectMembership' data entity, but there is no ProjectMembership Postgres table in the xyz schema. Membership is authoritative in hc-iam (Mongo). This plan routes the membership check through hc-iam via API2's existing authorization path rather than a Postgres table. Confirm this matches the intended membership source. - spec.data_touched implies any-member access with no distinct permission constant. This plan adds NO new permission and relies on the existing project-membership gate. If the team later wants a named ASSET_VIEW/ASSET_EDIT permission, that is a follow-up. - Cursor uses the internal INT Id as the keyset column (per-shard identity). This is stable for append-only reads but assumes Id ordering matches insertion order within a shard, which it does. If future soft-deletes/reordering change expectations, revisit. - Asset table has no unique constraint on Name within a project; duplicate names are allowed. usp_InsertAsset does not enforce uniqueness. Confirm this is acceptable for v1. out_of_scope: - Update (PUT/PATCH) and Delete (DELETE) asset endpoints. - "Role-differentiated permissions \u2014 all project members have equal access in\ \ v1." - Cross-project asset queries. - "Any changes to the xyz.Asset table schema (columns/constraints) \u2014 the table\ \ already exists and is used as-is." - AssetType creation/management endpoints (assetTypeId is expected to already exist). testing_plan: 'Postgres specialist: extend IntegrationTest/main.py only if needed to cover fn_GetAsset / fn_GetAssetList / usp_InsertAsset behavior (insert then read-back, keyset pagination boundaries, NULL shard id -> raise on unknown project, soft-deleted assets excluded). API2 specialist: add unit tests for assets.controller (membership 403, 404 on unknown project/asset, 201 create with returned body, 400 on missing required fields) and e2e tests for all three endpoints including cursor pagination (multi-page, empty page, stable ordering) under test/e2e/api/v2/projects/assets/assets.spec.ts and test/unit/.../assets.controller.spec.ts. Ensure npm test stays green.' _meta: model: claude-opus-4-8 atom_ids: - endpoint.api2.post__api_v2_projects__projectId__devices - endpoint.api2.get__api_v2_projects__projectId__videos - endpoint.api2.get__api_v2_projects__projectId__issues__issueId__history - endpoint.api2.post__api_v2_projects__projectId__upload-model - endpoint.api2.get__api_v2_projects__projectId__cde-project-locations - endpoint.api2.get__api_v2_projects__projectId__userfiles - endpoint.api2.get__api_v2_projects__projectId__userfiles_models - endpoint.api2.get__api_v2_projects__projectId__activities_mapping - endpoint.api2.get__api_v2_projects__projectId__models_folders - endpoint.api2.get__api_v2_projects__projectId__files - endpoint.api2.get__api_v2_projects__projectId__users - endpoint.api2.get__api_v2_projects__projectId__activities_categories__categoryTypeId_ - pg.reporting.CalculationMethod - pg.reporting.ProgressOutput - pg.reporting.ProjectCalculationMethod - pg.reporting.ProjectPerformanceSnapshot - pg.reporting.ProjectProgress - pg.staging.DuplicatedMigratedMongoElement - pg.xyz.Project - mongo.hc-project.project - mongo.embedded.Project (iam) - dto.api1-hc-project.Project - endpoint.api1-hc-iam.syncProject - dto.api2.Project spec: title: Asset CR Endpoints (API2) project_kind: modify user_facing_behavior: "Three new REST endpoints in API2:\n1. POST /api/v2/projects/:projectId/assets\ \ \u2014 create a new Asset scoped to a project.\n2. GET /api/v2/projects/:projectId/assets/:assetId\ \ \u2014 fetch a single Asset by ID.\n3. GET /api/v2/projects/:projectId/assets\ \ \u2014 list Assets for a project with cursor-based pagination.\n\nAll endpoints\ \ require the caller to be an authenticated project member (any role). Non-members\ \ receive 403. Project not found returns 404." data_touched: - Asset - Project - ProjectMembership api_surface: both non_functional_requirements: - Cursor-based pagination on the list endpoint - Project membership/role check enforced on all three endpoints (any member role is sufficient to create or read) - Scoped under /api/v2/projects/:projectId/assets to reflect Project ownership - Schema (columns, types, constraints) to be read from the API2 Postgres repo by the architect out_of_scope: - Update (PUT/PATCH) and Delete (DELETE) endpoints - Role-differentiated permissions (all project members have equal access in v1) - Cross-project asset queries open_questions: - "Exact Asset table columns, types, and constraints \u2014 architect to read\ \ from Postgres migration files in the API2 repo" - "Cursor field for pagination (e.g. created_at + id, or id only) \u2014 architect\ \ to decide based on schema" - Should a non-existent projectId on POST return 404 or 400? - Are there any required vs. optional fields on Asset creation that affect request validation?