107 lines
2.4 KiB
SQL
107 lines
2.4 KiB
SQL
create table users
|
|
(
|
|
id serial
|
|
primary key,
|
|
username varchar(50) not null
|
|
unique,
|
|
email varchar(100) not null
|
|
unique,
|
|
hashed_password text not null,
|
|
roles text[],
|
|
created_at timestamp default CURRENT_TIMESTAMP not null,
|
|
updated_at timestamp default CURRENT_TIMESTAMP not null
|
|
);
|
|
|
|
alter table users
|
|
owner to osirose;
|
|
|
|
create table refresh_tokens
|
|
(
|
|
id serial
|
|
primary key,
|
|
user_id integer not null
|
|
references users
|
|
on delete cascade,
|
|
token text not null,
|
|
created_at timestamp default CURRENT_TIMESTAMP,
|
|
expires_at timestamp not null
|
|
);
|
|
|
|
alter table refresh_tokens
|
|
owner to osirose;
|
|
|
|
create table characters
|
|
(
|
|
id serial
|
|
primary key,
|
|
user_id integer not null
|
|
references users
|
|
on delete cascade,
|
|
is_active boolean default true,
|
|
name varchar(50) not null,
|
|
inventory jsonb default '{}'::jsonb,
|
|
stats jsonb default '{}'::jsonb,
|
|
looks jsonb default '{}'::jsonb,
|
|
position jsonb default '{}'::jsonb,
|
|
created_at timestamp default CURRENT_TIMESTAMP,
|
|
updated_at timestamp default CURRENT_TIMESTAMP,
|
|
deleted_at timestamp
|
|
);
|
|
|
|
alter table characters
|
|
owner to osirose;
|
|
|
|
create table items
|
|
(
|
|
id serial
|
|
primary key,
|
|
name varchar(50) not null,
|
|
description text,
|
|
rarity varchar(20),
|
|
stats jsonb default '{}'::jsonb
|
|
);
|
|
|
|
alter table items
|
|
owner to osirose;
|
|
|
|
create table character_items
|
|
(
|
|
id serial
|
|
primary key,
|
|
character_id integer not null
|
|
references characters
|
|
on delete cascade,
|
|
item_id integer not null
|
|
references items
|
|
on delete cascade,
|
|
quantity integer default 1
|
|
);
|
|
|
|
alter table character_items
|
|
owner to osirose;
|
|
|
|
create table events
|
|
(
|
|
id serial
|
|
primary key,
|
|
event_type varchar(50) not null,
|
|
event_data jsonb not null,
|
|
created_at timestamp default CURRENT_TIMESTAMP
|
|
);
|
|
|
|
alter table events
|
|
owner to osirose;
|
|
|
|
create table password_resets
|
|
(
|
|
id serial
|
|
primary key,
|
|
email text not null,
|
|
reset_token text not null,
|
|
expires_at timestamp not null
|
|
);
|
|
|
|
alter table password_resets
|
|
owner to osirose;
|
|
|