name

upnote

description

Use when working with UpNote data: reading, writing, organizing notes, tags, notebooks, or workspaces via the SQLite database. Keywords: upnote, notes, sqlite, notebook, tag, workspace, note-taking, getupnote

globs

**/upnote.sqlite3*

UpNote Skill — Direct SQLite Access

UpNote stores all data in a plain SQLite 3 database — no encryption, no passphrase. A script can read/write everything while the app is closed.

1. Find the Database

The DB is always named upnote.sqlite3. Location varies by OS:

OS	Path
Linux	`~/.config/UpNote/upnote.sqlite3`
macOS	`~/Library/Application Support/UpNote/upnote.sqlite3`
Windows	`%APPDATA%/UpNote/upnote.sqlite3`

Discovery auto-detection (any OS):

find / -name "upnote.sqlite3" -type f 2>/dev/null
# or faster:
fd upnote.sqlite3 / 2>/dev/null

Templates note: Templates are just notes with isTemplate=1. You can also create them directly in the DB (see section 9).

Required tools: Any tool that can open SQLite — the agent should probe the system to find what's available:

# Probe for any SQLite-capable tool
for tool in sqlite3 python3 python node nodejs php perl ruby lua java go cargo dotnet; do
  command -v "$tool" >/dev/null 2>&1 && echo "available: $tool"
done

Tool	How to access SQLite
sqlite3	direct CLI
python3 / python	built-in `import sqlite3`
node / nodejs	`better-sqlite3` or `sql.js` npm package
php	`new SQLite3()` (built-in extension)
perl	`DBD::SQLite` module
ruby	`gem install sqlite3`
lua	`lsqlite3`
java	`JDBC SQLite driver`
go	`github.com/mattn/go-sqlite3`
rust / cargo	`rusqlite` crate
dotnet	`Microsoft.Data.Sqlite`

The agent should:

Probe available runtimes.
Pick the most practical one based on what's installed and what the agent can generate code for.
Generate a temporary script or one-liner to execute SQL.
Prefer sqlite3 CLI when available (simplest, no code generation needed).
If nothing is found, tell the user: "I need SQLite access to work with your notes. Please install it: sudo apt install sqlite3 (Linux), brew install sqlite3 (macOS), or download from https://sqlite.org/download.html"

Important: The agent does NOT need to know the tool beforehand. It just needs to check what's on the system and adapt. This skill documents the schema and rules — the agent fills in the tooling dynamically.

2. Schema Overview

`notes` — the main table

Column	Type	Description
`id`	TEXT (PK)	UUID
`title`	TEXT	Note title
`text`	TEXT	Plain-text body
`html`	TEXT	Rich-text body (generated from text)
`summary`	TEXT	One-line summary (can set via AI)
`tagLinks`	TEXT	JSON array of tag IDs: `["tag-id-1","tag-id-2"]`
`notebookLinks`	TEXT	JSON array of notebook IDs: `["nb-id"]`
`space`	TEXT	Workspace UUID (which workspace the note lives in)
`bookmarked`	INTEGER	0 or 1
`pinned`	INTEGER	0 or 1
`hasTodo`	INTEGER	0 or 1
`trashed`	INTEGER	0 or 1
`deleted`	INTEGER	0 or 1 (1 = permanently deleted)
`synced`	INTEGER	1 = synced, 0 = pending sync (critical to set)
`revision`	INTEGER	Bump this on write
`updatedAt`	DOUBLE	Epoch ms
`createdAt`	DOUBLE	Epoch ms
`syncedAt`	DOUBLE	Epoch ms
`highlighted`	INTEGER	0 or 1
`shared` / `shareId`	—	Sharing state
`isTemplate`	INTEGER	0 or 1
`rtl`	INTEGER	0 or 1 for RTL text
`noteLinks`	TEXT	JSON array of linked note IDs
`fileIds`	TEXT	JSON array of attached file IDs
`firstImage`	TEXT	URL of first image

`notebooks` — notebooks/folders

Column	Type	Description
`id`	TEXT (PK)	UUID
`title`	TEXT	Display name
`parent`	TEXT	Parent notebook ID (nesting)
`childNotebooks`	TEXT	JSON array of child notebook IDs
`notes`	TEXT	JSON array of note IDs in this notebook (denormalized)
`space`	TEXT	Workspace UUID
`cover`	TEXT	Cover image key
`sortBy`	TEXT	Sort order
`inactive`	INTEGER	0 or 1
`locked`	INTEGER	0 or 1
`deleted`	INTEGER	0 or 1
`synced`	INTEGER	0 or 1

`tags` — note labels

Column	Type	Description
`id`	TEXT (PK)	UUID
`title`	TEXT	Tag name
`icon`	TEXT	Emoji icon
`notes`	TEXT	JSON array of note IDs (denormalized)
`space`	TEXT	Workspace UUID
`inactive`	INTEGER	0 or 1
`deleted`	INTEGER	0 or 1
`synced`	INTEGER	0 or 1
`sortBy`	TEXT	Sort order
`revision`	INTEGER	Bump on write

`organizers` — note↔notebook join table

Column	Type	Description
`id`	TEXT (PK)	Composite: `notebookId:noteId`
`noteId`	TEXT	Note UUID
`notebookId`	TEXT	Notebook UUID
`synced`	INTEGER	1 or 0
`deleted`	INTEGER	0 or 1
`createdAt`	DOUBLE	Epoch ms
`syncedAt`	DOUBLE	Epoch ms

`workspaces` — top-level containers

Column	Type	Description
`id`	TEXT (PK)	UUID
`name`	TEXT	Workspace name
`locked`	INTEGER	0 or 1
`deleted`	INTEGER	0 or 1
`synced`	INTEGER	0 or 1
`sortBy`	TEXT	Sort order

`files` — attached files

Column	Type	Description
`id`	TEXT (PK)	UUID
`name`	TEXT	Filename
`downloadURL`	TEXT	Remote URL
`tag`	TEXT	File category
`deleted`	INTEGER	0 or 1

3. Using the `sqlite3` CLI Directly

For quick queries and one-off changes, use the CLI directly. No Python needed.

Open the DB

sqlite3 ~/.config/UpNote/upnote.sqlite3
# or after finding it:
sqlite3 "$(fd upnote.sqlite3 / -tf 2>/dev/null | head -1)"

Once inside the sqlite3> prompt, run any SQL directly:

sqlite3> SELECT title, substr(text,1,80) FROM notes WHERE trashed=0 LIMIT 3;

One-liner queries

# Count notes
sqlite3 ~/.config/UpNote/upnote.sqlite3 "SELECT count(*) FROM notes WHERE trashed=0"

# List all notebook titles
sqlite3 ~/.config/UpNote/upnote.sqlite3 "SELECT id, title FROM notebooks WHERE deleted=0"

# Show untagged notes with id
sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  SELECT id, title FROM notes
  WHERE trashed=0 AND tagLinks = '[]'
  ORDER BY updatedAt DESC
"

One-liner writes

# Update a note's title
sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  UPDATE notes SET title='New Title', synced=0, revision=revision+1,
                   updatedAt=(strftime('%s','now')*1000)
  WHERE id='NOTE-UUID-HERE'
"

Use with a shell variable

DB=~/.config/UpNote/upnote.sqlite3
sqlite3 "$DB" "SELECT count(*) FROM notes WHERE trashed=0"
sqlite3 "$DB" "UPDATE notes SET synced=0, revision=revision+1 WHERE id='...'"

4. Rules for Safe Writing

★ Always backup before every edit

Assume your SQL will break the database. Always create a backup before any write operation:

DB=~/.config/UpNote/upnote.sqlite3
cp "$DB" "${DB}.backup.$(date +%s)"

Or backup just the affected rows — dump the JSON of notes/notebooks/tags you're changing so they can be restored individually:

sqlite3 "$DB" "
  SELECT json_object('id', id, 'title', title, 'tagLinks', tagLinks,
                     'notebookLinks', notebookLinks, 'space', space)
  FROM notes WHERE id IN ('id1', 'id2', ...)
" > /tmp/upnote_rollback_notes.json

If something goes wrong:

# Restore full DB backup
cp "${DB}.backup.$(date +%s)" "$DB"

# Or restore individual note
sqlite3 "$DB" "UPDATE notes SET title='...', tagLinks='...' WHERE id='...'"

★ Always dry-run, always ask confirmation first

No write to the DB happens without user approval. Every edit — even changing a title, adding a tag, or trashing a single note — must follow this sequence:

Backup — create a restore point.
Read current state — show the user what exists now.
Propose the change — show the exact sqlite3 commands or a clear before/after table.
Ask — "Shall I apply this?" Wait for explicit yes.
Execute — only after confirmation.

Small example:

Backup saved: upnote.sqlite3.backup.1712345678

Current:  "resume builders" (no notebook, no tags)
Proposed: → notebook "dev-tools", tag "resume"

SQL: UPDATE notes SET notebookLinks='["nb-id"]' ... WHERE id='...'
     INSERT INTO organizers ... 
     UPDATE notebooks SET notes=...

Apply? (y/n):

Always close UpNote before writing

If you write while UpNote is open, the app may overwrite your changes on next sync or crash because of a locked database.

Always set `synced = 0` on changes

This tells UpNote: "this has local changes, please upload them." If you set synced = 1, the app assumes the data is already synced and may ignore your changes.

Bump `revision` on the changed entity

UPDATE notes SET revision = revision + 1, synced = 0, updatedAt = (strftime('%s','now')*1000) WHERE id = ?;

Keep denormalized fields in sync

If you assign a note to a notebook, you must update 4 places:

notes.notebookLinks → set JSON array of notebook IDs (or append to it)
organizers → insert row (notebookId:noteId, noteId, notebookId, synced=0, deleted=0, createdAt, syncedAt)
notebooks.notes → append note ID to JSON array
notebooks.synced = 0, revision++

Similarly for tags — update both notes.tagLinks and tags.notes.

UUID format

UpNote uses standard v4 UUIDs. The app generates them, but you can use uuid.uuid4() in Python. The organizers.id format is notebookId:noteId (lowercase hex).

5. Read Operations

Get all active notes

SELECT id, title, substr(text, 1, 200) AS preview, space, tagLinks, notebookLinks,
       bookmarked, pinned, hasTodo, summary, createdAt, updatedAt
FROM notes
WHERE trashed = 0 AND deleted = 0
ORDER BY updatedAt DESC;

Get notes in a specific notebook

SELECT n.id, n.title, n.text
FROM notes n
JOIN organizers o ON n.id = o.noteId
WHERE o.notebookId = 'YOUR-NOTEBOOK-ID' AND o.deleted = 0 AND n.deleted = 0
ORDER BY n.updatedAt DESC;

Get notes with a specific tag

SELECT n.id, n.title
FROM notes n
WHERE n.tagLinks LIKE '%"tag-id-here"%' AND n.deleted = 0;

Get all notebooks

SELECT id, title, parent, notes, space FROM notebooks WHERE deleted = 0 ORDER BY title;

Get all tags

SELECT id, title, icon, notes FROM tags WHERE deleted = 0 ORDER BY title;

Count by workspace

SELECT w.name, w.id, COUNT(n.id) as note_count
FROM workspaces w
LEFT JOIN notes n ON n.space = w.id AND n.deleted = 0
WHERE w.deleted = 0
GROUP BY w.id;

6. Write Operations

All write operations shown in both sqlite3 CLI and Python. Pick whichever fits.

Create a new note

sqlite3 CLI:

NID=$(uuidgen | tr '[:upper:]' '[:lower:]')
NOW=$(date +%s)000
DB=~/.config/UpNote/upnote.sqlite3
sqlite3 "$DB" "
  INSERT INTO notes (id, title, text, html, summary, tagLinks, notebookLinks,
                     space, synced, revision, createdAt, updatedAt, syncedAt,
                     bookmarked, pinned, trashed, deleted, hasTodo)
  VALUES ('$NID', 'My Title', 'Hello world', '<p>Hello world</p>', '',
          '[]', '[]', '', 0, 1, $NOW, $NOW, $NOW, 0, 0, 0, 0, 0);
"

Python:

import uuid, time
note_id = str(uuid.uuid4())
now_ms = int(time.time() * 1000)
db.execute("""
    INSERT INTO notes (id, title, text, html, summary, tagLinks, notebookLinks,
                       space, synced, revision, createdAt, updatedAt, syncedAt,
                       bookmarked, pinned, trashed, deleted, hasTodo)
    VALUES (?, ?, ?, ?, ?, '[]', '[]',
            ?, 0, 1, ?, ?, ?,
            0, 0, 0, 0, 0)
""", (note_id, title, text, html_text, summary, space_id, now_ms, now_ms, now_ms))

Update note title/summary

sqlite3 CLI:

DB=~/.config/UpNote/upnote.sqlite3
sqlite3 "$DB" "
  UPDATE notes SET title='New Title', summary='AI summary here',
                   synced=0, revision=revision+1,
                   updatedAt=(strftime('%s','now')*1000)
  WHERE id='NOTE-UUID-HERE'
"

Python:

db.execute("""
    UPDATE notes SET title = ?, summary = ?, synced = 0, revision = revision + 1, updatedAt = ?
    WHERE id = ?
""", (new_title, new_summary, int(time.time()*1000), note_id))

Assign a note to a notebook (4 places)

sqlite3 CLI (script):

NID="note-uuid"
NBID="notebook-uuid"
NOW=$(date +%s)000
DB=~/.config/UpNote/upnote.sqlite3

# 1. Append notebook to note's notebookLinks
LINKS=$(sqlite3 "$DB" "SELECT notebookLinks FROM notes WHERE id='$NID'")
# remove trailing ], append, re-close
NEW_LINKS="${LINKS%]},\"$NBID\"]"
sqlite3 "$DB" "UPDATE notes SET notebookLinks='$NEW_LINKS', synced=0, revision=revision+1, updatedAt=$NOW WHERE id='$NID'"

# 2. Insert organizer link
sqlite3 "$DB" "INSERT OR IGNORE INTO organizers (id, noteId, notebookId, synced, deleted, createdAt, syncedAt) VALUES ('$NBID:$NID','$NID','$NBID',0,0,$NOW,$NOW)"

# 3. Append note to notebook's notes list
NOTES=$(sqlite3 "$DB" "SELECT notes FROM notebooks WHERE id='$NBID'")
NEW_NOTES="${NOTES%]},\"$NID\"]"
sqlite3 "$DB" "UPDATE notebooks SET notes='$NEW_NOTES', synced=0, revision=revision+1, updatedAt=$NOW WHERE id='$NBID'"

Python:

import json, time
now_ms = int(time.time() * 1000)

# 1. Add notebook to note's notebookLinks
row = db.execute("SELECT notebookLinks FROM notes WHERE id = ?", (note_id,)).fetchone()
links = json.loads(row[0]) if row and row[0] else []
if notebook_id not in links:
    links.append(notebook_id)
    db.execute("UPDATE notes SET notebookLinks = ?, synced = 0, revision = revision + 1, updatedAt = ? WHERE id = ?",
               (json.dumps(links), now_ms, note_id))

# 2. Insert organizer link
organizer_id = f"{notebook_id}:{note_id}"
db.execute("INSERT OR IGNORE INTO organizers (id, noteId, notebookId, synced, deleted, createdAt, syncedAt) VALUES (?, ?, ?, 0, 0, ?, ?)",
           (organizer_id, note_id, notebook_id, now_ms, now_ms))

# 3. Add note to notebook's notes list
row = db.execute("SELECT notes FROM notebooks WHERE id = ?", (notebook_id,)).fetchone()
nb_notes = json.loads(row[0]) if row and row[0] else []
if note_id not in nb_notes:
    nb_notes.append(note_id)
    db.execute("UPDATE notebooks SET notes = ?, synced = 0, revision = revision + 1, updatedAt = ? WHERE id = ?",
               (json.dumps(nb_notes), now_ms, notebook_id))

Remove a note from a notebook

Update all 4 places in reverse: remove from notebookLinks, set organizers.deleted=1, remove from notebooks.notes.

Add a tag to a note

sqlite3 CLI:

TID="tag-uuid" NID="note-uuid" NOW=$(date +%s)000 DB=~/.config/UpNote/upnote.sqlite3
# Update notes.tagLinks
sqlite3 "$DB" "UPDATE notes SET synced=0, revision=revision+1, updatedAt=$NOW WHERE id='$NID'"
sqlite3 "$DB" "
  UPDATE notes SET tagLinks = json_insert(coalesce(nullif(tagLinks,''),'[]'), '$[#]', '$TID')
  WHERE id='$NID'"
# Update tags.notes
sqlite3 "$DB" "
  UPDATE tags SET notes = json_insert(coalesce(nullif(notes,''),'[]'), '$[#]', '$NID'),
                  synced=0, revision=revision+1, updatedAt=$NOW
  WHERE id='$TID'"

Python:

# Update notes.tagLinks AND tags.notes — same pattern as notebook but no organizers table

Create a new tag

sqlite3 CLI:

TID=$(uuidgen | tr '[:upper:]' '[:lower:]')
NOW=$(date +%s)000
sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  INSERT INTO tags (id, title, icon, notes, space, revision, synced,
                    deleted, inactive, createdAt, updatedAt, syncedAt)
  VALUES ('$TID', 'my-tag', '', '[]', '', 1, 0, 0, 0, $NOW, $NOW, $NOW)
"

Python:

tag_id = str(uuid.uuid4())
now_ms = int(time.time() * 1000)
db.execute("""
    INSERT INTO tags (id, title, icon, notes, space, revision, synced,
                      deleted, inactive, createdAt, updatedAt, syncedAt)
    VALUES (?, ?, ?, '[]', ?, 1, 0, 0, 0, ?, ?, ?)
""", (tag_id, tag_name, icon, space_id, now_ms, now_ms, now_ms))

Create a new notebook

sqlite3 CLI:

NBID=$(uuidgen | tr '[:upper:]' '[:lower:]')
NOW=$(date +%s)000
sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  INSERT INTO notebooks (id, title, notes, childNotebooks, parent, space,
                         revision, synced, deleted, inactive, locked,
                         sortBy, cover, createdAt, updatedAt, syncedAt)
  VALUES ('$NBID', 'My Notebook', '[]', '[]', NULL, '',
          1, 0, 0, 0, 0, 'created', 'cover0', $NOW, $NOW, $NOW)
"

Python:

nb_id = str(uuid.uuid4())
now_ms = int(time.time() * 1000)
db.execute("""
    INSERT INTO notebooks (id, title, notes, childNotebooks, parent, space,
                           revision, synced, deleted, inactive, locked,
                           sortBy, cover, createdAt, updatedAt, syncedAt)
    VALUES (?, ?, '[]', '[]', NULL, ?,
            1, 0, 0, 0, 0,
            'created', 'cover0', ?, ?, ?)
""", (nb_id, title, space_id, now_ms, now_ms, now_ms))

Move a note to a different workspace

sqlite3 CLI:

sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  UPDATE notes SET space='NEW-WORKSPACE-UUID',
                   synced=0, revision=revision+1,
                   updatedAt=(strftime('%s','now')*1000)
  WHERE id='NOTE-UUID'
"

Python:

db.execute("UPDATE notes SET space = ?, synced = 0, revision = revision + 1, updatedAt = ? WHERE id = ?",
           (new_workspace_id, int(time.time()*1000), note_id))

Trash / restore / delete

sqlite3 CLI:

NOW=$(date +%s)000
# Trash
sqlite3 ~/.config/UpNote/upnote.sqlite3 "UPDATE notes SET trashed=1, synced=0, revision=revision+1, updatedAt=$NOW WHERE id='NOTE-UUID'"
# Restore
sqlite3 ~/.config/UpNote/upnote.sqlite3 "UPDATE notes SET trashed=0, synced=0, revision=revision+1, updatedAt=$NOW WHERE id='NOTE-UUID'"
# Permanently delete (app prefers deleted=1 over DELETE)
sqlite3 ~/.config/UpNote/upnote.sqlite3 "UPDATE notes SET deleted=1, synced=0, revision=revision+1, updatedAt=$NOW WHERE id='NOTE-UUID'"

Python:

# Trash:
db.execute("UPDATE notes SET trashed=1, synced=0, revision=revision+1, updatedAt=? WHERE id=?", (now_ms, note_id))
# Restore:
db.execute("UPDATE notes SET trashed=0, synced=0, revision=revision+1, updatedAt=? WHERE id=?", (now_ms, note_id))
# Permanently delete:
db.execute("UPDATE notes SET deleted=1, synced=0, revision=revision+1, updatedAt=? WHERE id=?", (now_ms, note_id))

7. AI Organization — Interactive Workflow

When the user asks "organize my notes", follow the same dry-run + confirm flow from section 4. Do NOT silently execute.

7.1 First, assess the current state

DB=~/.config/UpNote/upnote.sqlite3
echo "=== NOTE COUNT ==="
sqlite3 "$DB" "SELECT count(*) FROM notes WHERE trashed=0 AND deleted=0"
echo "=== EXISTING NOTEBOOKS ==="
sqlite3 "$DB" "SELECT id, title FROM notebooks WHERE deleted=0 ORDER BY title"
echo "=== EXISTING TAGS ==="
sqlite3 "$DB" "SELECT id, title, icon FROM tags WHERE deleted=0 ORDER BY title"
echo "=== WORKSPACES ==="
sqlite3 "$DB" "SELECT id, name FROM workspaces WHERE deleted=0"
echo "=== NOTES WITH MISSING TITLES ==="
sqlite3 "$DB" "SELECT id, substr(text,1,60) FROM notes WHERE trashed=0 AND deleted=0 AND (title IS NULL OR title = '')"
echo "=== NOTES WITH POOR TITLES (URLs as titles, single words, etc) ==="
sqlite3 "$DB" "SELECT id, title FROM notes WHERE trashed=0 AND deleted=0 AND (title LIKE 'http%' OR title LIKE 'https%' OR length(title) < 5)"

7.2 Ask the user for their preferences

Always ask before any change. Suggesting is OK — creating is not without approval.

"Should I use existing notebooks/workspaces and fit notes into them, or generate new ones?"
- If "existing" — analyze what notebooks/workspaces exist and categorize notes into them. If a note doesn't fit any existing one, suggest a new one but ask: "Note 'X' doesn't fit any existing notebook. Should I create a new one or leave it unassigned?"
- If "generate" — propose the full structure (workspaces + notebooks) and get confirmation before creating anything.
"Same for tags — use existing ones or create new ones?"
- If "existing" — match notes to current tags, ask about notes that don't fit.
- If "generate" — propose tags per note and ask for approval.
"Should I fix titles that are missing, URLs, or poorly named?"
- Show the list of problematic titles from 7.1.
- Ask for each: "Note starting with '...' — suggest a new title?"
- Or ask: "Fix all automatically and I'll review?"
"Any notes I should skip or exclude?"
- Let them specify by title keywords or note IDs.
Template suggestions during note creation
- If a note looks like it'll be repeated (structured meeting notes, daily/weekly logs, project tracker, etc.), suggest: "This looks like a recurring note. Want me to make it a template so you can reuse it?"
- Let the user decide — don't auto-create.
- If the user agrees, keep the template focused. Split big templates into multiple smaller ones. A giant template with 10+ sections is distracting. For example, "College Subject Tracker" could be split into: "Subject Overview", "Grades Tracker", "Study Schedule" — each a standalone template the user mixes and matches.

The rule: you can suggest anything, but never create/modify/delete workspaces, notebooks, tags, or notes without the user saying yes first.

7.3 Present a dry-run plan before executing

Show the user a preview table like this before writing anything:

## Proposed Organization Plan

| Note | → Workspace | → Notebook | → Tags | → New Title |
|---|---|---|---|---|
| resume builders | Code | dev-tools | resume, tools | — |
| https://... | Master | bookmarks | reference | Resume Builders List |
| (empty title) | Master | inbox | — | Weekly Groceries |

Then ask: "Does this look good? Shall I apply it?"

7.4 Apply after confirmation

Once confirmed, execute all the SQL writes. Use a transaction so it's atomic:

sqlite3 CLI:

DB=~/.config/UpNote/upnote.sqlite3

sqlite3 "$DB" << 'EOSQL'
BEGIN TRANSACTION;

-- all updates here

COMMIT;
EOSQL

Python:

db.execute("BEGIN TRANSACTION")
try:
    # all writes
    db.execute("COMMIT")
except:
    db.execute("ROLLBACK")
    raise

7.6 Workspace & notebook placement rules

When adding, deleting, or moving a note/template (whether during organization or standalone creation), follow this decision flow:

Always check what exists first — query workspaces and notebooks tables.
If only one workspace exists — use it directly. No need to ask.
If the user already stated which workspace/notebook they want → use that directly. No need to ask.
If the user didn't specify → show what's available and ask: "Which workspace? Which notebook inside it?"
If the user specified one that doesn't exist → say so and ask: "That doesn't exist. Available: [list]. Use one of these or create a new one?"

Same logic for notebooks within a workspace:

Only one notebook in the target workspace? Use it silently.
User specified one? Use it (validate it exists).
Not specified? Ask, unless there's only one.

Summarized as: check → one? use it → stated? use it → otherwise ask → missing? flag it, ask again.

7.5 Post-organization summary

After applying, show the user what changed:

sqlite3 "$DB" "
  SELECT 'Before: ' || 89 || ' active, ' || 6 || ' notebooks, ' || 0 || ' tags'
  UNION ALL
  SELECT 'After:  ' || count(*) || ' active, ' ||
         (SELECT count(*) FROM notebooks WHERE deleted=0) || ' notebooks, ' ||
         (SELECT count(*) FROM tags WHERE deleted=0) || ' tags'
  FROM notes WHERE trashed=0 AND deleted=0
"

8. Template Generation

Templates in UpNote are just notes with isTemplate=1. The app stores them in the same notes table. When the user picks "New note from template", UpNote clones the template note's content into a new note.

How templates are stored

-- List all existing templates
SELECT id, title, updatedAt FROM notes WHERE isTemplate = 1 AND deleted = 0;

-- Count them
SELECT count(*) FROM notes WHERE isTemplate = 1 AND deleted = 0;

✦ Key insight — UpNote is uniquely flexible

UpNote's formatting power comes from mixing. In most apps, collapsibles only work with headings, quotes only contain text, lists can't mix types, and content between collapsibles gets hidden. UpNote breaks all these rules. You can put anything inside anything — images in collapsible headers, tables inside quotes, checklists inside bullet lists, content between collapsible sections. This is what makes stunning templates possible.

Supported HTML formatting (verified from shared templates and real DB data)

Element	Usage	Example
Headings	`<h1>` through `<h5>`	`<h1>Title</h1>`
Paragraph	`<div>`	`<div>Text here</div>`
Bold	`<b>` or `<strong>`	`<b>bold</b>`
Italic	`<em>` or `<i>`	`<em>italic</em>`
Link	`<a href="URL">`	`<a href="https://...">link</a>`
Tag link	`<a data-upnote-tag="#name" href="upnote://x-callback-url/tag/view?tag=name">#name</a>`	Clickable tag
Unordered list	`<ul><li>`	`<ul><li>item</li></ul>`
Checklist	`<li data-checked="true">` / `<li data-checked="false">`	Interactive todo
Table	`<table><colgroup><col style="width:..."><tbody><tr><td>`	With `colspan` / `rowspan`
Table header cell	`<th>` inside `<tr>`	Bold header
Table colored bg	`class="shine-blue-bg"` on `<td>`	Blue background cell
Colored text	`<span class="shine-text-red">`	Red text
Image	`	Remote images
Horizontal rule	`<hr>`	Section divider
Blockquote	`<blockquote>`	Indented block (used heavily in templates)
Collapsible section	See structure below	Expandable/collapsible group
File attachment	`<a data-non-editable="true" href="http://localhost:9425/files/..." data-file-id="...">`	Attached file
Inline styles	`style="..."` on `<span>` or `<div>`	`text-align`, `white-space`

CSS classes that work in UpNote

These are the app's built-in theme classes (verified from shared templates):

Class	Effect	Can apply to
`shine-blue-bg`	Blue background	`td`, `div`, collapsible sections
`shine-green-bg`	Green background	`td`, `div`, collapsible sections
`shine-red-bg`	Red background	`td`, `div`, collapsible sections
`shine-yellow-bg`	Yellow background	`td`, `div`, collapsible sections
`shine-text-red`	Red text color	`span`, any inline
`shine-text-green`	Green text color	`span`, any inline
`shine-text-blue`	Blue text color	`span`, any inline
`shine-table-wrapper`	Wraps `<table>` for scroll	`div` around table
`shine-break-all`	Word break on long URLs	`a`, `div`
`shine-collapsible-section`	Collapsible container	`div`
`shine-section-title-wrapper`	Clickable header of collapsible	`div`
`shine-section-title`	Title area	`div` with `data-upnote-placeholder-key="title"`
`shine-section-title-inner`	Inner title wrapper	`div`
`shine-section-content`	Collapsible content body	`div` with `data-upnote-placeholder-key="content"`
`shine-section-content-inner`	Inner content wrapper	`div`
`shine-placeholder`	Placeholder region	`div`

Collapsible section structure

Basic:

<div class="shine-collapsible-section">
  <div class="shine-section-title-wrapper">
    <div class="shine-section-title shine-placeholder" data-upnote-placeholder-key="title">
      <div class="shine-section-title-inner">
        <h3><strong>Section Title</strong></h3>
      </div>
    </div>
  </div>
  <div class="shine-section-content shine-placeholder" data-upnote-placeholder-key="content">
    <div class="shine-section-content-inner">
      <div>Content here...</div>
    </div>
  </div>
</div>

With colored background (entire collapsible gets a tint):

<div class="shine-collapsible-section shine-blue-bg">

With image in title:

<div class="shine-section-title-inner">
  <h3><strong>Section Title</strong> <img src="..." width="20"></h3>
</div>

With checkbox in title:

<div class="shine-section-title-inner">
  <h3><strong>
    <span><input type="checkbox" data-upnote-checked="false"></span>
    Section Title
  </strong></h3>
</div>

With multi-colored text in title (status indicator):

<div class="shine-section-title-inner">
  <h3>
    <span class="shine-text-green">✓</span>
    <span class="shine-text-blue">Research Complete</span>
    <span style="color: #999;">— Mar 2026</span>
  </h3>
</div>

With quote in title (colored header bar):

<div class="shine-section-title-inner">
  <blockquote class="shine-blue-bg">
    <h3><strong>📌 Section Title</strong></h3>
  </blockquote>
</div>

Template with table + colors + collapsible (real example)

<h1>Cadastro de Pessoas</h1>
<div class="shine-table-wrapper">
<table>
  <colgroup>
    <col style="width: 180px;">
    <col style="width: 160px;">
  </colgroup>
  <tbody>
    <tr>
      <td class="shine-blue-bg"><h5>NOME SOCIAL</h5><blockquote><br></blockquote></td>
      <td class="shine-blue-bg" rowspan="1" colspan="1" style="text-align: center;"><h1>📷</h1></td>
    </tr>
    <tr>
      <td colspan="2" class="shine-blue-bg"><h5>NOME</h5><blockquote><br></blockquote></td>
    </tr>
  </tbody>
</table>
</div>

Color coding patterns (community conventions)

The community uses collapsible section title colors as status indicators:

Color	Meaning
Red	New issue, needs contact, urgent
Pink	Waiting for first reply
Purple	Ongoing discussion
Orange	Action needed by me, "to do"
Blue	Completed, resolved
Gray	Abandoned, finished but not resolved

Achieved by putting colored text (<span class="shine-text-red">, etc.) or colored emoji (🟩 🟧 🟦) inside the collapsible section title. When collapsed, the color shows in the title bar.

Common emoji used as visual markers

📷 photo       🎬 movie/TV     🏐 sports     🍴 cooking
📖 reading     🎮 games        🟩 work       🟧 errand/home
🟦 personal    🅳️ directory    ▍ section     ♣ placeholder
📌 location    🛒 shopping     😴 sleep      🌈 mood
😷 sick        💰 money        🔋 energy     🧹 chore

Rating system pattern

★★★★★  ★★★★  ★★★  ★★  ★
★★★★★½  (half star via ½ unicode)
★X±  (personal modifier, e.g. "X" for rewatch, "±" for mixed)

Limitations

❌ No embedded scripts or iframes
❌ No custom fonts — only the app's theme fonts
❌ shine-* CSS classes may vary by theme (light/dark)
❌ Background color classes may not exist for all colors

Design philosophy for templates

UpNote templates are structurally rich but visually minimal — they rely on:

Collapsible sections for nesting and grouping — the single most powerful feature
Tables with colored backgrounds for forms/trackers
Collapsible section title colors as status indicators (red/pink/purple/orange/blue/gray)
Blockquotes for indented info blocks
Checklists for interactivity
Tags (data-upnote-tag) for automatic categorization
Emoji as low-fi visual icons
Date placeholders for auto-filling
Horizontal rules (<hr>) for section breaks

Key insight from community templates: A great UpNote template is not about visual design—it's about information architecture. The best templates use collapsible sections + colored titles + emoji to create a dense, scannable document where you can see status at a glance without expanding anything.

9. Notebook Covers

Notebook covers in UpNote can be built-in (referenced by key) or custom (imported from an image file).

Built-in covers (set via SQL)

The notebooks.cover column stores a string key. Built-in keys look like cover28:

# See what cover a notebook currently uses
sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  SELECT id, title, cover FROM notebooks WHERE deleted=0 ORDER BY title;
"

# Set a built-in cover
sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  UPDATE notebooks SET cover='cover32', synced=0, revision=revision+1,
                       updatedAt=(strftime('%s','now')*1000)
  WHERE id='NOTEBOOK-UUID';
"

# Remove a cover (no cover)
sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  UPDATE notebooks SET cover=NULL, synced=0, revision=revision+1,
                       updatedAt=(strftime('%s','now')*1000)
  WHERE id='NOTEBOOK-UUID';
"

Known built-in covers from existing notebooks: cover15, cover20, cover28, cover32, cover36. The app may have more — these are just what's in use.

The agent can suggest cover numbers or let the user choose a number.

Custom covers with Python/Pillow

If Pillow is installed, the agent can generate a custom 110×135 PNG cover with any background color, text, and emoji:

from PIL import Image, ImageDraw, ImageFont
import os

def generate_upnote_cover(bg_color=(59, 130, 246),
                          icon="📁",
                          label="",
                          output_path="/tmp/upnote_cover.png"):
    img = Image.new("RGB", (110, 135), bg_color)
    draw = ImageDraw.Draw(img)

    # border
    draw.rectangle([3, 3, 106, 131], outline=(255, 255, 255), width=2)

    # icon (rendered via basic shapes or use an emoji font)
    # For a simple colored block with initial
    if label:
        # draw text-based label
        draw.text((55, 80), label, fill=(255, 255, 255))

    img.save(output_path)
    print(f"Cover saved to {output_path} ({img.size[0]}x{img.size[1]})")
    return output_path

# Example usage
generate_upnote_cover(
    bg_color=(34, 197, 94),   # green
    icon="📋",                 # project icon
    label="P",
    output_path="/tmp/cover_project.png"
)

The agent can offer to generate covers with:

Any hex or RGB background color
A text label (letter, emoji, or word truncated to fit)
A border style (white, colored, or none)
Matching the cover to the notebook's purpose (blue for work, green for personal, etc.)

The generated PNG is 110×135 pixels — the exact size UpNote expects. The user must import it through UpNote's notebook settings (right-click notebook → Change Cover → Choose from Files).

For best results, the agent should:

Ask the user what color and icon/label they want
Generate the 110×135 PNG
Save it to a known location (e.g., ~/Desktop/upnote_cover.png)
Tell the user: "Go to UpNote → right-click your notebook → Change Cover → Choose from Files → select this image."

Cover color palettes by notebook type

The agent can suggest colors based on notebook purpose:

Notebook type	Suggested color	Hex
Work / Projects	Blue	`#3B82F6`
Personal / Journal	Green	`#22C55E`
Finance / Budget	Emerald	`#10B981`
Health / Fitness	Rose	`#F43F5E`
Learning / Study	Purple	`#8B5CF6`
Travel	Amber	`#F59E0B`
Ideas / Creative	Pink	`#EC4899`
Tech / Dev	Cyan	`#06B6D4`
Home / Family	Orange	`#F97316`
Archive	Slate / Gray	`#64748B`

The built-in cover numbers (cover15, 20, 28, 32, 36) have different color schemes. The agent can list available options and let the user pick.

Create a template directly in the DB

⚠️ Critical: templates must have a space value set to a workspace UUID. Templates with space=NULL or space='' will NOT appear in any workspace's template section. The space must match a workspace ID from the workspaces table.

List workspaces:

sqlite3 ~/.config/UpNote/upnote.sqlite3 "SELECT id, name FROM workspaces WHERE deleted=0;"

Template creation with proper workspace:

NID=$(uuidgen | tr '[:upper:]' '[:lower:]')
NOW=$(date +%s)000
DB=~/.config/UpNote/upnote.sqlite3

sqlite3 "$DB" "
  INSERT INTO notes (id, title, text, html, summary, tagLinks, notebookLinks,
                     space, synced, revision, createdAt, updatedAt, syncedAt,
                     bookmarked, pinned, trashed, deleted, hasTodo, isTemplate)
  VALUES ('$NID', 'Meeting Notes', 
          'Meeting: {{date}}\n\n## Attendees\n-\n\n## Agenda\n-\n\n## Action Items\n-\n',
          '<h2>Meeting: {{date}}</h2><hr><div><b>Attendees</b></div><ul><li></li><li></li><li></li></ul><div><b>Agenda</b></div><ul><li></li><li></li></ul><div><b>Action Items</b></div><ul><li></li><li></li></ul>',
          '', '[]', '[]', 'WORKSPACE-UUID-HERE', 0, 1, $NOW, $NOW, $NOW,
          0, 0, 0, 0, 0, 1);
"

Formatting mixing rules — what goes inside what

This is the most important table in this skill. Understanding what can nest inside what is the key to stunning templates:

Container	Can contain
Collapsible title	Headings (h1-h5), bold, italic, colored text, images, links, checklists, blockquotes, tables, centered/right text, multi-colored text, multiple lines (Shift+Enter), emoji
Collapsible body	Absolutely anything — headings, tables, images, lists, quotes, other collapsibles, horizontal rules
Blockquote	Absolutely anything — headings, tables, colored text/cells, lists, images, other quotes (nested)
Bullet list	Text, bold, italic, links, images, tables, checklists, ordinals, nested bullets (any level), multi-line items (Shift+Enter)
Checklist	Text, bold, italic, bullets inside, ordinals inside, links
Table cell	Headings (h1-h5), lists (bullet, checklist, ordinal), blockquotes, colored text, colored backgrounds (shine-*-bg), images, links, colspan/rowspan
Between collapsibles	Fully visible! Unlike other apps, content between collapsibles is NOT hidden when collapsibles are closed

Key takeaway: UpNote has NO nesting restrictions. You can put a table inside a quote inside a collapsible title. You can put an image inside a list inside a table cell. This is unique among note-taking apps.

Special formatting tricks

Trick	How
Multi-line collapsible title	Shift+Enter inside title
Multi-line bullet item	Shift+Enter inside bullet (text stays under same bullet)
Word-processor tabs	Tab key creates gaps in text (not just indenting)
Spaced-out headers	`R E L A T I O N S H I P S` — space between each letter for a distinct section header style
Colored collapsible header	Put colored text or emoji in title
Collapsible with colored background	Apply `shine-blue-bg` (or similar) class to the collapsible container div
Image in collapsible title	Insert `<img>` inside the title div
Quote as colored header	Use a blockquote with background class as a visual divider
Link color override	`<a href="..." style="color: #...">` or wrap in colored span
Center/right in headers	`style="text-align: center;"` on heading elements
Vertical alignment with TAB	Tab between text segments for aligned columns
↓more↓ indicator	Text at bottom of collapsible signaling more content below
Custom section symbols	Pick a unicode symbol (✦ † § • › ❯ ➤ 🅳 ▍) and use it consistently

Advanced design patterns — what makes a template "stunning" in UpNote

UpNote templates can't do custom fonts, gradients, or pixel-perfect layouts. But the community has discovered that "stunning" in UpNote means something different — it means information that reveals itself progressively, status you can read at a glance, and a visual language built from the tools available.

Pattern 1: Collapsible-as-status-indicator

The single most powerful technique. Each collapsible section title becomes a status dashboard when collapsed:

<h1>Project Alpha</h1>

<!-- Blue title = done -->
<div class="shine-collapsible-section">
  <div class="shine-section-title-wrapper">
    <div class="shine-section-title shine-placeholder" data-upnote-placeholder-key="title">
      <div class="shine-section-title-inner">
        <h3><span class="shine-text-blue">✓ Research Complete</span> Mar 2026</h3>
      </div>
    </div>
  </div>
  <div class="shine-section-content shine-placeholder" data-upnote-placeholder-key="content">
    <div class="shine-section-content-inner">
      <div>Details here...</div>
    </div>
  </div>
</div>

<!-- Red title = urgent -->
<div class="shine-collapsible-section">
  <div class="shine-section-title-wrapper">
    <div class="shine-section-title shine-placeholder" data-upnote-placeholder-key="title">
      <div class="shine-section-title-inner">
        <h3><span class="shine-text-red">⚠️ Budget Approval</span> Due Apr 15</h3>
      </div>
    </div>
  </div>
  <div class="shine-section-content shine-placeholder" data-upnote-placeholder-key="content">
    <div class="shine-section-content-inner">
      <div>Details here...</div>
    </div>
  </div>
</div>

When collapsed, the user sees: blue "✓ Research Complete" and red "⚠️ Budget Approval" — status at a glance. No need to open anything.

Pattern 2: Card-style forms using tables + colored backgrounds

Tables with shine-blue-bg (or other background classes) create card-like visual blocks. Combine with colspan/rowspan for layout:

<div class="shine-table-wrapper">
<table>
  <colgroup>
    <col style="width: 120px;">
    <col style="width: 200px;">
    <col style="width: 120px;">
    <col style="width: 200px;">
  </colgroup>
  <tbody>
    <tr>
      <td class="shine-blue-bg"><b>Project</b></td>
      <td colspan="3"><blockquote>Project Name Here</blockquote></td>
    </tr>
    <tr>
      <td class="shine-blue-bg"><b>Status</b></td>
      <td><span class="shine-text-green">● Active</span></td>
      <td class="shine-blue-bg"><b>Priority</b></td>
      <td><span class="shine-text-red">● High</span></td>
    </tr>
    <tr>
      <td class="shine-blue-bg"><b>Lead</b></td>
      <td>Name</td>
      <td class="shine-blue-bg"><b>Deadline</b></td>
      <td>Apr 15, 2026</td>
    </tr>
  </tbody>
</table>
</div>

Pattern 3: Layered information architecture

The best templates use progressive disclosure — most important info at top, detail in nested collapsibles:

h1  →  Title (always visible)
hr  →  Section break
     h2 → Key metadata (always visible — dates, status, links)
     hr
     Collapsible "Summary" → brief overview
     Collapsible "Details" → deeper info
       Nested collapsible "Sub-topic A"
       Nested collapsible "Sub-topic B"
     Collapsible "Action Items" → checkboxes
     Collapsible "History" → log of changes with color-coded dates

Pattern 4: Color-coded inline indicators

Use colored text spans and emoji as inline status badges:

<span class="shine-text-green">● Active</span>
<span class="shine-text-red">● Overdue</span>
<span class="shine-text-blue">● Complete</span>
<span class="shine-text-yellow">● Pending</span>
🟩 🟧 🟦 🟥 🟨  (emoji squares as colored badges)

Combine with bullet lists for a clean dashboard:

<ul>
  <li><span class="shine-text-green">●</span> Task A — <span class="shine-text-blue">Done</span></li>
  <li><span class="shine-text-yellow">●</span> Task B — <span class="shine-text-yellow">In Progress</span></li>
  <li><span class="shine-text-red">●</span> Task C — <span class="shine-text-red">Overdue</span></li>
</ul>

Pattern 5: Blockquotes as metadata panels

Blockquotes create a distinct indented visual block — perfect for key-value metadata:

<blockquote>
  <div><b>Genre:</b> Sci-Fi</div>
  <div><b>Year:</b> 2026</div>
  <div><b>Rating:</b> ★★★★½</div>
  <div><b>Where:</b> Netflix</div>
</blockquote>

This renders as a clean, visually grouped metadata panel.

Pattern 6: Data-dense tables with mixed content

Tables can contain headings, lists, blockquotes, and colored cells:

<table>
  <colgroup>
    <col style="width: 160px;">
    <col style="width: 300px;">
  </colgroup>
  <tbody>
    <tr>
      <td class="shine-blue-bg"><h5>Day 1</h5></td>
      <td>
        <ul>
          <li data-checked="true">Morning: Flight to Paris</li>
          <li data-checked="false">Afternoon: Louvre</li>
          <li data-checked="false">Evening: Dinner at Le Cinq</li>
        </ul>
      </td>
    </tr>
  </tbody>
</table>

Pattern 7: Directional arrows and communication patterns

Use arrows for conversation flow:

2026-03-15  >  Sent proposal to Joe
2026-03-18  <  Joe: questions about pricing
2026-03-19  >  Replied with clarifications
2026-03-20  >>  Joe, Jane: scheduled kickoff meeting

Pattern 8: Rating systems

★★★★★  Excellent
★★★★   Good
★★★    Average  
★★     Poor
★      Bad

★★★★★½  (half-star precision)

Can be combined with colored text for nuance.

Pattern 9: Emoji as structural elements

Emoji replace the need for icons and create a consistent visual language:

📋 Project Dashboard      🎯 Goals & Objectives
📅 Timeline               📊 Metrics
📝 Meeting Notes          ✅ Action Items
🔗 References             📎 Attachments
📌 Key Decisions          ⚠️ Risks & Issues
💡 Ideas                  🚧 Blockers
🏗️ In Progress            ✨ Completed

Pattern 10: The "multi-column" illusion

Using tables with no borders to create column-like layouts:

<table>
  <tbody>
    <tr>
      <td style="width: 33%;"><h3>Today</h3><ul><li data-checked="false">Task 1</li></ul></td>
      <td style="width: 33%;"><h3>This Week</h3><ul><li data-checked="false">Task A</li></ul></td>
      <td style="width: 33%;"><h3>Soon</h3><ul><li data-checked="false">Task X</li></ul></td>
    </tr>
  </tbody>
</table>

Pattern 11: "More below" indicators

Put ↓more↓ at the bottom of a section to signal there's additional content further down. This works especially well with collapsible sections that have a lot of content.

Pattern 12: Spaced-letter section dividers

Use spaced uppercase letters as elegant section dividers — no table or HR needed:

<h3>R E L A T I O N S H I P S</h3>
<hr>
<h3>A P P E A R A N C E</h3>

Creates a clean, distinct section header that visually separates content blocks.

Pattern 13: Mixed-type lists

Lists can mix bullet, ordinal, and checklist types:

<ul>
  <li>Research phase
    <ol>
      <li>User interviews</li>
      <li>Competitor analysis</li>
    </ol>
  </li>
  <li data-checked="true">Design complete
    <ul>
      <li>Wireframes ✓</li>
      <li>Mockups ✓</li>
    </ul>
  </li>
</ul>

Pattern 14: Tables inside lists, images inside list items

<ul>
  <li>Week 1
    <table>...</table>
  </li>
  <li>Screenshot <img src="..." width="200"></li>
</ul>

Pattern 15: Quotes as colored section headers

Instead of HR + heading, use a colored blockquote as a visual section divider:

<blockquote class="shine-blue-bg">
  <h3>📋 Meeting Notes — {{date}}</h3>
</blockquote>

Renders as a blue banner across the page. Can be used inside collapsible titles too.

Pattern 16: Multi-color inline progress bar (text-based)

[■■■■■■■■□□] 80% Complete

Using unicode block characters for visual progress indication.

Pattern 17: Breadcrumb directory navigation

For multi-note systems, add a breadcrumb line at the top showing the note's location:

🅳️ 目錄｜directory ＞ 模板｜templates
🅳️ 目錄｜directory ＞ 語言｜language ＞ 🎧聽力進度｜listening progress
🅳️ 目錄｜directory ＞ 書櫃｜bookcase ＞ 📗Series3｜《Book name》

This creates a navigable hierarchy when users have many interconnected notes. The 🅳️ symbol signals "this is a directory/breadcrumb."

Pattern 18: Bilingual translation pairs

For language learning or bilingual notes:

（副標題｜subtitle）
這是例子。　例子｜sample　#noun
This is sample.

Format: text ｜ translation — the vertical bar separates languages. Consistent across all entries.

Pattern 19: Color/symbol legend reference

When using a custom color or symbol system, include a legend (usually at the bottom):

♦️red     youtube    ♦️title • Short subject　2025-9-16　🔗
🔷blue    bilibili   🔷title • Short subject　2025-9-16　🔗
🟨orange  amazon     🟨title • Short subject　2025-9-16　🔗
📗green   books      📗《book name》 • chapters / themes
🎧grey    podcast    🎧program name • theme　2025-9-16　🔗
📋brown   article    📋source • article title
💡yellow  inspiration 💡inspirational themes • keywords
📄white   document   📄file name • key content
⚙️silver  tools      ⚙️tool name • function or technique

Each entry shows: symbol color → source type → entry format example → 🔗 link. This both documents the system and serves as a template for new entries.

Pattern 20: ♣ placeholder symbol for fill-in fields

Use ♣ (or any consistent symbol) as a visual placeholder for fields the user needs to fill:

Mood tag 🌈: ♣
Shopping bought 🛒: ♣
Spent💰: $ ♣
Comment: ♣

The ♣ stands out visually and signals "this is where you type." Users can search for ♣ to find unfilled fields.

Pattern 21: Fun/mood alignment meter

For qualitative tracking, use a horizontal alignment layout:

Did I have fun today?

Hell yeah        Fuck no          Meh

Achieved by tab-separated text on a single line. The visual spacing creates a scale from positive to negative.

Pattern 22: Repeating collapsible with identical internal structure

For meeting notes, episode logs, or daily journals — each collapsible has the EXACT same sub-sections:

📅 Apr 01, 2024          (collapsible title)
├── 💬 Feedback          (same sub-section every time)
├── 📘 Notes
├── 😟 Concerns
├── 💭 Suggestions
├── 🙌 Praise
└── 🔄 Follow Up

📅 Mar 01, 2024          (same structure)
├── 💬 Feedback
├── 📘 Notes
...

This creates predictability. User knows exactly where to find any piece of info.

Pattern 23: Emoji progress bars

Create visual progress indicators using emoji squares:

🟩🟩🟩🟩🟩🟩🟩🟩⬜️⬜️  80%  — Project Alpha
🟩🟩⬜️⬜️⬜️⬜️⬜️⬜️⬜️⬜️  20%  — Project Beta
🟩🟩🟩🟩🟩🟩🟩🟩🟩🟩  100% — Project Gamma

Each filled square = 10%. The visual density communicates progress instantly.

Can also use other colored squares: 🟦🟧🟥🟨 for different categories.

Pattern 24: Full-width character alignment

Use full-width Unicode characters (FF00 range) for monospace-perfect alignment:

２０２６　　（full-width numbers）
ＤＥＣ　　（full-width letters）
０１　０２　０３　０４　０５　（full-width date grid）

Full-width characters occupy exactly 2 ASCII widths, making tab-aligned grids work perfectly. Combine with full-width spaces (U+3000) for precise spacing:

　　　　　　ＤＥＣ　　　　　　← full-width spaces before DEC
　　06　07　08　09　10　11　12　← aligned grid

Pattern 25: Calendar grids (text-based)

Create monthly calendars using tabs + numbers:

APRIL 2026
Sun  Mon  Tue  Wed  Thu  Fri  Sat
          1    2    3    4
 5    6    7    8    9   10   11
12   13   14   15   16   17   18
19   20   21   22   23   24   25
26   27   28   29   30

Or with full-width characters for better alignment:

　　　　　　　ＤＥＣ　　　　　　　
　　　　　01　02　03　04　05
06　07　08　09　10　11　12
13　14　15　16　17　18　19
20　21　22　23　24　25　26
27　28　29　30　31

Pattern 26: Vertical date + day-of-week list

For a compact date reference:

１Ｍ　（1st = Monday）
２Ｔ　（2nd = Tuesday）
３Ｗ　（3rd = Wednesday）
...
３１Ｗ　（31st = Wednesday）

Full-width number + full-width day letter = perfect vertical alignment.

Pattern 27: Pipe-delimited date-prefixed items

For todo lists and logs where each item needs a date:

| 07-04-2026 | item 1 description here
| 04-12-2026 | item 2 description here
| 04-02-2026 | item 3 description here

The pipe creates a visual column separator. Consistent date format makes scanning easy.

Pattern 28: ▍ section block marker

Use ▍ (U+258D left seven-eighths block) as a consistent section marker:

▍ PROJECTS
▍ TODO LIST
▍ CALENDAR - APRIL 2026
▍ WEEKLY PREVIEW

The block character stands out as a visual column marker, different from both emoji and text headers.

Pattern 29: Weekly outlook with tile bullets

For week-at-a-glance templates:

3/22 🀰 SUNDAY
[content]

3/23 🀰 MONDAY
[content]

Use 🀰 (mahjong tile) as a distinctive day-of-week bullet. Each day is a section with consistent structure. Weekly preview at top, weekly review at bottom.

Pattern 30: Sundays highlight reference

For calendars, include a note about color coding:

Sundays red highlight : [note about how Sundays are colored red]

This documents the visual system within the template itself.

Pattern 31: #tags-as-categories within notes

Add hashtags at strategic points in the note for searchability:

#Journal        (in footer, so the note appears when searching all journal entries)
#MoviesWatched2024  (in movie list header)
#EduLecture     (in lecture notes)
#noun #adverb #verb #adjective  (in language notes)

These don't need to be linked to the DB tags — they're searchable text within the note content.

[■■■■■■■■□□] 80% Complete

Using unicode block characters for visual progress indication.

Complete stunning template example — Project Dashboard

<h1>📋 Project: Mobile App Redesign</h1>
<blockquote>
  <div><b>Status:</b> <span class="shine-text-green">● On Track</span></div>
  <div><b>Deadline:</b> Jun 30, 2026</div>
  <div><b>Team:</b> Ahmed (PM), Sara (Design), Omar (Dev)</div>
</blockquote>
<hr>

<div class="shine-collapsible-section">
  <div class="shine-section-title-wrapper">
    <div class="shine-section-title shine-placeholder" data-upnote-placeholder-key="title">
      <div class="shine-section-title-inner">
        <h2><span class="shine-text-blue">✓ Research & Discovery</span></h2>
      </div>
    </div>
  </div>
  <div class="shine-section-content shine-placeholder" data-upnote-placeholder-key="content">
    <div class="shine-section-content-inner">
      <ul>
        <li data-checked="true">User interviews completed</li>
        <li data-checked="true">Competitor analysis done</li>
        <li data-checked="true">Requirements documented</li>
      </ul>
    </div>
  </div>
</div>

<div class="shine-collapsible-section">
  <div class="shine-section-title-wrapper">
    <div class="shine-section-title shine-placeholder" data-upnote-placeholder-key="title">
      <div class="shine-section-title-inner">
        <h2><span class="shine-text-yellow">🔄 Design</span> In Progress</h2>
      </div>
    </div>
  </div>
  <div class="shine-section-content shine-placeholder" data-upnote-placeholder-key="content">
    <div class="shine-section-content-inner">
      <ul>
        <li data-checked="true">Wireframes v1</li>
        <li data-checked="false">Visual mockups</li>
        <li data-checked="false">Prototype</li>
      </ul>
    </div>
  </div>
</div>

<div class="shine-collapsible-section">
  <div class="shine-section-title-wrapper">
    <div class="shine-section-title shine-placeholder" data-upnote-placeholder-key="title">
      <div class="shine-section-title-inner">
        <h2><span class="shine-text-red">⚠️ Development</span> Blocked</h2>
      </div>
    </div>
  </div>
  <div class="shine-section-content shine-placeholder" data-upnote-placeholder-key="content">
    <div class="shine-section-content-inner">
      <ul>
        <li data-checked="false">Frontend — waiting for API</li>
        <li data-checked="false">Backend — <span class="shine-text-red">blocked on infra</span></li>
      </ul>
    </div>
  </div>
</div>

<hr>
<h3>📊 Timeline</h3>
<div class="shine-table-wrapper">
<table>
  <colgroup>
    <col style="width: 120px;">
    <col style="width: 100px;">
    <col style="width: 100px;">
    <col style="width: 200px;">
  </colgroup>
  <tbody>
    <tr>
      <td class="shine-blue-bg"><b>Phase</b></td>
      <td class="shine-blue-bg"><b>Start</b></td>
      <td class="shine-blue-bg"><b>End</b></td>
      <td class="shine-blue-bg"><b>Status</b></td>
    </tr>
    <tr>
      <td>Research</td>
      <td>Jan 1</td>
      <td>Jan 31</td>
      <td><span class="shine-text-blue">✓ Done</span></td>
    </tr>
    <tr>
      <td>Design</td>
      <td>Feb 1</td>
      <td>Mar 15</td>
      <td><span class="shine-text-yellow">● In Progress</span></td>
    </tr>
    <tr>
      <td>Dev</td>
      <td>Mar 16</td>
      <td>May 30</td>
      <td><span class="shine-text-red">⚠️ At Risk</span></td>
    </tr>
  </tbody>
</table>
</div>

How to design a stunning template (step-by-step process)

Follow this process when creating any template:

Step 1: Pick your visual language (be consistent)

Choose once and reuse across all templates:

Emoji set:       📋=project  📅=date  ✅=done  ⚠️=blocker  💡=idea  📌=key
                🔄=repeating  🎯=goal  📊=metrics  👤=person  🏷️=tag
Placeholder:    ♣  (one single symbol for fill-in-the-blank)
Date format:    {{YYYY}}-{{MM}}-{{DD}} or {{date}}
Section style:  Spaced letters (S U M M A R Y) or emoji line (📋 Summary)
Tag prefix:     #Category  (within note for searchability)

Step 2: Define the information hierarchy

Level 1 (always visible):  h1 title  →  metadata blockquote  →  hr
Level 2 (collapsible):     Major sections (color-coded by status)
Level 3 (nested):          Sub-sections within major sections
Level 4 (innermost):       Details, notes, history

Step 3: Choose your section container type

Container	Best for
Collapsible	Dense info the user doesn't need to see all at once — status tracking, history, details
Blockquote	Key-value metadata, callouts, important info that should stand out
Table with colored bg	Forms, cards, structured data, comparison grids
HR + heading	Simple section breaks, linear reading
Spaced-letter heading	Elegant section dividers, creative templates
Mixed list	Items with sub-items, nested categories
Emoji heading + content	Quick sections in journals, logs, daily notes

Step 4: Design the collapsed view

The collapsed state is more important than the expanded state. Users scan collapsed collapsibles to decide what to open. Make sure:

✓  Title contains colored status indicator (blue=done, red=urgent, etc.)
✓  Title contains key metadata (date, count, person)
✓  Title is scannable in under 1 second
✓  Emoji in title signals content type
✗  Don't hide critical info inside collapsibles

Step 5: Use consistent repeated structure

For repetitive data (meetings, episodes, days), use the EXACT same internal structure:

📅 Meeting Date
├── 💬 Feedback       (same structure each time)
├── 📘 Notes
├── 😟 Concerns
├── 💭 Suggestions
├── 🙌 Praise
└── 🔄 Follow Up

This creates predictability — user knows where to find info without looking.

Step 6: Add smart metadata in obvious places

In title:      📋 Project Name  │  Status: 🟢  │  Due: Apr 15
In subtitle:   #category  #status  @person
In blockquote: Key: Value pairs, one per line
In table:      Structured fields with colored labels

Step 7: Include a color/key legend when using custom symbols

If you use a non-obvious color or symbol system, include a legend at the bottom:

🟢 Completed    🟡 In Progress    🔴 Not Started
♦️ YouTube      🔷 Bilibili       🟨 Amazon
📗 Book         🎧 Podcast        📋 Article

⚠️ Mandatory rule: always use the full aesthetic toolkit

Never hold back on design. Every template must use the full aesthetic capabilities:

Colored collapsible backgrounds (shine-blue-bg, shine-green-bg, etc.)
Colored table headers and card-style table panels
Colored blockquote section headers
Status-colored collapsible titles (red/orange/blue/green)
Emoji throughout for visual cues
Spaced-letter section dividers where appropriate
Colored text spans for inline status

There is no "just get it working" mode. If capabilities exist in the skill doc, assume the user wants them used. Good design is not a bonus — it's the default expectation.

Design principles summary

Principle	How
Status at a glance	Collapsible section title colors + emoji, color-coded dates
Progressive disclosure	Collapsibles within collapsibles, most important first, ↓more↓ indicators
Visual hierarchy	h1 → h5 for structure, spaced-letter dividers, hr for breaks
Consistent icon language	Pick emoji or symbols (✦†§) and use them consistently
Color = meaning	Don't use color decoratively — use it for status (red=urgent, blue=done, etc.)
Tables for layout	Tables with colspan/rowspan + colored backgrounds create card-like layouts
Blockquotes for panels	Colored blockquotes as section headers, metadata panels, or dividers
Checklists for action	Interactive checkboxes for tasks — can live inside headers, lists, tables
Mixed lists	Bullets within ordinals within checklists — combine types freely
Images in unexpected places	In collapsible headers, list items, quotes — not just body text
Dense but scannable	Put as much useful info as possible in collapsed state
Mixing is the superpower	Table inside quote inside collapsible title — UpNote allows any nesting

Template ideas that showcase ALL capabilities

Project Dashboard — collapsible phases with status colors in titles, timeline table with colored cells, checklist tasks, emoji sections, colored progress bars
Travel Hub — flights table (colspan for airlines), day-by-day collapsible itineraries with colored status, packing checklist with mixed types, budget tracker, blockquote metadata panel
TV Series Tracker — season collapsibles with colored ratings in title, episode table with star ratings, cast list with images, nested collapsibles for episode notes
Meeting Notes — blockquote colored header with date placeholder, collapsible agenda items with checkboxes, decisions table, action items with colored priority, next meeting block
Daily Journal — date placeholder with {{dddd}}, mood/sleep/energy emoji trackers, collapsible morning/afternoon/night sections, gratitude blockquote, tag links
Knowledge Base — table of contents, nested collapsible categories (4+ levels), tag links, blockquote callouts, related notes with arrows (➜)
Habit Tracker — month table with color-coded cells (green=done, red=missed), weekly collapsible summaries, streak counter, inline progress bars
CRM Contact — card-style table (colored bg, colspan layout), collapsible conversation history with color-coded date status (red/pink/purple/orange/blue), tag links, contact info blockquote
Recipe Collection — ingredient table with checkboxes, step-by-step collapsibles with images, blockquote for tips, rating with stars, prep/cook time in colored spans
Goal Tracker (Cubes) — color-coded goal blocks (🟩🟧🟦) with 4 task faces each, progress indicators, wiggle room, time estimates, colored inline status
Character Template — spaced-letter dividers (B A C K S T O R Y), table cards with colored backgrounds, image placeholder, emoji markers, nested traits
Reading Journal — book info blockquote, collapsible sections (summary, characters, quotes), colored rating, emoji markers, tag links
Scene Card — table with colspan for scene structure, colored POV marker, cause/effect blockquotes, nested notes
Contact History — color-coded date collapsibles (red=new, purple=ongoing, blue=resolved), arrow direction indicators (><), ALLCAPS method labels, tag links

UpNote supports these template placeholders

Placeholder	Example output
`{{date}}`	Jul 30, 2026
`{{time}}`	3:45 PM
`{{datetime}}`	Jul 30, 2026 3:45 PM
`{{YYYY}}`	2026
`{{MM}}`	07
`{{DD}}`	30
`{{dd}}`	Wed
`{{dddd}}`	Wednesday
`{{HH}}`	15
`{{mm}}`	45
`{{ss}}`	00
`{{EEEE}} - {{MM}}{{DD}} {{YYYY}}`	Wednesday - 0730 2026

Full format reference: https://day.js.org/docs/en/parse/string-format

Templates the skill can generate

Common templates users often request, all with proper date placeholders:

- Meeting Notes (agenda, attendees, action items, next meeting)
- Daily Journal (gratitude, highlights, lessons, tomorrow's focus)
- Weekly Review (wins, challenges, learnings, priorities)
- Project Planner (objectives, milestones, resources, timeline)
- Habit Tracker (daily checklist with {{date}} header)
- Book Notes (title, author, key ideas, quotes, takeaways)
- Recipe (ingredients, steps, notes)
- Travel Packing List (category checkboxes)
- Workout Log (exercise, sets, reps, notes)
- Expense Tracker (date, category, amount, notes)
- Meeting Minutes Template (with attendee roles)
- CRM Contact Note (name, company, last contact, next steps)
- Brain Dump / Inbox (empty structured page for quick capture)

The agent should ask the user which template(s) they want, propose the content, get approval (per section 4 rules — backup, dry-run, confirm), then insert.

Turn an existing note into a template

sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  UPDATE notes SET isTemplate = 1, synced = 0, revision = revision + 1,
                   updatedAt = (strftime('%s','now')*1000)
  WHERE id = 'NOTE-UUID-HERE'
"

Remove a template (without deleting the note)

sqlite3 ~/.config/UpNote/upnote.sqlite3 "
  UPDATE notes SET isTemplate = 0, synced = 0, revision = revision + 1,
                   updatedAt = (strftime('%s','now')*1000)
  WHERE id = 'NOTE-UUID-HERE'
"

9. Pitfalls & Gotchas

Issue	Solution
App overwrites changes	Always close UpNote before writing. Set `synced=0`.
Conflict on sync	Bump `revision` on every write. Higher revision wins on sync.
Denormalized arrays out of sync	Update BOTH `notes.tagLinks` AND `tags.notes` (same for notebooks).
Timestamps	Use epoch milliseconds (JavaScript `Date.now()` style): `int(time.time() * 1000)`
Missing `html` field	The app regenerates HTML from `text` on sync. You can leave `html` as NULL or set it.
Empty JSON arrays	Use `'[]'`, not `NULL`, for `tagLinks`, `notebookLinks`, `notes` columns.
App caches	After writing, you may need to force-quit and reopen UpNote for changes to appear.
Templates not showing	Templates need `space` set to a workspace UUID — `NULL` or `''` hides them from the templates section.
Auto-updates	UpNote updates replace the app but NOT the SQLite DB. Your data survives.
Note about E2EE	UpNote does NOT encrypt data at rest. The SQLite file is plaintext.
`{{date}}` in regular notes	Placeholders like `{{date}}` only work in templates (`isTemplate=1`). In regular notes they appear literally. Use the actual date instead.
Cultural context mismatch	Never add languages, writing systems, or cultural references that don't match the user's context. Arabic notes get Arabic/English, not Chinese. Match the user's language and cultural background.

abdumu/upnote_skill.md

UpNote Skill — Direct SQLite Access

1. Find the Database

2. Schema Overview

notes — the main table

notebooks — notebooks/folders

tags — note labels

organizers — note↔notebook join table

workspaces — top-level containers

files — attached files

3. Using the sqlite3 CLI Directly

Open the DB

One-liner queries

One-liner writes

Use with a shell variable

4. Rules for Safe Writing

★ Always backup before every edit

★ Always dry-run, always ask confirmation first

Always close UpNote before writing

Always set synced = 0 on changes

Bump revision on the changed entity

Keep denormalized fields in sync

UUID format

5. Read Operations

Get all active notes

Get notes in a specific notebook

Get notes with a specific tag

Get all notebooks

Get all tags

Count by workspace

6. Write Operations

Create a new note

Update note title/summary

Assign a note to a notebook (4 places)

Remove a note from a notebook

Add a tag to a note

Create a new tag

Create a new notebook

Move a note to a different workspace

Trash / restore / delete

7. AI Organization — Interactive Workflow

7.1 First, assess the current state

7.2 Ask the user for their preferences

7.3 Present a dry-run plan before executing

7.4 Apply after confirmation

7.6 Workspace & notebook placement rules

7.5 Post-organization summary

8. Template Generation

How templates are stored

✦ Key insight — UpNote is uniquely flexible

Supported HTML formatting (verified from shared templates and real DB data)

CSS classes that work in UpNote

Collapsible section structure

Template with table + colors + collapsible (real example)

Color coding patterns (community conventions)

Common emoji used as visual markers

Rating system pattern

Limitations

Design philosophy for templates

9. Notebook Covers

Built-in covers (set via SQL)

Custom covers with Python/Pillow

Cover color palettes by notebook type

Create a template directly in the DB

Formatting mixing rules — what goes inside what

Special formatting tricks

Advanced design patterns — what makes a template "stunning" in UpNote

Pattern 1: Collapsible-as-status-indicator

Pattern 2: Card-style forms using tables + colored backgrounds

Pattern 3: Layered information architecture

Pattern 4: Color-coded inline indicators

Pattern 5: Blockquotes as metadata panels

Pattern 6: Data-dense tables with mixed content

Pattern 7: Directional arrows and communication patterns

Pattern 8: Rating systems

Pattern 9: Emoji as structural elements

Pattern 10: The "multi-column" illusion

Pattern 11: "More below" indicators

Pattern 12: Spaced-letter section dividers

Pattern 13: Mixed-type lists

`notes` — the main table

`notebooks` — notebooks/folders

`tags` — note labels

`organizers` — note↔notebook join table

`workspaces` — top-level containers

`files` — attached files

3. Using the `sqlite3` CLI Directly

Always set `synced = 0` on changes

Bump `revision` on the changed entity