- add: database schema

- add: ability to delete keys from RedisCache
- update: docker compose to init the database with the schema

database-service/Cargo.toml

@@ -11,6 +11,7 @@ consul = []
 tokio = { version = "1.41.1", features = ["full"] }
 sqlx = { version = "0.7", features = ["postgres", "runtime-tokio-native-tls", "chrono"] }
 tonic = "0.12.3"
+chrono = { version = "0.4.39", features = ["serde"] }
 serde = { version = "1.0", features = ["derive"] }
 dotenv = "0.15"
 tracing = "0.1"
@@ -27,4 +28,4 @@ warp = "0.3.7"
 utils = { path = "../utils" }
 
 [build-dependencies]
-tonic-build = "0.12.3"
+tonic-build = "0.12.3"

database-service/src/redis_cache.rs

@@ -16,7 +16,8 @@ pub trait Cache {
         &self,
         key: &String,
     ) -> Result<Option<T>, redis::RedisError>;
-
+    
+    async fn delete(&mut self, key: &str) -> redis::RedisResult<()>;
 }
 
 pub struct RedisCache {
@@ -77,4 +78,16 @@ impl Cache for RedisCache {
             Ok(None)
         }
     }
+
+    async fn delete(&mut self, key: &str) -> redis::RedisResult<()> {
+        let mut conn = self.pool.get().await
+            .map_err(|err| {
+                redis::RedisError::from((
+                    redis::ErrorKind::IoError,
+                    "Failed to get Redis connection",
+                    format!("{:?}", err),
+                ))
+            })?;
+        conn.del(key).await
+    }
 }

docker-compose.yml

@@ -87,6 +87,7 @@
       - "5432:5432"
     volumes:
       - db_data:/var/lib/postgresql/data
+      - ./sql/schema.sql:/docker-entrypoint-initdb.d/schema.sql:ro
 
   consul:
     image: consul:1.15.4

sql/schema.sql

@@ -0,0 +1,108 @@
+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,
+    level      smallint  default 1,
+    experience bigint    default 0,
+    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;
+