11
121. **Incremental Development**
13 - Start with database schema initialization
14 - Then build core API endpoints
15 - Finally develop the dashboard UI
312. **SQLite Usage**
32 - Use parameterized queries to prevent SQL injection
33 - Keep database schema simple to stay within size limits (10MB free, 1GB paid)
34 - Create new tables when modifying schema rather than using ALTER TABLE
35 - Consider occasional exports for backup
434. **Authentication**
44 - Generate strong API keys (at least 32 characters)
45 - Store API keys in the database, not as environment variables
46 - Use basic authentication for dashboard access
47 - Always validate API keys against the database
48
495. **UI Development**
113## Troubleshooting
114
115- **Database Issues**: Check SQLite query syntax and parameter types
116- **HTTP Val Errors**: Verify Request/Response format against Web API standards
117- **Authentication Problems**: Ensure API keys match and Authorization header is correctly formatted
57
58All API endpoints are secured using one of two methods:
591. API key authentication for app submissions (stored in database, not hardcoded)
602. Basic authentication for dashboard access
61
92 const data = await req.json();
93
94 // Store feedback in database
95 const feedback = await storeFeedback(apiKey, data);
96
10## Core Val Town Features Used
11
12### SQLite Database
13- Using `std/sqlite` for all database operations
14- Val Town provides up to 10MB storage on free tier, 1GB on paid tier
15- Database connection is managed by Val Town
16
17```ts
18import { sqlite } from "https://esm.town/v/std/sqlite";
19
20// Example database operation
21await sqlite.execute(`
22 SELECT * FROM apps WHERE api_key = ?
4alwaysApply: false
5---
6# Database Schema Design
7
8The AppFeedback.val project uses Val Town's SQLite database for storage. The database has the following schema:
9
10## Apps Table
33```
34
35## Database Operations
36
37Database interactions are encapsulated in dedicated modules:
38
39- `db/schema.ts` - Contains table definitions and constraints
40- `db/init.ts` - Handles database initialization on startup
41- `db/queries.ts` - Provides query functions with proper TypeScript types
42
43## Implementation Notes
44
45- All database operations are performed through Val Town's `std/sqlite` API
46- Schema includes proper constraints:
47 - Primary keys for uniquely identifying records
187 const { name, email: userEmail, comment } = await request.json();
188
189 // Save to database
190 await sqlite.execute(
191 `
1import { serveFile } from "https://esm.town/v/std/utils/index.ts";
2import { generateCode } from "./backend/generate-code.ts";
3import { createTables } from "./database/migrations.ts";
4import { createProject, getCode, getNextVersionNumber, insertVersion } from "./database/queries.ts";
5
6await createTables();
1import { serveFile } from "https://esm.town/v/std/utils/index.ts";
2import { generateCode } from "./backend/generate-code.ts";
3import { createTables } from "./database/migrations.ts";
4import { createProject, getCode, getNextVersionNumber, insertVersion } from "./database/queries.ts";
5
6await createTables();
1// Script to set up the telegram_chats table in SQLite
2// Run this script manually to create the database table
3
4export default async function setupTelegramChatDb() {
25 `);
26
27 return "Telegram chat database table created successfully.";
28 } catch (error) {
29 console.error("Error setting up telegram_chats table:", error);
13## Technical Architecture
14
15**⚠️ important caveat: the admin dashboard doesn't have auth! currently it just relies on security by obscurity of people not knowing the url to a private val. this is not very secure. if you fork this project and put sensitive data in a database you should think carefully about how to secure it.**
16
17Stevens has been designed with the utmost simplicity and extensibility, much like a well-organized household. At the heart of his operation lies a single "memories" table - a digital equivalent of a butler's meticulous records. This table serves as the foundation for all of Stevens' operations.
45- `dashboard`: the admin view for showing the memories notebook + visualizing imports
46- `dailyBriefing`: stuff related to sending a daily update via telegram
47- `dbUtils`: little one-off scripts for database stuff
48
49## Hiring your own Stevens
57- For the Google Calendar integration you'll need `GOOGLE_CALENDAR_ACCOUNT_ID` and `GOOGLE_CALENDAR_CALENDAR_ID`. See [these instuctions](https://www.val.town/v/stevekrouse/pipedream) for details.
58
59**important caveat: the admin dashboard doesn't have auth! currently it just relies on security by obscurity of people not knowing the url to a private val. this is not very secure, if you put sensitive data in a database you should think carefully about how to secure it.**
60
61Overall it's a simple enough project that I encourage you to just copy the ideas and run in your own direction rather than try to use it as-is.
4
5* `index.ts` - this is the **entrypoint** for this whole project
6* `database/` - this contains the code for interfacing with the app's SQLite database table
7
8## Hono
26## CRUD API Routes
27
28This app has two CRUD API routes: for reading and inserting into the messages table. They both speak JSON, which is standard. They import their functions from `/backend/database/queries.ts`. These routes are called from the React app to refresh and update data.
29
30## Errors