Database Schema — 27 PostgreSQL Tables

All of ProtoPulse’s persistent state lives in PostgreSQL. The schema is defined in shared/schema.ts — a single TypeScript file that drives table structure, API validation (via Zod), and static typing simultaneously. This page documents each table group, its columns, and how the tables relate to one another.

Overview

ORM: Drizzle ORM (drizzle-orm + drizzle-kit)
Schema file: shared/schema.ts (single source of truth)
Zod validators: generated automatically from each table using createInsertSchema() from drizzle-zod
TypeScript types: inferred from the Drizzle schema using $inferSelect and z.infer<>
Soft deletes: projects, architecture_nodes, architecture_edges, and bom_items use a deleted_at timestamp; all other tables use hard deletes

The schema has grown beyond the original 27 tables documented here. As of the 0002 migration (2026-03-08), the core schema covers the 27 tables below. The Unified Parts Catalog (ADR 0010) and Arduino Workbench features introduce additional tables that will be documented in their respective feature guides.

Tables by domain

Project & User

These tables form the authentication and project ownership foundation. All project-scoped tables cascade on projects.id.

`users`

Registered accounts. Password hashing uses scrypt.

Column	Type	Notes
`id`	`serial` PK	Auto-increment
`username`	`text`	NOT NULL, UNIQUE
`password_hash`	`text`	NOT NULL — scrypt output
`created_at`	`timestamp`	DEFAULT NOW

`sessions`

Active login sessions. Sessions expire after 7 days.

Column	Type	Notes
`id`	`text` PK	UUID — sent as `X-Session-Id` header
`user_id`	`integer` FK → users.id CASCADE
`expires_at`	`timestamp`	NOT NULL — 7-day TTL
`created_at`	`timestamp`	DEFAULT NOW

`api_keys`

Encrypted AI provider API keys (Gemini, Anthropic). The key value is never stored in plaintext.

Column	Type	Notes
`id`	`serial` PK
`user_id`	`integer` FK → users.id CASCADE
`provider`	`text`	e.g. `gemini`, `anthropic`
`encrypted_key`	`text`	AES-256-GCM ciphertext
`iv`	`text`	Initialization vector (hex)
`created_at`	`timestamp`	DEFAULT NOW

`user_chat_settings`

Per-user AI configuration. One row per user (unique on user_id).

Column	Type	Notes
`id`	`serial` PK
`user_id`	`integer` FK → users.id CASCADE	UNIQUE
`ai_provider`	`text`	DEFAULT `anthropic`
`ai_model`	`text`	DEFAULT `claude-sonnet-4-5-20250514`
`ai_temperature`	`real`	DEFAULT `0.7`
`custom_system_prompt`	`text`	DEFAULT `''`
`routing_strategy`	`text`	`user` \| `auto` \| `quality` \| `speed` \| `cost`; DEFAULT `user`
`preview_ai_changes`	`boolean`	DEFAULT `true`
`updated_at`	`timestamp`	DEFAULT NOW

`projects`

Top-level project container. All design data belongs to a project.

Column	Type	Notes
`id`	`serial` PK
`name`	`text`	NOT NULL
`description`	`text`	DEFAULT `''`
`owner_id`	`integer` FK → users.id	NULLABLE
`version`	`integer`	DEFAULT `1` — incremented on update
`created_at`	`timestamp`	DEFAULT NOW
`updated_at`	`timestamp`	DEFAULT NOW
`deleted_at`	`timestamp`	NULLABLE — soft delete marker
`approved_at`	`timestamp`	NULLABLE — design review approval
`approved_by`	`integer` FK → users.id	NULLABLE

Indexes: idx_projects_owner, idx_projects_owner_deleted

Architecture

The architecture editor stores block diagrams as nodes and edges in React Flow format.

`architecture_nodes`

Individual blocks on the architecture canvas (MCU, sensor, power, connector, etc.).

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`node_id`	`text`	Client-generated UUID; UNIQUE per project
`node_type`	`text`	1–100 chars; e.g. `mcu`, `sensor`, `power`, `comm`, `connector`
`label`	`text`	NOT NULL — display name
`position_x`	`real`	Canvas X coordinate
`position_y`	`real`	Canvas Y coordinate
`data`	`jsonb`	NULLABLE — `{ description?, componentPartId? }`
`version`	`integer`	DEFAULT `1`
`updated_at`	`timestamp`	DEFAULT NOW
`deleted_at`	`timestamp`	NULLABLE — soft delete

Indexes: idx_arch_nodes_project, idx_arch_nodes_project_deleted, unique uq_arch_nodes_project_node, GIN index on data (jsonb_path_ops)

`architecture_edges`

Connections between architecture nodes, carrying signal metadata.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`edge_id`	`text`	Client-generated UUID; UNIQUE per project
`source`	`text`	Source `node_id`
`target`	`text`	Target `node_id`
`label`	`text`	NULLABLE — e.g. `SPI Bus`
`animated`	`boolean`	DEFAULT `false` — React Flow animation
`style`	`jsonb`	NULLABLE — `{ stroke?: string }`
`signal_type`	`text`	NULLABLE — `SPI`, `I2C`, `UART`, `analog`, etc.
`voltage`	`text`	NULLABLE — e.g. `3.3V`
`bus_width`	`integer`	NULLABLE
`net_name`	`text`	NULLABLE — named net reference
`version`	`integer`	DEFAULT `1`
`deleted_at`	`timestamp`	NULLABLE — soft delete

Indexes: idx_arch_edges_project, idx_arch_edges_project_deleted, unique uq_arch_edges_project_edge

Circuit

The circuit domain models schematic, breadboard, and PCB views. A project can have multiple circuit_designs (hierarchical sheets); each design has its own instances, nets, and wires.

`circuit_designs`

Top-level circuit schematic. Supports hierarchical sheets via parent_design_id.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`parent_design_id`	`integer`	NULLABLE — links to parent sheet for hierarchy
`name`	`text`	DEFAULT `Main Circuit`
`description`	`text`	NULLABLE
`settings`	`jsonb`	NOT NULL, DEFAULT `{}`
`version`	`integer`	DEFAULT `1`
`created_at` / `updated_at`	`timestamp`	DEFAULT NOW

`circuit_instances`

Component placements within a circuit design. Tracks position across all three views (schematic, breadboard, PCB).

Column	Type	Notes
`id`	`serial` PK
`circuit_id`	`integer` FK → circuit_designs.id CASCADE
`part_id`	`integer` FK → component_parts.id SET NULL
`reference_designator`	`text`	e.g. `R1`, `U2`
`schematic_x/y`	`real`	DEFAULT `0`
`schematic_rotation`	`real`	DEFAULT `0`
`breadboard_x/y`	`real`	NULLABLE
`pcb_x/y`	`real`	NULLABLE
`pcb_rotation`	`real`	DEFAULT `0`
`pcb_side`	`text`	DEFAULT `front`
`properties`	`jsonb`	NOT NULL, DEFAULT `{}`

`circuit_nets`

Electrical nets — the logical connections between component pins.

Column	Type	Notes
`id`	`serial` PK
`circuit_id`	`integer` FK → circuit_designs.id CASCADE
`name`	`text`	NOT NULL
`net_type`	`text`	`signal` \| `power` \| `ground` \| `bus`; DEFAULT `signal`
`voltage`	`text`	NULLABLE
`bus_width`	`integer`	NULLABLE
`segments`	`jsonb`	DEFAULT `[]` — connection segments (instance + pin pairs)
`labels`	`jsonb`	DEFAULT `[]` — net labels with positions
`style`	`jsonb`	DEFAULT `{}` — visual style

`circuit_wires`

Physical wire routing for a net in a specific view.

Column	Type	Notes
`id`	`serial` PK
`circuit_id`	`integer` FK → circuit_designs.id CASCADE
`net_id`	`integer` FK → circuit_nets.id CASCADE
`view`	`text`	`schematic` \| `breadboard` \| `pcb`
`points`	`jsonb`	NOT NULL, DEFAULT `[]` — array of `{x, y}` points
`layer`	`text`	DEFAULT `front`
`width`	`real`	DEFAULT `1.0`
`wire_type`	`text`	DEFAULT `wire`
`provenance`	`text`	DEFAULT `manual` — `manual` or `autorouted`

`hierarchical_ports`

Inter-sheet connection ports for hierarchical circuit designs.

Column	Type	Notes
`id`	`serial` PK
`design_id`	`integer` FK → circuit_designs.id CASCADE
`port_name`	`text`	NOT NULL
`direction`	`text`	`input` \| `output` \| `bidirectional`; DEFAULT `bidirectional`
`net_name`	`text`	NULLABLE
`position_x/y`	`real`	DEFAULT `0`

BOM & Procurement

Bill of Materials tracking, point-in-time snapshots for diffing, and component lifecycle / obsolescence monitoring.

`bom_items`

Line items in a project’s bill of materials.

bom_items is a legacy table. New code should use the Unified Parts Catalog (parts + part_stock) introduced in ADR 0010. The bom_items table is kept for compatibility during the Phase 5–7 migration window.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`part_number`	`text`	NOT NULL
`manufacturer`	`text`	NOT NULL
`description`	`text`	NOT NULL
`quantity`	`integer`	DEFAULT `1`
`unit_price`	`numeric(10,4)`	NOT NULL
`total_price`	`numeric(10,4)`	Computed server-side: qty × unit_price
`supplier`	`text`	NOT NULL
`stock`	`integer`	DEFAULT `0`
`status`	`text`	`In Stock` \| `Low Stock` \| `Out of Stock` \| `On Order`
`lead_time`	`text`	NULLABLE
`deleted_at`	`timestamp`	NULLABLE — soft delete

`bom_snapshots`

Point-in-time BOM captures used for the BOM diff / comparison view.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`label`	`text`	NOT NULL — human-readable name, e.g. `v1.0 release`
`snapshot_data`	`jsonb`	NOT NULL — serialized `BomItem[]` at capture time
`created_at`	`timestamp`	DEFAULT NOW

`component_lifecycle`

Obsolescence and lifecycle status tracking per BOM part number.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`bom_item_id`	`integer`	NULLABLE — links to `bom_items`
`part_number`	`varchar(100)`	NOT NULL
`manufacturer`	`varchar(200)`	NULLABLE
`lifecycle_status`	`varchar(50)`	`active` \| `nrnd` \| `eol` \| `discontinued`; DEFAULT `active`
`last_checked_at`	`timestamp`	NULLABLE
`alternate_part_numbers`	`text`	NULLABLE — comma-separated alternates
`data_source`	`varchar(100)`	NULLABLE
`notes`	`text`	NULLABLE

Components

Custom component definitions used by the Component Editor and referenced by circuit instances.

`component_parts`

Project-scoped custom component definitions. Each part stores its visual representations (breadboard, schematic, PCB) and electrical connectors as JSONB.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`node_id`	`text`	NULLABLE — links to architecture node
`meta`	`jsonb`	`PartMeta` — title, family, manufacturer, tags, etc.
`connectors`	`jsonb`	Array of `Connector` objects (pin definitions)
`buses`	`jsonb`	Array of `Bus` objects
`views`	`jsonb`	`PartViews` — breadboard, schematic, PCB shapes
`constraints`	`jsonb`	Array of `Constraint` objects (DRC rules)
`version`	`integer`	DEFAULT `1` — auto-incremented on update

Indexes: GIN indexes on meta and connectors for JSONB path queries.

`component_library`

Global shared component library. Components can be public, forked, and downloaded by other users.

Column	Type	Notes
`id`	`serial` PK
`title`	`text`	NOT NULL
`description`	`text`	NULLABLE
`meta` / `connectors` / `buses` / `views` / `constraints`	`jsonb`	Same structure as `component_parts`
`tags`	`text[]`	NOT NULL, DEFAULT `[]`
`category`	`text`	NULLABLE
`is_public`	`boolean`	DEFAULT `false`
`author_id`	`text`	NULLABLE
`forked_from_id`	`integer`	NULLABLE — self-reference for forks
`download_count`	`integer`	DEFAULT `0`

`spice_models`

Global SPICE model library. Not project-scoped; seeded with ~20 standard models on first run.

Column	Type	Notes
`id`	`serial` PK
`name`	`text`	NOT NULL — e.g. `2N2222`
`model_type`	`text`	Enum: `NPN`, `PNP`, `DIODE`, `ZENER`, `NMOS`, `PMOS`, `OPAMP`, `TIMER`, `RESISTOR`, `CAPACITOR`, `INDUCTOR`, and more
`spice_directive`	`text`	Full `.MODEL` or `.SUBCKT` directive
`parameters`	`jsonb`	Key model parameters
`category`	`text`	`transistor` \| `diode` \| `opamp` \| `passive` \| `ic` \| `voltage_regulator` \| `mosfet` \| `jfet`
`datasheet`	`text`	NULLABLE — datasheet URL

AI & History

Tables that record the AI conversation history, user action log, and AI tool execution audit trail.

`chat_messages`

Messages in the AI chat panel. Supports conversation branching.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`role`	`text`	`user` \| `assistant` \| `system`
`content`	`text`	NOT NULL — message body (may contain Markdown)
`timestamp`	`timestamp`	DEFAULT NOW
`mode`	`text`	DEFAULT `chat`
`branch_id`	`text`	NULLABLE — conversation branch identifier
`parent_message_id`	`integer`	NULLABLE — parent message for branching
`metadata`	`text`	NULLABLE

Indexes: idx_chat_messages_project, idx_chat_messages_project_ts (for chronological queries), idx_chat_messages_branch

`history_items`

Human-readable action history shown in the Sidebar history panel.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`action`	`text`	NOT NULL — description of the action
`user`	`text`	NOT NULL — `User` or `AI`
`timestamp`	`timestamp`	DEFAULT NOW

`ai_actions`

Audit log of every AI tool call — tool name, parameters, and result. Used for replay and debugging.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`chat_message_id`	`text`	NULLABLE — links to the triggering chat message
`tool_name`	`text`	NOT NULL — AI tool function name, e.g. `addArchitectureNode`
`parameters`	`jsonb`	NOT NULL, DEFAULT `{}` — tool call inputs
`result`	`jsonb`	NOT NULL, DEFAULT `{}` — tool call output
`status`	`text`	DEFAULT `completed`
`created_at`	`timestamp`	DEFAULT NOW

Validation & Simulation

DRC/ERC results and SPICE simulation outputs.

`validation_issues`

Design rule check and electrical rules check results. Populated by the AI’s validation tools and the client-side DRC engine.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`severity`	`text`	`error` \| `warning` \| `info`
`message`	`text`	NOT NULL — human-readable description
`component_id`	`text`	NULLABLE — references a node label or reference designator
`suggestion`	`text`	NULLABLE — suggested fix

`simulation_results`

Stored SPICE simulation outputs. Each row is one simulation run.

Column	Type	Notes
`id`	`serial` PK
`circuit_id`	`integer` FK → circuit_designs.id CASCADE
`analysis_type`	`text`	`op` \| `tran` \| `ac` \| `dc`
`config`	`jsonb`	Simulation parameters (stop time, step, frequency range, etc.)
`results`	`jsonb`	Node voltages, branch currents, waveform data
`status`	`text`	`completed` \| `failed`; DEFAULT `completed`
`engine_used`	`text`	NULLABLE — simulator engine name
`elapsed_ms`	`integer`	NULLABLE — wall-clock time
`size_bytes`	`integer`	NULLABLE — result payload size
`error`	`text`	NULLABLE — error message if status is `failed`

`design_preferences`

AI-learned per-project user preferences (component families, routing styles, etc.). Unique per (project_id, category, key).

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`category`	`text`	Preference category, e.g. `component`, `routing`
`key`	`text`	Preference key, e.g. `preferred_mcu_family`
`value`	`text`	Preference value
`source`	`text`	`ai` \| `user`; DEFAULT `ai`
`confidence`	`real`	AI confidence score; DEFAULT `0.8`

Unique index: uq_design_prefs_project_cat_key on (project_id, category, key)

Design Management

Point-in-time captures and threaded code-review-style comments on design elements.

`design_snapshots`

Point-in-time captures of the architecture diagram (nodes + edges JSON). Used for visual diff and rollback.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`name`	`varchar(200)`	NOT NULL — e.g. `Before major refactor`
`description`	`text`	NULLABLE
`nodes_json`	`jsonb`	NOT NULL — serialized node array at capture time
`edges_json`	`jsonb`	NOT NULL — serialized edge array at capture time
`created_at`	`timestamp`	DEFAULT NOW

`design_comments`

Threaded, spatially-anchored comments for design review. Comments can be attached to nodes, edges, BOM items, or placed at arbitrary canvas coordinates.

Column	Type	Notes
`id`	`serial` PK
`project_id`	`integer` FK → projects.id CASCADE
`user_id`	`integer` FK → users.id	NULLABLE
`parent_id`	`integer`	NULLABLE — self-reference for thread replies
`target_type`	`text`	`general` \| `node` \| `edge` \| `bom_item` \| `spatial`
`target_id`	`text`	NULLABLE — ID of the target element
`spatial_x/y`	`real`	NULLABLE — canvas coordinates for spatial pins
`spatial_view`	`text`	`architecture` \| `schematic` \| `pcb` \| `breadboard`
`content`	`text`	NOT NULL — Markdown supported
`status`	`text`	`open` \| `resolved` \| `blocked` \| `wontfix`; DEFAULT `open`
`status_updated_by`	`integer` FK → users.id	NULLABLE
`status_updated_at`	`timestamp`	NULLABLE

Indexes: idx_design_comments_project, idx_design_comments_project_target, idx_design_comments_parent

Viewing and modifying the schema

Browse the schema interactively

Drizzle Studio provides a web-based database browser:

npm run db:studio

This opens a local web UI where you can browse tables, run queries, and inspect row data.

Modify the schema

All schema changes start in shared/schema.ts. After editing, sync the changes to your local database:

# Development — fast direct sync (no migration file)
npm run db:push

# Generate a migration file for production deployments
npm run db:generate

Never edit the database directly with ALTER TABLE or psql when using Drizzle. All schema changes must go through shared/schema.ts so that TypeScript types and Zod validators stay in sync. Out-of-band changes will cause type errors and potential runtime failures.

The generated migration files are stored in migrations/ and should be committed to source control. In production deployments, apply them with npm run db:migrate.

Add a new table

Define the table in shared/schema.ts using pgTable()
Add a createInsertSchema() call to generate the Zod validator
Export InsertX and X TypeScript types with z.infer<> and $inferSelect
Add the corresponding IStorage methods to server/storage.ts
Run npm run db:push locally to apply the change
Run npm run check to verify TypeScript is still clean

For related reading, see System Architecture and the API Overview.

Setup & Architecture

API Reference

Contributing

Database Schema — 27 PostgreSQL Tables

Overview

Tables by domain

`users`

`sessions`

`api_keys`

`user_chat_settings`

`projects`

`architecture_nodes`

`architecture_edges`

`circuit_designs`

`circuit_instances`

`circuit_nets`

`circuit_wires`

`hierarchical_ports`

`bom_items`

`bom_snapshots`

`component_lifecycle`

`component_parts`

`component_library`

`spice_models`

`chat_messages`

`history_items`

`ai_actions`

`validation_issues`

`simulation_results`

`design_preferences`

`design_snapshots`

`design_comments`

Viewing and modifying the schema

Browse the schema interactively

Modify the schema

Add a new table

Build docs developers (and LLMs) love

Setup & Architecture

API Reference

Contributing

Documentation Index

​Overview

​Tables by domain

​users

​sessions

​api_keys

​user_chat_settings

​projects

​architecture_nodes

​architecture_edges

​circuit_designs

​circuit_instances

​circuit_nets

​circuit_wires

​hierarchical_ports

​bom_items

​bom_snapshots

​component_lifecycle

​component_parts

​component_library

​spice_models

​chat_messages

​history_items

​ai_actions

​validation_issues

​simulation_results

​design_preferences

​design_snapshots

​design_comments

​Viewing and modifying the schema

​Browse the schema interactively

​Modify the schema

​Add a new table

Build docs developers (and LLMs) love

Overview

Tables by domain

`users`

`sessions`

`api_keys`

`user_chat_settings`

`projects`

`architecture_nodes`

`architecture_edges`

`circuit_designs`

`circuit_instances`

`circuit_nets`

`circuit_wires`

`hierarchical_ports`

`bom_items`

`bom_snapshots`

`component_lifecycle`

`component_parts`

`component_library`

`spice_models`

`chat_messages`

`history_items`

`ai_actions`

`validation_issues`

`simulation_results`

`design_preferences`

`design_snapshots`

`design_comments`

Viewing and modifying the schema

Browse the schema interactively

Modify the schema

Add a new table