Content-Driven Phoenix Sites with SQLite

The Problem

You’re building a portfolio site. You want structured content - projects, articles, experience - that you can filter and query. But you also want to edit content in simple text files, not a CMS admin panel.

Options:

• JSON/YAML at runtime: Parse on every request. No queries, no filtering.
• PostgreSQL: Overkill for a single-user site.
• SQLite + file-based content: Best of both worlds.

The Architecture

priv/content/           # YAML files (source of truth)
├── profile.yml
├── projects.yml
├── articles.yml
└── articles/           # Markdown content
    └── my-article.md

     ↓ mix portfolio.import

priv/portfolio_dev.db   # SQLite database (queryable)

YAML files are your source of truth. Edit them, run the import, database updates. Git tracks the YAML, not the database.

The Schema

Each content type gets an Ecto schema. Here’s a project:

defmodule Portfolio.Content.Project do
  use Ecto.Schema
  import Ecto.Changeset

  @category_values [:work, :open_source, :own]
  @status_values [:active, :maintained, :archived]

  @derive {
    Flop.Schema,
    filterable: [:category, :status, :featured, :organization_id],
    sortable: [:title, :inserted_at],
    default_order: %{order_by: [:inserted_at], order_directions: [:desc]}
  }

  schema "projects" do
    field :slug, :string
    field :title, :string
    field :description, :string
    field :category, Ecto.Enum, values: @category_values
    field :status, Ecto.Enum, values: @status_values
    field :featured, :boolean, default: false

    belongs_to :user, Portfolio.Content.User
    belongs_to :organization, Portfolio.Content.Organization
    many_to_many :tags, Portfolio.Content.Tag, join_through: "project_tags"

    timestamps(type: :utc_datetime)
  end
end

The @derive Flop.Schema enables filtering in LiveView. More on that later.

The YAML Format

- id: limitless-cam
  title: CNC AI Copilot
  company: LimitlessCNC
  description: Desktop AI copilot for CNC machining.
  category: work
  status: maintained
  featured: true
  tags:
    - Elixir
    - Phoenix
    - AI

The id becomes the slug. Company references link to the organizations file.

The Import Task

A Mix task reads YAML and upserts into SQLite:

defmodule Mix.Tasks.Portfolio.Import do
  use Mix.Task

  @content_path "priv/content"

  def run(args) do
    Mix.Task.run("app.start")
    {opts, _, _} = OptionParser.parse(args, strict: [user: :string, clean: :boolean])

    email = opts[:user] || raise "Missing --user option"
    user = get_or_create_user(email)

    if opts[:clean], do: clean_user_content(user)

    import_organizations(user)
    import_projects(user)
    import_articles(user)
    # ... other content types

    Mix.shell().info("Import complete")
  end
end

Upsert Pattern

Each import function reads YAML and upserts:

defp import_projects(user) do
  path = Path.join(@content_path, "projects.yml")

  {:ok, data} = YamlElixir.read_from_file(path)

  Enum.each(data, fn item ->
    attrs = %{
      user_id: user.id,
      slug: item["id"],
      title: item["title"],
      category: String.to_existing_atom(item["category"]),
      # ... other fields
    }

    %Project{}
    |> Project.changeset(attrs)
    |> Repo.insert!(on_conflict: :replace_all, conflict_target: [:user_id, :slug])
  end)
end

The key is on_conflict: :replace_all. Run import multiple times - existing records update, new ones insert.

Tag Sync

Many-to-many tags require clearing and reinserting:

defp sync_project_tags(user, project, tag_names) do
  import Ecto.Query

  tags = Enum.map(tag_names, fn name ->
    case Repo.get_by(Tag, user_id: user.id, name: name) do
      nil ->
        %Tag{}
        |> Tag.changeset(%{user_id: user.id, name: name})
        |> Repo.insert!()
      tag ->
        tag
    end
  end)

  Repo.delete_all(from pt in "project_tags", where: pt.project_id == ^project.id)

  Enum.each(tags, fn tag ->
    Repo.insert_all("project_tags", [%{project_id: project.id, tag_id: tag.id}])
  end)
end

Get-or-create each tag, clear the join table, reinsert. Simple and idempotent.

The Content API

A context module wraps all queries:

defmodule Portfolio.Content do
  import Ecto.Query
  alias Portfolio.Repo

  @default_user_email "you@example.com"

  defp default_user do
    Repo.get_by(User, email: @default_user_email)
  end

  def list_projects do
    case default_user() do
      nil -> []
      user ->
        from(p in Project,
          where: p.user_id == ^user.id,
          order_by: [desc: p.inserted_at],
          preload: [:organization, :tags]
        )
        |> Repo.all()
    end
  end

  def get_project(slug) do
    case default_user() do
      nil -> nil
      user ->
        from(p in Project,
          where: p.user_id == ^user.id and p.slug == ^slug,
          preload: [:organization, :tags]
        )
        |> Repo.one()
    end
  end
end

Single-user site, so we hardcode the user email. Multi-user? Pass user_id as parameter.

Why SQLite?

For a portfolio site:

• No external dependencies: Database is a file in priv/
• Fast reads: All queries are local
• Git-friendly workflow: Edit YAML, commit, deploy, run import
• Full SQL power: Ecto queries, joins, filtering, full-text search

SQLite handles concurrent reads fine. Write contention only matters if you have multiple processes writing simultaneously - not a concern for content import.

Full-Text Search Bonus

SQLite’s FTS5 enables search:

CREATE VIRTUAL TABLE projects_fts USING fts5(
  title, description, details,
  content='projects', content_rowid='id'
);

Query it through Ecto:

def search_projects(query) do
  from(p in Project,
    where: p.id in fragment(
      "SELECT rowid FROM projects_fts WHERE projects_fts MATCH ?",
      ^query
    ),
    preload: [:organization, :tags]
  )
  |> Repo.all()
end

Workflow

1. Edit priv/content/projects.yml
2. Run mix portfolio.import --user you@example.com
3. Changes appear in the site
4. Commit the YAML changes to git

No admin panel. No CMS. Just text files and a database.

References

• Ecto SQLite3 Adapter
• YamlElixir
• Flop for Filtering