init.sql

DROP DATABASE IF EXISTS `openaccounting`;

CREATE DATABASE openaccounting CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

use openaccounting;

CREATE TABLE org (id BINARY(16) NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, currency VARCHAR(10) NOT NULL, `precision` INT NOT NULL, timezone VARCHAR(100) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE user (id BINARY(16) NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, firstName VARCHAR(50) NOT NULL, lastName VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, passwordHash VARCHAR(100) NOT NULL, agreeToTerms BOOLEAN NOT NULL, passwordReset VARCHAR(32) NOT NULL, emailVerified BOOLEAN NOT NULL, emailVerifyCode VARCHAR(32) NOT NULL, signupSource VARCHAR(100) NOT NULL, UNIQUE(email), PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE userorg (id INT UNSIGNED NOT NULL AUTO_INCREMENT, userId BINARY(16) NOT NULL, orgId BINARY(16) NOT NULL, admin BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE token (id BINARY(16) NOT NULL, name VARCHAR(100), userOrgId INT UNSIGNED NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE account (id BINARY(16) NOT NULL, orgId BINARY(16) NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, parent BINARY(16) NOT NULL, currency VARCHAR(10) NOT NULL, `precision` INT NOT NULL, debitBalance BOOLEAN NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE transaction (id BINARY(16) NOT NULL, orgId BINARY(16) NOT NULL, userId BINARY(16) NOT NULL, date BIGINT UNSIGNED NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, description VARCHAR(300) NOT NULL, data TEXT NOT NULL, deleted BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE split (id INT UNSIGNED NOT NULL AUTO_INCREMENT, transactionId BINARY(16) NOT NULL, accountId BINARY(16) NOT NULL, date BIGINT UNSIGNED NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, amount BIGINT NOT NULL, nativeAmount BIGINT NOT NULL, deleted BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE balance (id INT UNSIGNED NOT NULL AUTO_INCREMENT, date BIGINT UNSIGNED NOT NULL, accountId BINARY(16) NOT NULL, amount BIGINT NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE permission (id BINARY(16) NOT NULL, userId BINARY(16), tokenId BINARY(16), orgId BINARY(16) NOT NULL, accountId BINARY(16) NOT NULL, type INT UNSIGNED NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE price (id BINARY(16) NOT NULL, orgId BINARY(16) NOT NULL, currency VARCHAR(10) NOT NULL, date BIGINT UNSIGNED NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, price DOUBLE UNSIGNED NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE session (id BINARY(16) NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, userId BINARY(16) NOT NULL, `terminated` BIGINT UNSIGNED, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE apikey (id BINARY(16) NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, userId BINARY(16) NOT NULL, label VARCHAR(300) NOT NULL, deleted BIGINT UNSIGNED, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE invite (id VARCHAR(32) NOT NULL, orgId BINARY(16) NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, email VARCHAR(100) NOT NULL, accepted BOOLEAN NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE budgetitem (id INT UNSIGNED NOT NULL AUTO_INCREMENT, orgId BINARY(16) NOT NULL, accountId BINARY(16) NOT NULL, inserted BIGINT UNSIGNED NOT NULL, amount BIGINT NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;CREATE INDEX account_orgId_index ON account (orgId);
CREATE INDEX split_accountId_index ON split (accountId);
CREATE INDEX split_transactionId_index ON split (transactionId);
CREATE INDEX split_date_index ON split (date);
CREATE INDEX split_updated_index ON split (updated);
CREATE INDEX budgetitem_orgId_index ON budgetitem (orgId);