# Project export: AgentOverflow

This document was generated by HackStack to give an AI agent context about a hackathon project. Sections are labeled with their provenance; content marked as truncated was cut to keep this document small.

## Project metadata

- Hackathon: Cal Hacks 12.0
- Tagline: Stack Overflow 2.0: break the prompt spiral-> jump straight to the proven fix.
- Devpost: https://devpost.com/software/agentoverflow
- GitHub: https://github.com/Ishaannarang22/agentoverflow
- Video: https://www.youtube.com/embed/GIcHKhtuh74?enablejsapi=1&hl=en_US&rel=0&start=&version=3&wmode=transparent
- Result: winner (Elastic: Best use of the Elastic Agent Builder on a Serverless instance)
- Team: 3 GitHub contributor(s) — Kart-ing (18 commits), KANIKA GUPTA (7 commits), het-sheth (7 commits)

## Devpost submission (written by the team)

### Overview

We’re bringing back our favorite developer tool — Stack Overflow — but rebuilt for the LLM and agent era. Developers waste time and tokens re-solving the same hallucination-driven issues. AgentOverflow captures, validates, and reuses real solutions — so you can break the spiral and ship faster.

### Inspiration

Devs burn time and tokens re-solving identical LLM failures; chat histories vanish, solutions don’t persist. Prompt spirals from hallucinations derail debugging and waste compute. We wanted a portable, structured format so that once a problem is solved, the fix lives forever. What It Does (Three Buckets) 🧩 1) Share JSON One-click “Share Solution” from a Claude share (or any public page). Scrapes, extracts, and assembles a Share Solution JSON — a canonical schema for storing LLM problem–solution pairs. LAVA validates and enforces structure (title, problem, context, technical details, code, tags). User adds short human context in our web app → LAVA re-checks summary alignment → stored + indexed in Elastic. 🔍 2) Find Solution When you’re stuck, click Find Solution. We scrape your current conversation, build a “query JSON,” and run a hybrid Elastic search over validated fixes. Returns ranked, community-verified solutions — you can copy the fix or reuse the exact prompt that worked. ⚙️ 3) Modular Context Protocol (MCP) Our MCP layer pipes structured solutions directly into live LLM sessions. Injects high-signal, low-noise context (code, logs, configs, prior fixes) at runtime. Turns LLMs from passive responders into context-aware problem solvers. How We Built It Chrome Extension (MV3 Side Panel) → captures URL and user action. Node.js Backend → orchestrates scraping and calls LAVA. Playwright Scraper → merges inline JSON (NEXT_DATA), DOM + code, shadow DOM, CDP snapshot; falls back to Jina/Readability; optional Bright Data proxy. Normalizer → canonical URLs, solution_id = sha256(canonical_url), de-dupe, preserve raw code. LAVA (Assembler → Validator) → populates schema, enforces required keys, integrates human-context correction. Web App → displays JSON, gathers human validation, saves to DB, indexes to Elasticsearch. MCP Server → injects stored JSON data into future LLM sessions in real time. Challenges We Ran Into Scraping Claude pages — dynamic Next.js + shadow DOM + iframes made extraction tricky. Canonicalization & de-dupe across mirrored URLs. Schema strictness — ensuring no fabricated fields, consistent arrays, and raw code preservation. MV3 plumbing — extension → backend → web app CORS flow. Elastic tuning — balancing vector + keyword recall without over-matching. Accomplishments We’re Proud Of Reliable multi-extractor pipeline that preserves full code context. Seamless extension → web app → DB integration. LAVA’s dual-phase validation (assembler + human-context correction). The Share Solution JSON — a portable format for AI problem–solution memory. Functional Find Solution loop: stuck → match → copy fix → unblocked in seconds. MCP turning static knowledge into live agent context. What We Learned LLM output isn’t reusable knowledge until you add structure and validation. Guardrails prevent hallucination, not just fine-tuning. Human context + schema validation beats auto-summarization. Canonical URLs + hashes create a single source of truth. Small UX choices (side panel, copy button, “open in web app”) dramatically improve adoption. What’s Next for AgentOverflow Team Repos: org-scoped libraries of problem–solution pairs with permissions, versioning, ownership. Better Ranking: success feedback, solve-rate metrics, evaluation signals. Deeper Elastic Integration: vector on code + technical_deep_context, framework-specific synonyms. More Input Channels: ingest Slack, Discord, GitHub issues into the same schema. Quality Gates: automatic spec/API validation before publishing. SDK + API: let any LLM or agent read/write Share Solution JSONs. Share Solution JSON Schema

## README (from the GitHub repository)

# AgentOverflow

**Stack Overflow for the AI era**: capture validated problem-solution pairs from AI debugging sessions, index them in Elasticsearch, and feed them back into future LLM sessions so nobody has to re-solve the same bug twice.

Winner of **Best Use of Elastic Agents** at **CalHacks 12.0**.

## The Problem

Every day, developers solve real bugs inside AI chat sessions, and then the fix disappears into an unsearchable conversation log. The next person (or the next LLM session) starts from zero, burning tokens and time rediscovering the same solution.

AgentOverflow turns those one-off debugging sessions into a durable, searchable knowledge base:

1. **Capture**: share a solved Claude conversation from a Chrome extension side panel.
2. **Extract**: the backend scrapes the share link and uses Claude to distill it into a structured solution JSON (problem, root cause, code snippets, error messages, attempted fixes, deep technical context, tags).
3. **Validate**: the author adds human context, and a second LLM pass merges it into a final, polished post.
4. **Index**: the post is stored in Elasticsearch, searchable by the community through a React web app.
5. **Retrieve**: when you're stuck, "Find Solution" queries an Elastic Agent over the knowledge base and copies a ready-to-paste solution to your clipboard. An MCP server (on the [`mcp`](../../tree/mcp) branch) exposes the same knowledge base directly to LLMs and agents.

## How It Works

```
┌──────────────────┐   share link    ┌─────────────────────────┐
│ Chrome Extension │ ──────────────► │ Pipeline Server (:3001) │
│  (side panel on  │                 │  Playwright scraper     │
│   claude.ai)     │ ◄────────────── │  Claude via Lava API    │
└──────────────────┘  solution JSON  └───────────┬─────────────┘
                                                 │ finalized post
        ┌──────────────────┐                     ▼
        │ React Web App    │  /api   ┌─────────────────────────┐
        │  (:8080)         │ ──────► │ API Server (:3002)      │
        │  browse/search/  │         │  posts, search, auth    │
        │  submit posts    │         └───────────┬─────────────┘
        └──────────────────┘                     │
                                                 ▼
┌──────────────────┐   MCP (stdio)   ┌─────────────────────────┐
│ LLMs / Agents    │ ──────────────► │ Elasticsearch +         │
│  (mcp branch)    │                 │ Elastic Agent Builder   │
└──────────────────┘                 └─────────────────────────┘
```

### Components

- **Chrome extension** (`extension/`): Manifest V3 side panel that runs on `claude.ai`. Two actions:
  - **Share Solution**: sends the current conversation's share link to the backend for extraction, then hands off to the web app for human review and publishing.
  - **Find Solution**: extracts your current problem and queries the knowledge base; the answer is copied to your clipboard so you can paste it straight back into the conversation.
- **Pipeline server** (`backend/server.js`, port 3001): Express server that:
  - Scrapes Claude share pages with headless **Playwright** (with anti-bot-detection measures and Cloudflare handling).
  - Calls **Claude 3.5 Sonnet** through the **Lava API** forwarding proxy to extract a strict solution schema from the raw conversation, in either `share` (solved) or `find` (unsolved) mode.
  - Runs a second finalization pass that merges the extracted JSON with the author's human context into the final post.
  - Answers `POST /api/find-solution` by conversing with an **Elastic Agent Builder** agent (`claude_solution_finder`) over the indexed knowledge base.
  - Includes an LRU cache for repeated scrapes, rate limiting, and usage tracking.
- **API server** (`backend/src/server.js`, port 3002): Express REST API backed by **Elasticsearch** (`@elastic/elasticsearch`):
  - `/api/search`: multi-field search with boosted relevance (`title^3`, `problem^2`, `solution^2`, ...), tag/category filters, trending.
  - `/api/posts`: create, update, like, and comment on posts (indices: `posts_ai`, `user_post_map`, `post_edges`).
  - `/api/auth`: JWT-based register/login with bcrypt.
  - Hardened with Helmet, CORS, compression, and rate limiting.
- **Web app** (`frontend/`): React + TypeScript + Vite, styled with Tailwind CSS and shadcn/ui (Radix primitives), with TanStack Query for data fetching. Pages for browsing, searching, trending, tags, leaderboard, post detail, and submitting posts.
- **MCP server** (`mcp` branch): a TypeScript **Model Context Protocol** server (`@modelcontextprotocol/sdk`, stdio transport) that lets any MCP-capable LLM or agent read from and write to the knowledge base.

## The Solution Schema

Everything revolves around a portable JSON schema for a solved (or open) problem:

```json
{
  "solution_id": "...",
  "share_link": "https://claude.ai/share/...",
  "type": "share | find",
  "title": "Fix React useEffect Infinite Loop with useRef",
  "problem": "...",
  "context": "...",
  "technical_description": "...",
  "solution": "...",
  "summary": "...",
  "error_messages": ["..."],
  "attempted_solutions": ["..."],
  "code_snippets": [{ "description": "...", "code": "..." }],
  "technical_deep_context": "...",
  "tags": ["react", "hooks"],
  "created_at": "ISO 8601"
}
```

The `technical_deep_context` field is deliberately exhaustive (versions, environment, stack, debugging steps taken): it exists purely to improve future similarity matching.

## Tech Stack

| Layer | Technology |
|---|---|
| Extension | Chrome Manifest V3, Side Panel API |
| Pipeline | Node.js, Express, Playwright, Lava API → Claude 3.5 Sonnet |
| Search | Elasticsearch (Elastic Cloud), Elastic Agent Builder |
| API | Express, JWT + bcrypt, Helmet, express-rate-limit, LRU cache |
| Frontend | React 18, TypeScript, Vite, Tailwind CSS, shadcn/ui (Radix), TanStack Query |
| MCP | TypeScript, `@modelcontextprotocol/sdk`, Zod, undici (on the `mcp` branch) |

## Getting Started

### Prerequisites

- Node.js >= 18
- An [Elastic Cloud](https://cloud.elastic.co/) deployment with an API key
- A [Lava](https://www.lavapayments.com/) forward token (used to proxy Anthropic API calls)

### 1. Backend

```bash
cd backend
npm install
```

Create `backend/.env`:

```env
# Pipeline server (server.js, port 3001)
LAVA_FORWARD_TOKEN=your_lava_token
ELASTICSEARCH_ENDPOINT=https://your-deployment.kb.us-central1.gcp.elastic.cloud:443
ELASTICSEARCH_API_KEY=your_kibana_api_key

# API server (src/server.js, port 3002)
ELASTIC_NODE=https://your-deployment.es.us-central1.gcp.elastic.cloud:443
ELASTIC_API_KEY=your_elasticsearch_api_key
API_PORT=3002
FRONTEND_URL=http://localhost:8080
```

Create the Elasticsearch indices, then start both servers:

```bash
node src/setup-indices.js   # creates posts_ai, user_post_map, post_edges
npm run dev:all             # pipeline server (3001) + API server (3002)
```

For "Find Solution" to work, your Elastic deployment needs an Agent Builder agent named `claude_solution_finder` (see `FIND_SOLUTION_SETUP.md`).

### 2. Frontend

```bash
cd frontend
npm install
npm run dev   # http://localhost:8080, proxies /api to :3002
```

### 3. Chrome Extension

1. Open `chrome://extensions`, enable **Developer mode**.
2. Click **Load unpacked** and select the `extension/` folder.
3. Open a conversation on `claude.ai`, open the side panel, and use **Share Solution** / **Find Solution**.

### Docker

The `DOCKER` file builds the pipeline server on the official Playwright image:

```bash
docker build -f DOCKER -t agentoverflow-backend ./backend
```

## MCP Server

The `mcp` branch contains a standalone MCP server that exposes the knowledge base to any MCP-capable client (Claude Desktop, agents, IDEs). It runs over stdio and forwards requests to the AgentOverflow mediator API, validating everything with Zod.

Tools:

- `agentoverflow_search`: search for similar questions/answers, with optional structured context (error lines, language, files, ta

[README truncated for size]

## Detected evidence (automated analysis)

Indexed codebase: 108 recognized source files, 459 KB.
- CSS (language) — detected in the code
- Express (technology) — detected in the code
- HTML (language) — detected in the code
- JavaScript (language) — detected in the code
- React (technology) — detected in the code
- SQL (language) — detected in the code
- Tailwind CSS (technology) — detected in the code
- TypeScript (language) — detected in the code
- Google Gemini (technology) — claimed on Devpost, not found in the code
- Node.js (technology) — claimed on Devpost, not found in the code
- Python (language) — claimed on Devpost, not found in the code
- AI coding agent: Claude Code — evidence: commit authorship or trailers

## Codebase structure (from repository index)

### Files (120 of 125)

```
.DS_Store
.gitignore
backend/.DS_Store
backend/.env
backend/.env.backup
backend/.env.backup2
backend/check-indices.js
backend/cookies.txt
backend/package.json
backend/README.md
backend/scraper.js
backend/server.js
backend/src/.DS_Store
backend/src/config/elastic.js
backend/src/middleware/auth.js
backend/src/routes/auth.js
backend/src/routes/posts.js
backend/src/routes/search.js
backend/src/server.js
backend/src/setup-indices.js
DOCKER
extension/background.js
extension/content_script.js
extension/content.js
extension/manifest.json
extension/sidepanel.html
extension/sidepanel.js
FIND_SOLUTION_SETUP.md
frontend/.DS_Store
frontend/index.html
frontend/package.json
frontend/postcss.config.js
frontend/public/robots.txt
frontend/src/App.css
frontend/src/App.tsx
frontend/src/components/AIProcessingScreen.tsx
frontend/src/components/AppSidebar.tsx
frontend/src/components/ChatPreview.tsx
frontend/src/components/CodeBlock.tsx
frontend/src/components/CommentThread.tsx
frontend/src/components/EditablePostForm.tsx
frontend/src/components/FilterBar.tsx
frontend/src/components/Layout.tsx
frontend/src/components/Navbar.tsx
frontend/src/components/PostCard.tsx
frontend/src/components/PostPreview.tsx
frontend/src/components/StepNavigator.tsx
frontend/src/components/ui/accordion.tsx
frontend/src/components/ui/alert-dialog.tsx
frontend/src/components/ui/alert.tsx
frontend/src/components/ui/aspect-ratio.tsx
frontend/src/components/ui/avatar.tsx
frontend/src/components/ui/badge.tsx
frontend/src/components/ui/breadcrumb.tsx
frontend/src/components/ui/button.tsx
frontend/src/components/ui/calendar.tsx
frontend/src/components/ui/card.tsx
frontend/src/components/ui/carousel.tsx
frontend/src/components/ui/chart.tsx
frontend/src/components/ui/checkbox.tsx
frontend/src/components/ui/collapsible.tsx
frontend/src/components/ui/command.tsx
frontend/src/components/ui/context-menu.tsx
frontend/src/components/ui/dialog.tsx
frontend/src/components/ui/drawer.tsx
frontend/src/components/ui/dropdown-menu.tsx
frontend/src/components/ui/form.tsx
frontend/src/components/ui/hover-card.tsx
frontend/src/components/ui/input-otp.tsx
frontend/src/components/ui/input.tsx
frontend/src/components/ui/label.tsx
frontend/src/components/ui/menubar.tsx
frontend/src/components/ui/navigation-menu.tsx
frontend/src/components/ui/pagination.tsx
frontend/src/components/ui/popover.tsx
frontend/src/components/ui/progress.tsx
frontend/src/components/ui/radio-group.tsx
frontend/src/components/ui/resizable.tsx
frontend/src/components/ui/scroll-area.tsx
frontend/src/components/ui/select.tsx
frontend/src/components/ui/separator.tsx
frontend/src/components/ui/sheet.tsx
frontend/src/components/ui/sidebar.tsx
frontend/src/components/ui/skeleton.tsx
frontend/src/components/ui/slider.tsx
frontend/src/components/ui/sonner.tsx
frontend/src/components/ui/switch.tsx
frontend/src/components/ui/table.tsx
frontend/src/components/ui/tabs.tsx
frontend/src/components/ui/textarea.tsx
frontend/src/components/ui/toast.tsx
frontend/src/components/ui/toaster.tsx
frontend/src/components/ui/toggle-group.tsx
frontend/src/components/ui/toggle.tsx
frontend/src/components/ui/tooltip.tsx
frontend/src/components/ui/use-toast.ts
frontend/src/contexts/AuthContext.tsx
frontend/src/data/mockData.ts
frontend/src/hooks/use-mobile.tsx
frontend/src/hooks/use-toast.ts
frontend/src/hooks/usePost.ts
frontend/src/hooks/usePosts.ts
frontend/src/index.css
frontend/src/lib/utils.ts
frontend/src/main.tsx
frontend/src/pages/Documentation.tsx
frontend/src/pages/Index.tsx
frontend/src/pages/Leaderboard.tsx
frontend/src/pages/Login.tsx
frontend/src/pages/NotFound.tsx
frontend/src/pages/PostDetail.tsx
frontend/src/pages/SubmitPost.tsx
frontend/src/pages/Tags.tsx
frontend/src/pages/Trending.tsx
frontend/src/pages/UserProfile.tsx
frontend/src/services/api.ts
frontend/src/vite-env.d.ts
frontend/tailwind.config.ts
frontend/test.html
frontend/tsconfig.app.json
[5 more files omitted for size]
```

### Dependencies

- backend/package.json: @elastic/elasticsearch@^8.15.0, bcryptjs@^2.4.3, compression@^1.7.4, concurrently@^8.2.2, cookie-parser@^1.4.6, cors@^2.8.5, dotenv@^16.4.5, express@^4.21.2, express-rate-limit@^7.5.1, express-validator@^7.0.1, helmet@^7.1.0, jsonwebtoken@^9.0.2, lru-cache@^11.2.2, morgan@^1.10.0, nodemon@^3.0.2, playwright@^1.56.1
- frontend/package.json: @eslint/js@^9.32.0, @hookform/resolvers@^3.10.0, @radix-ui/react-accordion@^1.2.11, @radix-ui/react-alert-dialog@^1.1.14, @radix-ui/react-aspect-ratio@^1.1.7, @radix-ui/react-avatar@^1.1.10, @radix-ui/react-checkbox@^1.3.2, @radix-ui/react-collapsible@^1.1.11, @radix-ui/react-context-menu@^2.2.15, @radix-ui/react-dialog@^1.1.14, @radix-ui/react-dropdown-menu@^2.1.15, @radix-ui/react-hover-card@^1.1.14, @radix-ui/react-label@^2.1.7, @radix-ui/react-menubar@^1.1.15, @radix-ui/react-navigation-menu@^1.2.13, @radix-ui/react-popover@^1.1.14, @radix-ui/react-progress@^1.1.7, @radix-ui/react-radio-group@^1.3.7, @radix-ui/react-scroll-area@^1.2.9, @radix-ui/react-select@^2.2.5, @radix-ui/react-separator@^1.1.7, @radix-ui/react-slider@^1.3.5, @radix-ui/react-slot@^1.2.3, @radix-ui/react-switch@^1.2.5, @radix-ui/react-tabs@^1.1.12, @radix-ui/react-toast@^1.2.14, @radix-ui/react-toggle@^1.1.9, @radix-ui/react-toggle-group@^1.1.10, @radix-ui/react-tooltip@^1.2.7, @tailwindcss/typography@^0.5.16, @tanstack/react-query@^5.83.0, @types/node@^22.16.5, @types/react@^18.3.23, @types/react-dom@^18.3.7, @vitejs/plugin-react-swc@^3.11.0, autoprefixer@^10.4.21, class-variance-authority@^0.7.1, clsx@^2.1.1, cmdk@^1.1.1, date-fns@^3.6.0, embla-carousel-react@^8.6.0, eslint@^9.32.0, eslint-plugin-react-hooks@^5.2.0, eslint-plugin-react-refresh@^0.4.20, framer-motion@^12.23.24, globals@^15.15.0, input-otp@^1.4.2, lovable-tagger@^1.1.11, lucide-react@^0.462.0, next-themes@^0.3.0, postcss@^8.5.6, react@^18.3.1, react-day-picker@^8.10.1, react-dom@^18.3.1, react-hook-form@^7.61.1, react-resizable-panels@^2.1.9, react-router-dom@^6.30.1, recharts@^2.15.4, sonner@^1.7.4, tailwind-merge@^2.6.0, tailwindcss@^3.4.17, tailwindcss-animate@^1.0.7, typescript@^5.8.3, typescript-eslint@^8.38.0, vaul@^0.9.9, vite@^5.4.19, zod@^3.25.76

### Recent commits (newest first)

- Tidy README
- Add README
- Update README.md
- Clean up .env.backup2 by removing sensitive data
- Create backup of .env file
- Remove sensitive data from backend/.env
- Merge pull request #7 from Ishaannarang22/frontend
- Final Push - except Elastic connection
- Update extension UI to match website color scheme - StackOverflow orange theme
- Merge pull request #6 from Ishaannarang22/main-extend
- Merge branch 'frontend' into main-extend
- THe Extension connects to the secondary backend
- it works!
- added more functions
- update
- Resolve merge conflicts: fix package.json structure and clean up .gitignore
- commit 1 - no auth
- backend-2 scraper works with stleath mode
- Fixing the files shit
- Merge pull request #5 from Ishaannarang22/parse-summarize

## Key source files (fetched from GitHub, selected and truncated for size)

### FIND_SOLUTION_SETUP.md

```markdown
# Find Solution Feature Setup

## Overview
The "Find Solution" feature allows users to search for similar problems in the Elasticsearch knowledge base and get instant solutions copied to their clipboard.

## Environment Variables

Add these to your `backend/.env` file:

```env
# Elasticsearch Agent Configuration
ELASTICSEARCH_ENDPOINT=https://your-elasticsearch-endpoint.kb.us-central1.gcp.elastic.cloud:443
ELASTICSEARCH_API_KEY=your_api_key_here
```

## How to Get Your Elasticsearch Credentials

1. Go to your Elasticsearch Cloud console
2. Navigate to your deployment
3. Find the "Endpoint" URL (ends with `.elastic.cloud`)
4. Go to "Security" → "API Keys" to create/get an API key
5. The agent should be named `claude_solution_finder` in your Elasticsearch deployment

## How It Works

### User Flow:
1. User clicks "Find Solution" in the extension
2. Extension extracts the problem from the Claude conversation
3. Backend processes the conversation to get structured problem data
4. Backend queries the Elasticsearch agent with the problem
5. Elasticsearch agent searches the knowledge base and returns a solution
6. Solution is automatically copied to the clipboard
7. User can paste it directly into Claude to continue the conversation

### API Endpoints:

**POST `/api/find-solution`**
- Queries the Elasticsearch agent with a problem description
- Returns the solution text ready for Claude

**Request:**
```json
{
  "problemDescription": "The problem description...",
  "context": "Additional context...",
  "tags": ["React", "JavaScript"]
}
```

**Response:**
```json
{
  "ok": true,
  "solution": "Here's the solution to your problem...",
  "metadata": {
    "problemLength": 123,
    "contextProvided": true,
    "tagsProvided": 2
  }
}
```

## Testing

1. Start the backend:
```bash
cd backend
npm run dev
```

2. Open Claude.ai and start a conversation
3. When you encounter a problem, click the share button
4. In the extension sidepanel, click "Find Solution"
5. Wait for the solution to be found and copied
6. Paste it into Claude

## Troubleshooting

### Error: "Missing Elasticsearch configuration"
- Check that you've added the environment variables to `backend/.env`
- Restart the backend server after adding the variables

### Error: "Elasticsearch agent error"
- Verify your endpoint URL is correct
- Check that your API key has proper permissions
- Ensure the agent `claude_solution_finder` exists in your deployment

### Timeout errors
- The agent query has a 60-second timeout
- If your knowledge base is large, consider optimizing your agent configuration

```

### backend/package.json

```
{
  "name": "agentoverflow",
  "version": "2.0.0",
  "description": "AgentOverflow - AI-powered solution finder using Lava API and Elasticsearch",
  "type": "module",
  "main": "server.js",
  "scripts": {
    "dev": "node --env-file=.env server.js",
    "dev:api": "node --env-file=.env src/server.js",
    "dev:all": "concurrently \"npm run dev\" \"npm run dev:api\"",
    "start": "node server.js",
    "start:api": "node src/server.js",
    "start:all": "concurrently \"npm run start\" \"npm run start:api\"",
    "test": "node test.js",
    "scrape": "node scraper.js",
    "dev:watch": "nodemon --env-file=.env server.js",
    "dev:api:watch": "nodemon --env-file=.env src/server.js"
  },
  "keywords": [
    "agentoverflow",
    "lava",
    "claude",
    "ai",
    "solutions",
    "elasticsearch",
    "express",
    "middleware",
    "api"
  ],
  "author": "AgentOverflow Team",
  "license": "MIT",
  "dependencies": {
    "@elastic/elasticsearch": "^8.15.0",
    "bcryptjs": "^2.4.3",
    "compression": "^1.7.4",
    "cookie-parser": "^1.4.6",
    "cors": "^2.8.5",
    "dotenv": "^16.4.5",
    "express": "^4.21.2",
    "express-rate-limit": "^7.5.1",
    "express-validator": "^7.0.1",
    "helmet": "^7.1.0",
    "jsonwebtoken": "^9.0.2",
    "lru-cache": "^11.2.2",
    "morgan": "^1.10.0",
    "playwright": "^1.56.1"
  },
  "devDependencies": {
    "concurrently": "^8.2.2",
    "nodemon": "^3.0.2"
  },
  "engines": {
    "node": ">=18.0.0"
  }
}

```

### frontend/package.json

```
{
  "name": "vite_react_shadcn_ts",
  "private": true,
  "version": "0.0.0",
  "type": "module",
  "scripts": {
    "dev": "vite",
    "build": "vite build",
    "build:dev": "vite build --mode development",
    "lint": "eslint .",
    "preview": "vite preview"
  },
  "dependencies": {
    "@hookform/resolvers": "^3.10.0",
    "@radix-ui/react-accordion": "^1.2.11",
    "@radix-ui/react-alert-dialog": "^1.1.14",
    "@radix-ui/react-aspect-ratio": "^1.1.7",
    "@radix-ui/react-avatar": "^1.1.10",
    "@radix-ui/react-checkbox": "^1.3.2",
    "@radix-ui/react-collapsible": "^1.1.11",
    "@radix-ui/react-context-menu": "^2.2.15",
    "@radix-ui/react-dialog": "^1.1.14",
    "@radix-ui/react-dropdown-menu": "^2.1.15",
    "@radix-ui/react-hover-card": "^1.1.14",
    "@radix-ui/react-label": "^2.1.7",
    "@radix-ui/react-menubar": "^1.1.15",
    "@radix-ui/react-navigation-menu": "^1.2.13",
    "@radix-ui/react-popover": "^1.1.14",
    "@radix-ui/react-progress": "^1.1.7",
    "@radix-ui/react-radio-group": "^1.3.7",
    "@radix-ui/react-scroll-area": "^1.2.9",
    "@radix-ui/react-select": "^2.2.5",
    "@radix-ui/react-separator": "^1.1.7",
    "@radix-ui/react-slider": "^1.3.5",
    "@radix-ui/react-slot": "^1.2.3",
    "@radix-ui/react-switch": "^1.2.5",
    "@radix-ui/react-tabs": "^1.1.12",
    "@radix-ui/react-toast": "^1.2.14",
    "@radix-ui/react-toggle": "^1.1.9",
    "@radix-ui/react-toggle-group": "^1.1.10",
    "@radix-ui/react-tooltip": "^1.2.7",
    "@tanstack/react-query": "^5.83.0",
    "class-variance-authority": "^0.7.1",
    "clsx": "^2.1.1",
    "cmdk": "^1.1.1",
    "date-fns": "^3.6.0",
    "embla-carousel-react": "^8.6.0",
    "framer-motion": "^12.23.24",
    "input-otp": "^1.4.2",
    "lucide-react": "^0.462.0",
    "next-themes": "^0.3.0",
    "react": "^18.3.1",
    "react-day-picker": "^8.10.1",
    "react-dom": "^18.3.1",
    "react-hook-form": "^7.61.1",
    "react-resizable-panels": "^2.1.9",
    "react-router-dom": "^6.30.1",
    "recharts": "^2.15.4",
    "sonner": "^1.7.4",
    "tailwind-merge": "^2.6.0",
    "tailwindcss-animate": "^1.0.7",
    "vaul": "^0.9.9",
    "zod": "^3.25.76"
  },
  "devDependencies": {
    "@eslint/js": "^9.32.0",
    "@tailwindcss/typography": "^0.5.16",
    "@types/node": "^22.16.5",
    "@types/react": "^18.3.23",
    "@types/react-dom": "^18.3.7",
    "@vitejs/plugin-react-swc": "^3.11.0",
    "autoprefixer": "^10.4.21",
    "eslint": "^9.32.0",
    "eslint-plugin-react-hooks": "^5.2.0",
    "eslint-plugin-react-refresh": "^0.4.20",
    "globals": "^15.15.0",
    "lovable-tagger": "^1.1.11",
    "postcss": "^8.5.6",
    "tailwindcss": "^3.4.17",
    "typescript": "^5.8.3",
    "typescript-eslint": "^8.38.0",
    "vite": "^5.4.19"
  }
}

```

### frontend/src/main.tsx

```typescript
import { createRoot } from "react-dom/client";
import App from "./App.tsx";
import "./index.css";

createRoot(document.getElementById("root")!).render(<App />);

```

### frontend/src/App.tsx

```typescript
import { Toaster } from "@/components/ui/toaster";
import { Toaster as Sonner } from "@/components/ui/sonner";
import { TooltipProvider } from "@/components/ui/tooltip";
import { QueryClient, QueryClientProvider } from "@tanstack/react-query";
import { BrowserRouter, Routes, Route } from "react-router-dom";
import { Layout } from "@/components/Layout";
import { AuthProvider } from "@/contexts/AuthContext";
import Index from "./pages/Index";
import Trending from "./pages/Trending";
import Tags from "./pages/Tags";
import PostDetail from "./pages/PostDetail";
import SubmitPost from "./pages/SubmitPost";
import UserProfile from "./pages/UserProfile";
import Leaderboard from "./pages/Leaderboard";
import Documentation from "./pages/Documentation";
import Login from "./pages/Login";
import NotFound from "./pages/NotFound";

const queryClient = new QueryClient();

const App = () => (
  <QueryClientProvider client={queryClient}>
    <AuthProvider>
      <TooltipProvider>
        <Toaster />
        <Sonner />
        <BrowserRouter>
          <Layout>
            <Routes>
              <Route path="/" element={<Index />} />
              <Route path="/trending" element={<Trending />} />
              <Route path="/tags" element={<Tags />} />
              <Route path="/login" element={<Login />} />
              <Route path="/post/:id" element={<PostDetail />} />
              <Route path="/submit" element={<SubmitPost />} />
              <Route path="/profile" element={<UserProfile />} />
              <Route path="/profile/:id" element={<UserProfile />} />
              <Route path="/leaderboard" element={<Leaderboard />} />
              <Route path="/docs" element={<Documentation />} />
              {/* ADD ALL CUSTOM ROUTES ABOVE THE CATCH-ALL "*" ROUTE */}
              <Route path="*" element={<NotFound />} />
            </Routes>
          </Layout>
        </BrowserRouter>
      </TooltipProvider>
    </AuthProvider>
  </QueryClientProvider>
);

export default App;

```

### backend/src/server.js

```javascript
import express from 'express';
import cors from 'cors';
import helmet from 'helmet';
import compression from 'compression';
import morgan from 'morgan';
import rateLimit from 'express-rate-limit';
import cookieParser from 'cookie-parser';
import dotenv from 'dotenv';

// Import routes
import searchRoutes from './routes/search.js';
import postRoutes from './routes/posts.js';
import authRoutes from './routes/auth.js';

// Import config
import { testConnection } from './config/elastic.js';

// Load environment variables
dotenv.config();

const app = express();
const PORT = process.env.API_PORT || 3002;

// Security middleware
app.use(helmet());

// CORS configuration
app.use(cors({
  origin: [
    process.env.FRONTEND_URL || 'http://localhost:5173',
    'http://localhost:8083',
    'http://localhost:8082',
    'http://localhost:8081',
    'http://localhost:8080'
  ],
  credentials: true
}));

// Compression
app.use(compression());

// Logging
app.use(morgan('combined'));

// Rate limiting
const limiter = rateLimit({
  windowMs: parseInt(process.env.RATE_LIMIT_WINDOW_MS) || 15 * 60 * 1000, // 15 minutes
  max: parseInt(process.env.RATE_LIMIT_MAX_REQUESTS) || 100, // limit each IP to 100 requests per windowMs
  message: {
    success: false,
    error: 'Too many requests from this IP, please try again later.'
  }
});
app.use(limiter);

// Body parsing
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true, limit: '10mb' }));

// Cookie parsing
app.use(cookieParser());

// Health check endpoint
app.get('/health', async (req, res) => {
  const elasticStatus = await testConnection();
  
  res.json({
    success: true,
    status: 'OK',
    timestamp: new Date().toISOString(),
    services: {
      elastic: elasticStatus ? 'connected' : 'disconnected'
    }
  });
});

// API routes
app.use('/api/auth', authRoutes);
app.use('/api/search', searchRoutes);
app.use('/api/posts', postRoutes);

// 404 handler
app.use('*', (req, res) => {
  res.status(404).json({
    success: false,
    error: 'Endpoint not found',
    message: `Cannot ${req.method} ${req.originalUrl}`
  });
});

// Error handler
app.use((error, req, res, next) => {
  console.error('Server error:', error);
  
  res.status(error.status || 500).json({
    success: false,
    error: error.message || 'Internal server error',
    ...(process.env.NODE_ENV === 'development' && { stack: error.stack })
  });
});

// Start server
app.listen(PORT, async () => {
  console.log(`🚀 Server running on port ${PORT}`);
  console.log(`📊 Health check: http://localhost:${PORT}/health`);
  console.log(`🔐 Auth API: http://localhost:${PORT}/api/auth`);
  console.log(`🔍 Search API: http://localhost:${PORT}/api/search`);
  console.log(`📝 Posts API: http://localhost:${PORT}/api/posts`);
  
  // Test Elastic connection
  await testConnection();
});

export default app;

```

### backend/server.js

```javascript
import "dotenv/config";
import express from "express";
import cors from "cors";
import rateLimit from "express-rate-limit";
import { LRUCache } from "lru-cache";
import { scrapeClaudeChat } from "./scraper.js";

const {
  LAVA_FORWARD_TOKEN,
  LAVA_BASE_URL = "https://api.lavapayments.com/v1",
  PORT = 3001,
  CORS_ORIGIN = "*",
  RATE_LIMIT_PER_MIN = "10",
  ELASTICSEARCH_ENDPOINT,
  ELASTICSEARCH_API_KEY,
} = process.env;

if (!LAVA_FORWARD_TOKEN) {
  console.error("❌ Missing LAVA_FORWARD_TOKEN in .env");
  process.exit(1);
}

const app = express();
app.use(express.json({ limit: "5mb" }));
app.use(cors({ origin: CORS_ORIGIN === "*" ? true : CORS_ORIGIN }));

// Rate limiting
const limiter = rateLimit({
  windowMs: 60 * 1000,
  max: parseInt(RATE_LIMIT_PER_MIN, 10),
});
app.use(limiter);

// Cache
const cache = new LRUCache({ max: 200, ttl: 15 * 60 * 1000 });

// Usage tracking
const usage = { totalCalls: 0, totalTokens: 0 };

// ============================================================================
// INSTRUCTIONS FOR CLAUDE
// ============================================================================

const shareInstructions = `You are extracting information from a COMPLETED Claude conversation where a problem was successfully solved.

CRITICAL: Follow this EXACT schema structure:

{
  "solution_id": "[Extract UUID from share link - the part after /share/]",
  "share_link": "[The full share URL provided]",
  "type": "share",
  "title": "[Brief descriptive title - e.g., 'Fix React useEffect Infinite Loop with useRef']",
  "problem": "[1-2 sentence description of what the issue was]",
  "context": "[Summarized overview in ~200 words: What was the issue? What wasn't working? What was the current behavior vs expected behavior?]",
  "technical_description": "[Detailed technical explanation of what was happening and how it was fixed: What was the root cause? What are the underlying mechanisms? What's the technical solution? How does the fix work? Include framework-specific details and architectural patterns.]",
  "solution": "[Clear description of the working solution - what was done to fix it? What approach was taken? What was the key insight?]",
  "summary": "[Brief 2-3 sentence summary of the entire conversation: problem encountered → solution found → outcome]",
  "error_messages": ["[Exact error message 1]", "[Exact error message 2]"],
  "attempted_solutions": ["[What they tried #1 and the result]", "[What they tried #2 and the result]"],
  "code_snippets": [
    {
      "description": "[e.g., 'Broken implementation', 'Working solution', 'Final fix']",
      "code": "[The actual code from the conversation]"
    }
  ],
  "technical_deep_context": "[Extract MAXIMUM information from the chat for future similarity matching: Include all technical details, edge cases mentioned, specific version numbers, environment details (OS, browser, Node version, etc.), package versions, configuration settings, file structure context, related technologies in the stack, deployment environment, timing issues, race conditions, performance characteristics, memory usage patterns, network conditions, authentication/authorization context, database schema details if relevant, API endpoint specifics, third-party service integrations, build tool configurations, linting/formatting setup, testing framework details, any async/sync considerations, concurrency patterns, state management approach, data flow architecture, component hierarchy, prop drilling issues, re-render triggers, lifecycle considerations, event handling specifics, CSS/styling approach if relevant, responsive design considerations, accessibility concerns mentioned, browser compatibility issues, polyfill requirements, bundler configuration, code splitting strategy, lazy loading patterns, caching strategies, API rate limiting, error boundaries, fallback UI, loading states, error handling patterns, validation logic, form handling approach, routing configuration, middleware setup, security considerations, CORS settings, cookie/session handling, WebSocket usage, real-time update patterns, optimization techniques tried, profiling results, debugging steps taken, console output details, network tab observations, React DevTools insights, Redux DevTools data if applicable, and any other contextual information that could help match similar problems in the future. Include conversational context like 'user mentioned they are a beginner' or 'user is working on a production app with 10k users' or 'tight deadline' or 'refactoring legacy code'. The more context, the better the matching.]",
  "tags": ["[technology]", "[framework]", "[concept]", "[error-type]"],
  "created_at": "[Current ISO 8601 timestamp]"
}

IMPORTANT:
- Extract 2-5 code_snippets with actual code from the conversation
- Make technical_deep_context EXTREMELY detailed - include EVERYTHING
- Use current timestamp for created_at
- Extract exact error messages verbatim
- Return ONLY valid JSON, no markdown formatting`;

const findInstructions = `You are extracting information from an INCOMPLETE Claude conversation where someone is seeking help with an unsolved problem.

CRITICAL: Follow this EXACT schema structure:

{
  "solution_id": "[Extract UUID from share link - the part after /share/]",
  "share_link": "[The full share URL provided]",
  "type": "find",
  "title": "[Brief descriptive title of the problem - e.g., 'React useEffect Causing Infinite Renders']",
  "problem": "[1-2 sentence description of the core issue they're facing]",
  "context": "[Summarized overview in ~200 words: What is the issue? What's not working? What's the current behavior vs expected behavior? What are they trying to accomplish?]",
  "technical_description": "[Detailed technical explanation of what's happening: What seems to be the root cause based on current understanding? What are the symptoms? What's the technical nature of the problem? Include any hypotheses about what might be wrong.]",
  "solution": null,
  "summ
[truncated — 20801 more characters]
```

### frontend/src/pages/Index.tsx

```typescript
import { useState, useEffect } from "react";
import { apiService, Post } from "@/services/api";
import { Card, CardContent, CardDescription, CardHeader, CardTitle } from "@/components/ui/card";
import { Badge } from "@/components/ui/badge";
import { Button } from "@/components/ui/button";
import { Input } from "@/components/ui/input";
import { Search, Calendar, Tag, ExternalLink, ArrowUp, ArrowDown, MessageSquare, Eye } from "lucide-react";
import { useToast } from "@/hooks/use-toast";
import { useAuth } from "@/contexts/AuthContext";
import { Link } from "react-router-dom";

const Index = () => {
  const [posts, setPosts] = useState<Post[]>([]);
  const [loading, setLoading] = useState(true);
  const [searchQuery, setSearchQuery] = useState("");
  const [totalPosts, setTotalPosts] = useState(0);
  const [votingPosts, setVotingPosts] = useState<Set<string>>(new Set());
  const { toast } = useToast();
  const { user, isAuthenticated } = useAuth();

  const fetchPosts = async (query = "") => {
    try {
      setLoading(true);
      const response = await apiService.searchPosts({
        query,
        limit: 20,
        sortBy: "created_at",
        sortOrder: "desc"
      });

      if (response.success && response.data) {
        setPosts(response.data.posts);
        setTotalPosts(response.data.total);
      } else {
        toast({
          title: "Error",
          description: "Failed to fetch posts",
          variant: "destructive"
        });
      }
    } catch (error) {
      console.error("Error fetching posts:", error);
      toast({
        title: "Error",
        description: "Failed to fetch posts",
        variant: "destructive"
      });
    } finally {
      setLoading(false);
    }
  };

  useEffect(() => {
    fetchPosts();
  }, []);

  const handleSearch = (e: React.FormEvent) => {
    e.preventDefault();
    fetchPosts(searchQuery);
  };

  const handleVote = async (postId: string, type: "like" | "dislike") => {
    if (!isAuthenticated) {
      toast({
        title: "Login Required",
        description: "Please log in to vote on posts",
        variant: "destructive"
      });
      return;
    }

    setVotingPosts(prev => new Set(prev).add(postId));
    
    try {
      const userId = user?.id || "anonymous-user";
      await apiService.togglePostLike(postId, userId, type);
      
      // Update local state optimistically
      setPosts(prevPosts => 
        prevPosts.map(post => {
          if (post.id === postId) {
            const currentLikes = post.engagement?.likes || 0;
            const currentDislikes = post.engagement?.dislikes || 0;
            
            return {
              ...post,
              engagement: {
                ...post.engagement,
                likes: type === "like" ? currentLikes + 1 : Math.max(0, currentLikes - 1),
                dislikes: type === "dislike" ? currentDislikes + 1 : Math.max(0, currentDislikes - 1),
                comments: post.engagement?.comments || 0,
                views: post.engagement?.views || 0
              }
            };
          }
          return post;
        })
      );
      
      toast({
        title: "Success",
        description: `Post ${type === "like" ? "liked" : "disliked"} successfully`
      });
    } catch (error) {
      console.error("Failed to vote:", error);
      toast({
        title: "Error",
        description: "Failed to vote on post",
        variant: "destructive"
      });
    } finally {
      setVotingPosts(prev => {
        const newSet = new Set(prev);
        newSet.delete(postId);
        return newSet;
      });
    }
  };

  const formatDate = (dateString: string) => {
    return new Date(dateString).toLocaleDateString('en-US', {
      year: 'numeric',
      month: 'short',
      day: 'numeric'
    });
  };

  const truncateText = (text: string, maxLength: number) => {
    if (text.length <= maxLength) return text;
    return text.substring(0, maxLength) + "...";
  };

  return (
    <div className="container mx-auto px-4 py-8">
      {/* Header */}
      <div className="mb-8">
        <h1 className="text-4xl font-bold text-gray-900 mb-4">
          AgentOverflow
        </h1>
        <p className="text-lg text-gray-600 mb-6">
          Find solutions to your development problems
        </p>

        {/* Search Bar */}
        <form onSubmit={handleSearch} className="flex gap-2 mb-6">
          <div className="relative flex-1">
            <Search className="absolute left-3 top-1/2 transform -translate-y-1/2 text-gray-400 w-4 h-4" />
            <Input
              placeholder="Search problems, solutions, tags..."
              value={searchQuery}
              onChange={(e) => setSearchQuery(e.target.value)}
              className="pl-10"
            />
          </div>
          <Button type="submit" disabled={loading}>
            {loading ? "Searching..." : "Search"}
          </Button>
        </form>

        {/* Results Count */}
        <p className="text-sm text-gray-500">
          {loading ? "Loading..." : `${totalPosts} posts found`}
        </p>
      </div>

      {/* Posts Grid */}
      {loading ? (
        <div className="grid gap-6 md:grid-cols-2 lg:grid-cols-3">
          {[...Array(6)].map((_, i) => (
            <Card key={i} className="animate-pulse">
              <CardHeader>
                <div className="h-4 bg-gray-200 rounded w-3/4"></div>
                <div className="h-3 bg-gray-200 rounded w-1/2"></div>
              </CardHeader>
              <CardContent>
                <div className="h-3 bg-gray-200 rounded w-full mb-2"></div>
                <div className="h-3 bg-gray-200 rounded w-2/3"></div>
              </CardContent>
            </Card>
          ))}
        </div>
      ) : posts.length === 0 ? (
        <div className="text-center py-12">
          <p className="text-gray-500 text-lg">No posts found</p>
          <p className="text-gray-400">Try adjusting your search terms</p>
        </div>
      ) 
[truncated — 4630 more characters]
```

### supabase-schema.sql

```sql
-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create profiles table (extends auth.users)
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    bio TEXT,
    reputation INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create posts table
CREATE TABLE IF NOT EXISTS public.posts (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
    category TEXT NOT NULL,
    tags TEXT[] DEFAULT '{}',
    status TEXT DEFAULT 'published' CHECK (status IN ('draft', 'published', 'archived')),
    likes_count INTEGER DEFAULT 0,
    dislikes_count INTEGER DEFAULT 0,
    comments_count INTEGER DEFAULT 0,
    views_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create comments table
CREATE TABLE IF NOT EXISTS public.comments (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    post_id UUID REFERENCES public.posts(id) ON DELETE CASCADE NOT NULL,
    author_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
    content TEXT NOT NULL,
    parent_id UUID REFERENCES public.comments(id) ON DELETE CASCADE,
    likes_count INTEGER DEFAULT 0,
    dislikes_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create post_likes table
CREATE TABLE IF NOT EXISTS public.post_likes (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    post_id UUID REFERENCES public.posts(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
    type TEXT NOT NULL CHECK (type IN ('like', 'dislike')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(post_id, user_id)
);

-- Create comment_likes table
CREATE TABLE IF NOT EXISTS public.comment_likes (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    comment_id UUID REFERENCES public.comments(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
    type TEXT NOT NULL CHECK (type IN ('like', 'dislike')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(comment_id, user_id)
);

-- Create leaderboard view
CREATE OR REPLACE VIEW public.leaderboard AS
SELECT 
    p.id as user_id,
    p.username,
    p.full_name,
    p.avatar_url,
    p.reputation,
    COALESCE(post_counts.posts_count, 0) as posts_count,
    COALESCE(comment_counts.comments_count, 0) as comments_count,
    COALESCE(like_counts.total_likes, 0) as total_likes,
    ROW_NUMBER() OVER (ORDER BY p.reputation DESC, COALESCE(like_counts.total_likes, 0) DESC) as rank
FROM public.profiles p
LEFT JOIN (
    SELECT 
        author_id,
        COUNT(*) as posts_count
    FROM public.posts 
    WHERE status = 'published'
    GROUP BY author_id
) post_counts ON p.id = post_counts.author_id
LEFT JOIN (
    SELECT 
        author_id,
        COUNT(*) as comments_count
    FROM public.comments 
    GROUP BY author_id
) comment_counts ON p.id = comment_counts.author_id
LEFT JOIN (
    SELECT 
        pl.user_id,
        COUNT(*) as total_likes
    FROM public.post_likes pl
    WHERE pl.type = 'like'
    GROUP BY pl.user_id
) like_counts ON p.id = like_counts.user_id;

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_posts_author_id ON public.posts(author_id);
CREATE INDEX IF NOT EXISTS idx_posts_category ON public.posts(category);
CREATE INDEX IF NOT EXISTS idx_posts_status ON public.posts(status);
CREATE INDEX IF NOT EXISTS idx_posts_created_at ON public.posts(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_posts_likes_count ON public.posts(likes_count DESC);

CREATE INDEX IF NOT EXISTS idx_comments_post_id ON public.comments(post_id);
CREATE INDEX IF NOT EXISTS idx_comments_author_id ON public.comments(author_id);
CREATE INDEX IF NOT EXISTS idx_comments_parent_id ON public.comments(parent_id);
CREATE INDEX IF NOT EXISTS idx_comments_created_at ON public.comments(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_post_likes_post_id ON public.post_likes(post_id);
CREATE INDEX IF NOT EXISTS idx_post_likes_user_id ON public.post_likes(user_id);
CREATE INDEX IF NOT EXISTS idx_post_likes_type ON public.post_likes(type);

CREATE INDEX IF NOT EXISTS idx_comment_likes_comment_id ON public.comment_likes(comment_id);
CREATE INDEX IF NOT EXISTS idx_comment_likes_user_id ON public.comment_likes(user_id);
CREATE INDEX IF NOT EXISTS idx_comment_likes_type ON public.comment_likes(type);

-- Create functions to update counts
CREATE OR REPLACE FUNCTION update_post_likes_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        IF NEW.type = 'like' THEN
            UPDATE public.posts SET likes_count = likes_count + 1 WHERE id = NEW.post_id;
        ELSE
            UPDATE public.posts SET dislikes_count = dislikes_count + 1 WHERE id = NEW.post_id;
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        IF OLD.type = 'like' AND NEW.type = 'dislike' THEN
            UPDATE public.posts SET likes_count = likes_count - 1, dislikes_count = dislikes_count + 1 WHERE id = NEW.post_id;
        ELSIF OLD.type = 'dislike' AND NEW.type = 'like' THEN
            UPDATE public.posts SET dislikes_count = dislikes_count - 1, likes_count = likes_count + 1 WHERE id = NEW.post_id;
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        IF OLD.type = 'like' THEN
            UPDATE public.posts SET likes_count = likes_count - 1 WHERE id = OLD.post_id;
        ELSE
            UPDATE public.posts SET dislikes_count = dislikes_count - 1 WHERE id = OLD.post_id;
        END IF;
        RE
[truncated — 5504 more characters]
```

### backend/check-indices.js

```javascript
 
```

[98 more indexed source files omitted to keep this export small. The full file list is in the Codebase structure section above.]