migrations

down.mysql.sql

drop table if exists `users_organizations`;
drop table if exists `users_organizations`;
drop table if exists `users_collections`;
drop table if exists `collections`;
drop table if exists `organizations`;
drop table if exists `attachments`;
drop table if exists `ciphers`;
drop table if exists `folders`;
drop table if exists `devices`;
drop table if exists `users`;

up.mysql.sql

CREATE TABLE `users` (
    `uuid` CHAR(36) NOT NULL PRIMARY KEY,
    `created_at` datetime not null,
    `updated_at` datetime not null,
    `email` varchar(255) not null,
    `name` varchar(255) not null,
    `password_hash` blob not null,
    `salt` blob not null,
    `password_iterations` integer not null,
    `password_hint` varchar(255),
    `private_key` text,
    `public_key` text,
    `totp_secret` text,
    `totp_recover` text,
    `security_stamp` text not null,
    `equivalent_domains` text not null,
    `excluded_globals` text not null,

    unique index `email_uniq` (`email`)
);

create table `devices` (
    `uuid` CHAR(36) not null primary key,
    `created_at` datetime not null,
    `updated_at` datetime not null,
    `user_uuid` CHAR(36) NOT NULL,
    `name` varchar(255) not null,
    `type` integer not null,
    `push_token` text, 
    `refresh_token` text not null,

    index `idx_user_uuid` (`user_uuid`),
    
    constraint `fk_devices_user_uuid` foreign key
        `idx_user_uuid`(`user_uuid`) references `users`(`uuid`)
        on update restrict
        on delete cascade
);


CREATE TABLE `folders` (
    `uuid` CHAR(36) NOT NULL PRIMARY KEY,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `user_uuid` CHAR(36) NOT NULL,
    `name` VARCHAR(255),

    INDEX `idx_user_uuid` (`user_uuid`),

    CONSTRAINT `fk_folders_user_uuid` FOREIGN KEY
        `idx_user_uuid`(`user_uuid`) REFERENCES `users` (`uuid`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
);

CREATE TABLE `ciphers` (
    `uuid` CHAR(36) NOT NULL PRIMARY KEY,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `user_uuid` CHAR(36) NOT NULL,
    `folder_uuid` CHAR(36) NOT NULL,
    `organization_uuid` CHAR(36),
    `type` INTEGER NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `notes` TEXT,
    `fields` TEXT,
    `data` TEXT NOT NULL,
    `favorite` TINYINT(1) NOT NULL,

    INDEX `idx_user_uuid`(`user_uuid`),
    INDEX `idx_folder_uuid`(`folder_uuid`),

    CONSTRAINT `fk_ciphers_user_uuid` FOREIGN KEY
        `idx_user_uuid`(`user_uuid`) REFERENCES `users`(`uuid`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE,

    CONSTRAINT `fk_ciphers_folder_uuid` FOREIGN KEY
        `idx_folder_uuid`(`folder_uuid`) REFERENCES `folders`(`uuid`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
    
);

CREATE TABLE `attachments` (
    `id` VARCHAR(512) NOT NULL PRIMARY KEY,
    `cipher_uuid` CHAR(36) NOT NULL,
    `file_name` VARCHAR(255) NOT NULL,
    `file_size` INTEGER NOT NULL,

    INDEX `idx_cipher_uuid`(`cipher_uuid`),

    CONSTRAINT `fk_attachments_cipher_uuid` FOREIGN KEY
        `idx_cipher_uuid`(`cipher_uuid`) REFERENCES `ciphers`(`uuid`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
);

CREATE TABLE `organizations` (
    `uuid` CHAR(36) NOT NULL PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `billing_email` VARCHAR(255) NOT NULL
);

CREATE TABLE `collections` (
    `uuid` CHAR(36) NOT NULL PRIMARY KEY,
    `org_uuid` CHAR(36) NOT NULL,
    `name` VARCHAR(255) NOT NULL,

    INDEX `idx_org_uuid`(`org_uuid`),

    CONSTRAINT `fk_collections_org_uuid` FOREIGN KEY
        `idx_org_uuid`(`org_uuid`) REFERENCES `organizations`(`uuid`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
);

CREATE TABLE `users_collections` (
    `user_uuid` CHAR(36) NOT NULL,
    `collection_uuid` CHAR(36) NOT NULL,

    PRIMARY KEY (`user_uuid`,`collection_uuid`),
    
    CONSTRAINT `fk_users_collections_user_uuid` FOREIGN KEY
        (`user_uuid`) REFERENCES `users`(`uuid`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE,

    CONSTRAINT `fk_users_collections_collection_uuid` FOREIGN KEY
        (`collection_uuid`) REFERENCES `collections`(`uuid`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
);

CREATE TABLE `users_organizations` (
    `uuid`       CHAR(36)    NOT NULL PRIMARY KEY,
    `user_uuid`  CHAR(36)    NOT NULL,
    `org_uuid`   CHAR(36)    NOT NULL,

    `access_all` TINYINT(1) NOT NULL,
    `key`        VARCHAR(255)    NOT NULL,
    `status`     INTEGER NOT NULL,
    `type`       INTEGER NOT NULL,

    UNIQUE INDEX `idx_uo_user_org_uniq`(`user_uuid`,`org_uuid`),

    FOREIGN KEY (`user_uuid`) REFERENCES `users`(`uuid`) ON UPDATE RESTRICT ON DELETE CASCADE,
    FOREIGN KEY (`org_uuid`) REFERENCES `organizations`(`uuid`) ON UPDATE RESTRICT ON DELETE CASCADE
);