CREATE TABLE users (
    id UUID PRIMARY KEY default uuid_generate_v4(),
    username VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE conversations (
    id UUID PRIMARY KEY default uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    owner_user_id UUID REFERENCES users(id) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE user_conversation (
    id UUID PRIMARY KEY default uuid_generate_v4(),
    user_id UUID REFERENCES users(id),
    conversation_id UUID REFERENCES conversations(id)
);

CREATE TABLE messages (
    id UUID PRIMARY KEY default uuid_generate_v4(),
    conversation_id UUID REFERENCES conversations(id),
    user_id UUID REFERENCES users(id),
    message TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL
);

npm init

npm install @supabase/supabase-js body-parser dotenv express socket.io
npm install -D @types/express @types/node @typescript-eslint/eslint-plugin @typescript-eslint/parser eslint nodemon ts-node typescript

"main": "index.ts"

"scripts": {
    "start": "nodemon -r dotenv/config src/index.ts",
    "lint": "eslint . --fix",
    "test": "echo \"Error: no test specified\" && exit 1"
  },

{
  "name": "supabase-node-chat-backend",
  "version": "1.0.0",
  "description": "Node backend for a chat application, using Supabase",
  "main": "index.ts",
  "scripts": {
    "start": "nodemon -r dotenv/config src/index.ts",
    "lint": "eslint . --fix",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "repository": {
    "type": "git",
    "url": "git+https://github.com/nsmet/supabase-node-chat-backend.git"
  },
  "keywords": [
    "supabase",
    "node",
    "chat"
  ],
  "author": "Nick Smet",
  "license": "ISC",
  "bugs": {
    "url": "https://github.com/nsmet/supabase-node-chat-backend/issues"
  },
  "homepage": "https://github.com/nsmet/supabase-node-chat-backend#readme",
  "dependencies": {
    "@supabase/supabase-js": "^2.4.1",
    "body-parser": "^1.20.1",
    "dotenv": "^16.0.3",
    "express": "^4.18.2",
    "socket.io": "^4.5.4"
  },
  "devDependencies": {
    "@types/express": "^4.17.15",
    "@types/node": "^18.11.18",
    "@typescript-eslint/eslint-plugin": "^5.48.2",
    "@typescript-eslint/parser": "^5.48.2",
    "eslint": "^8.32.0",
    "nodemon": "^2.0.20",
    "ts-node": "^10.9.1",
    "typescript": "^4.9.4"
  }
}

SUPABASE_PROJECT_URL=
SUPABASE_PUBLIC_ANON=

dist

module.exports = {
  parser: "@typescript-eslint/parser",
  parserOptions: {
    ecmaVersion: "latest", // Allows the use of modern ECMAScript features
    sourceType: "module", // Allows for the use of imports
  },
  extends: ["plugin:@typescript-eslint/recommended"], // Uses the linting rules from @typescript-eslint/eslint-plugin
  env: {
    node: true, // Enable Node.js global variables
  },
  rules: {
    'no-console': 'off',
    'import/prefer-default-export': 'off',
    '@typescript-eslint/no-unused-vars': 'warn',
  },
};

# Logs
logs
*.log
npm-debug.log*
yarn-debug.log*
yarn-error.log*
lerna-debug.log*

# Dependency directories
node_modules/

# dotenv environment variables file
.env
.env.test
dist

{
  "compilerOptions": {
    /* Visit https://aka.ms/tsconfig to read more about this file */

    /* Projects */
    // "incremental": true,                              /* Save .tsbuildinfo files to allow for incremental compilation of projects. */
    // "composite": true,                                /* Enable constraints that allow a TypeScript project to be used with project references. */
    // "tsBuildInfoFile": "./.tsbuildinfo",              /* Specify the path to .tsbuildinfo incremental compilation file. */
    // "disableSourceOfProjectReferenceRedirect": true,  /* Disable preferring source files instead of declaration files when referencing composite projects. */
    // "disableSolutionSearching": true,                 /* Opt a project out of multi-project reference checking when editing. */
    // "disableReferencedProjectLoad": true,             /* Reduce the number of projects loaded automatically by TypeScript. */

    /* Language and Environment */
    "target": "es2016",                                  /* Set the JavaScript language version for emitted JavaScript and include compatible library declarations. */
    "lib": ["es6"],                                      /* Specify a set of bundled library declaration files that describe the target runtime environment. */
    // "jsx": "preserve",                                /* Specify what JSX code is generated. */
    // "experimentalDecorators": true,                   /* Enable experimental support for TC39 stage 2 draft decorators. */
    // "emitDecoratorMetadata": true,                    /* Emit design-type metadata for decorated declarations in source files. */
    // "jsxFactory": "",                                 /* Specify the JSX factory function used when targeting React JSX emit, e.g. 'React.createElement' or 'h'. */
    // "jsxFragmentFactory": "",                         /* Specify the JSX Fragment reference used for fragments when targeting React JSX emit e.g. 'React.Fragment' or 'Fragment'. */
    // "jsxImportSource": "",                            /* Specify module specifier used to import the JSX factory functions when using 'jsx: react-jsx*'. */
    // "reactNamespace": "",                             /* Specify the object invoked for 'createElement'. This only applies when targeting 'react' JSX emit. */
    // "noLib": true,                                    /* Disable including any library files, including the default lib.d.ts. */
    // "useDefineForClassFields": true,                  /* Emit ECMAScript-standard-compliant class fields. */
    // "moduleDetection": "auto",                        /* Control what method is used to detect module-format JS files. */

    /* Modules */
    "module": "commonjs",                                /* Specify what module code is generated. */
    "rootDir": "src",                                    /* Specify the root folder within your source files. */
    // "moduleResolution": "node",                       /* Specify how TypeScript looks up a file from a given module specifier. */
    // "baseUrl": "./",                                  /* Specify the base directory to resolve non-relative module names. */
    // "paths": {},                                      /* Specify a set of entries that re-map imports to additional lookup locations. */
    // "rootDirs": [],                                   /* Allow multiple folders to be treated as one when resolving modules. */
    // "typeRoots": [],                                  /* Specify multiple folders that act like './node_modules/@types'. */
    // "types": [],                                      /* Specify type package names to be included without being referenced in a source file. */
    // "allowUmdGlobalAccess": true,                     /* Allow accessing UMD globals from modules. */
    // "moduleSuffixes": [],                             /* List of file name suffixes to search when resolving a module. */
    "resolveJsonModule": true,                           /* Enable importing .json files. */
    // "noResolve": true,                                /* Disallow 'import's, 'require's or '<reference>'s from expanding the number of files TypeScript should add to a project. */

    /* JavaScript Support */
    "allowJs": true,                                     /* Allow JavaScript files to be a part of your program. Use the 'checkJS' option to get errors from these files. */
    // "checkJs": true,                                  /* Enable error reporting in type-checked JavaScript files. */
    // "maxNodeModuleJsDepth": 1,                        /* Specify the maximum folder depth used for checking JavaScript files from 'node_modules'. Only applicable with 'allowJs'. */

    /* Emit */
    // "declaration": true,                              /* Generate .d.ts files from TypeScript and JavaScript files in your project. */
    // "declarationMap": true,                           /* Create sourcemaps for d.ts files. */
    // "emitDeclarationOnly": true,                      /* Only output d.ts files and not JavaScript files. */
    // "sourceMap": true,                                /* Create source map files for emitted JavaScript files. */
    // "outFile": "./",                                  /* Specify a file that bundles all outputs into one JavaScript file. If 'declaration' is true, also designates a file that bundles all .d.ts output. */
    "outDir": "build",                                   /* Specify an output folder for all emitted files. */
    // "removeComments": true,                           /* Disable emitting comments. */
    // "noEmit": true,                                   /* Disable emitting files from a compilation. */
    // "importHelpers": true,                            /* Allow importing helper functions from tslib once per project, instead of including them per-file. */
    // "importsNotUsedAsValues": "remove",               /* Specify emit/checking behavior for imports that are only used for types. */
    // "downlevelIteration": true,                       /* Emit more compliant, but verbose and less performant JavaScript for iteration. */
    // "sourceRoot": "",                                 /* Specify the root path for debuggers to find the reference source code. */
    // "mapRoot": "",                                    /* Specify the location where debugger should locate map files instead of generated locations. */
    // "inlineSourceMap": true,                          /* Include sourcemap files inside the emitted JavaScript. */
    // "inlineSources": true,                            /* Include source code in the sourcemaps inside the emitted JavaScript. */
    // "emitBOM": true,                                  /* Emit a UTF-8 Byte Order Mark (BOM) in the beginning of output files. */
    // "newLine": "crlf",                                /* Set the newline character for emitting files. */
    // "stripInternal": true,                            /* Disable emitting declarations that have '@internal' in their JSDoc comments. */
    // "noEmitHelpers": true,                            /* Disable generating custom helper functions like '__extends' in compiled output. */
    // "noEmitOnError": true,                            /* Disable emitting files if any type checking errors are reported. */
    // "preserveConstEnums": true,                       /* Disable erasing 'const enum' declarations in generated code. */
    // "declarationDir": "./",                           /* Specify the output directory for generated declaration files. */
    // "preserveValueImports": true,                     /* Preserve unused imported values in the JavaScript output that would otherwise be removed. */

    /* Interop Constraints */
    // "isolatedModules": true,                          /* Ensure that each file can be safely transpiled without relying on other imports. */
    // "allowSyntheticDefaultImports": true,             /* Allow 'import x from y' when a module doesn't have a default export. */
    "esModuleInterop": true,                             /* Emit additional JavaScript to ease support for importing CommonJS modules. This enables 'allowSyntheticDefaultImports' for type compatibility. */
    // "preserveSymlinks": true,                         /* Disable resolving symlinks to their realpath. This correlates to the same flag in node. */
    "forceConsistentCasingInFileNames": true,            /* Ensure that casing is correct in imports. */

    /* Type Checking */
    "strict": true,                                      /* Enable all strict type-checking options. */
    "noImplicitAny": true,                               /* Enable error reporting for expressions and declarations with an implied 'any' type. */
    // "strictNullChecks": true,                         /* When type checking, take into account 'null' and 'undefined'. */
    // "strictFunctionTypes": true,                      /* When assigning functions, check to ensure parameters and the return values are subtype-compatible. */
    // "strictBindCallApply": true,                      /* Check that the arguments for 'bind', 'call', and 'apply' methods match the original function. */
    // "strictPropertyInitialization": true,             /* Check for class properties that are declared but not set in the constructor. */
    // "noImplicitThis": true,                           /* Enable error reporting when 'this' is given the type 'any'. */
    // "useUnknownInCatchVariables": true,               /* Default catch clause variables as 'unknown' instead of 'any'. */
    // "alwaysStrict": true,                             /* Ensure 'use strict' is always emitted. */
    // "noUnusedLocals": true,                           /* Enable error reporting when local variables aren't read. */
    // "noUnusedParameters": true,                       /* Raise an error when a function parameter isn't read. */
    // "exactOptionalPropertyTypes": true,               /* Interpret optional property types as written, rather than adding 'undefined'. */
    // "noImplicitReturns": true,                        /* Enable error reporting for codepaths that do not explicitly return in a function. */
    // "noFallthroughCasesInSwitch": true,               /* Enable error reporting for fallthrough cases in switch statements. */
    // "noUncheckedIndexedAccess": true,                 /* Add 'undefined' to a type when accessed using an index. */
    // "noImplicitOverride": true,                       /* Ensure overriding members in derived classes are marked with an override modifier. */
    // "noPropertyAccessFromIndexSignature": true,       /* Enforces using indexed accessors for keys declared using an indexed type. */
    // "allowUnusedLabels": true,                        /* Disable error reporting for unused labels. */
    // "allowUnreachableCode": true,                     /* Disable error reporting for unreachable code. */

    /* Completeness */
    // "skipDefaultLibCheck": true,                      /* Skip type checking .d.ts files that are included with TypeScript. */
    "skipLibCheck": true                                 /* Skip type checking all .d.ts files. */
  }
}

import express from "express";
import bodyParser from "body-parser";
import cors from 'cors';

const app = express();

app.use(bodyParser.urlencoded({ extended: false}));
app.use(bodyParser.json());
app.use(cors())

app.get("/", function (req, res) {
    res.send("Hello World");
});
app.listen(3000);

npm run start 

// First authenticate with Supabase
npx supabase login

// Generate the types file
npx supabase gen types typescript --project-id "YOUR_PROJECT_ID" --schema public > src/supabase.types.ts

import { Socket } from "socket.io"

export interface TypedRequestBody<T> extends Express.Request {
    body: T
  }

export interface TypedRequestQuery<T> extends Express.Request {
    query: T
}

export interface TypedRequestQueryWithBodyAndParams<Params, ReqBody> extends Express.Request {
    body: ReqBody,
    params: Params
}

export interface TypedRequestQueryAndParams<Params, Query> extends Express.Request {
    params: Params
    query: Query,
}

export interface TypedRequestQueryWithParams<Params> extends Express.Request {
    params: Params
}

export interface User {
    id: string;
    username: string;
    created_at: string;
}

export interface Conversation {
    id: string;
    name: string;
    user_owner_id: string;
    created_at: string;
}

export interface Message {
    id: string;
    user_id: string;
    message: string;
    created_at: string;
}

export interface UserConversation {
    user_id: string;
    conversation_id: string;
}

export interface SocketConnectedUsers {
    [key: string]: {
        socketId: string;
        socket: Socket;
        user: User;
    }
}

export interface SocketSocketIdUserId {
    [key: string]: string
}

import { createClient } from '@supabase/supabase-js';
import { Database } from '../supabase.types';

const supabase = createClient<Database>(process.env.SUPABASE_PROJECT_URL as string, process.env.SUPABASE_PUBLIC_ANON as string);

export default supabase;

import { Response } from "express"
import supabase from "../utils/supabase"
import { TypedRequestBody, TypedRequestQuery } from "../types"

export const createUser = async function (req: TypedRequestBody<{username: string}>, res: Response) {
    const { data, error } = await supabase
        .from('users')
        .upsert({ 
            username: req.body.username,
            created_at: ((new Date()).toISOString()).toLocaleString()
        })
        .select()

    if (error) {
        res.send(500)
    } else {
        res.send(data[0])
    }
}

export const searchUsers = async function (req: TypedRequestQuery<{user_id: string, q: string}>, res: Response) {

    let query = supabase
      .from('users')
      .select();

    if (req.query.q) {
        query = query.like('username', `%${req.query.q}%`)
    }

    query = query.neq('id', req.query.user_id)
    .limit(50);

    const { data, error } = await query;

    if (error) {
        res.send(500)
    } else {
        res.send(data)
    }
}

// Add the import 
import { createUser, searchUsers } from './controllers/user.controller';

// Add the user endpoints
app.post("/users/create", createUser);
app.get("/users/search", searchUsers);

import { Response } from "express"
import supabase from "../utils/supabase"
import { 
    TypedRequestBody, 
    TypedRequestQuery, 
    TypedRequestQueryWithBodyAndParams, 
    TypedRequestQueryAndParams,
    User,
    Message
} from '../types';

export const getAllConversations = async function (req: TypedRequestQuery<{user_id: string}>, res: Response) {
    // get all conversations this user is attached to 
    const paticipatingConversationIds = await supabase
        .from('user_conversation')
        .select('conversation_id')
        .eq('user_id', req.query.user_id)

    if (!paticipatingConversationIds.data?.length) {
        return res.send([]);
    }

    const conversations = await supabase
        .from('conversations')
        .select(`
            *, 
            messages (
                id,
                conversation_id,
                message,
                created_at,
                users (
                    id,
                    username
                )
            )
        `)
        .or(`owner_user_id.eq.${req.query.user_id},or(id.in.(${paticipatingConversationIds.data.map((item: any) => item.conversation_id)}))`)

    return res.send(conversations.data)
}

//src/controllers/conversation.controller.ts

export const createConversation = async function (req: TypedRequestBody<{owner_id: string, participant_ids: string[], group_name: string}>, res: Response) {
    const {
      owner_id,
      participant_ids,
      group_name,
    } = req.body;

    // first create the conversation 
    const conversation = await supabase
      .from('conversations')
      .upsert({ 
        name: group_name,
        owner_user_id: owner_id,
        created_at: ((new Date()).toISOString()).toLocaleString()
       })
      .select()

    if (conversation.error) {
        res.send(500)
    }

    let participants: User[] = [];

    if (participant_ids.length > 1 && conversation.data?.length) {
        // attach all our users to this conversation
        const pivotData = await supabase
            .from('user_conversation')
            .upsert(participant_ids.map((participant_id) => {
            return { 
                user_id: participant_id, 
                conversation_id: conversation.data[0].id
            }
            }))
            .select()

            if (pivotData.data?.length) {
                // find our actual users 
                const actualParticipantUsers = await supabase
                    .from('users')
                    .select()
                    .in('id', participant_ids)

                if (actualParticipantUsers.data?.length) participants = actualParticipantUsers.data;
            }
    }



    if (conversation.error) {
        return res.sendStatus(500)
    } else {
        const conv: Conversation = {
            ...conversation.data[0],
            participants
        };

        return res.send(conv);
    }
}

import { 
  createConversation, 
  getAllConversations, 
} from './controllers/conversation.controller';

// CONVERSATION ENDPOINTS
app.post("/conversations/create", createConversation);
app.get("/conversations", getAllConversations)

// src/controllers/conversation.controller.ts
export const getConversationMessages = async function (req: TypedRequestQueryAndParams<{conversation_id: string} ,{last_message_date: Date}>, res: Response) {
    const { conversation_id } = req.params;
    const { last_message_date } = req.query;

    let query = supabase
        .from('messages')
        .select(`
            id,
            conversation_id,
            message,
            created_at,

            users (
                id,
                username
            )
        `)
        .order('created_at', { ascending: true })
        .eq('conversation_id', conversation_id)

        if (last_message_date){
            query = query.gt('created_at', last_message_date)
        }

    const messages = await query;    

    res.send(messages.data)
}

// src/controllers/conversation.controller.ts
export const addMessageToConversation = async function (req: TypedRequestQueryWithBodyAndParams<{conversation_id: string}, {user_id: string, message: string}>, res: Response) {
    const conversationid = req.params.conversation_id;
    const {
      user_id,
      message,
    } = req.body;

    const data = await supabase
      .from('messages')
      .upsert({ 
        conversation_id: conversationid,
        user_id,
        message,
        created_at: ((new Date()).toISOString()).toLocaleString()
      })
      .select(`
        *,
        users (
            id,
            username
        ),
        conversations (*)
      `)

    // get the users in this chat, except for the current one
    const userConversationIds = await supabase
        .from('user_conversation')
        .select('user_id')
        .eq('conversation_id', conversationid)

    if (data.error) {
        res.send(500)
    } else {
        res.send(
            data.data[0]
        )
    }
}

// src/utils/socket.ts
import { Server } from "socket.io";
import { SocketConnectedUsers, SocketSocketIdUserId, User, Message, Conversation } from '../types';

class Socket {
    private static _instance: Socket;

    private io;
    private users: SocketConnectedUsers = {};
    private socketIdUserId: SocketSocketIdUserId = {};

    private constructor(server: Server) {
        this.io = server;

        this.io.on('connection', (socket) => {
            console.log('a user connected');

            socket.on('join', (user: User) => {
                this.users[user.id] = {
                    socketId: socket.id,
                    socket: socket,
                    user
                }

                this.socketIdUserId[socket.id] = user.id;
            });

            socket.on('disconnect', () => {
                const userId = this.socketIdUserId[socket.id];

                if (userId) {
                    delete this.users[userId];
                    delete this.socketIdUserId[socket.id]
                }
            });
        });
    }

    static getInstance(server?: Server) {
        if (this._instance) {
            return this._instance;
        }

        if (server) {
            this._instance = new Socket(server);
            return this._instance;
        }

        return Error("Failed to init socket");
    }
}

export default Socket;

 socket.on('join', (user: User) => {
                this.users[user.id] = {
                    socketId: socket.id,
                    socket: socket,
                    user
                }

                this.socketIdUserId[socket.id] = user.id;
            });

socket.on('disconnect', () => {
                const userId = this.socketIdUserId[socket.id];

                if (userId) {
                    delete this.users[userId];
                    delete this.socketIdUserId[socket.id]
                }
            });

...
import { Server } from 'socket.io';
import http from 'http';
import Socket from "./utils/socket";

...
const server = http.createServer(app);
const ioServer = new Server(server);
Socket.getInstance(ioServer);

...
// change to
app.listen(3000); -> server.listen(3000);

public static sendMessageToUsers  (userIds: string[], message: Message) {
      userIds.forEach((item) => {
          const user = this._instance.users[item];

          if (user) {
              user.socket.emit('message', message);
          }
      })
  }

  public static notifyUsersOnConversationCreate  (userIds: string[], conversation: Conversation) {
      userIds.forEach((item) => {
          const user = this._instance.users[item];

          if (user) {
              user.socket.emit('newConversation', conversation);
          }
      })
  }

// ADD AN IMPORT STATEMENT 
import Socket from '../utils/socket';

// UPDATE THE FOLLOWING FUNCTIONS
export const createConversation = async function (req: TypedRequestBody<{owner_id: string, participant_ids: string[], group_name: string}>, res: Response) {
...
// UPDATE
if (conversation.error) {
        return res.sendStatus(500)
    } else {
        const conv: Conversation = {
            ...conversation.data[0],
            participants
        };

        Socket.notifyUsersOnConversationCreate(participant_ids as string[], conv)
        return res.send(conv);
    }
}

export const addMessageToConversation = async function (req: TypedRequestQueryWithBodyAndParams<{conversation_id: string}, {user_id: string, message: string}>, res: Response) {
...
// UPDATE 
if (data.error) {
        res.send(500)
    } else {
        if (userConversationIds.data && userConversationIds.data?.length > 0) {
            const userIdsForMessages = userConversationIds.data.map((item) => item.user_id).filter((item) => item !== user_id);
            Socket.sendMessageToUsers(userIdsForMessages as string[], data.data[0] as Message)
        }

        res.send(
            data.data[0]
        )
    }
}

Socket.notifyUsersOnConversationCreate(participant_ids as string[], conv)

Socket.sendMessageToUsers(userIdsForMessages as string[], data.data[0] as Message)