aegida-console / docs / superpowers / plans / 2026-07-26-postgresql-persistence.md
2026-07-26-postgresql-persistence.md
Raw

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
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
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
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
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.

Powered by GitFront