A backend service that mimics a ticket-based collaboration workflow similar to systems used in tools like Slack, Linear, or Jira.
Built with Node.js, Express, and PostgreSQL, focusing on correctness, security, and real-world backend tradeoffs rather than just CRUD APIs.
- JWT-based stateless authentication
- Role-based access control (RBAC) scoped to workspaces
- Authorization enforced via reusable middleware
- Proper HTTP semantics (
401,403,409, etc.)
- Users can belong to multiple workspaces
- Projects scoped to workspaces
- Permissions evaluated per workspace, not globally
- Create, assign, update, and soft-delete tickets
- Denormalized current ticket status for fast reads
- Append-only ticket status history for auditability
- Business-rule enforced state transitions (
open → in_progress → resolved → closed)
- Explicit database transactions
- Row-level locking (
SELECT … FOR UPDATE) for status transitions and deletes - Guarantees consistency under concurrent requests
- Clear tradeoff: pessimistic locking for correctness over throughput
- Append-only audit logs for destructive or significant actions
- Clear separation between:
- State history (ticket lifecycle)
- Audit logs (user actions)
- Cursor-based pagination using indexed timestamps
- Efficient filtering by status and assignee
- Partial indexes for active (non-deleted) tickets
- Parameterized SQL queries (SQL injection safe)
- Input validation using schema-based validation
- Rate limiting (global + stricter auth limits)
- Secure HTTP headers via Helmet
- Controlled CORS configuration
- Runtime: Node.js
- Framework: Express.js
- Database: PostgreSQL
- Auth: JWT (jsonwebtoken)
- Security: bcrypt, express-rate-limit, helmet
- Validation: Zod
- SQL: Raw SQL (no ORM)
Client
↓
Express App
├─ Validation Middleware
├─ Rate Limiting
├─ Auth Middleware (JWT)
├─Authorization Middleware (RBAC)
├─ Controllers
│ ├─ Transactions
│ ├─Row-level locks
│ └─ Business rules
↓
PostgreSQL
Key design principles:
- Explicit transactions
- Clear separation of concerns
- Database as source of truth for authorization
- Application-level enforcement of business rules
Chosen to prioritize correctness and consistency in ticket state transitions.
- Lower write throughput
- Strong guarantees under concurrency
- Faster reads
- Requires transactional consistency with history table
- Chosen intentionally for real-world performance patterns
- Full control over queries
- Clear understanding of execution plans and indexes
- Better learning outcome for database internals
- Move audit logging to async workers
- Add optimistic locking for low-contention paths
- Introduce read replicas for scaling reads
- Add comment APIs & notifications
- Dockerize and deploy with horizontal scaling
- Designing APIs beyond CRUD
- Handling concurrency correctly
- Cursor-based pagination at scale
- Real-world authorization pitfalls
- Tradeoffs between consistency and throughput