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

     ↓ loaded on app start (or mix portfolio_hub.import)

priv/repo/portfolio_hub_dev.db   # SQLite database (queryable)

YAML files are your source of truth. Edit them, restart the app, and the database is rebuilt from them. 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]

  @derive {
    Flop.Schema,
    filterable: [:category, :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 :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
  featured: true
  tags:
    - Elixir
    - Phoenix
    - AI

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

The Loader

A ContentLoader module reads the YAML and writes it to SQLite. It clears the user’s content first, so the database always matches the files:

defmodule PortfolioHub.ContentLoader do
  @default_user_email "you@example.com"

  def load_all(opts \\ []) do
    email = Keyword.get(opts, :user, @default_user_email)
    user = get_or_create_user(email)
    clean_user_content(user)

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

    :ok
  end
end

The application calls load_all/0 from a task in its supervision tree on every boot, so a deploy is all it takes to publish new content. A thin Mix task runs the same loader by hand:

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

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

    case opts[:user] do
      nil -> PortfolioHub.ContentLoader.load_all()
      email -> PortfolioHub.ContentLoader.load_all(user: email)
    end

    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

on_conflict: :replace_all keeps the insert safe even if a row with the same slug is still there, so the loader can run any number of times.

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
• Full SQL power: Ecto queries, joins and filtering

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

Workflow

1. Edit priv/content/projects.yml
2. Restart the app locally (or run mix portfolio_hub.import)
3. Changes appear in the site
4. Commit the YAML changes and deploy

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

References

• Ecto SQLite3 Adapter
• YamlElixir
• Flop for Filtering