You are a Senior Full-Stack Developer and Startup Consultant tasked with building a comprehensive, fully functional MVP of a SaaS application called 'SQLite Power Tools'. This application aims to democratize advanced SQLite features like JSON querying, FTS5 full-text search, and analytical functions (CTEs, Window Functions) for developers and data analysts, enabling them to leverage these capabilities within a single SQLite database file without external services.
PROJECT OVERVIEW:
'SQLite Power Tools' is a web-based SaaS application designed to empower developers and data analysts by providing an intuitive, visual interface for utilizing advanced features of SQLite. The core problem it solves is the underutilization and perceived complexity of powerful built-in SQLite functionalities like JSON handling, full-text search (FTS5), CTEs, and window functions. By offering a user-friendly GUI, the application aims to reduce the need for external databases or search services for many use cases, promoting local-first development, simplifying data management, and improving the efficiency of data querying and analysis directly within SQLite.
Key Value Proposition: Unlock the hidden power of SQLite with an easy-to-use, visual interface for advanced features, keeping your data and operations within a single database file.
TECH STACK:
- **Frontend:** Next.js (App Router) with React
- **Styling:** Tailwind CSS
- **UI Library:** shadcn/ui (for accessible, reusable components)
- **State Management:** React Context API / Zustand (for global state, e.g., auth, current project)
- **ORM/Database Client:** Drizzle ORM (for SQL generation, especially useful for interacting with potential backend DB for user accounts/projects if not purely client-side processed), `better-sqlite3` (if backend processing is needed for large files, otherwise, client-side SQLite WASM)
- **Authentication:** NextAuth.js (for robust user authentication)
- **Deployment:** Vercel/Netlify
- **Database (User/Project Metadata):** PostgreSQL (or similar managed SQL DB for storing user info, project metadata if not all processing is client-side)
- **Client-side DB Processing:** SQLite WASM (for processing user-uploaded .db files directly in the browser for enhanced privacy and performance for smaller files)
DATABASE SCHEMA (for User/Project Metadata - if backend processing is involved):
1. **users** table:
* `id` (UUID, primary key)
* `name` (VARCHAR)
* `email` (VARCHAR, unique)
* `emailVerified` (TIMESTAMP)
* `image` (VARCHAR, optional)
* `createdAt` (TIMESTAMP, default: now())
* `updatedAt` (TIMESTAMP, default: now())
2. **accounts** table (for NextAuth.js):
* `id` (BIGSERIAL, primary key)
* `userId` (UUID, foreign key to users.id)
* `type` (VARCHAR)
* `provider` (VARCHAR)
* `providerAccountId` (VARCHAR)
* `refresh_token` (TEXT, optional)
* `access_token` (TEXT, optional)
* `expires_at` (BIGINT, optional)
* `token_type` (VARCHAR, optional)
* `scope` (VARCHAR, optional)
* `id_token` (TEXT, optional)
* `session_state` (VARCHAR, optional)
3. **sessions** table (for NextAuth.js):
* `sessionToken` (VARCHAR, primary key)
* `userId` (UUID, foreign key to users.id)
* `expires` (TIMESTAMP)
4. **verification_tokens** table (for NextAuth.js):
* `identifier` (VARCHAR)
* `token` (VARCHAR)
* `expires` (TIMESTAMP)
5. **projects** table (for managing user's SQLite files/configurations):
* `id` (UUID, primary key)
* `userId` (UUID, foreign key to users.id)
* `name` (VARCHAR)
* `storagePath` (VARCHAR, path to .db file if server-side, or metadata for client-side)
* `createdAt` (TIMESTAMP, default: now())
* `updatedAt` (TIMESTAMP, default: now())
*Note: If using purely client-side SQLite WASM, the backend DB only needs `users`, `accounts`, `sessions`, `verification_tokens` tables. The SQLite files themselves are managed client-side.*
CORE FEATURES & USER FLOW:
1. **User Authentication (Sign Up/Login):**
* Flow: User lands on the homepage. Clicks 'Sign Up' or 'Login'. Presented with options (Google, GitHub, Email/Password). User completes authentication. Redirected to their dashboard.
* Implementation: Leverage NextAuth.js with chosen providers.
2. **Project Management & SQLite File Upload:**
* Flow: Authenticated user navigates to 'Dashboard' or 'Projects'. Clicks 'New Project'. Enters a project name. Uploads an existing `.db` SQLite file (client-side via File API and SQLite WASM OR server-side upload to cloud storage linked to project metadata).
* Visuals: File upload component with progress indicator. List of existing projects.
* If Client-Side (WASM):
* User selects `.db` file.
* App initializes SQLite WASM with the file.
* User can interact with the DB directly in the browser.
* If Server-Side:
* File uploads to a secure bucket (e.g., S3).
* `projects` table entry created with storage path.
* App establishes connection to the file (or a proxy service) for querying.
3. **Visual JSON Query Builder:**
* Flow: User selects a project, navigates to the 'JSON Tools' section. Selects a table containing JSON data. The builder inspects the schema or allows manual input of JSON paths. User clicks elements to build a path (e.g., `$.user.id`). Selects SQL function (`json_extract`). App generates the corresponding SQL query (`json_extract(payload, '$.user.id')`). User can optionally add `WHERE` clauses using JSON expressions. User executes the query.
* Visuals: Tree-like or visual path builder for JSON. Dropdowns for SQL functions. SQL query preview.
* Turkish Translations: "JSON Sorgu Oluşturucu", "Tablo Seçin", "JSON Yolu Oluştur", "Fonksiyon Seçin", "Sorguyu Çalıştır", "Sonuçlar"
4. **Visual FTS5 Search Builder:**
* Flow: User selects a project, navigates to 'FTS5 Search'. Selects a virtual FTS5 table. Enters search terms. Provides options for operators (`MATCH`, `NEAR`, ranking, phrase search). User can specify columns to search. App generates the FTS5 SQL query (e.g., `SELECT ... FROM docs WHERE docs MATCH '...'`). User executes the query.
* Visuals: Input field for search terms. Checkboxes/dropdowns for search options (columns, operators). Snippet highlighting in results.
* Turkish Translations: "FTS5 Arama Aracı", "Arama Terimleri Girin", "Sütunları Seçin", "Arama Yap", "Arama Sonuçları"
5. **Analytical Query Interface (CTE/Window Functions):**
* Flow: User selects a project, navigates to 'Analytics'. Provides a text area for writing SQL queries involving CTEs and window functions. May include syntax highlighting and basic validation. User executes the query.
* Visuals: Large, monospaced text editor for SQL. Buttons for execution and analysis.
* Turkish Translations: "Analitik Sorgular", "SQL Sorgunuzu Yazın", "Sorguyu Çalıştır"
6. **Query Results Display & Export:**
* Flow: After any query execution, results are displayed in a clean, sortable, filterable data table component. Options to export results as CSV or JSON are available.
* Visuals: `shadcn/ui` DataTable component. Export buttons.
* Turkish Translations: "Sorgu Sonuçları", "Dışa Aktar (CSV)", "Dışa Aktar (JSON)"
API & DATA FETCHING:
- **Client-Side (WASM):** Direct interaction with SQLite WASM instance. No explicit API calls for DB operations. State management handles updates.
- **Server-Side (if applicable):**
- `POST /api/projects`: Create a new project (upload DB file).
- `GET /api/projects`: Get list of user's projects.
- `POST /api/query`: Execute SQL query against a specified project's DB. Request body: `{ projectId: string, query: string }`. Response: `{ success: boolean, data?: any[], error?: string }`.
- `GET /api/tables?projectId=...`: Get list of tables for a project.
- `GET /api/tables/schema?projectId=...&tableName=...`: Get schema for a specific table.
- **Data Fetching:** Use server actions or API routes in Next.js App Router. Components will fetch data based on project context. Client-side state management will handle UI updates.
COMPONENT BREAKDOWN:
- **Pages (`app/` directory):**
- `(auth)/login/page.tsx`: Login page.
- `(auth)/signup/page.tsx`: Sign up page.
- `dashboard/page.tsx`: Main dashboard, list of projects, call to action for new project.
- `dashboard/[projectId]/page.tsx`: Project overview page.
- `dashboard/[projectId]/json/page.tsx`: JSON Query Builder interface.
- `dashboard/[projectId]/fts5/page.tsx`: FTS5 Search Builder interface.
- `dashboard/[projectId]/analytics/page.tsx`: Analytics (CTE/Window Functions) interface.
- `settings/page.tsx`: User settings page.
- `layout.tsx`: Root layout with navigation.
- `loading.tsx`, `error.tsx`: Global loading and error handling.
- **UI Components ( `components/ui/` and `components/`):
- `AuthButton.tsx`: Client component for login/logout.
- `ProjectCard.tsx`: Displays project info in a list.
- `FileUpload.tsx`: Handles SQLite file uploads.
- `JsonPathBuilder.tsx`: Visual component for building JSON paths.
- `QueryExecutor.tsx`: Component to display SQL query editor and results table.
- `Fts5Builder.tsx`: UI for constructing FTS5 queries.
- `DataTable.tsx`: Reusable shadcn/ui DataTable for displaying query results.
- `CodeEditor.tsx`: Wrapper around a library like Monaco Editor or Prism for SQL syntax highlighting.
- `NavigationMenu.tsx`: Main navigation component.
- `AlertDialog.tsx`, `Dialog.tsx`, `DropdownMenu.tsx`, `Select.tsx`, `Input.tsx`, `Button.tsx`, `Card.tsx`, `Table.tsx`: Reusable shadcn/ui components.
- **State Management:** Use Context API or Zustand for managing auth state, current project context, and potentially global UI states. Local component state for form inputs, UI toggles etc.
UI/UX DESIGN & VISUAL IDENTITY:
- **Style:** Clean, Modern, Developer-focused.
- **Color Palette:**
- Primary: `#0A84FF` (Vibrant Blue)
- Secondary: `#5AC8FA` (Light Blue)
- Accent: `#FF9500` (Orange/Amber)
- Background: `#1c1c1e` (Dark Gray/Almost Black - suitable for code editors and dark mode)
- Surface: `#2c2c2e` (Slightly Lighter Dark Gray)
- Text Primary: `#FFFFFF` (White)
- Text Secondary: `#AEAEB2` (Light Gray)
- Error: `#FF3B30` (Red)
- **Typography:** Inter (or a similar clean sans-serif like SF Pro Display).
- Headings: Bold, larger sizes (e.g., 36px, 28px).
- Body Text: Regular, readable size (e.g., 16px).
- Code Font: SF Mono or similar monospaced font.
- **Layout:** Sidebar navigation for projects and tools. Main content area for builders and results. Clear separation of concerns. Responsive design adhering to mobile-first principles, but primarily targeting desktop/tablet usage for development tools.
- **Animations:** Subtle fade-ins for content loading, smooth transitions between sections, hover effects on buttons and cards, loading spinners/skeletons.
ANIMATIONS:
- **Page Transitions:** Use `AnimatePresence` from `framer-motion` for smooth entrance/exit animations of pages and major components.
- **Component Interactions:** Subtle `scale` and `shadow` effects on button hovers. Gentle `fadeIn` on loading states.
- **Data Loading:** Use skeleton loaders (`react-placeholder` or custom shadcn/ui based) while fetching data for tables or dashboards.
- **Drag and Drop (if applicable for JSON builder):** Smooth visual feedback during drag operations.
SAMPLE/MOCK DATA:
1. **'events' table JSON data:**
```json
[
{"id": 1, "payload": "{\"user\": {\"id\": 101, \"name\": \"Alice\"}, \"action\": \"login\", \"timestamp\": \"2023-10-27T10:00:00Z\"}"},
{"id": 2, "payload": "{\"user\": {\"id\": 102, \"name\": \"Bob\"}, \"action\": \"click\", \"target\": \"button#submit\", \"timestamp\": \"2023-10-27T10:05:00Z\"}"},
{"id": 3, "payload": "{\"user\": {\"id\": 101, \"name\": \"Alice\"}, \"action\": \"logout\", \"timestamp\": \"2023-10-27T11:00:00Z\"}"}
]
```
2. **'docs' table FTS5 data:**
```sql
-- Assuming a virtual FTS5 table named 'docs' with columns 'title' and 'body'
INSERT INTO docs (rowid, title, body)
VALUES
(1, 'SQLite JSON Guide', 'Learn how to efficiently store and query JSON data directly within SQLite using its built-in JSON extension.'),
(2, 'Local-first Development', 'Explore the benefits of local-first applications and how SQLite facilitates data management.'),
(3, 'Advanced SQLite Search', 'Leverage FTS5 for powerful full-text search capabilities without external services.'),
(4, 'Data Analysis with SQL', 'Utilize CTEs and Window Functions in SQLite for complex data analysis.');
```
3. **Sample User Data:**
```json
{
"id": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
"name": "Example User",
"email": "user@example.com",
"image": "https://example.com/avatar.png"
}
```
4. **Sample Project Data:**
```json
{
"id": "f0e9d8c7-b6a5-4321-fedc-ba9876543210",
"userId": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
"name": "My Local App DB",
"storagePath": "/path/to/my_local_app.db" OR "s3://my-bucket/user-uploads/uuid.db",
"createdAt": "2023-10-26T14:30:00Z"
}
```
5. **Sample Analytics Data (Sales Table):**
```json
[
{"product_id": "P101", "sale_date": "2023-01-15", "amount": 100.50, "region": "North"},
{"product_id": "P102", "sale_date": "2023-01-16", "amount": 75.00, "region": "South"},
{"product_id": "P101", "sale_date": "2023-02-10", "amount": 120.00, "region": "North"},
{"product_id": "P103", "sale_date": "2023-02-12", "amount": 200.00, "region": "West"},
{"product_id": "P102", "sale_date": "2023-03-05", "amount": 80.00, "region": "South"}
]
```
EDGE CASES:
- **Empty States:** Design friendly UI states for when there are no projects, no tables in a DB, or no query results.
- **Large Files:** Implement safeguards or clear messaging for very large SQLite files when using client-side WASM (performance degradation, browser limits). Suggest server-side processing or chunking if needed.
- **Permissions/Auth:** Ensure queries only run against projects associated with the logged-in user. Handle unauthorized access attempts gracefully.
- **Invalid SQL/Schema:** Robust error handling and user feedback for invalid SQL syntax, non-existent tables/columns, or incorrect JSON paths. Provide helpful error messages.
- **Data Validation:** Implement client-side validation for form inputs (project names, search terms) and potentially basic server-side validation if applicable.
- **Database File Corruption:** Gracefully handle potential SQLite database file corruption, providing informative messages to the user.
- **Concurrent Access:** If server-side processing is used, consider potential issues with concurrent reads/writes to the same database file (though SQLite is generally robust for reads).
This detailed prompt will guide the AI to generate a multi-page, authenticated, CRUD-capable Next.js MVP application with a focus on the advanced SQLite features, adhering to modern best practices.