# Project Management & PostgreSQL Migration Design ## 1. Overview This document specifies the design for introducing a top-level **Project Management Hub** and refactoring the backend database from Cloudflare D1 (SQLite) to **PostgreSQL**. Projects serve as the main entity containing: - Basic Info: Name, Code (Key), Domain, GitHub URL, Description - Views & Features: Master Plan, Kanban Board, Rules Files, Documents & Knowledge Base Files, Repositories, and Agent configurations. --- ## 2. Architecture & Database Migration (SQLite -> PostgreSQL) ### 2.1 Database Driver & Connection - **Database Engine**: PostgreSQL - **Driver**: `pg` (node-postgres) with connection pooling. - **Connection Configuration**: Controlled via `DATABASE_URL` environment variable (e.g., `postgresql://postgres:postgres@localhost:5432/agent_kanban`). - **Better-Auth Integration**: Configured with the PostgreSQL adapter (`pg`). ### 2.2 SQL Query Adapter & Migration Standards - Replace SQLite parameterized queries (`?`) with PostgreSQL syntax (`$1, $2, $3...`). - Replace SQLite date functions (`datetime('now')`) with PostgreSQL native `NOW()`. - Primary keys remain UUID/nanoid strings (`TEXT PRIMARY KEY`). - Timestamps use `TIMESTAMPTZ NOT NULL DEFAULT NOW()`. - Convert boolean columns from SQLite `INTEGER (0/1)` to native PostgreSQL `BOOLEAN DEFAULT FALSE`. - Handle `updated_at` timestamps explicitly in repository methods (`updated_at = NOW()`) or via PostgreSQL `BEFORE UPDATE` trigger function `update_updated_at_column()`. - Better-Auth table names and camelCase identifiers retain double quotes (`"user"`, `"agentHost"`, `"createdAt"`) in PostgreSQL SQL queries to preserve case sensitivity. --- ## 3. Database Schema Specification ### 3.1 `projects` Table ```sql CREATE TABLE projects ( id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, code TEXT NOT NULL UNIQUE, name TEXT NOT NULL, domain TEXT, github_url TEXT, gitlab_url TEXT, gitlab_project_id TEXT, gitlab_access_token TEXT, gitlab_webhook_secret TEXT, description TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_projects_owner ON projects(owner_id); CREATE UNIQUE INDEX idx_projects_owner_code ON projects(owner_id, code); ``` ### 3.2 `project_rules` Table Manages individual markdown rule files associated with a project. ```sql CREATE TABLE project_rules ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, file_name TEXT NOT NULL, title TEXT NOT NULL, content TEXT NOT NULL DEFAULT '', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_project_rules_project ON project_rules(project_id); CREATE UNIQUE INDEX idx_project_rules_file ON project_rules(project_id, file_name); ``` ### 3.3 `project_documents` Table Manages individual markdown documents and knowledge base files. ```sql CREATE TABLE project_documents ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, category TEXT NOT NULL DEFAULT 'doc', -- 'doc' | 'knowledge' | 'spec' file_name TEXT NOT NULL, title TEXT NOT NULL, content TEXT NOT NULL DEFAULT '', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_project_documents_project ON project_documents(project_id); CREATE UNIQUE INDEX idx_project_documents_file ON project_documents(project_id, file_name); ``` ### 3.4 `boards` & `tasks` Updates - **`boards`**: Linked to projects via `project_id`. Each project automatically gets a default board upon creation. - **`tasks`**: Extended with a `phase` column for grouping tasks in the Master Plan view. ```sql ALTER TABLE boards ADD COLUMN project_id TEXT REFERENCES projects(id) ON DELETE CASCADE; CREATE INDEX idx_boards_project ON boards(project_id); ALTER TABLE tasks ADD COLUMN phase TEXT DEFAULT 'Phase 1'; ALTER TABLE tasks ADD COLUMN key TEXT; ALTER TABLE tasks ADD COLUMN module TEXT; ALTER TABLE tasks ADD COLUMN priority TEXT DEFAULT 'MEDIUM'; ALTER TABLE tasks ADD COLUMN business_context TEXT; ALTER TABLE tasks ADD COLUMN steps JSONB DEFAULT '[]'::jsonb; ALTER TABLE tasks ADD COLUMN qc_checks JSONB DEFAULT '[]'::jsonb; ALTER TABLE tasks ADD COLUMN affected_files JSONB DEFAULT '[]'::jsonb; ALTER TABLE tasks ADD COLUMN acceptance_tests JSONB DEFAULT '[]'::jsonb; ALTER TABLE tasks ADD COLUMN schema_data JSONB DEFAULT '{}'::jsonb; ALTER TABLE tasks ADD COLUMN git_branch TEXT; ALTER TABLE tasks ADD COLUMN pull_request_url TEXT; CREATE INDEX idx_tasks_phase ON tasks(phase); CREATE INDEX idx_tasks_key ON tasks(key); CREATE INDEX idx_tasks_module ON tasks(module); ``` ### 3.5 Detailed Task Schema (_TASK.schema.json) Tasks in Kanban and Master Plan support full structured data according to `_TASK.schema.json`: - **Identification & Module**: `key` (e.g. `nguoi-dung-00`), `module` (`NGUOI-DUNG`, `BAO-CAO`, etc.), `title`. - **Workflow & Priority**: `status` (`BACKLOG`, `TODO`, `IN_PROGRESS`, `IN_REVIEW`, `BLOCKED`, `DONE`, `CANCELLED`), `priority` (`CRITICAL`, `HIGH`, `MEDIUM`, `LOW`). - **Context & Technical Specs**: `businessContext`, `description`, `technicalNotes`, `outOfScope`. - **Actionable Steps & Quality Checks**: `steps` (`order`, `action`, `detail`, `codeHint`, `verifyCommand`), `qcChecks` (`category`, `item`, `severity`), `acceptanceTests` (`id`, `scenario`, `given`, `when`, `then`), `affectedFiles`. - **Git & PR Deliverables**: `git_branch` (e.g. `feature/nguoi-dung-00`), `pull_request_url` (e.g. `https://github.com/org/repo/pull/42`). ### 3.6 Project Repositories, Agents & Members (RBAC) Link Tables ```sql CREATE TABLE repository_connectors ( id TEXT PRIMARY KEY, repository_id TEXT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE, provider TEXT NOT NULL CHECK (provider IN ('github', 'gitlab', 'custom_gitlab')), custom_domain TEXT, -- e.g. https://gitlab.company.com api_endpoint TEXT, -- e.g. https://gitlab.company.com/api/v4 access_token TEXT, webhook_secret TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE project_members ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, role TEXT NOT NULL CHECK (role IN ('READ', 'DEVELOPER', 'MAINTAINER')), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (project_id, user_id) ); CREATE TABLE project_repositories ( project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, repository_id TEXT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), PRIMARY KEY (project_id, repository_id) ); CREATE TABLE project_agents ( project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, agent_id TEXT NOT NULL REFERENCES agents(id) ON DELETE CASCADE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), PRIMARY KEY (project_id, agent_id) ); ``` --- ## 4. REST API Endpoints ### 4.1 Projects & Members API - `GET /api/projects` - List all projects for current user (where user is owner or member). - `POST /api/projects` - Create a new project (code, name, domain, githubUrl, description). Automatically assigns creator as `MAINTAINER`. - `GET /api/projects/:id` - Get project details with associated board, repositories, agents, members, and task stats. - `PUT /api/projects/:id` - Update project details (MAINTAINER role required). - `DELETE /api/projects/:id` - Delete project (MAINTAINER role required). - `GET /api/projects/:id/members` - List project members and their roles. - `POST /api/projects/:id/members` - Add or update user role in project (`READ`, `DEVELOPER`, `MAINTAINER`). - `DELETE /api/projects/:id/members/:userId` - Remove member from project. ### 4.2 Master Plan & Tasks API - `GET /api/projects/:projectId/master-plan` - List tasks grouped by phase and status. - `POST /api/projects/:projectId/tasks` - Create a task assigned to project/board with phase tag. ### 4.3 Project Rules API - `GET /api/projects/:projectId/rules` - List all rule files (metadata: id, file_name, title, updated_at). - `GET /api/projects/:projectId/rules/:ruleId` - Get full rule file content. - `POST /api/projects/:projectId/rules` - Create a new rule file. - `PUT /api/projects/:projectId/rules/:ruleId` - Update rule file title/content/file_name. - `DELETE /api/projects/:projectId/rules/:ruleId` - Delete rule file. ### 4.4 Project Documents API - `GET /api/projects/:projectId/docs` - List all doc/knowledge files (metadata). - `GET /api/projects/:projectId/docs/:docId` - Get full document content. - `POST /api/projects/:projectId/docs` - Create a doc/knowledge file. - `PUT /api/projects/:projectId/docs/:docId` - Update doc/knowledge file. - `DELETE /api/projects/:projectId/docs/:docId` - Delete doc/knowledge file. ### 4.5 GitLab Enterprise Sync & Webhook API - `POST /api/webhooks/gitlab` - Universal webhook receiver for company GitLab instances: - **Merge Request Events**: Auto-links Merge Request URL, sets branch, updates Task status (`IN_REVIEW`, `DONE`). - **Pipeline Events**: Receives CI/CD status (passed/failed/running), attaches security scan summary to Task `qcChecks`. - **Push Events**: Syncs commit messages & authors into Task `changelog`. - `POST /api/projects/:projectId/gitlab/sync` - On-demand manual sync of commits, MRs, and pipelines from GitLab REST API. --- ## 5. Web Frontend Design & Component Structure ### 5.1 Route Structure & Backward Compatibility - `/` -> `ProjectsPage`: Home page listing all projects as cards. - `/projects/:projectId` -> `ProjectDetailPage`: Main dashboard for a project with tab navigation. - `/boards/:boardId` -> Redirects to `/projects/:projectId` (lookup project linked to `boardId`). ### 5.2 Project Detail Tabs - **Tab 1: 📝 Master Plan (`MasterPlanView`)**: - Displays tasks grouped by Phase (Milestones) and Status. - Allows quick adding/editing of tasks and assigning phases. - **Tab 2: 📋 Kanban Board (`KanbanView`)**: - Interactive drag-and-drop Kanban board (`BoardPage`). - **Tab 3: 📜 Rules (`RulesView`)**: - Split view: Left sidebar lists rule files (`.md`), Right pane provides Markdown Viewer & Live Editor. - **Tab 4: 📚 Tài liệu & Tri thức (`DocsView`)**: - Split view: Left sidebar lists documents & knowledge base files, Right pane provides Markdown Viewer & Live Editor. - **Tab 5: 🤖 Repos & Agents (`ReposAgentsView`)**: - Manage linked GitHub Repositories and AI Agents. - **Tab 6: ⚙️ Cài đặt (`SettingsView`)**: - Edit project info (name, code, domain, githubUrl, description) or delete project. --- ## 6. Implementation Milestones 1. **Database Migration**: Install PostgreSQL driver (`pg`), refactor `db.ts`, create PostgreSQL schema & update repository SQL queries. 2. **Backend API**: Add `projectRepo.ts`, `projectRulesRepo.ts`, `projectDocsRepo.ts` and API routes. 3. **Frontend Pages**: Create `ProjectsPage`, `ProjectDetailPage`, and tab subcomponents (`MasterPlanView`, `RulesView`, `DocsView`). 4. **Integration & Testing**: Verify database queries, API endpoints, backward route redirects, and end-to-end user workflows.