# Project Management & PostgreSQL Migration Implementation Plan

> **For agentic workers:** REQUIRED: Use superpowers:subagent-driven-development (if subagents available) or superpowers:executing-plans to implement this plan. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Build a top-level Project Management Hub with dedicated tabs for Master Plan, Kanban, Rules files, Documents & Knowledge Base files, and refactor backend storage to PostgreSQL.

**Architecture:** Refactor `db.ts` to use PostgreSQL `pg` pool, create PostgreSQL schemas & migration scripts, add project domain repositories (`projectRepo`, `projectRulesRepo`, `projectDocsRepo`), expose REST API endpoints in Hono, and build React UI components for Project list & Project detail tabs.

**Tech Stack:** React 18, React Router v6, Hono, PostgreSQL (`pg`), Better-Auth (PostgreSQL adapter), TailwindCSS / Vanilla CSS, Vitest.

---

### Task 1: PostgreSQL Client & Migration Script

**Files:**
- Create: `apps/web/server/pgDb.ts`
- Create: `apps/web/migrations/postgres_0001_initial.sql`
- Modify: `apps/web/package.json`

- [ ] **Step 1: Add `pg` and `@types/pg` dependencies to `apps/web/package.json`**

Edit `apps/web/package.json` to include `"pg": "^8.11.5"` under dependencies and `"@types/pg": "^8.11.6"` under devDependencies.

- [ ] **Step 2: Install dependencies**

Run: `pnpm install`  
Expected: Installation completes cleanly without errors.

- [ ] **Step 3: Create PostgreSQL connection pool module `apps/web/server/pgDb.ts`**

Implement connection pooling using `pg.Pool`, supporting `process.env.DATABASE_URL` (defaulting to `postgresql://postgres:postgres@localhost:5432/agent_kanban`). Expose `query(text, params)` helper.

- [ ] **Step 4: Create `apps/web/migrations/postgres_0001_initial.sql`**

Write PostgreSQL DDL for:
- `projects` (`id`, `owner_id`, `code`, `name`, `domain`, `github_url`, `description`, `created_at`, `updated_at`)
- `project_rules` (`id`, `project_id`, `file_name`, `title`, `content`, `created_at`, `updated_at`)
- `project_documents` (`id`, `project_id`, `category`, `file_name`, `title`, `content`, `created_at`, `updated_at`)
- `project_repositories` (`project_id`, `repository_id`, `created_at`)
- `project_agents` (`project_id`, `agent_id`, `created_at`)
- `boards` (with `project_id TEXT REFERENCES projects(id) ON DELETE CASCADE`)
- `tasks` (with `phase TEXT DEFAULT 'Phase 1'`)
- Better-Auth tables (`"user"`, `"session"`, `"account"`, `"verification"`, `"apikey"` with camelCase quotes and `TIMESTAMPTZ`).

- [ ] **Step 5: Commit Task 1**

```bash
git add apps/web/package.json apps/web/server/pgDb.ts apps/web/migrations/postgres_0001_initial.sql pnpm-lock.yaml
git commit -m "feat(db): khởi tạo kết nối postgresql và postgres initial schema"
```

---

### Task 2: Refactor Core DB & Existing Repositories to PostgreSQL Syntax

**Files:**
- Modify: `apps/web/server/db.ts`
- Modify: `apps/web/server/betterAuth.ts`
- Modify: `apps/web/server/boardRepo.ts`
- Modify: `apps/web/server/taskRepo.ts`
- Modify: `apps/web/server/agentRepo.ts`
- Modify: `apps/web/server/machineRepo.ts`

- [ ] **Step 1: Refactor `apps/web/server/db.ts` to export PostgreSQL client**

Export `db` using `pgDb.ts` query interface, replacing D1 database bindings.

- [ ] **Step 2: Refactor `betterAuth.ts` to use PostgreSQL adapter**

Update Better-Auth database configuration to use `pg` adapter.

- [ ] **Step 3: Update `boardRepo.ts` SQL queries**

Replace SQLite placeholders `?` with `$1, $2, $3...` and `datetime('now')` with `NOW()`. Add `project_id` support when creating/listing boards.

- [ ] **Step 4: Update `taskRepo.ts` SQL queries**

Replace `?` placeholders with `$1, $2...` and `datetime('now')` with `NOW()`. Add `phase` field to task select and insert statements.

- [ ] **Step 5: Run existing backend unit tests**

Run: `pnpm test`  
Expected: Existing tests pass or run against mocked DB client.

- [ ] **Step 6: Commit Task 2**

```bash
git add apps/web/server/db.ts apps/web/server/betterAuth.ts apps/web/server/boardRepo.ts apps/web/server/taskRepo.ts apps/web/server/agentRepo.ts apps/web/server/machineRepo.ts
git commit -m "refactor(db): chuyển đổi các repository hiện tại từ SQLite sang PostgreSQL syntax"
```

---

### Task 3: Shared Types Definition

**Files:**
- Create: `packages/shared/src/types/project.ts`
- Modify: `packages/shared/src/index.ts`
- Modify: `packages/shared/src/types/board.ts`
- Modify: `packages/shared/src/types/task.ts`

- [ ] **Step 1: Write `Project`, `ProjectRule`, `ProjectDocument` type definitions**

Define interfaces in `packages/shared/src/types/project.ts`:
```typescript
export interface Project {
  id: string;
  owner_id: string;
  code: string;
  name: string;
  domain?: string | null;
  github_url?: string | null;
  description?: string | null;
  created_at: string;
  updated_at: string;
}

export interface ProjectRule {
  id: string;
  project_id: string;
  file_name: string;
  title: string;
  content: string;
  created_at: string;
  updated_at: string;
}

export interface ProjectDocument {
  id: string;
  project_id: string;
  category: 'doc' | 'knowledge' | 'spec';
  file_name: string;
  title: string;
  content: string;
  created_at: string;
  updated_at: string;
}
```

- [ ] **Step 2: Update `Board` and `Task` interfaces in shared package**

Add `project_id?: string` to `Board` and `phase?: string` to `Task`.

- [ ] **Step 3: Export project types in `packages/shared/src/index.ts`**

Export all types from `./types/project`.

- [ ] **Step 4: Build shared package**

Run: `pnpm --filter @vtit-agent-coding/shared build`  
Expected: Build succeeds with 0 errors.

- [ ] **Step 5: Commit Task 3**

```bash
git add packages/shared/src/types/project.ts packages/shared/src/index.ts packages/shared/src/types/
git commit -m "feat(shared): mở rộng types cho Project, ProjectRule, ProjectDocument và cập nhật Board/Task"
```

---

### Task 4: Project Repositories Implementation

**Files:**
- Create: `apps/web/server/projectRepo.ts`
- Create: `apps/web/server/projectRulesRepo.ts`
- Create: `apps/web/server/projectDocsRepo.ts`
- Create: `tests/server/projectRepo.spec.ts`

- [ ] **Step 1: Write unit test for `projectRepo.ts`**

Create `tests/server/projectRepo.spec.ts` testing project creation (with default board creation), listing, getting by ID/code, and link repo/agent.

- [ ] **Step 2: Implement `projectRepo.ts`**

Functions:
- `createProject(pool, ownerId, data)` (automatically creates default board)
- `listProjects(pool, ownerId)`
- `getProjectById(pool, id)`
- `getProjectByBoardId(pool, boardId)`
- `linkProjectRepository(pool, projectId, repoId)` / `unlinkProjectRepository(pool, projectId, repoId)`
- `linkProjectAgent(pool, projectId, agentId)` / `unlinkProjectAgent(pool, projectId, agentId)`

- [ ] **Step 3: Implement `projectRulesRepo.ts`**

CRUD operations for `project_rules` (list rules metadata, get rule content by ID, create, update, delete).

- [ ] **Step 4: Implement `projectDocsRepo.ts`**

CRUD operations for `project_documents` (list docs metadata, get doc content by ID, create, update, delete).

- [ ] **Step 5: Run unit tests**

Run: `pnpm vitest run tests/server/projectRepo.spec.ts`  
Expected: All tests pass.

- [ ] **Step 6: Commit Task 4**

```bash
git add apps/web/server/projectRepo.ts apps/web/server/projectRulesRepo.ts apps/web/server/projectDocsRepo.ts tests/server/projectRepo.spec.ts
git commit -m "feat(backend): tạo repositories quản lý dự án, rules, tài liệu và liên kết repos/agents"
```

---

### Task 5: Hono API Routes for Projects, Master Plan, Rules & Docs

**Files:**
- Create: `apps/web/server/projectRoutes.ts`
- Modify: `apps/web/server/routes.ts`

- [ ] **Step 1: Implement Project Core Routes in `projectRoutes.ts`**

- `GET /api/projects`
- `POST /api/projects`
- `GET /api/projects/:id`
- `PUT /api/projects/:id`
- `DELETE /api/projects/:id`
- `GET /api/projects/by-board/:boardId`

- [ ] **Step 2: Implement Master Plan Tasks & Project Links Routes**

- `GET /api/projects/:projectId/master-plan`
- `POST /api/projects/:projectId/tasks` (accepts `title`, `description`, `phase`, `status`)
- `POST /api/projects/:projectId/repositories` (link repo)
- `POST /api/projects/:projectId/agents` (link agent)

- [ ] **Step 3: Implement Rules & Docs CRUD Routes**

- `GET /api/projects/:projectId/rules`
- `GET /api/projects/:projectId/rules/:ruleId`
- `POST /api/projects/:projectId/rules`
- `PUT /api/projects/:projectId/rules/:ruleId`
- `DELETE /api/projects/:projectId/rules/:ruleId`
- `GET /api/projects/:projectId/docs`
- `GET /api/projects/:projectId/docs/:docId`
- `POST /api/projects/:projectId/docs`
- `PUT /api/projects/:projectId/docs/:docId`
- `DELETE /api/projects/:projectId/docs/:docId`

- [ ] **Step 4: Mount `projectRoutes` in `apps/web/server/routes.ts`**

Register routes under `/api/projects`.

- [ ] **Step 5: Verify typecheck**

Run: `pnpm --filter @vtit-agent-coding/web typecheck`  
Expected: 0 errors.

- [ ] **Step 6: Commit Task 5**

```bash
git add apps/web/server/projectRoutes.ts apps/web/server/routes.ts
git commit -m "feat(api): triển khai đầy đủ API routes cho project, master plan, rules và docs"
```

---

### Task 6: Frontend Pages & Components

**Files:**
- Create: `apps/web/src/routes/ProjectsPage.tsx`
- Create: `apps/web/src/routes/ProjectDetailPage.tsx`
- Create: `apps/web/src/components/MasterPlanView.tsx`
- Create: `apps/web/src/components/RulesView.tsx`
- Create: `apps/web/src/components/DocsView.tsx`
- Create: `apps/web/src/components/ReposAgentsView.tsx`
- Modify: `apps/web/src/routes/BoardRedirect.tsx`
- Modify: `apps/web/src/App.tsx`

- [ ] **Step 1: Implement `ProjectsPage.tsx`**

Build Grid Card view displaying projects with Code (Badge), Name, Domain, GitHub URL, Description, and "+ Tạo dự án mới" modal.

- [ ] **Step 2: Implement `MasterPlanView.tsx` component**

Group tasks by Phase (`Phase 1`, `Phase 2`, etc.) and Status, with inline form to add new tasks with phase selection.

- [ ] **Step 3: Implement `RulesView.tsx` & `DocsView.tsx` components**

Split view layout: Left sidebar listing `.md` files, Right pane providing markdown viewer & live editor with Save/Delete actions.

- [ ] **Step 4: Implement `ReposAgentsView.tsx` & `ProjectDetailPage.tsx`**

Detail dashboard with top header info & 6 tabs navigation (`Master Plan`, `Kanban Board`, `Rules`, `Tài liệu & Tri thức`, `Repos & Agents`, `Cài đặt`).

- [ ] **Step 5: Update `BoardRedirect.tsx` and `App.tsx` routing**

Route `/` to `ProjectsPage`, `/projects/:projectId` to `ProjectDetailPage`. Update `BoardRedirect.tsx` to redirect `/boards/:boardId` to its parent `/projects/:projectId`.

- [ ] **Step 6: Check frontend typecheck & build**

Run: `pnpm --filter @vtit-agent-coding/web build`  
Expected: Build succeeds.

- [ ] **Step 7: Commit Task 6**

```bash
git add apps/web/src/routes/ProjectsPage.tsx apps/web/src/routes/ProjectDetailPage.tsx apps/web/src/components/ apps/web/src/routes/BoardRedirect.tsx apps/web/src/App.tsx
git commit -m "feat(ui): xây dựng trang danh sách dự án và trang chi tiết dự án với các tab chức năng"
```

---

### Task 7: Full `_TASK.schema.json` Task Form & Detail Modal Integration

**Files:**
- Create: `apps/web/src/components/TaskSchemaModal.tsx`
- Modify: `apps/web/src/components/MasterPlanView.tsx`
- Modify: `apps/web/src/routes/BoardPage.tsx`
- Modify: `apps/web/server/taskRepo.ts`

- [ ] **Step 1: Update `taskRepo.ts` to persist `_TASK.schema.json` fields**

Extend `createTask` and `updateTask` to handle `key`, `module`, `priority`, `businessContext`, `steps`, `qcChecks`, `affectedFiles`, `acceptanceTests`, and `schema_data`.

- [ ] **Step 2: Create `TaskSchemaModal.tsx` component**

Build rich modal dialog allowing users/developers to view and edit tasks following `_TASK.schema.json` (Key, Module, Priority, Business Context, Steps, QC Checks, Acceptance Tests).

- [ ] **Step 3: Integrate "+ Tạo Task mới" button and detail modal in Kanban & Master Plan**

Connect "+ Tạo Task mới" button in `BoardPage.tsx` (Kanban) and `MasterPlanView.tsx` to open `TaskSchemaModal`.

- [ ] **Step 4: Check frontend build**

Run: `pnpm --filter @vtit-agent-coding/web build`  
Expected: Build succeeds without errors.

- [ ] **Step 5: Commit Task 7**

```bash
git add apps/web/src/components/TaskSchemaModal.tsx apps/web/src/components/MasterPlanView.tsx apps/web/src/routes/BoardPage.tsx apps/web/server/taskRepo.ts
git commit -m "feat(task): tích hợp cấu trúc task chi tiết theo chuẩn _TASK.schema.json vào Kanban và Master Plan"
```

---

### Task 8: Project Agent Assignment & Task Assignee Selection

**Files:**
- Modify: `apps/web/src/components/ReposAgentsView.tsx`
- Modify: `apps/web/src/components/TaskSchemaModal.tsx`
- Modify: `apps/web/server/projectRepo.ts`
- Modify: `apps/web/server/projectRoutes.ts`

- [ ] **Step 1: Implement Project Agents API & Repo helper**

Support listing linked project agents via `GET /api/projects/:projectId/agents` and linking/unlinking via `POST /api/projects/:projectId/agents`.

- [ ] **Step 2: Update `ReposAgentsView.tsx` for adding agents to project**

Provide UI dropdown/selector in Tab `Repos & Agents` to add available system Agents to the Project (`project_agents`).

- [ ] **Step 3: Update `TaskSchemaModal.tsx` for Assignee selection**

In `TaskSchemaModal`, add Assignee dropdown populated with Agents linked to the Project (`project_agents`).

- [ ] **Step 4: Check frontend build**

Run: `pnpm --filter @vtit-agent-coding/web build`  
Expected: Build succeeds without errors.

- [ ] **Step 5: Commit Task 8**

```bash
git add apps/web/src/components/ReposAgentsView.tsx apps/web/src/components/TaskSchemaModal.tsx apps/web/server/projectRepo.ts apps/web/server/projectRoutes.ts
git commit -m "feat(agents): hỗ trợ gán AI Agents vào Project và chọn Agent cho từng Task"
```

---

### Task 9: Project Members & RBAC + GitLab Integration (Commits, MRs, CI/CD) + Task Git Links

**Files:**
- Modify: `apps/web/migrations/postgres_0001_initial.sql`
- Modify: `apps/web/server/projectRepo.ts`
- Create: `apps/web/server/gitlabService.ts`
- Create: `apps/web/server/gitlabRoutes.ts`
- Modify: `apps/web/server/projectRoutes.ts`
- Modify: `apps/web/server/taskRepo.ts`
- Create: `apps/web/src/components/ProjectMembersView.tsx`
- Modify: `apps/web/src/components/ReposAgentsView.tsx`
- Modify: `apps/web/src/components/TaskSchemaModal.tsx`
- Modify: `apps/web/src/routes/ProjectDetailPage.tsx`

- [ ] **Step 1: Update DDL migration & DB queries for `project_members`, `repository_connectors` (custom domain GitLab), `git_branch`, and `pull_request_url`**

Support table `repository_connectors` (`provider`, `custom_domain`, `api_endpoint`, `access_token`, `webhook_secret`), `project_members` (roles: `READ`, `DEVELOPER`, `MAINTAINER`), and task columns `git_branch`, `pull_request_url`.

- [ ] **Step 2: Implement Member Management API & RBAC Permission Guards**

Create API endpoints:
- `GET /api/projects/:id/members`
- `POST /api/projects/:id/members`
- `DELETE /api/projects/:id/members/:userId`

- [ ] **Step 3: Implement GitLab Webhook & REST API Service (`gitlabService.ts`)**

Handle GitLab Webhook events (`POST /api/webhooks/gitlab`) for Merge Requests, Push Commits, and CI/CD Pipeline status checks.

- [ ] **Step 4: Create `ProjectMembersView.tsx` & Update `ReposAgentsView.tsx` for GitLab Config**

Build UI tab for managing project members and configuring GitLab Enterprise repository connection & webhooks.

- [ ] **Step 5: Update `TaskSchemaModal.tsx` & Task Cards to display Git Branch, MR links & GitLab CI/CD status**

Add input fields & clickable badges for Git Branch, Merge Request URL, and CI/CD status.

- [ ] **Step 6: Check frontend build**

Run: `pnpm --filter @vtit-agent-coding/web build`  
Expected: Build succeeds without errors.

- [ ] **Step 7: Commit Task 9**

```bash
git add apps/web/migrations/postgres_0001_initial.sql apps/web/server/projectRepo.ts apps/web/server/gitlabService.ts apps/web/server/gitlabRoutes.ts apps/web/server/projectRoutes.ts apps/web/server/taskRepo.ts apps/web/src/components/ProjectMembersView.tsx apps/web/src/components/ReposAgentsView.tsx apps/web/src/components/TaskSchemaModal.tsx apps/web/src/routes/ProjectDetailPage.tsx
git commit -m "feat(gitlab): hỗ trợ liên thông dữ liệu với GitLab doanh nghiệp (Commits, Merge Requests, CI/CD) và phân quyền RBAC"
```


---

### Task 10: End-to-End Verification & Integration Test

**Files:**
- Create: `tests/e2e/projects.spec.ts`

- [ ] **Step 1: Write integration E2E test for project management workflow**

Test creating a project, adding project members with roles, adding agents to project, creating tasks assigned to project agents with `_TASK.schema.json` structure and Git PR links, managing rule/doc files, and viewing Master Plan & Kanban.

- [ ] **Step 2: Run all tests**

Run: `pnpm test`  
Expected: All tests pass.

- [ ] **Step 3: Commit Task 10**

```bash
git add tests/e2e/projects.spec.ts
git commit -m "test: bổ sung integration test cho luồng quản lý dự án, phân quyền RBAC và git PR links"
```