basic notes support

Signed-off-by: Xe Iaso <me@christine.website>

13 files changed, 442 insertions, 6 deletions

diff --git a/.gitignore b/.gitignore
index fed8779..fb94aed 100644
--- a/.gitignore
+++ b/.gitignore
@@ -8,3 +8,6 @@ cw.tar
 /target
 .patreon.json
 .direnv
+*.db
+*.db-shm
+*.db-wal
diff --git a/Cargo.lock b/Cargo.lock
index 4941361..1e55a07 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -265,6 +265,31 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "904dfeac50f3cdaba28fc6f57fdcddb75f49ed61346676a78c4ffe55877802fd"
 
 [[package]]
+name = "bb8"
+version = "0.7.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "2e9f4fa9768efd269499d8fba693260cfc670891cf6de3adc935588447a77cc8"
+dependencies = [
+ "async-trait",
+ "futures-channel",
+ "futures-util",
+ "parking_lot 0.11.2",
+ "tokio",
+]
+
+[[package]]
+name = "bb8-rusqlite"
+version = "0.1.0"
+source = "git+https://github.com/pleshevskiy/bb8-rusqlite?branch=bump-rusqlite#fa3da425ce060a20bb09bdd3313ed1b5e8a5f89d"
+dependencies = [
+ "async-trait",
+ "bb8",
+ "rusqlite",
+ "thiserror",
+ "tokio",
+]
+
+[[package]]
 name = "bincode"
 version = "1.3.3"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -777,6 +802,18 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "e88a8acf291dafb59c2d96e8f59828f3838bb1a70398823ade51a84de6a6deed"
 
 [[package]]
+name = "fallible-iterator"
+version = "0.2.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "4443176a9f2c162692bd3d352d745ef9413eec5782a80d8fd6f8a1ac692a07f7"
+
+[[package]]
+name = "fallible-streaming-iterator"
+version = "0.1.9"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "7360491ce676a36bf9bb3c56c1aa791658183a54d2744120f27285738d90465a"
+
+[[package]]
 name = "fancy-regex"
 version = "0.7.1"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -1017,6 +1054,9 @@ name = "hashbrown"
 version = "0.11.2"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "ab5ef0d4909ef3724cc8cce6ccc8572c5c817592e9285f5464f8e86f8bd3726e"
+dependencies = [
+ "ahash",
+]
 
 [[package]]
 name = "hashbrown"
@@ -1028,6 +1068,15 @@ dependencies = [
 ]
 
 [[package]]
+name = "hashlink"
+version = "0.7.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "7249a3129cbc1ffccd74857f81464a323a152173cdb134e0fd81bc803b29facf"
+dependencies = [
+ "hashbrown 0.11.2",
+]
+
+[[package]]
 name = "hdrhistogram"
 version = "7.5.0"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -1306,6 +1355,17 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "349d5a591cd28b49e1d1037471617a32ddcda5731b99419008085f72d5a53836"
 
 [[package]]
+name = "libsqlite3-sys"
+version = "0.23.2"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "d2cafc7c74096c336d9d27145f7ebd4f4b6f95ba16aa5a282387267e6925cb58"
+dependencies = [
+ "cc",
+ "pkg-config",
+ "vcpkg",
+]
+
+[[package]]
 name = "line-wrap"
 version = "0.1.1"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -1655,12 +1715,37 @@ checksum = "decf7381921fea4dcb2549c5667eda59b3ec297ab7e2b5fc33eac69d2e7da87b"
 
 [[package]]
 name = "parking_lot"
+version = "0.11.2"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "7d17b78036a60663b797adeaee46f5c9dfebb86948d1255007a1d6be0271ff99"
+dependencies = [
+ "instant",
+ "lock_api",
+ "parking_lot_core 0.8.5",
+]
+
+[[package]]
+name = "parking_lot"
 version = "0.12.1"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "3742b2c103b9f06bc9fff0a37ff4912935851bee6d36f3c02bcc755bcfec228f"
 dependencies = [
  "lock_api",
- "parking_lot_core",
+ "parking_lot_core 0.9.3",
+]
+
+[[package]]
+name = "parking_lot_core"
+version = "0.8.5"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "d76e8e1493bcac0d2766c42737f34458f1c8c50c0d23bcb24ea953affb273216"
+dependencies = [
+ "cfg-if",
+ "instant",
+ "libc",
+ "redox_syscall",
+ "smallvec",
+ "winapi",
 ]
 
 [[package]]
@@ -1977,7 +2062,7 @@ dependencies = [
  "lazy_static",
  "libc",
  "memchr",
- "parking_lot",
+ "parking_lot 0.12.1",
  "procfs",
  "thiserror",
 ]
@@ -2176,6 +2261,34 @@ dependencies = [
 ]
 
 [[package]]
+name = "rusqlite"
+version = "0.26.3"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "4ba4d3462c8b2e4d7f4fcfcf2b296dc6b65404fbbc7b63daa37fd485c149daf7"
+dependencies = [
+ "bitflags",
+ "chrono",
+ "fallible-iterator",
+ "fallible-streaming-iterator",
+ "hashlink",
+ "libsqlite3-sys",
+ "memchr",
+ "serde_json",
+ "smallvec",
+ "uuid 0.8.2",
+]
+
+[[package]]
+name = "rusqlite_migration"
+version = "0.5.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "a5a5fa55374b33f1acdafffb85530a63223b15fbd7704dc1a5c30f17d90c200a"
+dependencies = [
+ "log",
+ "rusqlite",
+]
+
+[[package]]
 name = "rustc-demangle"
 version = "0.1.21"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -2643,7 +2756,7 @@ dependencies = [
  "mio",
  "num_cpus",
  "once_cell",
- "parking_lot",
+ "parking_lot 0.12.1",
  "pin-project-lite",
  "signal-hook-registry",
  "socket2",
@@ -3215,6 +3328,8 @@ dependencies = [
  "axum 0.5.11",
  "axum-extra",
  "axum-macros",
+ "bb8",
+ "bb8-rusqlite",
  "cfcache",
  "chrono",
  "color-eyre",
@@ -3244,6 +3359,8 @@ dependencies = [
  "regex",
  "reqwest",
  "ructe",
+ "rusqlite",
+ "rusqlite_migration",
  "sdnotify",
  "serde",
  "serde_dhall",
diff --git a/Cargo.toml b/Cargo.toml
index 61058c2..b7427f0 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -12,6 +12,7 @@ repository = "https://github.com/Xe/site"
 axum = { version = "0.5", features = ["headers"] }
 axum-macros = "0.2"
 axum-extra = "0.3"
+bb8 = "0.7"
 color-eyre = "0.6"
 chrono = "0.4"
 comrak = "0.13.2"
@@ -34,6 +35,7 @@ prometheus = { version = "0.13", default-features = false, features = ["process"
 rand = "0"
 regex = "1"
 reqwest = { version = "0.11", features = ["json"] }
+rusqlite_migration = "0.5"
 serde_dhall = "0.11.2"
 serde = { version = "1", features = ["derive"] }
 serde_yaml = "0.8"
@@ -48,13 +50,18 @@ xml-rs = "0.8"
 url = "2"
 uuid = { version = "0.8", features = ["serde", "v4"] }
 
-xesite_types = { path = "./lib/xesite_types" }
-
 # workspace dependencies
 cfcache = { path = "./lib/cfcache" }
 xe_jsonfeed = { path = "./lib/jsonfeed" }
 mi = { path = "./lib/mi" }
 patreon = { path = "./lib/patreon" }
+xesite_types = { path = "./lib/xesite_types" }
+
+bb8-rusqlite = { git = "https://github.com/pleshevskiy/bb8-rusqlite", branch = "bump-rusqlite" }
+
+[dependencies.rusqlite]
+version = "0.26"
+features = [ "bundled", "uuid", "serde_json", "chrono" ]
 
 [dependencies.tower]
 version = "0.4"
diff --git a/docs/notes.markdown b/docs/notes.markdown
new file mode 100644
index 0000000..8ab83f5
--- /dev/null
+++ b/docs/notes.markdown
@@ -0,0 +1,38 @@
+# Notes
+
+## Goals
+
+- Have somewhere other than Twitter or Mastodon to host short-form content or
+  list articles I "like".
+- Authenticate over Tailscale
+- Simple API to automate posting with iOS Shortcuts
+- Have a JSONFeed for people to subscribe
+- Send WebMentions when I reply to things
+- Store things in SQLite
+
+## Schema
+
+```sql
+CREATE TABLE IF NOT EXISTS notes
+  ( id           INTEGER PRIMARY KEY
+  , content      TEXT    NOT NULL
+  , content_html TEXT    NOT NULL
+  , created_at   INTEGER NOT NULL -- Unix epoch timestamp
+  , updated_at   INTEGER          -- Unix epoch timestamp
+  , deleted_at   INTEGER          -- Unix epoch timestamp
+  , reply_to     TEXT
+  );
+```
+
+```rust
+#[derive(Clone, Debug, Serialize, Deserialize)]
+pub struct Note {
+  pub id: u64,
+  pub content: String,
+  pub content_html: String,
+  pub created_at: DateTime<Utc>,
+  pub updated_at: Option<DateTime<Utc>>,
+  pub deleted_at: Option<DateTime<Utc>>,
+  pub reply_to: Option<String>,
+}
+```
diff --git a/flake.nix b/flake.nix
index ac91b63..edf2a3f 100644
--- a/flake.nix
+++ b/flake.nix
@@ -83,6 +83,7 @@
             # system dependencies
             openssl
             pkg-config
+            sqlite-interactive
 
             # kubernetes deployment
             dhall
diff --git a/src/app/mod.rs b/src/app/mod.rs
index 5b8e719..24eabc5 100644
--- a/src/app/mod.rs
+++ b/src/app/mod.rs
@@ -1,7 +1,9 @@
 use crate::{post::Post, signalboost::Person};
+use bb8::Pool;
+use bb8_rusqlite::RusqliteConnectionManager;
 use chrono::prelude::*;
 use color_eyre::eyre::Result;
-use std::{fs, path::PathBuf, sync::Arc};
+use std::{env, fs, path::PathBuf, sync::Arc};
 use tracing::{error, instrument};
 
 pub mod config;
@@ -58,6 +60,7 @@ pub struct State {
     pub sitemap: Vec<u8>,
     pub patrons: Option<patreon::Users>,
     pub mi: mi::Client,
+    pub pool: Pool<RusqliteConnectionManager>,
 }
 
 pub async fn init(cfg: PathBuf) -> Result<State> {
@@ -73,6 +76,10 @@ pub async fn init(cfg: PathBuf) -> Result<State> {
     let gallery = crate::post::load(cfg.clone(), "gallery").await?;
     let talks = crate::post::load(cfg.clone(), "talks").await?;
     let mut everything: Vec<Post> = vec![];
+    let mgr = RusqliteConnectionManager::new(
+        env::var("DATABASE_URL").unwrap_or("./var/waifud.db".to_string()),
+    );
+    let pool = bb8::Pool::builder().build(mgr).await?;
 
     {
         let blog = blog.clone();
@@ -150,6 +157,7 @@ pub async fn init(cfg: PathBuf) -> Result<State> {
         jf: jfb.build(),
         sitemap: sm,
         patrons: patrons().await?,
+        pool,
     })
 }
 
diff --git a/src/handlers/mod.rs b/src/handlers/mod.rs
index 37fcac1..87175ca 100644
--- a/src/handlers/mod.rs
+++ b/src/handlers/mod.rs
@@ -15,6 +15,7 @@ pub mod api;
 pub mod blog;
 pub mod feeds;
 pub mod gallery;
+pub mod notes;
 pub mod talks;
 
 fn weekday_to_name(w: Weekday) -> &'static str {
@@ -164,6 +165,24 @@ pub enum Error {
 
     #[error("string conversion error: {0}")]
     ToStr(#[from] http::header::ToStrError),
+
+    #[error("database error: {0}")]
+    SQLite(#[from] rusqlite::Error),
+
+    #[error("database pool error: {0}")]
+    SQLitePool(#[from] bb8_rusqlite::Error),
+
+    #[error("other error: {0}")]
+    Catchall(String),
+}
+
+impl<E> From<bb8::RunError<E>> for Error
+where
+    E: std::error::Error + Send + 'static,
+{
+    fn from(err: bb8::RunError<E>) -> Self {
+        Self::Catchall(format!("{}", err))
+    }
 }
 
 pub type Result<T = Html<Vec<u8>>> = std::result::Result<T, Error>;
diff --git a/src/handlers/notes.rs b/src/handlers/notes.rs
new file mode 100644
index 0000000..feb2129
--- /dev/null
+++ b/src/handlers/notes.rs
@@ -0,0 +1,161 @@
+use crate::templates;
+use axum::{extract::Path, response::Html, Json};
+use chrono::prelude::*;
+use maud::{html, Markup, PreEscaped};
+use rusqlite::params;
+use serde::{Deserialize, Serialize};
+
+#[derive(Clone, Debug, Serialize, Deserialize)]
+pub struct Note {
+    pub id: u64,
+    pub content: String,
+    pub content_html: String,
+    pub created_at: DateTime<Utc>,
+    pub updated_at: Option<DateTime<Utc>>,
+    pub deleted_at: Option<DateTime<Utc>>,
+    pub reply_to: Option<String>,
+}
+
+impl Note {
+    pub fn to_html(&self) -> Markup {
+        html! {
+            article."h-entry" {
+                time."dt-published" datetime=(self.created_at) {
+                    {(self.detrytemci())}
+                }
+                a href={"/notes/" (self.id)} {
+                    "🔗"
+                }
+
+                @if let Some(reply_to) = &self.reply_to {
+                    p {
+                        "In reply to "
+                        a href=(reply_to) {(reply_to)}
+                        "."
+                    }
+                }
+
+                div."e-content" {
+                    (PreEscaped(self.content_html.clone()))
+                }
+            }
+        }
+    }
+
+    pub fn detrytemci(&self) -> String {
+        self.created_at.format("M%m %d %Y %M:%H").to_string()
+    }
+}
+
+impl Into<xe_jsonfeed::Item> for Note {
+    fn into(self) -> xe_jsonfeed::Item {
+        let url = format!("https://xeiaso.net/note/{}", self.id);
+        let mut builder = xe_jsonfeed::Item::builder()
+            .content_html(self.content_html)
+            .id(url.clone())
+            .url(url.clone())
+            .date_published(self.created_at.to_rfc3339())
+            .author(
+                xe_jsonfeed::Author::new()
+                    .name("Xe Iaso")
+                    .url("https://xeiaso.net")
+                    .avatar("https://xeiaso.net/static/img/avatar.png"),
+            );
+
+        if let Some(updated_at) = self.updated_at {
+            builder = builder.date_modified(updated_at.to_rfc3339());
+        }
+
+        builder.build().unwrap()
+    }
+}
+
+#[instrument(err)]
+pub async fn index() -> super::Result {
+    let conn = crate::establish_connection()?;
+
+    let mut stmt = conn.prepare("SELECT id, content, content_html, created_at, updated_at, deleted_at, reply_to FROM notes ORDER BY id DESC LIMIT 25")?;
+    let notes = stmt
+        .query_map(params![], |row| {
+            Ok(Note {
+                id: row.get(0)?,
+                content: row.get(1)?,
+                content_html: row.get(2)?,
+                created_at: row.get(3)?,
+                updated_at: row.get(4)?,
+                deleted_at: row.get(5)?,
+                reply_to: row.get(6)?,
+            })
+        })?
+        .filter(Result::is_ok)
+        .map(Result::unwrap)
+        .collect::<Vec<Note>>();
+
+    let mut result: Vec<u8> = vec![];
+    templates::notesindex_html(&mut result, notes)?;
+    Ok(Html(result))
+}
+
+#[instrument(err)]
+pub async fn feed() -> super::Result<Json<xe_jsonfeed::Feed>> {
+    let conn = crate::establish_connection()?;
+
+    let mut stmt = conn.prepare("SELECT id, content, content_html, created_at, updated_at, deleted_at, reply_to FROM notes ORDER BY id DESC LIMIT 25")?;
+    let notes = stmt
+        .query_map(params![], |row| {
+            Ok(Note {
+                id: row.get(0)?,
+                content: row.get(1)?,
+                content_html: row.get(2)?,
+                created_at: row.get(3)?,
+                updated_at: row.get(4)?,
+                deleted_at: row.get(5)?,
+                reply_to: row.get(6)?,
+            })
+        })?
+        .filter(Result::is_ok)
+        .map(Result::unwrap)
+        .collect::<Vec<Note>>();
+
+    let mut feed = xe_jsonfeed::Feed::builder()
+        .author(
+            xe_jsonfeed::Author::new()
+                .name("Xe Iaso")
+                .url("https://xeiaso.net")
+                .avatar("https://xeiaso.net/static/img/avatar.png"),
+        )
+        .description("Short posts that aren't to the same quality level as mainline blogposts")
+        .feed_url("https://xeiaso.net/notes.json")
+        .title("Xe's Notes");
+
+    for note in notes {
+        feed = feed.item(note.into());
+    }
+
+    Ok(Json(feed.build()))
+}
+
+#[instrument(err)]
+pub async fn view(Path(id): Path<u64>) -> super::Result {
+    let conn = crate::establish_connection()?;
+
+    let mut stmt = conn.prepare(
+        "SELECT id, content, content_html, created_at, updated_at, deleted_at, reply_to FROM notes WHERE id = ?1"
+    )?;
+
+    let note = stmt.query_row(params![id], |row| {
+        Ok(Note {
+            id: row.get(0)?,
+            content: row.get(1)?,
+            content_html: row.get(2)?,
+            created_at: row.get(3)?,
+            updated_at: row.get(4)?,
+            deleted_at: row.get(5)?,
+            reply_to: row.get(6)?,
+        })
+    })?;
+
+    let mut result: Vec<u8> = vec![];
+    templates::notepost_html(&mut result, note)?;
+    Ok(Html(result))
+}
diff --git a/src/main.rs b/src/main.rs
index cdcae68..76196a8 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -12,6 +12,7 @@ use axum::{
 use color_eyre::eyre::Result;
 use hyper::StatusCode;
 use prometheus::{Encoder, TextEncoder};
+use rusqlite::Connection;
 use sdnotify::SdNotify;
 use std::{
     env, io,
@@ -28,6 +29,7 @@ use tower_http::{
 
 pub mod app;
 pub mod handlers;
+pub mod migrate;
 pub mod post;
 pub mod signalboost;
 pub mod tmpl;
@@ -39,6 +41,11 @@ use crate::app::poke;
 
 const APPLICATION_NAME: &str = concat!(env!("CARGO_PKG_NAME"), "/", env!("CARGO_PKG_VERSION"));
 
+pub fn establish_connection() -> handlers::Result<Connection> {
+    let database_url = env::var("DATABASE_URL").unwrap_or("./xesite.db".to_string());
+    Ok(Connection::open(&database_url)?)
+}
+
 async fn healthcheck() -> &'static str {
     "OK"
 }
@@ -72,6 +79,8 @@ async fn main() -> Result<()> {
     tracing_subscriber::fmt::init();
     info!("starting up commit {}", env!("GITHUB_SHA"));
 
+    migrate::run()?;
+
     let state = Arc::new(
         app::init(
             env::var("CONFIG_FNAME")
@@ -189,6 +198,10 @@ async fn main() -> Result<()> {
         .route("/talks", get(handlers::talks::index))
         .route("/talks/", get(handlers::talks::index))
         .route("/talks/:name", get(handlers::talks::post_view))
+        // notes
+        .route("/notes", get(handlers::notes::index))
+        .route("/notes.json", get(handlers::notes::feed))
+        .route("/notes/:id", get(handlers::notes::view))
         // junk google wants
         .route("/sitemap.xml", get(handlers::feeds::sitemap))
         // static files
diff --git a/src/migrate/base_schema.sql b/src/migrate/base_schema.sql
new file mode 100644
index 0000000..65d19d0
--- /dev/null
+++ b/src/migrate/base_schema.sql
@@ -0,0 +1,11 @@
+CREATE TABLE IF NOT EXISTS notes
+  ( id           INTEGER PRIMARY KEY
+  , content      TEXT    NOT NULL
+  , content_html TEXT    NOT NULL
+  , created_at   TEXT    NOT NULL     -- Unix epoch timestamp
+  , updated_at   TEXT                 -- Unix epoch timestamp
+  , deleted_at   TEXT                 -- Unix epoch timestamp
+  , reply_to     TEXT
+  );
+
+CREATE INDEX IF NOT EXISTS notes_reply_to ON notes(reply_to);
diff --git a/src/migrate/mod.rs b/src/migrate/mod.rs
new file mode 100644
index 0000000..a2eb9c3
--- /dev/null
+++ b/src/migrate/mod.rs
@@ -0,0 +1,16 @@
+use super::establish_connection;
+use color_eyre::eyre::Result;
+use rusqlite_migration::{Migrations, M};
+
+#[instrument(err)]
+pub fn run() -> Result<()> {
+    info!("running");
+    let mut conn = establish_connection()?;
+
+    let migrations = Migrations::new(vec![M::up(include_str!("./base_schema.sql"))]);
+    conn.pragma_update(None, "journal_mode", &"WAL").unwrap();
+
+    migrations.to_latest(&mut conn)?;
+
+    Ok(())
+}
diff --git a/templates/notepost.rs.html b/templates/notepost.rs.html
new file mode 100644
index 0000000..4af995b
--- /dev/null
+++ b/templates/notepost.rs.html
@@ -0,0 +1,28 @@
+@use super::{header_html, footer_html};
+@use crate::handlers::notes::Note;
+
+@(note: Note)
+
+@:header_html(Some(&format!("Note written at {}", note.detrytemci())), None)
+
+<!-- Twitter -->
+<meta name="twitter:card" content="summary" />
+<meta name="twitter:site" content="@@theprincessxena" />
+<meta name="twitter:title" content="@note.detrytemci()" />
+
+<!-- Facebook -->
+<meta property="og:type" content="website" />
+<meta property="og:title" content="@note.detrytemci()" />
+<meta property="og:site_name" content="Xe's Notes" />
+
+<!-- Description -->
+<meta name="description" content="@note.detrytemci() - Xe's Notes" />
+<meta name="author" content="Xe Iaso">
+
+<link rel="canonical" href="https://xeiaso.net/notes/@note.id" />
+
+<h1>Note written at @note.detrytemci()</h1>
+
+@Html(note.to_html().0)
+
+@:footer_html()
diff --git a/templates/notesindex.rs.html b/templates/notesindex.rs.html
new file mode 100644
index 0000000..c1fa08a
--- /dev/null
+++ b/templates/notesindex.rs.html
@@ -0,0 +1,14 @@
+@use crate::handlers::notes::Note;
+@use super::{header_html, footer_html};
+
+@(notes: Vec<Note>)
+
+@:header_html(Some("Notes"), None)
+
+<h1>Notes</h1>
+
+@for note in notes {
+    @Html(note.to_html().0)
+}
+
+@:footer_html()