merna111/sequencejira
0
1# SequenceJira: System Architecture & Technical Specification2 3This document provides a production-grade, deeply technical architectural specification for **SequenceJira**—an AI-first, real-time task management SaaS designed for developers and product teams. It covers multi-tenancy isolation, multi-agent AI pipeline design, WebSocket-based real-time state synchronization, developer-centric webhook integrations, and a relational PostgreSQL database schema.4 5---6 7## System Topology & Data Flow8 9Below is the high-level system topology illustrating the flow of client requests, background AI processing, real-time notifications, and Git integrations.10 11```mermaid12graph TD13 %% Clients14 Client[Next.js Frontend] -->|HTTPS Requests| API[NestJS API Gateway]15 Client -->|WS Connections| WSS[NestJS WebSocket Gateway]16 17 %% Middleware & Auth18 subgraph Gateway [NestJS API Gateway & Middleware]19 Auth[JWT / RBAC Guard]20 ALS[AsyncLocalStorage Tenant Context]21 API --> Auth22 Auth --> ALS23 end24 25 %% DB & Caching26 subgraph Data Tier [Primary Databases & Caching]27 PG[(PostgreSQL Primary)]28 RLS[Row Level Security Policies]29 Redis[(Redis Caching & Pub/Sub)]30 PG --- RLS31 end32 ALS -->|Injected workspace_id| PG33 34 %% Message Broker35 subgraph Message Broker [RabbitMQ Event Broker]36 EX[Topic Exchange]37 QueueAI[ai.task.breakdown Queue]38 QueueGit[git.webhook.cleanup Queue]39 EX --> QueueAI40 EX --> QueueGit41 end42 API -->|Publish AI Job| EX43 44 %% AI Pipeline45 subgraph AI Pipeline [Multi-Agent Processing Engine]46 A1[Agent 1: Requirements Analyzer]47 A2[Agent 2: Epic & Story Generator]48 A3[Agent 3: Validator & Auto-Corrector]49 Worker[AI Worker Service]50 51 Worker --> A152 A1 --> A253 A2 --> A354 A3 -->|Failed| A255 A3 -->|Passed| Worker56 end57 QueueAI --> Worker58 Worker -->|Save Results| PG59 Worker -->|Publish Event| Redis60 61 %% WebSocket Sync62 Redis -->|Pub/Sub Event| WSS63 WSS -->|Broadcast state change| Client64 65 %% Webhook Integration66 GitHub[GitHub Webhook Event] -->|Signature verified HMAC-SHA256| API67 API -->|State Transition| PG68 API -->|Cleanup trigger| EX69```70 71---72 73## 1. Core Architecture & Multi-Tenancy74 75SequenceJira utilizes a **Logical Separation** multi-tenancy model. All tenants (Workspaces) share the same PostgreSQL database instance and schemas, but data isolation is strictly enforced at the database query level using a `workspace_id` foreign key on every tenant-owned table.76 77### 1.1 Tenant Isolation with `AsyncLocalStorage`78 79To prevent data leaks (cross-tenant contamination), developers should not manually append `WHERE workspace_id = x` filters to every SQL query. Instead, the request lifecycle is intercepted to dynamically extract and bind the tenant context.80 81#### Request Flow:821. **Extraction**: An HTTP middleware intercepts incoming requests and extracts the `workspace_id` from either a customized header (`x-workspace-id`) or decodes the authenticated user's JWT payload containing their active memberships.832. **Context Binding**: The middleware instantiates a context object and stores it within Node's `AsyncLocalStorage` execution context.843. **Automated Query Scoping**: An ORM interceptor (e.g., Prisma Client Extensions or TypeORM Subscriber) retrieves the current `workspace_id` from storage and automatically modifies the outbound database queries.85 86#### Execution Context Middleware (TypeScript / NestJS Example)87```typescript88// tenant.context.ts89import { AsyncLocalStorage } from 'async_hooks';90 91export interface TenantContext {92 workspaceId: string;93 userId: string;94}95 96export const tenantStorage = new AsyncLocalStorage<TenantContext>();97```98 99```typescript100// tenant.middleware.ts101import { Injectable, NestMiddleware, UnauthorizedException } from '@nestjs/common';102import { Request, Response, NextFunction } from 'express';103import { tenantStorage } from './tenant.context';104import * as jwt from 'jsonwebtoken';105 106@Injectable()107export class TenantMiddleware implements NestMiddleware {108 use(req: Request, res: Response, next: NextFunction) {109 const authHeader = req.headers.authorization;110 const workspaceIdHeader = req.headers['x-workspace-id'] as string;111 112 if (!authHeader || !authHeader.startsWith('Bearer ')) {113 return next(); // Let AuthGuard handle unauthenticated requests114 }115 116 try {117 const token = authHeader.split(' ')[1];118 const decoded = jwt.verify(token, process.env.JWT_SECRET) as any;119 const userId = decoded.sub;120 121 // Ensure the user has access to the requested workspace122 const userWorkspaces = decoded.workspaces || [];123 if (workspaceIdHeader && !userWorkspaces.includes(workspaceIdHeader)) {124 throw new UnauthorizedException('Access to this workspace is denied.');125 }126 127 const context: TenantContext = {128 workspaceId: workspaceIdHeader || userWorkspaces[0], // fallback to default129 userId,130 };131 132 // Run subsequent handlers inside the AsyncLocalStorage context133 tenantStorage.run(context, () => {134 next();135 });136 } catch (err) {137 throw new UnauthorizedException('Invalid or expired authentication session.');138 }139 }140}141```142 143#### Scoping Queries dynamically via PostgreSQL Row Level Security (RLS)144For absolute safety, Row Level Security is enabled directly in the PostgreSQL engine:145 146```sql147-- 1. Enable RLS on the tasks table148ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;149 150-- 2. Create the isolation policy151CREATE POLICY task_workspace_isolation ON tasks152 FOR ALL153 USING (workspace_id = NULLIF(current_setting('app.current_workspace_id', true), '')::uuid);154```155 156Before executing any database transaction, the application pool must bind the local tenant parameter:157```typescript158// db-transaction.ts159import { PrismaClient } from '@prisma/client';160import { tenantStorage } from './tenant.context';161 162const prisma = new PrismaClient();163 164export async function executeTenantQuery<T>(queryFn: (tx: any) => Promise<T>): Promise<T> {165 const context = tenantStorage.getStore();166 if (!context) {167 throw new Error('Tenant context missing in current thread execution.');168 }169 170 return await prisma.$transaction(async (tx) => {171 // Inject tenant identity into the local connection session context172 await tx.$executeRawUnsafe(173 `SET LOCAL app.current_workspace_id = '${context.workspaceId}';`174 );175 return await queryFn(tx);176 });177}178```179 180---181 182### 1.2 Role-Based Access Control (RBAC) Matrix183 184Users belong to Workspaces through a `workspace_members` join table, which holds a designated `role` column. The following matrix outlines permission enforcement levels:185 186| Feature / Resource Action | Owner | Admin | Developer | Client / Viewer |187| :--- | :---: | :---: | :---: | :---: |188| **Manage Billing & Delete Workspace** | Yes | No | No | No |189| **Manage Members & Change Roles** | Yes | Yes | No | No |190| **Integrate GitHub (Token Config)** | Yes | Yes | No | No |191| **Create/Edit Projects & Epics** | Yes | Yes | Yes | No |192| **Create/Assign/Update Tasks** | Yes | Yes | Yes | No |193| **Comment on Tasks** | Yes | Yes | Yes | Yes |194| **Trigger Multi-Agent AI Generation** | Yes | Yes | Yes | No |195| **Read Tasks & View Boards** | Yes | Yes | Yes | Yes |196 197#### Permission Guard Implementation Strategy198```typescript199// roles.decorator.ts200import { SetMetadata } from '@nestjs/common';201export const Roles = (...roles: string[]) => SetMetadata('roles', roles);202```203 204```typescript205// rbac.guard.ts206import { Injectable, CanActivate, ExecutionContext, ForbiddenException } from '@nestjs/common';207import { Reflector } from '@nestjs/core';208import { PrismaService } from '../prisma.service';209import { tenantStorage } from './tenant.context';210 211@Injectable()212export class RbacGuard implements CanActivate {213 constructor(214 private reflector: Reflector,215 private prisma: PrismaService216 ) {}217 218 async canActivate(context: ExecutionContext): Promise<boolean> {219 const requiredRoles = this.reflector.getAllAndOverride<string[]>('roles', [220 context.getHandler(),221 context.getClass(),222 ]);223 if (!requiredRoles) return true; // Public or unrestricted resource224 225 const store = tenantStorage.getStore();226 if (!store) throw new ForbiddenException('Tenant context not resolved.');227 228 // Fetch user's membership role in the specific workspace229 const member = await this.prisma.workspaceMember.findUnique({230 where: {231 workspaceId_userId: {232 workspaceId: store.workspaceId,233 userId: store.userId,234 },235 },236 });237 238 if (!member || !requiredRoles.includes(member.role)) {239 throw new ForbiddenException('Insufficient permissions within this workspace.');240 }241 242 return true;243 }244}245```246 247---248 249## 2. The Multi-Agent AI Pipeline (The USP)250 251The core value proposition is the ability to ingest a single, raw, unstructured product feature statement (e.g., *"Add Stripe integration for monthly subscriptions with a 14-day trial period"*) and generate detailed, architectural tasks, epics, and subtasks complete with technical scope and verification guidelines.252 253This is modeled as an **event-driven, asynchronous Multi-Agent process** mediated by a queue system to keep the web application highly responsive.254 255### 2.1 Agent Pipeline Architecture256 257```258User Prompt (e.g., "Add Stripe") 259 │260 ▼261[API Gateway] ──► Pushes job state "PENDING" to PG ──► Publishes payload to RabbitMQ262 │263 ▼264 [RabbitMQ Consumer]265 │266 ▼267 ┌───────────────────────────────┐268 │ AI Worker Service │269 │ │270 │ [Agent 1: Requirements] │271 │ │ │272 │ ▼ │273 │ [Agent 2: Epic & Stories] │274 │ │ │275 │ ▼ │276 │ [Agent 3: Validator] │277 │ ├──► (Fails Validation) ┘ (Loop back)278 │ └──► (Passes) ┐279 └───────────────────────────────┼280 ▼281 Update PG Status to "COMPLETED"282 Store Epics & Tasks283 Publish notification to Redis284 │285 ▼286 Redis Pub/Sub triggering287 WebSocket Gateway response288 │289 ▼290 Client UI Board auto-updates291```292 293---294 295### 2.2 Agent Detail Breakdowns296 297#### Agent 1: Requirements Analyzer298* **Role**: Technical Product Owner & Architect.299* **Prompt Strategy**: Parses the raw input to generate technical scopes. It maps out dependencies, potential data migrations, security requirements, and UI modifications.300* **Output**: Structured markdown including architectural impacts and required code layers.301 302#### Agent 2: Task Breakdown & Epic Generator303* **Role**: Senior Engineering Lead.304* **Prompt Strategy**: Consumes the output of Agent 1 and breaks it down into distinct Epics (broad features) and User Stories.305* **Structured Output (JSON Schema)**:306```json307{308 "$schema": "http://json-schema.org/draft-07/schema#",309 "title": "EpicAndTaskBreakdown",310 "type": "object",311 "properties": {312 "epics": {313 "type": "array",314 "items": {315 "type": "object",316 "properties": {317 "title": { "type": "string" },318 "description": { "type": "string" },319 "tasks": {320 "type": "array",321 "items": {322 "type": "object",323 "properties": {324 "title": { "type": "string" },325 "description": { "type": "string" },326 "technicalNotes": { "type": "string" },327 "estimatedPoints": { "type": "integer", "enum": [1, 2, 3, 5, 8, 13] },328 "priority": { "type": "string", "enum": ["LOW", "MEDIUM", "HIGH", "CRITICAL"] },329 "acceptanceCriteria": {330 "type": "array",331 "items": { "type": "string" }332 }333 },334 "required": ["title", "description", "technicalNotes", "estimatedPoints", "priority", "acceptanceCriteria"]335 }336 }337 },338 "required": ["title", "description", "tasks"]339 }340 }341 },342 "required": ["epics"]343}344```345 346#### Agent 3: Targeted Self-Correction & Validation347* **Role**: QA & Security Architect.348* **Execution Logic**:349 1. Validates structural adherence to the schema.350 2. Executes structural validation: checks for missing technical dependencies (e.g., if a task mentions database writes, there must be a preceding migration task).351 3. Evaluates security/privacy gaps (e.g., checking if database secrets are logged).352 4. If validation fails: The agent dynamically produces a `reconciliation_prompt` stating the failures (e.g., *"Validation Error: Task 'Integrate Stripe SDK' assumes database tables exist, but no migration task was generated"*).353 5. The validation error is passed back to **Agent 2** along with its original state to regenerate the layout. A limit of 3 retries is enforced before failing the job.354 355---356 357### 2.3 Message Queueing & Notification System Design358 3591. **Job Enqueueing**: The user hits `POST /api/tasks/generate-ai`. The API logs a record in the database table `ai_jobs` with status `PROCESSING` and publishes a message to RabbitMQ:360 * **Exchange**: `ai.exchange` (Type: Direct)361 * **Routing Key**: `task.generation`362 * **Message Body**: `{ jobId: "uuid-123", workspaceId: "uuid-ws-12", prompt: "..." }`3632. **Worker Processing**: The AI worker processes the job, interacting with the LLM API using structured tool calling via the OpenAI/Anthropic SDK.3643. **Persisting State**: Once output passes validation, the worker creates the `Epics` and `Tasks` inside a database transaction, updating the status of the `ai_jobs` record to `COMPLETED`.3654. **Pub/Sub & WebSocket Broadcast**:366 * The worker publishes an event to Redis: `PUBLISH workspace:uuid-ws-12:notifications '{"type": "AI_JOB_COMPLETED", "jobId": "uuid-123"}'`367 * The WebSocket server listening to Redis Pub/Sub receives the event and sends a socket frame to all clients joined to room `workspace:uuid-ws-12`.368 369---370 371## 3. Real-Time Engine & Component Sync372 373To emulate a highly dynamic environment, UI state updates (such as dragging cards on a Kanban board) are broadcast in real-time across workspace members.374 375### 3.1 WebSockets & Room Management (Socket.io Architecture)376 377Upon establishing a WebSocket connection, the client sends their JWT token. The server verifies this token, extracts the workspace IDs the user belongs to, and joins them to target rooms.378 379```typescript380// events.gateway.ts381import {382 WebSocketGateway,383 WebSocketServer,384 SubscribeMessage,385 OnGatewayConnection,386 ConnectedSocket,387 MessageBody,388} from '@nestjs/websockets';389import { Server, Socket } from 'socket.io';390import * as jwt from 'jsonwebtoken';391 392@WebSocketGateway({ cors: { origin: '*' } })393export class EventsGateway implements OnGatewayConnection {394 @WebSocketServer()395 server: Server;396 397 async handleConnection(client: Socket) {398 try {399 const authHeader = client.handshake.headers.authorization;400 if (!authHeader) throw new Error('Authorization header missing.');401 402 const token = authHeader.split(' ')[1];403 const decoded = jwt.verify(token, process.env.JWT_SECRET) as any;404 405 // Attach session info to socket406 client.data.userId = decoded.sub;407 client.data.workspaces = decoded.workspaces;408 409 // Join rooms for all workspaces user belongs to410 for (const workspaceId of decoded.workspaces) {411 client.join(`workspace:${workspaceId}`);412 }413 } catch (err) {414 client.disconnect(true);415 }416 }417 418 @SubscribeMessage('task:move')419 async handleTaskMove(420 @ConnectedSocket() client: Socket,421 @MessageBody() data: { taskId: string; workspaceId: string; newStatus: string; currentVersion: number }422 ) {423 // Validate membership424 if (!client.rooms.has(`workspace:${data.workspaceId}`)) {425 return { event: 'error', data: 'Unauthorized workspace channel.' };426 }427 428 // Broadcast change to other workspace members, excluding sender429 client.to(`workspace:${data.workspaceId}`).emit('task:moved', {430 taskId: data.taskId,431 newStatus: data.newStatus,432 updatedBy: client.data.userId,433 });434 }435}436```437 438---439 440### 3.2 Race Condition Mitigations441 442When multiple developers interact with the same board, race conditions occur if two individuals modify the same record concurrently.443 444#### Mitigation A: Optimistic Locking (Recommended)445Each task has an integer `version` field. When an update request is sent, the client includes the version it read.446 447```typescript448// task.service.ts449import { ConflictException } from '@nestjs/common';450 451async function updateTaskStatus(452 taskId: string,453 newStatus: string,454 clientSideVersion: number455): Promise<any> {456 return await executeTenantQuery(async (tx) => {457 // Attempt conditional update458 const updated = await tx.task.updateMany({459 where: {460 id: taskId,461 version: clientSideVersion, // Ensure no updates happened in the interim462 },463 data: {464 status: newStatus,465 version: { increment: 1 }, // Increment version atomically466 },467 });468 469 if (updated.count === 0) {470 // Version changed in database or record missing471 throw new ConflictException(472 'Task modification failed. The task has been modified by another developer.'473 );474 }475 476 return tx.task.findUnique({ where: { id: taskId } });477 });478}479```480 481If a client receives a `409 Conflict` response, the UI rolls back the visual drag animation and fires a toast notification requesting a task state refresh.482 483#### Mitigation B: Event-Based Sequencing via Redis Streams484For task operations requiring order guarantees (e.g., task sorting rank values), all updates are written to a Redis Stream (`workspace:ws-id:actions`).485* A consumer service processes the stream sequentially.486* It executes rank calculations in sequence, avoiding race conditions and decimal precision errors.487 488---489 490## 4. Dev-Centric Webhook Integration (GitHub Flow)491 492Developers interact with tasks by prefixing branches and commits with Task short IDs (e.g., `SEQ-105-stripe-webhooks`). The backend hooks into GitHub webhook events to automate state changes.493 494### 4.1 HMAC-SHA256 Signature Verification495To prevent spoofing attacks, all requests arriving at `/api/webhooks/github` are validated against GitHub's signature header using the configured webhook secret.496 497```typescript498// github-webhook.guard.ts499import { Injectable, CanActivate, ExecutionContext, UnauthorizedException } from '@nestjs/common';500import * as crypto from 'crypto';501 502@Injectable()503export class GithubWebhookGuard implements CanActivate {504 canActivate(context: ExecutionContext): boolean {505 const request = context.switchToHttp().getRequest();506 const signature = request.headers['x-hub-signature-256'] as string;507 508 if (!signature) {509 throw new UnauthorizedException('Signature header missing.');510 }511 512 const payload = JSON.stringify(request.body);513 const secret = process.env.GITHUB_WEBHOOK_SECRET;514 515 const hmac = crypto.createHmac('sha256', secret);516 const digest = 'sha256=' + hmac.update(payload).digest('hex');517 518 // Use safe timing comparison to mitigate side-channel timing attacks519 const isValid = crypto.timingSafeEqual(520 Buffer.from(signature),521 Buffer.from(digest)522 );523 524 if (!isValid) {525 throw new UnauthorizedException('Invalid payload signature.');526 }527 528 return true;529 }530}531```532 533---534 535### 4.2 Webhook Payload Processing Code & State Transitions536 537When a PR is opened or merged, the task short identifier (configured via projects e.g. `SEQ`) is matched.538 539```typescript540// github-webhook.service.ts541import { Injectable } from '@nestjs/common';542import { PrismaService } from '../prisma.service';543import { AmqpConnection } from '@golevelup/nestjs-rabbitmq';544 545@Injectable()546export class GithubWebhookService {547 constructor(548 private prisma: PrismaService,549 private rabbitMQ: AmqpConnection550 ) {}551 552 async processWebhook(event: string, payload: any) {553 if (event !== 'pull_request') return;554 555 const action = payload.action; // 'opened', 'closed', 'reopened'556 const pullRequest = payload.pull_request;557 const title = pullRequest.title;558 const branchName = pullRequest.head.ref;559 const prUrl = pullRequest.html_url;560 const merged = pullRequest.merged;561 562 // Scan text interfaces for task references matching PROJECT-NUMBER (e.g. SEQ-105)563 const taskKeyPattern = /[A-Z]{2,10}-\d+/g;564 const matchedKeys = new Set([565 ...(title.match(taskKeyPattern) || []),566 ...(branchName.match(taskKeyPattern) || []),567 ...(pullRequest.body?.match(taskKeyPattern) || [])568 ]);569 570 if (matchedKeys.size === 0) return;571 572 for (const key of matchedKeys) {573 const task = await this.prisma.task.findUnique({574 where: { key },575 include: { project: true }576 });577 578 if (!task) continue;579 580 if (action === 'opened' || action === 'reopened') {581 // Transition Task to "IN_REVIEW"582 await this.prisma.task.update({583 where: { id: task.id },584 data: {585 status: 'IN_REVIEW',586 pullRequestUrl: prUrl587 }588 });589 590 // Write Audit Log591 await this.logEvent(task, 'GITHUB_PR_OPENED', `GitHub PR opened: ${prUrl}`);592 } 593 else if (action === 'closed' && merged === true) {594 // Transition Task to "DONE"595 await this.prisma.task.update({596 where: { id: task.id },597 data: { status: 'DONE' }598 });599 600 await this.logEvent(task, 'GITHUB_PR_MERGED', `PR merged into primary branch: ${prUrl}`);601 602 // Queue asynchronous branch environment cleanup task603 await this.rabbitMQ.publish(604 'git.exchange',605 'webhook.cleanup',606 {607 taskId: task.id,608 branchName,609 workspaceId: task.workspaceId610 }611 );612 }613 }614 }615 616 private async logEvent(task: any, actionType: string, description: string) {617 await this.prisma.auditLog.create({618 data: {619 workspaceId: task.workspaceId,620 actorType: 'SYSTEM_WEBHOOK',621 actionType,622 entityName: 'tasks',623 entityId: task.id,624 newValues: { status: task.status, detail: description }625 }626 });627 }628}629```630 631---632 633## 5. Database Schema & Graph (PostgreSQL DDL)634 635To implement this structure with strict referential integrity, indexes, and cascades, we construct the tables using standard PostgreSQL DDL syntax.636 637```sql638-- Create custom enums639CREATE TYPE user_role AS ENUM ('OWNER', 'ADMIN', 'DEVELOPER', 'CLIENT');640CREATE TYPE task_priority AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL');641CREATE TYPE task_status AS ENUM ('BACKLOG', 'TODO', 'IN_PROGRESS', 'IN_REVIEW', 'DONE');642 643-- 1. WORKSPACES644CREATE TABLE workspaces (645 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),646 name VARCHAR(255) NOT NULL,647 slug VARCHAR(100) UNIQUE NOT NULL,648 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,649 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP650);651 652CREATE INDEX idx_workspaces_slug ON workspaces(slug);653 654-- 2. USERS655CREATE TABLE users (656 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),657 email VARCHAR(255) UNIQUE NOT NULL,658 password_hash VARCHAR(255) NOT NULL,659 full_name VARCHAR(255) NOT NULL,660 avatar_url VARCHAR(512),661 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,662 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP663);664 665CREATE INDEX idx_users_email ON users(email);666 667-- 3. WORKSPACE_MEMBERS (Join table with roles)668CREATE TABLE workspace_members (669 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),670 workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,671 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,672 role user_role NOT NULL DEFAULT 'DEVELOPER',673 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,674 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,675 CONSTRAINT uq_workspace_user UNIQUE (workspace_id, user_id)676);677 678CREATE INDEX idx_workspace_members_user ON workspace_members(user_id);679CREATE INDEX idx_workspace_members_composite ON workspace_members(workspace_id, role);680 681-- 4. PROJECTS682CREATE TABLE projects (683 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),684 workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,685 name VARCHAR(255) NOT NULL,686 key_prefix VARCHAR(10) NOT NULL, -- e.g., 'SEQ', 'JIRA'687 description TEXT,688 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,689 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,690 CONSTRAINT uq_project_key_per_workspace UNIQUE (workspace_id, key_prefix)691);692 693CREATE INDEX idx_projects_workspace ON projects(workspace_id);694 695-- 5. EPICS696CREATE TABLE epics (697 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),698 workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,699 project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,700 title VARCHAR(255) NOT NULL,701 description TEXT,702 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,703 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP704);705 706CREATE INDEX idx_epics_project ON epics(project_id);707 708-- 6. TASKS (Self-referencing parent_id hierarchy)709CREATE TABLE tasks (710 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),711 workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,712 project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,713 epic_id UUID REFERENCES epics(id) ON DELETE SET NULL,714 parent_id UUID REFERENCES tasks(id) ON DELETE CASCADE, -- Task Hierarchy715 716 key VARCHAR(30) UNIQUE NOT NULL, -- Project key prefix + index: 'SEQ-104'717 title VARCHAR(255) NOT NULL,718 description TEXT NOT NULL,719 status task_status NOT NULL DEFAULT 'TODO',720 priority task_priority NOT NULL DEFAULT 'MEDIUM',721 story_points INTEGER DEFAULT 1,722 723 version INTEGER NOT NULL DEFAULT 1, -- Optimistic locking724 pull_request_url VARCHAR(512),725 726 assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,727 reporter_id UUID REFERENCES users(id) ON DELETE SET NULL,728 729 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,730 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP731);732 733CREATE INDEX idx_tasks_workspace ON tasks(workspace_id);734CREATE INDEX idx_tasks_project_key ON tasks(project_id, key);735CREATE INDEX idx_tasks_epic ON tasks(epic_id);736CREATE INDEX idx_tasks_parent ON tasks(parent_id);737CREATE INDEX idx_tasks_status ON tasks(status);738 739-- 7. TASK DEPENDENCIES (Many-to-Many self-referential graph for blocker mapping)740CREATE TABLE task_dependencies (741 blocking_task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,742 blocked_task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,743 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,744 PRIMARY KEY (blocking_task_id, blocked_task_id),745 CONSTRAINT chk_no_self_blocking CHECK (blocking_task_id <> blocked_task_id)746);747 748CREATE INDEX idx_dependencies_blocked ON task_dependencies(blocked_task_id);749 750-- 8. COMMENTS751CREATE TABLE comments (752 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),753 workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,754 task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,755 author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,756 body TEXT NOT NULL,757 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,758 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP759);760 761CREATE INDEX idx_comments_task ON comments(task_id);762 763-- 9. AUDIT LOGS (JSONB schema modifications tracking)764CREATE TABLE audit_logs (765 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),766 workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,767 actor_id UUID REFERENCES users(id) ON DELETE SET NULL,768 actor_type VARCHAR(50) NOT NULL, -- 'USER' | 'SYSTEM_AI' | 'SYSTEM_WEBHOOK'769 action_type VARCHAR(100) NOT NULL, -- 'TASK_CREATED', 'TASK_MOVED', 'GITHUB_PR_MERGED'770 entity_name VARCHAR(100) NOT NULL, -- 'tasks', 'projects', 'members'771 entity_id UUID NOT NULL,772 old_values JSONB,773 new_values JSONB,774 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP775);776 777CREATE INDEX idx_audit_logs_workspace_entity ON audit_logs(workspace_id, entity_name, entity_id);778 779-- 10. GIT INTEGRATIONS (Tokens kept secure)780CREATE TABLE git_integrations (781 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),782 workspace_id UUID UNIQUE NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,783 github_app_installation_id VARCHAR(255),784 encrypted_access_token BYTEA NOT NULL, -- AES-256 encrypted string785 webhook_secret VARCHAR(255) NOT NULL,786 repository_name VARCHAR(255) NOT NULL,787 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,788 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP789);790```791