How to Build a Inventory Tracking Platform with Replit

1// Type this into Replit Agent:
2// Build an asset tracking platform with Express and PostgreSQL using Drizzle ORM.
3// Create a shared/schema.ts with these tables:
4// - assets: id serial primary key, asset_tag text not null unique (format AST-XXXX),
5//   name text not null, description text, category text not null (enum: equipment/vehicle/furniture/electronics/tools),
6//   serial_number text, purchase_date date, purchase_cost integer, warranty_expiry date,
7//   condition text default 'good' (enum: new/good/fair/poor/retired), image_url text,
8//   qr_code_data text unique, created_at timestamp default now()
9// - locations: id serial, name text not null, building text, floor text, room text
10// - asset_assignments: id serial, asset_id integer references assets, assigned_to text not null,
11//   assigned_by text not null, location_id integer references locations, assigned_at timestamp default now(),
12//   returned_at timestamp, notes text
13// - maintenance_records: id serial, asset_id integer references assets, type text not null
14//   (enum: inspection/repair/calibration/replacement), description text not null,
15//   cost integer, performed_by text, scheduled_date date, completed_date date, next_due_date date,
16//   created_at timestamp default now()
17// - audit_log: id serial, asset_id integer references assets, action text not null,
18//   performed_by text not null, details jsonb, created_at timestamp default now()
19// Create Express routes for all CRUD operations plus:
20// - POST /api/assets/:id/assign (check out to a person)
21// - POST /api/assets/:id/return (check back in)
22// - GET /api/assets/due-maintenance (overdue items)
23// - GET /api/assets/:tag/qr (lookup by asset tag for QR scanning)
24// Use Replit Auth for authentication.
25// Build a React frontend with an asset registry table, asset detail page,
26// checkout/return form, and maintenance dashboard.

1const express = require('express');
2const { db } = require('../db');
3const { assets, auditLog } = require('../../shared/schema');
4const { eq, sql } = require('drizzle-orm');
5
6const router = express.Router();
7
8// Generate next asset tag like AST-0042
9async function generateAssetTag() {
10  const result = await db.select({ max: sql`MAX(CAST(SUBSTRING(asset_tag FROM 5) AS INTEGER))` }).from(assets);
11  const next = (result[0].max || 0) + 1;
12  return `AST-${String(next).padStart(4, '0')}`;
13}
14
15// POST /api/assets — create new asset with auto tag and QR data
16router.post('/', async (req, res) => {
17  try {
18    const assetTag = await generateAssetTag();
19    const qrCodeData = `${process.env.REPLIT_DEPLOYMENT_URL || 'http://localhost:5000'}/api/assets/${assetTag}/qr`;
20
21    const [asset] = await db.insert(assets).values({
22      ...req.body,
23      assetTag,
24      qrCodeData,
25    }).returning();
26
27    await db.insert(auditLog).values({
28      assetId: asset.id,
29      action: 'created',
30      performedBy: req.user?.name || 'system',
31      details: { assetTag },
32    });
33
34    res.status(201).json(asset);
35  } catch (err) {
36    res.status(500).json({ error: err.message });
37  }
38});
39
40// GET /api/assets/:tag/qr — lookup by QR scan
41router.get('/:tag/qr', async (req, res) => {
42  const [asset] = await db.select().from(assets).where(eq(assets.assetTag, req.params.tag));
43  if (!asset) return res.status(404).json({ error: 'Asset not found' });
44  res.json(asset);
45});
46
47module.exports = router;

1const { assetAssignments, assets, auditLog } = require('../../shared/schema');
2const { eq, isNull, and } = require('drizzle-orm');
3
4// POST /api/assets/:id/assign
5router.post('/:id/assign', async (req, res) => {
6  const assetId = parseInt(req.params.id);
7  const { assignedTo, locationId, notes } = req.body;
8
9  try {
10    // Close any open assignment
11    await db.update(assetAssignments)
12      .set({ returnedAt: new Date() })
13      .where(and(
14        eq(assetAssignments.assetId, assetId),
15        isNull(assetAssignments.returnedAt)
16      ));
17
18    // Create new assignment
19    const [assignment] = await db.insert(assetAssignments).values({
20      assetId,
21      assignedTo,
22      assignedBy: req.user?.name || 'unknown',
23      locationId: locationId || null,
24      notes: notes || null,
25    }).returning();
26
27    await db.insert(auditLog).values({
28      assetId,
29      action: 'assigned',
30      performedBy: req.user?.name || 'unknown',
31      details: { assignedTo, locationId },
32    });
33
34    res.status(201).json(assignment);
35  } catch (err) {
36    res.status(500).json({ error: err.message });
37  }
38});
39
40// POST /api/assets/:id/return
41router.post('/:id/return', async (req, res) => {
42  const assetId = parseInt(req.params.id);
43
44  const [updated] = await db.update(assetAssignments)
45    .set({ returnedAt: new Date() })
46    .where(and(
47      eq(assetAssignments.assetId, assetId),
48      isNull(assetAssignments.returnedAt)
49    ))
50    .returning();
51
52  if (!updated) return res.status(404).json({ error: 'No active assignment found' });
53
54  await db.insert(auditLog).values({
55    assetId,
56    action: 'returned',
57    performedBy: req.user?.name || 'unknown',
58    details: {},
59  });
60
61  res.json(updated);
62});
63
64module.exports = router;

1const { maintenanceRecords, assets } = require('../../shared/schema');
2const { lte, eq } = require('drizzle-orm');
3
4// GET /api/assets/due-maintenance
5router.get('/due-maintenance', async (req, res) => {
6  const today = new Date().toISOString().split('T')[0];
7
8  const overdue = await db
9    .select({
10      assetId: assets.id,
11      assetTag: assets.assetTag,
12      assetName: assets.name,
13      category: assets.category,
14      maintenanceType: maintenanceRecords.type,
15      nextDueDate: maintenanceRecords.nextDueDate,
16      lastCompleted: maintenanceRecords.completedDate,
17    })
18    .from(maintenanceRecords)
19    .innerJoin(assets, eq(maintenanceRecords.assetId, assets.id))
20    .where(lte(maintenanceRecords.nextDueDate, today));
21
22  res.json(overdue);
23});
24
25// POST /api/assets/:id/maintenance — log or schedule maintenance
26router.post('/:id/maintenance', async (req, res) => {
27  const assetId = parseInt(req.params.id);
28  const { type, description, cost, performedBy, scheduledDate, completedDate, nextDueDate } = req.body;
29
30  const [record] = await db.insert(maintenanceRecords).values({
31    assetId,
32    type,
33    description,
34    cost: cost || null,
35    performedBy: performedBy || null,
36    scheduledDate: scheduledDate || null,
37    completedDate: completedDate || null,
38    nextDueDate: nextDueDate || null,
39  }).returning();
40
41  res.status(201).json(record);
42});

1// server/db.js — DB helper with retry wrapper
2const { drizzle } = require('drizzle-orm/node-postgres');
3const { Pool } = require('pg');
4
5const pool = new Pool({
6  connectionString: process.env.DATABASE_URL,
7  max: 10,
8  idleTimeoutMillis: 30000,
9  connectionTimeoutMillis: 5000,
10});
11
12exports.db = drizzle(pool);
13
14// Retry wrapper for the first query after DB idle
15exports.withRetry = async (fn, retries = 3) => {
16  for (let attempt = 1; attempt <= retries; attempt++) {
17    try {
18      return await fn();
19    } catch (err) {
20      const isConnectionError = err.code === 'ECONNREFUSED' || err.code === '57P03';
21      if (isConnectionError && attempt < retries) {
22        console.log(`DB connection attempt ${attempt} failed, retrying...`);
23        await new Promise(r => setTimeout(r, 1000 * attempt));
24      } else {
25        throw err;
26      }
27    }
28  }
29};
30
31// server/index.js — bind to 0.0.0.0 for Replit
32const app = require('express')();
33app.listen(5000, '0.0.0.0', () => console.log('Asset tracker running on port 5000'));