Derek Sivers

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

Projects » Songtest

First, read “A free, open song contest idea.”

The big idea is that this songwriting contest will mostly run itself, on a monthly loop. A manager's role is only to contact companies to ask if they want to donate prizes for the winners, in return for the exposure of having the musicians pore over the list of prizes, with links back to the company's site for more info. (The power of drool.) A little admin work to enter the prize info, make sure the winner gets it, and oversee the system to make sure it's not being gamed in some surprising way, and voilá! Free song contest.

status: building

Schema

A (musician) person creates a song, with a title, artist, lyrics, and genre. They probably upload the audio to SoundCloud.

Then any (normal) person can create a vote for that song anonymously, (they can hear the song but don't know who it is until after they vote), which is a numeric rating with a comment and date.

The site manager is contacting companies to contribute prizes to the contest. When one agrees, the manager creates a company, tied to a person_id, and at least one prize, with a name and description.

The musician who has added a song to the contest can browse through the prizes and add a wish for their song to win any number of available prizes for that contest.

At the end of the month, all votes are tallied to create a winner, which is really just a cached summary of that month's activity.

Then the manager goes through the winning songs to assign the prizes (based on the wishes, when possible), by updating that prize's song_id to match the winning song.

REST API

URI GET POST PUT DELETE
/person/2 person info . update person .
/person/2/songs songs by this person . . .
/person/2/votes votes by this person . . .
/genre list all genres code to add new genre . .
/genre/jazz genre info . update genre delete genre
/genre/jazz/songs songs in this genre . . .
/genre/jazz/winners winning songs in this genre . . .
/song list all songs person_id, artist, title, soundcloud_url, lyrics, genre to create new song . .
/song/random a random song, used for voting. redirects to a random temp-ID anonymizing of song . . .
/song/hf71oza9fea7 anonymous representation of song with one-time-use random ID. ID must be used for voting to prove vote was blind. . . .
/song/2 song info . update song delete song
/song/2/votes votes for song . . .
/song/2/wishes list of prizes wished for by this song . . .
/vote/hf71oza9fea7 . person_id, vote number, comment to create vote . .
/vote/2 vote info . update vote delete vote
/company list all companies person_id, name, url to add new company . .
/company/2 company info and list of their prizes . update company delete company
/prize list all prizes company_id, name, description to add new prize . .
/prize/available list of available prizes . . .
/prize/2 prize info . update prize delete prize
/prize/2/wishes list of songs that wished for this prize . . .
/wish . song_id, prize_id to create wish . .
/wish/song.id/prize.id . . . remove this wish
/winner list of winners . . .
/winner/2 details about this winner . . .

Database Schema

BEGIN;

CREATE SCHEMA songtest;
SET search_path = songtest;

CREATE TABLE genres (
	code varchar(16) primary key
);

CREATE TABLE songs (
	id serial primary key,
	person_id integer not null,
	lang char(2) not null default 'en',
	artist text,
	title text,
	soundcloud_url text,
	lyrics text,
	genre varchar(16) REFERENCES genres,
	created_at timestamp with time zone not null default CURRENT_TIMESTAMP
);
CREATE INDEX songs_person ON songs(person_id);
CREATE INDEX songs_genre ON songs(genre);

CREATE TABLE votes (
	id serial primary key,
	person_id integer not null,
	song_id integer not null REFERENCES songs,
	vote integer,
	comment text,
	created_at timestamp with time zone not null default CURRENT_TIMESTAMP,
	UNIQUE (person_id, song_id)
);
CREATE INDEX votes_person ON votes(person_id);
CREATE INDEX votes_song ON votes(song_id);

CREATE TABLE companies (
	id serial primary key,
	person_id integer not null,
	name text,
	url text
);

CREATE TABLE prizes (
	id serial primary key,
	company_id integer not null REFERENCES companies,
	name text,
	description text,  -- English
	created_at timestamp with time zone not null default CURRENT_TIMESTAMP,
	song_id integer REFERENCES songs  -- NULL until claimed
);
CREATE INDEX prizes_company ON prizes(company_id);
CREATE INDEX prizes_song ON prizes(song_id);

CREATE TABLE wishes (
	song_id integer not null REFERENCES songs,
	prize_id integer not null REFERENCES prizes,
	PRIMARY KEY (song_id, prize_id)
);

CREATE TABLE winners (   -- cache/summary
	id serial primary key,
	month char(7) not null,
	genre varchar(16) not null REFERENCES genres,
	song_id integer not null REFERENCES songs,
	votes integer not null,
	average numeric
);
CREATE INDEX winner_month ON winners(month);
CREATE INDEX winner_genre ON winners(genre);

COMMIT;