Derek Sivers

Entrepreneur, programmer, avid student of life. I make useful things, and share what I learn.

Projects » Muckwork

Management of remote assistants doing projects for clients. See the story in the Schema, below, to understand the process.

status: unfinished

Schema

A client submits a project - something they need done. They have already made a payment so their account has a positive balance.

A manager breaks it into a TO-DO list: each step called a task. If it's a common project, the manager will pull up a template and copy each template_task into that project's tasks.

The manager shows the tasks and estimated cost to the client for approval. The client's balance needs to be enough to cover the project before we begin.

A worker, a person paid by the second for their time, is alerted when there are unassigned tasks to do. They claim them if they have time, which assigns the task.worker_id to theirs, and have to start or finish soon or else the tasks are unassigned and put back into the pool so that other workers can be alerted.

As the worker marks a task as finished, the total number of seconds spent on that task are multiplied by the worker's fee per-second, to create a charge, which is subtracted from the client's balance, and a worker_charge. These are separate because sometimes the client will be charged a flate rate for a task, and not billed by the second, but the worker still needs to get paid for their time.

When Muckwork pays the worker, we log a worker_payment, and the corresponding worker_charges are updated with its worker_payment.id, to mark those worker_charges as paid.

All activity through PayPal is saved as a paypaltxn (“txn” = transaction) - usually corresponding to a payment_id.

Site

The muckwork.com website has all of the functionality described above, plus the REST URLs. All URLs except home/login/signup require authentication.

The REST interface needs to have the full functionality of the site, because many clients will choose to assign and monitor projects from alternate interfaces like mobile apps. So consider all website pages as just views, with all real POST/PUT/DELETE action run through the REST interface.

On muckwork.com, each of the three roles of browser-users have a subdirectory: /c/ for client, /m/ for manager, /w/ for worker. When a person successfully logs in, they are sent to the appropriate subdirectory. This allows for uniformity of URLs, but with the appropriate views and permissions.

For the client, the site gives a full transparent window into the execution of their project and its tasks. They can see exact time worked, by who, and the worker's notes for each task. If emails are sent and received for a project by the worker or manager, both are saved in the database and viewable by the client. If phone calls are made for a project, the audio recording of the call is saved as an MP3 and attached to that project. The client can interrupt a project at any time, or add comments/questions for the manager or worker.

For the worker, the site is an efficient TO-DO list, with quick and easy communication with the manager, an easy email interface for sending and receiving emails for the project.

For the manager, a central dashboard shows the status of all projects and tasks, with clear visual feedback when a task is taking longer than it should. Very easy to send and receive emails to the worker and client regarding a project. A manager will spend most time turning incoming project requests into specific tasks, and ensuring that current projects are being done well.

REST API

URI GET POST PUT DELETE
/person/2 person info . update person .
/manager list all managers person_id to create new manager . .
/manager/2 manager info . update manager delete manager
/client list all clients person_id to create new client . .
/client/2 client info . update client delete client
/worker list all workers person_id to create new worker . .
/worker/2 worker info . update worker delete worker
/worker/2/tasks worker's unfinished tasks . . .
/worker/2/finished worker's finished tasks . . .
/worker/2/payments worker's payments . . .
/worker/2/all all worker's related info . . .
/project list all projects client_id, title, description to create new project . .
/project/2 project info . update project delete project
/project/2/tasks project tasks . . .
/project/2/unfinished project unfinished tasks . . .
/project/2/finished project finished tasks . . .
/project/2/charges project charges . . .
/project/2/emails project emails . . .
/project/2/all all project's info (see above) . . .
/task list all tasks project_id, title, description to create new task . .
/task/unfinished list all unfinished tasks (across all projects) . . .
/task/2 task info . update task delete task
/charge list all charges project_id, currency, millicents, notes to create new charge . .
/charge/2 charge info . update charge delete charge
/payment list all payments client_id, currency, millicents, notes to create new payment . .
/payment/2 payment info . update payment delete payment
/worker_charge list all worker_charges task_id, notes to create new worker_charge based on time spent times worker rate . .
/worker_charge/2 worker_charge info . update worker_charge delete worker_charge
/worker_payment list all worker_payments worker_id, currency, millicents, notes to create new payment IF millicents exactly match outstanding worker_charges updates worker_charges with payment_id . .
/worker_payment/2 worker_payment info . update worker_payment delete worker_payment
/template list all templates title, description to create new template . .
/template/2 template info and its template_tasks . update template delete template
/template_task . template_id, sortid, title, description to create new template_task . .
/template_task/2 template_task info . update template_task delete template_task
/paypaltxn list all paypaltxns txn_id, txn_type, info to create new paypaltxn . .
/paypaltxn/2 paypaltxn info . update paypaltxn delete paypaltxn

Database Schema

-- MuckWork has WORKERS that do the TASKS inside PROJECTS for CLIENTS.
BEGIN;

CREATE SCHEMA muckwork;
SET search_path = muckwork;

CREATE TABLE managers (
	id serial primary key,
	person_id integer not null UNIQUE
);

CREATE TABLE clients (
	id serial primary key,
	person_id integer not null UNIQUE,
	currency char(3) not null default 'USD',
	millicents_balance integer not null default 0
);

CREATE TABLE workers (
	id serial primary key,
	person_id integer not null UNIQUE,
	rating integer not null default 50,
	currency char(3),
	millicents_per_second integer
);

CREATE TABLE projects (
	id serial primary key,
	client_id integer not null REFERENCES clients,
	lang char(2) not null default 'en',
	title text,
	description text,
	created_at timestamp(0) with time zone not null default CURRENT_TIMESTAMP,
	quoted_at timestamp(0) with time zone,
	approved_at timestamp(0) with time zone,
	started_at timestamp(0) with time zone,
	finished_at timestamp(0) with time zone,
	seconds integer,
	quoted_currency char(3),
	quoted_millicents integer,
	quoted_ratetype varchar(4),  -- time, fix
	final_currency char(3),
	final_millicents integer
);
CREATE INDEX pjci ON projects(client_id);
CREATE INDEX pjsa ON projects(started_at);
CREATE INDEX pjaa ON projects(finished_at);
	
CREATE TABLE tasks (
	id serial primary key,
	project_id integer REFERENCES projects,
	worker_id integer REFERENCES workers,
	sortid integer,
	lang char(2) not null default 'en',
	title text,
	description text,
	created_at timestamp(0) with time zone not null default CURRENT_TIMESTAMP,
	started_at timestamp(0) with time zone,
	finished_at timestamp(0) with time zone
);
CREATE INDEX tpi ON tasks(project_id);
CREATE INDEX twi ON tasks(worker_id);
CREATE INDEX tsa ON tasks(started_at);
CREATE INDEX tfa ON tasks(finished_at);

CREATE TABLE charges (
	id serial primary key,
	created_at timestamp(0) with time zone not null default CURRENT_TIMESTAMP,
	project_id integer REFERENCES projects,
	currency char(3) not null,
	millicents integer not null,
	notes text
);
CREATE INDEX chpi ON charges(project_id);

CREATE TABLE payments (
	id serial primary key,
	created_at timestamp(0) with time zone not null default CURRENT_TIMESTAMP,
	client_id integer REFERENCES clients,
	currency char(3) not null,
	millicents integer not null,
	notes text
);
CREATE INDEX pci ON payments(client_id);

CREATE TABLE worker_payments (
	id serial primary key,
	worker_id integer not null REFERENCES workers,
	currency char(3) not null,
	millicents integer,
	created_at date not null default CURRENT_DATE,
	notes text
);
CREATE INDEX wpwi ON worker_payments(worker_id);

CREATE TABLE worker_charges (
	id serial primary key,
	task_id integer not null REFERENCES tasks,
	currency char(3) not null,
	millicents integer not null,
	payment_id integer REFERENCES worker_payments  -- NULL until paid
);
CREATE INDEX wcpi ON worker_charges(payment_id);
CREATE INDEX wcti ON worker_charges(task_id);

CREATE TABLE templates (
	id serial primary key,
	title text,
	description text
);

CREATE TABLE template_tasks (
	id serial primary key,
	template_id integer REFERENCES templates,
	sortid integer,
	title text,
	description text
);
CREATE INDEX ttti ON template_tasks(template_id);

CREATE TABLE paypaltxns (
	id serial primary key,
	payment_id integer REFERENCES payments(id),
	created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
	txn_id varchar(32),
	txn_type varchar(35),
	info text,
        reconciled boolean not null default false
);
CREATE INDEX txpi ON paypaltxns(payment_id);
CREATE INDEX txrc ON paypaltxns(reconciled);

CREATE TABLE emails_projects (
	project_id integer not null REFERENCES projects,
	email_id integer not null, -- REFERENCES people::emails.id
	PRIMARY KEY (project_id, email_id)
);
CREATE INDEX eppi ON emails_projects(project_id);
CREATE INDEX epei ON emails_projects(email_id);

CREATE TABLE words (
	id serial primary key,
	code varchar(127) unique,
	lang char(2) not null default 'en',
	phrase text,
	comment text
);

COMMIT;