205 lines
8.1 KiB
Markdown
205 lines
8.1 KiB
Markdown
# Docker Migration + Redis + AI Context Cache — Implementation Plan
|
|
|
|
**Goal:** Migrate PostgreSQL to Docker · Add Redis · Build AI Steward global context cache
|
|
**Order:** Docker setup → data migration → Redis integration → context cache
|
|
|
|
---
|
|
|
|
## Part 1 — Docker Setup
|
|
|
|
### Services to containerize
|
|
- **PostgreSQL** — use `ankane/pgvector` image (includes pgvector extension, no manual install)
|
|
- **Redis** — use `redis:7-alpine` image, lightweight, set max memory 512mb, eviction policy `allkeys-lru`
|
|
- **Backend** — FastAPI app
|
|
- **Frontend** — React/Vite app
|
|
|
|
### Configuration
|
|
- All secrets (DB password, Redis password, GLM API key) in `.env` file, never committed to Git
|
|
- Services communicate via Docker internal network (backend connects to `db:5432`, not `localhost:5432`)
|
|
- All data volumes mounted to named Docker volumes (data persists across container restarts)
|
|
- Health checks on DB and Redis — backend only starts after both pass
|
|
|
|
---
|
|
|
|
## Part 2 — Database Migration Steps
|
|
|
|
1. Export existing local PostgreSQL data to a `.sql` backup file
|
|
2. Start Docker DB container
|
|
3. Wait for health check to pass
|
|
4. Import backup into Docker container
|
|
5. Enable pgvector extension inside Docker DB
|
|
6. Verify row counts match original
|
|
7. Update backend `DATABASE_URL` to point to Docker container
|
|
8. Run backend, confirm everything works
|
|
9. Stop local PostgreSQL service
|
|
|
|
---
|
|
|
|
## Part 3 — Redis Key Design
|
|
|
|
| Key pattern | Content | TTL |
|
|
|-------------|---------|-----|
|
|
| `pmflow:context:system` | System-wide snapshot (all projects, team, signals) | 30 min |
|
|
| `pmflow:context:user:{id}` | Per-user snapshot (my tasks, my signals, my workload) | 5 min |
|
|
| `pmflow:qa:session:{id}` | Conversation history for multi-turn chat (last 10 turns) | 1 hour |
|
|
| `pmflow:project:list` | Project list for quick lookup | 10 min |
|
|
| `pmflow:signal:pending_count` | Pending signal count by priority | 5 min |
|
|
|
|
### Cache invalidation rules
|
|
- Project created/updated → delete `pmflow:context:system`
|
|
- Signal status changed → delete `pmflow:context:system`
|
|
- Task assigned/completed → delete `pmflow:context:user:{affected_user_id}`
|
|
- Team member added/removed → delete `pmflow:context:system`
|
|
- TTL acts as safety net for any missed invalidations
|
|
|
|
---
|
|
|
|
## Part 4 — AI Context Cache
|
|
|
|
### System snapshot (shared by all users)
|
|
- All projects: id / name / status / current stage / owner / signal count / task count / completion rate
|
|
- All team members: id / name / role / workload ratio / assigned projects
|
|
- Pending signals: total / P0 count / P1 count / P2 count
|
|
- Snapshot build timestamp
|
|
|
|
### User snapshot (per user)
|
|
- Projects I own or am a member of
|
|
- Tasks assigned to me (not done, with due dates)
|
|
- Signals pending my action
|
|
- My current workload ratio
|
|
|
|
### How it works
|
|
1. User opens AI Steward chat
|
|
2. Backend checks Redis for system + user snapshots
|
|
3. Cache hit → use immediately (milliseconds)
|
|
4. Cache miss → query DB, build snapshot, store in Redis, use result
|
|
5. Both snapshots injected into GLM-4 System Prompt
|
|
6. GLM-4 answers based on real data — no more "I couldn't find information"
|
|
|
|
### System Prompt structure
|
|
- Role definition: "You are PMFlow's AI Steward..."
|
|
- System snapshot injected as structured data
|
|
- User personal snapshot injected
|
|
- Rules: answer directly from data / never fabricate / handle identity questions without searching / indicate when deeper search is needed
|
|
|
|
---
|
|
|
|
## Part 5 — Multi-turn Conversation Cache
|
|
|
|
- Every user message + AI response appended to Redis session key
|
|
- Max 20 messages (10 turns) per session — oldest trimmed automatically
|
|
- Session expires after 1 hour of inactivity
|
|
- Permanent QA records still written to PostgreSQL asynchronously (not affected by Redis TTL)
|
|
- If Redis restarts: session history resets (acceptable), permanent records safe in PostgreSQL
|
|
|
|
---
|
|
|
|
## Part 6 — Full Call Flow
|
|
|
|
```
|
|
User sends question
|
|
↓
|
|
Load system snapshot from Redis (cache hit: <5ms)
|
|
Load user snapshot from Redis (cache hit: <5ms)
|
|
Load session history from Redis
|
|
↓
|
|
Build: [system prompt + snapshots] + [history] + [new question]
|
|
↓
|
|
Call GLM-4
|
|
↓
|
|
Append messages to Redis session
|
|
Async write QA record to PostgreSQL
|
|
↓
|
|
Return answer
|
|
```
|
|
|
|
---
|
|
|
|
## Implementation Order
|
|
|
|
| Day | Tasks |
|
|
|-----|-------|
|
|
| 1 | docker-compose.yml. Export local DB. Start containers. Import data. Verify. Stop local PostgreSQL. |
|
|
| 2 | Redis connection setup. System snapshot builder. |
|
|
| 3 | User snapshot builder. Wire both into AI Steward endpoint. |
|
|
| 4 | Cache invalidation on all data-changing APIs. Session history cache. |
|
|
| 5 | End-to-end test: projects / members / tasks / signals. Verify accurate answers. |
|
|
|
|
---
|
|
|
|
## Out of scope
|
|
- Redis cluster / sentinel
|
|
- Cache monitoring dashboard
|
|
- Multi-tenant isolation
|
|
- Cache pre-warming
|
|
|
|
---
|
|
|
|
## Execution Backfill (2026-03-02)
|
|
|
|
### Part 1 — Docker Setup
|
|
- [x] Added `docker-compose.yml` with `db(redis/pgvector) + backend + frontend`.
|
|
- [x] Added DB/Redis health checks and `depends_on` health gating.
|
|
- [x] Added backend container build/runtime file: `backend/Dockerfile`.
|
|
- [x] Added backend dependency lock baseline: `backend/requirements.txt`.
|
|
- [x] Added docker env template: `.env.example` and updated `backend/.env.example`.
|
|
- [x] Redis policy configured to `maxmemory 512mb` + `allkeys-lru`.
|
|
|
|
### Part 2 — Database Migration Steps
|
|
- [x] Added migration helper script: `scripts/migrate_postgres_to_docker.sh`.
|
|
- [x] Script includes export/import/pgvector-enable/row-count verification steps.
|
|
- [x] Updated Alembic driver adaptation (`asyncpg -> psycopg`) for container migration runtime.
|
|
- [ ] Runtime execution blocked in current environment: Docker daemon unavailable.
|
|
|
|
### Part 3 — Redis Key Design
|
|
- [x] Implemented key-space in `backend/app/services/context_cache.py`:
|
|
- `pmflow:context:system:{tenant}`
|
|
- `pmflow:context:user:{tenant}:{user}`
|
|
- `pmflow:qa:session:{tenant}:{session}`
|
|
- `pmflow:project:list:{tenant}`
|
|
- `pmflow:signal:pending_count:{tenant}`
|
|
- [x] TTL implemented:
|
|
- system 30min / user 5min / session 1h / project-list 10min / pending-signal 5min.
|
|
|
|
### Part 4 — AI Context Cache
|
|
- [x] Added system snapshot builder (projects/team/pending signals/timestamp).
|
|
- [x] Added user snapshot builder (my projects/my tasks/pending signals/workload).
|
|
- [x] Wired snapshot cache read into `AdaptiveRAGService.answer_question`.
|
|
- [x] Updated RAG prompt template to inject `System Snapshot + User Snapshot`.
|
|
|
|
### Part 5 — Multi-turn Conversation Cache
|
|
- [x] Added Redis session message store (append user+assistant).
|
|
- [x] Enforced max 20 messages (10 turns) with auto trim.
|
|
- [x] Session TTL set to 1 hour.
|
|
- [x] DB persistence for QA records retained (existing behavior kept).
|
|
|
|
### Part 6 — Full Call Flow
|
|
- [x] Implemented flow in code path:
|
|
1. load system/user snapshot from Redis (fallback DB build),
|
|
2. load session history from Redis (fallback DB),
|
|
3. build prompt with snapshots + history + context,
|
|
4. call LLM,
|
|
5. append chat history to Redis,
|
|
6. persist QA record to PostgreSQL.
|
|
|
|
### Cache Invalidation Backfill
|
|
- [x] Added global SQLAlchemy invalidation hooks in `context_cache.py`.
|
|
- [x] Hooked app startup registration in `backend/app/main.py`.
|
|
- [x] Added explicit invalidation at high-impact endpoints:
|
|
- project create/update/delete/link/unlink,
|
|
- task status/update/delete/batch-create in SSTC,
|
|
- user-project replacement helper (bulk delete path).
|
|
|
|
### Verification Backfill
|
|
- [x] `docker compose config` passed.
|
|
- [x] Modified python modules syntax-checked via `python3 -m py_compile`.
|
|
- [x] Docker stack startup validated after daemon available.
|
|
- [x] Backend/Redis/DB health checks passed (`/healthz`, `redis ping`, `pgvector extension`).
|
|
|
|
### Runtime Deviation (Important)
|
|
- [x] Observed historical Alembic chain defect in this repository:
|
|
- `add_users_table_rbac.py` can trigger duplicate ENUM creation (`userrole`) on PostgreSQL.
|
|
- [x] For reliable runtime startup, backend container entrypoint switched to direct `uvicorn` boot,
|
|
while schema creation relies on existing `AUTO_CREATE_TABLES=true` in app lifespan.
|
|
- [ ] Follow-up recommended: fix legacy Alembic migration scripts for fully idempotent PostgreSQL upgrades.
|