1 files changed, 182 insertions, 0 deletions
diff --git a/db/kanta.sql b/db/kanta.sql
new file mode 100644
index 0000000..e6b4fb9
--- /dev/null
+++ b/db/kanta.sql
@@ -0,0 +1,182 @@
+-- set GLOBAL time_zone = '+00:00';
+pragma foreign_keys = on;
+
+-- user / organization data
+drop table if exists users;
+create table users (
+   -- id of the user / org
+   id integer not null primary key autoincrement,
+   -- creation time
+   created_at timestamp not null default current_timestamp,
+   -- last update
+   updated_at timestamp,
+   -- is this user an organization?
+   org boolean not null,
+   -- email address
+   email text not null unique,
+   -- user / organization name
+   name text not null unique,
+   -- password
+   pass text not null,
+   -- metadata
+   meta text not null default '{}'
+);
+
+-- organization level access control lists
+drop table if exists org_acls;
+create table org_acls (
+   -- org acl id
+   id integer not null primary key autoincrement,
+   -- user id (user.organization == true)
+   org integer not null,
+   -- user id (member of the organization)
+   member integer not null,
+   -- ACL bits (what the member can do in the organization)
+   rights integer not null,
+   -- relationships
+   foreign key (org) references users (id),
+   foreign key (member) references users (id)
+);
+
+-- engines
+drop table if exists engines;
+create table engines (
+   -- id of the engine
+   id integer not null primary key autoincrement,
+   -- engine name
+   name text not null,
+   -- extra metadata
+   meta text not null default '{}'
+);
+
+-- products
+drop table if exists products;
+create table products (
+   -- id of the product
+   id integer not null primary key autoincrement,
+   -- creation time
+   created_at timestamp not null default current_timestamp,
+   -- last update
+   updated_at timestamp,
+   -- engine id
+   engine integer not null,
+   -- supported platforms
+   platforms integer not null,
+   -- price
+   price integer not null,
+   -- title
+   title text not null,
+   -- description
+   body text not null,
+   -- short description
+   short text not null,
+   -- header image
+   header text not null,
+   -- extra metadata
+   meta text not null default '{}',
+   -- relationships
+   foreign key (engine) references engines (id)
+);
+
+-- tags
+drop table if exists tags;
+create table tags (
+   -- id of the tag
+   id integer not null primary key autoincrement,
+   -- tag name
+   name text not null,
+   -- extra metadata
+   meta text not null default '{}'
+);
+
+-- product tags
+drop table if exists product_tags;
+create table product_tags (
+   -- product tag id
+   id integer not null primary key autoincrement,
+   -- product id
+   product integer not null,
+   -- tag id
+   tag integer not null,
+   -- relationships
+   foreign key (product) references products (id),
+   foreign key (tag) references roles (id)
+);
+
+-- product level access control lists
+drop table if exists product_acls;
+create table product_acls (
+   -- product acl id
+   id integer not null primary key autoincrement,
+   -- product id
+   product integer not null,
+   -- user id
+   member integer not null,
+   -- ACL bits (what the member can do with the product)
+   rights integer not null,
+   -- relationships
+   foreign key (member) references users (id),
+   foreign key (product) references products (id)
+);
+
+-- roles
+drop table if exists roles;
+create table roles (
+   -- id of the role
+   id integer not null primary key autoincrement,
+   -- name of the role
+   name text not null
+);
+
+-- creators
+drop table if exists creators;
+create table creators (
+   -- id of the creator
+   id integer not null primary key autoincrement,
+   -- creation time
+   created_at timestamp not null default current_timestamp,
+   -- last update
+   updated_at timestamp,
+   -- name of the creator
+   name text not null,
+   -- extra metadata
+   meta text not null default '{}'
+);
+
+-- product creators
+drop table if exists product_creators;
+create table product_creators (
+   -- product creator id
+   id integer not null primary key autoincrement,
+   -- creator id
+   creator integer not null,
+   -- product id
+   product integer not null,
+   -- role id
+   role integer not null,
+   -- relationships
+   foreign key (creator) references creators (id),
+   foreign key (product) references products (id),
+   foreign key (role) references roles (id)
+);
+
+-- product reviews
+drop table if exists reviews;
+create table reviews (
+   -- review id
+   id integer not null primary key autoincrement,
+   -- creation time
+   created_at timestamp not null default current_timestamp,
+   -- last update
+   updated_at timestamp,
+   -- product id
+   product integer not null,
+   -- rating
+   rating integer not null,
+   -- title
+   title text not null,
+   -- body
+   body text not null,
+   -- relationships
+   foreign key (product) references products (id)
+);