Functional:
# Enhanced Authentication API
POST /auth/login
Content-Type: application/json
Authorization: Basic base64(login:password)
Request:
{
"email": "[email protected]",
"password": "••••••••",
"device_id": "mobile-12345" # For session management
}
Response: 200 OK
{
"access_token": "eyJhbGciOiJ...",
"refresh_token": "eyJhbGciOiJ...",
"token_type": "Bearer",
"expires_in": 3600,
"user": {
"employee_id": "EMP_001",
"email": "[email protected]",
"name": "John Doe",
"role": "EMPLOYEE",
"permissions": ["shifts:read", "shifts:swap"]
}
}
Response: 401 Unauthorized
{
"error": "INVALID_CREDENTIALS",
"message": "Invalid email or password",
"retry_after": 300 # Seconds before next attempt
}
POST /auth/refresh
Content-Type: application/json
Request:
{
"refresh_token": "eyJhbGciOiJ..."
}
Response: 200 OK
{
"access_token": "eyJhbGciOiJ...",
"expires_in": 3600
}
POST /auth/logout
Authorization: Bearer {token}
Response: 200 OK
{
"message": "Successfully logged out"
}
POST /users
Authorization: Bearer {token} # Requires ADMIN role
Content-Type: application/json
Request:
{
"email": "[email protected]",
"name": "Jane Smith",
"role": "EMPLOYEE",
"manager_id": "MGR_001",
"department": "Operations",
"phone": "+1234567890"
}
Response: 201 Created
{
"employee_id": "EMP_002",
"message": "User created successfully",
"temporary_password": "generated-temp-password" # Sent via email
}
GET /users/me
Authorization: Bearer {token}
Response: 200 OK
{
"employee_id": "EMP_001",
"email": "[email protected]",
"name": "John Doe",
"role": "EMPLOYEE",
"manager": {
"employee_id": "MGR_001",
"name": "Manager Name",
"email": "[email protected]"
},
"department": "Operations",
"preferences": {
"notifications": {
"email": true,
"push": true,
"sms": false
}
}
}
Get shifts with advanced filtering
GET /shifts
Authorization: Bearer {token}
Query Parameters:
start_date: 2024-01-15T00:00:00Z
end_date: 2024-01-22T23:59:59Z
status: SCHEDULED,IN_PROGRESS,COMPLETED
page: 1
page_size: 50
Response: 200 OK
{
"shifts": [
{
"shift_id": "SHIFT_001",
"employee_id": "EMP_001",
"employee_name": "John Doe",
"start_time": "2024-01-15T09:00:00Z",
"end_time": "2024-01-15T17:00:00Z",
"status": "SCHEDULED",
"location": "Main Office",
"role_requirements": ["CERTIFIED"],
"is_swappable": true,
"constraints": {
"min_skill_level": 2,
"required_certifications": ["first_aid"]
}
}
],
"pagination": {
"page": 1,
"page_size": 50,
"total_count": 125,
"total_pages": 3
},
"summary": {
"scheduled_hours": 40,
"swap_requests_pending": 2
}
}
Get shift recommendations for swapping
GET /shifts/{shift_id}/swap-recommendations
Authorization: Bearer {token}
Response: 200 OK
{
"recommendations": [
{
"shift": {
"shift_id": "SHIFT_002",
"employee_id": "EMP_002",
"employee_name": "Jane Smith",
"start_time": "2024-01-16T09:00:00Z",
"end_time": "2024-01-16T17:00:00Z",
"location": "Main Office"
},
"match_score": 0.85,
"reasons": [
"Same location",
"Similar duration",
"Compatible skills"
],
"compatibility_warnings": [
"Different department"
]
}
],
"filters_applied": {
"same_manager": true,
"time_proximity": "7 days",
"skill_match": true
}
}
Initiate swap request
POST /swap-requests
Authorization: Bearer {token}
Content-Type: application/json
Request:
{
"requested_shift_id": "SHIFT_001", # Shift I want to give away
"offered_shift_id": "SHIFT_002", # Shift I'm offering in return
"message": "I have a family event, can we swap?"
}
Response: 201 Created
{
"swap_request_id": "SWAP_REQ_001",
"status": "PENDING",
"requesting_employee": {
"employee_id": "EMP_001",
"name": "John Doe"
},
"receiving_employee": {
"employee_id": "EMP_002",
"name": "Jane Smith"
},
"requested_shift": {
"shift_id": "SHIFT_001",
"start_time": "2024-01-15T09:00:00Z",
"end_time": "2024-01-15T17:00:00Z"
},
"offered_shift": {
"shift_id": "SHIFT_002",
"start_time": "2024-01-16T09:00:00Z",
"end_time": "2024-01-16T17:00:00Z"
},
"created_at": "2024-01-10T14:30:00Z",
"expires_at": "2024-01-12T14:30:00Z", # 48 hours to respond
"next_actions": [
{
"role": "RECEIVING_EMPLOYEE",
"action": "APPROVE_OR_REJECT",
"deadline": "2024-01-12T14:30:00Z"
}
]
}
Get swap request status
GET /swap-requests/{swap_request_id}
Authorization: Bearer {token}
Response: 200 OK
{
"swap_request_id": "SWAP_REQ_001",
"status": "PENDING", # PENDING, ACCEPTED, MANAGER_REVIEW, APPROVED, REJECTED, CANCELLED, EXPIRED
"timeline": [
{
"timestamp": "2024-01-10T14:30:00Z",
"event": "REQUEST_CREATED",
"actor": "John Doe"
}
],
"participants": {
"requestor": {
"employee_id": "EMP_001",
"name": "John Doe",
"approved": true
},
"receiver": {
"employee_id": "EMP_002",
"name": "Jane Smith",
"approved": null # null = pending, true/false = decided
},
"manager": {
"employee_id": "MGR_001",
"name": "Manager Name",
"approved": null
}
},
"can_approve": false, # Whether current user can take action
"can_cancel": true # Whether current user can cancel
}
Approve/reject swap request
PUT /swap-requests/{swap_request_id}/response
Authorization: Bearer {token}
Content-Type: application/json
Request:
{
"action": "APPROVE", # or "REJECT"
"message": "Sure, I can take that shift!" # Optional
}
Response: 200 OK
{
"swap_request_id": "SWAP_REQ_001",
"status": "MANAGER_REVIEW", # Updated status
"message": "Swap request approved. Waiting for manager approval."
}
Manager approval endpoint
PUT /swap-requests/{swap_request_id}/manager-response
Authorization: Bearer {token} # Must be manager
Content-Type: application/json
Request:
{
"action": "APPROVE", # or "REJECT"
"reason": "Approved - coverage maintained" # Required for audit
}
Response: 200 OK
{
"swap_request_id": "SWAP_REQ_001",
"status": "APPROVED",
"message": "Swap request approved. Shifts have been updated.",
"updated_shifts": [
{
"shift_id": "SHIFT_001",
"previous_employee": "EMP_001",
"new_employee": "EMP_002"
},
{
"shift_id": "SHIFT_002",
"previous_employee": "EMP_002",
"new_employee": "EMP_001"
}
]
}
REST endpoints for notification management
GET /notifications
Authorization: Bearer {token}
Query Parameters:
page: 1
page_size: 20
unread_only: true
Response: 200 OK
{
"notifications": [
{
"notification_id": "NOTIF_001",
"type": "SWAP_REQUEST",
"title": "New Swap Request",
"message": "John Doe wants to swap shifts with you",
"data": {
"swap_request_id": "SWAP_REQ_001",
"action_url": "/swap-requests/SWAP_REQ_001"
},
"status": "UNREAD", # UNREAD, READ, ACTIONED
"created_at": "2024-01-10T14:30:00Z",
"expires_at": "2024-01-12T14:30:00Z"
}
],
"unread_count": 5
}
Mark notification as read
PUT /notifications/{notification_id}/read
Authorization: Bearer {token}
Response: 200 OK
{
"notification_id": "NOTIF_001",
"status": "READ"
}
Standardized error responses
{
"error": {
"code": "VALIDATION_ERROR", # INVALID_TOKEN, PERMISSION_DENIED, RESOURCE_NOT_FOUND, etc.
"message": "Invalid input parameters",
"details": [
{
"field": "start_time",
"message": "Start time must be before end time"
}
],
"trace_id": "trace-12345", # For support debugging
"timestamp": "2024-01-10T14:30:00Z"
}
}
HTTP/1.1 200 OK
X-RateLimit-Limit: 100
X-RateLimit-Remaining: 99
X-RateLimit-Reset: 1641822600
Retry-After: 60 # When rate limited
The system is built around a microservices architecture to ensure separation of concerns, independent scalability, and resilience. Communication between services is a mix of synchronous (HTTP/REST, WebSocket) and asynchronous (Kafka) patterns.
Improvements:
UserService, ShiftMatchingService, etc.).POST /register: Register new employees.POST /login: Authenticate users (e.g., with username/password). Upon success, it generates and returns a signed JWT. The JWT should contain claims like user_id, role (e.g., employee, manager), and permissions.POST /logout (Optional): Invalidate the JWT on the server-side if using a blacklist (stored in Redis).GET /shifts/{id}: Get a specific shift.GET /employees/{id}/shifts: Get an employee's shift schedule, including the last 100 swaps (as per the original requirement).SHIFT_SWAP_APPROVED events from the Shift Matching Service and permanently updates the shift ownership in the PostgreSQL database. This service is the ultimate authority on "who is working when."users table: id, name, email, role, etc.shifts table: id, employee_id (foreign key), start_time, end_time, status (SCHEDULED, IN_PROGRESS, CANCELED).shift_swaps_history table: A log of all completed swaps for audit and display purposes.This is the most complex service. Its logic is refined for clarity and robustness.
GET /swap/options: Provides a list of potential shifts an employee can swap for.SCHEDULED shifts from other employees that do not cause scheduling conflicts (e.g., overlapping shifts). The simple "Hungarian algorithm" mention is replaced with a more practical conflict-detection query in the database. Results can be cached in Redis for a short period to reduce load.POST /swap/request: Initiates a swap request.{ "my_shift_id": "123", "requested_shift_id": "456" }my_shift_id and requested_shift_id as part of the key. This prevents race conditions for the same shifts.SCHEDULED and available.PENDING.employee_456).POST /swap/{id}/approve: Allows an employee to accept a swap proposed to them.ACCEPTED_BY_EMPLOYEE.POST /swap/{id}/manager-approve: Manager gives final approval.APPROVED.SHIFT_SWAP_APPROVED containing the details of the swap (the two shift IDs and the two employee IDs).POST /swap/{id}/cancel: Allows either involved employee or a manager to cancel a swap at any state (PENDING, ACCEPTED_BY_EMPLOYEE). Status becomes CANCELED.swap_proposals table: id, requesting_employee_id, requesting_shift_id, receiving_employee_id, receiving_shift_id, status, created_at.SWAP_REQUESTED, SWAP_ACCEPTED, SWAP_APPROVED, SWAP_CANCELED.user_id -> WebSocket session).shift_swap_requestsshift_swap_approvals (consumed by the Employee Shift Service)notification_events (consumed by the Notification Service)lock:shift:{shift_id}.This improved design is more robust, scalable, and clearly defines the data flow and responsibilities of each component.
# Kubernetes Configuration for API Gateway
apiVersion: apps/v1
kind: Deployment
metadata:
name: api-gateway
spec:
replicas: 3
template:
spec:
containers:
- name: gateway
image: nginx-plus/istio-proxy
env:
- name: SHARDING_KEY
value: "employee_id"
Improvements:
Critical Fixes:
class ShiftMatchingService:
def get_swap_recommendations(self, employee_id: str) -> List[Shift]:
"""Get personalized shift swap recommendations"""
# 1. Get employee's upcoming shifts
my_shifts = self.shift_service.get_upcoming_shifts(employee_id)
# 2. Find compatible employees (same manager, similar role, location)
compatible_employees = self.find_compatible_employees(employee_id)
# 3. Get available shifts from compatible employees
candidate_shifts = self.get_available_shifts(
compatible_employees,
exclude_conflicts_with=my_shifts
)
# 4. Score and rank shifts based on multiple factors
scored_shifts = self.score_shifts(my_shifts, candidate_shifts)
return scored_shifts[:20] # Return top 20 recommendations
def score_shifts(self, my_shifts: List, candidate_shifts: List) -> List[ScoredShift]:
"""Score shifts based on multiple factors"""
scores = []
for candidate in candidate_shifts:
score = self.calculate_match_score(my_shifts, candidate)
scores.append(ScoredShift(shift=candidate, score=score))
return sorted(scores, key=lambda x: x.score, reverse=True)
def calculate_match_score(self, my_shifts: List, candidate: Shift) -> float:
"""Calculate match score using practical business rules"""
score = 0.0
# Time proximity (prefer shifts close to original)
time_diff = abs(candidate.start_time - my_shifts[0].start_time)
score += max(0, 1 - (time_diff.total_hours() / 168)) # Normalize to 1 week
# Duration match
if candidate.duration == my_shifts[0].duration:
score += 0.3
# Skill compatibility
if self.has_required_skills(candidate.required_skills):
score += 0.2
# Location preference
if candidate.location == my_shifts[0].location:
score += 0.2
return min(1.0, score) # Cap at 1.0
State Transitions:
-- PostgreSQL table for swap proposals
CREATE TABLE shift_swaps (
id UUID PRIMARY KEY,
requesting_employee_id UUID,
receiving_employee_id UUID,
requested_shift_id UUID,
offered_shift_id UUID,
status SWAP_STATUS, -- 'PENDING', 'ACCEPTED', 'MANAGER_REVIEW', 'COMPLETED', 'CANCELLED'
created_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ,
expires_at TIMESTAMPTZ, -- Redis lock TTL
manager_id UUID
);
class ShiftSwapOrchestrator:
def init(self, redis_client, db_session):
self.redis = redis_client
self.db = db_session
def request_swap(self, request: SwapRequest) -> SwapResult:
# Create lock keys for both shifts
lock_key_1 = f"lock:shift:{request.requested_shift_id}"
lock_key_2 = f"lock:shift:{request.offered_shift_id}"
try:
# Acquire distributed locks with retry logic
acquired = self.redis.set(
lock_key_1, request.requesting_employee_id,
nx=True, ex=3600 # 1 hour TTL
)
if not acquired:
return SwapResult(error="Shift already locked")
# Create swap proposal in database (source of truth)
swap = ShiftSwap(
id=uuid.uuid4(),
requesting_employee_id=request.requesting_employee_id,
requested_shift_id=request.requested_shift_id,
offered_shift_id=request.offered_shift_id,
status='PENDING'
)
self.db.add(swap)
self.db.commit()
# Send notification
self.notification_service.notify_swap_request(swap)
return SwapResult(success=True, swap_id=swap.id)
except Exception as e:
# Release locks on failure
self.redis.delete(lock_key_1)
self.redis.delete(lock_key_2)
raise e
AWS ElastiCache Redis Cluster configuration
ClusterMode: enabled
NumNodeGroups: 3
ReplicasPerNodeGroup: 1
AutoFailover: enabled
DataTiering: false
Key sharding strategy
ShardingKey: "employee_id" # Consistent hashing for user data
Redis Data Structure:
Shift cache (TTL: 5 minutes)
shift_key = f"shift:{shift_id}"
redis.hset(shift_key, mapping={
"employee_id": "emp_123",
"start_time": "2024-01-15T09:00:00Z",
"end_time": "2024-01-15T17:00:00Z",
"status": "SCHEDULED"
})
redis.expire(shift_key, 300)
User session store
session_key = f"session:{user_id}"
redis.setex(session_key, 3600, jwt_token)
Rate limiting
rate_limit_key = f"rate_limit:{user_id}:{action}"
redis.incr(rate_limit_key)
redis.expire(rate_limit_key, 60)
class ShiftSwapClient {
private retryConfig = {
maxRetries: 3,
baseDelay: 1000,
maxDelay: 10000
};
async requestShiftSwap(swapRequest: SwapRequest): Promise<SwapResponse> {
return this.retryableRequest(
() => this.apiClient.post('/swap/request', swapRequest),
this.retryConfig
);
}
private async retryableRequest(
requestFn: () => Promise<any>,
config: RetryConfig
): Promise<any> {
let lastError: Error;
for (let attempt = 0; attempt < config.maxRetries; attempt++) {
try {
return await requestFn();
} catch (error) {
lastError = error;
if (this.isRetryableError(error)) {
const delay = Math.min(
config.baseDelay * Math.pow(2, attempt),
config.maxDelay
);
await this.sleep(delay + Math.random() * 1000); // Jitter
continue;
}
throw error;
}
}
throw lastError;
}
// WebSocket reconnection with exponential backoff
setupWebSocket() {
this.ws = new WebSocket(this.wsUrl);
this.ws.onclose = () => {
const delay = Math.min(1000 * Math.pow(2, this.reconnectAttempts), 30000);
setTimeout(() => this.setupWebSocket(), delay);
};
}
}
class NotificationService:
def init(self, redis_client, ws_manager, email_client, sms_client):
self.redis = redis_client
self.ws_manager = ws_manager
self.email_client = email_client
self.sms_client = sms_client
async def notify_swap_request(self, swap: ShiftSwap):
# 1. Immediate WebSocket notification
await self.ws_manager.send_to_user(
swap.receiving_employee_id,
{
"type": "SWAP_REQUEST",
"swap_id": str(swap.id),
"message": "You have a new shift swap request"
}
)
# 2. Fallback to email (async, retryable)
asyncio.create_task(self.send_email_notification(swap))
# 3. Optional SMS for urgent notifications
if self.is_urgent(swap):
asyncio.create_task(self.send_sms_notification(swap))
async def send_email_notification(self, swap: ShiftSwap):
"""Send email with retry logic"""
max_retries = 3
for attempt in range(max_retries):
try:
await self.email_client.send(
to=swap.receiving_employee.email,
subject="Shift Swap Request",
template="swap_request.html",
context={"swap": swap}
)
break
except SMTPException as e:
if attempt == max_retries - 1:
await self.log_failed_notification(swap, str(e))
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
metadata:
name: shift-matching-hpa
spec:
scaleTargetRef:
apiVersion: apps/v1
kind: Deployment
name: shift-matching-service
minReplicas: 3
maxReplicas: 20
metrics:
type: Resource
resource:
name: cpu
target:
type: Utilization
averageUtilization: 70
type: Pods
pods:
metric:
name: requests_per_second
target:
type: AverageValue
averageValue: 100
behavior:
scaleDown:
stabilizationWindowSeconds: 300
policies:
type: Percent
value: 50
periodSeconds: 60
class SMTPClientWithCircuitBreaker:
def init(self):
self.failure_count = 0
self.circuit_state = "CLOSED" # CLOSED, OPEN, HALF_OPEN
self.last_failure_time = None
async def send_email(self, email: Email) -> bool:
if self.circuit_state == "OPEN":
if time.time() - self.last_failure_time > 60: # 60 second timeout
self.circuit_state = "HALF_OPEN"
else:
raise CircuitBreakerOpenError("SMTP service unavailable")
try:
result = await self._actual_send(email)
self._on_success()
return result
except Exception as e:
self._on_failure()
raise e
def _on_failure(self):
self.failure_count += 1
self.last_failure_time = time.time()
if self.failure_count >= 5:
self.circuit_state = "OPEN"
def _on_success(self):
self.failure_count = 0
self.circuit_state = "CLOSED"
-- Enhanced User Schema with proper constraints and indexing
CREATE TABLE users (
employee_id VARCHAR(128) PRIMARY KEY,
email VARCHAR(256) UNIQUE NOT NULL,
first_name VARCHAR(64) NOT NULL,
last_name VARCHAR(64) NOT NULL,
role VARCHAR(32) NOT NULL CHECK (role IN ('EMPLOYEE', 'MANAGER', 'ADMIN')),
manager_id VARCHAR(128) REFERENCES users(employee_id) ON DELETE SET NULL,
department_id VARCHAR(64),
phone_number VARCHAR(32),
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
last_login_at TIMESTAMPTZ,
-- Indexes for common queries
INDEX idx_users_manager_id (manager_id),
INDEX idx_users_department (department_id),
INDEX idx_users_role (role),
INDEX idx_users_email (email)
);
-- Shift schema with proper status tracking
CREATE TABLE shifts (
shift_id VARCHAR(128) PRIMARY KEY,
employee_id VARCHAR(128) NOT NULL REFERENCES users(employee_id) ON DELETE CASCADE,
start_time TIMESTAMPTZ NOT NULL,
end_time TIMESTAMPTZ NOT NULL,
status VARCHAR(32) NOT NULL CHECK (status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED', 'SWAPPED')),
location_id VARCHAR(64),
role_requirements VARCHAR(64), -- Required role for this shift
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
-- Ensure end_time is after start_time
CONSTRAINT chk_shift_times CHECK (end_time > start_time),
-- Indexes for performance
INDEX idx_shifts_employee_id (employee_id),
INDEX idx_shifts_status (status),
INDEX idx_shifts_time_range (start_time, end_time),
INDEX idx_shifts_employee_time (employee_id, start_time),
INDEX idx_shifts_location_time (location_id, start_time)
);
-- Swap proposals with full lifecycle tracking
CREATE TABLE swap_proposals (
swap_id VARCHAR(128) PRIMARY KEY,
requesting_employee_id VARCHAR(128) NOT NULL REFERENCES users(employee_id),
receiving_employee_id VARCHAR(128) NOT NULL REFERENCES users(employee_id),
requested_shift_id VARCHAR(128) NOT NULL REFERENCES shifts(shift_id),
offered_shift_id VARCHAR(128) NOT NULL REFERENCES shifts(shift_id),
status VARCHAR(32) NOT NULL CHECK (status IN (
'PENDING',
'ACCEPTED',
'MANAGER_REVIEW',
'APPROVED',
'COMPLETED',
'REJECTED',
'CANCELLED',
'EXPIRED'
)),
expires_at TIMESTAMPTZ NOT NULL, -- When the proposal auto-expires
manager_approved_by VARCHAR(128) REFERENCES users(employee_id),
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
-- Ensure different employees
CONSTRAINT chk_different_employees CHECK (
requesting_employee_id != receiving_employee_id
),
-- Indexes for performance
INDEX idx_swap_requesting_employee (requesting_employee_id),
INDEX idx_swap_receiving_employee (receiving_employee_id),
INDEX idx_swap_status (status),
INDEX idx_swap_expires (expires_at),
INDEX idx_swap_shifts (requested_shift_id, offered_shift_id),
INDEX idx_swap_created (created_at)
);
-- Audit trail for completed swaps
CREATE TABLE swap_history (
history_id BIGSERIAL PRIMARY KEY,
swap_id VARCHAR(128) NOT NULL,
requesting_employee_id VARCHAR(128) NOT NULL,
receiving_employee_id VARCHAR(128) NOT NULL,
original_shift_id VARCHAR(128) NOT NULL, -- Shift that was given away
new_shift_id VARCHAR(128) NOT NULL, -- Shift that was received
manager_approved_by VARCHAR(128),
swapped_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
-- Indexes for reporting and queries
INDEX idx_history_requesting_employee (requesting_employee_id),
INDEX idx_history_receiving_employee (receiving_employee_id),
INDEX idx_history_swapped_at (swapped_at),
INDEX idx_history_swap_id (swap_id)
);
-- Employee preferences and constraints
CREATE TABLE employee_constraints (
employee_id VARCHAR(128) PRIMARY KEY REFERENCES users(employee_id),
max_hours_per_week INTEGER DEFAULT 40,
preferred_locations JSONB, -- Array of preferred location IDs
unavailable_times JSONB, -- Recurring unavailability
skill_levels JSONB, -- Skills and certifications
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- Notifications table for audit and retry
CREATE TABLE notifications (
notification_id BIGSERIAL PRIMARY KEY,
user_id VARCHAR(128) NOT NULL REFERENCES users(employee_id),
type VARCHAR(64) NOT NULL, -- 'SWAP_REQUEST', 'SWAP_APPROVED', etc.
title VARCHAR(256) NOT NULL,
message TEXT NOT NULL,
metadata JSONB, -- Additional context like swap_id
status VARCHAR(32) DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'SENT', 'FAILED', 'READ')),
channel VARCHAR(32) NOT NULL CHECK (channel IN ('EMAIL', 'SMS', 'PUSH', 'IN_APP')),
retry_count INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
sent_at TIMESTAMPTZ,
INDEX idx_notifications_user_id (user_id),
INDEX idx_notifications_status (status),
INDEX idx_notifications_created (created_at),
INDEX idx_notifications_type (type)
);
Users Table:
Shifts Table:
Swap Proposals Table:
Swap History Table:
Notifications Table:
Total after 5 years: ~3.2 GB (including indexes and overhead)
-- Partition shifts by month for easy archiving
CREATE TABLE shifts_2024_01 PARTITION OF shifts
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
-- Partition swap_history by year
CREATE TABLE swap_history_2024 PARTITION OF swap_history
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- Composite indexes for common query patterns
CREATE INDEX CONCURRENTLY idx_shifts_employee_status
ON shifts(employee_id, status)
WHERE status IN ('SCHEDULED', 'IN_PROGRESS');
CREATE INDEX CONCURRENTLY idx_swap_active_proposals
ON swap_proposals(status, expires_at)
WHERE status IN ('PENDING', 'ACCEPTED');
CREATE INDEX CONCURRENTLY idx_user_active_shifts
ON shifts(employee_id, start_time)
WHERE status = 'SCHEDULED' AND start_time > NOW();
Choose to share one swap matrix between all the Employee shift matching service instances, whereas it's better to create a structure to partition that by shift_id between the
Employee shift matching service instances.
Need to invent the mechanism to restore the last swap shift information from the client application if the Employee shift matching service is down and loses the information, now it ignores that moment.
The system would be deployed to AWS EKS and the service container would be orchestrated by Kubernetes.
If client applications request the swap history information frequently it gives the high load to Postgres and it may be cached.
If many employees would request changes of the swap shift intensively, it causes the race condition in Redis, so it's better to shard this process.
What are some future improvements you would make? How would you mitigate the failure scenario(s) you described above?
we improve this service in future by spending time on choose other object store.