Schema reference: how SharePoint columns become SQL columns

What to expect in SQL Server or Postgres after a replication runs: table names, column names, and how each SharePoint column type maps across. Useful before you connect a report or BI tool, and when a SharePoint column changes type.

Naming

• Every table is prefixed with the site’s short name: {prefix}_{list} (e.g. a site prefixed sales with a Projects list becomes sales_projects). Multiple sites in one replication get different prefixes, so their tables never collide.
• List and column names come from SharePoint’s internal name, not its display name, so renaming a column in SharePoint does not rename it at the destination. Internal names are lowercased and _-separated (HoursEstimated becomes hours_estimated, Due Date becomes due_date).
• A name that collides with a SQL reserved word gets _col appended (Order becomes order_col); a name that would start with sp_ (SPSync’s own metadata-column prefix) gets _src appended.
• A multi-value column (multi-choice, multi-lookup, multi-person) gets its own child table, {table}__{col}, one row per value, keyed by (sp_item_id, ordinal).
• Optional convenience views, v_{prefix}_{list}, join in person columns’ display names (see Reporting views).

Standard columns on every table

Column	Type	Notes
sp_item_id	integer	Primary key. SharePoint’s item id (scoped to this table, not globally unique).
sp_list_id, sp_site_id	guid	Graph list and site ids.
sp_etag	text	Last-seen etag.
sp_created, sp_modified	datetime (UTC)
sp_author_id, sp_editor_id	guid, nullable	Look these up in sp_users (below).
sp_content_type	text
sp_deleted_at	datetime, nullable	Null means the row is live. Set on soft delete (the default; hard delete is a per-replication deletes: hard setting in the command-line tool’s YAML config; desktop and Azure runs always soft-delete).
sp_synced_at	datetime	Set on every write.

Every column derived from a SharePoint column is nullable at the destination, even if it is marked required in SharePoint, because a delta run can write an item before a required column is backfilled.

People

One shared sp_users table per destination (not per site or list):

Column	Type	Notes
id	guid	Primary key, derived deterministically from the person’s login, so the same person gets the same id on every run.
login	text	Unique. UPN or group login.
display_name	text
email	text, nullable
is_group	boolean

Every person/author/editor column across every list and site points at this one table.

Column type mapping

SharePoint type	Destination shape
Single line of text	{col} (text)
Multiple lines of text (plain or rich)	{col} (text; rich text is stored as raw HTML)
Number	{col} (float)
Currency	{col} (decimal, 4dp)
Yes/No	{col} (boolean)
Date and Time	{col} (date, or datetime in UTC)
Choice	{col} (text)
Choice (multi)	child table: value, ordinal
Lookup	{col}_id, {col}_value
Lookup (multi)	child table: lookup_id, lookup_value, ordinal (not confirmed on a live tenant)
Person	{col}_user_id -> sp_users.id
Person (multi)	child table: user_id -> sp_users.id, ordinal (not confirmed on a live tenant)
Managed metadata	not replicated yet: the column is skipped and reported as an unsupported column on each run
Managed metadata (multi)	not replicated yet: the column is skipped and reported as an unsupported column on each run
Hyperlink / Picture	{col}_url, {col}_description
Location	{col}_json, {col}_lat, {col}_lng
Calculated	{col}, as the formula’s result type (only the computed value, never the formula)
Content type, Created/Modified/Author/Editor	already covered by the standard columns
Attachments	not replicated (metadata-only product; file bytes and attachments are not copied)

A handful of SharePoint column types that carry no useful reporting value (workflow status, compliance/retention labels, _UIVersionString, business-connectivity/external data, audience targeting) are skipped, with a warning logged at discovery time rather than a silent drop.

Document libraries

A document library is a list table with these columns in addition to any custom ones:

Column	Type	Notes
file_name, file_url	text, nullable	Null on a folder or document set row.
file_size	integer, nullable	Null on a folder or document set row.
file_hash	text, nullable	Content hash, when Graph reports one. File bytes themselves are never fetched.
file_folder_path	text, nullable	The folder the item sits in, on every row.

Folders and document sets replicate as rows of their own, alongside the files, with the file columns left null; sp_content_type tells them apart. Two files with the same name in different folders are not a conflict: sp_item_id stays the primary key.

These five file columns have not been confirmed on a live tenant since the library read was changed to fill them, so check them against your own library before a report depends on them. The library’s own columns, content type and dates are populated as on any list.

Reporting views

An optional v_{prefix}_{list} view per list, mainly useful for BI tools. Lookup and choice columns already carry a human-readable value on the row itself, so those need no join. Person columns (including sp_author_id/sp_editor_id) only store an id, so the view left-joins sp_users to add display-name columns, so sp_author_id sits alongside a resolved sp_author_display_name. Multi-value child tables are outside the view, so query them directly. Soft-deleted rows (sp_deleted_at IS NOT NULL) are excluded, so the view always reflects what is live in SharePoint right now.

Only offered for a destination with a real, persistent, queryable table a report tool can connect to: SQL Server, PostgreSQL (including pgvector), MySQL, ClickHouse, Oracle, BigQuery, Databricks, SQLite and DuckDB. Every other destination kind (Azure AI Search, Elasticsearch, OpenSearch, Iceberg, LanceDB, Qdrant, Weaviate, ADLS Gen2, Dataverse, Delta Lake) writes through a local, ephemeral staging file rather than a server, so a view created there would be invisible to any report tool; asking for one there is refused.

From the desktop app, turn it on with the “Create reporting views” toggle, offered on the New replication wizard’s Destination step, and also on an existing replication’s Runs screen for any supported destination. A run that created views lists their names under “Reporting views” on that run’s row.

From the CLI, pass --create-reporting-views to spsync export-config (see Setup), or add create_reporting_views: true directly to an existing replication.yaml. It defaults to off either way.

Column descriptions in the database

The description you give a column in SharePoint’s column settings travels with it. On SQL Server, PostgreSQL (including pgvector), MySQL, MariaDB and Oracle, each run writes it to the destination as a column comment, together with the column’s display name, its SharePoint type, the list a lookup points at and a choice column’s values. Each table also gets a comment naming the SharePoint list, its site, its URL and its template, plus the list’s own description.

Where to read them:

• SQL Server: the MS_Description extended property, shown as the column’s Description in SQL Server Management Studio.
• PostgreSQL: \d+ table_name in psql, or col_description().
• MySQL and MariaDB: the COLUMN_COMMENT and TABLE_COMMENT columns of information_schema.
• Oracle: ALL_COL_COMMENTS and ALL_TAB_COMMENTS.

Rename a column or edit its description in SharePoint and the comment is updated on the next run. The destination column keeps its name, because names come from the internal name. Comments are cut to each database’s limit (1,024 characters for a MySQL column, 4,000 bytes on Oracle) and end in ... when cut. On MySQL and MariaDB a comment can only be set by restating the whole column, so SPSync leaves out any column it could not restate exactly, such as a MariaDB JSON column; the column itself is never changed.

DuckDB, SQLite and the other destinations get no comments. For any destination, spsync export-schema replication.yaml writes schema.md: one section per table with each column’s type and description, the joins between tables and the child tables. Pass --destination postgres (or another kind) to show that database’s column types, and --output to choose the file. It reads the list schemas from SharePoint and changes nothing in the destination. The file is plain Markdown, so you can paste it into the instructions of a text-to-SQL tool or an agent that queries the copy.

Search destinations: Azure AI Search

Azure AI Search gets the same tables, but as indexes rather than SQL tables: one index per list, one per multi-value child table, and one shared sp-users index. A few things differ from the SQL destinations above:

• Index names are the table names with underscores replaced by dashes (sales_projects becomes sales-projects, sales_projects__tags becomes sales-projects--tags), a naming rule of the service itself.
• Every index has a sp_key field as its document key (the service requires a string key; sp_item_id is an integer and a child table’s key is composite). For a list’s main index it is the item id as a string; for a child index it is an encoded form of (sp_item_id, ordinal). The table’s own key columns are still present as regular fields.
• Field types are the service’s own: text becomes Edm.String, integers Edm.Int64, numbers and currency Edm.Double, yes/no Edm.Boolean, dates Edm.DateTimeOffset (a date-only column is stored as midnight UTC). Choice values, lookup display names and other short text are searchable, filterable, sortable and facetable; multi-line text is searchable only; dates and numbers are filterable and sortable; ids are filterable only.
• Deleted items are removed from the index rather than kept with sp_deleted_at set, since a search index has no reason to keep a deleted document searchable.
• permitted_groups is a filterable string collection on every list and child index, for security trimming ($filter=permitted_groups/any(g: search.in(g, '...'))). It is empty today and will be populated once item-level permissions are captured.
• Optional vector field: with an embedding provider configured, every list index whose columns include one you chose to embed gains a sp_embedding vector field (plus sp_embedding_hash, used to re-embed only rows whose text changed) attached to an HNSW vector-search profile, ready for the service’s own vector and hybrid queries.
• A column that changes type gets a fresh -v2 index rather than an in-place change (the service does not allow one); new columns are added to the existing index in place.

Connecting to hosted destinations

SQL Server, PostgreSQL and MySQL each cover a family of hosted, wire-compatible variants. These connect through the exact same driver and destination as their engine, just with a different connection string. The provider-specific details below are compiled from each vendor’s published docs; confirm them against your own hosted instance before relying on them:

• SQL Server family: SQL Server, Azure SQL Database, Azure SQL Managed Instance, Amazon RDS for SQL Server, Google Cloud SQL for SQL Server. Azure SQL requires Encrypt=yes; RDS usually needs its own CA bundle trusted (or TrustServerCertificate=yes); Cloud SQL is simplest via the Cloud SQL Auth Proxy (127.0.0.1, no extra SSL flags).
• PostgreSQL family: PostgreSQL, Azure Database for PostgreSQL, Supabase, Neon, Google AlloyDB, Heroku Postgres, Amazon RDS for PostgreSQL, Amazon Aurora PostgreSQL, Google Cloud SQL for PostgreSQL. All of them want sslmode=require. Supabase and Heroku are best reached through their own connection pooler; Neon’s compute can cold-start on the first query after a period of idleness; AlloyDB and Cloud SQL are simplest via their own Auth Proxy.
• MySQL family: MySQL, MariaDB, Azure Database for MySQL, Amazon RDS for MySQL, Amazon Aurora MySQL, Google Cloud SQL for MySQL. Azure MySQL and RDS/Aurora require TLS by default; since this family connects through pymysql, that means ssl_ca=/path/to/ca.pem (not ssl_mode/sslmode, which belong to mysqlclient and Postgres respectively; pymysql accepts neither) pointing at the provider’s CA bundle; MariaDB uses the identical connection string and driver as MySQL.

Every one of the above authenticates with a plain username and password on the connection string; token-based authentication (Entra ID for Azure SQL/Postgres, IAM for RDS/Aurora) is not supported yet.

• Databricks (Delta tables in Unity Catalog): connects to a SQL warehouse with databricks://token:<PAT>@<workspace-host>/<sql-warehouse-http-path> (a personal access token; the user name is literally token, Databricks’s own JDBC/ODBC convention) or databricks://<client-id>:<client-secret>@<workspace-host>/<http-path> (a service principal, OAuth). The config additionally names the Unity Catalog (databricks_catalog); dataset_name is the schema inside it, so every table is a three-part catalog.schema.table name. Rows are staged through a Unity Catalog volume in the same schema (the principal needs CREATE VOLUME/READ VOLUME/WRITE VOLUME there, on top of USE CATALOG/USE SCHEMA/CREATE TABLE/SELECT/MODIFY) and merged with one atomic MERGE INTO per table. Type mapping differs from the table above only in width: every text column is Delta’s unbounded STRING, currency is DECIMAL(38,9), whole numbers are BIGINT, and date-times are TIMESTAMP instants in UTC (every session SPSync opens is pinned to a UTC session timezone).

See each destination’s own page (linked from /destinations/) for its exact connection-string example.

Schema evolution

• A new SharePoint column appears as a new destination column (or child table) on the next run; existing rows get it as null.
• A removed SharePoint column’s destination column is never dropped.
• A retyped SharePoint column (e.g. Number -> Currency) creates {col}_v2 with the new type and leaves {col} as it was; the run logs a warning naming both columns. This happens on SQL Server, PostgreSQL, MySQL (and MariaDB), ClickHouse, Oracle, SQLite and DuckDB only. Other destinations do not fork a retyped column into {col}_v2 today, and changing a column’s type on Iceberg or Dataverse is not supported yet; see the destination’s own page. A change between types that the destination stores the same way may not fork at all. See Troubleshooting for how to spot this.