summaryrefslogtreecommitdiff
path: root/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/db')
-rw-r--r--src/db/db.ts21
-rw-r--r--src/db/redis/cache.ts181
-rw-r--r--src/db/redis/client.ts10
-rw-r--r--src/db/redis/info.ts19
-rw-r--r--src/db/schema.ts95
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,
+ }),
+ },
+ }),
+);
generated by cgit v1.2.3 (git 2.25.1) at 2026-04-10 12:55:53 +0000