Skip to Content.
Sympa Menu - Database design for SER/Asterisk/Phonebuilder

Subject: SIP in higher education

List archive

Database design for SER/Asterisk/Phonebuilder

Chronological Thread 
  • From: "Karl A. Krueger" <>
  • To:
  • Subject: Database design for SER/Asterisk/Phonebuilder
  • Date: Thu, 3 Mar 2005 15:42:45 -0500

I've attached the database design document and the corresponding SQL
DDL for the PostgreSQL database we're using for SER, Asterisk voice
mail, and phone configuration building.

The DDL is basically portable, with two exceptions: First, there is a
little use of PostgreSQL rewrite rules (CREATE RULE statements) which
would have to be translated into SQL triggers. Second, PostgreSQL
natively supports MAC addresses as a data type (MACADDR), which most
DBMSes do not -- they would need to be changed to VARCHAR or something
less truth-preserving in MySQL or the like.

Some other notes -- This is not a "read only" database. Asterisk and
SER both need to write to it. SER writes to the location table, while
Asterisk voicemail requires to be able to update people's voice mail

Karl A. Krueger
Network Security -- Linux/Unix Systems Support -- Etc.
Woods Hole Oceanographic Institution

10 February 2005

The VoIP system stores several kinds of user information in a
PostgreSQL database hosted on This includes
information on:

* Extensions (phone numbers)
* Users (people)
* Voice mail accounts
* Telephones (SIP hard phones)

The "voip" database is owned by the "voip" PostgreSQL user, and is
accessed by THREE major software systems:

* Asterisk "Comedian Mail" (
* SIP Express Router (SER)
* Phone Configuration Builder (

Some of the database tables (such as "users" and "extensions") are used
by all of these applications. Others (such as "extensions_ser_flags"
and "phones") are used by only one. It is much more important to
protect the data integrity of the shared tables, as wayward modification
of these can break the whole VoIP system. However, it is the shared
tables that must be written to by user-provisioning software.

The tables are as follows:

* users (central)
* extensions (central)
* extension_voicemail (voicemail, semi-dynamic)
* extension_ser_flags (ser)
* location (ser, dynamic)
* aliases (ser, dynamic, unused?)
* phone_models (phone)
* phones (phone)
* phone_extensions (phone)
* phone_misc (phone)

There are also the following views (derived tables):

* voicemail_users -- connects extensions to user
* voicemail -- connects extension_voicemail to voicemail_users
* ser_extensions -- connects extensions to extension_ser_flags
* subscriber -- connects ser_extensions to users

The chief role of these views is to massage the relatively normalized
data in the main tables into the specific formats demanded by the
Asterisk and SER applications -- the "voicemail" and "subscriber" views,
specifically. These are both basically data products, but there is a
rewrite rule to allow updates to "voicemail" to propagate back to
"extension_voicemail" so that Asterisk can update passwords for voice
mail accounts.


users -- the list of human beings (and roles) who use VoIP
user_id -- a username; for people should be their LDAP uid
first_name -- person's first name
last_name -- person's last name
email_address -- person's full email addrses
pager -- person's pager (unused)
time_created -- creation timestamp
time_modified -- modification timestamp (not yet used)

extensions -- the list of valid VoIP extensions
extension -- an extension name or number. Any extension listed
here will have voice mail and be able to register
against SER, except as noted below.
user_id -- OPTIONAL user (in the users table) who is associated
with this extension. If there is no user associated
with an extension, it will not get voice mail email.
sip_password -- SIP registration password for the user.
sip_domain -- should be "" for almost everything. May
be set to "no_sip" to exclude from the subscriber table.
time_created -- creation timestamp
time_modified -- modification timestamp (not yet used)

extension_voicemail -- the list of valid voice mail accounts
extension -- an extension number; the voice mail account number.
DOES NOT have to be an extension in the extensions
table; it is possible to have a voice mail account that
does not have a SIP extension. Should usually be
context -- the Asterisk voice mail context. Currently it seems
that this must be "default". If this is set to "no_vm"
it means the account has no voice mail.
vm_password -- the voice mail password. For tolerably obvious
reasons, this needs to be numeric.
vm_options -- any Asterisk voice mail options string for the

extension_ser_flags -- basically unused SER account flags
This table is not documented because, frankly, I have no idea
what most of it does -- and the parts I do know, are basically
unused. However, it needs to exist so that the subscriber table
can be created, and in case any of these settings are actually
relevant in the future.

location -- dynamic SER table for location tracking
When an extension is registered with SER, SER puts the location
information in here. The interesting bit is the relation
between the "extension" field and the "contact" field -- the
latter specifies where the extension is registered (an IP
address and possibly other data). Mine this to get the IP
addresses of registered devices. The rest is internal SER stuff
that isn't too relevant to the administrator.

aliases -- dynamic SER table, identical to "location", unused?
SER requires the existence of this table but does not seem to
actually use it in our current configuration. It has the same
table layout as "location".

phone_models -- known types of SIP hard phones.
model -- name of the model. Currently 'snom190', 'cisco7940',
and 'grandstream100' are listed.
lines -- number of lines it can have.

phones -- known SIP hard phones
phone_mac -- the Ethernet MAC address of a phone.
user_id -- the user, if any, whose phone it is. Used to set the
display name on Cisco phones.
model -- the brand and model of phone, from phone_models.

phone_extensions -- SIP extensions to place on each phone.
This is an important one! This is where you assign extensions
to the hard phones. It is really the core of the phone
configuration system, since the interesting thing to do with
phones is to have extensions ring on them.

Extensions MUST be ordered, so that they will be mapped onto the
right keys on the phone. The order numbers you place here do
not matter of themselves, but they have to be distinct. You can
have them be 100, 123, and 231 but they will still be mapped to
the first three buttons on the phone.

You can put as many extensions as you want on a phone here, but
a Cisco 7940 only can accept two, and a SNOM 190 can only accept
five (four, when one button is mapped to voice mail).

phone_mac -- the MAC address of the phone.
extension -- the extension to place on the phone.
order -- the ordering of the extensions.

phone_misc -- "data-drivel------driven" phone configuration variables.
This is for storing miscellaneous phone configuration lines,
which will be dumped relatively raw into the phone configuration
file. Note that this is U_N_O_R_D_E_R_E_D_.
phone_mac -- the MAC address yadda yadda.
variable -- name of the config variable.
value -- value of the config variable.

user_id varchar(64) primary key,
first_name varchar(25) default '' not null,
last_name varchar(45) default '' not null,
email_address varchar(50) default '' not null,
pager varchar(79) default '' not null,
time_created timestamp with time zone default now(),
time_modified timestamp with time zone default now()

CREATE RULE users_create_time AS ON INSERT TO users DO (
UPDATE users SET time_created = now() WHERE user_id = new.user_id;

CREATE TABLE extensions (
extension varchar(64) primary key,
user_id varchar(64) references users,
sip_password varchar(25) not null,
sip_domain varchar(128) default '' not null,
time_created timestamp with time zone default now(),
time_modified timestamp with time zone default now()

CREATE RULE extensions_create_time AS ON INSERT TO extensions DO
UPDATE extensions SET time_created = now()
WHERE extension = new.extension;

CREATE TABLE extension_ser_flags (
extension varchar(64) references extensions
on delete cascade
on update cascade,
phplib_id varchar(32) default '' not null,
confirmation varchar(64) default '' not null,
flag char(1) default 'o' not null,
sendnotification varchar(50) default '' not null,
greeting varchar(50) default '' not null,
ha1 varchar(128) default '' not null,
ha1b varchar(128) default '' not null,
perms varchar(32),
allow_find char(1) default '0' not null,
timezone varchar(128),
rpid varchar(128)

CREATE VIEW ser_extensions AS
SELECT * FROM extensions NATURAL JOIN extension_ser_flags
WHERE extensions.sip_domain != 'no_sip';

CREATE RULE extension_creates_ser_flags AS ON INSERT to extensions DO
INSERT INTO extension_ser_flags ( extension ) VALUES ( new.extension

CREATE TABLE extension_voicemail (
extension varchar(64) unique not null,
context varchar(79) not null default 'default',
vm_password varchar(25) not null,
vm_options varchar(159) not null default '',
CONSTRAINT ev_pkey PRIMARY KEY ( extension, context )

CREATE VIEW voicemail_users AS SELECT
extensions.extension AS extension,
extensions.sip_domain AS sip_domain,
users.user_id AS user_id,
users.email_address AS email_address,
users.pager AS pager,
users.first_name || ' ' || users.last_name AS full_name,
users.time_modified AS time_modified
FROM extensions LEFT JOIN users ON extensions.user_id = users.user_id;

extension_voicemail.context AS context,
extension_voicemail.extension AS mailbox,
extension_voicemail.vm_password AS password,
voicemail_users.full_name AS fullname,
voicemail_users.email_address AS email,
voicemail_users.pager AS pager,
extension_voicemail.vm_options AS options,
voicemail_users.time_modified AS datetime_modified
FROM extension_voicemail LEFT JOIN voicemail_users
ON extension_voicemail.extension = voicemail_users.extension
WHERE extension_voicemail.context != 'no_vm';

CREATE RULE update_voicemail AS ON UPDATE TO voicemail DO INSTEAD (
UPDATE extension_voicemail SET
context = new.context,
extension = new.extension,
vm_password = new.password
WHERE extension_voicemail.extension = old.extension;

ser_extensions.phplib_id AS phplib_id,
ser_extensions.extension AS user_id,
ser_extensions.sip_domain AS domain,
ser_extensions.sip_password AS password,
users.first_name AS first_name,
users.last_name AS last_name,
ser_extensions.extension AS phone,
users.email_address AS email_address,
timestamptz_smaller(users.time_created, ser_extensions.time_created)
AS datetime_created,
timestamptz_larger(users.time_modified, ser_extensions.time_modified)
AS datetime_modified,
ser_extensions.confirmation AS confirmation,
ser_extensions.flag AS flag,
ser_extensions.greeting AS greeting,
ser_extensions.ha1 AS ha1,
ser_extensions.ha1b AS ha1b,
ser_extensions.perms AS perms,
ser_extensions.allow_find AS allow_find,
ser_extensions.timezone AS timezone,
ser_extensions.rpid AS rpid
FROM ser_extensions LEFT JOIN users
ON ser_extensions.user_id = users.user_id;

CREATE TABLE location (
user_id varchar(64) references extensions,
domain varchar(128) default '' not null,
contact varchar(255) default '' not null,
expires timestamp with time zone,
q numeric(10,2),
callid varchar(255),
cseq numeric(11,0),
last_modified timestamp with time zone default now() not null,
replicate numeric(10,0),
state numeric(3,0),
flags numeric(11,0) not null default '0',
user_agent varchar(128) not null default ''

CREATE TABLE aliases (
user_id varchar(64) references extensions,
domain varchar(128) default '' not null,
contact varchar(255) default '' not null,
expires timestamp with time zone,
q numeric(10,2),
callid varchar(255),
cseq numeric(11,0),
last_modified timestamp with time zone default now() not null,
replicate numeric(10,0),
state numeric(3,0),
flags numeric(11,0) not null default '0',
user_agent varchar(128) not null default ''

CREATE TABLE phone_models (
model varchar(32) primary key,
lines smallint not null

phone_mac macaddr primary key,
user_id varchar(64) references users,
model varchar(32) references phone_models
on update cascade

CREATE TABLE phone_extensions (
phone_mac macaddr references phones not null
on update cascade,
extension varchar(64) references extensions not null,
ordering smallint not null default '0',
CONSTRAINT extension_order_pkey PRIMARY KEY ( phone_mac, ordering )

CREATE TABLE phone_misc (
phone_mac macaddr references phones not null
on update cascade,
variable varchar(128) not null,
value varchar(256)

  • Database design for SER/Asterisk/Phonebuilder, Karl A. Krueger, 03/03/2005

Archive powered by MHonArc 2.6.16.

Top of Page