PROJECT OVERVIEW:
You are tasked with creating a single-page, responsive web application called 'Syntaqlite' using React and Tailwind CSS. Syntaqlite aims to revolutionize the developer experience for working with SQLite databases. The core problem it addresses is the lack of modern, intuitive, and AI-powered developer tools for SQLite, a widely used and critical database technology. Syntaqlite will provide an integrated environment featuring an intelligent query editor, AI-driven query optimization suggestions, database schema visualization, data browsing/editing capabilities, and project management features. The primary value proposition is to significantly boost developer productivity, reduce development time, improve query performance, and make working with SQLite more efficient and enjoyable.
TECH STACK:
- Frontend Framework: React (using Vite for fast development server and build)
- Styling: Tailwind CSS for rapid UI development and utility-first styling.
- State Management: Zustand for simple and efficient global state management.
- Routing: React Router DOM for client-side routing (though for a SPA, initial setup might be minimal).
- UI Components: Custom components built with Tailwind CSS, potentially leveraging Headless UI for accessibility and advanced interactions.
- Editor Component: Monaco Editor (the core of VS Code) or CodeMirror for the SQL query editor, configured with SQLite syntax highlighting and features.
- Icons: Heroicons or similar for clean SVG icons.
- Data Fetching/API: Mocking API calls initially using local state or localStorage, with a clear path for future integration with a backend API.
CORE FEATURES:
1. **Intelligent SQLite Query Editor:**
* **User Flow:** Users navigate to the 'Editor' tab. A file explorer-like sidebar allows selecting/creating `.sqlite` files (or connecting to existing ones via a future backend). The main area displays the Monaco/CodeMirror editor. As the user types SQL queries, the editor provides real-time syntax highlighting for SQLite, intelligent code completion for SQL keywords, table names, and column names. It also performs on-the-fly linting, highlighting syntax errors or potential issues. A 'Format SQL' button tidies up the query.
* **Details:** Integration with Monaco/CodeMirror. Configuration for SQLite dialect. Real-time error detection. Auto-completion suggestions based on loaded schema.
2. **AI-Powered Query Optimization Suggestions:**
* **User Flow:** After writing a query in the editor, the user clicks an 'Analyze Query' button (or it's triggered automatically on blur/save). The AI service (simulated via a prompt in this MVP) analyzes the query's structure, checks against the schema, and suggests potential optimizations (e.g., adding indexes, rewriting JOINs, avoiding `SELECT *`). Suggestions appear in a sidebar or a tooltip near the query.
* **Details:** This feature will initially be simulated. The AI prompt will analyze the provided SQL and schema. Output will be a list of text-based suggestions.
3. **Database Schema Visualization:**
* **User Flow:** Users select a database file. A dedicated 'Schema' tab loads. It displays a graph or interactive diagram showing tables as nodes and relationships (foreign keys) as edges. Users can zoom, pan, and click on tables to see their columns and indexes. Hovering over a table shows a tooltip with its schema definition.
* **Details:** Utilize a graph visualization library like React Flow or Vis.js. Data will be derived from SQLite's `sqlite_master` table and `PRAGMA foreign_key_list()`.
4. **Data Browsing and Editing Interface:**
* **User Flow:** From the 'Schema' view or a 'Data' tab, users can select a table. The data is displayed in a paginated, sortable, and filterable table format. Users can view all rows, sort by columns, filter using basic operators (e.g., `=`, `>`, `<`, `LIKE`), and potentially edit cell values directly. Saving changes triggers an `UPDATE` or `INSERT` operation (simulated).
* **Details:** Implement a robust data grid component. Handle pagination and sorting efficiently. Basic filtering UI.
5. **Project Management:**
* **User Flow:** A sidebar lists 'Projects'. Each project can contain multiple SQLite database files and associated queries/scripts. Users can create new projects, rename them, add/remove database files (simulated by managing local files or localStorage entries), and organize queries within a project. This provides context and separation for different development tasks.
* **Details:** State management for projects and databases. Use localStorage for persistence in the MVP.
UI/UX DESIGN:
- **Layout:** A three-column layout is envisioned. Left sidebar for Project/File navigation. Main content area for the active Editor, Schema Visualizer, or Data Grid. Right sidebar for AI suggestions, database info, or tool-specific panels.
- **Color Palette:** A dark theme primary for developer focus. Dark grey backgrounds (`#1a202c`), slightly lighter grey for panels (`#2d3748`), vibrant accent colors (e.g., a bright blue or teal `#3b82f6` or `#111827`) for interactive elements, errors (red `#e53e3e`), and highlights. White/light grey text (`#e2e8f0`).
- **Typography:** Clean, readable sans-serif font like Inter or Roboto. Clear hierarchy using font weights and sizes.
- **Responsive Design:** The layout should adapt gracefully. On smaller screens, sidebars might collapse into drawers or become hidden behind toggles. The main content area should fluidly resize. Ensure touch-friendly interactions.
- **Component Hierarchy:** A top-level `App` component. `Sidebar` component for navigation. `EditorPanel` containing `QueryEditor` (Monaco). `SchemaPanel` containing `SchemaVisualizer`. `DataPanel` containing `DataTable`. `RightSidebar` for AI suggestions/details. Each panel manages its own state or fetches from global state.
COMPONENT BREAKDOWN:
- **`App.js`:** Main container, sets up layout, routing (if needed), global state providers.
- **`Sidebar.js`:** Manages project/file list. Handles creating new projects/files. Receives `onSelect` prop.
- **`ProjectList.js` / `FileList.js`:** Renders lists of projects or files within the sidebar.
- **`EditorPanel.js`:** Container for the query editor. Manages editor instance, loads schema for intellisense.
- **`QueryEditor.js`:** Wraps Monaco Editor. Receives `value`, `onChange`, `language`, `options` props. Handles editor setup.
- **`SchemaPanel.js`:** Container for schema visualization. Fetches schema data.
- **`SchemaVisualizer.js`:** Renders the graph using a library like React Flow. Receives `nodes`, `edges` props.
- **`DataPanel.js`:** Container for the data table. Manages fetching, pagination, sorting, filtering state.
- **`DataTable.js`:** Renders the browsable/editable table. Receives `data`, `columns`, `onSort`, `onFilter` props.
- **`RightSidebar.js`:** Displays AI suggestions, query analysis, or other contextual information. Receives `suggestions` prop.
- **`Button.js`, `Input.js`, `Modal.js`:** Reusable UI components styled with Tailwind.
DATA MODEL:
- **Global State (Zustand):**
- `projects`: Array of project objects. Each project has `id`, `name`, `files: [{ id, name, type: 'db' | 'query', content: '...' }]`.
- `activeProject`: ID of the currently selected project.
- `activeFile`: ID of the currently selected file (db or query).
- `schema`: Object representing the current database schema { tables: { name: { columns: [...], indexes: [...] } }, foreignKeys: [...] }.
- `queryResults`: Data returned from executing a query.
- `aiSuggestions`: Array of optimization suggestions.
- **Mock Data Format (SQLite `sqlite_master` & `PRAGMA`):**
- `schema = {
tables: {
'users': {
columns: [{ name: 'id', type: 'INTEGER', pk: 1 }, { name: 'name', type: 'TEXT', notnull: 0 }],
indexes: [{ name: 'PRIMARY KEY', unique: 1, columns: ['id'] }]
},
'posts': {
columns: [{ name: 'id', type: 'INTEGER', pk: 1 }, { name: 'user_id', type: 'INTEGER' }, { name: 'title', type: 'TEXT' }],
indexes: [{ name: 'idx_user_id', unique: 0, columns: ['user_id'] }]
}
},
foreignKeys: [
{ fromTable: 'posts', fromCol: 'user_id', toTable: 'users', toCol: 'id' }
]
}`
- **Mock Data Format (Query Results):**
- `queryResults = {
columns: ['id', 'name'],
rows: [
[1, 'Alice'],
[2, 'Bob']
]
}`
ANIMATIONS & INTERACTIONS:
- **Hover Effects:** Subtle background color changes on sidebar items, buttons, and table rows.
- **Transitions:** Smooth transitions for sidebar opening/closing, panel content fading in/out.
- **Loading States:** Display spinners or skeleton loaders while fetching schema, running queries, or generating AI suggestions. Use placeholder elements that mimic the final content.
- **Micro-interactions:** Visual feedback when clicking buttons, saving files, or applying filters. Subtle animations on graph nodes when data updates.
EDGE CASES:
- **Empty State:** Display helpful messages and guides when no project is selected, no file is open, or query results are empty.
- **Error Handling:** Gracefully handle errors from the editor (syntax errors), simulated AI service (e.g., invalid input), and data operations. Display user-friendly error messages.
- **Validation:** Validate user input for project/file names. Ensure SQL queries are syntactically valid before attempting analysis.
- **Accessibility (a11y):** Use semantic HTML, ARIA attributes where necessary, ensure keyboard navigability for all interactive elements, provide sufficient color contrast.
- **Large Datasets:** For the data grid, implement efficient pagination and virtual scrolling to handle large numbers of rows without performance degradation.
SAMPLE DATA:
1. **Mock Project Structure:**
```json
{
"projects": [
{
"id": "proj_1",
"name": "User Analytics",
"files": [
{"id": "db_1", "name": "analytics.sqlite", "type": "db", "content": null},
{"id": "q_1", "name": "get_active_users.sql", "type": "query", "content": "SELECT id, name FROM users WHERE last_login > date('now', '-30 days');"},
{"id": "q_2", "name": "post_counts.sql", "type": "query", "content": "SELECT user_id, COUNT(*) as post_count FROM posts GROUP BY user_id ORDER BY post_count DESC;"}
]
}
],
"activeProject": "proj_1",
"activeFile": "q_1"
}
```
2. **Mock Schema for `analytics.sqlite`:**
```json
// As described in DATA MODEL section
```
3. **Mock Query Results for `get_active_users.sql`:**
```json
{
"columns": ["id", "name"],
"rows": [
[101, "Charlie"],
[105, "David"]
]
}
```
4. **Mock Query Results for `post_counts.sql`:**
```json
{
"columns": ["user_id", "post_count"],
"rows": [
[101, 5],
[105, 12],
[203, 2]
]
}
```
5. **Mock AI Suggestion:**
```json
{
"query": "SELECT * FROM users;",
"suggestions": [
"Consider selecting specific columns instead of using SELECT * for better performance.",
"Ensure an index exists on columns frequently used in WHERE clauses if performance is critical."
]
}
```
6. **Mock Table Data (`users`):**
```json
// Rendered in DataTable component
```
7. **Mock Table Data (`posts`):**
```json
// Rendered in DataTable component
```
8. **Mock Schema Visualization Data:**
```json
// Derived from PRAGMA commands, structured for React Flow
```
9. **Empty Query Editor State:**
```javascript
{ value: '', error: null }
```
10. **Empty Data Table State:**
```javascript
{ data: [], loading: false, error: null }
```
DEPLOYMENT NOTES:
- **Build:** Use Vite (`npm run build`) for optimized production builds.
- **Environment Variables:** Configure variables like `NODE_ENV` (development/production). Future API endpoints, AI service keys would also be managed here (e.g., `VITE_API_ENDPOINT`).
- **Performance Optimizations:** Code splitting (though less critical for a true SPA), lazy loading components, memoization (React.memo, useMemo), efficient state updates. Optimize image/asset loading. Ensure efficient rendering of the data grid and schema visualizer.
- **Static Hosting:** The build output is a set of static files suitable for deployment on Netlify, Vercel, GitHub Pages, or any static web server.