← back to Nationalrealestate
add sublease inventory: table + /api/sublease GET + gated admin POST/DELETE
aa180e342ef0d9643a1d89606bcc01636c4db4c8 · 2026-07-22 19:29:37 -0700 · Steve
Files touched
A db/migrations/007_sublease.sqlM src/server/index.tsA src/server/sublease.ts
Diff
commit aa180e342ef0d9643a1d89606bcc01636c4db4c8
Author: Steve <steve@designerwallcoverings.com>
Date: Wed Jul 22 19:29:37 2026 -0700
add sublease inventory: table + /api/sublease GET + gated admin POST/DELETE
---
db/migrations/007_sublease.sql | 33 ++++++++++++
src/server/index.ts | 2 +
src/server/sublease.ts | 117 +++++++++++++++++++++++++++++++++++++++++
3 files changed, 152 insertions(+)
diff --git a/db/migrations/007_sublease.sql b/db/migrations/007_sublease.sql
new file mode 100644
index 0000000..f2cc453
--- /dev/null
+++ b/db/migrations/007_sublease.sql
@@ -0,0 +1,33 @@
+-- Sublease inventory — manual-entry CRE sublease/direct listings.
+-- Data source is manual admin entry (all live sublease sources are Cloudflare/ToS bot-walled,
+-- so a clean robots-honored crawl is impossible). Ships empty; RENTV's team logs real deals.
+CREATE TABLE IF NOT EXISTS sublease (
+ id serial PRIMARY KEY,
+ address text NOT NULL,
+ city text,
+ submarket text,
+ floor_suite text,
+ sqft numeric,
+ asking_rate numeric, -- $ per SF
+ rate_period text DEFAULT 'yr', -- 'yr' | 'mo'
+ lease_type text DEFAULT 'sublease', -- 'sublease' | 'direct'
+ term_months integer,
+ available_date date,
+ sublandlord text, -- the sitting tenant offering the space
+ firm_name text, -- listing brokerage
+ firm_id integer,
+ broker_name text,
+ broker_phone text,
+ broker_email text,
+ broker_id integer,
+ listing_url text,
+ source text DEFAULT 'manual',
+ lat double precision,
+ lng double precision,
+ status text DEFAULT 'active', -- 'active' | 'leased' | 'withdrawn'
+ notes text,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ updated_at timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS idx_sublease_status ON sublease(status);
+CREATE INDEX IF NOT EXISTS idx_sublease_city ON sublease(lower(city));
diff --git a/src/server/index.ts b/src/server/index.ts
index 418d207..a4fbd82 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -11,6 +11,7 @@ import { mountWatchlistAdmin } from './watchlist_admin.ts';
import { mountProperty } from './property.ts';
import { mountListings } from './listings.ts';
import { mountCommercial } from './commercial.ts';
+import { mountSublease } from './sublease.ts';
const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..', '..');
@@ -346,6 +347,7 @@ mountWatchlistAdmin(app);
mountProperty(app);
mountListings(app);
mountCommercial(app);
+mountSublease(app);
app.use(express.static(join(ROOT, 'public')));
diff --git a/src/server/sublease.ts b/src/server/sublease.ts
new file mode 100644
index 0000000..d8bc18b
--- /dev/null
+++ b/src/server/sublease.ts
@@ -0,0 +1,117 @@
+/**
+ * Sublease inventory routes — manual-entry CRE sublease listings.
+ *
+ * Why manual-entry: every real commercial-sublease source (LoopNet/CoStar, CommercialCafe,
+ * Colliers, CREXi) is bot-walled (Cloudflare managed challenges or anti-scrape ToS), so a
+ * clean robots-honored/facts-only crawl is impossible — the only path through is a
+ * challenge-solver = detection evasion (a red line). So the honest pipeline is: RENTV's team
+ * enters real sublease deals they source through their broker relationships via the gated
+ * admin form, and this table is the single source of truth. Ships EMPTY-HONEST until a real
+ * row exists — never fabricated inventory.
+ *
+ * Mounted from index.ts via mountSublease(app); the whole API is already behind the global
+ * basic-auth middleware there, so GET (read, proxied to /desk) and POST (admin add) are both
+ * auth-gated without extra wiring.
+ */
+import type { Express } from 'express';
+import { query } from '../../db/pool.ts';
+
+// Columns the admin form may set, mapped to their coercers. Anything else is ignored.
+const FIELDS: Record<string, (v: unknown) => unknown> = {
+ address: v => String(v).trim(),
+ city: v => (v == null ? null : String(v).trim()),
+ submarket: v => (v == null ? null : String(v).trim()),
+ floor_suite: v => (v == null ? null : String(v).trim()),
+ sqft: v => (v === '' || v == null ? null : Number(v)),
+ asking_rate: v => (v === '' || v == null ? null : Number(v)),
+ rate_period: v => (v == null ? 'yr' : String(v)),
+ lease_type: v => (v == null ? 'sublease' : String(v)),
+ term_months: v => (v === '' || v == null ? null : Math.round(Number(v))),
+ available_date: v => (v === '' || v == null ? null : String(v)),
+ sublandlord: v => (v == null ? null : String(v).trim()),
+ firm_name: v => (v == null ? null : String(v).trim()),
+ broker_name: v => (v == null ? null : String(v).trim()),
+ broker_phone: v => (v == null ? null : String(v).trim()),
+ broker_email: v => (v == null ? null : String(v).trim()),
+ listing_url: v => (v == null ? null : String(v).trim()),
+ lat: v => (v === '' || v == null ? null : Number(v)),
+ lng: v => (v === '' || v == null ? null : Number(v)),
+ status: v => (v == null ? 'active' : String(v)),
+ notes: v => (v == null ? null : String(v).trim()),
+};
+
+export function mountSublease(app: Express): void {
+ // GET /api/sublease?q=&city=&status=active&limit= — CRCP-friendly rows, newest first.
+ app.get('/api/sublease', async (req, res) => {
+ try {
+ const limit = Math.min(Math.max(Number(req.query.limit) || 500, 1), 2000);
+ const conds: string[] = [];
+ const params: unknown[] = [];
+ // default to active inventory; ?status=all shows everything
+ if (String(req.query.status || 'active') !== 'all') {
+ params.push(String(req.query.status || 'active'));
+ conds.push(`status = $${params.length}`);
+ }
+ if (req.query.city) { params.push(String(req.query.city)); conds.push(`lower(city) = lower($${params.length})`); }
+ if (req.query.q) {
+ params.push('%' + String(req.query.q).toLowerCase() + '%');
+ const i = params.length;
+ conds.push(`(lower(address) LIKE $${i} OR lower(coalesce(city,'')) LIKE $${i}
+ OR lower(coalesce(submarket,'')) LIKE $${i} OR lower(coalesce(sublandlord,'')) LIKE $${i}
+ OR lower(coalesce(firm_name,'')) LIKE $${i} OR lower(coalesce(broker_name,'')) LIKE $${i})`);
+ }
+ const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
+ params.push(limit);
+ const r = await query(
+ `SELECT id, address, city, submarket, floor_suite, sqft, asking_rate, rate_period,
+ lease_type, term_months, available_date, sublandlord, firm_name, firm_id,
+ broker_name, broker_phone, broker_email, broker_id, listing_url, source,
+ lat, lng, status, notes, created_at, updated_at
+ FROM sublease ${where}
+ ORDER BY created_at DESC, id DESC
+ LIMIT $${params.length}`,
+ params,
+ );
+ const total = await query<{ n: string }>(`SELECT COUNT(*)::text AS n FROM sublease WHERE status='active'`);
+ res.json({ count: r.rows.length, total: Number(total.rows[0].n), rows: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+ });
+
+ // POST /api/admin/sublease — insert one real listing (admin, already basic-auth-gated).
+ app.post('/api/admin/sublease', async (req, res) => {
+ try {
+ const body = req.body || {};
+ if (!body.address || !String(body.address).trim()) {
+ return res.status(400).json({ error: 'address is required' });
+ }
+ const cols: string[] = [];
+ const vals: unknown[] = [];
+ const ph: string[] = [];
+ for (const [col, coerce] of Object.entries(FIELDS)) {
+ if (col in body) { cols.push(col); vals.push(coerce(body[col])); ph.push(`$${vals.length}`); }
+ }
+ cols.push('source'); vals.push(String(body.source || 'manual')); ph.push(`$${vals.length}`);
+ const r = await query(
+ `INSERT INTO sublease (${cols.join(', ')}) VALUES (${ph.join(', ')}) RETURNING *`,
+ vals,
+ );
+ res.json({ ok: true, row: r.rows[0] });
+ } catch (e: any) {
+ res.status(500).json({ ok: false, error: String(e.message || e) });
+ }
+ });
+
+ // DELETE /api/admin/sublease/:id — withdraw a listing (soft: mark withdrawn).
+ app.delete('/api/admin/sublease/:id', async (req, res) => {
+ try {
+ const id = Number(req.params.id);
+ if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'bad id' });
+ await query(`UPDATE sublease SET status='withdrawn', updated_at=now() WHERE id=$1`, [id]);
+ res.json({ ok: true });
+ } catch (e: any) {
+ res.status(500).json({ ok: false, error: String(e.message || e) });
+ }
+ });
+}
← 253038d Add /api/commercial/digest — one-shot marquee+briefs/recent/
·
back to Nationalrealestate
·
chore: harden sublease.ts (enum allowlists on status/lease_t dc6baf0 →