065f2edd23
Refresh of the open-core distribution from the private tree. Included since the previous snapshot: - Mail module (ADR-158/159/160/169): composer, labels, scheduling, attachments, reply-tokens, IMAP/SMTP bridge + migrations 079-083 - Crawler-readable SSR for /blog and public spaces (ADR-190): blogSeo, publicDocsSeo, per-space SEO prefs, blog index/post pages - Registration policy + referral/promo settings (ADR-183/188) - Message translation + language detection (ADR-185) - Reddit connector for the agent-tool surface Excised from the public distribution (unchanged policy): infrastructure topology and host config, internal ops scripts, DB cleanup snapshots, business documents, throwaway debug scripts, and two private product lines (SC-SIM simulator, personal one-off tools). Real host addresses are replaced with placeholders; credential-shaped literals are redacted. Frontend build verified green on this tree.
1477 lines
54 KiB
JavaScript
1477 lines
54 KiB
JavaScript
// API v3: Public Routes - Unauthenticated access to external spaces (ADR-105)
|
|
// No authentication required. Uses publicRateLimit and publicSpaceAccess middleware.
|
|
import express from 'express';
|
|
import { dbAll, dbGet, isPostgres, safeJsonParse } from '../../database/connection.js';
|
|
import { getProjectsBySpace } from '../../services/ProjectService.js';
|
|
import { buildOrderClause } from '../../services/orderClause.js';
|
|
import { apiLogger } from '../../utils/logger.js';
|
|
import { success, error, notFound } from '../../utils/response.js';
|
|
import { publicAbuseGuard, publicRateLimit, publicSpaceAccess, publicPasswordVerify } from '../../middleware/publicAccess.js';
|
|
import {
|
|
PUBLIC_PRESET_WHITELIST,
|
|
isPresetAllowed,
|
|
extractWidgetTableRef,
|
|
scrubWidgetConfig,
|
|
extractDocumentsRegistryTableId,
|
|
scrubRegistryRowData,
|
|
scrubAtomRowData
|
|
} from '../../lib/publicScrubber.js';
|
|
import { resolveLandingProject } from '../../services/public/resolveLandingProject.js';
|
|
import { readPublicSidebarPrefs } from '../../services/SpaceVisibilityService.js';
|
|
import { isPromoEnabled } from '../../services/starter-pack/StarterPackService.js';
|
|
// NO-SIGNUP-MCP §B1: per-space public MCP mount. Imported here (not the other
|
|
// way) so the route inherits this router's abuse-guard + rate-limit. The
|
|
// gated read loaders publicMcp.js needs are exported from THIS file at the
|
|
// bottom — the cycle is safe (they are hoisted function declarations called
|
|
// only at request time).
|
|
import { mcpPublicHandler } from '../../services/mcp/publicMcp.js';
|
|
|
|
const router = express.Router();
|
|
|
|
// ADR-105 AC11: Abuse guard (temp IP block) runs before rate limiter
|
|
router.use(publicAbuseGuard);
|
|
|
|
// Apply public rate limiter to all routes in this router
|
|
router.use(publicRateLimit);
|
|
|
|
/**
|
|
* ALL /api/v3/public/s/:slug/mcp — anonymous, no-signup MCP endpoint scoped to
|
|
* one public space (NO-SIGNUP-MCP.md Stream B v2). `publicSpaceAccess` pins
|
|
* `req.publicSpace` (and enforces the optional per-space password). The handler
|
|
* dispatches only through space-gated loaders — never `executeTool`.
|
|
* Streamable-HTTP uses GET (stream) + POST (messages), hence `router.all`.
|
|
*/
|
|
router.all('/s/:slug/mcp', publicSpaceAccess, mcpPublicHandler);
|
|
|
|
/**
|
|
* GET /api/v3/public/registration-config — ADR-183-A D6.
|
|
* Pre-auth bootstrap for the registration form. Exposes the owner-controlled promo
|
|
* toggle so the FE renders the promo field iff `promo_enabled === true`. The FE must
|
|
* NOT read `_app_settings` directly — this is the public contract. Not space-scoped
|
|
* (no publicSpaceAccess), but still behind the router-level abuse-guard + rate-limit.
|
|
*/
|
|
router.get('/registration-config', async (req, res) => {
|
|
try {
|
|
return success(res, { promo_enabled: await isPromoEnabled() });
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'registration-config failed');
|
|
// Fail-open to the flag's own default (promo ON) — never break the register page.
|
|
return success(res, { promo_enabled: true });
|
|
}
|
|
});
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug
|
|
* Get public space info (metadata, projects list for external visibility).
|
|
* Requires publicSpaceAccess middleware (handles password protection).
|
|
*/
|
|
router.get('/s/:slug', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
|
|
// Fetch projects in this space (public metadata only).
|
|
// AC15 (ADR-0060 §6): hard-ban the per-space System Data project here
|
|
// too — without this the public landing page would list it as a card
|
|
// that 404s on click. Filtering at the listing surface keeps UX in
|
|
// sync with the drill-in gates in loadPublicProject.
|
|
const projectsRaw = await getProjectsBySpace(space.id);
|
|
const projects = projectsRaw.filter(p => !isSystemDataProject(p));
|
|
|
|
// Return safe project metadata (no internal IDs for tables, no sensitive data)
|
|
const publicProjects = projects.map(p => ({
|
|
id: p.id,
|
|
name: p.name,
|
|
description: p.description,
|
|
icon: p.icon,
|
|
type: p.type,
|
|
order_index: p.order_index,
|
|
created_at: p.created_at
|
|
}));
|
|
|
|
// ADR-0060-A P7/A2: surface the landing project + its first dashboard.
|
|
// Frontend uses these to mount <DashboardGrid readOnly /> directly,
|
|
// replacing the P-track card-grid. The helper enforces AC15 (System
|
|
// Data ban) and the 3-tier fallback so the payload is meaningful even
|
|
// when main_project_id has never been set.
|
|
//
|
|
// Defensive try/catch: between code-deploy and migration-apply the
|
|
// `spaces.main_project_id` column may not yet exist on the target
|
|
// DB (e.g. DEV runtime pointed at unmigrated PROD). Falling back to
|
|
// nulls keeps the existing /s/:slug payload intact instead of 500ing
|
|
// the whole public surface. Becomes dead code once migration 062 has
|
|
// landed on every host.
|
|
let landing = { main_project_id: null, main_dashboard_id: null };
|
|
try {
|
|
landing = await resolveLandingProject(space.id);
|
|
} catch (resolveErr) {
|
|
apiLogger.warn(
|
|
{ err: resolveErr, spaceId: space.id },
|
|
'resolveLandingProject failed (migration 062 not applied?); falling back to nulls'
|
|
);
|
|
}
|
|
|
|
success(res, {
|
|
space: {
|
|
id: space.id,
|
|
name: space.name,
|
|
description: space.description,
|
|
icon: space.icon,
|
|
type: space.type,
|
|
theme_primary: space.theme_primary,
|
|
theme_secondary: space.theme_secondary,
|
|
theme_tertiary: space.theme_tertiary,
|
|
public_slug: space.public_slug,
|
|
main_project_id: landing.main_project_id,
|
|
main_dashboard_id: landing.main_dashboard_id,
|
|
created_at: space.created_at
|
|
},
|
|
projects: publicProjects
|
|
});
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug error');
|
|
error(res, 'PUBLIC_SPACE_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* Helper: find the documents `_registry` table for a public space.
|
|
* Space-scoped (joins through projects.space_id) — returns `{ id, project_id }`
|
|
* or null. Single source for both the REST routes and the MCP mount so a future
|
|
* gate added to one reaches the other (NO-SIGNUP-MCP §B2 drift guard).
|
|
*/
|
|
async function findPublicDocsRegistry(spaceId) {
|
|
return dbGet(`
|
|
SELECT ut.id, ut.project_id
|
|
FROM universal_tables ut
|
|
JOIN projects p ON p.id = ut.project_id
|
|
WHERE p.space_id = ?
|
|
AND ut.name = '_registry'
|
|
AND ut.table_type = 'documents_registry'
|
|
LIMIT 1
|
|
`, [spaceId]);
|
|
}
|
|
|
|
/**
|
|
* Gated loader: list published documents in a public space's registry.
|
|
* Returns `{ documents, registry_table_id, not_initialized? }`.
|
|
*/
|
|
async function loadPublicDocumentsRegistry(spaceId) {
|
|
const registry = await findPublicDocsRegistry(spaceId);
|
|
if (!registry) {
|
|
return { documents: [], not_initialized: true };
|
|
}
|
|
|
|
const orderBy = isPostgres()
|
|
? `COALESCE((data->>'order_index')::integer, 0), created_at`
|
|
: `CAST(json_extract(data, '$.order_index') AS INTEGER), created_at`;
|
|
|
|
const rows = await dbAll(
|
|
`SELECT id, base_id, data, created_at, updated_at FROM table_rows WHERE table_id = ? ORDER BY ${orderBy}`,
|
|
[registry.id]
|
|
);
|
|
|
|
const documents = rows.map(row => {
|
|
const data = safeJsonParse(row.data, {});
|
|
return {
|
|
id: row.id,
|
|
base_id: row.base_id,
|
|
name: data.name || '',
|
|
description: data.description || '',
|
|
slug: data.slug || '',
|
|
icon: data.icon || '',
|
|
category: data.category || null,
|
|
status: data.status || 'draft',
|
|
order_index: data.order_index || 0,
|
|
// ADR-190-A D3: optional per-document SEO overrides. Additive, optional
|
|
// keys — note this loader also backs the anonymous MCP `list_documents`
|
|
// (services/mcp/publicMcp.js), so the MCP payload widens by these three.
|
|
seo_title: data.seo_title || '',
|
|
seo_description: data.seo_description || '',
|
|
seo_noindex: data.seo_noindex === true,
|
|
created_at: row.created_at,
|
|
updated_at: row.updated_at
|
|
};
|
|
});
|
|
|
|
return { documents, registry_table_id: registry.id };
|
|
}
|
|
|
|
/**
|
|
* Gated loader: fetch a single public document + its content items.
|
|
* `docRef` may be the registry row's numeric id OR the document slug — the
|
|
* MCP `get_document_content` tool passes a `document_id` while the REST route
|
|
* passes a slug, so both resolve here. Returns `{ document, table_id, items,
|
|
* count }` or null (registry missing, not found, no content table, or —
|
|
* defence-in-depth — the content table lives outside this space / System Data).
|
|
*/
|
|
async function loadPublicDocument(spaceId, docRef) {
|
|
const registry = await findPublicDocsRegistry(spaceId);
|
|
if (!registry) return null;
|
|
|
|
// Numeric ref → registry row id; otherwise → slug lookup. Both are scoped to
|
|
// this space's registry table, so neither can reach another tenant's docs.
|
|
const asNum = Number(docRef);
|
|
const byId = Number.isFinite(asNum) && String(asNum) === String(docRef).trim();
|
|
let docRow;
|
|
if (byId) {
|
|
docRow = await dbGet(
|
|
`SELECT id, base_id, data, created_at, updated_at FROM table_rows WHERE table_id = ? AND id = ?`,
|
|
[registry.id, asNum]
|
|
);
|
|
} else {
|
|
const slugQuery = isPostgres()
|
|
? `SELECT id, base_id, data, created_at, updated_at FROM table_rows WHERE table_id = $1 AND data->>'slug' = $2`
|
|
: `SELECT id, base_id, data, created_at, updated_at FROM table_rows WHERE table_id = ? AND json_extract(data, '$.slug') = ?`;
|
|
docRow = await dbGet(slugQuery, [registry.id, docRef]);
|
|
}
|
|
if (!docRow) return null;
|
|
|
|
const docData = safeJsonParse(docRow.data, {});
|
|
const tableId = docData.table_id;
|
|
if (!tableId) return null;
|
|
|
|
// Defence-in-depth: the document's content table must itself live inside
|
|
// this same public space (same gate the widget-scoped atoms route uses).
|
|
// A registry row pointing at an out-of-space content table → null.
|
|
const contentTable = await loadPublicAtomsTable(spaceId, Number(tableId));
|
|
if (!contentTable) return null;
|
|
|
|
const contentOrderBy = buildOrderClause('order', {
|
|
dialect: isPostgres() ? 'pg' : 'sqlite', mode: 'coalesce', tieBreak: 'id',
|
|
});
|
|
|
|
const rows = await dbAll(
|
|
`SELECT id, base_id, data, created_at, updated_at FROM table_rows WHERE table_id = ? ORDER BY ${contentOrderBy}`,
|
|
[tableId]
|
|
);
|
|
|
|
const items = rows.map(row => ({
|
|
id: row.id,
|
|
base_id: row.base_id,
|
|
...safeJsonParse(row.data, {}),
|
|
created_at: row.created_at,
|
|
updated_at: row.updated_at
|
|
}));
|
|
|
|
return {
|
|
document: {
|
|
id: docRow.id,
|
|
base_id: docRow.base_id,
|
|
name: docData.name || '',
|
|
description: docData.description || '',
|
|
slug: docData.slug || '',
|
|
icon: docData.icon || '',
|
|
category: docData.category || null,
|
|
status: docData.status || 'draft',
|
|
created_at: docRow.created_at,
|
|
updated_at: docRow.updated_at
|
|
},
|
|
table_id: tableId,
|
|
items,
|
|
count: items.length
|
|
};
|
|
}
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/docs
|
|
* List documents in the public space.
|
|
* Finds the documents project and returns the _registry entries.
|
|
*/
|
|
router.get('/s/:slug/docs', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
success(res, await loadPublicDocumentsRegistry(req.publicSpace.id));
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/docs error');
|
|
error(res, 'PUBLIC_DOCS_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/docs/:docSlug
|
|
* Get a single document's content by its slug.
|
|
* Returns the document items (read-only).
|
|
*/
|
|
router.get('/s/:slug/docs/:docSlug', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const doc = await loadPublicDocument(req.publicSpace.id, req.params.docSlug);
|
|
if (!doc) {
|
|
return notFound(res, 'Document');
|
|
}
|
|
success(res, doc);
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/docs/:docSlug error');
|
|
error(res, 'PUBLIC_DOC_CONTENT_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* POST /api/v3/public/s/:slug/verify-password
|
|
* Verify password for a protected public space.
|
|
* On success, sets an httpOnly session cookie.
|
|
*/
|
|
router.post('/s/:slug/verify-password', publicPasswordVerify);
|
|
|
|
// ============================================================
|
|
// ADR-0060 P1 — Public tree + read-only table endpoints
|
|
// ============================================================
|
|
|
|
/**
|
|
* Helper: parse column config JSON safely (table_columns.config is TEXT).
|
|
* Returns {} on any failure.
|
|
*
|
|
* Exported for the row-level column-whitelist unit tests (ADR-0060 P5d AC14).
|
|
*/
|
|
export function parseColumnConfig(col) {
|
|
return safeJsonParse(col?.config, {}) || {};
|
|
}
|
|
|
|
/**
|
|
* Helper: is column publicly exposed?
|
|
* Inside a public space (gated by space.public_slug) all columns are visible
|
|
* by default. Owners can hide a specific column by setting
|
|
* `config.is_public === false` (explicit opt-out).
|
|
*
|
|
* Exported so the row-level column whitelist (AC3 projection / AC14 row data
|
|
* gate) can be unit-tested without spinning up a DB-backed supertest.
|
|
*/
|
|
export function isColumnPublic(col) {
|
|
const cfg = parseColumnConfig(col);
|
|
const v = cfg.is_public;
|
|
return !(v === false || v === 'false');
|
|
}
|
|
|
|
/**
|
|
* Helper: project a column row to its public-safe metadata shape.
|
|
*/
|
|
function projectPublicColumn(col) {
|
|
const cfg = parseColumnConfig(col);
|
|
// Whitelist of safe config fields only — never leak the full config blob.
|
|
const safeConfig = {
|
|
is_public: true
|
|
};
|
|
if (cfg.cellFormat !== undefined) safeConfig.cellFormat = cfg.cellFormat;
|
|
// Options can be inline on column.options text OR config.options — prefer
|
|
// config.options (canonical), fall back to column.options (JSON text).
|
|
if (Array.isArray(cfg.options)) {
|
|
safeConfig.options = cfg.options;
|
|
} else if (col.options) {
|
|
const parsedOptions = safeJsonParse(col.options, null);
|
|
if (Array.isArray(parsedOptions)) safeConfig.options = parsedOptions;
|
|
}
|
|
// Relation metadata — only labelColumn is useful publicly, tableId reveals
|
|
// internal structure but is needed by the viewer; keep minimal.
|
|
if (cfg.relation && cfg.relation.enabled) {
|
|
safeConfig.relation = {
|
|
enabled: true,
|
|
labelColumn: cfg.relation.labelColumn || null
|
|
};
|
|
}
|
|
return {
|
|
id: col.id,
|
|
// `name` is the canonical row-data key (matches table_rows.data[name]).
|
|
// `display_name` is the human-readable label rendered in the viewer.
|
|
// Before ADR-0060 P5d these were collapsed onto a single `name` field,
|
|
// which broke row lookup when display_name diverged from column_name.
|
|
name: col.column_name,
|
|
display_name: col.display_name || col.column_name,
|
|
type: col.type,
|
|
position: col.order_index ?? 0,
|
|
settings: safeConfig
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Helper: resolve a relation cell value (which is the related row's id or
|
|
* base_id) into a `{ label }` projection — never leaks the internal id.
|
|
* Returns `{ label: '<unresolved>' }` if the target table has no usable
|
|
* label column or the relation row is missing.
|
|
*/
|
|
async function resolveRelationLabel(relCfg, cellValue) {
|
|
if (cellValue === null || cellValue === undefined || cellValue === '') {
|
|
return { label: '' };
|
|
}
|
|
const targetTableId = relCfg.tableId;
|
|
const labelColumn = relCfg.labelColumn;
|
|
if (!targetTableId || !labelColumn) {
|
|
return { label: '<unresolved>' };
|
|
}
|
|
try {
|
|
// Find the column id on the target table whose column_name matches the
|
|
// configured labelColumn — that's the key under which the row's data is
|
|
// stored in table_rows.data.
|
|
const labelCol = await dbGet(
|
|
'SELECT id, column_name FROM table_columns WHERE table_id = ? AND column_name = ?',
|
|
[targetTableId, labelColumn]
|
|
);
|
|
if (!labelCol) {
|
|
return { label: '<unresolved>' };
|
|
}
|
|
// Cell value might be the related row id (numeric) or a base_id (string).
|
|
// Try numeric id first.
|
|
let relRow = null;
|
|
const asNum = Number(cellValue);
|
|
if (Number.isFinite(asNum) && String(asNum) === String(cellValue)) {
|
|
relRow = await dbGet(
|
|
'SELECT data FROM table_rows WHERE table_id = ? AND id = ?',
|
|
[targetTableId, asNum]
|
|
);
|
|
}
|
|
if (!relRow) {
|
|
relRow = await dbGet(
|
|
'SELECT data FROM table_rows WHERE table_id = ? AND base_id = ?',
|
|
[targetTableId, String(cellValue)]
|
|
);
|
|
}
|
|
if (!relRow) {
|
|
return { label: '<unresolved>' };
|
|
}
|
|
const parsed = safeJsonParse(relRow.data, {}) || {};
|
|
// table_rows.data is keyed by column id (string).
|
|
const label =
|
|
parsed[String(labelCol.id)] ??
|
|
parsed[labelCol.column_name] ??
|
|
'<unresolved>';
|
|
return { label: String(label) };
|
|
} catch {
|
|
return { label: '<unresolved>' };
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Helper: project a single cell value based on column type.
|
|
* - relation → { label } (uses async resolver, see caller)
|
|
* - file → { filename } (strip storage path)
|
|
* - everything else → raw value
|
|
*/
|
|
function projectFileCell(value) {
|
|
if (value === null || value === undefined) return null;
|
|
// File cells can be a string path, an object { url, filename }, or an array
|
|
// of such. Always reduce to a filename-only shape.
|
|
const toFilename = (v) => {
|
|
if (!v) return null;
|
|
if (typeof v === 'string') {
|
|
const parts = v.split('/').filter(Boolean);
|
|
return { filename: parts[parts.length - 1] || v };
|
|
}
|
|
if (typeof v === 'object') {
|
|
const fn = v.filename || v.name || (v.url ? String(v.url).split('/').filter(Boolean).pop() : null);
|
|
return { filename: fn || '<file>' };
|
|
}
|
|
return { filename: String(v) };
|
|
};
|
|
if (Array.isArray(value)) return value.map(toFilename).filter(Boolean);
|
|
return toFilename(value);
|
|
}
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/tree
|
|
* Returns the full project/table/dashboard/widget tree for a public space.
|
|
* Space-level `public_slug` is the visibility gate — once a space is public,
|
|
* everything inside is viewable read-only (no per-entity flags required).
|
|
*/
|
|
/**
|
|
* Gated loader: build the full project/table/dashboard/widget tree for a
|
|
* public space. Every layer is `is_public`-filtered and the System Data
|
|
* project is hard-banned. Returns `{ space, projects:[{tables,dashboards,
|
|
* widgets}] }`. Shared by the REST `/tree` route and the MCP mount's
|
|
* `list_tables` / `list_projects` handlers (NO-SIGNUP-MCP §B2).
|
|
*/
|
|
async function loadPublicTree(space) {
|
|
// Opt-out gate: per-entity is_public defaults TRUE (visible). Owners hide
|
|
// a specific entity by setting is_public=FALSE in its settings UI.
|
|
//
|
|
// AC15 (ADR-0060 §6): the per-space System Data project is hard-banned
|
|
// regardless of is_public. Filtered in JS (after fetching name+type) so
|
|
// the rule is centralized in `isSystemDataProject` instead of duplicated
|
|
// as a SQL WHERE clause that future schema renames could miss.
|
|
const projectsRaw = await dbAll(
|
|
`SELECT id, name, type, icon, order_index
|
|
FROM projects
|
|
WHERE space_id = ?
|
|
AND is_public IS NOT FALSE
|
|
ORDER BY order_index, id`,
|
|
[space.id]
|
|
);
|
|
const projects = projectsRaw.filter(p => !isSystemDataProject(p));
|
|
|
|
const spaceMeta = {
|
|
id: space.id,
|
|
name: space.name,
|
|
icon: space.icon,
|
|
public_slug: space.public_slug,
|
|
public_sidebar: readPublicSidebarPrefs(space.settings)
|
|
};
|
|
|
|
if (projects.length === 0) {
|
|
return { space: spaceMeta, projects: [] };
|
|
}
|
|
|
|
const projectIds = projects.map(p => p.id);
|
|
const placeholders = projectIds.map(() => '?').join(',');
|
|
|
|
const tables = await dbAll(
|
|
`SELECT id, project_id, COALESCE(display_name, name) AS name, icon, order_index
|
|
FROM universal_tables
|
|
WHERE project_id IN (${placeholders})
|
|
AND deleted_at IS NULL
|
|
AND is_public IS NOT FALSE
|
|
ORDER BY order_index, id`,
|
|
projectIds
|
|
);
|
|
|
|
const dashboards = await dbAll(
|
|
`SELECT id, project_id, name, icon, order_index
|
|
FROM dashboards
|
|
WHERE project_id IN (${placeholders})
|
|
AND is_public IS NOT FALSE
|
|
ORDER BY order_index, id`,
|
|
projectIds
|
|
);
|
|
|
|
let widgets = [];
|
|
if (dashboards.length > 0) {
|
|
const dashIds = dashboards.map(d => d.id);
|
|
const dashPlaceholders = dashIds.map(() => '?').join(',');
|
|
widgets = await dbAll(
|
|
`SELECT w.id, w.dashboard_id, w.title AS name, w.icon, w.order_index, d.project_id
|
|
FROM widgets w
|
|
JOIN dashboards d ON d.id = w.dashboard_id
|
|
WHERE w.dashboard_id IN (${dashPlaceholders})
|
|
AND w.is_public IS NOT FALSE
|
|
ORDER BY w.order_index, w.id`,
|
|
dashIds
|
|
);
|
|
}
|
|
|
|
// Group children by project_id.
|
|
const byProject = new Map();
|
|
for (const p of projects) {
|
|
byProject.set(p.id, {
|
|
id: p.id,
|
|
name: p.name,
|
|
icon: p.icon,
|
|
is_public: true,
|
|
tables: [],
|
|
dashboards: [],
|
|
widgets: []
|
|
});
|
|
}
|
|
for (const t of tables) {
|
|
const entry = byProject.get(t.project_id);
|
|
if (entry) entry.tables.push({ id: t.id, name: t.name, icon: t.icon });
|
|
}
|
|
for (const d of dashboards) {
|
|
const entry = byProject.get(d.project_id);
|
|
if (entry) entry.dashboards.push({ id: d.id, name: d.name, icon: d.icon });
|
|
}
|
|
for (const w of widgets) {
|
|
const entry = byProject.get(w.project_id);
|
|
if (entry) entry.widgets.push({ id: w.id, name: w.name, icon: w.icon });
|
|
}
|
|
|
|
return { space: spaceMeta, projects: Array.from(byProject.values()) };
|
|
}
|
|
|
|
router.get('/s/:slug/tree', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
success(res, await loadPublicTree(req.publicSpace));
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/tree error');
|
|
error(res, 'PUBLIC_TREE_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* Helper: load a table inside a public space.
|
|
* Space-level public_slug is the primary gate. Opt-out gates layered on top:
|
|
* the table or its parent project may set is_public=FALSE to hide.
|
|
* Returns null if the table doesn't exist, isn't in this space, or is hidden.
|
|
*/
|
|
async function loadPublicTable(spaceId, tableId) {
|
|
const table = await dbGet(
|
|
`SELECT ut.id, ut.project_id, ut.name, ut.display_name, ut.icon,
|
|
ut.is_public AS table_public,
|
|
p.space_id, p.is_public AS project_public,
|
|
p.name AS project_name, p.type AS project_type
|
|
FROM universal_tables ut
|
|
JOIN projects p ON p.id = ut.project_id
|
|
WHERE ut.id = ? AND ut.deleted_at IS NULL`,
|
|
[tableId]
|
|
);
|
|
if (!table) return null;
|
|
// AC15: HARD ban — tables inside System Data are never public.
|
|
if (isSystemDataProject({ name: table.project_name, type: table.project_type })) {
|
|
return null;
|
|
}
|
|
if (String(table.space_id) !== String(spaceId)) return null;
|
|
if (table.table_public === false) return null;
|
|
if (table.project_public === false) return null;
|
|
return table;
|
|
}
|
|
|
|
/**
|
|
* Helper: load a per-doc atoms table that is referenced from an already-gated
|
|
* public registry row. Atoms tables are internal storage of a public document
|
|
* — the parent widget + registry have already passed the public gates, so we
|
|
* only enforce same-space + System Data ban here, NOT `table.table_public`.
|
|
*
|
|
* Without this relaxation, per-doc atoms tables (which are seeded as
|
|
* is_public=false by default and never flipped) 404 even when their parent
|
|
* registry row is fully public. See bug: orphan-style data integrity issue
|
|
* where atoms render empty on /s/:slug despite registry list returning fine.
|
|
*/
|
|
async function loadPublicAtomsTable(spaceId, atomsTableId) {
|
|
const table = await dbGet(
|
|
`SELECT ut.id, ut.project_id, ut.name,
|
|
p.space_id, p.is_public AS project_public,
|
|
p.name AS project_name, p.type AS project_type
|
|
FROM universal_tables ut
|
|
JOIN projects p ON p.id = ut.project_id
|
|
WHERE ut.id = ? AND ut.deleted_at IS NULL`,
|
|
[atomsTableId]
|
|
);
|
|
if (!table) return null;
|
|
// AC15: HARD ban — tables inside System Data are never public.
|
|
if (isSystemDataProject({ name: table.project_name, type: table.project_type })) {
|
|
return null;
|
|
}
|
|
if (String(table.space_id) !== String(spaceId)) return null;
|
|
// Defence-in-depth: a registry row pointing at an atoms table inside a
|
|
// PRIVATE project (cross-project misconfig or crafted payload) must still
|
|
// 404. The relaxed gate is only on the per-table `is_public` opt-out.
|
|
if (table.project_public === false) return null;
|
|
return table;
|
|
}
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/tables/:tableId
|
|
* Returns metadata + whitelisted columns for a public table.
|
|
* 404 (not 403) if the table is not public or not in this space.
|
|
*/
|
|
router.get('/s/:slug/tables/:tableId', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const tableId = Number(req.params.tableId);
|
|
if (!Number.isFinite(tableId) || tableId <= 0) {
|
|
return notFound(res, 'Table');
|
|
}
|
|
const schema = await loadPublicTableSchema(req.publicSpace.id, tableId);
|
|
if (!schema) {
|
|
return notFound(res, 'Table');
|
|
}
|
|
success(res, schema);
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/tables/:tableId error');
|
|
error(res, 'PUBLIC_TABLE_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* Gated loader: table metadata + whitelisted, projected columns for a public
|
|
* table. Returns null (→ 404 / MCP not_found) when the table is not in this
|
|
* space, hidden, or under System Data. Shared by the REST `/tables/:tableId`
|
|
* route and the MCP `get_table_schema` handler so the column projection has a
|
|
* single source (NO-SIGNUP-MCP §B2).
|
|
*/
|
|
async function loadPublicTableSchema(spaceId, tableId) {
|
|
const table = await loadPublicTable(spaceId, tableId);
|
|
if (!table) return null;
|
|
const columns = (await fetchPublicColumns(tableId)).map(projectPublicColumn);
|
|
return {
|
|
table: {
|
|
id: table.id,
|
|
name: table.display_name || table.name,
|
|
icon: table.icon
|
|
},
|
|
columns
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Helper: clamp limit/offset for the public surface.
|
|
* limit → [1, 500], default 50
|
|
* offset → [0, ∞), default 0
|
|
*/
|
|
function clampPagination(rawLimit, rawOffset, { defLimit = 50, maxLimit = 500 } = {}) {
|
|
const limitN = Number(rawLimit);
|
|
const offsetN = Number(rawOffset);
|
|
const limit = Number.isFinite(limitN)
|
|
? Math.max(1, Math.min(maxLimit, Math.floor(limitN)))
|
|
: defLimit;
|
|
const offset = Number.isFinite(offsetN) && offsetN > 0 ? Math.floor(offsetN) : 0;
|
|
return { limit, offset };
|
|
}
|
|
|
|
/**
|
|
* Helper: fetch a page of public rows for a table.
|
|
*
|
|
* Same scrubbing as the table-viewer endpoint: column whitelist via
|
|
* `isColumnPublic`, relation cells resolved to `{ label }`, file cells
|
|
* reduced to `{ filename }`. Used by both `/tables/:id/rows` and
|
|
* `/widgets/:id/data` so the two surfaces can never diverge.
|
|
*
|
|
* Caller is responsible for the public-access gates on the table itself.
|
|
*/
|
|
async function fetchPublicColumns(tableId) {
|
|
const allColumns = await dbAll(
|
|
`SELECT id, column_name, display_name, type, config, options, order_index
|
|
FROM table_columns
|
|
WHERE table_id = ?
|
|
ORDER BY order_index, id`,
|
|
[tableId]
|
|
);
|
|
return allColumns.filter(isColumnPublic);
|
|
}
|
|
|
|
/**
|
|
* Project a single raw table_rows row through the public column whitelist —
|
|
* relation cells → { label }, file cells → { filename }, everything else raw.
|
|
* Shared by the paginated reader and the single-row reader so both surfaces
|
|
* scrub identically.
|
|
*/
|
|
async function projectPublicRow(row, publicColumns) {
|
|
const parsed = safeJsonParse(row.data, {}) || {};
|
|
const projected = {};
|
|
for (const col of publicColumns) {
|
|
const key = String(col.id);
|
|
const altKey = col.column_name;
|
|
const value = parsed[key] !== undefined ? parsed[key] : parsed[altKey];
|
|
const cfg = parseColumnConfig(col);
|
|
if (cfg.relation && cfg.relation.enabled) {
|
|
// eslint-disable-next-line no-await-in-loop
|
|
projected[col.column_name] = await resolveRelationLabel(cfg.relation, value);
|
|
} else if (col.type === 'file' || col.type === 'image' || col.type === 'attachment') {
|
|
projected[col.column_name] = projectFileCell(value);
|
|
} else {
|
|
projected[col.column_name] = value ?? null;
|
|
}
|
|
}
|
|
return {
|
|
id: row.id,
|
|
base_id: row.base_id,
|
|
data: projected,
|
|
created_at: row.created_at,
|
|
updated_at: row.updated_at
|
|
};
|
|
}
|
|
|
|
async function fetchPublicRows(tableId, { limit, offset }) {
|
|
const publicColumns = await fetchPublicColumns(tableId);
|
|
|
|
const totalRow = await dbGet(
|
|
'SELECT COUNT(*) AS total FROM table_rows WHERE table_id = ?',
|
|
[tableId]
|
|
);
|
|
const total = Number(totalRow?.total) || 0;
|
|
|
|
if (publicColumns.length === 0) {
|
|
return { rows: [], total };
|
|
}
|
|
|
|
const rawRows = await dbAll(
|
|
`SELECT id, base_id, data, created_at, updated_at
|
|
FROM table_rows
|
|
WHERE table_id = ?
|
|
ORDER BY created_at DESC, id DESC
|
|
LIMIT ? OFFSET ?`,
|
|
[tableId, limit, offset]
|
|
);
|
|
|
|
const projectedRows = [];
|
|
for (const row of rawRows) {
|
|
// eslint-disable-next-line no-await-in-loop
|
|
projectedRows.push(await projectPublicRow(row, publicColumns));
|
|
}
|
|
return { rows: projectedRows, total };
|
|
}
|
|
|
|
/**
|
|
* Fetch a single public row by id, scrubbed through the same column whitelist.
|
|
* Caller is responsible for the public-access gate on the table itself
|
|
* (`loadPublicTable`). Returns null if the row id is not in this table.
|
|
*/
|
|
async function fetchPublicRowById(tableId, rowId) {
|
|
const id = Number(rowId);
|
|
if (!Number.isFinite(id) || id <= 0) return null;
|
|
const publicColumns = await fetchPublicColumns(tableId);
|
|
if (publicColumns.length === 0) return null;
|
|
const row = await dbGet(
|
|
`SELECT id, base_id, data, created_at, updated_at
|
|
FROM table_rows WHERE table_id = ? AND id = ?`,
|
|
[tableId, id]
|
|
);
|
|
if (!row) return null;
|
|
return projectPublicRow(row, publicColumns);
|
|
}
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/tables/:tableId/rows?limit=50&offset=0
|
|
* Returns paginated rows containing only whitelisted columns.
|
|
* 404 if the table is not public or not in this space.
|
|
* `limit` clamps to [1, 500]; `offset` clamps to [0, ∞).
|
|
*/
|
|
router.get('/s/:slug/tables/:tableId/rows', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const tableId = Number(req.params.tableId);
|
|
if (!Number.isFinite(tableId) || tableId <= 0) {
|
|
return notFound(res, 'Table');
|
|
}
|
|
|
|
const table = await loadPublicTable(space.id, tableId);
|
|
if (!table) {
|
|
return notFound(res, 'Table');
|
|
}
|
|
|
|
const { limit, offset } = clampPagination(req.query.limit, req.query.offset);
|
|
const { rows, total } = await fetchPublicRows(tableId, { limit, offset });
|
|
success(res, { rows, total });
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/tables/:tableId/rows error');
|
|
error(res, 'PUBLIC_TABLE_ROWS_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
// ============================================================
|
|
// ADR-0060 §Fat-P5 — Public widget endpoints (projects/dashboards/widgets/data)
|
|
//
|
|
// Surface: separate `/public/s/:slug/...` series, NOT auth-bypass on the
|
|
// internal `/widgets/...` routes. Every gate (entity is_public, parent
|
|
// dashboard/project public, space match, preset whitelist, table FK
|
|
// reachability) is checked before any row data is touched. 404 instead of
|
|
// 403 to avoid leaking the existence of private entities.
|
|
// ============================================================
|
|
|
|
/**
|
|
* AC15 (ADR-0060 §6): the per-space "System Data" project — created by
|
|
* `backend/services/system-tables-creator/space-setup.js` with
|
|
* name='System Data' and type='system_data' — holds Variables, Storage
|
|
* Providers and other sensitive aggregates (per ADR-0024) and MUST be
|
|
* hard-banned from the public surface regardless of is_public.
|
|
*
|
|
* The check runs BEFORE any is_public gate so an owner accidentally flipping
|
|
* `is_public=true` on a system project cannot leak. The match is broad on
|
|
* purpose (type='system_data', legacy 'system', name='System Data') so
|
|
* future renames or partial creates still fail closed.
|
|
*
|
|
* NOTE on identifier: the spec referenced `projects.slug` but no such
|
|
* column exists in this schema — `type` is the canonical discriminator set
|
|
* by space-setup.js. Hence the type+name match here.
|
|
*
|
|
* Callers must SELECT both `name` and `type` columns when fetching the row
|
|
* (or, for JOINed loaders, alias them as project_name/project_type and pass
|
|
* a synthetic `{ name, type }` view).
|
|
*/
|
|
function isSystemDataProject(projectRow) {
|
|
if (!projectRow) return false;
|
|
const t = projectRow.type;
|
|
if (t === 'system_data' || t === 'system') return true;
|
|
if (projectRow.name === 'System Data') return true;
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Helper: load a project inside a public space.
|
|
* Returns null if the project doesn't exist, isn't in this space, has
|
|
* is_public=FALSE, or is the per-space System Data project (AC15).
|
|
*/
|
|
async function loadPublicProject(spaceId, projectId) {
|
|
const row = await dbGet(
|
|
`SELECT id, space_id, name, type, icon, description, theme_primary,
|
|
is_public, primary_table_id
|
|
FROM projects WHERE id = ?`,
|
|
[projectId]
|
|
);
|
|
if (!row) return null;
|
|
// AC15: HARD ban — runs BEFORE is_public so accidental flips can't leak.
|
|
if (isSystemDataProject(row)) return null;
|
|
if (String(row.space_id) !== String(spaceId)) return null;
|
|
if (row.is_public === false) return null;
|
|
return row;
|
|
}
|
|
|
|
/**
|
|
* Helper: load a dashboard inside a public space.
|
|
* Verifies the dashboard, its project, and the project's space match the
|
|
* public space. Returns null otherwise.
|
|
*/
|
|
async function loadPublicDashboard(spaceId, dashboardId) {
|
|
const row = await dbGet(
|
|
`SELECT d.id, d.name, d.icon, d.project_id,
|
|
d.is_public AS dash_public,
|
|
p.space_id, p.is_public AS project_public,
|
|
p.name AS project_name, p.type AS project_type
|
|
FROM dashboards d
|
|
JOIN projects p ON p.id = d.project_id
|
|
WHERE d.id = ?`,
|
|
[dashboardId]
|
|
);
|
|
if (!row) return null;
|
|
// AC15: HARD ban — system_data project hides every descendant.
|
|
if (isSystemDataProject({ name: row.project_name, type: row.project_type })) {
|
|
return null;
|
|
}
|
|
if (String(row.space_id) !== String(spaceId)) return null;
|
|
if (row.dash_public === false) return null;
|
|
if (row.project_public === false) return null;
|
|
return row;
|
|
}
|
|
|
|
/**
|
|
* Helper: load a widget inside a public space.
|
|
* Walks widget → dashboard → project → space and refuses if any layer's
|
|
* is_public=FALSE, if it's a template, or if its preset is not in the
|
|
* whitelist. Returns the raw widget row when allowed.
|
|
*/
|
|
async function loadPublicWidget(spaceId, widgetId) {
|
|
const row = await dbGet(
|
|
`SELECT w.id, w.dashboard_id, w.widget_type, w.preset_name, w.title, w.icon,
|
|
w.config, w.position,
|
|
w.is_public AS widget_public, w.is_template,
|
|
d.project_id, d.is_public AS dash_public,
|
|
p.space_id, p.is_public AS project_public,
|
|
p.name AS project_name, p.type AS project_type
|
|
FROM widgets w
|
|
JOIN dashboards d ON d.id = w.dashboard_id
|
|
JOIN projects p ON p.id = d.project_id
|
|
WHERE w.id = ?`,
|
|
[widgetId]
|
|
);
|
|
if (!row) return null;
|
|
// AC15: HARD ban — system_data project hides every descendant.
|
|
if (isSystemDataProject({ name: row.project_name, type: row.project_type })) {
|
|
return null;
|
|
}
|
|
if (String(row.space_id) !== String(spaceId)) return null;
|
|
if (row.widget_public === false) return null;
|
|
if (row.dash_public === false) return null;
|
|
if (row.project_public === false) return null;
|
|
if (row.is_template === true) return null;
|
|
if (!isPresetAllowed(row.preset_name)) return null;
|
|
return row;
|
|
}
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/projects/:projectId
|
|
* Returns project metadata + the id of the project's default dashboard
|
|
* (if any). 404 if the project is not public or not in this space.
|
|
*/
|
|
router.get('/s/:slug/projects/:projectId', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const projectId = Number(req.params.projectId);
|
|
if (!Number.isFinite(projectId) || projectId <= 0) {
|
|
return notFound(res, 'Project');
|
|
}
|
|
const project = await loadPublicProject(space.id, projectId);
|
|
if (!project) {
|
|
return notFound(res, 'Project');
|
|
}
|
|
const dashboard = await dbGet(
|
|
`SELECT id FROM dashboards
|
|
WHERE project_id = ?
|
|
AND is_public IS NOT FALSE
|
|
ORDER BY is_default DESC, order_index, id
|
|
LIMIT 1`,
|
|
[projectId]
|
|
);
|
|
success(res, {
|
|
project: {
|
|
id: project.id,
|
|
name: project.name,
|
|
icon: project.icon,
|
|
description: project.description,
|
|
theme_primary: project.theme_primary
|
|
},
|
|
dashboard_id: dashboard?.id ?? null
|
|
});
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/projects/:projectId error');
|
|
error(res, 'PUBLIC_PROJECT_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/dashboards/:dashboardId
|
|
* Returns dashboard metadata + the list of whitelisted, public widgets it
|
|
* contains. 404 if the dashboard / its project is not public or not in this
|
|
* space. Non-whitelisted presets are silently filtered out (default-deny).
|
|
*/
|
|
router.get('/s/:slug/dashboards/:dashboardId', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const dashboardId = Number(req.params.dashboardId);
|
|
if (!Number.isFinite(dashboardId) || dashboardId <= 0) {
|
|
return notFound(res, 'Dashboard');
|
|
}
|
|
const dashboard = await loadPublicDashboard(space.id, dashboardId);
|
|
if (!dashboard) {
|
|
return notFound(res, 'Dashboard');
|
|
}
|
|
const presetList = Array.from(PUBLIC_PRESET_WHITELIST);
|
|
const placeholders = presetList.map(() => '?').join(',');
|
|
const widgets = await dbAll(
|
|
`SELECT id, title, icon, preset_name, position, order_index
|
|
FROM widgets
|
|
WHERE dashboard_id = ?
|
|
AND is_public IS NOT FALSE
|
|
AND is_template = false
|
|
AND preset_name IN (${placeholders})
|
|
ORDER BY order_index, id`,
|
|
[dashboardId, ...presetList]
|
|
);
|
|
const items = widgets.map(w => ({
|
|
id: w.id,
|
|
name: w.title,
|
|
icon: w.icon,
|
|
type: w.preset_name,
|
|
position: safeJsonParse(w.position, { x: 0, y: 0, w: 6, h: 4 })
|
|
}));
|
|
success(res, {
|
|
dashboard: {
|
|
id: dashboard.id,
|
|
name: dashboard.name,
|
|
icon: dashboard.icon
|
|
},
|
|
widgets: items,
|
|
widget_ids: items.map(i => i.id)
|
|
});
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/dashboards/:dashboardId error');
|
|
error(res, 'PUBLIC_DASHBOARD_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/widgets/:widgetId
|
|
* Returns scrubbed widget config for read-only public rendering.
|
|
* 404 if the widget is not in this space, has any non-public ancestor,
|
|
* is a template, its preset is not whitelisted, or its referenced
|
|
* `table_id` points at a private table (don't reveal existence of
|
|
* private tables via a widget).
|
|
*/
|
|
router.get('/s/:slug/widgets/:widgetId', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const widgetId = Number(req.params.widgetId);
|
|
if (!Number.isFinite(widgetId) || widgetId <= 0) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const widget = await loadPublicWidget(space.id, widgetId);
|
|
if (!widget) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
// FK gate: if the widget references a table, the table must be public too.
|
|
const refTableId = extractWidgetTableRef(widget.config);
|
|
if (refTableId != null) {
|
|
const table = await loadPublicTable(space.id, refTableId);
|
|
if (!table) return notFound(res, 'Widget');
|
|
}
|
|
const scrubbed = scrubWidgetConfig(widget);
|
|
if (!scrubbed) return notFound(res, 'Widget');
|
|
success(res, {
|
|
widget: {
|
|
...scrubbed,
|
|
icon: widget.icon,
|
|
position: safeJsonParse(widget.position, { x: 0, y: 0, w: 6, h: 4 })
|
|
}
|
|
});
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/widgets/:widgetId error');
|
|
error(res, 'PUBLIC_WIDGET_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/widgets/:widgetId/data?limit=50&offset=0
|
|
* Returns paginated rows for the widget's referenced table, scrubbed
|
|
* through the same column whitelist as `/tables/:id/rows`.
|
|
*
|
|
* 404 if the widget or its referenced table is not public. If the widget
|
|
* has no `table_id` reference, returns `{ rows:[], total:0 }` so the
|
|
* renderer can still mount an empty view (rather than leaking "this widget
|
|
* had a config issue" via a 404).
|
|
*/
|
|
router.get('/s/:slug/widgets/:widgetId/data', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const widgetId = Number(req.params.widgetId);
|
|
if (!Number.isFinite(widgetId) || widgetId <= 0) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const widget = await loadPublicWidget(space.id, widgetId);
|
|
if (!widget) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const refTableId = extractWidgetTableRef(widget.config);
|
|
if (refTableId == null) {
|
|
return success(res, { rows: [], total: 0, table_id: null });
|
|
}
|
|
const table = await loadPublicTable(space.id, refTableId);
|
|
if (!table) return notFound(res, 'Widget');
|
|
|
|
const { limit, offset } = clampPagination(req.query.limit, req.query.offset);
|
|
const { rows, total } = await fetchPublicRows(refTableId, { limit, offset });
|
|
success(res, { rows, total, table_id: refTableId });
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/widgets/:widgetId/data error');
|
|
error(res, 'PUBLIC_WIDGET_DATA_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
// ============================================================
|
|
// ADR-0060 P6/B — Public Documents Widget endpoints
|
|
//
|
|
// Widget-scoped read-only mirror of the DocumentsWidget data layer. Lets the
|
|
// F-track adapter mount the authoring widget under <PublicViewProvider> by
|
|
// swapping its apiClient for a publicApi dataSource that reads from these
|
|
// routes — same envelope shapes as the authenticated /tables/:id/rows /
|
|
// /tables/:id/rows/:id / /tables/:id/columns endpoints (modulo the public
|
|
// scrub).
|
|
//
|
|
// All endpoints share a single loader (`loadPublicDocumentsWidget`) that
|
|
// enforces: widget is a 'documents' preset, widget+dashboard+project+space
|
|
// all public, registry table belongs to the same public space, registry
|
|
// resolves to a real id. 404 on any failure to avoid existence-leak.
|
|
// ============================================================
|
|
|
|
/**
|
|
* Helper: resolve a documents widget + its registry table for a given public
|
|
* space. Returns `{ widget, registryTableId }` or null.
|
|
*
|
|
* Gates (any false → null, no info leak):
|
|
* - widget exists and walks widget → dashboard → project → space
|
|
* - widget.preset_name === 'documents'
|
|
* - widget.is_public / dashboard.is_public / project.is_public all NOT FALSE
|
|
* - widget.is_template === false
|
|
* - widget.config carries a positive integer registry_table_id /
|
|
* documents_table_id
|
|
* - the resolved registry table actually lives in this space (defends
|
|
* against cross-space widget configs).
|
|
*/
|
|
async function loadPublicDocumentsWidget(spaceId, widgetId) {
|
|
const row = await dbGet(
|
|
`SELECT w.id, w.dashboard_id, w.widget_type, w.preset_name, w.title, w.icon,
|
|
w.config, w.position,
|
|
w.is_public AS widget_public, w.is_template,
|
|
d.project_id, d.is_public AS dash_public,
|
|
p.space_id, p.is_public AS project_public,
|
|
p.name AS project_name, p.type AS project_type
|
|
FROM widgets w
|
|
JOIN dashboards d ON d.id = w.dashboard_id
|
|
JOIN projects p ON p.id = d.project_id
|
|
WHERE w.id = ?`,
|
|
[widgetId]
|
|
);
|
|
if (!row) return null;
|
|
// AC15: HARD ban — a documents widget hosted under System Data never serves.
|
|
if (isSystemDataProject({ name: row.project_name, type: row.project_type })) {
|
|
return null;
|
|
}
|
|
if (String(row.space_id) !== String(spaceId)) return null;
|
|
if (row.widget_public === false) return null;
|
|
if (row.dash_public === false) return null;
|
|
if (row.project_public === false) return null;
|
|
if (row.is_template === true) return null;
|
|
if (row.preset_name !== 'documents') return null;
|
|
|
|
const registryTableId = extractDocumentsRegistryTableId(row.config);
|
|
if (!registryTableId) return null;
|
|
|
|
// Cross-space gate: the registry table must belong to a project inside this
|
|
// same public space. Without this, a documents widget configured against a
|
|
// private space's registry would leak through the public widget id.
|
|
const registryTable = await dbGet(
|
|
`SELECT ut.id, ut.project_id, p.space_id, ut.is_public AS table_public,
|
|
p.is_public AS project_public
|
|
FROM universal_tables ut
|
|
JOIN projects p ON p.id = ut.project_id
|
|
WHERE ut.id = ? AND ut.deleted_at IS NULL`,
|
|
[registryTableId]
|
|
);
|
|
if (!registryTable) return null;
|
|
if (String(registryTable.space_id) !== String(spaceId)) return null;
|
|
if (registryTable.table_public === false) return null;
|
|
if (registryTable.project_public === false) return null;
|
|
|
|
return { widget: row, registryTableId };
|
|
}
|
|
|
|
/**
|
|
* Helper: resolve a single registry row by slug within a registry table.
|
|
* Returns the raw row (with parsed data) or null.
|
|
*
|
|
* Cross-space gate for the per-doc atoms table is applied by the caller
|
|
* (so a single DB hit suffices when only the registry row is needed).
|
|
*/
|
|
async function findDocRowBySlug(registryTableId, docSlug) {
|
|
const slugQuery = isPostgres()
|
|
? `SELECT id, base_id, data, created_at, updated_at
|
|
FROM table_rows
|
|
WHERE table_id = $1 AND data->>'slug' = $2`
|
|
: `SELECT id, base_id, data, created_at, updated_at
|
|
FROM table_rows
|
|
WHERE table_id = ? AND json_extract(data, '$.slug') = ?`;
|
|
const row = await dbGet(slugQuery, [registryTableId, docSlug]);
|
|
if (!row) return null;
|
|
return { ...row, parsed: safeJsonParse(row.data, {}) || {} };
|
|
}
|
|
|
|
/**
|
|
* Project a single registry row to its public-safe shape.
|
|
* Mirrors the authenticated `/tables/:id/rows/:id` envelope so the F-track
|
|
* adapter does NOT need a separate transform path.
|
|
*/
|
|
function projectRegistryRow(row) {
|
|
return {
|
|
id: row.id,
|
|
base_id: row.base_id,
|
|
data: scrubRegistryRowData(safeJsonParse(row.data, {}) || {}),
|
|
created_at: row.created_at,
|
|
updated_at: row.updated_at,
|
|
};
|
|
}
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/widgets/:widgetId/documents
|
|
* List registry rows for a public documents widget.
|
|
*
|
|
* Envelope: `{ data: { rows, total } }` — same shape as the public
|
|
* /tables/:id/rows endpoint so the F-track adapter consumes both via one
|
|
* normalizer.
|
|
*
|
|
* Sort: same as authoring widget — `order_index ASC, created_at ASC` so the
|
|
* sidebar ordering matches what authors see.
|
|
*/
|
|
router.get('/s/:slug/widgets/:widgetId/documents', publicSpaceAccess, async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const widgetId = Number(req.params.widgetId);
|
|
if (!Number.isFinite(widgetId) || widgetId <= 0) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const resolved = await loadPublicDocumentsWidget(space.id, widgetId);
|
|
if (!resolved) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const { registryTableId } = resolved;
|
|
|
|
const orderBy = isPostgres()
|
|
? `COALESCE((data->>'order_index')::integer, 0), created_at`
|
|
: `CAST(json_extract(data, '$.order_index') AS INTEGER), created_at`;
|
|
|
|
const rawRows = await dbAll(
|
|
`SELECT id, base_id, data, created_at, updated_at
|
|
FROM table_rows WHERE table_id = ?
|
|
ORDER BY ${orderBy}`,
|
|
[registryTableId]
|
|
);
|
|
|
|
const rows = rawRows.map(projectRegistryRow);
|
|
success(res, { rows, total: rows.length, registry_table_id: registryTableId });
|
|
} catch (err) {
|
|
apiLogger.error({ err }, 'GET /public/s/:slug/widgets/:widgetId/documents error');
|
|
error(res, 'PUBLIC_DOCS_WIDGET_LIST_ERROR', err.message, 500);
|
|
}
|
|
});
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/widgets/:widgetId/documents/columns
|
|
* Registry columns for a public documents widget. Same shape as authenticated
|
|
* /tables/:id/columns (`data` is the columns array directly) so the F-track
|
|
* adapter swaps base URLs without reshaping.
|
|
*
|
|
* IMPORTANT: this route MUST be registered before /:docSlug below — Express
|
|
* matches in order, and `:docSlug = 'columns'` would otherwise catch the
|
|
* literal segment first.
|
|
*/
|
|
router.get(
|
|
'/s/:slug/widgets/:widgetId/documents/columns',
|
|
publicSpaceAccess,
|
|
async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const widgetId = Number(req.params.widgetId);
|
|
if (!Number.isFinite(widgetId) || widgetId <= 0) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const resolved = await loadPublicDocumentsWidget(space.id, widgetId);
|
|
if (!resolved) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const { registryTableId } = resolved;
|
|
|
|
const allColumns = await dbAll(
|
|
`SELECT id, column_name, display_name, type, config, options, order_index
|
|
FROM table_columns
|
|
WHERE table_id = ?
|
|
ORDER BY order_index, id`,
|
|
[registryTableId]
|
|
);
|
|
const columns = allColumns.filter(isColumnPublic).map(projectPublicColumn);
|
|
success(res, columns);
|
|
} catch (err) {
|
|
apiLogger.error(
|
|
{ err },
|
|
'GET /public/s/:slug/widgets/:widgetId/documents/columns error'
|
|
);
|
|
error(res, 'PUBLIC_DOCS_WIDGET_COLUMNS_ERROR', err.message, 500);
|
|
}
|
|
}
|
|
);
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/widgets/:widgetId/documents/:docSlug
|
|
* Single registry row by slug.
|
|
*
|
|
* Envelope: `{ data: { row } }` — mirrors authenticated /tables/:id/rows/:id.
|
|
*/
|
|
router.get(
|
|
'/s/:slug/widgets/:widgetId/documents/:docSlug',
|
|
publicSpaceAccess,
|
|
async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const widgetId = Number(req.params.widgetId);
|
|
const { docSlug } = req.params;
|
|
if (!Number.isFinite(widgetId) || widgetId <= 0) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const resolved = await loadPublicDocumentsWidget(space.id, widgetId);
|
|
if (!resolved) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const doc = await findDocRowBySlug(resolved.registryTableId, docSlug);
|
|
if (!doc) {
|
|
return notFound(res, 'Document');
|
|
}
|
|
success(res, { row: projectRegistryRow(doc) });
|
|
} catch (err) {
|
|
apiLogger.error(
|
|
{ err },
|
|
'GET /public/s/:slug/widgets/:widgetId/documents/:docSlug error'
|
|
);
|
|
error(res, 'PUBLIC_DOCS_WIDGET_DOC_ERROR', err.message, 500);
|
|
}
|
|
}
|
|
);
|
|
|
|
/**
|
|
* GET /api/v3/public/s/:slug/widgets/:widgetId/documents/:docSlug/atoms
|
|
* Per-doc atoms list (each doc has its own atoms table; registry row's
|
|
* `data.table_id` points at it).
|
|
*
|
|
* Envelope: `{ data: { rows, total } }`. Atoms are ordered by `order` ASC,
|
|
* id ASC (same as authoring). Cross-space gate: the per-doc atoms table
|
|
* must itself live inside this public space (a registry row that points at
|
|
* an out-of-space atoms table → 404).
|
|
*/
|
|
router.get(
|
|
'/s/:slug/widgets/:widgetId/documents/:docSlug/atoms',
|
|
publicSpaceAccess,
|
|
async (req, res) => {
|
|
try {
|
|
const space = req.publicSpace;
|
|
const widgetId = Number(req.params.widgetId);
|
|
const { docSlug } = req.params;
|
|
if (!Number.isFinite(widgetId) || widgetId <= 0) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const resolved = await loadPublicDocumentsWidget(space.id, widgetId);
|
|
if (!resolved) {
|
|
return notFound(res, 'Widget');
|
|
}
|
|
const doc = await findDocRowBySlug(resolved.registryTableId, docSlug);
|
|
if (!doc) {
|
|
return notFound(res, 'Document');
|
|
}
|
|
const atomsTableId = Number(doc.parsed?.table_id);
|
|
if (!Number.isFinite(atomsTableId) || atomsTableId <= 0) {
|
|
// Doc has no atoms table configured — return empty rather than 404
|
|
// so the F-track viewer can mount an empty doc cleanly.
|
|
return success(res, { rows: [], total: 0, table_id: null });
|
|
}
|
|
// Cross-space gate on atoms table — atoms are internal storage of a
|
|
// public document, so the per-table `is_public` gate is skipped (parent
|
|
// widget/registry already passed). Same-space + System Data ban still
|
|
// enforced via loadPublicAtomsTable.
|
|
const atomsTable = await loadPublicAtomsTable(space.id, atomsTableId);
|
|
if (!atomsTable) {
|
|
return notFound(res, 'Document');
|
|
}
|
|
|
|
const atomsOrderBy = buildOrderClause('order', {
|
|
dialect: isPostgres() ? 'pg' : 'sqlite', mode: 'coalesce', tieBreak: 'id',
|
|
});
|
|
const rawAtoms = await dbAll(
|
|
`SELECT id, base_id, data, created_at, updated_at
|
|
FROM table_rows WHERE table_id = ?
|
|
ORDER BY ${atomsOrderBy}`,
|
|
[atomsTableId]
|
|
);
|
|
const rows = rawAtoms.map(r => ({
|
|
id: r.id,
|
|
base_id: r.base_id,
|
|
data: scrubAtomRowData(safeJsonParse(r.data, {}) || {}),
|
|
created_at: r.created_at,
|
|
updated_at: r.updated_at,
|
|
}));
|
|
success(res, { rows, total: rows.length, table_id: atomsTableId });
|
|
} catch (err) {
|
|
apiLogger.error(
|
|
{ err },
|
|
'GET /public/s/:slug/widgets/:widgetId/documents/:docSlug/atoms error'
|
|
);
|
|
error(res, 'PUBLIC_DOCS_WIDGET_ATOMS_ERROR', err.message, 500);
|
|
}
|
|
}
|
|
);
|
|
|
|
/**
|
|
* Gated loader: list the whitelisted public widgets of a dashboard inside a
|
|
* public space. Walks dashboard → project → space via `loadPublicDashboard`
|
|
* (404-on-mismatch) then returns only whitelisted, non-template, public
|
|
* widgets. Returns null if the dashboard is not public / not in this space.
|
|
*/
|
|
async function listPublicDashboardWidgets(spaceId, dashboardId) {
|
|
const dashboard = await loadPublicDashboard(spaceId, dashboardId);
|
|
if (!dashboard) return null;
|
|
const presetList = Array.from(PUBLIC_PRESET_WHITELIST);
|
|
const placeholders = presetList.map(() => '?').join(',');
|
|
const widgets = await dbAll(
|
|
`SELECT id, title, icon, preset_name, order_index
|
|
FROM widgets
|
|
WHERE dashboard_id = ?
|
|
AND is_public IS NOT FALSE
|
|
AND is_template = false
|
|
AND preset_name IN (${placeholders})
|
|
ORDER BY order_index, id`,
|
|
[dashboardId, ...presetList]
|
|
);
|
|
return {
|
|
dashboard: { id: dashboard.id, name: dashboard.name, icon: dashboard.icon },
|
|
widgets: widgets.map(w => ({
|
|
id: w.id,
|
|
name: w.title,
|
|
icon: w.icon,
|
|
type: w.preset_name
|
|
}))
|
|
};
|
|
}
|
|
|
|
// ── Gated public read loaders shared with the MCP mount (NO-SIGNUP-MCP §B2) ──
|
|
// Every export below takes `spaceId` from the caller's pinned `req.publicSpace`
|
|
// and re-checks space membership, so a table/row/doc id outside the space
|
|
// resolves to null/404. `executeTool` is NOT on this path.
|
|
export {
|
|
loadPublicTable,
|
|
loadPublicTableSchema,
|
|
fetchPublicRows,
|
|
fetchPublicRowById,
|
|
clampPagination,
|
|
loadPublicTree,
|
|
loadPublicDocumentsRegistry,
|
|
loadPublicDocument,
|
|
listPublicDashboardWidgets,
|
|
};
|
|
|
|
export default router;
|