How to Build a Productivity App with Replit

1// Build a personal productivity app with Express and PostgreSQL using Drizzle ORM.
2// Use Replit Auth for user authentication — every query must scope data by user_id.
3//
4// Tables:
5// 1. folders: id serial primary key, user_id text not null, name text not null,
6//    parent_id integer references folders (self-referential nesting),
7//    color text, position integer default 0
8// 2. notes: id serial primary key, user_id text not null, title text not null,
9//    content text (markdown body), folder_id integer references folders,
10//    is_pinned boolean default false, created_at timestamp default now(),
11//    updated_at timestamp default now()
12// 3. tasks: id serial primary key, user_id text not null, title text not null,
13//    description text, due_date timestamp, priority text default 'medium'
14//    (enum: low/medium/high/urgent), status text default 'todo'
15//    (enum: todo/in_progress/done), recurrence text default 'none'
16//    (enum: none/daily/weekly/monthly), completed_at timestamp, created_at timestamp default now()
17// 4. bookmarks: id serial primary key, user_id text not null, url text not null,
18//    title text, description text, tags text[] (PostgreSQL text array),
19//    favicon_url text, created_at timestamp default now()
20// 5. daily_plans: id serial primary key, user_id text not null, date date not null,
21//    task_ids jsonb (ordered array of task IDs), notes text,
22//    unique(user_id, date)
23//
24// Routes:
25// GET/POST /api/notes, PUT/DELETE /api/notes/:id
26// GET/POST /api/folders (GET returns tree structure via recursive CTE)
27// GET/POST /api/tasks, PATCH /api/tasks/:id/status, PUT /api/tasks/:id
28// GET/POST /api/bookmarks, DELETE /api/bookmarks/:id
29// GET/PUT /api/daily/:date (get or create daily plan for a date)
30//
31// React frontend: three-panel layout with folder tree sidebar,
32// center content list, right detail/editor panel.
33// Bind server to 0.0.0.0:3000.

1const express = require('express');
2const { db } = require('../db');
3const { folders } = require('../schema');
4const { eq, isNull } = require('drizzle-orm');
5
6const router = express.Router();
7
8// Get entire folder tree for the current user using recursive CTE
9router.get('/api/folders', async (req, res) => {
10  if (!req.user) return res.status(401).json({ error: 'Login required' });
11
12  // Recursive CTE: start with root folders, then recursively include children
13  const result = await db.execute({
14    sql: `
15      WITH RECURSIVE tree AS (
16        SELECT id, name, parent_id, color, position, 0 AS depth
17        FROM folders
18        WHERE user_id = $1 AND parent_id IS NULL
19        UNION ALL
20        SELECT f.id, f.name, f.parent_id, f.color, f.position, t.depth + 1
21        FROM folders f
22        JOIN tree t ON f.parent_id = t.id
23        WHERE f.user_id = $1
24      )
25      SELECT * FROM tree ORDER BY depth, position, name
26    `,
27    params: [req.user.id],
28  });
29
30  // Build nested tree structure from flat rows
31  const folderMap = {};
32  const roots = [];
33
34  for (const row of result.rows) {
35    folderMap[row.id] = { ...row, children: [] };
36  }
37  for (const row of result.rows) {
38    if (row.parent_id) {
39      folderMap[row.parent_id]?.children.push(folderMap[row.id]);
40    } else {
41      roots.push(folderMap[row.id]);
42    }
43  }
44
45  return res.json({ folders: roots });
46});
47
48router.post('/api/folders', express.json(), async (req, res) => {
49  if (!req.user) return res.status(401).json({ error: 'Login required' });
50  const { name, parentId, color } = req.body;
51  if (!name) return res.status(400).json({ error: 'name is required' });
52
53  const row = await db.insert(folders)
54    .values({ userId: req.user.id, name, parentId: parentId || null, color: color || null })
55    .returning();
56  return res.status(201).json(row[0]);
57});
58
59module.exports = router;

1const express = require('express');
2const { db } = require('../db');
3const { tasks } = require('../schema');
4const { eq, and, lte, gte, inArray } = require('drizzle-orm');
5
6const router = express.Router();
7
8router.get('/api/tasks', async (req, res) => {
9  if (!req.user) return res.status(401).json({ error: 'Login required' });
10  const { status, priority, dueBefore, dueAfter } = req.query;
11
12  const conditions = [eq(tasks.userId, req.user.id)];
13  if (status) conditions.push(eq(tasks.status, status));
14  if (priority) conditions.push(eq(tasks.priority, priority));
15  if (dueBefore) conditions.push(lte(tasks.dueDate, new Date(dueBefore)));
16  if (dueAfter) conditions.push(gte(tasks.dueDate, new Date(dueAfter)));
17
18  const rows = await db.select().from(tasks)
19    .where(and(...conditions))
20    .orderBy(tasks.dueDate, tasks.priority);
21
22  return res.json({ tasks: rows });
23});
24
25router.post('/api/tasks', express.json(), async (req, res) => {
26  if (!req.user) return res.status(401).json({ error: 'Login required' });
27  const { title, description, dueDate, priority, recurrence } = req.body;
28  if (!title) return res.status(400).json({ error: 'title is required' });
29
30  const row = await db.insert(tasks).values({
31    userId: req.user.id,
32    title,
33    description: description || null,
34    dueDate: dueDate ? new Date(dueDate) : null,
35    priority: priority || 'medium',
36    recurrence: recurrence || 'none',
37  }).returning();
38  return res.status(201).json(row[0]);
39});
40
41router.patch('/api/tasks/:id/status', express.json(), async (req, res) => {
42  if (!req.user) return res.status(401).json({ error: 'Login required' });
43  const { status } = req.body;
44  const validStatuses = ['todo', 'in_progress', 'done'];
45  if (!validStatuses.includes(status)) {
46    return res.status(400).json({ error: 'Invalid status' });
47  }
48  const updated = await db.update(tasks)
49    .set({ status, completedAt: status === 'done' ? new Date() : null, updatedAt: new Date() })
50    .where(and(eq(tasks.id, parseInt(req.params.id)), eq(tasks.userId, req.user.id)))
51    .returning();
52  if (!updated[0]) return res.status(404).json({ error: 'Not found' });
53  return res.json(updated[0]);
54});
55
56module.exports = router;

1// Ask Agent to add bookmarks and daily planner with this prompt:
2// Add to the Express app:
3//
4// 1. POST /api/bookmarks:
5//    - Require Replit Auth
6//    - Accept {url, tags} in request body
7//    - Server-side fetch the URL using node-fetch: GET the HTML, extract <title> tag
8//    - Try to find favicon at /favicon.ico on the domain
9//    - Insert into bookmarks with fetched title, favicon_url, and tags
10//    - If fetch fails (CORS, timeout), store with title=null and continue
11//    - Set a 5-second timeout on the title fetch to not block the response
12//
13// 2. GET /api/bookmarks:
14//    - Require auth
15//    - Accept optional ?tag= query param to filter by tag
16//    - For tag filter, use PostgreSQL array contains: WHERE $1 = ANY(tags)
17//    - Return bookmarks ordered by created_at desc
18//
19// 3. GET /api/daily/:date:
20//    - Require auth
21//    - Accept date in YYYY-MM-DD format
22//    - If daily_plan exists for user+date, return it with task details joined
23//    - If not, INSERT a new empty daily_plan and return it
24//    - Join task_ids array against tasks table to return full task objects in order
25//
26// 4. PUT /api/daily/:date:
27//    - Require auth
28//    - Accept {task_ids: [array of IDs], notes: string}
29//    - Upsert: INSERT ... ON CONFLICT (user_id, date) DO UPDATE
30//    - Return the updated plan

1// Ask Agent to build the React frontend with this prompt:
2// Build a React productivity app with a three-panel layout:
3//
4// Left sidebar (240px fixed):
5// - Section navigation icons at top: Notes, Tasks, Bookmarks, Today (icons with labels)
6// - Below icons: folder tree rendered from GET /api/folders
7//   Each folder is a collapsible row with indentation based on depth
8//   An 'Add folder' button creates a subfolder via POST /api/folders
9//
10// Center panel (flex-grow):
11// - Notes view: list of notes for selected folder, sorted by is_pinned desc, updated_at desc
12//   Each row shows title, first line of content, and timestamp
13//   Click row to open in right panel
14// - Tasks view: list with checkbox, title, priority dot (urgent=red, high=orange, medium=blue, low=gray),
15//   due date badge (red if overdue, orange if today). Click checkbox calls PATCH /api/tasks/:id/status
16// - Bookmarks view: list with favicon, title, domain, and tag chips
17// - Today view: date header with today's date, ordered list of today's tasks from GET /api/daily/:date
18//
19// Right panel (320px fixed):
20// - Notes: textarea with live markdown preview toggle (toggle between edit and preview modes)
21//   Auto-save on 2-second debounce via PUT /api/notes/:id
22// - Tasks: edit form with all fields
23// - Bookmarks: full URL, description, tag editor
24//
25// Use React Router. Add keyboard shortcut Cmd+N to create a new note.