helios-server-rs/migrations/2024-07-07-151037_base_schema/up.sql

-- Your SQL goes here
CREATE TABLE "categories" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "book_id" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "level" INT NOT NULL DEFAULT 0,
    "parent_category_id" BIGINT NOT NULL DEFAULT 0,
    "op_id" BIGINT NOT NULL DEFAULT 0,
    "is_delete" BOOLEAN NOT NULL DEFAULT FALSE,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "tags" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "book_id" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "level" INT NOT NULL DEFAULT 0,
    "parent_tag_id" BIGINT NOT NULL DEFAULT 0,
    "op_id" BIGINT NOT NULL DEFAULT 0,
    "is_delete" BOOLEAN NOT NULL DEFAULT FALSE,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "books" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "op_id" BIGINT NOT NULL DEFAULT 0,
    "is_delete" BOOLEAN NOT NULL DEFAULT FALSE,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "transactions" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "book_id" BIGINT NOT NULL,
    "description" TEXT NOT NULL,
    "category_id" BIGINT NOT NULL,
    "op_id" BIGINT NOT NULL DEFAULT 0,
    "is_delete" BOOLEAN NOT NULL DEFAULT FALSE,
    "time" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "transaction_tag_rels" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "transaction_id" BIGINT NOT NULL,
    "tag_id" BIGINT NOT NULL,
    "op_id" BIGINT NOT NULL DEFAULT 0,
    "is_delete" BOOLEAN NOT NULL DEFAULT FALSE,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "accounts" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "account_type" BIGINT NOT NULL DEFAULT 0,
    "op_id" BIGINT NOT NULL DEFAULT 0,
    "is_delete" BOOLEAN NOT NULL DEFAULT FALSE,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "amounts" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "account_id" BIGINT NOT NULL,
    "transaction_id" BIGINT NOT NULL,
    "value" BIGINT NOT NULL DEFAULT 0,
    "expo" BIGINT NOT NULL DEFAULT 5,
    "currency" TEXT NOT NULL DEFAULT '',
    "op_id" BIGINT NOT NULL DEFAULT 0,
    "is_delete" BOOLEAN NOT NULL DEFAULT FALSE,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "users" (
    "id" BIGSERIAL PRIMARY KEY,
    "username" TEXT NOT NULL UNIQUE,
    "password" TEXT NOT NULL,
    "mail" TEXT NOT NULL,
    "is_delete" BOOLEAN NOT NULL DEFAULT FALSE,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "operations" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "entity_type" BIGINT NOT NULL,
    "entity_id" BIGINT NOT NULL,
    "action" BIGINT NOT NULL,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp,
    "update_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TABLE "operation_snapshots" (
    "id" BIGSERIAL PRIMARY KEY,
    "uid" BIGINT NOT NULL,
    "max_op_id" BIGINT NOT NULL,
    "create_at" TIMESTAMP NOT NULL DEFAULT current_timestamp
);