# PostgreSQL Chat Persistence Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Persist users, conversations, messages, and last selected model in PostgreSQL, and run the Next.js application with PostgreSQL through Docker Compose.

**Architecture:** A small `pg` data layer owns SQL, migrations, transactions, and repositories. Route Handlers authenticate JWT subjects against the database and make the database authoritative for chat history; the client consumes server conversations rather than local history.

**Tech Stack:** Next.js 16, TypeScript, PostgreSQL 16, `pg`, `bcryptjs`, Docker Compose, Vitest, React Testing Library.

## Global Constraints

- Work directly in `main`; do not create or use subagents.
- Do not stage pre-existing user changes in `app/globals.css`, `package.json`, `package-lock.json`, `components.json`, `components/ui/button.tsx`, or `lib/utils.ts`.
- PostgreSQL is published to the host only on `5433`, with user/password `ai-control-chat-ui` and database `ai_control_chat_ui`.
- JWT remains HS256, expires in 24 hours, and is sent only as `Authorization: Bearer <token>`.
- User JWT never reaches AI Gateway.
- Supported model IDs are exactly `chatgpt`, `deepseek`, and `qwen`.
- Message limits remain 100 messages, 32,000 characters per message, and 128,000 characters per context.
- SQL must use positional parameters; route handlers must not embed SQL.

---

### Task 1: Docker, Dependencies, Migrations, and Database Core

**Files:**

- Modify: `package.json`, `package-lock.json`, `.env.example`, `.gitignore`, `next.config.ts`
- Create: `Dockerfile`, `docker-compose.yml`, `docker-entrypoint.sh`
- Create: `db/migrations/001_initial_schema.sql`, `scripts/migrate.ts`, `scripts/seed.ts`
- Create: `lib/db/pool.ts`, `lib/db/migrate.ts`, `lib/db/types.ts`, `lib/db/migrate.test.ts`

**Interfaces:** Produces `getPool()`, `closePool()`, `applyMigrations(queryable, directory)`, and one idempotent seed script.

- [ ] **Step 1: Write the failing migration test**

```ts
it("applies each migration once in a transaction", async () => {
  const queryable = createRecordingQueryable();
  await applyMigrations(queryable, migrationsDirectory);
  expect(queryable.sql).toContain("BEGIN");
  expect(queryable.sql.filter((sql) => sql.includes("001_initial_schema.sql"))).toHaveLength(1);
});
```

- [ ] **Step 2: Verify RED**

Run `npm test -- lib/db/migrate.test.ts`. It must fail because the migration module does not exist.

- [ ] **Step 3: Implement the core**

Install `pg`, `bcryptjs`, and `@types/pg`. Define `Queryable`, a server-only `pg.Pool`, transaction-safe migration runner, schema, idempotent bcrypt seed, Node 22 standalone Dockerfile, and Compose services. Compose maps `5433:5432`, sets the requested credentials, waits on `pg_isready`, and starts the app after migration/seed.

- [ ] **Step 4: Verify and commit**

Run `npm test -- lib/db/migrate.test.ts` and `docker compose config`. Commit only task files with partial staging.

---

### Task 2: Repositories and Database-backed Authentication

**Files:**

- Create: `lib/db/users.ts`, `lib/db/conversations.ts`, `lib/db/messages.ts`
- Create: `lib/db/users.test.ts`, `lib/db/conversations.test.ts`, `lib/db/messages.test.ts`
- Modify: `app/api/auth/login/route.ts`, `app/api/auth/me/route.ts`, `lib/auth/server.ts`, `app/api/auth/routes.test.ts`

**Interfaces:** Produces `findUserByEmail`, `findUserById`, `verifyUserPassword`, `updateLastModel`, `listConversations`, `findOwnedConversation`, `appendMessage`, `finalizeMessage`, and `buildServerContext`.

- [ ] **Step 1: Write failing repository/auth tests**

```ts
it("does not return a conversation owned by another user", async () => {
  await expect(repository.findOwnedConversation("mine", "other")).resolves.toBeNull();
});

it("issues a JWT only after database bcrypt authentication", async () => {
  const response = await login(requestWithCredentials());
  expect((await response.json()).user).toEqual({ id: "user-id", email: "demo@ai-control.local" });
});
```

- [ ] **Step 2: Verify RED**

Run `npm test -- lib/db/users.test.ts lib/db/conversations.test.ts lib/db/messages.test.ts app/api/auth/routes.test.ts`. It must fail because repositories do not exist and login uses environment credentials.

- [ ] **Step 3: Implement repositories and login/me migration**

Use only `$1`-style parameterized SQL. Repository mappers never expose password hashes. `POST /api/auth/login` verifies bcrypt password through users repository. `GET /api/auth/me` verifies JWT and confirms current database identity by user ID and email.

- [ ] **Step 4: Verify and commit**

Run the same focused repository/auth test command. Commit only repository, auth, and test files.

---

### Task 3: Conversations, Model Preference, and Persistent Streaming Route

**Files:**

- Create: `app/api/conversations/route.ts`, `app/api/conversations/route.test.ts`
- Create: `app/api/me/model/route.ts`, `app/api/me/model/route.test.ts`
- Modify: `app/api/chat/route.ts`, `app/api/chat/route.test.ts`, `lib/chat/gateway.ts`, `lib/chat/validation.ts`, `lib/chat/types.ts`

**Interfaces:** Produces `GET /api/conversations`, `PATCH /api/me/model`, and persistent `POST /api/chat` accepting `{ conversationId, model, content?, retry? }`.

- [ ] **Step 1: Write failing API tests**

```ts
it("rejects a conversation owned by another user before invoking Gateway", async () => {
  const response = await postChat(otherUsersConversationRequest());
  expect(response.status).toBe(404);
  expect(streamGatewayResponse).not.toHaveBeenCalled();
});

it("persists a completed assistant stream", async () => {
  const response = await postChat(ownedMessageRequest());
  await response.text();
  expect(messages.finalizeMessage).toHaveBeenCalledWith(expect.any(String), "complete", "ответ");
});
```

- [ ] **Step 2: Verify RED**

Run `npm test -- app/api/conversations/route.test.ts app/api/me/model/route.test.ts app/api/chat/route.test.ts`. It must fail because routes are missing or chat accepts untrusted client history.

- [ ] **Step 3: Implement server-authoritative API**

Conversations route authenticates, repairs persisted `streaming` to `stopped`, and returns owner data. Model route validates one supported model and stores `users.last_model_id`. Chat route creates/persists user and assistant shell records, builds server-side newest context within exact limits, streams Gateway output while collecting it, and finalizes `complete`, `stopped`, or safe `error`. Retry reuses only the immediate preceding complete user message of the final retryable assistant record.

- [ ] **Step 4: Verify and commit**

Run the Task 3 focused command. Commit only persistent API files and tests.

---

### Task 4: Server-authoritative Client and End-to-end Verification

**Files:**

- Modify: `hooks/use-chat.ts`, `hooks/use-chat.test.tsx`, `components/auth/auth-gate.tsx`, `components/chat/chat-app.tsx`, `components/chat/chat-app.test.tsx`, `README.md`, `.env.example`
- Create: `scripts/smoke-db.sh`

**Interfaces:** Consumes `GET /api/conversations`, `PATCH /api/me/model`, persistent `POST /api/chat`; produces UI hydrated from database, not `localStorage` history.

- [ ] **Step 1: Write failing client tests**

```tsx
it("hydrates chats and selected model from the authenticated server", async () => {
  vi.mocked(authFetch).mockResolvedValueOnce(jsonResponse({ conversations: [conversation] }));
  render(<ChatApp user={user} onLogout={vi.fn()} />);
  expect(await screen.findByText(conversation.title)).toBeInTheDocument();
});

it("persists model selection before creating a new chat", async () => {
  await user.click(screen.getByRole("button", { name: "Qwen" }));
  expect(authFetch).toHaveBeenCalledWith("/api/me/model", expect.objectContaining({ method: "PATCH" }));
});
```

- [ ] **Step 2: Verify RED**

Run `npm test -- hooks/use-chat.test.tsx components/chat/chat-app.test.tsx`. It must fail because chat state still uses device-local history.

- [ ] **Step 3: Implement hydration and reconciliation**

After auth, load server conversations and last model. Send only `{ conversationId, model, content }` or `{ conversationId, retry: true }`. On API failure reload server history. Remove local history/model persistence while retaining local JWT storage.

- [ ] **Step 4: Verify Docker and commit**

Run `npm test`, `npm run lint`, `npx tsc --noEmit`, `npm run build`, `docker compose config`, `docker compose up --build -d`, `./scripts/smoke-db.sh`, and `docker compose down --volumes`. Commit only Task 4 files and report any Docker sandbox limitation exactly.