- basic functionality with psql and sqlite

- cli helper tool

31 files changed, 1459 insertions, 0 deletions

diff --git a/crates/secd/Cargo.toml b/crates/secd/Cargo.toml
new file mode 100644
index 0000000..7e80277
--- /dev/null
+++ b/crates/secd/Cargo.toml
@@ -0,0 +1,23 @@
+[package]
+name = "secd"
+version = "0.1.0"
+edition = "2021"
+
+[dependencies]
+async-std = { version = "1.12.0", features = [ "attributes" ] }
+async-trait =  "0.1"
+base64 =  "0.13.1"
+derive_more =  "0.99"
+email_address =  "0.2"
+lazy_static =  "1.4"
+log =  "0.4"
+openssl = "0.10.42"
+rand = "0.8"
+serde = "1"
+serde_json = { version = "1.0", features = ["raw_value"] }
+strum = "0.24.1"
+strum_macros = "0.24"
+sqlx = { version = "0.6", features = [  "runtime-async-std-native-tls", "postgres", "uuid", "sqlite", "time" ] }
+time = { version = "0.3", features = [ "serde" ] }
+thiserror = "1.0"
+uuid = { version = "1.2", features = ["v4", "serde"]}
\ No newline at end of file
diff --git a/build.rs b/crates/secd/build.rs
index 3a8149e..3a8149e 100644
--- a/build.rs
+++ b/crates/secd/build.rs
diff --git a/crates/secd/src/client/email.rs b/crates/secd/src/client/email.rs
new file mode 100644
index 0000000..fc48702
--- /dev/null
+++ b/crates/secd/src/client/email.rs
@@ -0,0 +1,62 @@
+use std::{path::PathBuf, str::FromStr};
+
+use email_address::EmailAddress;
+
+use super::{
+    EmailMessenger, EmailMessengerError, EmailType, EMAIL_TEMPLATE_DEFAULT_LOGIN,
+    EMAIL_TEMPLATE_DEFAULT_SIGNUP,
+};
+
+pub(crate) struct LocalEmailStubber {
+    pub(crate) email_template_login: Option<String>,
+    pub(crate) email_template_signup: Option<String>,
+}
+
+#[async_trait::async_trait]
+impl EmailMessenger for LocalEmailStubber {
+    // TODO: this module really shouldn't be called client, it should be called services... the client is sqlx/mailgun/sns wrapper or whatever...
+    async fn send_email(
+        &self,
+        email_address: &str,
+        validation_id: &str,
+        secret_code: &str,
+        t: EmailType,
+    ) -> Result<(), EmailMessengerError> {
+        let login_template = self
+            .email_template_login
+            .clone()
+            .unwrap_or(EMAIL_TEMPLATE_DEFAULT_LOGIN.to_string());
+        let signup_template = self
+            .email_template_signup
+            .clone()
+            .unwrap_or(EMAIL_TEMPLATE_DEFAULT_SIGNUP.to_string());
+
+        let replace_template = |s: &str| {
+            s.replace(
+                "%secd_link%",
+                &format!("{}?code={}", validation_id, secret_code),
+            )
+            .replace("%secd_email_address%", email_address)
+            .replace("%secd_code%", secret_code)
+        };
+
+        if !EmailAddress::is_valid(email_address) {
+            return Err(EmailMessengerError::InvalidEmailAddress);
+        }
+
+        let body = match t {
+            EmailType::Login => replace_template(&login_template),
+            EmailType::Signup => replace_template(&signup_template),
+        };
+
+        // TODO: write to the system mailbox instead?
+        std::fs::write(
+            PathBuf::from_str(&format!("/tmp/{}.localmail", validation_id))
+                .map_err(|_| EmailMessengerError::Unknown)?,
+            body,
+        )
+        .map_err(|_| EmailMessengerError::FailedToSendEmail)?;
+
+        Ok(())
+    }
+}
diff --git a/crates/secd/src/client/mod.rs b/crates/secd/src/client/mod.rs
new file mode 100644
index 0000000..3925657
--- /dev/null
+++ b/crates/secd/src/client/mod.rs
@@ -0,0 +1,209 @@
+pub mod email;
+pub mod sqldb;
+
+use std::collections::HashMap;
+
+use super::Identity;
+use crate::{EmailValidation, Session, SessionSecret};
+
+use lazy_static::lazy_static;
+use thiserror::Error;
+use uuid::Uuid;
+
+pub enum EmailType {
+    Login,
+    Signup,
+}
+
+#[derive(Error, Debug, derive_more::Display)]
+pub enum EmailMessengerError {
+    InvalidEmailAddress,
+    FailedToSendEmail,
+    Unknown,
+}
+
+#[async_trait::async_trait]
+pub trait EmailMessenger {
+    async fn send_email(
+        &self,
+        email_address: &str,
+        validation_id: &str,
+        secret_code: &str,
+        t: EmailType,
+    ) -> Result<(), EmailMessengerError>;
+}
+
+#[derive(Error, Debug, derive_more::Display)]
+pub enum StoreError {
+    SqlxError(#[from] sqlx::Error),
+    EmailAlreadyExists,
+    CodeAppearsMoreThanOnce,
+    CodeDoesNotExist(String),
+    IdentityIdMustExistInvariant,
+    TooManyEmailValidations,
+    NoEmailValidationFound,
+    Unknown,
+}
+
+const EMAIL_TEMPLATE_DEFAULT_LOGIN: &str = "You requested a login link. Please click the following link %secd_code% to login as %secd_email_address%";
+const EMAIL_TEMPLATE_DEFAULT_SIGNUP: &str = "You requested a sign up. Please click the following link %secd_code% to complete your sign up and validate %secd_email_address%";
+
+const ERR_MSG_MIGRATION_FAILED: &str = "Failed to execute migrations. This appears to be a secd issue. File a bug at https://www.github.com/secd-lib";
+
+const SQLITE: &str = "sqlite";
+const PGSQL: &str = "pgsql";
+
+const WRITE_IDENTITY: &str = "write_identity";
+const WRITE_EMAIL_VALIDATION: &str = "write_email_validation";
+const FIND_EMAIL_VALIDATION: &str = "find_email_validation";
+
+const WRITE_EMAIL: &str = "write_email";
+
+const READ_IDENTITY: &str = "read_identity";
+const FIND_IDENTITY: &str = "find_identity";
+const FIND_IDENTITY_BY_CODE: &str = "find_identity_by_code";
+
+const READ_IDENTITY_RAW_ID: &str = "read_identity_raw_id";
+const READ_EMAIL_RAW_ID: &str = "read_email_raw_id";
+
+const WRITE_SESSION: &str = "write_session";
+const READ_SESSION: &str = "read_session";
+
+lazy_static! {
+    static ref SQLS: HashMap<&'static str, HashMap<&'static str, &'static str>> = {
+        let sqlite_sqls: HashMap<&'static str, &'static str> = [
+            (
+                WRITE_IDENTITY,
+                include_str!("../../store/sqlite/sql/write_identity.sql"),
+            ),
+            (
+                WRITE_EMAIL_VALIDATION,
+                include_str!("../../store/sqlite/sql/write_email_validation.sql"),
+            ),
+            (
+                WRITE_EMAIL,
+                include_str!("../../store/sqlite/sql/write_email.sql"),
+            ),
+            (
+                READ_IDENTITY,
+                include_str!("../../store/sqlite/sql/read_identity.sql"),
+            ),
+            (
+                FIND_IDENTITY,
+                include_str!("../../store/sqlite/sql/find_identity.sql"),
+            ),
+            (
+                FIND_IDENTITY_BY_CODE,
+                include_str!("../../store/sqlite/sql/find_identity_by_code.sql"),
+            ),
+            (
+                READ_IDENTITY_RAW_ID,
+                include_str!("../../store/sqlite/sql/read_identity_raw_id.sql"),
+            ),
+            (
+                READ_EMAIL_RAW_ID,
+                include_str!("../../store/sqlite/sql/read_email_raw_id.sql"),
+            ),
+            (
+                WRITE_SESSION,
+                include_str!("../../store/sqlite/sql/write_session.sql"),
+            ),
+            (
+                READ_SESSION,
+                include_str!("../../store/sqlite/sql/read_session.sql"),
+            ),
+            (
+                FIND_EMAIL_VALIDATION,
+                include_str!("../../store/sqlite/sql/find_email_validation.sql"),
+            ),
+        ]
+        .iter()
+        .cloned()
+        .collect();
+
+        let pg_sqls: HashMap<&'static str, &'static str> = [
+            (
+                WRITE_IDENTITY,
+                include_str!("../../store/pg/sql/write_identity.sql"),
+            ),
+            (
+                WRITE_EMAIL_VALIDATION,
+                include_str!("../../store/pg/sql/write_email_validation.sql"),
+            ),
+            (
+                WRITE_EMAIL,
+                include_str!("../../store/pg/sql/write_email.sql"),
+            ),
+            (
+                READ_IDENTITY,
+                include_str!("../../store/pg/sql/read_identity.sql"),
+            ),
+            (
+                FIND_IDENTITY,
+                include_str!("../../store/pg/sql/find_identity.sql"),
+            ),
+            (
+                FIND_IDENTITY_BY_CODE,
+                include_str!("../../store/pg/sql/find_identity_by_code.sql"),
+            ),
+            (
+                READ_IDENTITY_RAW_ID,
+                include_str!("../../store/pg/sql/read_identity_raw_id.sql"),
+            ),
+            (
+                READ_EMAIL_RAW_ID,
+                include_str!("../../store/pg/sql/read_email_raw_id.sql"),
+            ),
+            (
+                WRITE_SESSION,
+                include_str!("../../store/pg/sql/write_session.sql"),
+            ),
+            (
+                READ_SESSION,
+                include_str!("../../store/pg/sql/read_session.sql"),
+            ),
+            (
+                FIND_EMAIL_VALIDATION,
+                include_str!("../../store/pg/sql/find_email_validation.sql"),
+            ),
+        ]
+        .iter()
+        .cloned()
+        .collect();
+
+        let sqls: HashMap<&'static str, HashMap<&'static str, &'static str>> =
+            [(SQLITE, sqlite_sqls), (PGSQL, pg_sqls)]
+                .iter()
+                .cloned()
+                .collect();
+        sqls
+    };
+}
+
+#[async_trait::async_trait]
+pub trait Store {
+    async fn write_email(&self, identity_id: Uuid, email_address: &str) -> Result<(), StoreError>;
+
+    async fn find_email_validation(
+        &self,
+        validation_id: Option<&Uuid>,
+        code: Option<&str>,
+    ) -> Result<EmailValidation, StoreError>;
+    async fn write_email_validation(
+        &self,
+        ev: &EmailValidation,
+        // TODO: Make this write an EmailValidation
+    ) -> Result<Uuid, StoreError>;
+
+    async fn find_identity(
+        &self,
+        identity_id: Option<&Uuid>,
+        email: Option<&str>,
+    ) -> Result<Option<Identity>, StoreError>;
+    async fn find_identity_by_code(&self, code: &str) -> Result<Identity, StoreError>;
+    async fn write_identity(&self, i: &Identity) -> Result<(), StoreError>;
+    async fn read_identity(&self, identity_id: &Uuid) -> Result<Identity, StoreError>;
+
+    async fn write_session(&self, session: &Session) -> Result<(), StoreError>;
+    async fn read_session(&self, secret: &SessionSecret) -> Result<Session, StoreError>;
+}
diff --git a/crates/secd/src/client/sqldb.rs b/crates/secd/src/client/sqldb.rs
new file mode 100644
index 0000000..6048c48
--- /dev/null
+++ b/crates/secd/src/client/sqldb.rs
@@ -0,0 +1,424 @@
+use std::sync::Arc;
+
+use super::{
+    EmailValidation, Identity, Session, SessionSecret, Store, StoreError, ERR_MSG_MIGRATION_FAILED,
+    FIND_EMAIL_VALIDATION, FIND_IDENTITY, FIND_IDENTITY_BY_CODE, PGSQL, READ_EMAIL_RAW_ID,
+    READ_IDENTITY_RAW_ID, READ_SESSION, SQLITE, SQLS, WRITE_EMAIL, WRITE_EMAIL_VALIDATION,
+    WRITE_IDENTITY, WRITE_SESSION,
+};
+use crate::util;
+use log::error;
+use openssl::sha::Sha256;
+use sqlx::{
+    self, database::HasArguments, ColumnIndex, Database, Decode, Encode, Executor, IntoArguments,
+    Pool, Postgres, Sqlite, Transaction, Type,
+};
+use time::OffsetDateTime;
+use uuid::Uuid;
+
+fn get_sqls(root: &str, file: &str) -> Vec<String> {
+    SQLS.get(root)
+        .unwrap()
+        .get(file)
+        .unwrap()
+        .split("--")
+        .map(|p| p.to_string())
+        .collect()
+}
+
+fn hash_secret(secret: &str) -> Vec<u8> {
+    let mut hasher = Sha256::new();
+    hasher.update(secret.as_bytes());
+    hasher.finish().to_vec()
+}
+
+struct SqlClient<D>
+where
+    D: sqlx::Database,
+{
+    pool: sqlx::Pool<D>,
+    sqls_root: String,
+}
+
+impl<D> SqlClient<D>
+where
+    D: sqlx::Database,
+    for<'c> <D as HasArguments<'c>>::Arguments: IntoArguments<'c, D>,
+    for<'c> i64: Decode<'c, D> + Type<D>,
+    for<'c> &'c str: Decode<'c, D> + Type<D>,
+    for<'c> &'c str: Encode<'c, D> + Type<D>,
+    for<'c> usize: ColumnIndex<<D as Database>::Row>,
+    for<'c> Uuid: Decode<'c, D> + Type<D>,
+    for<'c> Uuid: Encode<'c, D> + Type<D>,
+    for<'c> &'c Pool<D>: Executor<'c, Database = D>,
+{
+    async fn read_identity_raw_id(&self, id: &Uuid) -> Result<i64, StoreError> {
+        let sqls = get_sqls(&self.sqls_root, READ_IDENTITY_RAW_ID);
+
+        Ok(sqlx::query_as::<_, (i64,)>(&sqls[0])
+            .bind(id)
+            .fetch_one(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?
+            .0)
+    }
+
+    async fn read_email_raw_id(&self, address: &str) -> Result<i64, StoreError> {
+        let sqls = get_sqls(&self.sqls_root, READ_EMAIL_RAW_ID);
+
+        Ok(sqlx::query_as::<_, (i64,)>(&sqls[0])
+            .bind(address)
+            .fetch_one(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?
+            .0)
+    }
+}
+
+#[async_trait::async_trait]
+impl<D> Store for SqlClient<D>
+where
+    D: sqlx::Database,
+    for<'c> <D as HasArguments<'c>>::Arguments: IntoArguments<'c, D>,
+    for<'c> bool: Decode<'c, D> + Type<D>,
+    for<'c> bool: Encode<'c, D> + Type<D>,
+    for<'c> i64: Decode<'c, D> + Type<D>,
+    for<'c> i64: Encode<'c, D> + Type<D>,
+    for<'c> i32: Decode<'c, D> + Type<D>,
+    for<'c> i32: Encode<'c, D> + Type<D>,
+    for<'c> OffsetDateTime: Decode<'c, D> + Type<D>,
+    for<'c> OffsetDateTime: Encode<'c, D> + Type<D>,
+    for<'c> &'c str: ColumnIndex<<D as Database>::Row>,
+    for<'c> &'c str: Decode<'c, D> + Type<D>,
+    for<'c> &'c str: Encode<'c, D> + Type<D>,
+    for<'c> Option<&'c str>: Decode<'c, D> + Type<D>,
+    for<'c> Option<&'c str>: Encode<'c, D> + Type<D>,
+    for<'c> String: Decode<'c, D> + Type<D>,
+    for<'c> String: Encode<'c, D> + Type<D>,
+    for<'c> Option<String>: Decode<'c, D> + Type<D>,
+    for<'c> Option<String>: Encode<'c, D> + Type<D>,
+    for<'c> usize: ColumnIndex<<D as Database>::Row>,
+    for<'c> Uuid: Decode<'c, D> + Type<D>,
+    for<'c> Uuid: Encode<'c, D> + Type<D>,
+    for<'c> &'c [u8]: Encode<'c, D> + Type<D>,
+    for<'c> Option<&'c Uuid>: Encode<'c, D> + Type<D>,
+    for<'c> Option<&'c Vec<u8>>: Encode<'c, D> + Type<D>,
+    for<'c> Option<OffsetDateTime>: Decode<'c, D> + Type<D>,
+    for<'c> Option<OffsetDateTime>: Encode<'c, D> + Type<D>,
+    for<'c> &'c Pool<D>: Executor<'c, Database = D>,
+    for<'c> &'c mut Transaction<'c, D>: Executor<'c, Database = D>,
+{
+    async fn write_email(&self, identity_id: Uuid, email_address: &str) -> Result<(), StoreError> {
+        let sqls = get_sqls(&self.sqls_root, WRITE_EMAIL);
+
+        let identity_id = self.read_identity_raw_id(&identity_id).await?;
+
+        let email_id: (i64,) = match sqlx::query_as(&sqls[0])
+            .bind(email_address)
+            .fetch_one(&self.pool)
+            .await
+        {
+            Ok(i) => i,
+            Err(sqlx::Error::RowNotFound) => sqlx::query_as::<_, (i64,)>(&sqls[1])
+                .bind(email_address)
+                .fetch_one(&self.pool)
+                .await
+                .map_err(util::log_err_sqlx)?,
+            Err(e) => return Err(StoreError::SqlxError(e)),
+        };
+
+        sqlx::query(&sqls[2])
+            .bind(identity_id)
+            .bind(email_id.0)
+            .bind(OffsetDateTime::now_utc())
+            .execute(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?;
+
+        Ok(())
+    }
+
+    async fn find_email_validation(
+        &self,
+        validation_id: Option<&Uuid>,
+        code: Option<&str>,
+    ) -> Result<EmailValidation, StoreError> {
+        let sqls = get_sqls(&self.sqls_root, FIND_EMAIL_VALIDATION);
+        let mut rows = sqlx::query_as::<_, EmailValidation>(&sqls[0])
+            .bind(validation_id)
+            .bind(code)
+            .fetch_all(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?;
+
+        match rows.len() {
+            0 => Err(StoreError::NoEmailValidationFound),
+            1 => Ok(rows.swap_remove(0)),
+            _ => Err(StoreError::TooManyEmailValidations),
+        }
+    }
+
+    async fn write_email_validation(&self, ev: &EmailValidation) -> Result<Uuid, StoreError> {
+        let sqls = get_sqls(&self.sqls_root, WRITE_EMAIL_VALIDATION);
+
+        let identity_id = self
+            .read_identity_raw_id(
+                &ev.identity_id
+                    .ok_or(StoreError::IdentityIdMustExistInvariant)?,
+            )
+            .await?;
+        let email_id = self.read_email_raw_id(&ev.email_address).await?;
+
+        let new_id = Uuid::new_v4();
+        sqlx::query(&sqls[0])
+            .bind(ev.id.unwrap_or(new_id))
+            .bind(identity_id)
+            .bind(email_id)
+            .bind(ev.attempts)
+            .bind(&ev.code)
+            .bind(ev.is_validated)
+            .bind(ev.created_at)
+            .bind(ev.expires_at)
+            .execute(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?;
+
+        Ok(new_id)
+    }
+
+    async fn find_identity(
+        &self,
+        id: Option<&Uuid>,
+        email: Option<&str>,
+    ) -> Result<Option<Identity>, StoreError> {
+        let sqls = get_sqls(&self.sqls_root, FIND_IDENTITY);
+        Ok(
+            match sqlx::query_as::<_, Identity>(&sqls[0])
+                .bind(id)
+                .bind(email)
+                .fetch_one(&self.pool)
+                .await
+            {
+                Ok(i) => Some(i),
+                Err(sqlx::Error::RowNotFound) => None,
+                Err(e) => return Err(StoreError::SqlxError(e)),
+            },
+        )
+    }
+    async fn find_identity_by_code(&self, code: &str) -> Result<Identity, StoreError> {
+        let sqls = get_sqls(&self.sqls_root, FIND_IDENTITY_BY_CODE);
+
+        let rows = sqlx::query_as::<_, (i32,)>(&sqls[0])
+            .bind(code)
+            .fetch_all(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?;
+
+        if rows.len() == 0 {
+            return Err(StoreError::CodeDoesNotExist(code.to_string()));
+        }
+
+        if rows.len() != 1 {
+            return Err(StoreError::CodeAppearsMoreThanOnce);
+        }
+
+        let identity_email_id = rows.get(0).unwrap().0;
+
+        // TODO: IF we expand beyond email codes, then we'll need to join against a bunch of identity tables.
+        // but since a single code was found, only one of them should pop...
+        Ok(sqlx::query_as::<_, Identity>(&sqls[1])
+            .bind(identity_email_id)
+            .fetch_one(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?)
+    }
+
+    async fn write_identity(&self, i: &Identity) -> Result<(), StoreError> {
+        let sqls = get_sqls(&self.sqls_root, WRITE_IDENTITY);
+        sqlx::query(&sqls[0])
+            .bind(i.id)
+            .bind(i.data.clone())
+            .bind(i.created_at)
+            .execute(&self.pool)
+            .await
+            .map_err(|e| {
+                error!("write_identity_failure");
+                error!("{:?}", e);
+                e
+            })?;
+
+        Ok(())
+    }
+    async fn read_identity(&self, id: &Uuid) -> Result<Identity, StoreError> {
+        Ok(sqlx::query_as::<_, Identity>(
+            "
+select identity_public_id, data, created_at from identity where identity_public_id = ?",
+        )
+        .bind(id)
+        .fetch_one(&self.pool)
+        .await
+        .map_err(util::log_err_sqlx)?)
+    }
+
+    async fn write_session(&self, session: &Session) -> Result<(), StoreError> {
+        let sqls = get_sqls(&self.sqls_root, WRITE_SESSION);
+
+        let secret_hash = session.secret.as_ref().map(|s| hash_secret(s));
+
+        sqlx::query(&sqls[0])
+            .bind(&session.identity_id)
+            .bind(secret_hash.as_ref())
+            .bind(session.created_at)
+            .bind(OffsetDateTime::now_utc())
+            .bind(session.expires_at)
+            .bind(session.revoked_at)
+            .execute(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?;
+
+        Ok(())
+    }
+    async fn read_session(&self, secret: &SessionSecret) -> Result<Session, StoreError> {
+        let sqls = get_sqls(&self.sqls_root, READ_SESSION);
+
+        let secret_hash = hash_secret(secret);
+        let mut session = sqlx::query_as::<_, Session>(&sqls[0])
+            .bind(&secret_hash[..])
+            .fetch_one(&self.pool)
+            .await
+            .map_err(util::log_err_sqlx)?;
+
+        // This should do nothing other than updated touched_at, and then
+        // clear the plaintext secret
+        session.secret = Some(secret.to_string());
+        self.write_session(&session).await?;
+        session.secret = None;
+
+        Ok(session)
+    }
+}
+
+pub struct PgClient {
+    sql: SqlClient<Postgres>,
+}
+
+impl PgClient {
+    pub async fn new(pool: sqlx::Pool<Postgres>) -> Arc<dyn Store + Send + Sync + 'static> {
+        sqlx::migrate!("store/pg/migrations")
+            .run(&pool)
+            .await
+            .expect(ERR_MSG_MIGRATION_FAILED);
+
+        Arc::new(PgClient {
+            sql: SqlClient {
+                pool,
+                sqls_root: PGSQL.to_string(),
+            },
+        })
+    }
+}
+
+#[async_trait::async_trait]
+impl Store for PgClient {
+    async fn write_email(&self, identity_id: Uuid, email_address: &str) -> Result<(), StoreError> {
+        self.sql.write_email(identity_id, email_address).await
+    }
+    async fn find_email_validation(
+        &self,
+        validation_id: Option<&Uuid>,
+        code: Option<&str>,
+    ) -> Result<EmailValidation, StoreError> {
+        self.sql.find_email_validation(validation_id, code).await
+    }
+    async fn write_email_validation(&self, ev: &EmailValidation) -> Result<Uuid, StoreError> {
+        self.sql.write_email_validation(ev).await
+    }
+    async fn find_identity(
+        &self,
+        identity_id: Option<&Uuid>,
+        email: Option<&str>,
+    ) -> Result<Option<Identity>, StoreError> {
+        self.sql.find_identity(identity_id, email).await
+    }
+    async fn find_identity_by_code(&self, code: &str) -> Result<Identity, StoreError> {
+        self.sql.find_identity_by_code(code).await
+    }
+    async fn write_identity(&self, i: &Identity) -> Result<(), StoreError> {
+        self.sql.write_identity(i).await
+    }
+    async fn read_identity(&self, identity_id: &Uuid) -> Result<Identity, StoreError> {
+        self.sql.read_identity(identity_id).await
+    }
+    async fn write_session(&self, session: &Session) -> Result<(), StoreError> {
+        self.sql.write_session(session).await
+    }
+    async fn read_session(&self, secret: &SessionSecret) -> Result<Session, StoreError> {
+        self.sql.read_session(secret).await
+    }
+}
+
+pub struct SqliteClient {
+    sql: SqlClient<Sqlite>,
+}
+
+impl SqliteClient {
+    pub async fn new(pool: sqlx::Pool<Sqlite>) -> Arc<dyn Store + Send + Sync + 'static> {
+        sqlx::migrate!("store/sqlite/migrations")
+            .run(&pool)
+            .await
+            .expect(ERR_MSG_MIGRATION_FAILED);
+
+        sqlx::query("pragma foreign_keys = on")
+            .execute(&pool)
+            .await
+            .expect(
+                "Failed to initialize FK pragma. File a bug at https://www.github.com/secd-lib",
+            );
+
+        Arc::new(SqliteClient {
+            sql: SqlClient {
+                pool,
+                sqls_root: SQLITE.to_string(),
+            },
+        })
+    }
+}
+
+#[async_trait::async_trait]
+impl Store for SqliteClient {
+    async fn write_email(&self, identity_id: Uuid, email_address: &str) -> Result<(), StoreError> {
+        self.sql.write_email(identity_id, email_address).await
+    }
+    async fn find_email_validation(
+        &self,
+        validation_id: Option<&Uuid>,
+        code: Option<&str>,
+    ) -> Result<EmailValidation, StoreError> {
+        self.sql.find_email_validation(validation_id, code).await
+    }
+    async fn write_email_validation(&self, ev: &EmailValidation) -> Result<Uuid, StoreError> {
+        self.sql.write_email_validation(ev).await
+    }
+    async fn find_identity(
+        &self,
+        identity_id: Option<&Uuid>,
+        email: Option<&str>,
+    ) -> Result<Option<Identity>, StoreError> {
+        self.sql.find_identity(identity_id, email).await
+    }
+    async fn find_identity_by_code(&self, code: &str) -> Result<Identity, StoreError> {
+        self.sql.find_identity_by_code(code).await
+    }
+    async fn write_identity(&self, i: &Identity) -> Result<(), StoreError> {
+        self.sql.write_identity(i).await
+    }
+    async fn read_identity(&self, identity_id: &Uuid) -> Result<Identity, StoreError> {
+        self.sql.read_identity(identity_id).await
+    }
+    async fn write_session(&self, session: &Session) -> Result<(), StoreError> {
+        self.sql.write_session(session).await
+    }
+    async fn read_session(&self, secret: &SessionSecret) -> Result<Session, StoreError> {
+        self.sql.read_session(secret).await
+    }
+}
diff --git a/crates/secd/src/lib.rs b/crates/secd/src/lib.rs
new file mode 100644
index 0000000..9eb7f0e
--- /dev/null
+++ b/crates/secd/src/lib.rs
@@ -0,0 +1,409 @@
+mod client;
+mod util;
+
+use std::sync::Arc;
+
+use client::{
+    email,
+    sqldb::{PgClient, SqliteClient},
+    EmailMessenger, EmailMessengerError, Store, StoreError,
+};
+use derive_more::Display;
+use email_address::EmailAddress;
+use log::error;
+use rand::distributions::{Alphanumeric, DistString};
+use serde::{Deserialize, Serialize};
+use strum_macros::{EnumString, EnumVariantNames};
+use time::{Duration, OffsetDateTime};
+use uuid::Uuid;
+
+const SESSION_SIZE_BYTES: usize = 32;
+const SESSION_DURATION: i64 = 60 /* seconds*/ * 60 /* minutes */ * 24 /* hours */ * 360 /* days */;
+const EMAIL_VALIDATION_DURATION: i64 = 60 /* seconds*/ * 15 /* minutes */;
+const VALIDATION_ATTEMPTS_MAX: i32 = 5;
+const VALIDATION_CODE_SIZE: usize = 6;
+
+#[derive(sqlx::FromRow, Debug, Serialize)]
+pub struct ApiKey {
+    pub public_key: String,
+    pub private_key: String,
+}
+
+#[derive(sqlx::FromRow, Debug, Serialize)]
+pub struct Authorization {
+    session: Session,
+}
+
+#[derive(sqlx::FromRow, Debug, Serialize)]
+pub struct Identity {
+    #[sqlx(rename = "identity_public_id")]
+    id: Uuid,
+    created_at: OffsetDateTime,
+    #[serde(skip_serializing_if = "Option::is_none")]
+    data: Option<String>,
+}
+
+#[derive(sqlx::FromRow, Debug, Serialize)]
+pub struct Session {
+    #[sqlx(rename = "identity_public_id")]
+    identity_id: IdentityId,
+    #[serde(skip_serializing_if = "Option::is_none")]
+    #[sqlx(default)]
+    secret: Option<SessionSecret>,
+    #[serde(with = "time::serde::timestamp")]
+    created_at: OffsetDateTime,
+    #[serde(with = "time::serde::timestamp")]
+    expires_at: OffsetDateTime,
+    #[serde(skip_serializing_if = "Option::is_none")]
+    revoked_at: Option<OffsetDateTime>,
+}
+
+#[derive(sqlx::FromRow, Debug)]
+pub struct EmailValidation {
+    #[sqlx(rename = "email_validation_public_id")]
+    id: Option<Uuid>,
+    #[sqlx(rename = "identity_public_id")]
+    identity_id: Option<IdentityId>,
+    #[sqlx(rename = "address")]
+    email_address: String,
+    attempts: i32,
+    code: String,
+    is_validated: bool,
+    created_at: OffsetDateTime,
+    expires_at: OffsetDateTime,
+    revoked_at: Option<OffsetDateTime>,
+}
+
+#[derive(Copy, Display, Clone, Debug)]
+pub enum OauthProvider {
+    Amazon,
+    Apple,
+    Dropbox,
+    Facebook,
+    Github,
+    Gitlab,
+    Google,
+    Instagram,
+    LinkedIn,
+    Microsoft,
+    Paypal,
+    Reddit,
+    Spotify,
+    Strava,
+    Stripe,
+    Twitch,
+    Twitter,
+    WeChat,
+}
+
+#[derive(Display, Debug, Serialize, Deserialize, EnumString, EnumVariantNames)]
+#[strum(ascii_case_insensitive)]
+pub enum AuthStore {
+    Sqlite,
+    Postgres,
+    MySql,
+    Mongo,
+    Dynamo,
+    Redis,
+}
+
+#[derive(Display, Debug, Serialize, Deserialize, EnumString, EnumVariantNames)]
+#[strum(ascii_case_insensitive)]
+pub enum AuthEmail {
+    LocalStub,
+    Ses,
+    Mailgun,
+    Sendgrid,
+}
+
+pub type IdentityId = Uuid;
+pub type SessionSecret = String;
+pub type SessionSecretHash = String;
+pub type ValidationRequestId = Uuid;
+pub type ValidationSecretCode = String;
+
+#[derive(Debug, derive_more::Display, thiserror::Error)]
+pub enum SecdError {
+    InvalidEmailAddress,
+    InvalidCode,
+    InitializationFailure(sqlx::Error),
+    IdentityIdShouldExistInvariant,
+    EmailSendError(#[from] EmailMessengerError),
+    EmailValidationRequestError,
+    EmailValidationExpiryOverflow,
+    SessionExpiryOverflow,
+    Unauthenticated,
+    Unknown,
+}
+
+pub struct Secd {
+    store: Arc<dyn Store + Send + Sync + 'static>,
+    email_messenger: Arc<dyn EmailMessenger + Send + Sync + 'static>,
+}
+
+impl Secd {
+    pub async fn init(
+        auth_store: AuthStore,
+        conn_string: Option<&str>,
+        email_messenger: AuthEmail,
+        email_template_login: Option<String>,
+        email_template_signup: Option<String>,
+    ) -> Result<Self, SecdError> {
+        let store = match auth_store {
+            AuthStore::Sqlite => {
+                SqliteClient::new(
+                    sqlx::sqlite::SqlitePoolOptions::new()
+                        .connect(conn_string.unwrap_or("sqlite::memory:".into()))
+                        .await
+                        .map_err(|e| SecdError::InitializationFailure(e))?,
+                )
+                .await
+            }
+            AuthStore::Postgres => {
+                PgClient::new(
+                    sqlx::postgres::PgPoolOptions::new()
+                        .connect(conn_string.expect("No postgres connection string provided."))
+                        .await
+                        .map_err(|e| SecdError::InitializationFailure(e))?,
+                )
+                .await
+            }
+            rest @ _ => {
+                error!(
+                    "requested an AuthStore which has not yet been implemented: {:?}",
+                    rest
+                );
+                unimplemented!()
+            }
+        };
+
+        let email_sender = match email_messenger {
+            // TODO: initialize email and SMS templates with secd
+            AuthEmail::LocalStub => email::LocalEmailStubber {
+                email_template_login,
+                email_template_signup,
+            },
+            _ => unimplemented!(),
+        };
+
+        Ok(Secd {
+            store,
+            email_messenger: Arc::new(email_sender),
+        })
+    }
+    /// create_validation_request
+    ///
+    /// Generate a request to validate the provided email.
+    pub async fn create_validation_request(
+        &self,
+        email: Option<&str>,
+    ) -> Result<ValidationRequestId, SecdError> {
+        let now = OffsetDateTime::now_utc();
+
+        let email = match email {
+            Some(ea) => {
+                if EmailAddress::is_valid(ea) {
+                    ea
+                } else {
+                    return Err(SecdError::InvalidEmailAddress);
+                }
+            }
+            None => return Err(SecdError::InvalidEmailAddress),
+        };
+
+        let mut ev = EmailValidation {
+            id: None,
+            identity_id: None,
+            email_address: email.to_string(),
+            attempts: 0,
+            code: Alphanumeric
+                .sample_string(&mut rand::thread_rng(), VALIDATION_CODE_SIZE)
+                .to_lowercase(),
+            is_validated: false,
+            created_at: now,
+            expires_at: now
+                .checked_add(Duration::new(EMAIL_VALIDATION_DURATION, 0))
+                .ok_or(SecdError::EmailValidationExpiryOverflow)?,
+            revoked_at: None,
+        };
+
+        let (req_id, mail_type) = match self
+            .store
+            .find_identity(None, Some(email))
+            .await
+            .map_err(|e| util::log_err(e.into(), SecdError::Unknown))?
+        {
+            Some(identity) => {
+                let req_id = {
+                    ev.identity_id = Some(identity.id);
+                    self.store
+                        .write_email_validation(&ev)
+                        .await
+                        .map_err(|e| util::log_err(e.into(), SecdError::Unknown))?
+                };
+                (req_id, client::EmailType::Login)
+            }
+            None => {
+                let identity = Identity {
+                    id: Uuid::new_v4(),
+                    created_at: OffsetDateTime::now_utc(),
+                    data: None,
+                };
+                self.store
+                    .write_identity(&identity)
+                    .await
+                    .map_err(|e| util::log_err(e.into(), SecdError::Unknown))?;
+                self.store
+                    .write_email(identity.id, email)
+                    .await
+                    .map_err(|e| util::log_err(e.into(), SecdError::Unknown))?;
+
+                let req_id = {
+                    ev.identity_id = Some(identity.id);
+                    self.store
+                        .write_email_validation(&ev)
+                        .await
+                        .map_err(|e| util::log_err(e.into(), SecdError::Unknown))?
+                };
+
+                (req_id, client::EmailType::Signup)
+            }
+        };
+
+        self.email_messenger
+            .send_email(email, &req_id.to_string(), &ev.code, mail_type)
+            .await?;
+
+        Ok(req_id)
+    }
+    /// exchange_secret_for_session
+    ///
+    /// Exchanges a secret, which consists of a validation_request_id and secret_code
+    /// for a session which allows authentication on behalf of the associated identity.
+    ///
+    /// Session secrets should be used to return authorization for the associated identity.
+    pub async fn exchange_code_for_session(
+        &self,
+        validation_request_id: ValidationRequestId,
+        code: ValidationSecretCode,
+    ) -> Result<Session, SecdError> {
+        let mut ev = self
+            .store
+            .find_email_validation(Some(&validation_request_id), Some(&code))
+            .await
+            .map_err(|e| util::log_err(e.into(), SecdError::EmailValidationExpiryOverflow))?;
+
+        if ev.is_validated
+            || ev.expires_at < OffsetDateTime::now_utc()
+            || ev.attempts >= VALIDATION_ATTEMPTS_MAX
+        {
+            return Err(SecdError::InvalidCode);
+        };
+
+        ev.is_validated = true;
+        ev.attempts += 1;
+        self.store
+            .write_email_validation(&ev)
+            .await
+            .map_err(|e| util::log_err(e.into(), SecdError::Unknown))?;
+
+        // TODO: clear previous sessions if they fit the criteria
+        let now = OffsetDateTime::now_utc();
+        let s = Session {
+            identity_id: ev
+                .identity_id
+                .ok_or(SecdError::IdentityIdShouldExistInvariant)?,
+            secret: Some(Alphanumeric.sample_string(&mut rand::thread_rng(), SESSION_SIZE_BYTES)),
+            created_at: now,
+            expires_at: now
+                .checked_add(Duration::new(SESSION_DURATION, 0))
+                .ok_or(SecdError::SessionExpiryOverflow)?,
+            revoked_at: None,
+        };
+        self.store
+            .write_session(&s)
+            .await
+            .map_err(|e| util::log_err(e.into(), SecdError::Unknown))?;
+
+        Ok(s)
+    }
+    /// get_identity
+    ///
+    /// Return all information associated with the identity id.
+    pub async fn get_identity(&self, identity: IdentityId) -> Result<Identity, SecdError> {
+        unimplemented!()
+    }
+    /// get_authorization
+    ///
+    /// Return the authorization for this session. If the session is
+    /// invalid, expired or otherwise unauthenticated, an error will
+    /// be returned.
+    pub async fn get_authorization(
+        &self,
+        secret: SessionSecret,
+    ) -> Result<Authorization, SecdError> {
+        match self.store.read_session(&secret).await {
+            Ok(session)
+                if session.expires_at > OffsetDateTime::now_utc()
+                    || session.revoked_at > Some(OffsetDateTime::now_utc()) =>
+            {
+                Ok(Authorization { session })
+            }
+            Ok(_) => Err(SecdError::Unauthenticated),
+            Err(_e) => Err(SecdError::Unknown),
+        }
+    }
+    /// revoke_session
+    ///
+    /// Revokes a session such that it may no longer be used to authenticate
+    /// the associated identity.
+    pub async fn revoke_session(&self, secret_hash: SessionSecretHash) -> Result<(), SecdError> {
+        unimplemented!()
+    }
+    /// revoke_identity
+    ///
+    /// Soft delete an identity such that all associated resources are
+    /// deleted as well.
+    ///
+    /// NOTE: This operation cannot be undone. Although it may not be undone
+    /// a separate call to delete_identity is required to cleanup necessary
+    /// resources.
+    ///
+    /// You may configure secd to periodically clean all revoked
+    /// identities and associated resources with AUTOCLEAN_REVOKED.
+    pub async fn revoke_identity(&self, identity_id: IdentityId) -> Result<(), SecdError> {
+        unimplemented!()
+    }
+    /// delete_identity
+    ///
+    /// Delete an identity and all associated resources (e.g. session,
+    /// authorization structures, etc...). This is a hard delete and permanently
+    /// removes all stored information.
+    ///
+    /// NOTE: An identity _must_ be revoked before it can be deleted. Otherwise,
+    /// secd will return an error.
+    pub async fn delete_identity(&self, identity_id: IdentityId) -> Result<(), SecdError> {
+        unimplemented!()
+    }
+
+    // register service
+    // register service_action(service_id, action)
+    // list services
+    // list service actions
+
+    // create permission
+    // create group (name, identities)
+    // create role (name, permissios)
+    // list group
+    // list role
+    // list permission
+    // describe group
+    // describe role
+    // describe permission
+    // add_identity_to_group
+    // remove_identity_from_group
+    // add_permission_to_role
+    // remove_permission_from_role
+    // attach_role_to_group
+    // attach_permission_to_group (just creates single role and attaches it)
+}
diff --git a/crates/secd/src/util/mod.rs b/crates/secd/src/util/mod.rs
new file mode 100644
index 0000000..da16901
--- /dev/null
+++ b/crates/secd/src/util/mod.rs
@@ -0,0 +1,21 @@
+use log::error;
+use rand::distributions::Alphanumeric;
+use rand::{thread_rng, Rng};
+
+use crate::SecdError;
+
+pub(crate) fn log_err(e: Box<dyn std::error::Error>, new_e: SecdError) -> SecdError {
+    error!("{:?}", e);
+    new_e
+}
+pub(crate) fn log_err_sqlx(e: sqlx::Error) -> sqlx::Error {
+    error!("{:?}", e);
+    e
+}
+pub(crate) fn generate_random_url_safe(n: usize) -> String {
+    thread_rng()
+        .sample_iter(&Alphanumeric)
+        .take(n)
+        .map(char::from)
+        .collect()
+}
diff --git a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
new file mode 100644
index 0000000..7a1bf07
--- /dev/null
+++ b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
@@ -0,0 +1,49 @@
+create extension if not exists pgcrypto;
+create extension if not exists citext;
+create schema if not exists auth;
+
+create table if not exists auth.identity (
+       identity_id          bigserial          primary key
+       , identity_public_id uuid
+       , data text
+       , created_at         timestamptz not null
+       , unique(identity_public_id)
+);
+
+create table if not exists auth.email (
+       email_id bigserial primary key
+       , address text not null
+       , unique(address)
+);
+
+create table if not exists auth.identity_email (
+       identity_email_id bigserial primary key
+       , identity_id bigint not null references auth.identity(identity_id)
+       , email_id bigint not null references auth.email(email_id)
+       , created_at timestamptz not null
+       , deleted_at timestamptz
+);
+
+create table if not exists auth.email_validation (
+       email_validation_id bigserial primary key
+       , email_validation_public_id uuid not null
+       , identity_email_id integer not null references auth.identity_email(identity_email_id)
+       , attempts integer not null
+       , code text
+       , is_validated boolean not null default false
+       , created_at timestamptz not null
+       , expires_at timestamptz
+       , revoked_at timestamptz
+       , unique(email_validation_public_id)
+);
+
+create table if not exists auth.session (
+       session_id bigserial primary key
+       , identity_id bigint not null references auth.identity(identity_id)
+       , secret_hash bytea not null
+       , created_at timestamptz not null
+       , touched_at timestamptz not null
+       , expires_at timestamptz
+       , revoked_at timestamptz
+       , unique(secret_hash)
+);
diff --git a/crates/secd/store/pg/sql/find_email_validation.sql b/crates/secd/store/pg/sql/find_email_validation.sql
new file mode 100644
index 0000000..d16d8e7
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_email_validation.sql
@@ -0,0 +1,17 @@
+select
+        ev.email_validation_public_id
+        , i.identity_public_id
+        , e.address
+        , ev.attempts
+        , ev.code
+        , ev.is_validated
+        , ev.created_at
+        , ev.expires_at
+        , ev.revoked_at
+from auth.email_validation ev
+join auth.identity_email   ie using (identity_email_id)
+join auth.email            e  using (email_id)
+join auth.identity         i  using (identity_id)
+where (($1 is null) or (email_validation_public_id = $1))
+and   (($2 is null) or (code = $2));
+--
diff --git a/crates/secd/store/pg/sql/find_identity.sql b/crates/secd/store/pg/sql/find_identity.sql
new file mode 100644
index 0000000..3a86a83
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_identity.sql
@@ -0,0 +1,9 @@
+select
+        identity_public_id,
+        data,
+        i.created_at
+from auth.identity       i
+join auth.identity_email ie using (identity_id)
+join auth.email          e  using (email_id)
+where (($1 is null) or (i.identity_public_id = $1))
+and   (($2 is null) or (e.address = $2))
diff --git a/crates/secd/store/pg/sql/find_identity_by_code.sql b/crates/secd/store/pg/sql/find_identity_by_code.sql
new file mode 100644
index 0000000..9df6614
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_identity_by_code.sql
@@ -0,0 +1,11 @@
+select identity_email_id
+from auth.email_validation
+where email_validation_public_id = $1::uuid
+--
+select
+        identity_public_id
+        , data
+        , i.created_at
+from auth.identity            i
+left join auth.identity_email ie using (identity_id)
+where ie.identity_email_id = $1;
diff --git a/crates/secd/store/pg/sql/read_email_raw_id.sql b/crates/secd/store/pg/sql/read_email_raw_id.sql
new file mode 100644
index 0000000..f62331c
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_email_raw_id.sql
@@ -0,0 +1 @@
+select email_id from auth.email where address = $1
diff --git a/crates/secd/store/pg/sql/read_identity.sql b/crates/secd/store/pg/sql/read_identity.sql
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_identity.sql
diff --git a/crates/secd/store/pg/sql/read_identity_raw_id.sql b/crates/secd/store/pg/sql/read_identity_raw_id.sql
new file mode 100644
index 0000000..d550cc0
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_identity_raw_id.sql
@@ -0,0 +1,2 @@
+select identity_id from auth.identity where identity_public_id = $1;
+--
diff --git a/crates/secd/store/pg/sql/read_session.sql b/crates/secd/store/pg/sql/read_session.sql
new file mode 100644
index 0000000..febc1ab
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_session.sql
@@ -0,0 +1,8 @@
+select
+        i.identity_public_id
+        , s.created_at
+        , s.expires_at
+        , s.revoked_at
+from auth.session  s
+join auth.identity i using (identity_id)
+where secret_hash = $1;
diff --git a/crates/secd/store/pg/sql/write_email.sql b/crates/secd/store/pg/sql/write_email.sql
new file mode 100644
index 0000000..75fc494
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_email.sql
@@ -0,0 +1,11 @@
+insert into auth.email (
+       address
+) values (
+       $1
+) on conflict (address) do nothing
+returning email_id;
+--
+select email_id from auth.email where address = $1;
+--
+insert into auth.identity_email (identity_id, email_id, created_at) values ($1, $2, $3);
+--
diff --git a/crates/secd/store/pg/sql/write_email_validation.sql b/crates/secd/store/pg/sql/write_email_validation.sql
new file mode 100644
index 0000000..98fc60e
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_email_validation.sql
@@ -0,0 +1,27 @@
+insert into auth.email_validation
+       (
+           email_validation_public_id
+           , identity_email_id
+           , attempts
+           , code
+           , is_validated
+           , created_at
+           , expires_at
+       )
+values (
+           $1
+           , (
+               select identity_email_id
+               from auth.identity_email
+               where identity_id = $2
+               and email_id = $3
+           )
+           , $4
+           , $5
+           , $6
+           , $7
+           , $8
+) on conflict (email_validation_public_id) do update
+  set attempts       = excluded.attempts
+      , is_validated = excluded.is_validated
+      , expires_at   = excluded.expires_at;
diff --git a/crates/secd/store/pg/sql/write_identity.sql b/crates/secd/store/pg/sql/write_identity.sql
new file mode 100644
index 0000000..eed1710
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_identity.sql
@@ -0,0 +1,9 @@
+insert into auth.identity (
+       identity_public_id,
+       data,
+       created_at
+) values (
+       $1,
+       $2,
+       $3
+);
diff --git a/crates/secd/store/pg/sql/write_session.sql b/crates/secd/store/pg/sql/write_session.sql
new file mode 100644
index 0000000..cd5892b
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_session.sql
@@ -0,0 +1,18 @@
+insert into auth.session (
+       identity_id
+       , secret_hash
+       , created_at
+       , touched_at
+       , expires_at
+       , revoked_at
+) values (
+         (select identity_id from auth.identity where identity_public_id = $1)
+       , $2
+       , $3
+       , $4
+       , $5
+       , $6
+) on conflict (secret_hash) do update
+  set touched_at = excluded.touched_at
+      , revoked_at = excluded.revoked_at;
+--
diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
new file mode 100644
index 0000000..aa95afc
--- /dev/null
+++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
@@ -0,0 +1,45 @@
+create table if not exists identity (
+       identity_id integer primary key autoincrement
+       , identity_public_id uuid
+       , data text
+       , created_at timestamp not null
+       , unique(identity_public_id)
+);
+
+create table if not exists email (
+       email_id integer primary key autoincrement
+       , address text not null
+       , unique(address)
+);
+
+create table if not exists identity_email (
+       identity_email_id integer primary key autoincrement
+       , identity_id integer not null references identity(identity_id)
+       , email_id integer not null references email(email_id)
+       , created_at timestamp not null
+       , deleted_at timestamp
+);
+
+create table if not exists email_validation (
+       email_validation_id integer primary key autoincrement
+       , email_validation_public_id text not null -- uuid
+       , identity_email_id integer not null references identity_email(identity_email_id)
+       , attempts integer not null
+       , code text
+       , is_validated boolean not null
+       , created_at timestamp not null
+       , expires_at timestamp
+       , revoked_at timestamp
+       , unique(email_validation_public_id)
+);
+
+create table if not exists session (
+       session_id integer primary key autoincrement
+       , identity_id not null references identity(identity_id)
+       , secret_hash blob not null
+       , created_at timestamp not null
+       , touched_at timestamp not null
+       , expires_at timestamp
+       , revoked_at timestamp
+       , unique(secret_hash)
+);
diff --git a/crates/secd/store/sqlite/sql/find_email_validation.sql b/crates/secd/store/sqlite/sql/find_email_validation.sql
new file mode 100644
index 0000000..a34c149
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_email_validation.sql
@@ -0,0 +1,16 @@
+select
+        ev.email_validation_public_id
+        , i.identity_public_id
+        , e.address
+        , ev.attempts
+        , ev.code
+        , ev.is_validated
+        , ev.created_at
+        , ev.expires_at
+        , ev.revoked_at
+from email_validation ev
+join identity_email   ie using (identity_email_id)
+join email            e  using (email_id)
+join identity         i  using (identity_id)
+where ((?1 is null) or (email_validation_public_id = ?1))
+and   ((?2 is null) or (code = ?2));
diff --git a/crates/secd/store/sqlite/sql/find_identity.sql b/crates/secd/store/sqlite/sql/find_identity.sql
new file mode 100644
index 0000000..bd1654d
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_identity.sql
@@ -0,0 +1,9 @@
+select
+        identity_public_id,
+        data,
+        i.created_at
+from identity       i
+join identity_email ie using (identity_id)
+join email          e  using (email_id)
+where ((?1 is null) or (i.identity_public_id = ?1))
+and   ((?2 is null) or (e.address = ?2))
diff --git a/crates/secd/store/sqlite/sql/find_identity_by_code.sql b/crates/secd/store/sqlite/sql/find_identity_by_code.sql
new file mode 100644
index 0000000..e1a6050
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_identity_by_code.sql
@@ -0,0 +1,11 @@
+select identity_email_id
+from auth.email_validation
+where email_validation_public_id = ?1;
+--
+select
+        identity_public_id
+        , data
+        , i.created_at
+from auth.identity            i
+left join auth.identity_email ie using (identity_id)
+where ie.identity_email_id = ?1;
diff --git a/crates/secd/store/sqlite/sql/read_email_raw_id.sql b/crates/secd/store/sqlite/sql/read_email_raw_id.sql
new file mode 100644
index 0000000..0bbafad
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_email_raw_id.sql
@@ -0,0 +1 @@
+select email_id from email where address = ?
diff --git a/crates/secd/store/sqlite/sql/read_identity.sql b/crates/secd/store/sqlite/sql/read_identity.sql
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_identity.sql
diff --git a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql
new file mode 100644
index 0000000..552c570
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql
@@ -0,0 +1,2 @@
+select identity_id from identity where identity_public_id = ?;
+--
diff --git a/crates/secd/store/sqlite/sql/read_session.sql b/crates/secd/store/sqlite/sql/read_session.sql
new file mode 100644
index 0000000..4daa352
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_session.sql
@@ -0,0 +1,8 @@
+select
+        i.identity_public_id
+        , s.created_at
+        , s.expires_at
+        , s.revoked_at
+from session  s
+join identity i using (identity_id)
+where secret_hash = ?1;
diff --git a/crates/secd/store/sqlite/sql/write_email.sql b/crates/secd/store/sqlite/sql/write_email.sql
new file mode 100644
index 0000000..c127d9c
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_email.sql
@@ -0,0 +1,11 @@
+insert into email (
+       address
+) values (
+       ?1
+) on conflict (address) do nothing
+returning email_id;
+--
+select email_id from email where email = ?1;
+--
+insert into identity_email (identity_id, email_id, created_at) values (?1, ?2, ?3);
+--
diff --git a/crates/secd/store/sqlite/sql/write_email_validation.sql b/crates/secd/store/sqlite/sql/write_email_validation.sql
new file mode 100644
index 0000000..37b13e1
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_email_validation.sql
@@ -0,0 +1,27 @@
+insert into email_validation
+       (
+           email_validation_public_id
+           , identity_email_id
+           , attempts
+           , code
+           , is_validated
+           , created_at
+           , expires_at
+       )
+values (
+           ?1
+           , (
+               select identity_email_id
+               from identity_email
+               where identity_id = ?2
+               and email_id = ?3
+           )
+           , ?4
+           , ?5
+           , ?6
+           , ?7
+           , ?8
+) on conflict (email_validation_public_id) do update
+  set attempts       = excluded.attempts
+      , is_validated = excluded.is_validated
+      , expires_at   = excluded.expires_at;
diff --git a/crates/secd/store/sqlite/sql/write_identity.sql b/crates/secd/store/sqlite/sql/write_identity.sql
new file mode 100644
index 0000000..ff54468
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_identity.sql
@@ -0,0 +1 @@
+insert into identity (identity_public_id, data, created_at) values (?1, ?2, ?3);
diff --git a/crates/secd/store/sqlite/sql/write_session.sql b/crates/secd/store/sqlite/sql/write_session.sql
new file mode 100644
index 0000000..3c26986
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_session.sql
@@ -0,0 +1,18 @@
+insert into session (
+       identity_id
+       , secret_hash
+       , created_at
+       , touched_at
+       , expires_at
+       , revoked_at
+) values (
+         (select identity_id from identity where identity_public_id = ?1)
+       , ?2
+       , ?3
+       , ?4
+       , ?5
+       , ?6
+) on conflict (secret_hash) do update
+  set touched_at = excluded.touched_at
+      , revoked_at = excluded.revoked_at;
+--