refactor model-set, session, models/{blog,csrf,page,project,role,session,site,state,user}

12 files changed, 937 insertions, 948 deletions

diff --git a/src/guff.cr b/src/guff.cr
index 9696e5d..f0e2671 100644
--- a/src/guff.cr
+++ b/src/guff.cr
@@ -11,14 +11,6 @@ end
 
 require "./guff/**"
 
-private macro define_lazy_getters(hash)
-  {% for name, klass in hash %}
-    def {{ name.id }} : {{ klass.id }}
-      (@cached_{{ name.id }} ||= {{ klass.id }}.new(@context))
-    end
-  {% end %}
-end
-
 private macro include_api_modules(modules)
   {% for mod in modules.resolve %}
     include {{ mod.id }}
@@ -46,946 +38,6 @@ private macro api_method_dispatch(modules)
 end
 
 module Guff
-  module Models
-    class PageModel < Model
-      SQL = {
-        get_id: "
-          SELECT b.post_id
-
-            FROM sites a
-            JOIN posts b
-              ON (b.site_id = a.site_id)
-            JOIN pages c
-              ON (c.post_id = b.post_id)
-            JOIN states d
-              ON (d.state_id = b.state_id)
-
-           WHERE a.site_id = ?
-             AND b.slug = ?
-             AND d.state = 'public'
-
-           ORDER BY b.created_at DESC
-           LIMIT 1
-        ",
-
-        add: "
-          INSERT INTO pages(post_id, layout_id)
-            VALUES (?, (SELECT layout_id FROM layouts WHERE layout = 'default'))
-        ",
-
-        set: "
-          UPDATE pages
-             SET layout_id = (SELECT layout_id FROM layouts WHERE layout = ?)
-           WHERE post_id = ?
-        ",
-
-        get: "
-          SELECT a.post_id,
-                 a.site_id,
-                 c.state,
-                 a.posted_at,
-                 a.expires_at,
-                 a.slug,
-                 a.slug_lock,
-                 a.name,
-                 a.body,
-                 d.layout
-
-            FROM posts a
-            JOIN pages b
-              ON (b.post_id = a.post_id)
-            JOIN states c
-              ON (c.state_id = a.state_id)
-            JOIN layouts d
-              ON (d.layout_id = b.layout_id)
-
-           WHERE a.post_id = ?
-        ",
-      }
-
-      def get_id(
-        site_id : Int64,
-        slug    : String
-      ) : Int64?
-        r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug])
-        r ? r.to_i64 : nil
-      end
-
-      def add(
-        site_id : Int64,
-        user_id : Int64,
-      ) : Int64
-        db = @context.dbs.rw
-        post_id = -1_i64
-
-        db.transaction do
-          post_id = @context.models.post.add(site_id, user_id)
-          db.query(SQL[:add], [post_id.to_s])
-        end
-
-        post_id
-      end
-
-      def set(
-        post_id         : Int64,
-
-        site_id         : Int64?  = nil,
-        state           : String? = nil,
-
-        have_posted_at  : Bool    = false,
-        posted_at       : Time?   = nil,
-
-        have_expires_at : Bool    = false,
-        expires_at      : Time?   = nil,
-
-        slug            : String? = nil,
-        slug_lock       : Bool?   = nil,
-
-        name            : String? = nil,
-        body            : String? = nil,
-
-        layout          : String? = nil,
-      )
-        db = @context.dbs.rw
-
-        db.transaction do
-          @context.models.post.set(
-            post_id:          post_id,
-
-            site_id:          site_id,
-            state:            state,
-
-            have_posted_at:   have_posted_at,
-            posted_at:        posted_at,
-
-            have_expires_at:  have_expires_at,
-            expires_at:       expires_at,
-
-            slug:             slug,
-            slug_lock:        slug_lock,
-
-            name:             name,
-            body:             body,
-          )
-
-          if layout
-            db.query(SQL[:set], [layout, post_id.to_s])
-          end
-        end
-      end
-
-      def get(post_id : Int64)
-        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
-      end
-    end
-
-    class ProjectModel < Model
-      SQL = {
-        get_id: "
-          SELECT b.post_id
-
-            FROM sites a
-            JOIN posts b
-              ON (b.site_id = a.site_id)
-            JOIN projects c
-              ON (c.post_id = b.post_id)
-            JOIN states d
-              ON (d.state_id = b.state_id)
-
-           WHERE a.site_id = ?
-             AND b.slug = ?
-             AND d.state = 'public'
-
-           ORDER BY b.created_at DESC
-           LIMIT 1
-        ",
-
-        add: "
-          INSERT INTO projects(post_id) VALUES (?)
-        ",
-
-        set: "
-          UPDATE projects
-             SET repo_url = ?
-           WHERE post_id = ?
-        ",
-
-        get: "
-          SELECT a.post_id,
-                 a.site_id,
-                 c.state,
-                 a.posted_at,
-                 a.expires_at,
-                 a.slug,
-                 a.slug_lock,
-                 a.name,
-                 a.body,
-                 b.repo_url
-
-            FROM posts a
-            JOIN projects b
-              ON (b.post_id = a.post_id)
-            JOIN states c
-              ON (c.state_id = a.state_id)
-
-           WHERE a.post_id = ?
-        ",
-      }
-
-      def get_id(
-        site_id : Int64,
-        slug    : String
-      ) : Int64?
-        r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug])
-        r ? r.to_i64 : nil
-      end
-
-      def add(
-        site_id : Int64,
-        user_id : Int64,
-      ) : Int64
-        db = @context.dbs.rw
-        post_id = -1_i64
-
-        db.transaction do
-          post_id = @context.models.post.add(site_id, user_id)
-          db.query(SQL[:add], [post_id.to_s])
-        end
-
-        post_id
-      end
-
-      def set(
-        post_id         : Int64,
-
-        site_id         : Int64?  = nil,
-        state           : String? = nil,
-
-        have_posted_at  : Bool    = false,
-        posted_at       : Time?   = nil,
-
-        have_expires_at : Bool    = false,
-        expires_at      : Time?   = nil,
-
-        slug            : String? = nil,
-        slug_lock       : Bool?   = nil,
-
-        name            : String? = nil,
-        body            : String? = nil,
-
-        repo_url        : String? = nil,
-      )
-        db = @context.dbs.rw
-
-        db.transaction do
-          @context.models.post.set(
-            post_id:          post_id,
-
-            site_id:          site_id,
-            state:            state,
-
-            have_posted_at:   have_posted_at,
-            posted_at:        posted_at,
-
-            have_expires_at:  have_expires_at,
-            expires_at:       expires_at,
-
-            slug:             slug,
-            slug_lock:        slug_lock,
-
-            name:             name,
-            body:             body,
-          )
-
-          if repo_url
-            db.query(SQL[:set], [repo_url, post_id.to_s])
-          end
-        end
-      end
-
-      def get(post_id : Int64)
-        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
-      end
-    end
-
-    class BlogModel < Model
-      SQL = {
-        get_ids: "
-          SELECT b.post_id
-
-            FROM sites a
-            JOIN posts b
-              ON (b.site_id = a.site_id)
-            JOIN blogs c
-              ON (c.post_id = b.post_id)
-            JOIN states d
-              ON (d.state_id = b.state_id)
-
-           WHERE a.site_id = ?
-             AND %s
-             AND d.state = 'public'
-             -- TODO: handle posted_at and expired_at
-
-           ORDER BY COALESCE(b.posted_at, b.created_at) DESC
-
-           LIMIT ? OFFSET ?
-        ",
-
-        add: "
-          INSERT INTO blogs(post_id) VALUES (?)
-        ",
-
-        get: "
-          SELECT a.post_id,
-                 a.site_id,
-                 c.state,
-                 a.posted_at,
-                 a.expires_at,
-                 a.slug,
-                 a.slug_lock,
-                 a.name,
-                 a.body
-
-            FROM posts a
-            JOIN blogs b
-              ON (b.post_id = a.post_id)
-            JOIN states c
-              ON (c.state_id = a.state_id)
-
-           WHERE a.post_id = ?
-        ",
-      }
-
-      def get_id(
-        site_id : Int64,
-        year    : Int32,
-        month   : Int32,
-        day     : Int32,
-        slug    : String
-      ) : Int64?
-        ids = get_ids(site_id, year, month, day, slug)
-        (ids.size > 0) ? ids.first : nil
-      end
-
-      # TODO: make this configurable
-      LIMIT = 50
-
-      def get_ids(
-        site_id : Int64,
-        year    : Int32?  = nil,
-        month   : Int32?  = nil,
-        day     : Int32?  = nil,
-        slug    : String? = nil,
-        page    : Int32?  = nil
-      ) : Array(Int64)
-        sql = ["1"]
-        args = [site_id.to_s]
-
-        # STDERR.puts "DEBUG: site_id = #{site_id}, year = #{year}, month = #{month}, day = #{day}, slug = \"#{slug}\""
-
-        if year
-          # add year filter
-          sql << "strftime('%Y', b.posted_at) + 0 = ? + 0"
-          args << year.to_s
-
-          if month
-            # add month filter
-            sql << "strftime('%m', b.posted_at) + 0 = ? + 0"
-            args << month.to_s
-
-            if day
-              # add day filter
-              sql << "strftime('%d', b.posted_at) + 0 = ? + 0"
-              args << day.to_s
-            end
-          end
-        end
-
-        if slug
-          # add slug filter
-          sql << "b.slug = ?"
-          args << slug
-        end
-
-        page ||= 1
-        raise "invalid page: #{page}" if page < 1
-
-        args << LIMIT.to_s
-        args << ((page - 1) * LIMIT).to_s
-
-        # STDERR.puts "DEBUG: args = #{args.to_json}"
-
-        # exec query, build result
-        r = [] of Int64
-        @context.dbs.ro.all(SQL[:get_ids] % sql.join(" AND "), args) do |row|
-          p row
-          r << row["post_id"] as Int64
-        end
-
-        # return results
-        r
-      end
-
-      def add(
-        site_id : Int64,
-        user_id : Int64,
-      ) : Int64
-        db = @context.dbs.rw
-        post_id = -1_i64
-
-        db.transaction do
-          post_id = @context.models.post.add(site_id, user_id)
-          db.query(SQL[:add], [post_id.to_s])
-        end
-
-        post_id
-      end
-
-      def set(
-        post_id         : Int64,
-
-        site_id         : Int64?  = nil,
-        state           : String? = nil,
-
-        have_posted_at  : Bool    = false,
-        posted_at       : Time?   = nil,
-
-        have_expires_at : Bool    = false,
-        expires_at      : Time?   = nil,
-
-        slug            : String? = nil,
-        slug_lock       : Bool?   = nil,
-
-        name            : String? = nil,
-        body            : String? = nil,
-
-        repo_url        : String? = nil,
-      )
-        @context.models.post.set(
-          post_id:          post_id,
-
-          site_id:          site_id,
-          state:            state,
-
-          have_posted_at:   have_posted_at,
-          posted_at:        posted_at,
-
-          have_expires_at:  have_expires_at,
-          expires_at:       expires_at,
-
-          slug:             slug,
-          slug_lock:        slug_lock,
-
-          name:             name,
-          body:             body,
-        )
-      end
-
-      def get(post_id : Int64)
-        @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
-      end
-    end
-
-    class UserModel < Model
-      SQL = {
-        login: "
-          SELECT user_id,
-                 password
-
-            FROM users
-
-           WHERE is_active
-             AND email = ?
-        ",
-
-        has_role: "
-          SELECT 1
-
-            FROM users
-
-           WHERE is_active
-             AND user_id = ?
-             AND role_id IN (SELECT role_id FROM roles WHERE role IN (%s))
-        ",
-
-        add: "
-          INSERT INTO users(role_id, name, email, password, is_active) VALUES
-            ((SELECT role_id FROM roles where role = ?), ?, ?, ?, ?)
-        ",
-
-        set: "
-          UPDATE users
-             SET %s
-           WHERE user_id = ?
-        ",
-
-        get_users: "
-          SELECT a.user_id,
-                 a.name,
-                 a.email,
-                 a.is_active,
-                 b.role,
-                 b.name AS role_name
-
-            FROM users a
-            JOIN roles b
-              ON (b.role_id = a.role_id)
-
-           ORDER BY LOWER(a.name)
-        ",
-
-        get: "
-          SELECT a.user_id,
-                 a.name,
-                 a.email,
-                 a.is_active,
-                 b.role,
-                 b.name AS role_name
-
-            FROM users a
-            JOIN roles b
-              ON (b.role_id = a.role_id)
-
-           WHERE a.user_id = ?
-        ",
-      }
-
-      def login(
-        email     : String,
-        password  : String
-      ) : Int64?
-        r = nil
-
-        if row = @context.dbs.ro.row(SQL[:login], [email])
-          if Password.test(row["password"] as String, password)
-            # given email and password matches active user
-            r = row["user_id"] as Int64
-          end
-        end
-
-        # return result
-        r
-      end
-
-      def has_role?(user_id : Int64, roles : Array(String))
-        raise "empty role list" unless roles.size > 0
-
-        !!@context.dbs.ro.one(SQL[:has_role] % [
-          (["?"] * roles.size).join(",")
-        ], [user_id.to_s].concat(roles))
-      end
-
-      def add(
-        name      : String,
-        email     : String,
-        password  : String,
-        role      : String,
-        active    : Bool,
-      ) : Int64
-        @context.dbs.rw.query(SQL[:add], [
-          role,
-          name,
-          email,
-          Password.create(password),
-          active ? "1" : "0",
-        ])
-
-        @context.dbs.rw.last_insert_row_id.to_i64
-      end
-
-      def set(
-        user_id   : Int64,
-        name      : String? = nil,
-        email     : String? = nil,
-        password  : String? = nil,
-        role      : String? = nil,
-        active    : Bool?   = nil,
-      )
-        sets = [] of String
-        args = [] of String
-
-        if name
-          sets << "name = ?"
-          args << name
-        end
-
-        if email
-          sets << "email = ?"
-          args << email
-        end
-
-        if role
-          sets << "role_id = (SELECT role_id FROM roles WHERE role = ?)"
-          args << role
-        end
-
-        if password
-          sets << "password = ?"
-          args << Password.create(password)
-        end
-
-        if active != nil
-          sets << "is_active = ?"
-          args << (active ? "1" : "0")
-        end
-
-        if sets.size > 0
-          args << user_id.to_s
-          @context.dbs.rw.query(SQL[:set] % sets.join(", "), args)
-        end
-      end
-
-      def get_users
-        rows = [] of Hash(String, String)
-
-        @context.dbs.ro.all(SQL[:get_users]) do |row|
-          # append row to result
-          rows << row.reduce({} of String => String) do |r, kv|
-            r[kv[0]] = kv[1].to_s
-            r
-          end
-        end
-
-        rows
-      end
-
-      def get(user_id : Int64)
-        row = @context.dbs.ro.row(SQL[:get], [user_id.to_s])
-        raise "unknown user: #{user_id}" unless row
-
-        row.reduce({} of String => String) do |r, kv|
-          r[kv[0]] = kv[1].to_s
-          r
-        end
-      end
-    end
-
-    # TODO: handle session expiration
-    class SessionModel < Model
-      SQL = {
-        load: "
-          SELECT data
-
-            FROM sessions
-
-           WHERE id = ?
-             -- TODO:
-             -- AND strftime('%s', created_at, '1 week') > strftime('%s')
-             -- AND strftime('%s', updated_at, '2 hours') > strftime('%s')
-        ",
-
-        save: "
-          UPDATE sessions
-
-             SET updated_at = CURRENT_TIMESTAMP,
-                 data = ?
-
-           WHERE id = ?
-        ",
-
-        delete: "
-          DELETE FROM sessions WHERE id = ?
-        ",
-
-        create: "
-          INSERT INTO sessions(id, data) VALUES (?, ?)
-        ",
-      }
-
-      def load(id : String) : String?
-        @context.dbs.ro.one(SQL[:load], [id])
-      end
-
-      def save(id : String, data : String)
-        @context.dbs.rw.query(SQL[:save], [data, id])
-        nil
-      end
-
-      def delete(id : String?)
-        @context.dbs.rw.query(SQL[:delete], [id]) if id
-        nil
-      end
-
-      def create(data : String) : String
-        # generate id
-        r = SecureRandom.hex(32)
-
-        # save session
-        @context.dbs.rw.query(SQL[:create], [r, data])
-
-        # return session id
-        r
-      end
-    end
-
-    class CSRFModel < Model
-      getter :minutes
-
-      def initialize(context : Context)
-        super(context)
-        @cache = {} of String => Int64
-
-        # expire form after 5 minutes
-        # TODO: make this configurable
-        @minutes = 5
-      end
-
-      def create_token
-        remove_expired_tokens
-
-        # generate and cache new token
-        r = SecureRandom.hex(16)
-        @cache[r] = Time.now.epoch + 60 * @minutes
-
-        # return token
-        r
-      end
-
-      def use_token(id : String)
-        remove_expired_tokens
-
-        if @cache.has_key?(id)
-          # remove token, return success
-          @cache.delete(id)
-          true
-        else
-          # return failure
-          false
-        end
-      end
-
-      private def remove_expired_tokens
-        now = Time.now.epoch
-
-        # FIXME: limit the size of the cache
-        # to prevent insane memory use
-
-        # remove expired entries
-        @cache.delete_if do |key, val|
-          val < now
-        end
-      end
-    end
-
-    class StateModel < Model
-      SQL = {
-        get_states: "
-          SELECT state_id,
-                 state,
-                 name,
-                 icon
-
-            FROM states
-
-           ORDER BY sort
-        ",
-      }
-
-      def get_states
-        rows = [] of Hash(String, String)
-
-        @context.dbs.ro.all(SQL[:get_states]) do |row|
-          rows << row.reduce({} of String => String) do |r, kv|
-            r[kv[0]] = kv[1].to_s
-            r
-          end
-        end
-
-        rows
-      end
-    end
-
-    class SiteModel < Model
-      SQL = {
-        get_id: "
-          SELECT site_id
-
-            FROM (
-              SELECT a.site_id
-
-                FROM site_domains a
-                JOIN sites b
-                  ON (b.site_id = a.site_id)
-
-               WHERE b.is_active
-                 AND a.domain = $1
-
-              UNION ALL
-
-              SELECT site_id
-
-                FROM sites
-
-               WHERE is_active
-                 AND is_default
-            ) a
-
-          LIMIT 1
-        ",
-
-        get_default_id: "
-          SELECT site_id
-
-            FROM sites
-
-           WHERE is_active
-             AND is_default
-        ",
-
-        get_sites: "
-          SELECT site_id,
-                 name,
-                 is_active,
-                 is_default
-
-            FROM sites
-
-           ORDER BY LOWER(name)
-
-        ",
-      }
-
-      def get_id(host : String?) : Int64?
-        r = @context.dbs.ro.one(SQL[:get_id], [host || ""])
-        r ? r.to_i64 : nil
-      end
-
-      def get_default_id : Int64
-        @context.dbs.ro.one(SQL[:get_default_id]).not_nil!.to_i64
-      end
-
-      def get_sites
-        rows = [] of Hash(String, String)
-
-        @context.dbs.ro.all(SQL[:get_sites]) do |row|
-          rows << row.reduce({} of String => String) do |r, kv|
-            r[kv[0]] = kv[1].to_s
-            r
-          end
-        end
-
-        rows
-      end
-    end
-
-    class RoleModel < Model
-      SQL = {
-        get_roles: "
-          SELECT role,
-                 name
-
-            FROM roles
-
-           ORDER BY sort
-        ",
-      }
-
-      def get_roles : Array(Hash(String, String))
-        r = [] of Hash(String, String)
-
-        @context.dbs.ro.all(SQL[:get_roles]) do |row|
-          r << {
-            "role" => row["role"] as String,
-            "name" => row["name"] as String,
-          }
-        end
-
-        r
-      end
-    end
-  end
-
-  class ModelSet
-    def initialize(@context : Context)
-    end
-
-    define_lazy_getters({
-      user:     Models::UserModel,
-      session:  Models::SessionModel,
-      csrf:     Models::CSRFModel,
-      post:     Models::PostModel,
-      page:     Models::PageModel,
-      project:  Models::ProjectModel,
-      blog:     Models::BlogModel,
-      site:     Models::SiteModel,
-      role:     Models::RoleModel,
-      state:    Models::StateModel,
-    })
-  end
-
-  class Session < Hash(String, String)
-    getter :session_id
-
-    # session cookie name
-    # FIXME: does this belong here?
-    COOKIE = "guff_session"
-
-    def initialize(@context : Context)
-      super()
-      @session_id = nil
-    end
-
-    def load(id : String)
-      begin
-        # clear existing session
-        clear
-
-        # load session values
-        JSON.parse(@context.models.session.load(id).not_nil!).each do |key, val|
-          self[key.as_s] = val.as_s
-        end
-
-        # save session id
-        @session_id = id
-
-        # return success
-        true
-      rescue err
-        STDERR.puts "session load failed: #{err}"
-        # invalid session id, return failure
-        false
-      end
-    end
-
-    def create(hash : Hash(String, String)) : String
-      clear
-      merge!(hash)
-      @session_id = @context.models.session.create(hash.to_json)
-    end
-
-    def save
-      if valid?
-        @context.models.session.save(@session_id.not_nil!, to_json)
-
-        # return success
-        true
-      else
-        # no session, return failure
-        false
-      end
-    end
-
-    def clear
-      super
-      @session_id = nil
-    end
-
-    def delete : String?
-      r = @session_id
-
-      if valid?
-        @context.models.session.delete(r)
-        clear
-      end
-
-      r
-    end
-
-    def valid?
-      @session_id != nil
-    end
-  end
-
   module Views
     abstract class View
       def initialize(@context : Context)
diff --git a/src/guff/model-set.cr b/src/guff/model-set.cr
new file mode 100644
index 0000000..e4c4616
--- /dev/null
+++ b/src/guff/model-set.cr
@@ -0,0 +1,25 @@
+private macro define_lazy_getters(hash)
+  {% for name, klass in hash %}
+    def {{ name.id }} : {{ klass.id }}
+      (@cached_{{ name.id }} ||= {{ klass.id }}.new(@context))
+    end
+  {% end %}
+end
+
+class Guff::ModelSet
+  def initialize(@context : Context)
+  end
+
+  define_lazy_getters({
+    user:     Models::UserModel,
+    session:  Models::SessionModel,
+    csrf:     Models::CSRFModel,
+    post:     Models::PostModel,
+    page:     Models::PageModel,
+    project:  Models::ProjectModel,
+    blog:     Models::BlogModel,
+    site:     Models::SiteModel,
+    role:     Models::RoleModel,
+    state:    Models::StateModel,
+  })
+end
diff --git a/src/guff/models/blog.cr b/src/guff/models/blog.cr
new file mode 100644
index 0000000..9c25b77
--- /dev/null
+++ b/src/guff/models/blog.cr
@@ -0,0 +1,179 @@
+require "./model"
+
+class Guff::Models::BlogModel < Guff::Models::Model
+  SQL = {
+    get_ids: "
+      SELECT b.post_id
+
+        FROM sites a
+        JOIN posts b
+          ON (b.site_id = a.site_id)
+        JOIN blogs c
+          ON (c.post_id = b.post_id)
+        JOIN states d
+          ON (d.state_id = b.state_id)
+
+       WHERE a.site_id = ?
+         AND %s
+         AND d.state = 'public'
+         -- TODO: handle posted_at and expired_at
+
+       ORDER BY COALESCE(b.posted_at, b.created_at) DESC
+
+       LIMIT ? OFFSET ?
+    ",
+
+    add: "
+      INSERT INTO blogs(post_id) VALUES (?)
+    ",
+
+    get: "
+      SELECT a.post_id,
+             a.site_id,
+             c.state,
+             a.posted_at,
+             a.expires_at,
+             a.slug,
+             a.slug_lock,
+             a.name,
+             a.body
+
+        FROM posts a
+        JOIN blogs b
+          ON (b.post_id = a.post_id)
+        JOIN states c
+          ON (c.state_id = a.state_id)
+
+       WHERE a.post_id = ?
+    ",
+  }
+
+  def get_id(
+    site_id : Int64,
+    year    : Int32,
+    month   : Int32,
+    day     : Int32,
+    slug    : String
+  ) : Int64?
+    ids = get_ids(site_id, year, month, day, slug)
+    (ids.size > 0) ? ids.first : nil
+  end
+
+  # TODO: make this configurable
+  LIMIT = 50
+
+  def get_ids(
+    site_id : Int64,
+    year    : Int32?  = nil,
+    month   : Int32?  = nil,
+    day     : Int32?  = nil,
+    slug    : String? = nil,
+    page    : Int32?  = nil
+  ) : Array(Int64)
+    sql = ["1"]
+    args = [site_id.to_s]
+
+    # STDERR.puts "DEBUG: site_id = #{site_id}, year = #{year}, month = #{month}, day = #{day}, slug = \"#{slug}\""
+
+    if year
+      # add year filter
+      sql << "strftime('%Y', b.posted_at) + 0 = ? + 0"
+      args << year.to_s
+
+      if month
+        # add month filter
+        sql << "strftime('%m', b.posted_at) + 0 = ? + 0"
+        args << month.to_s
+
+        if day
+          # add day filter
+          sql << "strftime('%d', b.posted_at) + 0 = ? + 0"
+          args << day.to_s
+        end
+      end
+    end
+
+    if slug
+      # add slug filter
+      sql << "b.slug = ?"
+      args << slug
+    end
+
+    page ||= 1
+    raise "invalid page: #{page}" if page < 1
+
+    args << LIMIT.to_s
+    args << ((page - 1) * LIMIT).to_s
+
+    # STDERR.puts "DEBUG: args = #{args.to_json}"
+
+    # exec query, build result
+    r = [] of Int64
+    @context.dbs.ro.all(SQL[:get_ids] % sql.join(" AND "), args) do |row|
+      p row
+      r << row["post_id"] as Int64
+    end
+
+    # return results
+    r
+  end
+
+  def add(
+    site_id : Int64,
+    user_id : Int64,
+  ) : Int64
+    db = @context.dbs.rw
+    post_id = -1_i64
+
+    db.transaction do
+      post_id = @context.models.post.add(site_id, user_id)
+      db.query(SQL[:add], [post_id.to_s])
+    end
+
+    post_id
+  end
+
+  def set(
+    post_id         : Int64,
+
+    site_id         : Int64?  = nil,
+    state           : String? = nil,
+
+    have_posted_at  : Bool    = false,
+    posted_at       : Time?   = nil,
+
+    have_expires_at : Bool    = false,
+    expires_at      : Time?   = nil,
+
+    slug            : String? = nil,
+    slug_lock       : Bool?   = nil,
+
+    name            : String? = nil,
+    body            : String? = nil,
+
+    repo_url        : String? = nil,
+  )
+    @context.models.post.set(
+      post_id:          post_id,
+
+      site_id:          site_id,
+      state:            state,
+
+      have_posted_at:   have_posted_at,
+      posted_at:        posted_at,
+
+      have_expires_at:  have_expires_at,
+      expires_at:       expires_at,
+
+      slug:             slug,
+      slug_lock:        slug_lock,
+
+      name:             name,
+      body:             body,
+    )
+  end
+
+  def get(post_id : Int64)
+    @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
+  end
+end
diff --git a/src/guff/models/csrf.cr b/src/guff/models/csrf.cr
new file mode 100644
index 0000000..e719e02
--- /dev/null
+++ b/src/guff/models/csrf.cr
@@ -0,0 +1,48 @@
+class Guff::Models::CSRFModel < Guff::Models::Model
+  getter :minutes
+
+  def initialize(context : Context)
+    super(context)
+    @cache = {} of String => Int64
+
+    # expire form after 5 minutes
+    # TODO: make this configurable
+    @minutes = 5
+  end
+
+  def create_token
+    remove_expired_tokens
+
+    # generate and cache new token
+    r = SecureRandom.hex(16)
+    @cache[r] = Time.now.epoch + 60 * @minutes
+
+    # return token
+    r
+  end
+
+  def use_token(id : String)
+    remove_expired_tokens
+
+    if @cache.has_key?(id)
+      # remove token, return success
+      @cache.delete(id)
+      true
+    else
+      # return failure
+      false
+    end
+  end
+
+  private def remove_expired_tokens
+    now = Time.now.epoch
+
+    # FIXME: limit the size of the cache
+    # to prevent insane memory use
+
+    # remove expired entries
+    @cache.delete_if do |key, val|
+      val < now
+    end
+  end
+end
diff --git a/src/guff/models/page.cr b/src/guff/models/page.cr
new file mode 100644
index 0000000..b30e94a
--- /dev/null
+++ b/src/guff/models/page.cr
@@ -0,0 +1,131 @@
+class Guff::Models::PageModel < Guff::Models::Model
+  SQL = {
+    get_id: "
+      SELECT b.post_id
+
+        FROM sites a
+        JOIN posts b
+          ON (b.site_id = a.site_id)
+        JOIN pages c
+          ON (c.post_id = b.post_id)
+        JOIN states d
+          ON (d.state_id = b.state_id)
+
+       WHERE a.site_id = ?
+         AND b.slug = ?
+         AND d.state = 'public'
+
+       ORDER BY b.created_at DESC
+       LIMIT 1
+    ",
+
+    add: "
+      INSERT INTO pages(post_id, layout_id)
+        VALUES (?, (SELECT layout_id FROM layouts WHERE layout = 'default'))
+    ",
+
+    set: "
+      UPDATE pages
+         SET layout_id = (SELECT layout_id FROM layouts WHERE layout = ?)
+       WHERE post_id = ?
+    ",
+
+    get: "
+      SELECT a.post_id,
+             a.site_id,
+             c.state,
+             a.posted_at,
+             a.expires_at,
+             a.slug,
+             a.slug_lock,
+             a.name,
+             a.body,
+             d.layout
+
+        FROM posts a
+        JOIN pages b
+          ON (b.post_id = a.post_id)
+        JOIN states c
+          ON (c.state_id = a.state_id)
+        JOIN layouts d
+          ON (d.layout_id = b.layout_id)
+
+       WHERE a.post_id = ?
+    ",
+  }
+
+  def get_id(
+    site_id : Int64,
+    slug    : String
+  ) : Int64?
+    r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug])
+    r ? r.to_i64 : nil
+  end
+
+  def add(
+    site_id : Int64,
+    user_id : Int64,
+  ) : Int64
+    db = @context.dbs.rw
+    post_id = -1_i64
+
+    db.transaction do
+      post_id = @context.models.post.add(site_id, user_id)
+      db.query(SQL[:add], [post_id.to_s])
+    end
+
+    post_id
+  end
+
+  def set(
+    post_id         : Int64,
+
+    site_id         : Int64?  = nil,
+    state           : String? = nil,
+
+    have_posted_at  : Bool    = false,
+    posted_at       : Time?   = nil,
+
+    have_expires_at : Bool    = false,
+    expires_at      : Time?   = nil,
+
+    slug            : String? = nil,
+    slug_lock       : Bool?   = nil,
+
+    name            : String? = nil,
+    body            : String? = nil,
+
+    layout          : String? = nil,
+  )
+    db = @context.dbs.rw
+
+    db.transaction do
+      @context.models.post.set(
+        post_id:          post_id,
+
+        site_id:          site_id,
+        state:            state,
+
+        have_posted_at:   have_posted_at,
+        posted_at:        posted_at,
+
+        have_expires_at:  have_expires_at,
+        expires_at:       expires_at,
+
+        slug:             slug,
+        slug_lock:        slug_lock,
+
+        name:             name,
+        body:             body,
+      )
+
+      if layout
+        db.query(SQL[:set], [layout, post_id.to_s])
+      end
+    end
+  end
+
+  def get(post_id : Int64)
+    @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
+  end
+end
diff --git a/src/guff/models/project.cr b/src/guff/models/project.cr
new file mode 100644
index 0000000..2ead9db
--- /dev/null
+++ b/src/guff/models/project.cr
@@ -0,0 +1,128 @@
+class Guff::Models::ProjectModel < Guff::Models::Model
+  SQL = {
+    get_id: "
+      SELECT b.post_id
+
+        FROM sites a
+        JOIN posts b
+          ON (b.site_id = a.site_id)
+        JOIN projects c
+          ON (c.post_id = b.post_id)
+        JOIN states d
+          ON (d.state_id = b.state_id)
+
+       WHERE a.site_id = ?
+         AND b.slug = ?
+         AND d.state = 'public'
+
+       ORDER BY b.created_at DESC
+       LIMIT 1
+    ",
+
+    add: "
+      INSERT INTO projects(post_id) VALUES (?)
+    ",
+
+    set: "
+      UPDATE projects
+         SET repo_url = ?
+       WHERE post_id = ?
+    ",
+
+    get: "
+      SELECT a.post_id,
+             a.site_id,
+             c.state,
+             a.posted_at,
+             a.expires_at,
+             a.slug,
+             a.slug_lock,
+             a.name,
+             a.body,
+             b.repo_url
+
+        FROM posts a
+        JOIN projects b
+          ON (b.post_id = a.post_id)
+        JOIN states c
+          ON (c.state_id = a.state_id)
+
+       WHERE a.post_id = ?
+    ",
+  }
+
+  def get_id(
+    site_id : Int64,
+    slug    : String
+  ) : Int64?
+    r = @context.dbs.ro.one(SQL[:get_id], [site_id.to_s, slug])
+    r ? r.to_i64 : nil
+  end
+
+  def add(
+    site_id : Int64,
+    user_id : Int64,
+  ) : Int64
+    db = @context.dbs.rw
+    post_id = -1_i64
+
+    db.transaction do
+      post_id = @context.models.post.add(site_id, user_id)
+      db.query(SQL[:add], [post_id.to_s])
+    end
+
+    post_id
+  end
+
+  def set(
+    post_id         : Int64,
+
+    site_id         : Int64?  = nil,
+    state           : String? = nil,
+
+    have_posted_at  : Bool    = false,
+    posted_at       : Time?   = nil,
+
+    have_expires_at : Bool    = false,
+    expires_at      : Time?   = nil,
+
+    slug            : String? = nil,
+    slug_lock       : Bool?   = nil,
+
+    name            : String? = nil,
+    body            : String? = nil,
+
+    repo_url        : String? = nil,
+  )
+    db = @context.dbs.rw
+
+    db.transaction do
+      @context.models.post.set(
+        post_id:          post_id,
+
+        site_id:          site_id,
+        state:            state,
+
+        have_posted_at:   have_posted_at,
+        posted_at:        posted_at,
+
+        have_expires_at:  have_expires_at,
+        expires_at:       expires_at,
+
+        slug:             slug,
+        slug_lock:        slug_lock,
+
+        name:             name,
+        body:             body,
+      )
+
+      if repo_url
+        db.query(SQL[:set], [repo_url, post_id.to_s])
+      end
+    end
+  end
+
+  def get(post_id : Int64)
+    @context.dbs.ro.row(SQL[:get], [post_id.to_s]).not_nil!
+  end
+end
diff --git a/src/guff/models/role.cr b/src/guff/models/role.cr
new file mode 100644
index 0000000..f2e84a5
--- /dev/null
+++ b/src/guff/models/role.cr
@@ -0,0 +1,25 @@
+class Guff::Models::RoleModel < Guff::Models::Model
+  SQL = {
+    get_roles: "
+      SELECT role,
+             name
+
+        FROM roles
+
+       ORDER BY sort
+    ",
+  }
+
+  def get_roles : Array(Hash(String, String))
+    r = [] of Hash(String, String)
+
+    @context.dbs.ro.all(SQL[:get_roles]) do |row|
+      r << {
+        "role" => row["role"] as String,
+        "name" => row["name"] as String,
+      }
+    end
+
+    r
+  end
+end
diff --git a/src/guff/models/session.cr b/src/guff/models/session.cr
new file mode 100644
index 0000000..b715d6f
--- /dev/null
+++ b/src/guff/models/session.cr
@@ -0,0 +1,57 @@
+# TODO: handle session expiration
+class Guff::Models::SessionModel < Guff::Models::Model
+  SQL = {
+    load: "
+      SELECT data
+
+        FROM sessions
+
+       WHERE id = ?
+         -- TODO:
+         -- AND strftime('%s', created_at, '1 week') > strftime('%s')
+         -- AND strftime('%s', updated_at, '2 hours') > strftime('%s')
+    ",
+
+    save: "
+      UPDATE sessions
+
+         SET updated_at = CURRENT_TIMESTAMP,
+             data = ?
+
+       WHERE id = ?
+    ",
+
+    delete: "
+      DELETE FROM sessions WHERE id = ?
+    ",
+
+    create: "
+      INSERT INTO sessions(id, data) VALUES (?, ?)
+    ",
+  }
+
+  def load(id : String) : String?
+    @context.dbs.ro.one(SQL[:load], [id])
+  end
+
+  def save(id : String, data : String)
+    @context.dbs.rw.query(SQL[:save], [data, id])
+    nil
+  end
+
+  def delete(id : String?)
+    @context.dbs.rw.query(SQL[:delete], [id]) if id
+    nil
+  end
+
+  def create(data : String) : String
+    # generate id
+    r = SecureRandom.hex(32)
+
+    # save session
+    @context.dbs.rw.query(SQL[:create], [r, data])
+
+    # return session id
+    r
+  end
+end
diff --git a/src/guff/models/site.cr b/src/guff/models/site.cr
new file mode 100644
index 0000000..8ab4162
--- /dev/null
+++ b/src/guff/models/site.cr
@@ -0,0 +1,72 @@
+class Guff::Models::SiteModel < Guff::Models::Model
+  SQL = {
+    get_id: "
+      SELECT site_id
+
+        FROM (
+          SELECT a.site_id
+
+            FROM site_domains a
+            JOIN sites b
+              ON (b.site_id = a.site_id)
+
+           WHERE b.is_active
+             AND a.domain = $1
+
+          UNION ALL
+
+          SELECT site_id
+
+            FROM sites
+
+           WHERE is_active
+             AND is_default
+        ) a
+
+      LIMIT 1
+    ",
+
+    get_default_id: "
+      SELECT site_id
+
+        FROM sites
+
+       WHERE is_active
+         AND is_default
+    ",
+
+    get_sites: "
+      SELECT site_id,
+             name,
+             is_active,
+             is_default
+
+        FROM sites
+
+       ORDER BY LOWER(name)
+
+    ",
+  }
+
+  def get_id(host : String?) : Int64?
+    r = @context.dbs.ro.one(SQL[:get_id], [host || ""])
+    r ? r.to_i64 : nil
+  end
+
+  def get_default_id : Int64
+    @context.dbs.ro.one(SQL[:get_default_id]).not_nil!.to_i64
+  end
+
+  def get_sites
+    rows = [] of Hash(String, String)
+
+    @context.dbs.ro.all(SQL[:get_sites]) do |row|
+      rows << row.reduce({} of String => String) do |r, kv|
+        r[kv[0]] = kv[1].to_s
+        r
+      end
+    end
+
+    rows
+  end
+end
diff --git a/src/guff/models/state.cr b/src/guff/models/state.cr
new file mode 100644
index 0000000..07557e9
--- /dev/null
+++ b/src/guff/models/state.cr
@@ -0,0 +1,27 @@
+class Guff::Models::StateModel < Guff::Models::Model
+  SQL = {
+    get_states: "
+      SELECT state_id,
+             state,
+             name,
+             icon
+
+        FROM states
+
+       ORDER BY sort
+    ",
+  }
+
+  def get_states
+    rows = [] of Hash(String, String)
+
+    @context.dbs.ro.all(SQL[:get_states]) do |row|
+      rows << row.reduce({} of String => String) do |r, kv|
+        r[kv[0]] = kv[1].to_s
+        r
+      end
+    end
+
+    rows
+  end
+end
diff --git a/src/guff/models/user.cr b/src/guff/models/user.cr
new file mode 100644
index 0000000..62538e1
--- /dev/null
+++ b/src/guff/models/user.cr
@@ -0,0 +1,173 @@
+class Guff::Models::UserModel < Guff::Models::Model
+  SQL = {
+    login: "
+      SELECT user_id,
+             password
+
+        FROM users
+
+       WHERE is_active
+         AND email = ?
+    ",
+
+    has_role: "
+      SELECT 1
+
+        FROM users
+
+       WHERE is_active
+         AND user_id = ?
+         AND role_id IN (SELECT role_id FROM roles WHERE role IN (%s))
+    ",
+
+    add: "
+      INSERT INTO users(role_id, name, email, password, is_active) VALUES
+        ((SELECT role_id FROM roles where role = ?), ?, ?, ?, ?)
+    ",
+
+    set: "
+      UPDATE users
+         SET %s
+       WHERE user_id = ?
+    ",
+
+    get_users: "
+      SELECT a.user_id,
+             a.name,
+             a.email,
+             a.is_active,
+             b.role,
+             b.name AS role_name
+
+        FROM users a
+        JOIN roles b
+          ON (b.role_id = a.role_id)
+
+       ORDER BY LOWER(a.name)
+    ",
+
+    get: "
+      SELECT a.user_id,
+             a.name,
+             a.email,
+             a.is_active,
+             b.role,
+             b.name AS role_name
+
+        FROM users a
+        JOIN roles b
+          ON (b.role_id = a.role_id)
+
+       WHERE a.user_id = ?
+    ",
+  }
+
+  def login(
+    email     : String,
+    password  : String
+  ) : Int64?
+    r = nil
+
+    if row = @context.dbs.ro.row(SQL[:login], [email])
+      if Password.test(row["password"] as String, password)
+        # given email and password matches active user
+        r = row["user_id"] as Int64
+      end
+    end
+
+    # return result
+    r
+  end
+
+  def has_role?(user_id : Int64, roles : Array(String))
+    raise "empty role list" unless roles.size > 0
+
+    !!@context.dbs.ro.one(SQL[:has_role] % [
+      (["?"] * roles.size).join(",")
+    ], [user_id.to_s].concat(roles))
+  end
+
+  def add(
+    name      : String,
+    email     : String,
+    password  : String,
+    role      : String,
+    active    : Bool,
+  ) : Int64
+    @context.dbs.rw.query(SQL[:add], [
+      role,
+      name,
+      email,
+      Password.create(password),
+      active ? "1" : "0",
+    ])
+
+    @context.dbs.rw.last_insert_row_id.to_i64
+  end
+
+  def set(
+    user_id   : Int64,
+    name      : String? = nil,
+    email     : String? = nil,
+    password  : String? = nil,
+    role      : String? = nil,
+    active    : Bool?   = nil,
+  )
+    sets = [] of String
+    args = [] of String
+
+    if name
+      sets << "name = ?"
+      args << name
+    end
+
+    if email
+      sets << "email = ?"
+      args << email
+    end
+
+    if role
+      sets << "role_id = (SELECT role_id FROM roles WHERE role = ?)"
+      args << role
+    end
+
+    if password
+      sets << "password = ?"
+      args << Password.create(password)
+    end
+
+    if active != nil
+      sets << "is_active = ?"
+      args << (active ? "1" : "0")
+    end
+
+    if sets.size > 0
+      args << user_id.to_s
+      @context.dbs.rw.query(SQL[:set] % sets.join(", "), args)
+    end
+  end
+
+  def get_users
+    rows = [] of Hash(String, String)
+
+    @context.dbs.ro.all(SQL[:get_users]) do |row|
+      # append row to result
+      rows << row.reduce({} of String => String) do |r, kv|
+        r[kv[0]] = kv[1].to_s
+        r
+      end
+    end
+
+    rows
+  end
+
+  def get(user_id : Int64)
+    row = @context.dbs.ro.row(SQL[:get], [user_id.to_s])
+    raise "unknown user: #{user_id}" unless row
+
+    row.reduce({} of String => String) do |r, kv|
+      r[kv[0]] = kv[1].to_s
+      r
+    end
+  end
+end
diff --git a/src/guff/session.cr b/src/guff/session.cr
new file mode 100644
index 0000000..2566a59
--- /dev/null
+++ b/src/guff/session.cr
@@ -0,0 +1,72 @@
+class Guff::Session < ::Hash(String, String)
+  getter :session_id
+
+  # session cookie name
+  # FIXME: does this belong here?
+  COOKIE = "guff_session"
+
+  def initialize(@context : Context)
+    super()
+    @session_id = nil
+  end
+
+  def load(id : String)
+    begin
+      # clear existing session
+      clear
+
+      # load session values
+      JSON.parse(@context.models.session.load(id).not_nil!).each do |key, val|
+        self[key.as_s] = val.as_s
+      end
+
+      # save session id
+      @session_id = id
+
+      # return success
+      true
+    rescue err
+      STDERR.puts "session load failed: #{err}"
+      # invalid session id, return failure
+      false
+    end
+  end
+
+  def create(hash : Hash(String, String)) : String
+    clear
+    merge!(hash)
+    @session_id = @context.models.session.create(hash.to_json)
+  end
+
+  def save
+    if valid?
+      @context.models.session.save(@session_id.not_nil!, to_json)
+
+      # return success
+      true
+    else
+      # no session, return failure
+      false
+    end
+  end
+
+  def clear
+    super
+    @session_id = nil
+  end
+
+  def delete : String?
+    r = @session_id
+
+    if valid?
+      @context.models.session.delete(r)
+      clear
+    end
+
+    r
+  end
+
+  def valid?
+    @session_id != nil
+  end
+end