27 lines
1.1 KiB
SQL
27 lines
1.1 KiB
SQL
CREATE EXTENSION citext;
|
|
CREATE DOMAIN email AS citext
|
|
CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );
|
|
CREATE TABLE "users" (
|
|
"id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
"email" email UNIQUE NOT NULL,
|
|
"full_name" TEXT NOT NULL,
|
|
"password" CHARACTER VARYING (1000),
|
|
"active" BOOLEAN NOT NULL
|
|
);
|
|
CREATE TABLE "roles" (
|
|
"id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
"name" TEXT NOT NULL UNIQUE
|
|
);
|
|
CREATE TABLE "user_roles" (
|
|
"user_id" UUID NOT NULL REFERENCES "users" ("id") ON DELETE CASCADE,
|
|
"role_name" TEXT NOT NULL REFERENCES "roles" ("name") ON DELETE NO ACTION
|
|
);
|
|
CREATE TABLE "user_tokens" (
|
|
"id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
"user_id" UUID NOT NULL,
|
|
"token" CHARACTER VARYING (1000) NOT NULL,
|
|
"insert_time" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "user_tokens_user_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE
|
|
);
|
|
INSERT INTO "roles" ("name") VALUES ('admin');
|