aiseed.dev›AI-Native Software Development
2-03 / Series
2-03 № 03 · 2026

Stand up the data layer
first, on your own side.

Stand up the data layer everything sits on, first, on your own side

2026.07.01 🌐 日本語

The first thing the Independence part lays is the data layer everything sits on. Analysis, the AI's RAG, course booking, the core systems — all of the data sits on top of this.

2-01: Becoming Independent from Microsoft and Google — The Whole Map drew the map. From here, this part stands up the OSS on that map, one by one. It all goes on the one machine handed to the AI in 2-02: Give the AI a PC of Its Own.

A builder's work does not begin with writing code. It begins with standing up proven OSS (1-05). Generic functionality is already shared with the world, so you don't "write" it, you "stand it up."

Lay the data first

The order has a reason. Much of what later chapters stand up depends on the database.

With the foundation in place first, everything above is just "put it on the DB that already exists." The earlier you lay it, the lighter everything after.

And that foundation does not have to be heavy from the start.

SQLite is usually enough. Step up to PostgreSQL only when you share and several people write at once.

For one person, one app, most uses — start with SQLite. The order in which you lay things follows from that.

Hold it in a single file

The default database is SQLite. There is no server to run; it fits in one file. It is built into Python from the start, so there is nothing extra to install. It ships inside every phone and browser, and SQLite itself says it is the most widely deployed database in the world (sqlite.org, "Most Widely Deployed").

# no library needed — it's in the standard install
sqlite3 app.db 'CREATE TABLE memo(id integer primary key, body text)'

Settings one person keeps, and small data sitting on the device, are both covered by this. The gate (PocketBase) stood up in 2-05 also runs on this SQLite. If a single app just keeps data locally, start with SQLite. It is standard SQL, so the AI writes it as-is.

What runs out is the moment you share and several people write at the same time. Only then do you step the warehouse up a level.

Step up to PostgreSQL when you share

When you need a shared warehouse that several apps or people read and write at once, step up to PostgreSQL. It is open source, free, and fine for commercial use. It is the same standard SQL as SQLite, so stepping up does not change how you write. Trade the notebook (SQLite) for the warehouse (PostgreSQL) by what each is for.

Four decisions set it up.

sudo apt install postgresql postgresql-17-pgvector   # install; systemd starts it
sudo -u postgres createdb app                        # create the database
sudo -u postgres psql -d app -c 'CREATE EXTENSION vector'   # enable the extension

The application role's name and password are the human's to decide.

Enable pgvector now

Enable now the vector search that the AI's RAG (2-16) will use. The third line of the previous section is that one line.

-- example table holding embeddings; the dimension is set by the embedding model chosen in 2-16
CREATE TABLE docs (
  id    bigserial PRIMARY KEY,
  body  text,
  embedding vector(1024)
);

Now you have the foundation for searching documents by meaning. Put embeddings in embedding and pull the nearest with ORDER BY embedding <=> :query. The real RAG pipeline gets built in 2-16. For now, just have the vessel ready.

Migrate from Azure SQL

If you have an existing Azure SQL / SQL Server, pgloader carries schema and data in one pass.

pgloader mssql://user:pass@azure-host/db \
         postgresql://app:pass@localhost/app

Standard SQL (SELECT, JOIN, window functions) runs as-is. You drop only the vendor dialect, T-SQL. Business logic buried in stored procedures gets extracted by the AI and translated into Python (1-05). Then run in parallel with the old DB, reconcile the output, and stop the old one when the difference is gone (2-12).

Migration is not "rewrite everything." It is move onto the standard and drop only the dialect.

Analyze with DuckDB — on your own machine, at no extra charge

For aggregation and analysis, layer DuckDB on top of PostgreSQL. It is a columnar analytics engine: one file, no server. Excel's ceiling of 1,048,576 rows (Microsoft's specification) is gone; *your own machine aggregates up to the size of the file.*

uv add duckdb polars            # into the Python environment built in 2-04

import duckdb
# Aggregate across Parquet, PostgreSQL, and CSV in place, with SQL
duckdb.sql("INSTALL postgres; LOAD postgres;")
duckdb.sql("ATTACH 'dbname=app user=postgres host=localhost' AS pg (TYPE postgres)")
duckdb.sql("SELECT dept, sum(sales) FROM pg.sales GROUP BY dept ORDER BY 2 DESC")

You analyze where the data already sits, without copying it. The AI writes the SQL. "Which department is anomalous versus last month?" can be asked against your own real data, any number of times, at no extra charge. This is where you step off Power BI's per-seat billing.

Crunch Excel data with Polars

Excel is the input/output tool where people enter numbers and read results. That role does not change; the grid stays, as 2-07 says. What changes is the side that crunches the data behind it.

The .xlsx files piled up on disk are read directly by Polars, a fast dataframe built in Rust. It handles row counts that freeze Excel in an instant, and writes the result back to Excel, PostgreSQL, or Parquet alike.

import polars as pl
df  = pl.read_excel("sales_2025.xlsx")               # read the Excel people made
agg = df.group_by("dept").agg(pl.col("sales").sum()) # heavy aggregation in one line
agg.write_excel("dept_summary.xlsx")                 # write back to Excel people read

People enter and read in Excel; machines crunch with Polars and DuckDB. *Split the human's tool from the machine's tool by role.* SQL-shaped work goes to DuckDB, dataframe-shaped work to Polars. The same data can be touched with whichever tool you like.

For huge, always-on aggregation, move it onto the columnar DB server ClickHouse. But most in-house analysis needs only DuckDB and Polars.

How to check you are done

This chapter is done when these five hold.

  1. The table you created with sqlite3 app.db shows up under .tables
  2. systemctl status postgresql shows it running, and psql connects and lists the databases
  3. Typing \dx in psql lists vector
  4. From Python, DuckDB aggregates a PostgreSQL table and the per-department totals print
  5. Polars reads an .xlsx a person made, writes the aggregate back to an .xlsx, and that file opens in a spreadsheet application
sqlite3 app.db '.tables'
sudo -u postgres psql -d app -c '\dx'
uv run python -c "import duckdb, polars; print('ok')"

What the human holds

Values the human supplies

Actions the AI states before performing

Versions checked, and when

Summary

The data layer, onto your own side, first.

Almost no code was written. The generic is already there, as OSS. The builder stands it up. The next chapter writes the logic on top of this foundation — moving the macros and charts buried in Excel and Word out into Python, and putting a Flet screen on where one is needed.

Related articles