slices / packages / session / src / adapters / postgres.ts

import type { SessionAdapter, SessionData } from "../types.ts";
import { Client } from "pg";

interface SessionTable {
  session_id: string;
  user_id: string;
  handle: string | null;
  is_authenticated: boolean;
  data: string | null;
  created_at: Date;
  expires_at: Date;
  last_accessed_at: Date;
}

export class PostgresAdapter implements SessionAdapter {
  private connectionString: string;

  constructor(connectionString: string) {
    this.connectionString = connectionString;
  }

  // Initialize the sessions table
  async initialize(): Promise<void> {
    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();

      await client.query(`
        CREATE TABLE IF NOT EXISTS sessions (
          session_id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL,
          handle TEXT,
          is_authenticated BOOLEAN NOT NULL DEFAULT true,
          data JSONB,
          created_at BIGINT NOT NULL,
          expires_at BIGINT NOT NULL,
          last_accessed_at BIGINT NOT NULL
        )
      `);

      // Index for cleanup operations
      await client.query(`
        CREATE INDEX IF NOT EXISTS idx_sessions_expires_at
        ON sessions(expires_at)
      `);

      // Index for user lookups
      await client.query(`
        CREATE INDEX IF NOT EXISTS idx_sessions_user_id
        ON sessions(user_id)
      `);
    } finally {
      await client.end();
    }
  }

  async get(sessionId: string): Promise<SessionData | null> {
    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();

      const result = await client.query(
        "SELECT * FROM sessions WHERE session_id = $1",
        [sessionId]
      );

      if (result.rows.length === 0) return null;

      return this.rowToSessionData(result.rows[0]);
    } finally {
      await client.end();
    }
  }

  async set(sessionId: string, data: SessionData): Promise<void> {
    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();

      await client.query(
        `
        INSERT INTO sessions 
        (session_id, user_id, handle, is_authenticated, data, created_at, expires_at, last_accessed_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
        ON CONFLICT (session_id) 
        DO UPDATE SET
          user_id = $2,
          handle = $3,
          is_authenticated = $4,
          data = $5,
          created_at = $6,
          expires_at = $7,
          last_accessed_at = $8
      `,
        [
          sessionId,
          data.userId,
          data.handle || null,
          data.isAuthenticated,
          data.data ? JSON.stringify(data.data) : null,
          data.createdAt,
          data.expiresAt,
          data.lastAccessedAt,
        ]
      );
    } finally {
      await client.end();
    }
  }

  async update(
    sessionId: string,
    updates: Partial<SessionData>
  ): Promise<boolean> {
    const setParts: string[] = [];
    const values: (string | number | boolean | null)[] = [];
    let paramIndex = 1;

    if (updates.userId !== undefined) {
      setParts.push(`user_id = $${paramIndex++}`);
      values.push(updates.userId);
    }

    if (updates.handle !== undefined) {
      setParts.push(`handle = $${paramIndex++}`);
      values.push(updates.handle);
    }

    if (updates.isAuthenticated !== undefined) {
      setParts.push(`is_authenticated = $${paramIndex++}`);
      values.push(updates.isAuthenticated);
    }

    if (updates.data !== undefined) {
      setParts.push(`data = $${paramIndex++}`);
      values.push(updates.data ? JSON.stringify(updates.data) : null);
    }

    if (updates.expiresAt !== undefined) {
      setParts.push(`expires_at = $${paramIndex++}`);
      values.push(updates.expiresAt);
    }

    if (updates.lastAccessedAt !== undefined) {
      setParts.push(`last_accessed_at = $${paramIndex++}`);
      values.push(updates.lastAccessedAt);
    }

    if (setParts.length === 0) return false;

    values.push(sessionId);

    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();

      const result = await client.query(
        `
        UPDATE sessions 
        SET ${setParts.join(", ")}
        WHERE session_id = $${paramIndex}
      `,
        values
      );

      return result.rowCount !== null && result.rowCount > 0;
    } finally {
      await client.end();
    }
  }

  async delete(sessionId: string): Promise<void> {
    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();
      await client.query("DELETE FROM sessions WHERE session_id = $1", [
        sessionId,
      ]);
    } finally {
      await client.end();
    }
  }

  async cleanup(expiresBeforeMs: number): Promise<number> {
    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();

      const result = await client.query(
        "DELETE FROM sessions WHERE expires_at < $1",
        [expiresBeforeMs]
      );

      return result.rowCount || 0;
    } finally {
      await client.end();
    }
  }

  async exists(sessionId: string): Promise<boolean> {
    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();

      const result = await client.query(
        "SELECT 1 FROM sessions WHERE session_id = $1 LIMIT 1",
        [sessionId]
      );

      return result.rows.length > 0;
    } finally {
      await client.end();
    }
  }

  private rowToSessionData(row: SessionTable): SessionData {
    return {
      sessionId: row.session_id,
      userId: row.user_id,
      handle: row.handle || undefined,
      isAuthenticated: row.is_authenticated,
      data: row.data ? JSON.parse(row.data) : undefined,
      createdAt: row.created_at.getTime(),
      expiresAt: row.expires_at.getTime(),
      lastAccessedAt: row.last_accessed_at.getTime(),
    };
  }

  // PostgreSQL-specific methods
  async getSessionsByUser(userId: string): Promise<SessionData[]> {
    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();

      const result = await client.query(
        "SELECT * FROM sessions WHERE user_id = $1",
        [userId]
      );

      return result.rows.map((row: Record<string, unknown>) =>
        this.rowToSessionData(row as unknown as SessionTable)
      );
    } finally {
      await client.end();
    }
  }

  async vacuum(): Promise<void> {
    const client = new Client({ connectionString: this.connectionString });

    try {
      await client.connect();
      await client.query("VACUUM ANALYZE sessions");
    } finally {
      await client.end();
    }
  }
}