KarmaList

Open database of contacts with ratings. REST interface so it can be integrated into other internal software.

For example, Muckwork will be constantly finding companies for people: a photographer in Berlin, a rehearsal studio in Chicago. Instead of keeping those findings locked in a private database, it would be better to open them up to the world, and let other people suggest companies they've used and like, or give ratings for which companies to avoid. So Muckwork will actually use it as its sole database of contacts.

Usually databases like this are locked down and sold, or traffic is encouraged to stay on the site for advertising, but Karmalist will be kept wide open with no standalone business motive, and encourage embedding into other software.

status: unstarted

Schema

A company is the basic contact, and has only an ID# and unique name.

A fact is a simple key-value pair, with unlimited number attached to a company to hold info like URL, email, address, description, etc.

A user is any registered user using the site, including a flag to say whether they are an administrator or not.

A user creates a rating for a company. A simple 0-5 digit.

A user can also create a comment for a company. Anything they want to say about a company. Kept separate from rating, so people can give a rating without a comment, or a comment without a rating.

Any change to a company or fact is saved as a change, like a diff.

Site

The site is pretty standard and obvious. Browse companies by any facts or ratings. Search for any phrase in a company name or fact info. No registration needed to view.

To leave a rating or comment, or to add or edit info, someone signs up as a user. (Then the usual “forgot my password” stuff.)

REST API

URI	GET	POST	PUT	DELETE
/company	all companies	name to create new company	.	.
/company/2	company info	.	update name	delete company
/company/2/facts	facts for company	name, fact to add new fact	.	.
/company/2/ratings	ratings for company	user_id, rating to add new rating	.	.
/company/2/comments	comments for company	user_id, comment to add new comment	.	.
/company/2/changes	changes to a company	.	.	.
/company/2/all	all company info (see above)	.	.	.
/fact/2	fact info	.	update fact	delete fact
/rating/2	rating info	.	update rating	delete rating
/comment/2	comment info	.	update comment	delete comment
/change/2	change info	.	update change (example: before patching)	delete change (refuse)
/patch	.	post change.id to apply+accept it	.	.

Database Schema

BEGIN;
CREATE SCHEMA karmalist;
SET search_path = karmalist;

CREATE TABLE companies (
	id serial primary key,
	name text not null unique
);

CREATE TABLE facts (
	id serial primary key,
	company_id integer not null REFERENCES companies(id),
	name varchar(32) not null,
	fact text
);
CREATE INDEX facts_company ON facts(company_id);

CREATE TABLE users (
	id serial primary key,
	remote_id text,
	username varchar(32) not null UNIQUE,
	hashpass char(32),
	email varchar(255),
	is_admin boolean not null default FALSE,
	created_at date not null default CURRENT_DATE
);

CREATE TABLE ratings (
	id serial primary key,
	user_id integer not null REFERENCES users(id),
	company_id integer not null REFERENCES companies(id),
	rating integer not null CHECK (rating >= 0 AND rating <= 5),
	created_at date not null default CURRENT_DATE
);
CREATE INDEX ratings_company ON ratings(company_id);

CREATE TABLE comments (
	id serial primary key,
	user_id integer not null REFERENCES users(id),
	company_id integer not null REFERENCES companies(id),
	comment text not null,
	created_at date not null default CURRENT_DATE
);
CREATE INDEX comments_company ON comments(company_id);

CREATE TABLE changes (
	id serial primary key,
	user_id integer not null REFERENCES users(id),
	company_id integer not null REFERENCES companies(id),
	fact_id integer REFERENCES facts(id),
	name varchar(32) not null,
	before text not null,
	after text not null,
	created_at date not null default CURRENT_DATE,
	accepted_at date
);
CREATE INDEX changes_accepted ON changes(accepted_at);

COMMIT;