denchhq App Data Builder
This skill covers building data apps that interact with workspace objects and DuckDB. For core app structure and manifest basics, see the parent app-builder skill (app-builder/SKILL.md).
Objects CRUD API (objects permission required)
The window.dench.objects.* API provides full CRUD access to workspace objects (.object.yaml tables). Add objects to your manifest permissions:
permissions:
- objects
List Entries
const result = await dench.objects.list("people", {
search: "john",
filters: JSON.stringify([{ field: "Status", operator: "eq", value: "Active" }]),
sort: JSON.stringify({ field: "Full Name", direction: "asc" }),
page: 1,
pageSize: 50,
});
// Returns { object, fields, entries, totalCount, statuses }
Filter operators: eq, neq, contains, not_contains, starts_with, ends_with, gt, gte, lt, lte, is_empty, is_not_empty.
Get a Single Entry
const entry = await dench.objects.get("people", "entry_id_here");
// Returns { entry: { id, fields: { "Full Name": "...", ... }, created_at, updated_at } }
Create an Entry
const { entryId } = await dench.objects.create("people", {
"Full Name": "Jane Doe",
"Email Address": "jane@example.com",
Status: "Active",
});
Update an Entry
await dench.objects.update("people", entryId, {
Status: "Lead",
});
Delete an Entry
await dench.objects.delete("people", entryId);
Bulk Delete Entries
await dench.objects.bulkDelete("people", [id1, id2, id3]);
Get Object Schema
const schema = await dench.objects.getSchema("people");
// Returns { object, fields, statuses }
Get Relation Options
const options = await dench.objects.getOptions("people", "jane");
// Returns filtered list of entries matching query
Use this for building relation dropdowns and autocomplete fields that reference entries in other objects.
Database Access (database / database:write permissions)
The database permission grants read-only query access. The database:write permission grants full mutation access (INSERT, UPDATE, DELETE, CREATE TABLE, etc.).
permissions:
- database # SELECT queries only
- database:write # SELECT + mutations
Read Queries (database permission)
const result = await dench.db.query("SELECT * FROM objects");
// Returns { rows: [...] }
Mutations (database:write permission)
await dench.db.execute("INSERT INTO game_scores (game, score) VALUES ('my-game', 1500)");
await dench.db.execute("CREATE TABLE IF NOT EXISTS app_data (key TEXT PRIMARY KEY, value TEXT)");
await dench.db.execute("UPDATE app_data SET value = 'new' WHERE key = 'setting1'");
await dench.db.execute("DELETE FROM app_data WHERE key = 'old'");
DuckDB Workspace Schema
The workspace database uses an Entity-Attribute-Value (EAV) schema:
| Table | Columns | Description |
|---|---|---|
objects |
id, name, description, icon | Workspace object definitions |
fields |
id, object_id, name, type, required, position | Field definitions for each object |
entries |
id, object_id, created_at, updated_at | Row entries in each object |
entry_fields |
id, entry_id, field_id, value | Individual cell values (EAV) |
statuses |
id, object_id, name, color, position | Status options for status-type fields |
PIVOT views provide columnar access to object data:
SELECT * FROM v_people
-- Returns rows with columns like: id, "Full Name", "Email Address", "Status", ...
The view name is v_{object_name} where the object name is lowercased with spaces replaced by underscores.
Common Queries
// List all objects
const objects = await dench.db.query("SELECT * FROM objects");
// Get entries via PIVOT view
const people = await dench.db.query("SELECT * FROM v_people");
// Aggregate stats
const stats = await dench.db.query(`
SELECT o.name, COUNT(e.id) as count
FROM objects o LEFT JOIN entries e ON e.object_id = o.id
GROUP BY o.name ORDER BY count DESC
`);
// Get field definitions
const fields = await dench.db.query(
"SELECT * FROM fields WHERE object_id = (SELECT id FROM objects WHERE name = 'people')",
);
Creating App-Specific Tables
Apps can create their own tables for storing app-specific data. Always use CREATE TABLE IF NOT EXISTS for idempotency:
await dench.db.execute(`
CREATE TABLE IF NOT EXISTS app_settings (
key TEXT PRIMARY KEY, value TEXT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
Data Dashboards & Visualization
Chart.js Dashboard
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Chart Dashboard</title>
<script src="https://unpkg.com/chart.js@4/dist/chart.umd.min.js"></script>
<style>
* {
box-sizing: border-box;
margin: 0;
padding: 0;
}
body {
font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif;
padding: 24px;
transition:
background 0.2s,
color 0.2s;
}
body.dark {
--app-bg: #0f0f1a;
--app-surface: #1a1a2e;
--app-border: #2a2a45;
--app-text: #e8e8f0;
--app-text-muted: #8888a8;
--app-accent: #6366f1;
background: var(--app-bg);
color: var(--app-text);
}
body.light {
--app-bg: #ffffff;
--app-surface: #f8f9fa;
--app-border: #e2e4e8;
--app-text: #1a1a2e;
--app-text-muted: #6b7280;
--app-accent: #6366f1;
background: var(--app-bg);
color: var(--app-text);
}
h1 {
font-size: 24px;
margin-bottom: 24px;
}
.chart-container {
position: relative;
height: 400px;
padding: 20px;
border-radius: 12px;
background: var(--app-surface);
border: 1px solid var(--app-border);
}
</style>
</head>
<body>
<h1>Object Entries</h1>
<div class="chart-container">
<canvas id="barChart"></canvas>
</div>
<script>
async function init() {
try {
const theme = await window.dench.app.getTheme();
document.body.className = theme;
} catch {
document.body.className = "dark";
}
try {
const result = await window.dench.db.query(`
SELECT o.name, COUNT(e.id) as entry_count
FROM objects o LEFT JOIN entries e ON e.object_id = o.id
GROUP BY o.name ORDER BY entry_count DESC
`);
const isDark = document.body.classList.contains("dark");
const textColor = isDark ? "#e8e8f0" : "#1a1a2e";
const gridColor = isDark ? "#2a2a4530" : "#e2e4e830";
const ctx = document.getElementById("barChart").getContext("2d");
new Chart(ctx, {
type: "bar",
data: {
labels: result.rows.map((r) => r.name),
datasets: [
{
label: "Entries",
data: result.rows.map((r) => r.entry_count),
backgroundColor: "#6366f180",
borderColor: "#6366f1",
borderWidth: 1,
borderRadius: 6,
},
],
},
options: {
responsive: true,
maintainAspectRatio: false,
plugins: {
legend: { display: false },
},
scales: {
y: {
beginAtZero: true,
grid: { color: gridColor },
ticks: { color: textColor },
},
x: {
grid: { display: false },
ticks: { color: textColor },
},
},
},
});
} catch (err) {
document.querySelector(".chart-container").innerHTML =
'<p style="color:#ef4444">Error: ' + err.message + "</p>";
}
}
init();
</script>
</body>
</html>
D3.js Visualization
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>D3 Visualization</title>
<script src="https://unpkg.com/d3@7/dist/d3.min.js"></script>
<style>
* {
box-sizing: border-box;
margin: 0;
padding: 0;
}
body {
font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif;
padding: 24px;
transition:
background 0.2s,
color 0.2s;
}
body.dark {
--app-bg: #0f0f1a;
--app-text: #e8e8f0;
--app-accent: #6366f1;
background: var(--app-bg);
color: var(--app-text);
}
body.light {
--app-bg: #ffffff;
--app-text: #1a1a2e;
--app-accent: #6366f1;
background: var(--app-bg);
color: var(--app-text);
}
h1 {
font-size: 24px;
margin-bottom: 24px;
}
#chart {
width: 100%;
}
.bar {
transition: opacity 0.2s;
}
.bar:hover {
opacity: 0.8;
}
.axis text {
fill: var(--app-text);
font-size: 12px;
}
.axis path,
.axis line {
stroke: var(--app-text);
opacity: 0.2;
}
</style>
</head>
<body>
<h1>Workspace Overview</h1>
<div id="chart"></div>
<script>
async function init() {
try {
const theme = await window.dench.app.getTheme();
document.body.className = theme;
} catch {
document.body.className = "dark";
}
try {
const result = await window.dench.db.query(`
SELECT o.name, COUNT(e.id) as count
FROM objects o LEFT JOIN entries e ON e.object_id = o.id
GROUP BY o.name ORDER BY count DESC
`);
const data = result.rows;
const margin = { top: 20, right: 20, bottom: 40, left: 60 };
const width = Math.min(window.innerWidth - 48, 800) - margin.left - margin.right;
const height = 400 - margin.top - margin.bottom;
const svg = d3
.select("#chart")
.append("svg")
.attr("width", width + margin.left + margin.right)
.attr("height", height + margin.top + margin.bottom)
.append("g")
.attr("transform", `translate(${margin.left},${margin.top})`);
const x = d3
.scaleBand()
.domain(data.map((d) => d.name))
.range([0, width])
.padding(0.3);
const y = d3
.scaleLinear()
.domain([0, d3.max(data, (d) => d.count) || 1])
.nice()
.range([height, 0]);
svg
.append("g")
.attr("class", "axis")
.attr("transform", `translate(0,${height})`)
.call(d3.axisBottom(x));
svg.append("g").attr("class", "axis").call(d3.axisLeft(y).ticks(5));
svg
.selectAll(".bar")
.data(data)
.join("rect")
.attr("class", "bar")
.attr("x", (d) => x(d.name))
.attr("y", (d) => y(d.count))
.attr("width", x.bandwidth())
.attr("height", (d) => height - y(d.count))
.attr("rx", 4)
.attr("fill", "#6366f1");
} catch (err) {
document.getElementById("chart").innerHTML =
'<p style="color:#ef4444">Error: ' + err.message + "</p>";
}
}
init();
</script>
</body>
</html>
CSS-Only Stat Cards
No charting library needed — use CSS grid and custom properties for simple metric displays:
<div class="stats-grid">
<div class="stat-card">
<div class="stat-label">Total Records</div>
<div class="stat-value" id="total">—</div>
<div class="stat-change positive">+12% this week</div>
</div>
<div class="stat-card">
<div class="stat-label">Active Users</div>
<div class="stat-value" id="active">—</div>
<div class="stat-change positive">+5 today</div>
</div>
<div class="stat-card">
<div class="stat-label">Objects</div>
<div class="stat-value" id="objects">—</div>
</div>
</div>
.stats-grid {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
gap: 16px;
padding: 24px;
}
.stat-card {
padding: 20px;
border-radius: 12px;
background: var(--app-surface);
border: 1px solid var(--app-border);
}
.stat-label {
font-size: 13px;
color: var(--app-text-muted);
margin-bottom: 8px;
}
.stat-value {
font-size: 36px;
font-weight: 700;
font-variant-numeric: tabular-nums;
}
.stat-change {
font-size: 12px;
margin-top: 4px;
}
.stat-change.positive {
color: var(--app-success);
}
.stat-change.negative {
color: var(--app-error);
}
Interactive Tools & Utilities
Form-Based Tools
Template for tools that collect input, process it, and display output:
<div class="tool-container">
<form id="tool-form">
<div class="field">
<label for="input">Input</label>
<textarea id="input" rows="6" placeholder="Paste your data here..."></textarea>
</div>
<button type="submit">Process</button>
<div id="output" class="output-box"></div>
</form>
</div>
.tool-container {
max-width: 720px;
margin: 0 auto;
padding: 24px;
}
.field {
margin-bottom: 16px;
}
.field label {
display: block;
font-size: 13px;
font-weight: 500;
color: var(--app-text-muted);
margin-bottom: 6px;
}
.field textarea,
.field input,
.field select {
width: 100%;
padding: 10px 12px;
border-radius: 8px;
border: 1px solid var(--app-border);
background: var(--app-surface);
color: var(--app-text);
font-size: 14px;
font-family: inherit;
resize: vertical;
}
.field textarea:focus,
.field input:focus {
outline: none;
border-color: var(--app-accent);
box-shadow: 0 0 0 3px color-mix(in srgb, var(--app-accent) 20%, transparent);
}
button[type="submit"] {
padding: 10px 20px;
border: none;
border-radius: 8px;
background: var(--app-accent);
color: white;
font-size: 14px;
font-weight: 500;
cursor: pointer;
transition: background 0.15s;
}
button[type="submit"]:hover {
background: var(--app-accent-hover);
}
.output-box {
margin-top: 16px;
padding: 16px;
border-radius: 8px;
background: var(--app-surface);
border: 1px solid var(--app-border);
font-family: "SF Mono", "Fira Code", monospace;
font-size: 13px;
white-space: pre-wrap;
max-height: 400px;
overflow: auto;
}
Kanban / Drag-and-Drop
Use SortableJS for draggable interfaces backed by workspace objects:
<script src="https://unpkg.com/sortablejs@1/Sortable.min.js"></script>
async function initKanban() {
const schema = await dench.objects.getSchema("tasks");
const statuses = schema.statuses || [];
const board = document.getElementById("board");
for (const status of statuses) {
const column = document.createElement("div");
column.className = "kanban-column";
column.dataset.status = status.name;
column.innerHTML = `
<div class="column-header" style="border-color: ${status.color}">
${status.name}
</div>
<div class="column-body" data-status="${status.name}"></div>
`;
board.appendChild(column);
}
const result = await dench.objects.list("tasks", { pageSize: 200 });
for (const entry of result.entries) {
const status = entry.fields["Status"] || statuses[0]?.name;
const body = board.querySelector(`.column-body[data-status="${status}"]`);
if (body) {
const card = document.createElement("div");
card.className = "kanban-card";
card.dataset.id = entry.id;
card.textContent = entry.fields["Title"] || entry.id;
body.appendChild(card);
}
}
document.querySelectorAll(".column-body").forEach((col) => {
Sortable.create(col, {
group: "tasks",
animation: 150,
ghostClass: "drag-ghost",
onEnd: async (evt) => {
const entryId = evt.item.dataset.id;
const newStatus = evt.to.dataset.status;
try {
await dench.objects.update("tasks", entryId, { Status: newStatus });
} catch (err) {
console.error("Failed to update status:", err);
}
},
});
});
}
Patterns
CRUD Form App Pattern
A complete pattern for a form that creates, reads, updates, and deletes entries in a workspace object:
let currentEntries = [];
let editingId = null;
async function loadEntries() {
const result = await dench.objects.list("tasks", { pageSize: 100 });
currentEntries = result.entries;
renderTable(result.entries);
}
function renderTable(entries) {
const tbody = document.getElementById("entries-body");
tbody.innerHTML = entries
.map(
(e) => `
<tr>
<td>${e.fields["Title"] || ""}</td>
<td>${e.fields["Status"] || ""}</td>
<td>${new Date(e.created_at).toLocaleDateString()}</td>
<td>
<button
<button
</td>
</tr>
`,
)
.join("");
}
async function createEntry(formData) {
try {
const { entryId } = await dench.objects.create("tasks", formData);
await loadEntries();
dench.ui.toast("Entry created", { type: "success" });
resetForm();
} catch (err) {
dench.ui.toast("Failed: " + err.message, { type: "error" });
}
}
async function updateEntry(id, formData) {
try {
await dench.objects.update("tasks", id, formData);
await loadEntries();
dench.ui.toast("Entry updated", { type: "success" });
resetForm();
} catch (err) {
dench.ui.toast("Failed: " + err.message, { type: "error" });
}
}
async function deleteEntry(id) {
if (!confirm("Delete this entry?")) return;
try {
await dench.objects.delete("tasks", id);
await loadEntries();
dench.ui.toast("Entry deleted", { type: "success" });
} catch (err) {
dench.ui.toast("Failed: " + err.message, { type: "error" });
}
}
function editEntry(id) {
const entry = currentEntries.find((e) => e.id === id);
if (!entry) return;
editingId = id;
document.getElementById("title").value = entry.fields["Title"] || "";
document.getElementById("status").value = entry.fields["Status"] || "";
document.getElementById("submit-btn").textContent = "Update";
}
function resetForm() {
editingId = null;
document.getElementById("task-form").reset();
document.getElementById("submit-btn").textContent = "Create";
}
document.getElementById("task-form").addEventListener("submit", async (e) => {
e.preventDefault();
const formData = {
Title: document.getElementById("title").value,
Status: document.getElementById("status").value,
};
if (editingId) {
await updateEntry(editingId, formData);
} else {
await createEntry(formData);
}
});
loadEntries();
Dashboard with Live Refresh
Pattern for an auto-refreshing dashboard that polls for updated data:
const REFRESH_INTERVAL = 30000; // 30 seconds
let refreshTimer = null;
async function loadDashboard() {
try {
const stats = await dench.db.query(`
SELECT o.name, COUNT(e.id) as count
FROM objects o LEFT JOIN entries e ON e.object_id = o.id
GROUP BY o.name ORDER BY count DESC
`);
renderStats(stats.rows);
document.getElementById("last-updated").textContent =
"Updated: " + new Date().toLocaleTimeString();
} catch (err) {
console.error("Refresh failed:", err);
}
}
function startAutoRefresh() {
loadDashboard();
refreshTimer = setInterval(loadDashboard, REFRESH_INTERVAL);
}
function stopAutoRefresh() {
if (refreshTimer) {
clearInterval(refreshTimer);
refreshTimer = null;
}
}
document.addEventListener("visibilitychange", () => {
if (document.hidden) {
stopAutoRefresh();
} else {
startAutoRefresh();
}
});
startAutoRefresh();
Full Example: Data Dashboard
A complete workspace dashboard app with stat cards, theme support, and live data from DuckDB.
.dench.yaml:
name: "Dashboard"
description: "Workspace overview dashboard"
icon: "layout-dashboard"
version: "1.0.0"
entry: "index.html"
runtime: "static"
permissions:
- database
index.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Workspace Dashboard</title>
<style>
* {
box-sizing: border-box;
margin: 0;
padding: 0;
}
body {
font-family:
-apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif;
line-height: 1.5;
-webkit-font-smoothing: antialiased;
padding: 24px;
transition:
background 0.2s,
color 0.2s;
}
body.dark {
--app-bg: #0f0f1a;
--app-surface: #1a1a2e;
--app-surface-hover: #252540;
--app-border: #2a2a45;
--app-text: #e8e8f0;
--app-text-muted: #8888a8;
--app-accent: #6366f1;
--app-success: #22c55e;
--app-warning: #f59e0b;
--app-error: #ef4444;
background: var(--app-bg);
color: var(--app-text);
}
body.light {
--app-bg: #ffffff;
--app-surface: #f8f9fa;
--app-surface-hover: #f0f1f3;
--app-border: #e2e4e8;
--app-text: #1a1a2e;
--app-text-muted: #6b7280;
--app-accent: #6366f1;
--app-success: #16a34a;
--app-warning: #d97706;
--app-error: #dc2626;
background: var(--app-bg);
color: var(--app-text);
}
.header {
display: flex;
align-items: center;
justify-content: space-between;
margin-bottom: 24px;
}
.header h1 {
font-size: 24px;
font-weight: 700;
}
.header .meta {
font-size: 13px;
color: var(--app-text-muted);
}
.stats-grid {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
gap: 16px;
margin-bottom: 32px;
}
.stat-card {
padding: 20px;
border-radius: 12px;
background: var(--app-surface);
border: 1px solid var(--app-border);
transition: background 0.15s;
}
.stat-card:hover {
background: var(--app-surface-hover);
}
.stat-label {
font-size: 13px;
color: var(--app-text-muted);
margin-bottom: 8px;
text-transform: uppercase;
letter-spacing: 0.5px;
}
.stat-value {
font-size: 36px;
font-weight: 700;
font-variant-numeric: tabular-nums;
}
.objects-table {
width: 100%;
border-collapse: collapse;
background: var(--app-surface);
border-radius: 12px;
overflow: hidden;
border: 1px solid var(--app-border);
}
.objects-table th {
text-align: left;
padding: 12px 16px;
font-size: 12px;
font-weight: 600;
text-transform: uppercase;
letter-spacing: 0.5px;
color: var(--app-text-muted);
border-bottom: 1px solid var(--app-border);
}
.objects-table td {
padding: 12px 16px;
font-size: 14px;
border-bottom: 1px solid var(--app-border);
}
.objects-table tr:last-child td {
border-bottom: none;
}
.objects-table tr:hover td {
background: var(--app-surface-hover);
}
.error-box {
padding: 16px;
background: color-mix(in srgb, var(--app-error) 10%, transparent);
border: 1px solid color-mix(in srgb, var(--app-error) 30%, transparent);
border-radius: 8px;
color: var(--app-error);
font-size: 14px;
}
.empty-state {
text-align: center;
padding: 48px 24px;
color: var(--app-text-muted);
font-size: 15px;
}
.loading {
text-align: center;
padding: 48px 24px;
color: var(--app-text-muted);
}
</style>
</head>
<body>
<div class="header">
<h1>Workspace Dashboard</h1>
<div class="meta" id="last-updated"></div>
</div>
<div class="stats-grid" id="stats"></div>
<div id="table-container">
<div class="loading">Loading workspace data...</div>
</div>
<script>
async function init() {
try {
const theme = await window.dench.app.getTheme();
document.body.className = theme;
} catch {
document.body.className = "dark";
}
await loadDashboard();
}
async function loadDashboard() {
try {
const result = await window.dench.db.query(`
SELECT o.name, o.description, COUNT(e.id) as entry_count
FROM objects o
LEFT JOIN entries e ON e.object_id = o.id
GROUP BY o.name, o.description
ORDER BY entry_count DESC
`);
const rows = result.rows || [];
const totalObjects = rows.length;
const totalEntries = rows.reduce((sum, r) => sum + (r.entry_count || 0), 0);
const statsEl = document.getElementById("stats");
statsEl.innerHTML = `
<div class="stat-card">
<div class="stat-label">Objects</div>
<div class="stat-value">${totalObjects}</div>
</div>
<div class="stat-card">
<div class="stat-label">Total Entries</div>
<div class="stat-value">${totalEntries.toLocaleString()}</div>
</div>
<div class="stat-card">
<div class="stat-label">Avg Entries / Object</div>
<div class="stat-value">${totalObjects ? Math.round(totalEntries / totalObjects) : 0}</div>
</div>
`;
const tableContainer = document.getElementById("table-container");
if (rows.length === 0) {
tableContainer.innerHTML =
'<div class="empty-state">No objects in workspace yet.</div>';
} else {
tableContainer.innerHTML = `
<table class="objects-table">
<thead>
<tr>
<th>Object</th>
<th>Description</th>
<th>Entries</th>
</tr>
</thead>
<tbody>
${rows
.map(
(r) => `
<tr>
<td><strong>${r.name}</strong></td>
<td style="color: var(--app-text-muted)">${r.description || "—"}</td>
<td>${r.entry_count || 0}</td>
</tr>
`,
)
.join("")}
</tbody>
</table>
`;
}
document.getElementById("last-updated").textContent =
"Updated " + new Date().toLocaleTimeString();
} catch (err) {
document.getElementById("stats").innerHTML = "";
document.getElementById("table-container").innerHTML =
'<div class="error-box">Error loading data: ' + err.message + "</div>";
}
}
init();
</script>
</body>
</html>