How We Accelerate Customer Support with an Online Knowledge Base
Imagine your site has hundreds of support articles, customers can't find answers, and support tickets keep duplicating. A help center with proper search and a Q&A section solves this pain — but only if implemented correctly, considering performance and usability. We've been doing such projects for years: designed and deployed documentation portals for dozens of companies — from startups to enterprise. One of the key issues is search speed: users won't wait more than a second. The solution is a full-text index on the database side.
Problems We Solve
The first problem is N+1 queries when loading a list of articles with categories. The second is slow LIKE-based search on article body. The third is lack of feedback: customers can't rate article usefulness, and the team doesn't know which materials need improvement. A typical scenario: a site has 15,000 articles, search uses LIKE '%query%', response time 5–10 seconds. After migration to PostgreSQL tsvector, response time drops to 10 milliseconds — that's up to 500 times faster.
How We Design the Database Schema
We build category hierarchy using self-reference parent_id. For each level, we store an icon and sort order. Articles are linked to a category, with a slug and numeric counters.
CREATE TABLE kb_categories (
id SERIAL PRIMARY KEY,
parent_id INTEGER REFERENCES kb_categories(id),
name VARCHAR(150) NOT NULL,
slug VARCHAR(150) NOT NULL UNIQUE,
icon VARCHAR(50),
sort_order INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE kb_articles (
id SERIAL PRIMARY KEY,
category_id INTEGER REFERENCES kb_categories(id),
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL UNIQUE,
excerpt TEXT,
body TEXT NOT NULL,
body_search TSVECTOR GENERATED ALWAYS AS (
to_tsvector('russian', title || ' ' || body)
) STORED,
helpful_yes INTEGER NOT NULL DEFAULT 0,
helpful_no INTEGER NOT NULL DEFAULT 0,
views_count INTEGER NOT NULL DEFAULT 0,
is_published BOOLEAN NOT NULL DEFAULT true,
sort_order INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ON kb_articles USING gin(body_search);
CREATE INDEX ON kb_articles(category_id, is_published, sort_order);
CREATE TABLE faq_items (
id SERIAL PRIMARY KEY,
category VARCHAR(100),
question TEXT NOT NULL,
answer TEXT NOT NULL,
sort_order INTEGER NOT NULL DEFAULT 0
);
The body_search field is a generated column, eliminating data desynchronization. The GIN index delivers search speeds <10ms on 10K articles. We use a separate table for Q&A items — it simplifies ordering and categorization.
How to Implement Full-Text Search on PostgreSQL?
We use plainto_tsquery('russian', $query) and rank with ts_rank. A Laravel controller:
class KnowledgeBaseController extends Controller
{
// Full-text search
public function search(Request $request): JsonResponse
{
$query = trim($request->input('q', ''));
if (strlen($query) < 2) {
return response()->json(['data' => [], 'query' => $query]);
}
$articles = KbArticle::published()
->whereRaw(
"body_search @@ plainto_tsquery('russian', ?)",
[$query]
)
->selectRaw("*, ts_rank(body_search, plainto_tsquery('russian', ?)) as rank", [$query])
->orderByDesc('rank')
->limit(10)
->get(['id', 'title', 'slug', 'excerpt', 'category_id', 'rank']);
return response()->json([
'data' => KbArticleResource::collection($articles),
'query' => $query,
]);
}
// Article with view tracking
public function show(string $slug): JsonResponse
{
$article = KbArticle::published()
->with('category')
->where('slug', $slug)
->firstOrFail();
// Increment views (async)
dispatch(fn() => $article->increment('views_count'))->afterResponse();
// Related articles in same category
$related = KbArticle::published()
->where('category_id', $article->category_id)
->where('id', '!=', $article->id)
->orderByDesc('views_count')
->limit(5)
->get(['id', 'title', 'slug']);
return response()->json([
'article' => KbArticleResource::make($article),
'related' => $related,
]);
}
// Rate article helpfulness
public function helpful(Request $request, KbArticle $article): JsonResponse
{
$request->validate(['helpful' => 'required|boolean']);
$session = $request->session()->getId();
$key = "helpful:{$article->id}:{$session}";
if (Cache::has($key)) {
return response()->json(['already_voted' => true]);
}
Cache::put($key, true, now()->addDays(30));
if ($request->boolean('helpful')) {
$article->increment('helpful_yes');
} else {
$article->increment('helpful_no');
}
return response()->json([
'yes' => $article->fresh()->helpful_yes,
'no' => $article->fresh()->helpful_no,
]);
}
}
The show method uses afterResponse() — views are counted without slowing down the response. Vote caching prevents abuse. The response includes related articles, increasing time on site.
React Components: FAQ Accordion and Search
We build the frontend with React or Vue — doesn't matter. Here's a React example.
FAQ Accordion
import { useState } from 'react';
interface FaqItem {
id: number;
question: string;
answer: string;
}
function FaqAccordion({ items, category }: { items: FaqItem[]; category: string }) {
const [openId, setOpenId] = useState<number | null>(null);
return (
<section>
<h2>{category}</h2>
<dl>
{items.map(item => (
<div key={item.id} className={`faq-item ${openId === item.id ? 'open' : ''}`}>
<dt>
<button
onClick={() => setOpenId(openId === item.id ? null : item.id)}
aria-expanded={openId === item.id}
aria-controls={`faq-answer-${item.id}`}
>
{item.question}
<span aria-hidden>{openId === item.id ? '−' : '+'}</span>
</button>
</dt>
<dd
id={`faq-answer-${item.id}`}
hidden={openId !== item.id}
>
<div dangerouslySetInnerHTML={{ __html: item.answer }} />
</dd>
</div>
))}
</dl>
</section>
);
}
Implemented following Accessibility guidelines: aria-expanded, aria-controls, hidden. The answer can contain HTML (images, links).
Search with Debounce
function KbSearch() {
const [query, setQuery] = useState('');
const [results, setResults] = useState<KbArticle[]>([]);
useEffect(() => {
if (query.length < 2) { setResults([]); return; }
const timer = setTimeout(async () => {
const { data } = await api.get('/api/kb/search', { params: { q: query } });
setResults(data.data);
}, 300);
return () => clearTimeout(timer);
}, [query]);
return (
<div className="kb-search">
<input
type="search"
value={query}
onChange={e => setQuery(e.target.value)}
placeholder="Search the knowledge base..."
aria-label="Search the knowledge base"
/>
{results.length > 0 && (
<ul className="kb-search__results" role="listbox">
{results.map(article => (
<li key={article.id} role="option">
<a href={`/help/${article.slug}`}>
<strong>{article.title}</strong>
<p>{article.excerpt}</p>
</a>
</li>
))}
</ul>
)}
</div>
);
}
300ms debounce prevents excessive requests. Uses role="listbox" and role="option" for accessibility.
What If You Need to Find Answers Quickly?
Search with autocomplete is the minimum. Additionally, we can implement filtering by category, sorting by popularity or date. In complex cases, we integrate Elasticsearch, but for 95% of projects, PostgreSQL tsvector is sufficient.
How Long Does Development Take?
| Stage | Duration (working days) | What's Included |
|---|---|---|
| Database + API design | 1–2 | ER diagram, migrations, Laravel controllers |
| Basic frontend | 1–2 | Category pages, article page, search |
| FAQ accordion | 0.5–1 | React component, Schema.org markup |
| Helpfulness rating | 0.5–1 | Voting API, cache, view tracking |
| Integration + tests | 1 | Feature tests, query optimization |
Total: 4 to 5 days for a standard knowledge base with Q&A section. Timeline may increase if integration with external systems or custom analytics is required. A typical project costs between $2,500 and $5,000, often saving companies thousands in support overhead annually.
Search Method Comparison
| Criterion | LIKE '%query%' | PostgreSQL tsvector | Elasticsearch |
|---|---|---|---|
| Time on 10K articles | ~5 s | <10 ms | <5 ms |
| Ranking | None | ts_rank | BM25 |
| Morphology | None | Russian morphology | Full |
| Implementation complexity | 0 | 1 day | 2–3 days |
Docker Compose configuration for PostgreSQL
version: '3.8'
services:
postgres:
image: postgres:16
environment:
POSTGRES_DB: knowledge_base
POSTGRES_USER: kb_user
POSTGRES_PASSWORD: secret
ports:
- '5432:5432'
volumes:
- pgdata:/var/lib/postgresql/data
volumes:
pgdata:
PostgreSQL documentation on full-text search: https://www.postgresql.org/docs/current/textsearch.html
What You Get
- Source code in Laravel 11 / React 18 (TypeScript)
- Migrations and seeders for test data
- Postman collection or Swagger for API
- Deployment documentation (Docker compose or hosting instructions)
- Access to repository and demo environment
- 2 weeks free bug guarantee
Why Choose Us
- 7+ years of web development experience, 5+ of which with Laravel and React
- Delivered 30+ projects related to knowledge bases, documentation, and portals
- Guarantee no spelling errors and valid HTML/CSS markup
- Certified Laravel Certified, AWS Cloud Practitioner
Contact us to discuss your project — we'll assess your task and propose the best solution. Request a demo to see results on a live project.