Veritabanı Araçları
Postgres Text Search

warningProblem

"Show HN: Postgres extension for BM25 relevance-ranked full-text search"
psychologyPotansiyel Çözüm

Tiger Data'nın yaşadığı sorunu (Postgres'te ölçeklenebilir ve açık kaynaklı bir BM25 tam metin arama çözümü bulma zorluğu) ele alan, geliştiricilerin ve şirketlerin PostgreSQL veritabanlarına entegre edebilecekleri, yüksek performanslı, açık kaynaklı ve AGPL lisans kısıtlaması olmayan bir BM25 tam metin arama uzantısı sunan bir SaaS platformu.
groupHedef Kitle

PostgreSQL veritabanı kullanan geliştiriciler, veritabanı yöneticileri (DBA'lar), veri mühendisleri ve AI/ML odaklı uygulamalar geliştiren şirketler. Özellikle pgvector gibi vektör arama çözümlerini kullanan ancak geleneksel anahtar kelime tabanlı sıralı arama için de güvenilir ve ölçeklenebilir bir çözüme ihtiyaç duyan kullanıcılar hedeflenmektedir. AGPL lisansı nedeniyle mevcut çözümleri kullanamayan veya kendi özel çözümlerini geliştirmek istemeyen firmalar da potansiyel müşteriler arasındadır.
paymentsGelir Modeli

Ücretsiz açık kaynaklı uzantı sunulacak. Bunun yanı sıra, uzantının yönetimi, bakımı, güncellemeleri, gelişmiş destek, kurumsal düzeyde özellikler (örn: daha gelişmiş analiz, güvenlik özellikleri, yönetilen hizmetler) ve özel entegrasyonlar için ücretli abonelik modelleri (örneğin, Pro, Enterprise planları) sunulacaktır. Ayrıca, SaaS platformu üzerinden yönetilen bir hizmet olarak da sunulabilir.
Aksiyon Planı

PostgreSQL veritabanlarına kolay kurulum ve entegrasyon için sağlanan `pg_textsearch` uzantısı.
BM25 algoritmasını temel alan yüksek performanslı tam metin arama fonksiyonelliği.
Skalabilite: Büyük veri kümelerinde bile performansı koruyabilen optimize edilmiş sorgu işleme.
Esnek sorgu seçenekleri: Boolean operatörleri (AND, OR, NOT), wildcard aramalar, fuzzy eşleşme (opsiyonel MVP sonrası).
Geliştirici dostu API/SDK: Uygulama içinden arama fonksiyonlarına erişimi kolaylaştıran araçlar.
Pazar Analizi

8.2Puan
Kaynak: Hacker Newsopen_in_new
AI Prompt

You are an AI assistant tasked with generating a fully functional, multi-page Next.js MVP application for a PostgreSQL BM25 full-text search extension. The application should be visually impressive, robust, and include authentication, CRUD operations, and a clean user interface. This prompt must be comprehensive enough to be directly usable by a development team or an AI coding assistant.

## 1. PROJECT OVERVIEW

**Project Title:** Postgres Text Search

**Problem Solved:** Many companies using PostgreSQL for their data infrastructure face challenges in implementing a scalable, high-performance, and permissively licensed (e.g., not AGPL) BM25 full-text search solution. Existing solutions might be proprietary, complex to implement, or come with restrictive licenses. This project addresses this gap by providing an open-source, easy-to-integrate PostgreSQL extension (`pg_textsearch`) and a supporting SaaS platform for management, advanced features, and support.

**Core Value Proposition:** Empower developers and organizations to leverage powerful, ranked keyword search directly within their PostgreSQL databases without licensing constraints. The platform will offer a user-friendly interface for managing search configurations, monitoring performance, and accessing advanced functionalities, complemented by a freely available, high-performance open-source extension.

**Target Audience:** Developers, Database Administrators (DBAs), Data Engineers, and companies building AI/ML-centric applications who use PostgreSQL and require a robust, scalable keyword search solution. This includes users of vector search extensions (like pgvector) needing a complementary text search capability.

## 2. TECH STACK

*   **Frontend Framework:** React (Next.js 14+ with App Router)
*   **Styling:** Tailwind CSS (v3.x)
*   **UI Components:** shadcn/ui (built on Radix UI and Tailwind CSS)
*   **Database ORM:** Drizzle ORM (for PostgreSQL)
*   **Database:** PostgreSQL (required for the extension, local dev can use Docker)
*   **Authentication:** NextAuth.js (or Clerk for a quicker integration)
*   **State Management:** Zustand or React Context API for global state, component-local state for UI elements.
*   **API Layer:** Next.js API Routes (App Router handlers)
*   **Data Fetching:** React Server Components (RSC), Client Components with `fetch` API, SWR or React Query for client-side caching and revalidation.
*   **Form Handling:** React Hook Form + Zod for validation.
*   **Deployment:** Vercel (recommended for Next.js)
*   **Essential Packages:** `pg` (node-postgres driver), `@types/pg`, `zod`, `@hookform/resolvers/zod`, `uuid` (or similar for IDs), `clsx` (for conditional class names).

## 3. DATABASE SCHEMA

We will primarily focus on the SaaS platform's database schema, assuming the `pg_textsearch` extension is installed directly on the user's PostgreSQL instance.

**Database:** PostgreSQL (via Drizzle ORM)

**Tables:**

1.  **`users`**
    *   `id`: UUID (Primary Key, default gen_random_uuid())
    *   `name`: VARCHAR(255)
    *   `email`: VARCHAR(255) UNIQUE NOT NULL
    *   `emailVerified`: TIMESTAMP WITH TIME ZONE
    *   `image`: TEXT
    *   `createdAt`: TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    *   `updatedAt`: TIMESTAMP WITH TIME ZONE DEFAULT NOW()

2.  **`accounts`** (for NextAuth.js)
    *   `id`: BIGSERIAL PRIMARY KEY
    *   `userId`: UUID REFERENCES `users`(`id`) ON DELETE CASCADE
    *   `type`: VARCHAR(255) NOT NULL
    *   `provider`: VARCHAR(255) NOT NULL
    *   `providerAccountId`: VARCHAR(255) NOT NULL
    *   `refresh_token`: TEXT
    *   `access_token`: TEXT
    *   `expires_at`: BIGINT
    *   `token_type`: VARCHAR(255)
    *   `scope`: VARCHAR(255)
    *   `id_token`: TEXT
    *   `session_state`: VARCHAR(255)
    *   UNIQUE (`provider`, `providerAccountId`)

3.  **`sessions`** (for NextAuth.js)
    *   `id`: BIGSERIAL PRIMARY KEY
    *   `sessionToken`: VARCHAR(255) UNIQUE NOT NULL
    *   `userId`: UUID REFERENCES `users`(`id`) ON DELETE CASCADE
    *   `expires`: TIMESTAMP WITH TIME ZONE NOT NULL

4.  **`verificationTokens`** (for NextAuth.js)
    *   `identifier`: VARCHAR(255) NOT NULL
    *   `token`: VARCHAR(255) NOT NULL UNIQUE
    *   `expires`: TIMESTAMP WITH TIME ZONE NOT NULL

5.  **`databases`** (Represents user's connected PostgreSQL instances)
    *   `id`: UUID PRIMARY KEY DEFAULT gen_random_uuid()
    *   `userId`: UUID REFERENCES `users`(`id`) ON DELETE CASCADE
    *   `name`: VARCHAR(255) NOT NULL
    *   `connectionString`: TEXT NOT NULL (Encrypted in production)
    *   `host`: VARCHAR(255)
    *   `port`: INTEGER
    *   `database`: VARCHAR(255)
    *   `user`: VARCHAR(255)
    *   `password`: TEXT (Encrypted)
    *   `createdAt`: TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    *   `updatedAt`: TIMESTAMP WITH TIME ZONE DEFAULT NOW()

6.  **`tables`** (Tracks tables within connected databases to be indexed)
    *   `id`: UUID PRIMARY KEY DEFAULT gen_random_uuid()
    *   `databaseId`: UUID REFERENCES `databases`(`id`) ON DELETE CASCADE
    *   `tableName`: VARCHAR(255) NOT NULL
    *   `primaryKeyColumn`: VARCHAR(255) NOT NULL
    *   `searchableColumns`: TEXT[] NOT NULL
    *   `indexName`: VARCHAR(255) UNIQUE NOT NULL (e.g., `idx_my_table_textsearch`)
    *   `status`: VARCHAR(50) DEFAULT 'pending' (e.g., 'pending', 'indexing', 'active', 'error')
    *   `createdAt`: TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    *   `updatedAt`: TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    *   UNIQUE (`databaseId`, `tableName`)

7.  **`searchConfigurations`** (Optional, for advanced tuning if needed beyond basic extension settings)
    *   `id`: UUID PRIMARY KEY DEFAULT gen_random_uuid()
    *   `tableId`: UUID REFERENCES `tables`(`id`) ON DELETE CASCADE
    *   `parameterName`: VARCHAR(100) NOT NULL
    *   `parameterValue`: VARCHAR(255) NOT NULL
    *   `createdAt`: TIMESTAMP WITH TIME ZONE DEFAULT NOW()

**Relationships:**
*   One `user` can have many `databases`.
*   One `database` can have many `tables`.
*   One `table` can have many `searchConfigurations`.

## 4. CORE FEATURES & USER FLOW

**MVP Goal:** Allow users to connect their PostgreSQL database, select a table, specify columns for indexing, and enable BM25 full-text search via the `pg_textsearch` extension. Provide a basic dashboard to monitor status.

**Feature 1: User Authentication**
*   **Flow:**
    1.  User lands on the homepage.
    2.  Clicks 'Sign Up' or 'Sign In'.
    3.  Option to sign up/in using Google, GitHub, or email/password.
    4.  Upon successful authentication, user is redirected to their dashboard.
    5.  Session is managed via NextAuth.js cookies.
*   **Edge Cases:** Invalid credentials, email verification (if email/password used), password reset, OAuth errors.

**Feature 2: Database Connection Management**
*   **Flow:**
    1.  Authenticated user navigates to 'Database Connections' in the dashboard.
    2.  Clicks 'Add New Connection'.
    3.  User provides connection details (connection string or individual fields: host, port, db name, user, password).
    4.  The platform attempts to validate the connection by pinging the database and checking if the `pg_textsearch` extension is installed (this check might be basic in MVP, assuming user installs it manually first).
    5.  If validation is successful, the connection is saved (sensitive credentials encrypted).
    6.  User can view, edit, or delete existing connections.
*   **Edge Cases:** Invalid connection string/details, network errors, extension not found, insufficient permissions, credential encryption/decryption failures.

**Feature 3: Table Indexing Configuration**
*   **Flow:**
    1.  From the 'Database Connections' list, user clicks 'Manage Tables' for a specific connection.
    2.  The platform fetches a list of tables from the selected PostgreSQL database.
    3.  User selects a table to index.
    4.  User specifies the primary key column of the table.
    5.  User selects one or more columns to be included in the full-text search index.
    6.  User defines a name for the search index (e.g., `my_table_fts_idx`). The MVP will generate a default name based on table name.
    7.  User clicks 'Create Index'.
    8.  The platform generates and executes the necessary SQL command (e.g., `CREATE EXTENSION IF NOT EXISTS pg_textsearch; CREATE INDEX ... USING pg_textsearch(...)`) on the user's database via the saved connection.
    9.  The status of the index creation is updated in the `tables` table ('pending', 'indexing', 'active', 'error').
*   **Edge Cases:** Table not found, primary key not specified, selected columns do not exist, SQL execution errors, index naming conflicts, insufficient database privileges to create indexes.

**Feature 4: Search Execution (via API/Direct SQL)**
*   **Flow (Illustrative - MVP focuses on setup, direct SQL use is primary):**
    1.  Once an index is 'active', the user can perform searches directly on their PostgreSQL database using SQL queries against the defined index.
    2.  Example SQL Query: `SELECT id, title FROM articles WHERE pg_textsearch(to_tsvector('english', title || ' ' || body), 'search & query') @@ to_tsquery('english', 'search & query');` (Syntax depends on final `pg_textsearch` extension API).
    3.  The MVP *platform* might include a simple query builder or a 'playground' to test queries, but the primary interaction is expected to be direct SQL from the user's applications.
*   **Edge Cases:** Invalid search query syntax, no results found, index not active.

**Feature 5: Dashboard & Monitoring**
*   **Flow:**
    1.  Authenticated user lands on the main dashboard.
    2.  Displays a list of connected databases and their indexing status.
    3.  Shows the status ('pending', 'indexing', 'active', 'error') for each indexed table.
    4.  Basic stats: number of indexed tables, errors encountered.
*   **Edge Cases:** No databases connected, no tables indexed, errors in status reporting.

## 5. API & DATA FETCHING

*   **Authentication:** Handled by NextAuth.js, API routes are protected.
*   **Database Connection API (`/api/v1/connections`)**
    *   `POST /`: Add new connection. Req Body: `{ name: string, connectionString: string }` or `{ name: string, host: string, port: number, database: string, user: string, password: string }`. Res Body: `{ success: boolean, data?: Database, error?: string }`.
    *   `GET /`: Get all connections for the logged-in user. Res Body: `{ success: boolean, data: Database[] }`.
    *   `PUT /:id`: Update connection. Req Body: (same as POST). Res Body: `{ success: boolean, data?: Database, error?: string }`.
    *   `DELETE /:id`: Delete connection. Res Body: `{ success: boolean, error?: string }`.
*   **Table Indexing API (`/api/v1/tables`)**
    *   `POST /`: Create/enable index for a table. Req Body: `{ databaseId: string, tableName: string, primaryKeyColumn: string, searchableColumns: string[], indexName?: string }`. Res Body: `{ success: boolean, data?: Table, error?: string }`.
    *   `GET /:databaseId`: Get tables and their indexing status for a database. Res Body: `{ success: boolean, data: Table[] }`.
    *   `DELETE /:id`: Disable/remove index. Res Body: `{ success: boolean, error?: string }`.
*   **Data Fetching Strategy:**
    *   **Server Components (Dashboard, Connection List):** Fetch data directly within Server Components using Drizzle ORM queries to the platform's database. Example: `const databases = await db.query.users.findFirst({ where: (u, { eq }) => eq(u.id, userId), with: { databases: true } });`
    *   **Client Components (Forms, Modals):** Use API routes with `fetch` or a library like SWR/React Query. Example form submission POST request using `fetch`.
    *   **Real-time Status:** For index status updates, consider polling or WebSockets for a more advanced MVP. For MVP, simple refresh or manual refresh will suffice.

## 6. COMPONENT BREAKDOWN (Next.js App Router)

*   **`app/layout.tsx`**: Root layout (includes Providers, global styles, header/footer).
*   **`app/page.tsx`**: Landing Page (marketing content, features overview, call to action).
*   **`app/auth/signin/page.tsx`**: Sign In page (using NextAuth.js providers).
*   **`app/(protected)/layout.tsx`**: Protected route layout (sidebar, main content area).
*   **`app/(protected)/dashboard/page.tsx`**: Main Dashboard (Overview of connections, index status).
*   **`app/(protected)/connections/page.tsx`**: Database Connections List page.
*   **`app/(protected)/connections/new/page.tsx`**: Add New Database Connection Form page.
*   **`app/(protected)/connections/[dbId]/page.tsx`**: Manage Tables for a specific Database page (list tables, add/manage indexes).
*   **`app/(protected)/settings/page.tsx`**: User Settings page (profile, etc.).
*   **`components/ui/`**: Re-usable shadcn/ui components (Button, Input, Card, Table, Form, Dialog, etc.).
*   **`components/layout/`**: Header, Sidebar, Footer, PageWrapper.
*   **`components/auth/`**: Auth providers, SignOutButton.
*   **`components/dashboard/`**: DashboardWidgets (StatusCard, ConnectionTable).
*   **`components/connections/`**: ConnectionForm, ConnectionList, ConnectionItem.
*   **`components/tables/`**: TableIndexForm, TableList, TableItem.
*   **`components/ui/`**: Custom components built using shadcn/ui primitives (e.g., `StatusIndicator`, `LoadingSpinner`).
*   **`lib/`**: Utility functions, API clients, database connection logic, Zod schemas.
*   **`hooks/`**: Custom React hooks (e.g., `useDatabases`, `useTables`).
*   **`providers/`**: Context providers (e.g., `ThemeProvider`, `QueryProvider` if using React Query).

**State Management:**
*   **Global:** User authentication state (via NextAuth.js session context), theme settings (Zustand or Context).
*   **Server State:** Data fetched in Server Components is directly available.
*   **Client State:** Local component state for form inputs, modal visibility, UI toggles.
*   **Server/Shared State (Client):** For data that needs to be fetched, cached, and updated on the client (e.g., list of tables, connection status), use SWR or React Query hooks.

## 7. UI/UX DESIGN & VISUAL IDENTITY

*   **Design Style:** Modern, Clean, Professional, Developer-Focused.
*   **Color Palette:**
    *   Primary: Deep Blue (`#1E3A8A` - slate-800)
    *   Secondary: Teal (`#06B6D4` - cyan-400)
    *   Accent/Action: Bright Blue/Purple Gradient (`#6366F1` to `#8B5CF6` - indigo-500 to violet-500)
    *   Background: Dark Gray (`#1F2937` - gray-800)
    *   Surface/Card Background: Slightly Lighter Gray (`#27303A` - gray-700/800)
    *   Text (Primary): Light Gray (`#E5E7EB` - gray-200)
    *   Text (Secondary): Muted Gray (`#9CA3AF` - gray-400)
    *   Success: Green (`#10B981` - green-500)
    *   Warning/Error: Red (`#EF4444` - red-500)
*   **Typography:**
    *   Headings: Inter (Variable Font) - Bold
    *   Body Text: Inter (Variable Font) - Regular
    *   Code/Monospace: Source Code Pro or similar
*   **Layout:**
    *   Use a two-column layout for protected routes: Fixed sidebar on the left, main content area on the right.
    *   Sidebar contains navigation links (Dashboard, Connections, Settings).
    *   Main content area uses cards and tables for displaying information.
    *   Forms should be clean, with clear labels and validation feedback.
*   **Responsiveness:** Mobile-first approach. Sidebar collapses into a hamburger menu on smaller screens. Content adapts gracefully.
*   **Visual Elements:** Subtle gradients on buttons or active states, clean icons (e.g., Lucide Icons), minimal use of shadows, focus on clear data presentation.

## 8. SAMPLE/MOCK DATA

**Mock Database Connections:**
1.  `{ id: 'uuid-1', userId: 'user-abc', name: 'Production DB', connectionString: 'postgresql://user:pass@host:5432/prod_db', createdAt: '2023-10-26T10:00:00Z', updatedAt: '2023-10-26T10:00:00Z' }`
2.  `{ id: 'uuid-2', userId: 'user-abc', name: 'Staging DB', connectionString: 'postgresql://user:pass@stage-host:5432/staging_db', createdAt: '2023-10-26T10:05:00Z', updatedAt: '2023-10-26T10:05:00Z' }`

**Mock Tables (for 'Production DB'):**
1.  `{ id: 'uuid-t1', databaseId: 'uuid-1', tableName: 'users', primaryKeyColumn: 'id', searchableColumns: ['name', 'email'], indexName: 'idx_users_textsearch', status: 'active', createdAt: '2023-10-27T11:00:00Z', updatedAt: '2023-10-27T11:05:00Z' }`
2.  `{ id: 'uuid-t2', databaseId: 'uuid-1', tableName: 'articles', primaryKeyColumn: 'article_id', searchableColumns: ['title', 'content_body'], indexName: 'idx_articles_textsearch', status: 'active', createdAt: '2023-10-27T11:10:00Z', updatedAt: '2023-10-27T11:15:00Z' }`
3.  `{ id: 'uuid-t3', databaseId: 'uuid-1', tableName: 'products', primaryKeyColumn: 'product_sku', searchableColumns: ['product_name', 'description'], indexName: 'idx_products_textsearch', status: 'pending', createdAt: '2023-10-27T11:20:00Z', updatedAt: '2023-10-27T11:20:00Z' }`
4.  `{ id: 'uuid-t4', databaseId: 'uuid-1', tableName: 'logs', primaryKeyColumn: 'log_id', searchableColumns: ['message'], indexName: 'idx_logs_textsearch', status: 'error', createdAt: '2023-10-27T11:25:00Z', updatedAt: '2023-10-27T11:30:00Z' }`

**Mock User:**
*   `{ id: 'user-abc', name: 'Jane Doe', email: 'jane.doe@example.com', emailVerified: '2023-01-15T09:00:00Z', image: 'https://example.com/avatar.jpg' }`

**Mock Search Results (Illustrative):**
*   For `articles` table searching 'Next.js performance':
    `[{ article_id: 'uuid-a1', title: 'Optimizing Next.js App Router Performance' }, { article_id: 'uuid-a5', title: 'Deep Dive into Next.js Server Components' }]`

## 9. TURKISH TRANSLATIONS

*   **App Title:** Postgres Metin Araması
*   **Hero Section:** PostgreSQL'de Ölçeklenebilir Tam Metin Arama. `pg_textsearch` ile tanışın.
*   **Buttons:**
    *   Sign Up: Kayıt Ol
    *   Sign In: Giriş Yap
    *   Add Connection: Bağlantı Ekle
    *   Create Index: İndeks Oluştur
    *   Manage Tables: Tabloları Yönet
    *   Save: Kaydet
    *   Cancel: İptal
    *   Edit: Düzenle
    *   Delete: Sil
*   **Labels & Placeholders:**
    *   Database Name: Veritabanı Adı
    *   Connection String: Bağlantı Dizisi
    *   Host: Sunucu
    *   Port: Port
    *   Username: Kullanıcı Adı
    *   Password: Şifre
    *   Table Name: Tablo Adı
    *   Primary Key Column: Birincil Anahtar Sütunu
    *   Searchable Columns: Aranabilir Sütunlar
    *   Index Name: İndeks Adı
    *   Search Query: Arama Sorgusu
*   **Status Messages:**
    *   Connecting: Bağlanıyor...
    *   Indexing: İndeksleniyor...
    *   Active: Aktif
    *   Error: Hata
    *   Pending: Bekliyor
    *   Connection Successful: Bağlantı Başarılı
    *   Index Created Successfully: İndeks Başarıyla Oluşturuldu
*   **Navigation:**
    *   Dashboard: Pano
    *   Connections: Bağlantılar
    *   Settings: Ayarlar

## 10. ANIMATIONS & TRANSITIONS

*   **Page Transitions:** Subtle fade-in/out transitions between pages using Next.js's built-in capabilities or a library like `Framer Motion` for more complex animations if needed (e.g., slide-in from right for new pages).
*   **Hover Effects:** Slight scale-up or background color change on interactive elements like buttons, cards, and table rows.
*   **Loading States:** Use skeleton loaders or spinners (from shadcn/ui or custom) for data fetching operations. Ensure smooth transitions into loaded states.
*   **Form Feedback:** Animate validation error messages appearing below input fields.
*   **Element Transitions:** Smooth transitions for expanding/collapsing elements (e.g., accordions, dropdowns).

## 11. EDGE CASES & VALIDATION SUMMARY

*   **Authentication:** Robust handling of login/logout, session expiration, token refresh.
*   **Database Connections:** Comprehensive validation of connection details. Graceful error handling for network issues, authentication failures, or permission errors on the target DB. Secure storage and retrieval of credentials.
*   **Table Indexing:** Validation of table/column names, primary key presence. SQL execution error handling and reporting. Handling cases where the extension might not be installed or enabled on the user's DB.
*   **Empty States:** Display user-friendly messages and clear calls to action when lists are empty (e.g., 'No database connections yet. Add your first connection!').
*   **API Errors:** Consistent error response format for all API endpoints. Client-side handling of API errors with user feedback.
*   **Permissions:** Ensure the application only allows actions permitted by the logged-in user's role (though MVP is single-user focused).
*   **Concurrency:** Handle potential race conditions if users try to create/delete indexes simultaneously (less critical for MVP).
*   **Data Integrity:** Ensure database schema changes are reflected correctly, and operations maintain data integrity.