Profile Schema

Actual keyword order in tables must never be significant in queries.

Member Table

The member table stores the basic information for authentication and login. The username and password hash may additionally be stored in an Apache .htpasswd file.

Username+domain must be a globally unique identifier. The name/uri/email triple should also be unique.

CREATE TABLE wbm_members (
  username character varying(16) NOT NULL,
  realname character varying(80),
  uri character varying(255),
   email character varying(50),
  status character varying(1),
  authz integer,
  authcomment character varying(40),
  firstvisit timestamp without time zone,
  lastvisit timestamp without time zone,
  thisvisit timestamp without time zone,
  pwhash character varying(40),
  seed character varying(4),
  CONSTRAINT wbm_members_pkey PRIMARY KEY (username)
)

Address Table

The address table stores all the email history for a member.

CREATE TABLE wbm_addresses (
  address_id serial NOT NULL,
  username character varying(16) NOT NULL,
  realname character varying(80),
  address character varying(50),
  confirmed character varying(1),
  active character varying(1),
  status character varying(1),
  firstactive timestamp without time zone,
  lastactive timestamp without time zone,
  lastmessage character varying(80),
  CONSTRAINT wbm_addresses_pkey PRIMARY KEY (address_id)
)