Build AI-Native B2B application with Postgres and Drizzle ORM

const app = express();
app.listen(PORT, () => console.log(`Server is running on port ${PORT}`));
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.use((req, res, next) => {
  const fn = match("/api/tenants/:tenantId/todos", {
    decode: decodeURIComponent,
  });
  const m = fn(req.path);

  //@ts-ignore
  const tenantId = m?.params?.tenantId;
  console.log("setting context to tenant: " + tenantId);
  tenantContext.run(tenantId, next);
});

const client = new Client({
  connectionString: process.env.DATABASE_URL,
});

// Drizzle expects the connection to be open when using a client. Alternatively, you can use a pool
await client.connect();

// check the connection
const res = await client.query("SELECT $1::text as message", [
  "Client connected to Nile",
]);
console.log(res.rows[0].message);

export const db = drizzle(client, { logger: true });

return db.transaction(async (tx) => {
  const tenantId = tenantContext.getStore();
  // clean old context
  await tx.execute(sql`reset nile.tenant_id`);
  // if there's a tenant ID, set it in the context
  if (tenantId) {
    await tx.execute(sql`set nile.tenant_id = '${sql.raw(tenantId)}'`);
  }
  return cb(tx);
});

const todos = await tenantDB(async (tx) => {
  return await tx.select().from(todoSchema);
});
res.json(todos);

const { title, complete } = req.body;
const tenantId = req.params.tenantId;

// We are using tenantDB with tenant context to ensure that we only find tasks for the current tenant
const similarTasks = await findSimilarTasks(tenantDB, title);
console.log("found similar tasks: " + JSON.stringify(similarTasks));

const estimate = await aiEstimate(title, similarTasks);
console.log("estimated time: " + estimate);

// get the embedding for the task, so we can find it in future similarity searches
const embedding = await embedTask(title, EmbeddingTasks.SEARCH_DOCUMENT);

const newTodo = await tenantDB(async (tx) => {
  return await tx
    .insert(todoSchema)
    .values({ tenantId, title, complete, estimate, embedding })
    .returning();
});
// return without the embedding vector, since it is huge and useless
res.json(newTodo.map((t: any) => ({ ...t, embedding: "<omitted>" })));

const embedding = await embedTask(title, EmbeddingTasks.SEARCH_QUERY);
const similarity = sql<number>`(${cosineDistance(
  todoSchema.embedding,
  embedding
)})`;

// get similar tasks, no need to filter by tenant because we are already in the tenant context
const similarTasks = await tenantNile(async (tx) => {
  return await tx
    .select({ task: todoSchema.title, estimate: todoSchema.estimate })
    .from(todoSchema)
    .where(lt(similarity, 1))
    .orderBy((t: any) => desc(similarity))
    .limit(3);
});

let resp = await ai.embeddings.create({
  model: EMBEDDING_MODEL,
  input: adjust_input(title, task),
});

const aiEstimate = await ai.chat.completions.create({
  messages: [
    {
      role: "user",
      content: `you are an amazing project manager. I need to ${title}. How long do you think this will take?
        I have a few similar tasks with their estimates, please use them as reference: ${similarTasks}.
        respond with just the estimate, keep the answer short.`,
    },
  ],
  max_tokens: 64, // limit the response to 64 tokens
  model: model,
});