feat: initial commit!

5 files changed, 326 insertions, 0 deletions

diff --git a/src/db/db.ts b/src/db/db.ts
new file mode 100644
index 0000000..c5cd07c
--- /dev/null
+++ b/src/db/db.ts
@@ -0,0 +1,21 @@
+import { drizzle } from "drizzle-orm/node-postgres";
+
+import * as schema from "./schema";
+import { logger } from "../lib/pino";
+import { redisCache } from "./redis/cache";
+import { redis } from "./redis/client";
+
+logger.info("Initializing database connection...");
+
+/** Main database instance with Redis caching and query logging */
+export const db = drizzle(process.env.DATABASE_URL!, {
+  schema,
+  cache: redisCache({ global: true, defaultTtl: 120, redis }),
+  logger: {
+    logQuery: (query, params) => {
+      logger.debug({ query, params }, "Database query executed");
+    },
+  },
+});
+
+logger.info("Database connection initialized");
diff --git a/src/db/redis/cache.ts b/src/db/redis/cache.ts
new file mode 100644
index 0000000..3385977
--- /dev/null
+++ b/src/db/redis/cache.ts
@@ -0,0 +1,181 @@
+import { Cache } from "drizzle-orm/cache/core";
+import type { CacheConfig } from "drizzle-orm/cache/core/types";
+import { Table, getTableName, is } from "drizzle-orm";
+import type { Redis } from "ioredis";
+
+/** Configuration options for Redis-based query caching */
+export interface RedisCacheOptions {
+  redis: Redis;
+  global?: boolean;
+  defaultTtl?: number;
+  prefix?: string;
+}
+
+/**
+ * Custom cache implementation using Redis for Drizzle ORM.
+ * Handles query result caching with automatic invalidation.
+ */
+export class RedisCache extends Cache {
+  private redis: Redis;
+  private globalStrategy: boolean;
+  private defaultTtl: number;
+  private prefix: string;
+  private usedTablesPerKey: Record<string, string[]> = {};
+
+  constructor(options: RedisCacheOptions) {
+    super();
+    if (!options || !options.redis) {
+      throw new Error("Redis client is required in RedisCacheOptions.redis");
+    }
+    this.redis = options.redis;
+    this.globalStrategy = options.global ?? false;
+    this.defaultTtl = options.defaultTtl ?? 60;
+    this.prefix = options.prefix ?? "drizzle";
+  }
+
+  private buildKey(key: string): string {
+    return `${this.prefix}:query:${key}`;
+  }
+
+  private buildTableKey(table: string): string {
+    return `${this.prefix}:tables:${table}`;
+  }
+
+  override strategy(): "explicit" | "all" {
+    return this.globalStrategy ? "all" : "explicit";
+  }
+
+  override async get(key: string): Promise<unknown[] | undefined> {
+    const fullKey = this.buildKey(key);
+    try {
+      const cached = await this.redis.get(fullKey);
+      if (cached !== null) {
+        return JSON.parse(cached) as unknown[];
+      }
+      return undefined;
+    } catch {
+      /* empty */
+    }
+    return undefined;
+  }
+
+  override async put(
+    key: string,
+    response: unknown,
+    tables: string[],
+    isTag: boolean,
+    config?: CacheConfig,
+  ): Promise<void> {
+    const fullKey = this.buildKey(key);
+    let ttlSeconds: number;
+    if (config?.px) {
+      ttlSeconds = Math.ceil(config.px / 1000);
+    } else if (config?.ex) {
+      ttlSeconds = config.ex;
+    } else {
+      ttlSeconds = this.defaultTtl;
+    }
+
+    try {
+      const serialized = JSON.stringify(response);
+      if (config?.keepTtl) {
+        await this.redis.set(fullKey, serialized, "KEEPTTL");
+      } else if (config?.exat) {
+        await this.redis.set(fullKey, serialized, "EXAT", config.exat);
+      } else if (config?.pxat) {
+        await this.redis.set(fullKey, serialized, "PXAT", config.pxat);
+      } else {
+        await this.redis.set(fullKey, serialized, "EX", ttlSeconds);
+      }
+      await this.trackTablesForKey(key, tables);
+    } catch {
+      /* empty */
+    }
+  }
+
+  private async trackTablesForKey(
+    key: string,
+    tables: string[],
+  ): Promise<void> {
+    const pipeline = this.redis.pipeline();
+    for (const table of tables) {
+      const tableKey = this.buildTableKey(table);
+      pipeline.sadd(tableKey, key);
+      pipeline.expire(tableKey, this.defaultTtl * 10);
+    }
+    for (const table of tables) {
+      if (!this.usedTablesPerKey[table]) {
+        this.usedTablesPerKey[table] = [];
+      }
+      if (!this.usedTablesPerKey[table].includes(key)) {
+        this.usedTablesPerKey[table].push(key);
+      }
+    }
+    await pipeline.exec();
+  }
+
+  override async onMutate(params: {
+    tags: string | string[];
+    tables: string | string[] | Table | Table[];
+  }): Promise<void> {
+    const tagsArray = params.tags
+      ? Array.isArray(params.tags)
+        ? params.tags
+        : [params.tags]
+      : [];
+
+    const tablesArray = params.tables
+      ? Array.isArray(params.tables)
+        ? params.tables
+        : [params.tables]
+      : [];
+
+    const keysToDelete = new Set<string>();
+
+    try {
+      for (const table of tablesArray) {
+        const tableName = is(table, Table)
+          ? getTableName(table)
+          : (table as string);
+        const tableKey = this.buildTableKey(tableName);
+        const keys = await this.redis.smembers(tableKey);
+        for (const key of keys) {
+          keysToDelete.add(key);
+        }
+        const memoryKeys = this.usedTablesPerKey[tableName] ?? [];
+        for (const key of memoryKeys) {
+          keysToDelete.add(key);
+        }
+      }
+
+      if (keysToDelete.size > 0 || tagsArray.length > 0) {
+        const pipeline = this.redis.pipeline();
+        for (const tag of tagsArray) {
+          const tagKey = this.buildKey(tag);
+          pipeline.del(tagKey);
+        }
+        for (const key of keysToDelete) {
+          const fullKey = this.buildKey(key);
+          pipeline.del(fullKey);
+        }
+        for (const table of tablesArray) {
+          const tableName = is(table, Table)
+            ? getTableName(table)
+            : (table as string);
+          const tableKey = this.buildTableKey(tableName);
+          pipeline.del(tableKey);
+          this.usedTablesPerKey[tableName] = [];
+        }
+        await pipeline.exec();
+      }
+    } catch {
+      /* empty */
+    }
+  }
+}
+
+export function redisCache(options: RedisCacheOptions): RedisCache {
+  return new RedisCache(options);
+}
+
+export default redisCache;
diff --git a/src/db/redis/client.ts b/src/db/redis/client.ts
new file mode 100644
index 0000000..55c0535
--- /dev/null
+++ b/src/db/redis/client.ts
@@ -0,0 +1,10 @@
+import Redis from "ioredis";
+import { connection } from "./info";
+
+/** Redis client instance for caching and session management */
+export const redis = new Redis({
+  host: connection.host,
+  port: connection.port,
+  password: connection.password,
+  db: connection.db,
+});
diff --git a/src/db/redis/info.ts b/src/db/redis/info.ts
new file mode 100644
index 0000000..f8ebba0
--- /dev/null
+++ b/src/db/redis/info.ts
@@ -0,0 +1,19 @@
+import { logger } from "../../lib/pino";
+
+const port = Number(process.env.REDIS_PORT);
+const db = Number(process.env.REDIS_DB || 0);
+
+logger.info(
+  { host: process.env.REDIS_HOST, port, db },
+  "Configuring Redis connection",
+);
+
+/** Redis connection configuration loaded from environment variables */
+export const connection = {
+  host: process.env.REDIS_HOST!,
+  port,
+  password: process.env.REDIS_PASSWORD!,
+  db,
+};
+
+logger.info("Redis connection configuration complete");
diff --git a/src/db/schema.ts b/src/db/schema.ts
new file mode 100644
index 0000000..4e89484
--- /dev/null
+++ b/src/db/schema.ts
@@ -0,0 +1,95 @@
+import { integer, pgTable, varchar, boolean, text } from "drizzle-orm/pg-core";
+import { defineRelations, sql } from "drizzle-orm";
+
+/** Parent user accounts with email auth and push notification tokens */
+export const users = pgTable("users", {
+  id: integer().primaryKey().generatedAlwaysAsIdentity(),
+  email: varchar({ length: 255 }).notNull().unique(),
+  password: varchar({ length: 255 }).notNull(),
+  emailVerified: boolean().default(false),
+  emailCode: varchar({ length: 6 }).notNull(),
+  pushTokens: text("push_tokens")
+    .array()
+    .default(sql`'{}'::text[]`),
+});
+
+/** Child devices linked to parent accounts for monitoring */
+export const linkedDevices = pgTable("linkedDevices", {
+  id: integer().primaryKey().generatedAlwaysAsIdentity(),
+  nickname: varchar({ length: 255 }).notNull().default("New Device"),
+  parentId: integer("parent_id").notNull(),
+  lastOnline: integer("last_online"),
+
+  devEnabled: boolean().default(false),
+});
+
+/** Safety and monitoring settings for each child device */
+export const deviceConfig = pgTable("deviceConfig", {
+  id: integer().primaryKey().generatedAlwaysAsIdentity(),
+  deviceId: integer("device_id").notNull().unique(),
+  disableBuddy: boolean("disable_buddy").notNull().default(false),
+  blockAdultSites: boolean("block_adult_sites").notNull().default(true),
+  familyLinkAntiCircumvention: boolean("family_link_anti_circumvention")
+    .notNull()
+    .default(false),
+  newContactAlerts: boolean("new_contact_alerts").notNull().default(true),
+  blockStrangers: boolean("block_strangers").notNull().default(false),
+  notifyDangerousMessages: boolean("notify_dangerous_messages")
+    .notNull()
+    .default(true),
+  notifyNewContactAdded: boolean("notify_new_contact_added")
+    .notNull()
+    .default(true),
+});
+
+/** Stores flagged messages and content alerts for parent review */
+export const alerts = pgTable("alerts", {
+  id: integer().primaryKey().generatedAlwaysAsIdentity(),
+  deviceId: integer("device_id").notNull(),
+  parentId: integer("parent_id").notNull(),
+  category: varchar({ length: 50 }),
+  title: varchar({ length: 255 }).notNull(),
+  message: text().notNull(),
+  summary: text().notNull(),
+  confidence: integer().notNull(),
+  packageName: varchar({ length: 255 }),
+  timestamp: integer().notNull(),
+  read: boolean().notNull().default(false),
+});
+
+export const relations = defineRelations(
+  { users, linkedDevices, deviceConfig, alerts },
+  (r) => ({
+    users: {
+      linkedDevices: r.many.linkedDevices(),
+      alerts: r.many.alerts(),
+    },
+    linkedDevices: {
+      parent: r.one.users({
+        from: r.linkedDevices.parentId,
+        to: r.users.id,
+      }),
+      config: r.one.deviceConfig({
+        from: r.linkedDevices.id,
+        to: r.deviceConfig.deviceId,
+      }),
+      alerts: r.many.alerts(),
+    },
+    deviceConfig: {
+      device: r.one.linkedDevices({
+        from: r.deviceConfig.deviceId,
+        to: r.linkedDevices.id,
+      }),
+    },
+    alerts: {
+      parent: r.one.users({
+        from: r.alerts.parentId,
+        to: r.users.id,
+      }),
+      device: r.one.linkedDevices({
+        from: r.alerts.deviceId,
+        to: r.linkedDevices.id,
+      }),
+    },
+  }),
+);