---
spec: Expose ScheduleDependencies via GET API ep
status: done
implemented-by: Sachin.Badoni
owner: Sachin.Badoni
related-routes:
  - GET /api/v2/projects/:projectId/schedules/:scheduleRevisionId
  - GET /api/v2/projects/:projectId/schedules
  - GET /api/v2/projects/:projectId/schedules/:scheduleRevisionId/dependencies

files:
  - src/api/v2/projects/schedules/schedules.controller.ts
  - src/api/v2/projects/schedules/schedules.routes.ts
  - src/api/v2/projects/schedules/schedules.validator.ts
  - src/services/schedules.service.ts

---

# Project File Hash (PAPI-3286)

## Goal

Add details from ScheduleDependency table and return it in with /schedule GET call.

## Context

- Get /schedule by Id and GET /schedules return schedule information. They are not returning any ScheduleDependency
  information

## Behavior changes

- New `GET /api/v2/projects/:projectId/schedules/:scheduleRevisionId/dependencies` — Fetch all the dependencies for
  given scheduleRevisionId.
- New DB function `fn_GetScheduleRevisionDependency` to fetch all the dependencies for given project and scheduleRevisionId
- This endpoint should support pagination in line with other endpoint

## Database

| Object                              | Type     | Location                                                    |
|-------------------------------------|----------|-------------------------------------------------------------|
| `fn_GetScheduleRevisionDependency`  | Function | `xyz/Functions/fn_GetScheduleRevisionDependency.sql`        |
| `fn_GetScheduleRevision`            | Function | `xyz/Functions/fn_GetScheduleRevision.sql`                  |
| `fn_GetProjectSchedules`            | Function | `xyz/Functions/fn_GetProjectSchedules.sql`                  |

**Table**: `xyz."ScheduleDependency"`

**Key columns**:

| Column                  | Type         | Notes                                       |
|-------------------------|--------------|---------------------------------------------|
| `Id`                    | INT          | Surrogate PK — used as pagination cursor    |
| `ScheduleDependencyId`  | UUID         | Business PK                                 |
| `ScheduleRevisionId`    | UUID         |                                             |
| `ProjectShardId`        | INT          | derived via `fn_GetProjectShardId`          |
| `ActivityId`            | UUID         |                                             |
| `PredecessorActivityId` | UUID         |                                             |
| `Dependency`            | VARCHAR(32)  |                                             |
| `LagHours`              | REAL         |                                             |
| `UserComments`          | VARCHAR(256) |                                             |

## API response shape

`GET /api/v2/projects/:projectId/schedules/:scheduleRevisionId/dependencies?size=100&lastFetchedIndexId=0` — `200 OK` (paginated envelope):

```json
{
  "records": [
    {
      "scheduleRevisionId": "uuid",
      "activityId": "uuid",
      "predecessorActivityId": "uuid",
      "dependency": "text",
      "lagHours": 1.5,
      "userComments": "text"
    }
  ],
  "recordCount": 1,
  "lastFetchedIndexId": 42
}
```

Query params:

| Param                | Type    | Required | Default           | Notes                                          |
|----------------------|---------|----------|-------------------|------------------------------------------------|
| `size`               | integer | no       | server default    | Capped at server max page size                 |
| `lastFetchedIndexId` | integer | no       | 0                 | Pass `lastFetchedIndexId` from previous response for next page |

## Acceptance criteria

- [x] `GET .../schedules/:scheduleRevisionId/dependencies` returns an array of schedule dependency.
- [x] `GET .../schedules/:scheduleRevisionId/dependencies` returns an empty array (not `404`) when the schedule has no
  dependency.
- [x] `GET .../schedules/:scheduleRevisionId/dependencies` returns `404` when the scheduleRevisionId does not exist.
- [x] This endpoints enforce `DATA_PIPELINE_ROLE` roles or  `SCHEDULE_VIEW` authorities respectively.
- [x] This endpoint support pagination and return default page size if size is not provided and pagination can be found here PostgreSQLDatabase/Database/xyz/Functions/fn_GetUnmappedActivities.sql 

## Test plan

- **Unit**: `test/unit/api/v2/projects/schedules/schedules.controller.spec.ts` and
  `test/unit/services/schedules.service.spec.ts`.
- **E2E**: `test/e2e/api/schedules.e2e.spec.ts` — `describe("Get schedule dependencies")` covers:
  - 200 with paginated envelope shape when dependencies exist (`records`, `recordCount`, `lastFetchedIndexId`)
  - 200 empty `records` array when schedule has no dependencies
  - 404 when `scheduleRevisionId` does not exist
  - 400 when `projectId` or `scheduleRevisionId` is not a valid UUID
  - 404 when project does not exist
  - 401 when auth header is missing
  - 403 when caller lacks `SCHEDULE_VIEW` authority
- Run with: `npm test` (unit) and `npm run test:e2e:file -- test/e2e/api/schedules.e2e.spec.ts` (e2e).

## Decisions

- Route mounted as `/:projectId/schedules` under the v2 project router — consistent with all other project-scoped
  resources.
- DB table is `xyz."ScheduleDependency"`.
- Stored procedures follow the existing `fn_` (function) / `usp_` (procedure) naming convention and live under
  `Functions/` and `Procedures/` respectively.
- 404 vs empty-records distinction: `fn_GetScheduleRevisionDependency` raises a PostgreSQL EXCEPTION with message
  `'No schedule revision found for ScheduleRevisionId ...'` when the revision doesn't exist; the service layer
  catches this and converts it to `NotFoundError`. If the revision exists but has no dependencies the function
  returns zero rows and the endpoint responds with `{ records: [], recordCount: 0, lastFetchedIndexId: -1 }`.
- `PredecessorActivityId` is included in the API response as `predecessorActivityId`.
- Pagination uses `Id` (surrogate integer PK on `ScheduleDependency`) as the cursor — same pattern as `fn_GetUnmappedActivities`. The DB function accepts `_lastFetchedIndexId` (default `0`) and `_limit` (default `1000`); rows are filtered with `WHERE sd."Id" > _lastFetchedIndexId` and ordered by `Id ASC`.
- SQL function lives at `data/PostgreSQLDatabase/Database/xyz/Functions/fn_GetScheduleRevisionDependency.sql` in the DB migration repo.