Recipe: Full CRUD App with Auth ​

import {DatabaseSettings, sql, sqlValue, TableAuthExtensionData, TableData, TableRulesExtensionData} from "teenybase"
import {authFields, baseFields, createdTrigger, updatedTrigger} from "teenybase/scaffolds/fields"

const userTable: TableData = {
    name: "users",
    autoSetUid: true,
    fields: [
        ...baseFields,  // id, created, updated
        ...authFields,   // username, email, email_verified, password, password_salt, name, avatar, role, meta
    ],
    indexes: [{fields: "role COLLATE NOCASE"}],
    extensions: [
        {
            name: "rules",
            // Public profiles: anyone can list non-private users. Admins see all.
            listRule: "(auth.uid == id) | auth.role ~ '%admin' | meta->>'$.pvt'!=true",
            viewRule: "(auth.uid == id) | auth.role ~ '%admin'",
            // Sign-up: anonymous users get 'guest' role. Admins can create non-superadmin users.
            createRule: "(auth.uid == null & role == 'guest') | (auth.role ~ '%admin' & role != 'superadmin')",
            // Users can update own profile but can't change role/meta. Admins can change roles (except promote to superadmin).
            updateRule: "(auth.uid == id & role == new.role & meta == new.meta) | (auth.role ~ '%admin' & new.role != 'superadmin' & (role != 'superadmin' | auth.role = 'superadmin'))",
            // Only admins can delete non-admin users.
            deleteRule: "auth.role ~ '%admin' & role !~ '%admin'",
        } as TableRulesExtensionData,
        {
            name: "auth",
            passwordType: "sha256",
            passwordCurrentSuffix: "Current",     // requires passwordCurrent field on password change
            passwordConfirmSuffix: "Confirm",      // requires passwordConfirm field on sign-up
            jwtSecret: "$JWT_SECRET_USERS",        // required — resolved from env at runtime
            jwtTokenDuration: 3 * 60 * 60,         // required — 3 hours
            maxTokenRefresh: 4,                     // required — total session: 12 hours
            emailTemplates: {
                verification: {
                    variables: {
                        message_title: 'Email Verification',
                        message_description: 'Welcome to {{APP_NAME}}. Click the button below to verify your email address.',
                        message_footer: 'If you did not request this, please ignore this email.',
                        action_text: 'Verify Email',
                        action_link: '{{APP_URL}}#/verify-email/{{TOKEN}}',
                    }
                },
                passwordReset: {
                    variables: {
                        message_title: 'Password Reset',
                        message_description: 'Click the button below to reset the password for your {{APP_NAME}} account.',
                        message_footer: 'If you did not request this, you can safely ignore this email.',
                        action_text: 'Reset Password',
                        action_link: '{{APP_URL}}#/reset-password/{{TOKEN}}',
                    }
                }
            }
        } as TableAuthExtensionData,
    ],
    triggers: [createdTrigger, updatedTrigger],
}

const itemsTable: TableData = {
    name: "notes",  // ← rename to your domain (bookmarks, recipes, tasks, etc.)
    autoSetUid: true,
    fields: [
        ...baseFields,
        {name: "owner_id", type: "relation", sqlType: "text", notNull: true, foreignKey: {table: "users", column: "id"}},
        {name: "title", type: "text", sqlType: "text", notNull: true},
        {name: "content", type: "editor", sqlType: "text", notNull: true},
        {name: "is_public", type: "bool", sqlType: "boolean", notNull: true, default: sqlValue(false)},
        {name: "slug", type: "text", sqlType: "text", unique: true, notNull: true, noUpdate: true},
        {name: "tags", type: "text", sqlType: "text"},
        {name: "meta", type: "json", sqlType: "json"},
        {name: "cover", type: "file", sqlType: "text"},
        {name: "views", type: "number", sqlType: "integer", noUpdate: true, noInsert: true, default: sqlValue(0)},
        {name: "archived", type: "bool", sqlType: "boolean", noInsert: true, default: sqlValue(false)},
        {name: "deleted_at", type: "date", sqlType: "timestamp", noInsert: true, default: sqlValue(null)},
    ],
    fullTextSearch: {
        fields: ["title", "content", "tags"],
        tokenize: "trigram"  // substring matching. Use "porter" for English word stemming.
    },
    indexes: [
        {fields: "owner_id"},
        {fields: "tags COLLATE NOCASE"},
        {fields: "is_public"},
        {fields: "archived"},
        {fields: "deleted_at"},
    ],
    extensions: [
        {
            name: "rules",
            viewRule: "(is_public = true & !deleted_at & !archived) | auth.role ~ '%admin' | (auth.uid != null & owner_id == auth.uid)",
            listRule: "(is_public & !deleted_at & !archived) | auth.role ~ '%admin' | (auth.uid != null & owner_id == auth.uid)",
            createRule: "auth.uid != null & owner_id == auth.uid",
            updateRule: "auth.uid != null & owner_id == auth.uid & owner_id = new.owner_id",
            deleteRule: "auth.role ~ '%admin' | (auth.uid != null & owner_id == auth.uid)",
        } as TableRulesExtensionData,
    ],
    triggers: [createdTrigger, updatedTrigger],
}

export default {
    tables: [userTable, itemsTable],
    appName: "My App",
    appUrl: "https://my-app--username.apps.teenybase.work", // your deployed worker URL
    jwtSecret: "$JWT_SECRET_MAIN",     // resolved from .prod.vars / .dev.vars

    // OAuth — add client IDs as env vars in .dev.vars / .prod.vars
    authProviders: [
        { name: 'google', clientId: '$GOOGLE_CLIENT_ID' },
    ],

    // Auth cookie — set for SSR frontends that need cookie-based auth
    authCookie: {
        name: 'app_auth',
        httpOnly: true,
        secure: false,   // set true in production (requires HTTPS)
        sameSite: 'Lax',
        path: '/',
    },

    // Actions — server-side logic callable via POST /api/v1/action/{name}
    actions: [
        {
            name: 'increment_view',
            description: 'Increment view count for an item by slug.',
            applyTableRules: false,
            params: { slug: 'string' },
            sql: {
                type: 'UPDATE',
                table: itemsTable.name,
                set: {views: sql`views + 1`},
                where: sql`slug = {:slug}`,
            },
        },
    ],

    // Email — for verification and password reset (optional, requires Mailgun or Resend)
    email: {
        from: "App Name <noreply@example.com>",
        variables: {
            company_name: "Company",
            company_copyright: "Company",
            company_address: "Company address",
            support_email: "support@example.com",
            company_url: "https://example.com",
        },
        mailgun: {
            MAILGUN_API_SERVER: "mail.example.com",
            MAILGUN_API_KEY: "$MAILGUN_API_KEY",
        },
        // Alternative: resend: { RESEND_API_KEY: '$RESEND_API_KEY' },
    },
} satisfies DatabaseSettings

import { $Database, $Env, OpenApiExtension, PocketUIExtension, D1Adapter, teenyHono } from 'teenybase/worker'
import config from 'virtual:teenybase'

type Env = $Env & { Bindings: CloudflareBindings }

const app = teenyHono<Env>(async (c) => {
    const db = new $Database(c, config, new D1Adapter(c.env.PRIMARY_DB))
    db.extensions.push(new OpenApiExtension(db, true))
    db.extensions.push(new PocketUIExtension(db))
    return db
})

app.get('/', async (c) => {
    // Query D1 directly for SSR — don't fetch your own worker URL
    const items = await c.env.PRIMARY_DB
        .prepare(`SELECT n.*, u.username FROM notes n
                  LEFT JOIN users u ON n.owner_id = u.id
                  WHERE n.is_public = 1 AND n.deleted_at IS NULL AND n.archived = 0
                  ORDER BY n.created DESC LIMIT 50`)
        .all()
    const rows = items.results || []

    return c.html(`<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>My App</title>
<style>body{font-family:system-ui;max-width:700px;margin:2rem auto;padding:0 1rem}</style>
</head><body>
<h1>My App</h1>
<div id="items">${rows.map((r: any) => `
  <div style="border-bottom:1px solid #eee;padding:1rem 0">
    <strong>${r.title}</strong> <small>by ${r.username || 'anon'}</small>
    ${r.tags ? '<div>' + r.tags.split(',').map((t: string) => '<code>' + t.trim() + '</code> ').join('') + '</div>' : ''}
  </div>`).join('')}
</div>
</body></html>`)
})

export default app