The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Building a Task Management Tool

Next ►
►

The fourth and last project does not face the public: it faces inward. It is the Escena Viva team's internal tool, where everything that happens before the curtain goes up gets coordinated. "Sound check at Auditorio Ribera", "print the Festival tickets", "confirm the quartet's rider", "set up the bleachers at Sala Bóveda": real tasks, with an assignee, a due date and dependencies between them.

The genuinely new idea in this project is collaboration. Until now every user owned their own things: their tickets, their order, their articles. Here several people work on the same objects at the same time, and that breaks three things at once: authorization stops being answerable from the global role, two simultaneous edits can overwrite each other, and you need an account of "what happened" that nobody can alter.

Contents

  1. The business requirement and design decisions
  2. The data model
  3. Membership-based authorization: beyond RBAC
  4. Manual ordering without renumbering the list
  5. Concurrent editing: optimistic locking and 412
  6. The technical challenge: activity and notifications
  7. A live board with Socket.IO
  8. Due dates, filters and reports
  9. Exporting to CSV with streams
  10. Testing the permission policy, and what stays out of scope

  1. The business requirement and design decisions

  • Work is organized into spaces: one per venue (Teatro Almendra, Sala Bóveda, Auditorio Ribera) and one per large event (evt-003).
  • Each space has members with a role of their own: viewer, collaborator or manager.
  • Tasks have a status, a priority, a due date, an assignee, tags, subtasks and dependencies.
  • The order inside a column is decided by a person dragging things around, and that order is shared.
  • Every change is recorded in a per-space activity history.
  • Members receive grouped notifications: nobody wants 40 emails for one afternoon of reshuffling.
  • The board updates live for whoever has it open.
Decision Rejected alternative Why
PostgreSQL with Sequelize (M7) MongoDB Dense relationships (space-member-task-dependency), combined filters and reports with SQL aggregation. And membership is best checked with a JOIN
Permission by space membership The global role from M8 alone An organizer at Teatro Almendra must not see the Auditorio Ribera board; the global role does not know that
The check pushed into the query Loading the task and deciding afterwards Filtering in the query avoids the insecure direct object reference and one extra query
Fractional positions Contiguous renumbered integers Moving one task must not rewrite 300 rows
Optimistic locking with version and If-Match Pessimistic locking when the task is opened Conflicts are rare; locking on open punishes the common case and leaves orphaned locks
Immutable activity as the source of truth Reconstructing history from the updated fields An updated_at does not tell you what changed or who changed it
Grouped, deferred notifications One email per event Ten changes in one minute are one email, not ten

New dependencies: none required. sequelize, pg, bullmq, ioredis, zod, socket.io (from 12-01) and pino are already there. Only if you go with LexoRank instead of fractional positions is a lexical range library worth adding.

  1. The data model

Table Key fields
spaces id, name, type (venue|event), reference (teatro-almendra, evt-003)
members spaceId, userId, role, joinedAt — composite primary key
tasks id, spaceId, title, description, status, priority, dueAt, assignedTo, tags, parentTaskId, position, version, createdBy
dependencies taskId, dependsOnId — composite key, no cycles (checked in the domain)
activity id, spaceId, taskId, actorId, type, data (JSONB), occurredAt — INSERT only

Statuses: pending, in_progress, blocked, done. Moving to in_progress requires every dependency to be done; otherwise the task is blocked. That rule lives in the domain:

// src/domain/task.js
const canStart = (dependencies) => dependencies.every((d) => d.status === 'done');
function changeStatus(task, newStatus, dependencies) {
  if (newStatus === 'in_progress' && !canStart(dependencies)) throw new StateConflict(
    'PENDING_DEPENDENCIES',
    { pending: dependencies.filter((d) => d.status !== 'done').map((d) => d.id) });
  return { ...task, status: newStatus, version: task.version + 1 };
}
module.exports = { canStart, changeStatus };

  1. Membership-based authorization: beyond RBAC

In M8 you built requireRole('organizer') and a pure policy in src/authorization/policy.js that answered questions like "can this user create events?". Here the question is a different one: "can this user move this task in this space?". The global role does not hold the answer because it depends on a relationship that lives in the database.

The solution is not to push queries inside the policy — it would stop being pure and testable without a database — but to load the context first and decide afterwards.

// src/authorization/space-policy.js — still PURE: it receives what it needs
const VIEW = ['space:view', 'task:view', 'comment:view'];
const EDIT = [...VIEW, 'task:create', 'task:edit', 'task:move', 'comment:create'];
const PERMISSIONS_BY_ROLE = { viewer: VIEW, collaborator: EDIT, manager: [...EDIT,
  'task:delete', 'member:invite', 'member:remove', 'space:archive'] };

function can({ actor, membership, action, resource = null }) {
  // The global administrator keeps their master key (M8), audited separately.
  if (actor.role === 'administrator') return true;
  if (!membership) return false;
  if (!(PERMISSIONS_BY_ROLE[membership.role] ?? []).includes(action)) return false;
  // Extra per-resource rule: a collaborator only deletes their own comments.
  if (action === 'comment:delete' && membership.role === 'collaborator') {
    return resource?.authorId === actor.id;
  }
  return true;
}

module.exports = { can, PERMISSIONS_BY_ROLE };

One middleware loads the membership once per request and leaves it on req.membership without blocking anything — the policy decides, not the middleware. Another one, requirePermission(action), calls can(...) and, if the answer is no, picks the error: ACCESS_DENIED (403) if the user is a member, and SPACE_NOT_FOUND (404) if they are not. That nuance matters: a 403 confirms that the space exists, and that already leaks information about the organization.

And the important part: the check is pushed into the query. In M8 you saw the insecure direct object reference: asking for /tasks/tsk-8814 and getting it because nobody checked it was yours. With spaces, the temptation is to load the task, look at its spaceId and compare. It works, but it is fragile — you have to remember in every controller — and it costs two round trips. Better: membership is part of the WHERE.

// src/repositories/tasks-sql.js
async function getVisibleTask({ taskId, userId }) {
  const [task] = await sequelize.query(
    `SELECT t.* FROM tasks t JOIN members m ON m.space_id = t.space_id
      WHERE t.id = :taskId AND m.user_id = :userId`,
    { replacements: { taskId, userId }, type: SELECT });
  return task ? toDomain(task) : null;   // null = does not exist OR cannot be seen
}

A repository that has no way of returning somebody else's task is far safer than one that returns it and trusts that somebody checks afterwards: security that depends on remembering something fails sooner or later. Listings follow the same rule, with WHERE t.space_id IN (SELECT space_id FROM members WHERE user_id = :userId).

  1. Manual ordering without renumbering the list

Marc drags "sound check at Auditorio Ribera" from the bottom of the column into second place. With contiguous integer positions (1, 2, 3…), inserting at 2 forces you to add one to every position after it: 300 rows updated per drag, and guaranteed conflicts if two people drag at once.

Fractional positions. The position is a real number and the task is placed at the midpoint between its neighbors:

// src/domain/ordering.js — inserting touches ONE row
const INITIAL_STEP = 65536;
function calculatePosition({ previous, next }) {
  if (!previous && !next) return INITIAL_STEP;
  if (!previous) return next.position / 2;              // at the top
  if (!next) return previous.position + INITIAL_STEP;   // at the bottom
  return (previous.position + next.position) / 2;       // in between
}
module.exports = { calculatePosition, INITIAL_STEP };

The known problem with fractions is precision: a double has 53 bits of mantissa, so after roughly 50 insertions into the same gap two positions become indistinguishable. It is rare but real, and it is solved by detecting it and recompacting that list — an expensive and very infrequent operation:

async function moveTask({ taskId, previousId, nextId, spaceId }) {
  const [previous, next] = await taskRepository.getNeighbors(previousId, nextId);
  if (previous && next && Math.abs(next.position - previous.position) < 1e-9) {
    // The gap between neighbors ran out: renumber the column and retry.
    await taskRepository.recompactColumn({ spaceId, status: previous.status });
    return moveTask({ taskId, previousId, nextId, spaceId });
  }
  return taskRepository.updatePosition({ taskId,
    position: calculatePosition({ previous, next }) });
}

The alternative: LexoRank. Instead of numbers, lexicographically sortable strings (0|hzzzzz, 0|i00000…). Between "a" and "b" there is always room for "an", and between "an" and "b" there is room for "anv": the space never runs out, only the string grows. It is what Jira uses; in exchange, generation is more elaborate and ordering depends on the database collation.

Criterion Fractions LexoRank
Simplicity Four lines A library or your own algorithm
Limit double precision (~50 insertions at the same point) None in practice; the string grows
Index NUMERIC/DOUBLE, natural ordering TEXT with a mandatory binary collation

For Escena Viva, with columns of a few dozen tasks, fractions are the right call: simpler, sufficient, and with a clear exit if they ever stop being enough. It is a small and very instructive decision because it shows the complete pattern: choose the simple thing, know its limit, have plan B ready.

  1. Concurrent editing: optimistic locking and 412

Lucía and Marc open the same task. Lucía changes the priority to high; Marc, two seconds later, changes the assignee and saves with the data he loaded before Lucía's change. If we accept his full PUT, the priority drops back to medium and nobody notices: this is the lost update, the quietest bug in collaborative applications.

The answer is the optimistic locking from M10 — If-Match and 412 — applied to the version field:

// src/controllers/tasks.js — optimistic locking with If-Match (M10)
async function updateTask(req, res, next) {
  const expectedVersion = req.get('If-Match');
  // Demanding the precondition prevents the client "forgetting" and reintroducing the bug.
  if (!expectedVersion) return next(new ApplicationError('PRECONDITION_REQUIRED', 428));
  const updated = await taskRepository.updateIfVersion({
    taskId: req.params.taskId, actorId: req.user.id,
    version: Number(expectedVersion.replaceAll('"', '')),
    changes: req.validatedData });               // zod from M6
  if (!updated) {
    // We return the current state: the client can show the difference without
    // a second request and without losing what the user had typed.
    const current = await taskRepository.getVisibleTask(
      { taskId: req.params.taskId, userId: req.user.id });
    return res.status(412).json({ error: { code: 'VERSION_CONFLICT', status: 412,
      message: 'Someone else modified the task while you were editing it',
      details: { currentVersion: current.version, currentTask: current } } });
  }
  res.set('ETag', `"${updated.version}"`);
  return res.json(updated);
}

In the repository, the condition travels inside the UPDATE: UPDATE tasks SET … version = version + 1 WHERE id = :taskId AND version = :version RETURNING *. If somebody got there first, the filter does not match, no row is updated and RETURNING gives back the empty set we translate into null. It is atomic and needs no explicit transaction: the same idea as the conditional findOneAndUpdate from 12-01, now in SQL.

What the client should do with a 412. Not reload silently (it would lose the typing) and not force the save (we would be back where we started). The right move: show what the other person changed, keep what the user had typed and offer "keep mine" or "take theirs". And a cheap improvement: if the touched fields do not overlap (Lucía touched priority, Marc the assignee), you can merge automatically and say so. Sending a PATCH with only the modified fields, instead of a PUT with the whole task, cuts the frequency of real conflicts enormously.

  1. The technical challenge: activity and notifications

Activity is the source of truth for "what happened". It is not a decorative log: it is an insert-only table where every domain change leaves a fact with its actor, its moment and its data:

// src/services/activity.js
const TYPES = { TASK_CREATED: 'task.created', TASK_ASSIGNED: 'task.assigned',
  TASK_STATUS_CHANGED: 'task.status_changed', TASK_MOVED: 'task.moved',
  COMMENT_CREATED: 'comment.created', MEMBER_INVITED: 'member.invited' };
// It is written INSIDE the same transaction as the change: if the change is
// rolled back, so is the activity. There is never a fact without its effect.
// `data` carries the detail, e.g. { from: 'pending', to: 'in_progress' }.
const recordActivity = ({ transaction, spaceId, taskId, actorId, type, data }) =>
  activityRepository.insert({ spaceId, taskId, actorId, type, data,
    occurredAt: new Date().toISOString() }, transaction);

This table looks a lot like event sourcing, where state is rebuilt by replaying the facts. We do not go that far here: we keep the current state in tasks and activity is a parallel record. It is a deliberate compromise — the queries stay trivial — and it is worth knowing the pure version exists in case the requirement one day becomes "rebuild the board exactly as it was on Tuesday". The rule for this table: it is never updated and never deleted; if something was recorded wrongly, you record a correction. An editable history is not a history.

Notifications: the 40-email problem. Marc reorganizes the Festival board: he moves 12 tasks, reassigns 5 and comments on 3. If each fact fires an email, Lucía gets 20 emails in four minutes and creates a filter so she never sees another one: notification stops working. The solution is to group and defer with BullMQ (M10) using a quiet window — the first fact enqueues a delayed job and the following ones accumulate without enqueueing anything new.

// src/services/notifications.js
const GROUPING_WINDOW_MS = 5 * 60 * 1000;
async function enqueueNotification({ recipientId, activity, redis, notificationQueue }) {
  const preferences = await preferencesRepository.get(recipientId);
  if (!preferences.receives(activity.type)) return;   // the user asked for silence
  if (activity.actorId === recipientId) return;       // nobody notifies themselves
  const key = `pending:${recipientId}`;
  await redis.rpush(key, JSON.stringify(activity));   // every fact accumulates
  await redis.expire(key, 3600);   // safety net if the consumer goes down
  // Deterministic jobId + delay: if the job already exists, it is not duplicated.
  // The first fact opens the window; the rest join the same send.
  await notificationQueue.add('activity-digest', { recipientId },
    { delay: GROUPING_WINDOW_MS, jobId: `digest-${recipientId}` });
}

// src/queues/notification-consumer.js — a separate process (M10/M11)
new Worker('notifications', async ({ data }) => {
  const key = `pending:${data.recipientId}`;
  // Read and empty in a single atomic operation: if new facts arrive while
  // we send, they open a new window instead of getting lost.
  const raw = await redis.multi().lrange(key, 0, -1).del(key).exec();
  const facts = (raw[0][1] ?? []).map((f) => JSON.parse(f));
  if (facts.length === 0) return { sent: 0 };
  const recipient = await userRepository.get(data.recipientId);
  await emailService.send({
    to: recipient.email,                          // [email protected]
    subject: facts.length === 1 ? summarizeFact(facts[0])
      : `${facts.length} updates in your Escena Viva spaces`,
    body: digestTemplate({ facts: groupBySpace(facts) }) });
  return { sent: 1, facts: facts.length };
}, { connection, concurrency: 5 });

Per-user preferences are part of the design, not an extra: channel (email, in-app, both), which fact types matter and the grouping window (immediate, 5 minutes, daily digest). A notification system with no preferences ends up muted entirely.

  1. A live board with Socket.IO

Here the projects connect: the infrastructure from 12-01 — Socket.IO on the http.Server, authentication in the handshake, the Redis adapter for several processes — is reused as is, changing only the namespace (/board/v1) and the rooms (space:<id>). The space:subscribe handler applies the same rule as section 3: it queries findMembership, returns SPACE_NOT_FOUND if there is none and only then calls socket.join. Membership decides here too, and it is queried: the spaceId the client sends is never trusted.

And the emission is not fired by hand from the controller but from the very place that already records the activity, a single point of truth: the publishActivity service emits activity:new to the space:<id> room of /board/v1 — for whoever is looking at the board right now — and walks spaceRepository.interestedMembers(activity) calling enqueueNotification for whoever is not looking.

Watch out for one detail from M10: io lives in the web process and notifications are processed in the consumer, so the Redis adapter from 12-01 is what allows an emission made from any process to reach every socket.

  1. Due dates, filters and reports

Two scheduled BullMQ jobs handle due dates: a daily repeatable one (repeat: { pattern: '0 8 * * *', tz: 'Europe/Madrid' }) that warns about what is due tomorrow, and a per-task delayed one that reminds 24 hours before its date, with jobId: reminder-<taskId> so that rescheduling replaces instead of piling up zombie reminders. As in 12-03, the consumer revalidates before acting: if the task is already done or the date changed, the job is discarded; a delayed job can always run in a world different from the one that created it. The team's typical filter, on top of that, is a combined one — "Auditorio Ribera tasks, in progress or blocked, assigned to Marc, due this week" — and without the right index that is a sequential scan of the table.

-- The order follows the usage: space_id first because it is ALWAYS present (section 3).
CREATE INDEX idx_tasks_space_status_due ON tasks (space_id, status, due_at);
-- PARTIAL index: only what is open, which is what gets queried every day.
CREATE INDEX idx_tasks_assigned_open ON tasks (assigned_to, due_at)
  WHERE status <> 'done';
CREATE INDEX idx_tasks_tags ON tasks USING GIN (tags);

The partial index is one of those tools that go unnoticed: most tasks in a long-lived space are done, so an index that excludes them is far smaller and far faster, and it serves exactly the query that runs a hundred times a day. Always confirm it with EXPLAIN ANALYZE (M7): an index the planner does not use is nothing but write cost. Space reports are pure aggregation:

SELECT status, COUNT(*) AS total, COUNT(*) FILTER (WHERE due_at < NOW()) AS overdue,
       AVG(EXTRACT(EPOCH FROM (updated_at - created_at)) / 3600)
         FILTER (WHERE status = 'done') AS average_hours
  FROM tasks WHERE space_id = :spaceId GROUP BY status;

  1. Exporting to CSV with streams

"Export every task in the space to CSV" looks trivial until the space has 200,000 historical rows. Loading them into an array and returning one giant res.send eats hundreds of megabytes and blocks the process while it serializes. It is exactly the problem M3 solved: flow, do not accumulate.

// src/controllers/export.js
const { pipeline } = require('node:stream/promises');
const { Transform } = require('node:stream');
async function exportTasksCsv(req, res) {
  const { spaceId } = req.params;
  res.setHeader('Content-Type', 'text/csv; charset=utf-8');
  res.setHeader('Content-Disposition', `attachment; filename="tasks-${spaceId}.csv"`);
  // A stream from the database cursor: rows arrive one at a time.
  const rowStream = taskRepository.taskStream({ spaceId });
  const toCsv = new Transform({
    objectMode: true,
    construct(done) { this.push('id,title,status,priority,due_at,assigned_to\n'); done(); },
    transform(task, encoding, done) {
      this.push([task.id, escapeCsvField(task.title), task.status, task.priority,
        task.dueAt ? new Date(task.dueAt).toISOString() : '',
        task.assignedTo ?? ''].join(',') + '\n');
      done();
    } });
  try {
    // pipeline (M3) propagates errors and destroys the streams if the client aborts,
    // so the cursor is not left open holding a connection from the pool.
    await pipeline(rowStream, toCsv, res);
  } catch (error) {
    // The response has already started: we cannot send error JSON, only cut it off.
    req.logger.error({ error, spaceId }, 'CSV export failed');
    res.destroy();
  }
}
// A title with a comma, a quote or a line break breaks the CSV unless escaped.
const escapeCsvField = (value) => /[",\n\r]/.test(String(value ?? ''))
  ? `"${String(value).replaceAll('"', '""')}"` : String(value ?? '');

Memory usage stays constant — a few megabytes — whatever the volume.

  1. Testing the permission policy, and what stays out of scope

With permissions that depend on a relationship, authorization tests stop being optional. The policy is pure: it is tested in milliseconds and without a database.

describe('space policy', () => {
  const marc = { id: 'usr-marc', role: 'organizer' };
  it('a viewer cannot create tasks', () => {
    expect(can({ actor: marc, membership: { role: 'viewer' }, action: 'task:create' }))
      .to.equal(false);
  });
  it('without membership they can do nothing, even as a global organizer', () => {
    expect(can({ actor: marc, membership: null, action: 'task:view' })).to.equal(false);
  });
  it('the global administrator can', () => {
    expect(can({ actor: { id: 'usr-admin', role: 'administrator' },
      membership: null, action: 'space:archive' })).to.equal(true);
  });
});
describe('membership-based access', () => {   // integration with supertest (M9)
  it('returns 404 when asking for a task in someone else\'s space', async () => {
    const task = await createTask({ spaceId: 'esp-ribera', title: 'Sound check' });
    await request(app).get(`/api/v1/tasks/${task.id}`)
      .set('Authorization', `Bearer ${tokenFor('usr-lucia')}`).expect(404);
  });
  it('does not list tasks from spaces the user does not belong to', async () => {
    const { body } = await request(app).get('/api/v1/tasks?due=week')
      .set('Authorization', `Bearer ${tokenFor('usr-lucia')}`).expect(200);
    expect(body.tasks.every((t) => t.spaceId === 'esp-almendra')).to.equal(true);
  });
});

The one from section 5 is missing: a PATCH with If-Match: "2" on a task at version 3 must return 412. And the last of the ones written is the one that most often prevents a real leak: it does not check a specific permission, it checks that the listing does not leak. Add it for every listing endpoint you have.

Out Why Extension
Space templates ("standard venue setup") It is a layer on top of what we built Clone a space with its tasks and relative dependencies
Gantt chart and critical path Considerable computation and front-end Dependency graph + topological sort on the server
Per-space custom fields Dynamic model and validation A JSONB column + a zod schema generated per space
Attachments and external integrations Already solved in 12-03; each integration is a project Reuse the upload pipeline; signed outbound webhooks

Common Mistakes and Tips

  • Checking membership in the controller after loading the resource. It works until somebody adds an endpoint and forgets. Push the filter into the repository's query.
  • Returning 403 when the user is not a member. A 403 confirms the space exists; for private resources, 404. And do not accept a PUT without If-Match: that opens the door to the lost update, so return 428 when the precondition is missing.
  • Renumbering the whole list on a drag. Fractional positions, with recompaction as the exception. And never update or delete rows in activity: it stops being a source of truth the moment it is editable.
  • Notifying each fact separately. The user mutes the channel and you lose the only way you had to reach them.
  • Tip: include the requestId (M11) in every activity row. When somebody asks "why did this task's assignee change?", you will be able to cross-reference the fact with the pino logs of that exact request.

Exercises

  1. Cycle-free dependencies. Implement createDependency(taskId, dependsOnId) so that it rejects with a 409 any dependency that would create a cycle (A → B → C → A). Write tests for the direct and the indirect cycle.

  2. Bulk reassignment with activity. Implement POST /spaces/:id/tasks/reassign, which changes the assignee of N tasks in one transaction, records one fact per task and produces a single grouped email. Prove the recipient gets one send and not N.

  3. The "my day" view. Return the user's tasks that are due today or overdue, across all their spaces, sorted by priority and due date, in one query that uses the partial index.

Solutions

1. Cycle-free dependencies

// src/services/dependencies.js
async function createDependency({ taskId, dependsOnId, actorId }) {
  if (taskId === dependsOnId) throw new StateConflict('CIRCULAR_DEPENDENCY', { taskId });
  // Breadth-first traversal: if taskId is reachable from dependsOnId,
  // adding this edge would close the cycle.
  const visited = new Set(); const queue = [dependsOnId];
  while (queue.length > 0) {
    const current = queue.shift();
    if (current === taskId) throw new StateConflict('CIRCULAR_DEPENDENCY',
      { taskId, dependsOnId });
    if (visited.has(current)) continue;
    visited.add(current);
    queue.push(...(await taskRepository.dependenciesOf(current)).map((p) => p.dependsOnId));
  }
  return taskRepository.insertDependency({ taskId, dependsOnId, actorId });
}

The test chains B→A and C→B and checks that A→C is rejected with CIRCULAR_DEPENDENCY. With large graphs, traversing through chained queries gets expensive: the alternative is a WITH RECURSIVE that resolves reachability in a single round trip.

2. Grouped bulk reassignment

async function reassignTasks({ spaceId, taskIds, newAssigneeId, actor }) {
  const facts = await sequelize.transaction(async (t) => {
    const collected = [];
    for (const taskId of taskIds) {
      const task = await taskRepository.getForUpdate(taskId, t);
      if (task?.spaceId !== spaceId) throw new ResourceNotFound(
        'TASK_NOT_FOUND', { taskId });
      await taskRepository.updateInTransaction(
        { taskId, changes: { assignedTo: newAssigneeId } }, t);
      collected.push(await recordActivity({ transaction: t, spaceId, taskId,
        actorId: actor.id, type: TYPES.TASK_ASSIGNED,
        data: { from: task.assignedTo, to: newAssigneeId } }));
    }
    return collected;
  });
  // Outside the transaction: external effects only after the commit lands.
  for (const f of facts) await publishActivity({ activity: f, io, notificationQueue, redis });
  return facts.length;
}

The grouping comes for free: the N facts pile up in the Redis list and share the same jobId digest-<recipientId>, so BullMQ keeps one delayed job. The test spies on emailService.send, advances the clock by GROUPING_WINDOW_MS and verifies callCount === 1 with a subject containing "12 updates".

3. The "my day" view

async function myDayTasks({ userId }) {
  const [rows] = await sequelize.query(
    `SELECT t.*, s.name AS space_name
       FROM tasks t JOIN spaces s ON s.id = t.space_id
      WHERE t.assigned_to = :userId AND t.status <> 'done'
        AND t.due_at < (CURRENT_DATE + INTERVAL '1 day')
        AND EXISTS (SELECT 1 FROM members m
                     WHERE m.space_id = t.space_id AND m.user_id = :userId)
      ORDER BY CASE t.priority WHEN 'high' THEN 0 WHEN 'medium' THEN 1 ELSE 2 END, t.due_at
      LIMIT 100`, { replacements: { userId } });
  return rows.map(toDomain);
}

The EXISTS against members looks redundant — the task is already assigned to the user — but it covers the case of someone removed from the space who still has tasks assigned: without it they would keep seeing work from a space they no longer belong to. The WHERE status <> 'done' matches the partial index from section 8; confirm it with EXPLAIN ANALYZE.

Conclusion

The internal tool closes the four projects with the challenge that only appears when several people share the same objects. You have seen that Module 8's RBAC does not die, it extends: the policy is still a pure function, but it receives membership on top of the role, and — the part that really protects you — the check is pushed into the repository's WHERE, so that returning someone else's resource becomes impossible instead of merely unlikely.

You have solved the lost update with optimistic locking and a 412 response that hands the client everything it needs to reconcile; you have ordered lists without rewriting them using fractional positions, knowing their limit and their plan B; and you have built an immutable activity log that serves at once as history, as the trigger for 12-01's real time and as the origin of grouped notifications. With this, Escena Viva's four satellite products are built. The last lesson of the course adds no fifth project: it gathers the whole journey, turns what you learned into a production-readiness checklist and a framework for making technical decisions, and maps out honestly where to go once the course is over.

◄ Previous
◄
Next ►
►

Node.js Course: From Beginner to Advanced

Module 1: Introduction to Node.js

Module 2: Core Concepts

Module 3: File System and I/O

Module 4: HTTP and Web Servers

Module 5: NPM and Package Management

Module 6: The Express.js Framework

Module 7: Databases and ORMs

Module 8: Authentication and Authorization

Module 9: Testing and Debugging

Module 10: Advanced Topics

Module 11: Deployment and DevOps

Module 12: Real-World Projects

The Project | About Us | Contribute | Donations | License
© Copyright 2026. All rights reserved