servnest/db/schema.sql

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "params" (
	"name"  TEXT NOT NULL UNIQUE,
	"value" TEXT NOT NULL,
	PRIMARY KEY("name")
);
INSERT INTO "params"("name", "value") VALUES('instance_bucket_tokens', '0');
INSERT INTO "params"("name", "value") VALUES('instance_bucket_last_update', '0');
INSERT INTO "params"("name", "value") VALUES('secret_key', '0');
INSERT INTO "params"("name", "value") VALUES('secret_key_last_change', '0');
INSERT INTO "params"("name", "value") VALUES('username_salt', '00000000000000000000000000000000'); -- Should be unique and secret ; generate one using `openssl rand -hex 16` ; can't be changed without breaking current accounts login
CREATE TABLE IF NOT EXISTS "users" (
	"id"                 TEXT    NOT NULL UNIQUE CHECK (LENGTH(id) = 64),
	"username"           TEXT    NOT NULL UNIQUE,
	"password"           TEXT    NOT NULL,
	"registration_date"  TEXT    NOT NULL,
	"bucket_tokens"      INTEGER NOT NULL,
	"bucket_last_update" INTEGER NOT NULL,
	"type"               TEXT    NOT NULL,
	"services"           TEXT    NOT NULL,
	"medium"             TEXT    NOT NULL DEFAULT "",
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "approval-keys" (
	"key"     TEXT    NOT NULL UNIQUE,
	PRIMARY KEY("key")
);
CREATE TABLE IF NOT EXISTS "registry" (
	"domain"       TEXT    NOT NULL UNIQUE,
	"username"     TEXT    NOT NULL,
	"creation"     TEXT    NOT NULL,
	PRIMARY KEY("domain"),
	FOREIGN KEY("username") REFERENCES "users"("id")
);
CREATE TABLE IF NOT EXISTS "registry-history" (
	"domain"       TEXT    NOT NULL,
	"creation"     TEXT    NOT NULL,
	"expiration"   TEXT    NOT NULL
);
CREATE TABLE IF NOT EXISTS "zones" (
	"zone"     TEXT    NOT NULL UNIQUE,
	"username" TEXT    NOT NULL,
	PRIMARY KEY("zone"),
	FOREIGN KEY("username") REFERENCES "users"("id")
);
CREATE TABLE IF NOT EXISTS "ns-syncs" (
	"username"    TEXT    NOT NULL,
	"source"      TEXT    NOT NULL,
	"destination" TEXT    NOT NULL UNIQUE,
	FOREIGN KEY("username") REFERENCES "users"("id"),
	FOREIGN KEY("destination") REFERENCES "zones"("zone")
);
CREATE TABLE IF NOT EXISTS "sites" (
	"username"      TEXT    NOT NULL,
	"site_dir"      TEXT    NOT NULL,
	"address"       TEXT    NOT NULL,
	"type"          TEXT    NOT NULL,
	"creation_date" TEXT    NOT NULL,
	UNIQUE("address", "type"),
	UNIQUE("username", "site_dir", "type"),
	PRIMARY KEY("address", "type"),
	FOREIGN KEY("username") REFERENCES "users"("id")
);
CREATE TABLE IF NOT EXISTS "ssh-keys" (
	"key"       TEXT    NOT NULL,
	"username"  TEXT    NOT NULL,
	"directory" TEXT    NOT NULL,
	UNIQUE("key", "username", "directory"),
	FOREIGN KEY("username") REFERENCES "users"("id")
);
COMMIT;