1 -- PostgreSQL database schema.
7 -- Note that unsorted tasks (which need to be moved to 'imminent',
8 -- 'todo', 'ideas', 'retired' lists) are any item which appears in
9 -- this table but not in one of the other tables.
11 id serial not null primary key,
12 description text not null,
13 created timestamp without time zone not null default now(),
16 rhbz integer, -- if task comes from RHBZ
17 message_id text -- if task comes from email
20 -- Tasks which must be completed immediately and should take no more
21 -- than a few minutes (else they should be moved to another list).
22 create table imminent (
23 taskid integer not null references tasks (id),
24 added timestamp without time zone not null default now()
27 -- Regular to-do items with deadlines.
29 taskid integer not null references tasks (id),
30 added timestamp without time zone not null default now(),
31 deadline timestamp without time zone not null,
32 estimate interval -- optional
35 -- Ideas. These have no committed deadline.
37 taskid integer not null references tasks (id),
38 added timestamp without time zone not null default now()
41 -- Retired/done items.
42 create table retired (
43 taskid integer not null references tasks (id),
44 added timestamp without time zone not null default now()
47 -- Tags (eg 'urgent', 'personal', 'work') which can be added to any
50 id serial not null primary key,
52 colour text not null references colours (colour),
56 create table tags_tasks (
57 tagid integer not null references tags (id),
58 taskid integer not null references tasks (id),
59 unique (tagid, taskid)
62 create table colours (
66 insert into colours (colour) values ('black');
67 insert into colours (colour) values ('blue');
68 insert into colours (colour) values ('green');
69 insert into colours (colour) values ('red');
70 insert into colours (colour) values ('purple');
71 insert into colours (colour) values ('cyan');
72 insert into colours (colour) values ('yellow');