Data Model

Recommended database schema for EWP implementations.

Naming Convention

Database column names use snake_case (standard for relational databases). JSON serialization uses camelCase. The mapping is explicit: content_hash ↔ contentHash, created_at ↔ createdAt, publisher_address ↔ publisherAddress.

Node

Stores identity records for the self-node and all known peers.

Column	Type	Constraints	Description
address	string	PRIMARY KEY	Ethereum address (EIP-55 checksummed)
url	string	NOT NULL, UNIQUE	Canonical HTTPS URL
title	string	NOT NULL	Display name
description	string	NULLABLE	Node description
ewp_version	string	NOT NULL	Highest EWP version advertised
is_self	boolean	NOT NULL	true for local node; false for peers
created_at	timestamp	NOT NULL	Record creation time
updated_at	timestamp	NOT NULL	Time of last accepted profile update

Connection

Stores directed follow relationships.

Column	Type	Constraints	Description
id	integer	PRIMARY KEY, AUTO	Surrogate key
follower_address	string	NOT NULL, FK → nodes.address	Address of the following node
followee_address	string	NOT NULL, FK → nodes.address	Address of the followed node
created_at	timestamp	NOT NULL	When connection was established. Used for staleness checks.

Unique constraint: (follower_address, followee_address).

Content

Stores Content Units, deduplicated by hash.

Column	Type	Constraints	Description
content_hash	string	PRIMARY KEY	SHA-256 hash (0x-prefixed)
content_kind	string	NOT NULL	POST or FILE
body	text	NULLABLE	UTF-8 text body (POST only)
filename	string	NULLABLE	Original filename (FILE only)
mimetype	string	NULLABLE	MIME type
size	integer	NULLABLE	Content size in bytes
local_path	string	NULLABLE	Storage path for FILE content
created_at	timestamp	NOT NULL	When content was first stored

Publication

Links a Content Unit to authorship and its Proof of Source.

Column	Type	Constraints	Description
id	integer	PRIMARY KEY, AUTO	Surrogate key
content_hash	string	NOT NULL, FK → contents.content_hash	Content identifier
publisher_address	string	NOT NULL, FK → nodes.address	Publisher's Ethereum address
content_kind	string	NOT NULL	POST or FILE (denormalized)
signature	string	NULLABLE	EIP-712 signature. NULL = local-only draft.
description	string	NULLABLE	Description for FILE publications
slug	string	NULLABLE	Human-friendly URL alias (local metadata)
timestamp	integer	NOT NULL	Unix epoch seconds. Canonical value for PoS reconstruction.
created_at	timestamp	NOT NULL	ISO 8601 representation of timestamp.

Immutability

content_hash, publisher_address, signature, content_kind, and timestamp are immutable. description and slug are local metadata and may be updated.

Entity Relationship Diagram

┌──────────────────┐         ┌───────────────────┐
│       Node       │         │    Connection     │
│──────────────────│         │───────────────────│
│ address (PK)     │◄────────│ follower_address  │
│ url              │         │ followee_address  │────►[ Node ]
│ title            │         │ created_at        │
│ description      │         └───────────────────┘
│ ewp_version      │
│ is_self          │
│ created_at       │
│ updated_at       │
└────────┬─────────┘
         │ 1:N
         ▼
┌──────────────────────┐
│     Publication      │
│──────────────────────│
│ id (PK)              │
│ content_hash         │──────────────────────►┌──────────────────┐
│ publisher_address    │                        │     Content      │
│ content_kind         │                        │──────────────────│
│ signature            │                        │ content_hash (PK)│
│ description          │                        │ content_kind     │
│ slug                 │                        │ body             │
│ timestamp            │                        │ filename         │
│ created_at           │                        │ mimetype         │
└──────────────────────┘                        │ size             │
                                                │ local_path       │
                                                │ created_at       │
                                                └──────────────────┘