E-Commerce Database Schema

Problem Description

A new e-commerce platform needs a PostgreSQL database schema to power its core ordering system. The platform sells physical goods and needs to track customers, their orders, individual order lines, and shipments. Customers may have both a standard account and the ability to store saved payment methods, but the schema must enforce that payment method records can only exist for verified customers.

The team has adopted a strict PostgreSQL-first approach: they want the database itself — not the application layer — to enforce business rules wherever possible. All naming must be consistent and self-documenting so that any developer looking at the schema immediately understands the relationships and constraints at a glance, including from error messages in production logs.

The schema should also support multiple account tiers (standard and premium), where each tier has a different set of attributes tracked, but all share common customer attributes. The ordering hierarchy must preserve the full ancestry of each item so that joins across the hierarchy never require traversing intermediate tables unnecessarily.

Output Specification

Produce a single SQL file named schema.sql containing all DDL needed to build the schema from scratch, including:

• All type and column definitions required for the schema
• All table definitions with constraints, including the customer hierarchy and order line hierarchy
• Any trigger functions and triggers required to enforce cross-table rules
• At least one stored procedure for inserting a new order (with its first line)
• At least one stored function for looking up a customer by email
• A view appropriate for customer-facing order history

The file should be runnable as a fresh database setup. Also produce a types.yaml file cataloging all custom types used in the schema, their underlying base types, and what category of data they represent.