Requirements gathering - Server Inventory App

Ideal requirements for the new application..

The basic information we need to manage

People:

We are a hosting center with various clients (could be considered "customers" by other hosting entities)
Most clients are associated with an OSS group (could be considered "companies")

contact information
- Name
- Email
- Address
- Telephone
- Cellphone
- Pager
- IM
- Homepage
- Additional Notes
- Community (company) connection(s)

Give the option to store contact information in an ldap directory?? (low-priority feature. MUST be an option for those who do not have ldap)

Systems:

We have servers that we manage (hands-on) and those that we don't (hands-off)
The servers are all different makes/models, sizes, configurations, etc.
They are all various uses as well. We need to know at a moment's notice what a server is setup to do (web? mail? etc.. what ports are open?)
Other inventory-like information should be tracked as well, such as purchasing data, service contracts, etc.
Location and size information should be kept to integrate into a visualization interface (such as a new "rackview")
virtualized hosts will be an issue as well. We may have one physical host that serves many virtual hosts.
We want to rely heavily on this app as a central changelog and change-management system. Changelog entries for each host (as well as global changelog entries) should be there.

Various app requirements & needs

Themeability/skinning.. Does not have to be fancy, but being able to easily change a logo or css settings would be great
Modularity: Ability to add plugins that manage other pieces of information maybe?
User control: To begin with, let apache handle user access and just pull the user from the apache variable. This user info could be used to control access to certain pieces of information, and to know who is making a changelog entry.

Possible db tables

Earlier this year, Kees and Corey got together and drew out a db schema for a combined rackview/inventory app. These are the tables laid out, put in the wiki so we can modify them and comment them as needed:

physical_hosts

id
physical_name
description
serial_number
initial_cost
current_value (how will this be kept up to date? -c)
purchase_date
service_date (next date the host requires a regular servicing check-up (cleaning, etc))
host_type_id (pointer to the host table for physical information)
service_contract_start
service_contract_end
service_contract_renewal
note_id (pointer to the note table for more details on this object)
rack_id
u_count_from_bottom (how many U of space from the bottom of a rack does this box start.. size will pull from host_type table)
racked_orientation_id (pointer to the racked_orientation table for direction of racking)

operating_systems

(operating system gets installed on to a host. this is where OS info and admin info tie in to a host)
id
hostname
contact_id
primary_admin_id
secondary_admin_id
os_name
os_version
physical_host_id
note_id (notes table)

host_types

(describes the physical aspects of a host. Can be linked to many hosts. ie, a Dell 2850 server could be found running dozens of hosts)
id
description
cpu
ram_qty
platform
note_id (notes table)
max_btus (heat info)
height_u (measured in U space)
width (measured in cm, even though we're generally going to see a 19" device)
depth (measured in cm)
supplier
supplier_model_number
brand
brand_model_number
hardware_class_id (pointer into table of hardware classes)

notes

id
note
private?? (how should we tie this in.. I like the concept of keeping some notes private, but from whom?)

connector_types

id
wire_type_id
name ("5-15P", "RJ45", "RJ11", "DB9", etc)

rooms

id
name
address_id (address table)
user_id (contact info)
note_id (notes table)

racks

id
rack_type_id (rack_type table)
room_id (rooms table)

groups_operating_systems

(this is to associate a given group to a set of operating_systems -- assigning Mozilla to os instances X, Y, Z)
group_id
operating_system_id

groups_physical_hosts

(same as groups_operating_systems, but for the phys host)
group_id
physical_host_id

rack_types

id
name
height (total height of rack, in cm)
width (total width of rack, in cm)
depth (total depth of rack, in cm)
height_u (how many U slots are available for use)
width_u (how wide is the U area, in cm -- most US racks are 19in)
height_upper (cm between top of U slots to top of rack)
width_left (cm between left side of U slots to side of rack)
depth_front (cm between front of U space to front of rack)

(went with "cm" since "in" seemed too US-centric, even if they're all 19" racks...)

physical_host_changelogs

(changelog entries that are mapped to a host)
id
phys_host_id
date
note_id (notes table)
user_id (users table)

operating_systems_changelogs

(changelog entries that are mapped to an operating system)
id
operating_system_id
date
note_id (notes table)
user_id (users table)

fqdns

(for mapping a DNS fully qualified domain name to a host)
id
host_id
domainname

tcp_ports

(most of this will be an /etc/services import)
id
number
service

tcp_ports_servers

(maps ports from tcp_ports to hosts that should have those ports open appropriately.. if they have open ports not in the db, we worry. port 1337, heh)
port_id
host_id

(I notice we added "tcp" at the end.. should we drop it and add a field designating tcp/udp, or how do we want to handle udp?)

wire_types

id
name ("power", "ethernet", "serial", etc)

wires

id
wire_type_id

physical_ports

host_id
wire_id (can be null to signify an open port)
name
direction
connector_type_id

(this is the mapping table for connections, via two phys_ports sharing a common wire_id (even if connectors are different) -- allows for buses too)

users

(rename "people"?? they are not always "users" here, but could be contacts.. said contacts could also end up in ldap)
id
name
email
address_id
telephone
cellphone
pager
im
homepage
note_id
group_id
secondary_email
ssh_public_key
gpg_public_key
password (if using an internal auth mechanism)

groups

id
name
email
address_id (if it's a company, there would be a physical address for the group)
homepage
irc
note_id
group_state_id (optional "approval state of the group")

group_states

id
name ("Approved", "Pending", "Closed", etc)

users_groups

user_id
group_id
role? (leaving the ? here for comment)