Technical Decisions - FreelanceOS
Overview
This document explains the why behind major technical choices in FreelanceOS. Each decision includes the problem context, alternatives considered, rationale for the chosen solution, and results achieved.
Table of Contents
- Workflow Automation Platform
- AI Model Selection
- Mobile Interface Platform
- Project Management Tool
- Job Discovery Method
- State Management Strategy
- Deployment Platform
- Database Choice
- Error Handling Philosophy
- Security Architecture
Decision: n8n (self-hosted, open-source)
Problem Context
Need a workflow automation platform to orchestrate complex multi-step processes (job screening, proposal generation, project management). Requirements:
- Connect multiple APIs (Claude, ClickUp, Telegram, Toggl)
- Handle webhooks for real-time triggering
- Manage stateful workflows
- Visual debugging capabilities
- Cost-effective for solo developer
Alternatives Considered
| Option |
Pros |
Cons |
Cost |
| Zapier |
Easy to use, 5000+ integrations, hosted |
Expensive ($20-50/mo), limited logic, vendor lock-in |
$20-50/mo |
| Make (Integromat) |
Visual builder, good for complex flows |
Expensive at scale, learning curve |
$9-29/mo |
| n8n |
Open-source, self-hostable, unlimited workflows, visual editor |
Requires server management, smaller community |
$40/mo (server only) |
| Custom code (Python/Node.js) |
Total control, no limits |
High dev time, no visual debugging, maintenance burden |
$0-20/mo (server) |
| Pipedream |
Code + low-code hybrid, generous free tier |
Limited workflow complexity, vendor lock-in |
$0-19/mo |
Decision Rationale
Chose n8n for:
- Cost: Self-hosting = $40/mo server vs $20-50/mo + per-execution fees with SaaS
- Unlimited workflows: No per-workflow pricing (Zapier charges per “Zap”)
- Data privacy: Client data stays on my server (important for NDA work)
- Visual debugging: See data flow through each node (crucial for troubleshooting)
- Open-source: Can modify if needed, no vendor lock-in
- Active development: Regular updates, growing community
- Webhook support: First-class support for webhook triggers
Trade-offs Accepted:
- Need to manage infrastructure (Docker, backups, monitoring)
- Smaller integration library vs Zapier (but has 400+ nodes)
- Steeper learning curve than Zapier
Results
- Workflows created: 12+ complex workflows
- Reliability: 99.5% uptime (only failures were DigitalOcean outages)
- Development speed: Visual editor reduced debugging time 60% vs custom code
- Cost savings: ~$180/year vs Zapier equivalent ($240/year server vs $600/year Zapier Professional)
Code Example: n8n Workflow Structure (Conceptual)
// n8n workflow is JSON, but conceptually:
{
"nodes": [
{ "type": "n8n-nodes-base.emailTrigger", "name": "Email Trigger" },
{ "type": "n8n-nodes-base.function", "name": "Parse Job Data" },
{ "type": "n8n-nodes-base.if", "name": "Score >= 70?" },
{ "type": "n8n-nodes-base.httpRequest", "name": "Claude API" },
{ "type": "n8n-nodes-base.telegram", "name": "Send Approval" }
],
"connections": {
"Email Trigger": { "main": [[{ "node": "Parse Job Data" }]] },
// ... etc
}
}
2. AI Model Selection
Decision: Claude API (Sonnet 4.5)
Problem Context
Need an AI model to generate:
- High-quality, personalized Upwork proposals (200-400 words)
- Interview preparation materials
- Project retrospectives
Requirements:
- Natural writing style (not formulaic)
- Consistent quality across multiple runs
- Large context window (full job posting + profile)
- Affordable for 20-30 generations/month
Alternatives Considered
| Model |
Writing Quality |
Context Window |
Cost (per proposal) |
Strengths |
| GPT-4o |
Very good, sometimes formulaic |
128K tokens |
~$0.015 |
Fast, cheaper, widely adopted |
| GPT-3.5 |
Good but generic |
16K tokens |
~$0.002 |
Very cheap, fast |
| Claude Sonnet 4.5 |
Excellent, nuanced |
200K tokens |
~$0.025 |
Best writing quality, large context |
| Claude Haiku 4.5 |
Good, concise |
200K tokens |
~$0.003 |
Cheapest Claude option, fast |
| Llama 3 (self-hosted) |
Good but requires tuning |
Variable |
$0 (server cost) |
Free, privacy, control |
Decision Rationale
Chose Claude Sonnet 4.5 for:
- Writing Quality: Most human-like, nuanced writing
- GPT-4o proposals felt slightly formulaic
- Claude better at matching tone to job posting
- Natural conversational flow
- Instruction Following: Precisely follows complex prompts
- Consistently hits 200-400 word target
- Adapts style based on job type (startup vs enterprise)
- Doesn’t hallucinate credentials
- Context Window: 200K tokens allows full context
- Can include full job posting (2-3K words)
- Freelancer profile (1-2K words)
- 3-5 past successful proposals as examples
- Total context: ~10K tokens comfortably
- Cost-Effectiveness: $0.50-0.75/month for 20-30 proposals
- Input: $3 per 1M tokens
- Output: $15 per 1M tokens
- Avg proposal: ~2K input + 500 output = $0.025/proposal
- 30 proposals/month = $0.75/month
Trade-offs Accepted:
- Slightly more expensive than GPT-4o (~60% more)
- Slower than GPT-3.5 (~3 seconds vs 1 second)
- Smaller ecosystem than OpenAI
Why NOT GPT-4o:
Writing quality difference is worth $0.015 extra cost. A better proposal has much higher ROI:
- Better proposal → Higher acceptance rate
- 1 extra project won = $2,000+ revenue
- Quality > cost at this scale
Why NOT self-hosted Llama:
- Time to fine-tune outweighs savings ($0.75/month)
- GPU server cost ~$50-100/month (worse than $0.75/month API)
- Writing quality not on par with frontier models
Results
- Proposal acceptance rate: 10-15% (industry average is 5-10%)
- Client feedback: “This was the most thoughtful proposal I received”
- Consistency: 95% of proposals required no edits before sending
- Cost: $0.82/month actual usage (28 proposals in January)
Prompt Engineering Example (Conceptual)
// Simplified prompt structure (actual prompts are proprietary)
const prompt = `You are an expert freelance proposal writer.
Context:
- Job Posting: ${jobDescription}
- Freelancer Profile: ${profile}
- Past Successful Proposals: ${examples}
Requirements:
1. Address client's specific pain points
2. Highlight relevant experience (don't invent)
3. Professional yet personable tone
4. 200-400 words
5. End with clear call-to-action
Generate proposal now.`;
Decision: Telegram Bot
Problem Context
As a homeschool parent, work happens in fragmented 10-15 minute blocks throughout the day. Need a mobile interface that:
- Receives instant notifications
- Allows one-tap approvals
- Works on any device (phone, tablet, desktop)
- Doesn’t require custom app development
Alternatives Considered
| Option |
Setup Time |
User Experience |
Cost |
Push Notifications |
| Custom mobile app |
4-8 weeks |
Perfect, branded |
$5,000-15,000 dev |
Yes (FCM/APNS) |
| Email-based |
1 hour |
Slow, cluttered |
$0 |
Yes (email client) |
| Slack bot |
1 day |
Good for teams |
$0-8/mo |
Yes |
| Telegram bot |
4 hours |
Excellent |
$0 |
Yes |
| SMS/Twilio |
1 day |
Limited formatting |
$0.01-0.05/msg |
No (SMS only) |
| Web dashboard |
1-2 weeks |
Good on desktop |
$0-20/mo |
No |
Decision Rationale
Chose Telegram Bot for:
- Zero Development Time for Client:
- No app to build
- No app store submissions
- Users already have Telegram (or can install in 1 minute)
- Rich Interaction:
- Inline keyboards: One-tap approve/reject buttons
- Markdown formatting: Bold, code blocks, links
- Media support: Can send images, PDFs (future: interview prep docs)
- Commands:
/approve, /status, /submit_job
- Universal Platform:
- iOS app
- Android app
- Desktop app (Windows, Mac, Linux)
- Web interface (no install needed)
- Same interface across all platforms
- Push Notifications:
- Instant delivery (< 1 second)
- Custom sounds/vibrations
- Badge counts
- Lock screen previews
- Free (No Per-Message Cost):
- Unlimited messages
- Unlimited users (if scaling to team)
- No API rate limits for small scale
- Developer-Friendly API:
- Webhook support (real-time)
- Simple REST API
- Excellent documentation
- Active community
Trade-offs Accepted:
- Single webhook limitation: Can only register ONE webhook URL per bot
- Solved with master router pattern
- Not enterprise-grade: Telegram blocked in some countries
- Not an issue for solo US-based freelancer
- No data ownership: Messages flow through Telegram servers
- Acceptable for non-sensitive data (job titles, approval requests)
Why NOT custom mobile app:
$5K-15K development + 4-8 weeks vs 4 hours setup time = not worth it for solo use
Why NOT email:
- Cluttered inbox (mixing personal + work)
- No interactive buttons (need to click link → open web page)
- Slower (email delivery can lag 30-60 seconds)
Why NOT Slack:
- Overkill for solo use (designed for teams)
- Less mobile-friendly than Telegram
- Free plan limits message history
Results
- Mobile operation: 95% of approvals done from phone
- Response time: Average 2 minutes from notification to action
- User experience: One-tap approvals vs multi-step web dashboard
- Cost: $0/month
Telegram Bot Pattern Example
// Simplified Telegram interaction flow
// 1. System sends approval request
await telegram.sendMessage({
chat_id: USER_ID,
text: `*New Proposal Ready*\n\nJob: ${jobTitle}\nBudget: $${budget}\n\nReview?`,
parse_mode: 'Markdown',
reply_markup: {
inline_keyboard: [[
{ text: '✅ Approve', callback_data: `approve_${jobId}` },
{ text: '❌ Reject', callback_data: `reject_${jobId}` },
{ text: '✏️ Edit', callback_data: `edit_${jobId}` }
]]
}
});
// 2. User taps button → Callback received
// Master router workflow processes:
if (callback_data.startsWith('approve_')) {
const jobId = callback_data.split('_')[1];
// Trigger approval workflow
triggerWorkflow('approval-handler', { jobId, action: 'approve' });
}
Decision: ClickUp
Problem Context
Need a project management system to:
- Track job opportunities through pipeline (prospect → proposal → active → complete)
- Store custom data (job URL, budget, score)
- Trigger automation based on status changes
- Mobile access for on-the-go visibility
Alternatives Considered
| Tool |
API Quality |
Custom Fields |
Mobile App |
Cost/Month |
Webhooks |
| Notion |
Limited API |
Database properties |
Good |
$0-10 |
No |
| Airtable |
Excellent |
Full flexibility |
Good |
$0-20 |
Yes (paid) |
| Trello |
Good |
Power-Ups only |
Excellent |
$0-12.50 |
Yes |
| ClickUp |
Excellent |
Full flexibility |
Excellent |
$0-9 |
Yes (free) |
| Asana |
Good |
Limited |
Excellent |
$0-13.49 |
Yes (paid) |
| Custom database |
Perfect (own API) |
Unlimited |
N/A |
$0-20 |
Self-built |
Decision Rationale
Chose ClickUp for:
- Flexible Custom Fields:
- Can create custom fields: Job URL (link), Budget (currency), Score (number)
- Multiple field types: text, number, date, dropdown, labels
- Use for filtering and sorting (e.g., show jobs with budget > $1000)
- Robust API:
- RESTful API with full CRUD operations
- Create tasks, update statuses, add comments via API
- Well-documented with code examples
- Rate limits generous for solo use (100 requests/minute)
- Webhooks (Free Tier):
- Trigger n8n workflows when task status changes
- Example: Task moves to “Active Projects” → Start Toggl timer
- No webhook costs (unlike Airtable paid tier)
- Mobile App:
- Full-featured iOS/Android apps
- Can view pipeline, update statuses from phone
- Push notifications for task updates
- Affordable:
- Free tier: 100MB storage, unlimited tasks
- Unlimited tier: $9/month for unlimited storage, goals, dashboards
- Cheaper than Asana, Airtable at comparable features
- Multiple Views:
- List view (for pipeline stages)
- Board view (Kanban for visual tracking)
- Calendar view (for deadlines)
- Table view (spreadsheet-like for bulk editing)
Trade-offs Accepted:
- Steeper learning curve than Trello
- Occasional UI lag (heavy app)
- Not as “database-first” as Airtable
Why NOT Notion:
- API was limited at decision time (2024)
- No webhooks
- Slower performance as database grows
Why NOT Airtable:
- Webhooks require paid tier ($20/month vs ClickUp $9/month)
- Less project management-focused (more database-focused)
Why NOT custom database:
- Building CRUD interface = 1-2 weeks dev time
- Maintenance burden
- No mobile app
Results
- Pipeline visibility: Clear view of 5-10 active opportunities at any time
- Automation: 15+ workflow automations based on ClickUp webhooks
- Mobile usage: 40% of status updates done from phone
- Cost: $9/month (worth it for time saved in tracking)
ClickUp API Pattern Example
// Create task when proposal approved
const response = await fetch('https://api.clickup.com/api/v2/list/{list_id}/task', {
method: 'POST',
headers: {
'Authorization': API_KEY,
'Content-Type': 'application/json'
},
body: JSON.stringify({
name: jobTitle,
description: proposalText,
status: 'Proposal Sent',
custom_fields: [
{ id: 'job_url_field_id', value: jobUrl },
{ id: 'budget_field_id', value: budget },
{ id: 'score_field_id', value: jobScore }
],
due_date: Date.now() + (7 * 24 * 60 * 60 * 1000) // 7 days from now
})
});
const task = await response.json();
// task.id can be used for future updates
5. Job Discovery Method
Decision: Email Forwarding + Manual Telegram Trigger
Problem Context
Upwork doesn’t provide an official job discovery API. Previous methods no longer work:
- RSS feeds: Blocked by anti-bot detection (nx protection)
- Vollna service: Defunct/shut down
- Web scraping: Violates Terms of Service, high detection risk
Need a job discovery method that:
- Complies with Upwork ToS
- Provides real-time or near-real-time alerts
- Doesn’t risk account suspension
- Can be automated
Alternatives Considered
| Method |
ToS Compliant |
Real-Time |
Risk Level |
Effort |
| RSS feeds |
Unclear |
Yes |
Medium (blocked) |
Low |
| Upwork official API |
Yes |
N/A |
None |
N/A (doesn’t exist) |
| Browser automation (Puppeteer) |
No |
Yes |
High (suspension) |
Medium |
| Email parsing |
Yes |
Near (1-2 min) |
None |
Low |
| Manual monitoring |
Yes |
No (sporadic) |
None |
High (manual) |
| Telegram manual trigger |
Yes |
Instant (when found) |
None |
Low (semi-manual) |
Decision Rationale
Chose Email Forwarding + Manual Telegram Trigger for:
-
Email Forwarding (Primary - Automated):
Why:
- Upwork’s official notification system (100% ToS compliant)
- Email delivery within 1-2 minutes of job posting
- No scraping or ToS violations
- Reliable (email infrastructure is mature)
How:
- Configure Upwork job alerts for target criteria
- Forward to dedicated email (e.g., upwork-alerts@domain.com)
- n8n Email Trigger monitors inbox via IMAP every 5 minutes
- Parse email for job details, extract URL
- Fetch full job page via HTTP request (viewing public page = ToS compliant)
Limitations:
- Email format could change (requires parser update)
- 5-10 minute total lag (email delivery + polling + processing)
- Limited job details in email (must fetch full page separately)
-
Manual Telegram Trigger (Secondary - On-Demand):
Why:
- For high-priority jobs discovered while browsing Upwork
- Instant processing (no waiting for email)
- User has already pre-qualified the job (zero false positives)
- Simple: Copy URL → Send to Telegram → Proposal ready in 60 seconds
How:
- Telegram command:
/submit_job https://www.upwork.com/jobs/...
- n8n webhook receives command
- Fetch job page, extract details
- Process through standard workflow (score → generate → approve)
Use Case:
- Found perfect job while browsing Upwork
- Want to be first to submit proposal
- Can’t wait for email alert cycle
Trade-offs Accepted:
- Not 100% automated (manual trigger for priority jobs)
- 5-10 minute lag for email alerts (vs instant RSS would have been)
- Email parsing fragility (Upwork could change format)
Why NOT browser automation:
- High risk: Upwork actively detects automated browsing
- ToS violation: Could result in account suspension
- Fragile: Breaks every time Upwork updates UI
- Not worth risk: Losing Upwork account = losing income stream
Why NOT third-party aggregators:
- Limited: Most don’t have real-time Upwork data
- Cost: $20-50/month additional
- Data quality: Often delayed or incomplete
Results
- Job coverage: 95% via email, 5% via manual trigger
- ToS compliance: Zero account issues in 6 months
- Response time: Avg 10 minutes (email alert → proposal ready)
- Reliability: Email parsing success rate 98% (2% require manual review)
Email Parsing Pattern Example
// Simplified email parsing logic
const emailBody = emailTriggerNode.json.html;
// Extract job URL (Upwork emails have consistent pattern)
const urlMatch = emailBody.match(/https:\/\/www\.upwork\.com\/jobs\/~[a-f0-9]+/);
const jobUrl = urlMatch ? urlMatch[0] : null;
// Extract basic details from email
const titleMatch = emailBody.match(/<h2.*?>(.*?)<\/h2>/);
const jobTitle = titleMatch ? titleMatch[1] : 'Unknown Title';
// Fetch full job page for complete details
const jobPage = await fetch(jobUrl);
const $ = cheerio.load(await jobPage.text());
const fullDescription = $('.job-description').text();
const budget = $('.budget-amount').text();
const skills = $('.skills span').toArray().map(el => $(el).text());
// Return structured job object
return {
source: 'email',
url: jobUrl,
title: jobTitle,
description: fullDescription,
budget: parseBudget(budget),
skills: skills
};
6. State Management Strategy
Decision: workflow.staticData + PostgreSQL
Problem Context
n8n workflows are stateless by default. Each execution is independent, with no memory of previous runs. This creates challenges:
Example scenario:
- Workflow generates proposal, sends to Telegram for approval
- Workflow execution ends
- User clicks “Approve” button → triggers new workflow execution
- New execution has no memory of original proposal
Need: Maintain state across workflow executions without adding external dependencies.
Alternatives Considered
| Method |
Persistence |
Scope |
Performance |
Complexity |
| workflow.staticData |
Survives restarts |
Single workflow |
Fast (in-memory) |
Low |
| External Redis |
Persistent |
Cross-workflow |
Fast (network) |
Medium |
| PostgreSQL |
Persistent |
Cross-workflow |
Slower (DB query) |
Medium |
| File system |
Persistent |
Cross-workflow |
Slow (I/O) |
Low |
| External API/service |
Persistent |
Cross-workflow |
Slow (HTTP) |
High |
Decision Rationale
Chose Multi-Tier Strategy:
Tier 1: workflow.staticData (Short-Term State)
- Use for: Pending approvals, temporary caches, execution counters
- Persistence: Survives n8n restarts, cleared on workflow deactivation
- Performance: Instant (in-memory)
- Limitations: Single workflow scope, not suitable for long-term storage
Tier 2: PostgreSQL (Long-Term State)
- Use for: Historical job data, proposal archive, analytics
- Persistence: Permanent until deleted
- Performance: ~10-50ms query time
- Limitations: Requires database schema design
Why this hybrid approach:
- Performance: Hot data (pending approvals) in memory, cold data (history) in DB
- Simplicity: No additional infrastructure (PostgreSQL already required for n8n)
- Reliability: staticData + DB backup = no data loss
Why NOT Redis:
- Would require additional service ($5-10/month)
- PostgreSQL sufficient for solo use (not high-traffic)
- n8n already uses PostgreSQL for its own state
Why NOT file system:
- Slower than in-memory
- Concurrent access issues
- Harder to query
Implementation Pattern
// Tier 1: staticData for pending approvals
const workflow = this.getWorkflow();
const staticData = workflow.staticData;
// Initialize if first run
if (!staticData.pendingApprovals) {
staticData.pendingApprovals = {};
}
// Store pending approval
staticData.pendingApprovals[jobId] = {
proposalText: generatedProposal,
jobData: jobDetails,
timestamp: Date.now(),
score: jobScore
};
// Later: User approves via Telegram
// Different workflow execution, but can access staticData
const pendingData = staticData.pendingApprovals[jobId];
// Use pendingData to create ClickUp task, etc.
delete staticData.pendingApprovals[jobId]; // Clean up
// Tier 2: PostgreSQL for historical record
await db.query(`
INSERT INTO jobs (id, title, budget, score, status, created_at)
VALUES ($1, $2, $3, $4, $5, NOW())
`, [jobId, jobTitle, budget, score, 'qualified']);
Results
- State retrieval time: < 1ms (staticData), ~15ms (PostgreSQL)
- Data loss: Zero (staticData persists across restarts, PostgreSQL backed up daily)
- Complexity: Low (no additional services to manage)
Decision: DigitalOcean Droplet (self-hosted Docker)
Problem Context
Need hosting for:
- n8n application
- PostgreSQL database
- (Optional) Redis cache
Requirements:
- Reliable uptime (99%+)
- Affordable ($20-50/month budget)
- Full control (can customize, install packages)
- Easy backup/restore
Alternatives Considered
| Platform |
Type |
Cost/Month |
Pros |
Cons |
| DigitalOcean Droplet |
VPS |
$40 |
Full control, predictable cost |
Requires DevOps |
| AWS EC2 |
VPS |
$40-60 |
Mature, many services |
Complex pricing, overkill |
| Heroku |
PaaS |
$14-50 |
Easy, managed Postgres |
Vendor lock-in, sleep on free tier |
| n8n.cloud |
SaaS |
$20-50 |
Zero DevOps, auto-updates |
Less control, vendor lock-in |
| Render |
PaaS |
$7-25 |
Modern, Docker support |
Newer, less mature |
| Railway |
PaaS |
$5-20 |
Great DX, simple pricing |
Smaller company, less proven |
Decision Rationale
Chose DigitalOcean Droplet for:
- Predictable Pricing:
- Flat $40/month for 4GB RAM droplet
- No surprise costs (vs AWS where bandwidth, snapshots, etc. add up)
- Includes 4TB bandwidth (more than sufficient)
- Full Control:
- Root access (can install anything)
- Custom Docker Compose setup
- Can optimize PostgreSQL, add Redis, etc.
- No platform limitations
- Simplicity:
- Single droplet runs entire stack
- No complex networking/security groups (vs AWS)
- Easy snapshots for backups
- Performance:
- 4GB RAM sufficient for n8n + PostgreSQL + Redis
- SSD storage (fast database queries)
- Multiple datacenter locations
- Data Ownership:
- Full control over data (important for NDA client work)
- Can export/migrate any time
- No vendor lock-in
Trade-offs Accepted:
- Requires DevOps knowledge (Docker, Linux, backups)
- No auto-scaling (not needed for solo use)
- Manual security updates (vs managed platforms)
Why NOT n8n.cloud:
- $20-50/month for managed vs $40/month for full VPS
- Less control (can’t add custom services like Redis)
- Data on third-party servers (client NDA concerns)
Why NOT AWS:
- Overkill for solo use
- Complex pricing (EC2 + EBS + data transfer + backups = hard to predict)
- Steeper learning curve
Why NOT Heroku:
- Free tier sleeps (unacceptable for 24/7 automation)
- $14/month hobby tier has limited RAM
- $50/month production tier = same as DigitalOcean but less control
Infrastructure Setup
Droplet Specs:
- Size: Basic / 4GB RAM / 2 vCPUs / 80GB SSD
- OS: Ubuntu 22.04 LTS
- Location: New York (closest to US East Coast clients)
- Backups: Weekly automated snapshots ($8/month)
Docker Compose Stack:
# Simplified docker-compose.yml
version: '3.8'
services:
n8n:
image: n8nio/n8n:latest
restart: unless-stopped
ports:
- "5678:5678"
environment:
- N8N_HOST=${DOMAIN}
- DB_TYPE=postgresdb
- DB_POSTGRESDB_HOST=postgres
volumes:
- n8n_data:/home/node/.n8n
postgres:
image: postgres:14
restart: unless-stopped
environment:
- POSTGRES_DB=n8n
- POSTGRES_PASSWORD=${DB_PASSWORD}
volumes:
- postgres_data:/var/lib/postgresql/data
nginx:
image: nginx:alpine
restart: unless-stopped
ports:
- "80:80"
- "443:443"
volumes:
- ./nginx.conf:/etc/nginx/nginx.conf
- ./ssl:/etc/nginx/ssl
Results
- Uptime: 99.6% over 6 months (only 1 unplanned outage from DigitalOcean network issue)
- Cost: $40/month droplet + $8/month backups = $48/month total
- Performance: Workflows execute in < 2 seconds (ample headroom)
- DevOps time: ~1 hour/month (updates, monitoring, occasional troubleshooting)
8. Database Choice
Decision: PostgreSQL 14
Problem Context
n8n requires a database for:
- Workflow execution history
- Credentials storage (encrypted)
- Internal state management
Additionally, need database for:
- Job history (for analytics)
- Proposal archive
- Performance metrics
Alternatives Considered
| Database |
Type |
n8n Support |
Pros |
Cons |
| SQLite |
File-based SQL |
Yes |
Simple, no server |
Single-user, limited scale |
| PostgreSQL |
Relational SQL |
Yes |
Robust, mature, full-featured |
More complex than SQLite |
| MySQL |
Relational SQL |
Yes |
Popular, good docs |
Slightly less feature-rich than Postgres |
| MongoDB |
NoSQL |
No |
Flexible schema |
Not supported by n8n |
Decision Rationale
Chose PostgreSQL for:
- n8n Recommendation:
- n8n official docs recommend PostgreSQL for production
- Best-tested with n8n (vs SQLite for dev only)
- Robustness:
- ACID compliance (data integrity)
- Proven reliability at scale
- Excellent crash recovery
- Features:
- JSONB data type: Store structured job data flexibly
- Full-text search: Search historical proposals
- Window functions: Analytics queries (running averages, etc.)
- Extensions: pg_trgm for fuzzy searching, pgvector for future ML
- Performance:
- Query planner optimizations
- Indexes for fast lookups
- Sufficient for solo use (hundreds of jobs/month)
- Ecosystem:
- Widely supported (easy to find hosting, backup tools)
- Many GUI tools (pgAdmin, DBeaver)
- Docker image well-maintained
Trade-offs Accepted:
- More complex than SQLite (but solo use doesn’t hit complexity)
- Requires server process (vs file-based SQLite)
Why NOT SQLite:
- n8n docs say “SQLite only for testing/development”
- Concurrent access limitations
- Not suitable for production
Why NOT MySQL:
- PostgreSQL has better JSON support (important for job data)
- Personal preference (more experience with Postgres)
Schema Design Example
-- Jobs table (long-term storage)
CREATE TABLE jobs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
upwork_job_id VARCHAR UNIQUE,
title TEXT NOT NULL,
description TEXT,
budget DECIMAL,
budget_type VARCHAR, -- 'fixed' or 'hourly'
skills TEXT[], -- PostgreSQL array type
client_data JSONB, -- Flexible JSON storage
score INTEGER,
status VARCHAR, -- 'qualified', 'rejected', 'proposal_sent', 'won', 'lost'
source VARCHAR, -- 'email' or 'manual'
created_at TIMESTAMP DEFAULT NOW(),
processed_at TIMESTAMP
);
-- Indexes for performance
CREATE INDEX idx_jobs_status ON jobs(status);
CREATE INDEX idx_jobs_score ON jobs(score);
CREATE INDEX idx_jobs_created_at ON jobs(created_at);
-- Proposals table
CREATE TABLE proposals (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
job_id UUID REFERENCES jobs(id),
content TEXT NOT NULL,
status VARCHAR, -- 'draft', 'approved', 'sent', 'rejected'
clickup_task_id VARCHAR,
approved_at TIMESTAMP,
submitted_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW()
);
-- Analytics view
CREATE VIEW job_analytics AS
SELECT
DATE_TRUNC('week', created_at) as week,
COUNT(*) as total_jobs,
AVG(score) as avg_score,
COUNT(*) FILTER (WHERE status = 'won') as jobs_won,
AVG(budget) FILTER (WHERE status = 'won') as avg_won_budget
FROM jobs
GROUP BY week
ORDER BY week DESC;
Results
- Query performance: < 10ms for most queries (indexed properly)
- Data volume: ~1000 jobs over 6 months (~5MB data)
- Reliability: Zero data corruption or loss
- Backup: Daily pg_dump → AWS S3 (automated)
9. Error Handling Philosophy
Decision: Fail-Safe with Human Notification
Problem Context
Automation systems can fail due to:
- External API outages (Claude, ClickUp, Telegram down)
- Rate limits exceeded
- Network issues
- Unexpected data formats
- Code bugs
Requirement: System should never silently fail or block other jobs from processing.
Approach: Fail-Safe Design
Core Principle: “If automation fails, alert human and continue processing other jobs.”
Pattern
Every workflow follows:
Try:
Main workflow logic
Catch (Specific error - e.g., rate limit):
Retry with exponential backoff (3x max)
Catch (Still failing or other error):
Send Telegram alert with error details
Log error to PostgreSQL
Mark item as "Needs Manual Review"
Continue workflow (don't crash)
Implementation Example
// n8n Function node: Error handling wrapper
try {
// Main logic: Call Claude API
const proposal = await generateProposal(jobData);
return { success: true, proposal };
} catch (error) {
// Specific error: Rate limit
if (error.status === 429) {
// Retry with backoff
for (let i = 0; i < 3; i++) {
await sleep(Math.pow(2, i) * 1000); // 1s, 2s, 4s
try {
const proposal = await generateProposal(jobData);
return { success: true, proposal };
} catch (retryError) {
if (i === 2) break; // Last retry failed
}
}
}
// All retries failed or other error
// Alert human via Telegram
await telegram.sendMessage({
chat_id: ADMIN_ID,
text: `❌ *Error: Proposal Generation Failed*\n\nJob: ${jobData.title}\nError: ${error.message}\n\nPlease review manually.`,
parse_mode: 'Markdown'
});
// Log error
await db.query(`
INSERT INTO errors (workflow, job_id, error_message, created_at)
VALUES ($1, $2, $3, NOW())
`, ['proposal-generator', jobData.id, error.message]);
// Mark job for manual review
await db.query(`
UPDATE jobs SET status = 'needs_manual_review' WHERE id = $1
`, [jobData.id]);
// Return error state (but don't crash workflow)
return { success: false, error: error.message };
}
Rationale
- Don’t Block Other Jobs:
- If one job’s API call fails, others can still process
- Workflow continues even after error
- Human in the Loop:
- Immediate Telegram notification (can respond in minutes)
- All context provided (which job, what error)
- Can manually complete the failed task
- Graceful Degradation:
- System operates at reduced capacity vs total failure
- Example: Claude API down → Can still screen jobs, just can’t generate proposals automatically
- Audit Trail:
- All errors logged to database
- Can analyze patterns (is Claude API frequently down on Sundays?)
- Helps identify chronic issues
Results
- System uptime: 99.5% (workflows run even when individual services fail)
- Error recovery time: Avg 15 minutes (get Telegram alert → manually handle)
- Silent failures: Zero (all errors generate Telegram alert)
10. Security Architecture
Decision: Environment Variables + SSH Keys + HTTPS
Problem Context
System handles sensitive data:
- API keys (Claude, ClickUp, Telegram, Toggl)
- Client job data (under NDA)
- Proposal content (competitive advantage)
Security Measures Implemented
1. API Key Management:
- Storage:
.env file (never committed to git)
- n8n access: n8n credentials manager (encrypted in PostgreSQL)
- Rotation: Quarterly key rotation schedule
- Principle: Never hardcode keys in workflows
2. Server Access:
- SSH: Key-based authentication only (passwords disabled)
- Firewall: UFW (only ports 22, 80, 443 open)
- Updates: Automated security updates (unattended-upgrades)
3. Network Security:
- HTTPS: All traffic encrypted (Let’s Encrypt SSL)
- Cloudflare: DDoS protection and CDN
- Webhook secrets: Validate all incoming webhooks with secret tokens
4. Application Security:
- n8n login: Basic auth enabled (username/password)
- Database: Not exposed to internet (only localhost)
- Backups: Encrypted before uploading to S3
5. Data Handling:
- Logging: No sensitive data in logs (job IDs only, not full content)
- Retention: Job data deleted after 12 months (GDPR-style)
- Client data: Minimal PII stored (job titles, descriptions only)
Rationale
Why environment variables:
- Industry standard for secrets management
- Easy to rotate (change .env, restart Docker)
- Not committed to version control
Why SSH keys:
- More secure than passwords (can’t be brute-forced)
- Standard practice for VPS access
Why Let’s Encrypt:
- Free SSL certificates
- Automated renewal
- Encrypts all API traffic
Trade-offs Accepted:
- No advanced secrets manager (HashiCorp Vault, AWS Secrets Manager)
- Overkill for solo use
- .env sufficient with proper file permissions (chmod 600)
Results
- Security incidents: Zero in 6 months
- Key rotations: Performed quarterly as scheduled
- Compliance: Meets basic GDPR requirements (data deletion, minimal PII)
Summary of Technical Decisions
| Decision |
Choice |
Key Reasons |
| Workflow Platform |
n8n (self-hosted) |
Cost, control, unlimited workflows |
| AI Model |
Claude Sonnet 4.5 |
Superior writing quality, large context |
| Mobile Interface |
Telegram Bot |
Free, rich UX, universal platform |
| Project Management |
ClickUp |
Flexible API, webhooks, mobile app |
| Job Discovery |
Email + Manual Telegram |
ToS compliant, reliable, zero risk |
| State Management |
staticData + PostgreSQL |
Fast + persistent, no extra services |
| Deployment |
DigitalOcean Droplet |
Predictable cost, full control |
| Database |
PostgreSQL 14 |
Robust, n8n recommended, full-featured |
| Error Handling |
Fail-safe + Telegram alerts |
Human in loop, no silent failures |
| Security |
Env vars + SSH + HTTPS |
Industry standard, sufficient for solo use |
Overall Philosophy: Choose simple, proven tools with good APIs. Prefer self-hosting for control and cost. Fail-safe design over perfection. Mobile-first UX. ToS compliance always.
Note: These technical decisions were made for a solo freelancer use case. Different constraints (team size, budget, compliance requirements) may lead to different optimal choices. The decision framework and rationale are reusable.